Various initialization parameters influence the choice of execution plan by the optimizer. Multiple executions of the same SQL with different optimizer parameters may employ different execution plans and hence result in difference in performance . In this post, I will demonstrate how we can identify the CBO parameters which had different values during two executions of the same SQL.
I will explore two scenarios :
- the cursors are still available in shared pool
- the cursors have been flushed to AWR
Overview:
- Flush shared pool
- Execute the same statement in two sessions with different values of OPTIMIZER_MODE
- Verify that the cursors for the statement are still in the shared pool
- Find out the execution plans employed by two cursors
- Find out the optimizer parameters which had different values during two executions of the SQL
- Take snapshot so that cursors are flushed to AWR
- Verify that the cursors for the statement are in AWR
- Find out the execution plans employed by two cursors
- Find out the value of parameter OPTIMIZER_MODE during two executions of the SQL
Implementation
- Flush shared pool
SQL>conn / as sysdba alter system flush shared_pool;
- Execute the same statement in two sessions with different values of OPTIMIZER_MODE
– Note that there is difference in the time elapsed during two executions of the same statement
– Session – I –
SQL>alter session set optimizer_mode = 'ALL_ROWS'; set timing on select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s, sh.products p where s.prod_id = p.prod_id; set timing off 918843 rows selected. Elapsed: 00:01:19.57 -- Session - II -- SQL>alter session set optimizer_mode = 'FIRST_ROWS_1'; set timing on select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s, sh.products p where s.prod_id = p.prod_id; set timing off 918843 rows selected. Elapsed: 00:01:16.63
– Verify that the cursors for the statement are still in the shared pool and find out SQL_ID of the statement
SQL>set pagesize 200 col sql_text for a50 select sql_id, child_number, sql_text from v$sql where sql_text like '%select s.quantity_sold, s.amount_sold%'; SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ ---------------------------------------------- 394uuwwbyjdnh 0 select sql_id, child_number, sql_text from v$sql w here sql_text like '%select s.quantity_sold, s.am ount_sold%' 6y2xaw3asr6xv 0 select s.quantity_sold, s.amount_sold, p.prod_nam e from sh.sales s, sh.products p where s.prod_id = p.prod_id 6y2xaw3asr6xv 1 select s.quantity_sold, s.amount_sold, p.prod_nam e from sh.sales s, sh.products p where s.prod_id = p.prod_id
– Find out the execution plans employed by two cursors
– Note that
- child 0 uses hash join whereas child 1 employs nested loops join
- Outline Data shows that optimizer_mode was ALL_ROWS during first execution and FIRST_ROWS(1) during second execution.
SQL>select * from table (dbms_xplan.display_cursor('6y2xaw3asr6xv', 0, format => 'TYPICAL +OUTLINE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 6y2xaw3asr6xv, child number 0 ------------------------------------- select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s, sh.products p where s.prod_id = p.prod_id Plan hash value: 1019954709 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 495 (100)| || | |* 1 | HASH JOIN | | 918K| 36M| 495 (3)| 00:00:06 | | | | 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | | | 3 | PARTITION RANGE ALL| | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 | | 4 | TABLE ACCESS FULL | SALES | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 | ------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "P"@"SEL$1") FULL(@"SEL$1" "S"@"SEL$1") LEADING(@"SEL$1" "P"@"SEL$1" "S"@"SEL$1") USE_HASH(@"SEL$1" "S"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("S"."PROD_ID"="P"."PROD_ID") select * from table (dbms_xplan.display_cursor('6y2xaw3asr6xv', 1, format => 'TYPICAL +OUTLINE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 6y2xaw3asr6xv, child number 1 ------------------------------------- select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s, sh.products p where s.prod_id = p.prod_id Plan hash value: 3603960078 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | | | 1 | NESTED LOOPS | | | | | | | | | 2 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 | | | | 3 | PARTITION RANGE ALL | | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 | | 4 | TABLE ACCESS FULL | SALES | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 | |* 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| | | | | 6 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 1 (0)| 00:00:01 | | | ------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') FIRST_ROWS(1) OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "S"@"SEL$1") INDEX(@"SEL$1" "P"@"SEL$1" ("PRODUCTS"."PROD_ID")) LEADING(@"SEL$1" "S"@"SEL$1" "P"@"SEL$1") USE_NL(@"SEL$1" "P"@"SEL$1") NLJ_BATCHING(@"SEL$1" "P"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("S"."PROD_ID"="P"."PROD_ID")
- It can be verified from v$sql_shared_cursor also that there was optimizer mode mismatch between two executions of the SQL
SQL>select SQL_ID, child_number, OPTIMIZER_MODE_MISMATCH
from v$sql_shared_cursor
where sql_id = ‘6y2xaw3asr6xv';
SQL_ID CHILD_NUMBER O
————- ———— –
6y2xaw3asr6xv 0 N
6y2xaw3asr6xv 1 Y
– We can also employ v$sql_optimizer_env to find out optimizer parameters which have different values during 2 executions
SQL>set line 500 col name for a15 select child1.name, child1.value child1_value, child2.value child2_value from v$sql_optimizer_env child1, v$sql_optimizer_env child2 where child1.sql_id = '6y2xaw3asr6xv' and child1.name like 'optimizer%' and child2.sql_id = '6y2xaw3asr6xv' and child2.name like 'optimizer%' and child1.name = child2.name and child1.value <> child2.value; NAME CHILD1_VALUE CHILD2_VALUE --------------- ------------------------- ------------------------- optimizer_mode first_rows_1 all_rows optimizer_mode all_rows first_rows_1
– Take snapshot so that cursors are flushed to AWR
SQL>exec dbms_workload_repository.create_snapshot ('ALL');
– Verify that the statement is in AWR
SQL> set pagesize 200
SQL> col sql_text for a50
SQL> select sql_id, sql_text from dba_hist_sqltext where sql_text like '%select s.quantity_sold, s.amount_sold%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
6y2xaw3asr6xv select s.quantity_sold, s.amount_sold, p.prod_nam
e from sh.sales s,
sh.products
9fr7ycjcfqydb select sql_id, sql_text from v$sql where sql_text
like '%select s.quantity_sold
– Find out the execution plans that were employed by various cursors of the same statement
Note that
- one cursor uses hash join whereas other cursor employs nested loops join
- The value of parameter OPIMIZER_MODE was ALL_ROWS during one execution and FIRST_ROWS(1) during second execution
SQL>select * from table(dbms_xplan.display_awr ('6y2xaw3asr6xv', format => 'TYPICAL +OUTLINE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 6y2xaw3asr6xv -------------------- select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s, sh.products p where s.prod_id = p.prod_id Plan hash value: 1019954709 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 495 (100)| || | | 1 | HASH JOIN | | 918K| 36M| 495 (3)| 00:00:06 | | | | 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | | | 3 | PARTITION RANGE ALL| | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 | | 4 | TABLE ACCESS FULL | SALES | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 | ------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "P"@"SEL$1") FULL(@"SEL$1" "S"@"SEL$1") LEADING(@"SEL$1" "P"@"SEL$1" "S"@"SEL$1") USE_HASH(@"SEL$1" "S"@"SEL$1") END_OUTLINE_DATA */ SQL_ID 6y2xaw3asr6xv -------------------- select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s, sh.products p where s.prod_id = p.prod_id Plan hash value: 3603960078 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | | | 1 | NESTED LOOPS | | | | | | | | | 2 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 | | | | 3 | PARTITION RANGE ALL | | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 | | 4 | TABLE ACCESS FULL | SALES | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 | | 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| | | | | 6 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 1 (0)| 00:00:01 | | | ------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') FIRST_ROWS(1) OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "S"@"SEL$1") INDEX(@"SEL$1" "P"@"SEL$1" ("PRODUCTS"."PROD_ID")) LEADING(@"SEL$1" "S"@"SEL$1" "P"@"SEL$1") USE_NL(@"SEL$1" "P"@"SEL$1") NLJ_BATCHING(@"SEL$1" "P"@"SEL$1") END_OUTLINE_DATA */
— We can also use dba_hist_sqlstat to check the optimizer mode during two executions
SQL>select sql_text, optimizer_mode from dba_hist_sqlstat h, dba_hist_sqltext t where h.sql_id = '6y2xaw3asr6xv' and t.sql_id = '6y2xaw3asr6xv'; SQL_TEXT OPTIMIZER_ -------------------------------------------------- ---------- select s.quantity_sold, s.amount_sold, p.prod_nam ALL_ROWS e from sh.sales s, sh.products select s.quantity_sold, s.amount_sold, p.prod_nam FIRST_ROWS e from sh.sales s, sh.products
Summary:
The difference in values of various CBO parameters during various executions of the same statement can be found out while the statement is in shared pool or has been flushed to disk .
————————————————————————————————————————-
Related links:
Find Out Values Of Bind Variables Across Multiple Executions Of A SQL
Thanks Anju for this nice post.
Foued