SQL Performance issues basic Troubleshooting Stpes

 

SQL Performance Troubleshooting

In order to troubleshoot SQL Performance , you will need to do some basic troubleshooting in systematic approach

Steps :
(Request apps user to provide Request id/session details and Level 12 Tkprof for problematic SQL)

a)Find Top SQL from Gv$session/gv$active_session_history/dba_hist_active_sess_history(by giving Sid serial #and inst id)

b) SQL Text for Top sql

C) Execution or online history for the SQL

also check for CPU usage LIO PIO 

Row executed

No of Executions for SQL(time per single execution)

and also check for IO latency if any

d) check plan_table for plans opted by the SQL(dbms-xplain-awr/cursor)

e)check Table and index Stats and data distribution on columns

f) if SQL is currently running check How many rows it's processing

g)check previous history for the SQL using hash_value

h)find bind variable passing in the Query

I)find Row source info if SQL is running 

1)Get the Execution Statistics for given SQL_ID
2)Get the Execution Plan for given SQL_ID
3)Get the SQL HC /SQLT Report for given SQL_ID
4) from the sql history/online history Check the given SQL ID is using same plan or different plan

5) Condition 1:- If you have a different Plan

Compare the Plan_Hash_Value with Old Plan Hash Value

a) Check where exactly the change is appearing in the plan with current and historical

b) Check the statistics of the tables where change is appearing or for all objects

(stale stats)

Check statistics for both tables/indexes should not be Stale and PCT analysed must be greater than 10%atleast

Check fragmentation it should not be more than 80 % if it is try to rebuild the table/index

Also check for clustering factor as well.

c) Check the data Volume differences for the job/ Sql which may  be causing plan change

Solution 1:- Collect the statistics and ask to rerun

Solution 2:- Even if collecting statistics does not resolved the issue and you need to fix on fly,

Create SQL Profile based on old good plan.

6)Condition 2:-
If you have same plan but still slow
(Condition 1 Clear)
a) Check what the SQL is waiting for?
Also check for blocking session s if any
Kill them if required by checking the impact and all
b) capture SQLT Report, ADDM/AWR/ASH Report for given SQL_ID

Check the recommendation in the STA/SQLT report.

c) Troubleshoot based on wait event
Also from Tkprof finding where the SQL spending most of the time.

See Type of Joins used by query

(Nested loop/Hash join/sort merge join)

Access paths(Table scan/Index scan)

I.e (Full/Index unique scan/

Index Range scan/Index Fast full Scan/

Index skip scan)

Filtering conditions in the query(i.e conditions in where clause)

d) Identify is the SQL is culprit or victim,
means

culprit:- If taking more I/O,CPU,Change plan etc

victim:- Because of some other consumers your SQL waiting for resources?

Solutions :

1)As part of STF , 

We will suggest to deploy SQL profile with baseline for  plan fluctuation

 if there is good plan .

2)As part of LTF,

 If at all there is tunning scope then we will fix it 

1) With  Hints in the Query.

2) Index / Histogram

3) Partitioning 

based on the scenarios

Step : 3: in worst case  as a last option we can Re-write the query for the better performance.

  implement the code changes in dev/test environment  and will take Tkprof for post changes and we will compare pre and post Tkprof to validate performance gain for the same.

Finally if it's works fine then will go for code changes in Production with proper approvals.

Monitor the changes made in prod if it's fine in prod as well will provide signoff for the case or incident 

4) Also in some cases we can raise an Oracle SR (service request) to work closely with oracle engineer to fix the critical issues.