Explore Oracle Stuff: OEM and SQL tuning

Thank you very much for visiting my website. I have got one interesting SQL tuning case study to share with you.

Problem statement: Application team complained about their job performance and a SQL statement was identified which was keep on appearing coming from a stored procedure.When we capture execution plan we found that already there was a SQL profile which indicates some tuning history. Though SQL profile is not helping much and definitely need new tuning approach. Please mind that we can not change application code as it not written by application team rather a code coming from software product.

Gather and analyze:

We gathered below execution plan which was active during that time.Please emphasize the highlighted plan line with TABLE ACESS FULL. Registerreading is a partitioned table of size around 2 TB.

Use below query to gather SQL execution stats

SELECT sql_id,elapsed_time_total/1000000 elapsed_time,cpu_time_total/1000000 cpu_time,buffer_gets_total,physical_read_bytes_total,optimizer_cost,plan_hash_value FROM dba_hist_sqlstat where sql_id='9p57n25mfmnqx';

SQL_ID ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS OPTIMIZER_COST PLAN_HASH_VALUE EXECUTIONS

------------- --------------- --------------- ----------- ---------- -------------- ---------------

9p57n25mfmnqx 1147390 815874 1.5195E+10 541315184 13086 3578737397 6874

You can see that elapsed time in seconds is also very high. Though it is cumulated value for multiple execution but per execution is coming around 166 seconds which is very high if your sql statement is coming through a loop multiple times.

Definitely this will slow down your job performance.

Approach:-

In 1st attempt tried to find out any other plan available in cursor cache which we can find using v$sql but no luck.If it would there I can extract the plan and compare.If it better I could have created SQL plan baseline out of that.
In 2nd attempt tried to find from AWR repository but still no luck.
In 3rd attempt tried to find from SQL tunning set and surprisingly I found a way much better execution plan.Someone must have tried in past to create STS and I really wanted to thank that person.Now explore that 3rd option how did I found that.I must appreciate oracle product development team of OEM who has made our life easy. Use below navigation from OEM to seach specific SQL_ID with multiple options (Performance>SQL>Search SQL).Provide SQL_ID as input and select option as SQL tuning sets

Now I can see there is a plan stored under that sql_id which has far better elapsed time.Exciting right!!

Lets explore these plan and see the change.

set line 200

set pagesize 400

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET(sqlset_name => '01W1G5Z5GMAJ2',sql_id => '9p57n25mfmnqx' , format => 'TYPICAL' ));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Tuning Set Name: 01W1G5Z5GMAJ2
SQL Tuning Set Owner: SYSTEM
SQL_ID: 9p57n25mfmnqx
SQL Text: SELECT NODEID, ENDTIME, DATAVALUE, STATUSID, READINGGROUPALTERNATEKEY
FROM ( SELECT R.SPCNODEID AS NODEID, R.ENDTIME, R.DATAVALUE,
R.STATUSID, R.READINGGROUPALTERNATEKEY, RRS.DELETED , ROW_NUMBER()
OVER (PARTITION BY R.SPCNODEID, R.ENDTIME ORDER BY
R.READINGGROUPALTERNATEKEY DESC) AS RN FROM T$_VALUE_LIST NL JOIN
REGISTERREADING R ON (R.SPCNODEID = TO_NUMBER(NL.LISTVALUE)) JOIN
READINGREADINGSTATUS RRS ON (RRS.STATUSID = R.STATUSID) WHERE
R.ENDTIME > :B2 AND R.ENDTIME <= :B1 ) X WHERE X.RN = 1 AND X.DELETED
= 0 ORDER BY NODEID, ENDTIME, READINGGROUPALTERNATEKEY
--------------------------------------------------------------------------------

Plan hash value: 1555854364

----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 5170 (100)| | | |
| 1 | SORT ORDER BY | | 5827 | 455K| | 5170 (1)| 00:00:01 | | |
| 2 | VIEW | | 5827 | 455K| | 5169 (1)| 00:00:01 | | |
| 3 | WINDOW SORT PUSHED RANK | | 5827 | 11M| 15M| 5169 (1)| 00:00:01 | | |
| 4 | FILTER | | | | | | | | |
| 5 | HASH JOIN | | 5827 | 11M| | 2683 (1)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | READINGREADINGSTATUS | 20727 | 161K| | 116 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | | | | | | | |
| 8 | NESTED LOOPS | | 5827 | 11M| | 2566 (1)| 00:00:01 | | |
| 9 | TABLE ACCESS FULL | T$_VALUE_LIST | 8 | 16016 | | 2 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ITERATOR | | 335 | | | 135 (0)| 00:00:01 | KEY | KEY |
| 11 | PARTITION HASH ITERATOR | | 335 | | | 135 (0)| 00:00:01 | KEY | KEY |
| 12 | INDEX RANGE SCAN | PK_REGISTERREADING | 335 | | | 135 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID| REGISTERREADING | 728 | 22568 | | 320 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile "SYS_SQLPROF_0190f0a58bf60001" used for this statement

I am so happy to see this plan highlighted in green which is using index range scan and reducing buffer gets(See Bytes column) and CPU cost .Aso observe that partition start and end is mentioned 1.For other plans it partition hash all 1-8 leading more I/O.

Plan hash value: 2534627475

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 873 (100)| | | |
| 1 | SORT ORDER BY | | 2000 | 156K| | 873 (1)| 00:00:01 | | |
| 2 | VIEW | | 2000 | 156K| | 872 (1)| 00:00:01 | | |
| 3 | WINDOW SORT PUSHED RANK | | 2000 | 4031K| 5344K| 872 (1)| 00:00:01 | | |
| 4 | FILTER | | | | | | | | |
| 5 | HASH JOIN | | 2000 | 4031K| | 8 (13)| 00:00:01 | | |
| 6 | NESTED LOOPS | | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 62 | | 3 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE ITERATOR | | 1 | 54 | | 2 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION HASH ALL | | 1 | 54 | | 2 (0)| 00:00:01 | 1 | 8 |
| 10 | TABLE ACCESS FULL | REGISTERREADING | 1 | 54 | | 2 (0)| 00:00:01 | KEY | KEY |
| 11 | INDEX UNIQUE SCAN | PK_READINGREADINGSTATUS | 1 | | | 1 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID| READINGREADINGSTATUS | 1 | 8 | | 1 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | T$_VALUE_LIST | 2000 | 3910K| | 4 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile "SYS_SQLPROF_0190f0a58bf60001" used for this statement

Plan hash value: 3578737397

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13086 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 80 | 13086 (1)| 00:00:01 | | |
| 2 | VIEW | | 1 | 80 | 13085 (1)| 00:00:01 | | |
| 3 | WINDOW SORT PUSHED RANK | | 1 | 2042 | 13085 (1)| 00:00:01 | | |
| 4 | FILTER | | | | | | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 2042 | 13084 (1)| 00:00:01 | | |
| 7 | HASH JOIN | | 1 | 2034 | 13083 (1)| 00:00:01 | | |
| 8 | PARTITION RANGE ITERATOR | | 1199 | 38368 | 13078 (1)| 00:00:01 | KEY | KEY |
| 9 | PARTITION HASH ALL | | 1199 | 38368 | 13078 (1)| 00:00:01 | 1 | 8 |
| 10 | TABLE ACCESS FULL | REGISTERREADING | 1199 | 38368 | 13078 (1)| 00:00:01 | KEY | KEY |
| 11 | TABLE ACCESS FULL | T$_VALUE_LIST | 2000 | 3910K| 4 (0)| 00:00:01 | | |
| 12 | INDEX UNIQUE SCAN | PK_READINGREADINGSTATUS | 1 | | 1 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID| READINGREADINGSTATUS | 1 | 8 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile "SYS_SQLPROF_0190f0a58bf60001" used for this statement

92 rows selected.

Final soultion:

We can create plan baseline from this tuning set

set serveroutput on

declare

my_integer pls_integer;

begin

my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => '01W1G5Z5GMAJ2', sqlset_owner => 'SYSTEM', fixed => 'NO', enabled => 'YES');

DBMS_OUTPUT.PUT_line(my_integer);

end;

Verify it using below sql

select * from dba_sql_plan_baselines where sql_text like 'Your partial sql text%'

Thank you very much for your patience.I hope you have learnt something very useful....(:-)

Explore Oracle Stuff

Saturday, March 22, 2025

OEM and SQL tunning

When SQL plan baseline is not used

Report Abuse

Labels