Oracle uses coe_load_sql_profile script to bind execution plan
The coe_load_sql_profile_v2.sql script uses the profile principle and is only used in a semi-automatic form. Here is the test process.
Coe_load_sql_profile_v2.txt
Create the environment and build the test table:
SQL > create table T1 as select * from dba_objects where object_id is not null
Table created.
SQL > alter table T1 modify object_id not null
Table altered.
SQL > create index idx_t1_obj_id on T1 (object_id)
Index created.
The index does not store null values
SQL > analyze table T1 compute statistics
Table analyzed.
SQL > select count (*) from T1
COUNT (*)
-
87070
SQL > select * from table (dbms_xplan.display_cursor (null,0))
PLAN_TABLE_OUTPUT
-
SQL_ID 12c0v4my7dvr3, child number 0
-
Select count (*) from T1
Plan hash value: 1657298618
-
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | 54 (100) | |
| | 1 | SORT AGGREGAT | | 1 |
PLAN_TABLE_OUTPUT
-
| | 2 | INDEX FAST FULL SCAN | IDX_T1_OBJ_ID | 87070 | 54 (0) | 00:00:01 |
14 rows selected.
You will find that the index is scanned quickly.
Using the hint prompt, force a full table scan, and then generate an execution plan.
SQL > select / * + full (T1) * / count (*) from T1
COUNT (*)
--
87070
SQL > select * from table (dbms_xplan.display_cursor (null,0))
PLAN_TABLE_OUTPUT
-
SQL_ID 20pat9zfypprh, child number 0
-
Select / * + full (T1) * / count (*) from T1
Plan hash value: 3724264953
-
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | 347100 | | |
| | 1 | SORT AGGREGATE | | 1 | | |
PLAN_TABLE_OUTPUT
-
| | 2 | TABLE ACCESS FULL | T1 | 87070 | 347 (1) | 00:00:05 |
-
14 rows selected.
SQL > @ coe_load_sql_profile_v2.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 12c0v4my7dvr3
PLAN_HASH_VALUE AVG_ET_SECS
--
1657298618. 141
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3724264953
Values passed to coe_xfr_sql_profile:
~ ~
SQL_ID: "12c0v4my7dvr3"
PLAN_HASH_VALUE: "3724264953"
SQL > BEGIN
2 IF: sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR (- 20100, 'SQL_TEXT for SQL_ID & & sql_id. Was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).')
4 END IF
5 END
6 /
SQL > SET TERM OFF
SQL > BEGIN
2 IF: other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR (- 20101, 'PLAN for SQL_ID & & sql_id. And PHV & & plan_hash_value. Was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).)
4 END IF
5 END
6 /
SQL > SET TERM OFF
Execute coe_xfr_sql_profile_12c0v4my7dvr3_3724264953.sql
On TARGET system in order to create a custom SQL Profile
With plan 3724264953 linked to adjusted sql_text.
. Omit
COE_XFR_SQL_PROFILE_12c0v4my7dvr3_3724264953 completed
COE_XFR_SQL_PROFILE completed.
SQL > explain plan for select count (*) from T1
Explained.
SQL > select * from table (dbms_xplan.display ())
PLAN_TABLE_OUTPUT
-
Plan hash value: 3724264953
-
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 347 (1) | 00:00:05 |
| | 1 | SORT AGGREGATE | | 1 |
| | 2 | TABLE ACCESS FULL | T1 | 87070 | 347 (1) | 00:00:05 |
-
Note
PLAN_TABLE_OUTPUT
-
-
-SQL profile "coe_12c0v4my7dvr3_3724264953" used for this statement
13 rows selected.
SQL > select name,category,status,sql_text from dba_sql_profiles
NAME CATEGORY STATUS SQL_TEXT
-
Coe_12c0v4my7dvr3_3724264953 DEFAULT ENABLED select count (*) from T1
You can see that the script coe_load_sql_profile_v2.sql fixes the execution plan.
