Basic Performance Tunning in Oracle Database
How will you find the performance issues ?
Mostly, when any users intimates us that:
This query is running slowly ?
Daily, this query will retrieve data in 1 minute. But, today it is running since 2 hours ?
Load average is high ?
CPU utilization of a process is high ?
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.
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 we have two ways:
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’));
2. 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;
/
We can also run this SQL Tunning Advisor from OEM:
1.Go to home
2.Click on Advisor Central
3.Click on SQL Advisor
4.Click on SQL Tunning Advisor
5.Click on Top Activity
6.Choose a SQL_ID for which you want to run tune
7.Click on Actions button go
8.click Submit Button
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 we have two ways:
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’));
2. 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;
/
We can also run this SQL Tunning Advisor from OEM:
1.Go to home
2.Click on Advisor Central
3.Click on SQL Advisor
4.Click on SQL Tunning Advisor
5.Click on Top Activity
6.Choose a SQL_ID for which you want to run tune
7.Click on Actions button go
8.click Submit Button
Method.2
How to tune a SELECT query ?
Step.1
Find the SQL_ID of the slow running query
There could be two possibilities:
1) Query is still running: If the slow query is still running then we can find the sql_id of the query by using v$session view.
2) Query is completed: It might be query is completed but application team got to know it later that the query was slow and it did not finish in its usual time. Since this query is not running right now, we can’t query v$session to get its information.
So for this we use AWR/ASH report. We ask application team that at what time the query was running and for that duration we generate the AWR. In ASH report we find all kind of information regarding the top SQL’s. Basically we see SQL STATISTIS section of the AWR report. In this section there is SQL ORDERED BY ELAPSED TIME which matters most to us. If the query which is reported by application team is present in this section then we note down the sql_id of the query. Otherwise we generate ASH report for that particular time and get the sql_id.
Step.2
Run the SQL Tuning advisor for that SQL_ID
After finding the sql_id we can run sql tuning advisor on this sql_id.
@$ORACLE_HOME/rdbms/admin/sqltrpt.sql) by providing SQL_ID as the input for generating the findings and recommendations. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics.
Based on the tuning advisor recommendation we have to take corrective actions. These recommendation could be and many more:
1) Gather Statistics
2) Create Index
3) Drop Index
4) Join orders
5) Accept sql profile
6) Create baseline and many more recommendations
Before applying any corrective action on production, we need to test that in DEV/QA/UAT/TEST environment or we can ask and tuning expert that this is the recommendation. After all the analysis we should apply in the production database.
After corrective action from tuning advisor run the SQL again and see the improvement.
Step.3
Use TOP command to check the CPU usages by various processes:
TOP command is also useful for performance tuning. Many times, in a single server multiple databases are running. It may happen that one database is consuming more server resources than others. So we have to find out which oracle process is consuming more resources and it is related to which database. For this we use TOP command. If we see there is CPU used by an oracle process is very high then this a matter of subject to worry about.
If it is a DML statement then we have check the locking in the database
Step.4
Find the locking in the database:
The very first step is to find out if there is any locking in the database. Sometime due to locking a session does not get the required resources and the session gets slow.
We can find below command to check locking in the database:
sql>select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
|| s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.block=1 and l2.request > 0
and l1.id1=l2.id1
and l2.id2=l2.id2;
Query Output:
S1.USERNAME||'@'||S1.MACHINE||'(SID='||S1.SID||')ISBLOCKING'||S2.USERNAME||'@'||
----------------------------------------------------------------------------------------
SYS@host1.abc.com ( SID=229) is blockingSYS@host1.abc.com( SID=226)
If we see the locking from above query then we can simply inform to user/application. And if they suggest to kill this blocking session then after killing we can get rid of this slowness.
Step.5
Check for the wait events:
There could be some wait events on the database. Check for the particular user and session.
Query for displaying sessions, session state, and wait details
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
Output:
SID STATE Description
————— —————————— ———————————————————————————————————————————————————————
2832 Working Last waited 2029 secs for SQL*Net message from client
3346 Waiting So far 743 secs for enq: TX - row lock contention
4208 Waiting So far 5498 secs for SQL*Net message from client
It clearly shows the state of the sessions: whether they are working or waiting; if they are working, what they were waiting for earlier and for how long; and if they are waiting, what for and for how long.
In many troubleshooting situations, just knowing the SID of each session is not enough. We may need to know other details, such as the client machine the session is connecting from, the user (of both the database and the operating system), and the service name. All of this information is also readily available in the same V$SESSION view we have been using. Let’s briefly examine the columns that provide that information, by running the below query
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = 'ARUP';
SID OSUSER MACHINE TERMINAL SERVICE_NAME LOGON_TIME LAST_CALL_ET
————— —————— ——————— ———————— ———————————— —————————— ————————————
3346 oradb prodb1 pts/5 SYS$USERS 05-FEB-12 6848
2832 oradb prodb1 pts/6 SERV1 05-FEB-12 7616
4408 ANANDA ANLAP ANLAP ADHOC 05-FEB-12 0
OSUSER. The operating system user as which the client is connected. The output indicates that session 4408 is connected from the ANLAP machine, where a Windows user, ANANDA, has logged in.
MACHINE. The name of the machine where the client is running. This could be the database server itself. For two of the sessions, the machine name shows up as “prodb1.” Session 4408 runs on a different machine—ANLAP—presumably a laptop.
TERMINAL. If the session is connected from a UNIX server, this is the terminal where it runs.
LOGON_TIME. This shows when the session was first connected to the Oracle Database instance.
More:
So for this we use AWR/ASH report. We ask application team that at what time the query was running and for that duration we generate the AWR. In ASH report we find all kind of information regarding the top SQL’s. Basically we see SQL STATISTIS section of the AWR report. In this section there is SQL ORDERED BY ELAPSED TIME which matters most to us. If the query which is reported by application team is present in this section then we note down the sql_id of the query. Otherwise we generate ASH report for that particular time and get the sql_id.
Step.2
Run the SQL Tuning advisor for that SQL_ID
After finding the sql_id we can run sql tuning advisor on this sql_id.
@$ORACLE_HOME/rdbms/admin/sqltrpt.sql) by providing SQL_ID as the input for generating the findings and recommendations. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics.
Based on the tuning advisor recommendation we have to take corrective actions. These recommendation could be and many more:
1) Gather Statistics
2) Create Index
3) Drop Index
4) Join orders
5) Accept sql profile
6) Create baseline and many more recommendations
Before applying any corrective action on production, we need to test that in DEV/QA/UAT/TEST environment or we can ask and tuning expert that this is the recommendation. After all the analysis we should apply in the production database.
After corrective action from tuning advisor run the SQL again and see the improvement.
Step.3
Use TOP command to check the CPU usages by various processes:
TOP command is also useful for performance tuning. Many times, in a single server multiple databases are running. It may happen that one database is consuming more server resources than others. So we have to find out which oracle process is consuming more resources and it is related to which database. For this we use TOP command. If we see there is CPU used by an oracle process is very high then this a matter of subject to worry about.
If it is a DML statement then we have check the locking in the database
Step.4
Find the locking in the database:
The very first step is to find out if there is any locking in the database. Sometime due to locking a session does not get the required resources and the session gets slow.
We can find below command to check locking in the database:
sql>select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
|| s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.block=1 and l2.request > 0
and l1.id1=l2.id1
and l2.id2=l2.id2;
Query Output:
S1.USERNAME||'@'||S1.MACHINE||'(SID='||S1.SID||')ISBLOCKING'||S2.USERNAME||'@'||
----------------------------------------------------------------------------------------
SYS@host1.abc.com ( SID=229) is blockingSYS@host1.abc.com( SID=226)
If we see the locking from above query then we can simply inform to user/application. And if they suggest to kill this blocking session then after killing we can get rid of this slowness.
Step.5
Check for the wait events:
There could be some wait events on the database. Check for the particular user and session.
Query for displaying sessions, session state, and wait details
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
Output:
SID STATE Description
————— —————————— ———————————————————————————————————————————————————————
2832 Working Last waited 2029 secs for SQL*Net message from client
3346 Waiting So far 743 secs for enq: TX - row lock contention
4208 Waiting So far 5498 secs for SQL*Net message from client
It clearly shows the state of the sessions: whether they are working or waiting; if they are working, what they were waiting for earlier and for how long; and if they are waiting, what for and for how long.
In many troubleshooting situations, just knowing the SID of each session is not enough. We may need to know other details, such as the client machine the session is connecting from, the user (of both the database and the operating system), and the service name. All of this information is also readily available in the same V$SESSION view we have been using. Let’s briefly examine the columns that provide that information, by running the below query
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = 'ARUP';
SID OSUSER MACHINE TERMINAL SERVICE_NAME LOGON_TIME LAST_CALL_ET
————— —————— ——————— ———————— ———————————— —————————— ————————————
3346 oradb prodb1 pts/5 SYS$USERS 05-FEB-12 6848
2832 oradb prodb1 pts/6 SERV1 05-FEB-12 7616
4408 ANANDA ANLAP ANLAP ADHOC 05-FEB-12 0
OSUSER. The operating system user as which the client is connected. The output indicates that session 4408 is connected from the ANLAP machine, where a Windows user, ANANDA, has logged in.
MACHINE. The name of the machine where the client is running. This could be the database server itself. For two of the sessions, the machine name shows up as “prodb1.” Session 4408 runs on a different machine—ANLAP—presumably a laptop.
TERMINAL. If the session is connected from a UNIX server, this is the terminal where it runs.
LOGON_TIME. This shows when the session was first connected to the Oracle Database instance.
More:
1.Run TOP command in Linux to check CPU usage.
2.Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.
3.Enable the trace file before running your queries,then check the trace file using tkprof create output file.
According to explain plan check the elapsed time for each query,then tune them respectively.
What is the use of iostat/vmstat/netstat command in Linux?
iostat – reports on terminal, disk and tape I/O activity.
vmstat – reports on virtual memory statistics for processes, disk, tape and CPU activity.
netstat – reports on the contents of network data structures.
2.Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.
3.Enable the trace file before running your queries,then check the trace file using tkprof create output file.
According to explain plan check the elapsed time for each query,then tune them respectively.
What is the use of iostat/vmstat/netstat command in Linux?
iostat – reports on terminal, disk and tape I/O activity.
vmstat – reports on virtual memory statistics for processes, disk, tape and CPU activity.
netstat – reports on the contents of network data structures.
