Basic Performance in oracle

How will you find the performance issues ?

Mostly, when any users intimates us that

1. This query is running slowly
2. Daily, this query will retrieve data in 1 minute. But, today it is running since 2 hours.
3. Load average is high
4. CPU utilization of a process is high
5. Users experiencing slowness

 

a. Get the top consuming PID’s (Works in almost all linux related OS). And if it is Windows, please look into task manager.

ps -eo pcpu,pid,user,args | sort -k 1 -r |head -10

b.  Now, you need to pick the SQL_ID mainly for proceeding further. You will be prompted for PID which you picked in above command.

set linesize 2000;
select s.sid,s.serial#, s.inst_id,p.spid, s.SQL_ID, t.SQL_TEXT, s.machine from gv$process p, gv$session s, gv$sqltext t where s.paddr = p.addr and p.spid=&processid and s.SQL_HASH_VALUE = t.HASH_VALUE;

NOW TWO WAYS to go…!

c. 1. Using the sql_id, pick the tables involved in it. We are using explain plans.

select * from table(dbms_xplan.display_cursor(sql_id => ‘&SQL_ID’, format => ‘+ALLSTATS’));

c2. Run below Oracle Provided script to get details. This is Sql Tuning Report. This report will give all the recommendations available. 

@?/rdbms/admin/sqltrpt.sql

Now, you will be having the tables list. Check when was latest time stamp of the table analyzed.

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, TO_CHAR(LAST_ANALYZED, ‘MM/DD/YYYY HH24:MI:SS’) FROM DBA_TABLES WHERE TABLE_NAME =’&TABLE_NAME’;

WHAT IS THE USE OF LAST_ANALYZED COLUMN ?
This will give the information when the stats were gathered.

If the stats are not up-to-date, we need to gather stats.

Find whether the table is partitioned table or normal table.

select table_name, subpartition_name, global_stats, last_analyzed, num_rows from dba_tab_subpartitions where table_name=’&Tablename’ and table_owner=’&owner’ order by 1, 2, 4 desc nulls last;

select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER=’&owner’ and TABLE_NAME=’&Tablename’ order by LAST_ANALYZED;

 

If table has NO partitions and if stats need to be gathered, use this

EXEC dbms_stats.gather_table_stats(‘&Owner’,’&Table’,cascade=>TRUE);

If table has partitions, we can gather stats on whole table. But, its better to gather stats for recently created ten partitions, use below

begin
SYS.DBMS_STATS.gather_table_stats
(ownname => ‘&Table_Owner’,
tabname => ‘&Table_Name’,
PARTNAME => ‘&partition’,
estimate_percent => 0.001,
DEGREE => 24,
granularity => ‘PARTITION’,
CASCADE => TRUE,
stattab => NULL,
statid => NULL,
statown => NULL
);
END;
/

 

 

Basic Things in RAC

1. What is inittab?

Inittab is like oratab entry. Inittab is used for starting crs services in RAC environment. The line which is responsible to start is below. This file is responsible for starting the services.

h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null

Useful Commands:

1. crsctl enable has –> Enable Automatic start of Oracle High Availability services after reboot
2. crsctl disable has –> Disable Automatic start of Oracle High Availability services after reboot

2. What is OHASD?

Ohasd stands for Oracle High Availability Services Daemon. Ohasd spawns 3 types of services at cluster level.

Level 1 : Cssd Agent
Level 2: Oraroot Agent (respawns cssd, crsd, cttsd, diskmon,acfs)
Level 3: OraAgent(respawns mdsnd, gipcd, gpnpd, evmd, asm), CssdMonitor

Useful Commands:
1. crsctl enable has –> To start has services after reboot.
2. crsctl disable has –> has services should not start after reboot
3. crsctl config has –> Check configuration whether autostart is enabled or not.
4. cat /etc/oracle/scls_scr/<Node_name>/root/ohasdstr –> check whether it is enabled or not.
5. cat /etc/oracle/scls_scr/<Node_name>/root/ohasdrun –> whether restart enabled if node fails.

3. What is OCR? How and why OLR is used? Where is the location of OCR & OLR?

OCR stands for Oracle Cluster Registry. It holds information on it such as node membership (which nodes are part of this cluster), Software Version, Location of the voting disk, Status of RAC databases, listeners, instances & services. OCR is placed in ASM, OCFS.

ASM can be brought up only if we have access to OCR. But, OCR is accessible only after the ASM Is up. In this case, how will CRS services come up?

Yes. For this OLR (Oracle Local Registry) is there. This is a multiplexing of OCR file which was placed in local file system.

OLR holds information on it such as CRS_HOME, GPnP details, active version, localhost version, OCR latest backup(with time & location), Node name.,., .
Location Of OCR & OLR:

#cat /etc/oracle/ocr.loc –> OCR file Details.
ocrconfig_loc=<+ASM_Location>
local_only=FALSE

# cat /etc/oracle/olr.loc –> OLR file Details.
olrconfig_loc=<file_name_with_location.olr>
crs_home=<CRS_HOME_Location>

Useful Commands:

NOTE: Some commands like restore need bounce of services. Please verify before taking any action.

1. ocrconfig –showbackup –> OCR file backup location
2. ocrconfig –export < File_Name_with_Full_Location.ocr > –> OCR Backup
3. ocrconfig –restore <File_Name_with_Full_Location.ocr> –> Restore OCR
4. ocrconfig –import <File_Name_With_Full_Location.dmp> –> Import metadata specifically for OCR.
5. Ocrcheck –details –> Gives the OCR info in detail
6. ocrcheck –local –> Gives the OLR info in detail
7. ocrdump –local <File_Name_with_Full_Location.olr> –> Take the dump of OLR.
8. ocrdump <File_Name_with_Full_Location.ocr> –> Take the dump of OCR.

4. What is the Voting Disk and how is this Used?

If a node joins cluster, if a node fails (may be evicted), if VIP need to be assigned in case of GNS is configured. In all the cases, voting disk comes into picture. Voting disk saves the info of which nodes were part of cluster. While starting the crs services, with the help of OCR, it will vote in the voting disk (Nothing but mark attendance in the cluster)

We need not take the backup of the voting disk periodically like our cron jobs. We are supposed to take backup only in SOME of the below cases.

There are two different jobs done by voting disk.

1. Dynamic – Heart beat information
2. Static – Node information in the cluster

Useful Commands:

1. dd if=Name_Of_Voting_Disk of=Name_Of_Voting_Disk_Backup –> Taking backup of voting disk
2. crsctl query css votedisk –> Check voting disk details.
3. crsctl add css votedisk path_to_voting_disk –> To add voting disk
4. crsctl add css votedisk –force –> If the Cluster is down
5. crsctl delete css votedisk <File_Name_With_Password_With_file_name> –> Delete Voting disk
6. crsctl delete css votedisk –force –> If the cluster is down
7. crsctl replace votedisk <+ASM_Disk_Group> –> Replace the voting disk.

5. What is CRS?

CRSD stands for Cluster Resource Service Daemon. It is a proce–> which is responsible to monitor, stop, start & failover the resources. This process maintains OCR and this is responsible for restarting resource when any failover is about to take place.

Useful Commands:

1. crs_stat –t –v –> Check crs resources
2. crsctl stat res -t –> Check in a bit detail view. BEST ONE.
3. crsctl enable crs –> Enable Automatic start of Services after reboot
4. crsctl check crs –> Check crs Services.
5. crsctl disable crs –> Disable Automatic start of CRS services after reboot
6. crsctl stop crs –> Stop the crs services on the node which we are executing
7. crsctl stop crs –f –> Stop the crs services forcefully
8. crsctl start crs –> To start the crs services on respective node
9. crsctl start crs –excl –> To start the crs services in exclusive mode when u lost voting disk.
   You need to replace the voting disk after you start the css.
10. crsctl stop cluster –all –> Stop the crs services on the cluster nodes
11. crsctl start cluster –all –> Start the crs services on all the cluster nodes.
12. olsnodes –> Find all the nodes relative to the cluster
13. oclumon manage –get master –> With this you will get master node information
14. cat $CRS_HOME/crs/init/<node_name>.pid –> Find PID from which crs is running.

6. What is CSSD?

CSSD stands for Cluster Synchronization Service Daemon. This is responsible for communicating the nodes each other. This will monitor the heart beat messages from all the nodes.

Example:

We have 2 node RAC cluster. Till one hour back, our CSSD is monitoring both the nodes and able to communicate each other. Now, if one of the node is down, CRS should know that one of the node is down. This information is provided by CSSD process.

Simple Scenario:

If both the nodes are up & running now. And due to one of the communication channel, CSSD process got information that the other node is down. So, in this case, new transactions cannot be assigned to that node. The node eviction will be done. And the node which is running now will be taking the ownership as master node.

This sample scenario was taken for a better understanding ONLY.

Useful Commands:

1. crsctl stop css–> For stopping the css
2. crsctl disable css –> Disabling automatic startup after reboot.

7. What is CTTSD?

CTTSD stands for Cluster Time Synchronization Service Daemon. This service by default will be in observer mode. If time difference is there, it won’t be taking any action. To run this service in active mode, we need to disable all the time synchronization services like NTP (Network Time Protocol). But, it is recommended as per my knowledge to keep this service in observer mode. This line was quoted because, if this service is in online mode. And time synchronization difference is huge, the cttsd process may terminate. And sometimes, crsd fail to startup due to time difference.

Useful Commands:

1. cluvfy comp clocksync -n all -verbose –> To check the clock synchronization across all the nodes
2. crsctl check ctts –> Check the service status & timeoffset in msecs.

8. What is VIP?

VIP stands for Virtual IP Address. Oracle uses VIP for Database level access. Basically, when a connection comes from application end. Then using this IP address, it will connect. Suppose if IP for one of the node is down. As per protocol timeout, it need to wait 90 seconds to get a session. In this scenario, VIP comes into picture. If one of the VIP is down, connections will be routed only to the active node. The VIP must be on same address as public IP address. This VIP is used for RAC failover and RAC management.

Useful Commands:

1. srvctl start vip –n <node_name> -i <VIP_Name> –> To start VIP
2. srvctl stop vip –n <node_name> -i <VIP_Name> –> To stop VIP
3. srvctl enable vip -i vip_name –> Enable the VIP.
4. srvctl disable vip -i vip_name –> Disable the VIP.
5. srvctl status nodeapps –n <node_name> –> status of nodeapps
6. srvctl status vip –n <node_name> –> status of vip on a node

9. What is SCAN IP & Listener?

SCAN stands for Single Client Access Name. Scan IP’s must be on same sub net mask. Three SCAN IP’s is a recommended number of count which redirects user sessions to the scan listeners. Load balancing on scan listener will be done by least_recently_loaded algorithm.

SCAN Listener… When a connection is initiated from the application end, scan listener verifies the load balancing. And once it gets info, it will assign the connection to the node listener. And user can do his transaction.

Main use is that we need not change the connect string in the application servers if any changes on the cluster are done like adding a node, deleting a node and other modifications basing on requirement.

Useful Commands:

1. srvctl config scan –> retrieves scan listener configuration
2. srvctl config scan_listener –> List of scan listeners with Port number
3. srvctl add scan –n <node_name> –> Add a scan listener to the cluster
4. srvctl add scan_listener –p <Desired_port_number> –> to add scan listener on specific port
5. SQL> SHOW PARAMETER REMOTE_LISTENER; –> find the list of scan listeners
6. srvctl stop scan –> stops all scan listeners when used without –i option
7. srvctl stop scan_listener –> Stops one or more services in the cluster
8. srvctl start scan –> To start the scan VIP
9. srvctl start scan_listener –> Start the scan listener.
10. srvctl status scan –> verify scan VIP status
11. srvctl status scan_listener –> Verify scan listener status.
12. srvctl modify scan_listener –> Modify the scan listener
13. srvctl relocate scan_listener –i <Ordinal_Number> –n <node_name> –> relocate the scan listener to another node.

10. What is ologgerd?

Ologgerd stands for cluster logger service Daemon. This is otherwise called as cluster logger service. This logger services writes the data in the master node. And chooses other nodes as standby. If any network issue occurs between the nodes, and if it is unable to contact the master. Then the other node takes ownership & chooses a node as standby node. This master will manage the operating system metric database in CHM repository.

Useful Commands:

1. Oclumon manage –get master –> Find which is the master node
2. oclumon manage -get reppath –> Will get the path of the repository logs
3. oclumon manage -get repsize –> This will give you the limitations on repository size
4. Oclumon showobjects –>find which nodes are connected to loggerd
5. Oclumon dumpnodeview –> This will give a detail view including system, topconsumers, processes, devices, nics, filesystems status, protocol errors.
6. oclumon dumpnodeview -n <node_1 node_2 node_3> -last “HH:MM:SS” –> you can view all the details in c. column from a specific time you mentioned.
7. oclumon dumpnodeview allnodes -last “HH:MM:SS” –> If we need info from all the nodes.11.What is sysmon?

This process is responsible for collecting information in the local node. This will collect the info from every node and that data will be sent the data to master loggerd. This will send the info like CPU, memory usage, Os level info, disk info, disk info, process, file system info.

11. What is evmd?

Evmd stands for Event Volume Manager Daemon. This handles event messaging for the processes. It sends and receives actions regarding resource state changes to and from all other nodes in a cluster. This will take the help of ONS(Oracle Notification Services).

Useful Commands:

1. evmwatch -A -t “@timestamp @@” –> Get events generated in evmd.
2. Evmpost –u “<Message here>” –h <node_name> –> This will post message in evmd log in the mentioned node.

13. What is mdnsd?

Mdnsd stands for Multicast Domain Name Service. This process is used by gpndp to locate profiles in the cluster as well as by GNS to perform name resolutions. Mdnsd updates the pid file in init directory.

14. What is ONS?

ONS stands for Oracle Notification Service. ONS will allow users to send SMS, emails, voice messages and fax messages in a easy way. ONS will send the state of database, instance. This state information is used for load balancing. ONS will also communicate with daemons in other nodes for informing state of database.

This is started as part of CRS as part of nodeapps. ONS will run as a node application. Every node will have its own ONS configured.

Useful Commands:

1. srvctl status nodeapps –> Status of nodeapps
2. cat $ORACLE_HOME/opmn/conf/ons.config –> Check ons configuration.
3. $ORACLE_HOME/opmn/logs –> ONS logs will be in this location.

15. what is OPROCD ?

OPROCD stands for Oracle Process Monitor Daemon. Oprocd monitors the system state of cluster nodes. Stonith, which is nothing but power cycling the node. Simply, means power off & power on the server using reboot command. And main change in OPROCD is cssd agent from 11gR2.

Useful Commands:

1. CRS_HOME/oprocd stop –> To stop the processon single node.

16. What is FAN?

FAN stands for Fast Application Notification. If any state change occurs in cluster/instance/node, an event is triggered by the event manager and it is propogated by ONS. The event is known as FAN event. It was the feature which was introduced in Oracle 10g for an immediate notification. FAN uses ONS for notifying.

Useful Commands:

1. onsctl ping –> To check whether ons is running or not.
2. onsctl debug –> Will get detail view of ons.
3. onsctl start –> Start the daemon.
4. onsctl stop –> Stop the daemon.

17. What is TAF?

TAF stands for Trasparent Application Failover. When any rac node is down, the select statements need to failover to the active node. And insert, delete, update and also Alter session statements are not supported by TAF. Temporary objects & pl/sql packages are lost during the failover.

There are two types of failover methods used in TAF.

1. Basic failover: It will connect to single node. And no overload will be there. End user experiences delay in completing the transaction.
2. Preconnect failover: It will connect to primary & backup node at at time. This offers faster failover. An overload will be experienced as statement need to be ready to complete transaction with minimal delay.

Useful Commands:

1. Add a service:
   Srvctl add service –d <database_name> -s <Name_for_service> -r <instance_names> -p <Policy_specification>

Policy specification – none, basic, preconnect

2.   Check TAF status:
SELECT machine, failover_type, failover_method, failed_over, COUNT(*) FROM gv$session GROUP BY machine, failover_type, failover_method, failed_over;

18. What is FCF?

FCF stands for Fast Connection Failover. It is an application level failover process. This will automatically subscribes to FAN events and this will help in immediate reaction on the up & down events from the database cluster. All the failure applications are cleaned up immediately, so that the application will receive a failure message. And after cleanup, if new connection is received then with load balancing it will reach active node. As said, this is application level process I am not discussing much.

19. What is GCS(LMSn)?

GCS stands for Global Cache Service. GCS catches the information of data blocks, and access privileges of various instances. Integrity is maintained by maintaining global access. It is responsible for transferring blocks from instance to another instance when needed.

Clear Understanding: Blocks of table “A” were retrieved with a connection to second node. Now, if first node requests blocks from this table, services need not pick the data from the datafiles. Blocks can be retrieved from other instance. This is the main use of GCS.

19. What is GES(LMD)?

GES stands for Global Enqueue Service. GES controls library and dictionary caches on all the nodes. GES manages transaction locks, table locks, library cache locks, dictionary cache locks, database mount lock.

21. What is GRD?

GRD stands for Global Resource Directory. This is to record the information of resources and enqueues. As the word, it stores info on all the information. Information like Data block identifiers, data block mode(shared, exclusive, null), buffer caches will be having access.

22. What is GPNPD?

GPNPD stands for Grid Plug aNd Play Daemon. A file is located in CRS_HOME/gpnp/<node_name>/profile/peer/profile.xml which is known as GPNP profile. And this profile consists of cluster name, hostname, ntwork profiles with IP addresses, OCR. If we do any modifications for voting disk, profile will be updated.

Useful Commands:

1. gpnptool ver -> Check the version of tool.
2. gpnptool lfind ->  get local gpnpd server.
3. gpnptool get -> read the profile
4. gpnptool lfind -> check daemon is running on local node.
5. gpnptool check –p= CRS_HOME/gpnp/<node_name>/profile/peer/profile.xml -> Check whether configuration is valid.

23. why is Diskmon?

Disk monitor daemon continuously runs when ocssd starts. And it monitors and performs I/O fencing for Exadata storage server (This server is termed as cell as per Exadata). This process will run since the ocssd starts because exadata cell can be added to any cluster at any time.

Useful Commands:

1. ./crsctl stat res ora.diskmon <– To check diskmon status.

 

—————–END OF THE POST———————