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.
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;
/
Santhosh Bhamidipati - DBA 