(on) Additional Hints

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/noappend_hint.gif!|borderStyle=solid}
INSERT /*+ NOAPPEND */ INTO T1 (N1, N2, N3, N4) SELECT * FROM T2;

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      | 10000 |   136K|    10   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T2   | 10000 |   136K|    10   (0)| 00:00:01 |
---------------------------------------------------------------------------------
-- LOAD TABLE CONVENTIONAL : CONVENTIONAL-PATH

|

INSERT /*+ APPEND_VALUES */ INTO T1 (N1, N2, N3, N4) VALUES (-1, -1, -1, -1);

|

||CACHE||NOCACHE||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/cache_hint.gif!|borderStyle=solid}
SELECT /*+ FULL(T1) CACHE(T1) */ COUNT(*) FROM T1;
-- 읽은 블록을 LRU 리스트의 MRU END 위치에 보관

|

SELECT /*+ FULL(T1) NOCACHE(T1) */ COUNT(*) FROM T1;
-- 읽은 블록을 LRU 리스트의 LRU END 위치에 보관

|

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_push_pred_hint.gif!|borderStyle=solid}
SELECT /*+ NO_MERGE(X) USE_NL(T1 X) NO_PUSH_PRED(X) */ COUNT(*) FROM T1, (SELECT N4 FROM T2) X WHERE T1.N4 = X.N4(+) AND T1.N2 = 10; 

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    10 |    54   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |    10 |            |          |
|   2 |   NESTED LOOPS OUTER          |      |  5000 | 50000 |    54   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    10 |    70 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    VIEW                       |      |   500 |  1500 |     5   (0)| 00:00:01 |
|   6 |     INDEX FULL SCAN           | T2N4 | 10000 | 30000 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

|

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_push_subq_hint.gif!|borderStyle=solid}
SELECT COUNT(*) FROM T1 A, T4 B WHERE A.N4 = B.N4 AND EXISTS (SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */ 1 FROM T2 WHERE A.N4 = T2.N4 AND T2.N1 = 10);

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     6 |    15   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |     6 |            |          |
|*  2 |   FILTER                      |      |       |       |            |          |
|   3 |    NESTED LOOPS               |      |    10M|    57M|     5   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1N4 | 10000 | 30000 |     5   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS HASH         | T4   |  1000 |  3000 |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T2   |     1 |     7 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | T2PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

|

SELECT /*+ QB_NAME(QB) FULL(@QB1 T1) / COUNT() FROM T1 WHERE N1 = 10;

|

||CURSOR_SHARING_EXACT||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/cursor_sharing_exact_hint.gif!|borderStyle=solid}
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET CURSOR_SHARING = SIMILAR;


SELECT SQL_ID, EXECUTIONS, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT% FROM T1 %' AND SQL_TEXT NOT LIKE '%V$SQL%';

SQL_ID          EXECUTIONS SQL_TEXT
-------------   ---------- -------------------------------------------------------
2u436jdqsrw4d	2	   SELECT COUNT(*) FROM T1 WHERE N1 = :"SYS_B_0"


SELECT SQL_ID, EXECUTIONS, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT% FROM T1 %' AND SQL_TEXT NOT LIKE '%V$SQL%';

SQL_ID          EXECUTIONS SQL_TEXT
-------------   ---------- -------------------------------------------------------
2zdn2wy2dnad6	1	   SELECT /*+ CURSOR_SHARING_EXACT */ COUNT(*) FROM T1 WHERE N1 = 1
gtxtp70k5yrrt	1	   SELECT /*+ CURSOR_SHARING_EXACT */ COUNT(*) FROM T1 WHERE N1 = 2
2u436jdqsrw4d	2	   SELECT COUNT(*) FROM T1 WHERE N1 = :"SYS_B_0"

|

SELECT /*+ USE_NL(A B) DRIVING_SITE(B) / COUNT() FROM T1 A, T3@REMOTE_DB B WHERE A.N4 = B.N4 AND A.N2 = 10;

|

||DYNAMIC_SAMPLING||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/dynamic_sampling_hint.gif!|borderStyle=solid}
SELECT /*+ DYNAMIC_SAMPLING(T1 10) */ COUNT(*) FROM T1;

-- 통계 정보를 사용할 수 없을때 사용
-- http://ukja.tistory.com/112

|

SELECT /*+ RESULT_CACHE / T1.N4, COUNT()
FROM T1, T2
WHERE T1.N4 = T2.N4
GROUP BY T1.N4;

Statistics

SELECT /*+ RESULT_CACHE / T1.N4, COUNT()
FROM T1, T2
WHERE T1.N4 = T2.N4
GROUP BY T1.N4;

Statistics

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_result_cache_hint.gif!|borderStyle=solid}
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ NO_RESULT_CACHE */ T1.N4, COUNT(*)
  FROM T1, T2
 WHERE T1.N4 = T2.N4
 GROUP BY T1.N4;

Statistics
----------------------------------------------------------
       1167  recursive calls
          0  db block gets
        358  consistent gets
          0  physical reads
          0  redo size
         14  sorts (memory)
          0  sorts (disk)
         10  rows processed

SELECT /*+ NO_RESULT_CACHE */ T1.N4, COUNT(*)
  FROM T1, T2
 WHERE T1.N4 = T2.N4
 GROUP BY T1.N4;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         92  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

|

Note

• automatic DOP: Computed Degree of Parallelism is 2

-- 가용한 PARALLEL 프로세스가 존재할 때 까지 대기
– FIFO

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_statement_queuing_hint.gif!|borderStyle=solid}
SELECT /*+ PARALLEL NO_STATEMENT_QUEUING */ T1.N4, COUNT(*)
  FROM T1 FULL OUTER JOIN T2
    ON T1.N4 = T2.N4
 GROUP BY T1.N4;

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - statement not queuable: no-queuing hint specified

|

Outline Data

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(10)
OUTLINE_LEAF(@"SEL$5771D262")
TRANSFORM_DISTINCT_AGG(@"SEL$1")
OUTLINE_LEAF(@"SEL$C33C846D")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$5771D262")
TRANSFORM_DISTINCT_AGG(@"SEL$1")
NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
USE_HASH_AGGREGATION(@"SEL$C33C846D")
FULL(@"SEL$5771D262" "T1"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$5771D262")
END_OUTLINE_DATA
*/

– DISTINCT ROW
– T1.N1 : 10000
– T1.N2 : 1000
– T1.N3 : 100
– T1.N4 : 10

– QUERY TRANSFORMATION 결과 (10053 TRACE)
SELECT "VW_DAG_0"."ITEM_2" "N4"
,DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"),0)
,0,TO_NUMBER(NULL)
,SUM("VW_DAG_0"."ITEM_3")/NVL(SUM("VW_DAG_0"."ITEM_4"),0)) "AVG(N3)"
,COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTN2)"
FROM (SELECT "T1"."N2" "ITEM_1","T1"."N4" "ITEM_2", SUM("T1"."N3") "ITEM_3",COUNT(*) "ITEM_4"
FROM "UADMIN"."T1" "T1"
GROUP BY "T1"."N4","T1"."N2") "VW_DAG_0"
GROUP BY "VW_DAG_0"."ITEM_2"

|{code:sql|titleBGColor=#FFFFFF|title=NO_TRANSFORM_DISTINCT_AGG|borderStyle=solid}
SELECT /*+ NO_TRANSFORM_DISTINCT_AGG */ N4, AVG(N3), COUNT(DISTINCT N2) FROM T1 GROUP BY N4;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.02 |      31 |       |       |          |
|   1 |  SORT GROUP BY     |      |      1 |     10 |     10 |00:00:00.02 |      31 | 70656 | 70656 |63488  (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |  10000 |  10000 |00:00:00.01 |      31 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

|