There are few situations where we know the execution plan of a particular query can be improved with use of proper hint, But if we are not allowed to change the application query in any way(we can’t even add a hint also),then how to achive this?
Example:
For the below query, we want to force it to use parallel 4 Hint execution plan, But without changing the query.
select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
09:33:13 select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
13899072
Elapsed: 00:00:17.29
select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID g5ch3zx56jawc, child number 1
-------------------------------------
select count(*) from bigtab where weight in ( select distinct( weight)
from bigtab)
Plan hash value: 1484132691
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 50544 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN SEMI | | 10M| 258M| 248M| 50544 (1)| 00:10:07 |
| 3 | TABLE ACCESS FULL| BIGTAB | 10M| 129M| | 12861 (1)| 00:02:35 |
| 4 | TABLE ACCESS FULL| BIGTAB | 10M| 129M| | 12861 (1)| 00:02:35 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("WEIGHT"="WEIGHT")
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
Elapsed: 00:00:00.03
Currently the query is taking around 17 seconds.
Lets create a baseline for this query.
SQL > variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'g5ch3zx56jawc');09:37:49 SQL >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
SQL> select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:37:35 2 09:37:35 3 09:37:35 4 09:37:35 5
09:38:02 SQL > 09:38:02 2 09:38:02 3 09:38:02 4 09:38:02 5
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxd856ccc5f YES
tab)
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxdc33b09c9 YES
tab)
Elapsed: 00:00:00.01
We can see two plans got loaded. Lets disable these existing plans.
09:41:16 SQL > exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle = > 'SQL_a868c91ef7af57ad',-
plan_name = >'SQL_PLAN_ahu693vvuypxd856ccc5f',-
attribute_name= >'enabled',-
attribute_value= >'NO');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
09:42:49 SQL > exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle = >'SQL_a868c91ef7af57ad',-
plan_name = > 'SQL_PLAN_ahu693vvuypxdc33b09c9',-
attribute_name= > 'enabled',-
attribute_value= >'NO');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
09:43:02 SQL > select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:43:05 2 09:43:05 3 09:43:05 4 09:43:05 5
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxd856ccc5f NO
tab)
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxdc33b09c9 NO
tab)
Elapsed: 00:00:00.01
Lets run the the same query with parallel hint and get the execution plan.
09:33:37 SQL > select /*+ parallel (4) */ count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
13899072
Elapsed: 00:00:07.41
09:33:57 SQL > select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID gq322uk9152yg, child number 0
-------------------------------------
select /*+ parallel (4) */ count(*) from bigtab where weight in (
select distinct( weight) from bigtab)
Plan hash value: 1244248794
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 13342 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 26 | | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN SEMI | | 10M| 258M| 62M| 13342 (1)| 00:02:41 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| BIGTAB | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL| BIGTAB | 10M| 129M| | 3568 (1)| 00:00:43 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("WEIGHT"="WEIGHT")
9 - access(:Z>=:Z AND :Z<=:Z) 13 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=2)
- Degree of Parallelism is 4 because of hint
38 rows selected.
Elapsed: 00:00:00.02
So for the query with parallel hint, you can get these two details from the above execution plan.
SQL ID: gq322uk9152yg
Plan hash value: 1244248794
Now load these two values to the existing sql handle id SQL_a868c91ef7af57ad
09:43:02 SQL > select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:43:05 2 09:43:05 3 09:43:05 4 09:43:05 5
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxd856ccc5f NO
tab)
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxdc33b09c9 NO
tab)
Elapsed: 00:00:00.01
09:47:45 SQL > execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id = > 'gq322uk9152yg',-
plan_hash_value => 1244248794,-
sql_handle=>'SQL_a868c91ef7af57ad');
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
09:50:43 SQL > select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%bigtab%';09:50:49 2 09:50:49 3 09:50:49 4 09:50:49 5
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxd6e73d422 YES
tab)
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxd856ccc5f NO
tab)
SQL_a868c91ef7af57ad select count(*) from bigtab where weight in ( select distinct( weight) from big SQL_PLAN_ahu693vvuypxdc33b09c9 NO
tab)
Elapsed: 00:00:00.01
We can see a new plan has been enabled for this sql. This is the parallel hinted plan.
Lets check the explain plan of the orginal query and verify whether it is using the parallel hinted execution plan or not.
09:50:49 SQL > select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
COUNT(*)
----------
13899072
Elapsed: 00:00:10.98
09:51:25 SQL > explain plan for
09:51:32 2 select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
Explained.
Elapsed: 00:00:00.03
09:51:35 SQL > select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));09:51:47 2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
Plan hash value: 1244248794
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 00:00:15 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PX COORDINATOR | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | Q1,02 | PCWP | |
|* 5 | HASH JOIN SEMI | | 3847K| 00:00:15 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 3847K| 00:00:08 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 3847K| 00:00:08 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 3847K| 00:00:08 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| BIGTAB | 3847K| 00:00:08 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 3847K| 00:00:08 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 3847K| 00:00:08 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 3847K| 00:00:08 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| BIGTAB | 3847K| 00:00:08 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("WEIGHT"="WEIGHT")
Note
-----
- Degree of Parallelism is 4 because of hint
- SQL plan baseline "SQL_PLAN_ahu693vvuypxd6e73d422" used for this statement
30 rows selected.
Elapsed: 00:00:00.03
Now it is using the parallel hint execution plan, even if we haven’t used that hint in the query.
Hi,
Great Article!
How can do this for query from application that have bind variables?
We can get the bind values of the application query, by passing the sql_id in below query.
SELECT
sql_id,
b. LAST_CAPTURED,
t.sql_text sql_text,
b.HASH_VALUE,
b.name bind_name,
b.value_string bind_value
FROM
gv$sql t
JOIN
gv$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND sql_id=’&sqlid’ order by b.last_captured desc
Once you have the bind values, You can run that query with hint(if required) by placing the bind values
Regards
Admin