ID NAME TYPE ---------- ------------------------------ ------------------------------ 3167 DBA_TABLES VIEW 3168 DBA_TABLES SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('0xy0uj562r893','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID 0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 112 (100)| | |* 1 | TABLE ACCESS FULL| DH_SQL | 2 | 76 | 112 (0)| 00:07:50 | --已经固定的执行计划还是使用全表扫描 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:SYS_B_0) Note ----- - SQL profile PROF_0xy0uj562r893_1575588977 used for t
SQLProfile总结（四）--使用示例(五)
----------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:SYS_B_0) Note ----- - SQL profile PROF_0xy0uj562r893_1575588977 used for this statement --可以看到sql profile确实已经生效 22 rows selected. --新建一个索引，确认已经固定执行计划的语句不会因为访问路径而改变执行计划 SQL> create index ind_dh_sql2 on dh_sql(name) compute statistics; Index created. SQL> select /* test2 */ * from dh_sql a where name='DBA_TABLES'; ID NAME TYPE ---------- ------------------------------ ------------------------------ 3167 DBA_TABLES VIEW 3168 DBA_TABLES SYNONYM 2 rows selected. SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%test2%' and sql_text not like '%v$sql%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER ---------------------------------------------------------------------- ------------- ---------- ------------ select /* test2 */ * from dh_sql a where name=:"SYS_B_0" bp7gpwq6w88nv 2378441371 0 select /* test2 */ * from dh_sql where name=:"SYS_B_0" 0xy0uj562r893 1277927715 0 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('bp7gpwq6w88nv','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID bp7gpwq6w88nv, child number 0 ------------------------------------- select /* test2 */ * from dh_sql a where name=:"SYS_B_0" Plan hash value: 3828038811 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DH_SQL | 2 | 76 | 4 (0)| 00:00:17 | |* 2 | INDEX RANGE SCAN | IND_DH_SQL2 | 2 | | 3 (0)| 00:00:13 | --没有固定的语句使用索引计划 ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"=:SYS_B_0) 19 rows selected.
SQL> select /* test2 */ * from dh_sql where name='DBA_TABLES';
ID NAME TYPE ---------- ------------------------------ ------------------------------ 3167 DBA_TABLES VIEW 3168 DBA_TABLES SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('0xy0uj562r893','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID 0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 112 (100)| | |* 1 | TABLE ACCESS FULL| DH_SQL | 2 | 76 | 112 (0)| 00:07:50 | --已经固定的执行计划还是使用全表扫描 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:SYS_B_0) Note ----- - SQL profile PROF_0xy0uj562r893_1575588977 used for t
ID NAME TYPE ---------- ------------------------------ ------------------------------ 3167 DBA_TABLES VIEW 3168 DBA_TABLES SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor('0xy0uj562r893','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID 0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 112 (100)| | |* 1 | TABLE ACCESS FULL| DH_SQL | 2 | 76 | 112 (0)| 00:07:50 | --已经固定的执行计划还是使用全表扫描 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:SYS_B_0) Note ----- - SQL profile PROF_0xy0uj562r893_1575588977 used for t