Shulou(Shulou.com)06/01 Report--

APPEDND hint: used to control whether insert statements can insert data in a direct path insert.

CACHE hint: used to control whether the target sql puts the data blocks of the full table scan target table on the hot end of the LRU linked list of buffer cache when executing.

MONITER hint: used to control whether the target sql being executed is monitored by sql monitor

Gather_plan_statistics hint: used to collect some additional statistics when the target sql executes:

SQL > select / * + gather_plan_statistics * / t1.empno from table t1.Ename t2.dname from emp T1 jurisdiction dept t2 where t1.deptnosanct2.deptno; EMPNO ENAME DNAME- 7782 CLARK ACCOUNTING,14 rows selected.SQL > select * from table (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT- -SQL_ID4m81jub7yju91 Child number 0--select / * + gather_plan_statistics * / t1.empnocharge t1.enamejournal t2.dname fromemp T1 Dept T2 where t1.deptno=t2.deptnoPlan hash value: 844388907- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | | 14 | 00 00.01 | 10 | | | 1 | MERGE JOIN | | 1 | 14 | 14 | 00: | 00 INDEX FULL SCAN 00.01 | 10 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 4 | 4 | 4 | 00 INDEX FULL SCAN 00.01 | 4 | | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 | 00 INDEX FULL SCAN 00.01 | 2 | | * 4 | SORT JOIN | 4 | 14 | 14 | 00:00:00 | | .01 | 6 | 2048 | 2048 | 2048 (0) | | 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 | 00VLAR 00.01 | 6 | |-- | -Predicate Information (identified by operation id):- -4-access ("T1". "DEPTNO" = "T2". "DEPTNO") filter ("T1". "DEPTNO" = "T2". "DEPTNO") 24 rows selected.

Without hint, you can't see a similar execution plan for starts above:

SQL > select / * + gather_plan_statistics * / t1.empnorecy t1.Enamefrom table t2.dname from emp T1 coach dept T2 where t1.deptnosanct2.deptno; EMPNO ENAME DNAME- 7782 CLARK ACCOUNTING, 14 rows selected.SQL > select * from table (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT- -SQL_ID4m81jub7yju91 Child number 0--select / * + gather_plan_statistics * / t1.empnocharge t1.enamejournal t2.dname fromemp T1 Dept T2 where t1.deptno=t2.deptnoPlan hash value: 844388907- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | | 14 | 00 00.01 | 10 | | | 1 | MERGE JOIN | | 1 | 14 | 14 | 00: | 00 INDEX FULL SCAN 00.01 | 10 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 4 | 4 | 4 | 00 INDEX FULL SCAN 00.01 | 4 | | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 | 00 INDEX FULL SCAN 00.01 | 2 | | * 4 | SORT JOIN | 4 | 14 | 14 | 00:00:00 | | .01 | 6 | 2048 | 2048 | 2048 (0) | | 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 | 00VLAR 00.01 | 6 | |-- | -Predicate Information (identified by operation id):- -4-access ("T1". "DEPTNO" = "T2". "DEPTNO") filter ("T1". "DEPTNO" = "T2". "DEPTNO") 24 rows selected.SQL > select / * + full (scott.emp) * / * from scott.emp where empno=7369 -- incorrect writing SQL > select / * + full (emp) * / * from scott.emp where empno=7369;-correct writing SQL > select / * + full (T1) * / * from scott.emp T1 where empno=7369;-- alias specified in HINT, otherwise invalid

The scope of entry into force for query block,hint is limited to its own.

SQL > select / * + full (T1) * / t 1.deptno from emp T1 where t1.deptno in (select / * + full (T2) * / t2.deptno from dept T2 where T2 where t2.localized CHICAGO'); SQL > select / * + full (T1) full (T2) * / t1.enamePowert1.deptno from T1 where t1.deptno in (select t2.deptno from dept T2 where t2.localized recording CHICAGO');-- this HINT is not valid for T2 table

Query block appears in HINT and must be in the format @ query block name.

Method 1:

SQL > select / * + full (@ sel$1 T1) full (@ sel$2 T2) * / t1.enamelightt1.deptno from emp T1 where t1.deptno in (select t2.deptno from dept T2 where t2.localizedCHICAGO')

Method 2:

SQL > select / * + full (t1@sel$1) full (t2@sel$2) * / t1.EnamePowert1.deptno from emp T1 where t1.deptno in (select t2.deptno from dept T2 where t2.localizedCHICAGO')

Method 3: (custom qb_name)

SQL > select / * + full (t1@sel$1) full (@ llc T2) * / t1.enamet2.deptno from dept T1 where t1.deptno in (select / * + qb_name (llc) * / t2.deptno from dept T2 where T2 where t2.localizedCHICAGO'); SQL > select / * + full (t1@sel$1) full (t2@llc) * / t1.enamestamentt1.deptno from emp T1 where t1.deptno in (select / * + qb_name (llc) * / t2.deptno from dept T2 where t2.lockedCHICAGO') SQL > set autot off;SQL > select t1.enameJournal t1.deptno from emp T1 where t1.deptno in (select t2.deptno from dept T2 where t2.localized languages CHICAGO'); ENAME DEPTNO--ALLEN 30WARD 30MARTIN 30BLAKE 30TURNER 30JAMES 30ALLEN 30WARD 3024 rows selected.SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced')) PLAN_TABLE_OUTPUT- -SQL_ID3v4x69w2mvqgs Child number 0--select t1.ename T1.deptno from T1 where t1.deptno in (select t2.deptnofrom dept T2 where T2 localizedCHICAGO') Plan hash value: 2392421419- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | | 0 | SELECT STATEMENT | | 5 | HASH JOIN | | 19 | 380 | 5 (0) | 00:00:01 | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) | 00:00:01 | | * 3 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | 1 (0) | 00:00:01 | 4 | TABLE ACCESS FULL | T1 | | | 56 | 504 | 3 (0) | 00:00:01 |-Query Block Name / Object Alias (identified by operation) | Id):-1-SEL$5DA710D3 2-SEL$5DA710D3 / T2@SEL$2 3-SEL$5DA710D3 / T2@SEL$2 4-SEL$5DA710D3 / T1@SEL$1Outline Data- / * + BEGIN_OUTLINE_DATA IGNORE _ OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('11.2.0.4') DB_VERSION ('11.2.0.4') ALL_ROWS OUTLINE_LEAF (@ "SEL$5DA710D3") UNNEST (@ "SEL$2") OUTLINE (@ "SEL$1") OUTLINE (@ "SEL$2") INDEX_RS_ASC (@ "SEL$5DA710D3"T2" @ "SEL$2" ("DEPT". "LOC")) FULL (@ "SEL$5DA710D3"T1" @ "SEL$1") LEADING (@ "SEL$5DA710D3"T2" @ "SEL$2"T1" @ "SEL$1") USE_HASH (@ "SEL$5DA710D3"T1" @ "SEL$1") END_OUTLINE_DATA * / Predicate Information (identified by operation id):-- -1-access ("T1". "DEPTNO" = "T2". "DEPTNO") 3-access ("T2". "LOC" = 'CHICAGO') Column Projection Information (identified by operation id): -- 1-(# keys=1) "T1". "DEPTNO" [NUMBER 22], "T1". "ENAME" [VARCHAR2,10] 2-"T2". "DEPTNO" [NUMBER,22] 3-"T2" .ROWID [ROWID,10] 4-"T1". "ENAME" [VARCHAR2,10], "T1". "DEPTNO" [NUMBER,22] 59 rows selected.

In the above execution plan: T2@SEL$2 and T1@SEL$1 query block, and SEL$5DA710D3 is a new query block formed by a query transformation (including subquery expansion, view merge, and join predicate push).

Outline data, an internal hint combination used to fix the execution plan, is a very comprehensive combination that is more reliable than the general hint:

Build an index on emp deptno and let sql take NL:

SQL > select t1.enamewhere t1.deptno in t1.deptno from emp T1 where t1.deptno in (select t2.deptno from dept T2 where t2.localized recording CHICAGO') 6 rows selected.Execution Plan---Plan hash value: 902326130 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 5 | 100 | 3 (0) | 00:00:01 | | 1 | NESTED LOOPS | | 5 | 100 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 5 | 100 | 3 | (0) | 00:00:01 | 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) | 00:00:01 | * 4 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1 (0) | 00:00:01 | * 5 | INDEX RANGE SCAN | IDX_EMP_DEPT | 5 | 0 | 00:00:01 | 6 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 45 | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-4-access ("T2". "LOC" = 'CHICAGO') 5-access ("T1". "DEPTNO" = "T2". "DEPTNO") Statistics- -- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size714 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed

If you add the hash connection outline data to the hint, you will not use the new index and go out of the hash connection:

Select / * + BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('11.2.0.4') DB_VERSION ('11.2.0.4') ALL_ROWS OUTLINE_LEAF (@ "SEL$5DA710D3") UNNEST (@ "SEL$2") OUTLINE (@ "SEL$1") OUTLINE (@ "SEL$2") INDEX_RS_ASC (@ "SEL$5DA710D3"T2" @ "SEL$2" ("DEPT". "LOC") FULL (@ "SEL$5DA710D3"T1" @ "SEL$1") LEADING (@ "SEL$5DA710D3"T2" @ "SEL$2"T1" @ "SEL$1") USE_HASH (@ "SEL$5DA710D3"T1" @ "SEL$1") END_OUTLINE_DATA 16 * / t1.ename T1.deptno from emp T1 where t1.deptno in (select t2.deptno from dept T2 where T2. Localizable CHICAGO') 6 rows selected.Execution Plan---Plan hash value: 2711458306 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 5 | 100 | 5 (0) | 00:00:01 | | * 1 | HASH JOIN | | 5 | 100 | 5 (0) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) | 00:00:01 | * | 3 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1 (0) | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0) | 00:00:01 |-- -- Predicate Information (identified by operation id):-- 1-access ("T1". "DEPTNO" = "T2". "DEPTNO") 3-access (" T2 "." LOC "= 'CHICAGO') Statistics--- 1 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size714 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed

All hint is determined by _ optimizer_ignore_hints (system or session level), default false, do not ignore hint, set to ture will ignore all hint.

SQL > alter system set "_ optimizer_ignore_hints" = true;System altered.SQL > select / * + full (emp) * / * from emp where empno=7369 Execution Plan---Plan hash value: 2949544139 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |- -Predicate Information (identified by operation id):- -2-access ("EMPNO" = 7369) Statistics--- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size889 bytes sent via SQL*Net to client512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.