<<Oracle Version 별 SQL_ID(SQL_HASH_VALUE), PLAN_HASH_VALUE 확인 >>
결론 : DB Version 이 달라도, SQL_ID 및 PLAN_HASH_VALUE 가 같다.
( PLAN 이 변경되는 경우 빼곤 ㅇ.ㅇ )
<< RESULT>>
11.2.0.4
12.2.0.1 순서
테스트 쿼리 : SELECT /*+ TEST_TG_SQL_01 */ 'TEST GOGO ' FROM DUAL ;
SQL_ID PLAN_HASH_VALUE
------------- ---------------
au5t9g74cmpqj 1388734953
au5t9g74cmpqj 1388734953
테스트 쿼리 : select /*+ TEST_TG_SQL_02 full(a) */ count(*) from tg_container a ;
SQL_ID PLAN_HASH_VALUE
------------- ---------------
bt3072u533tmg 1346740893
bt3072u533tmg 1346740893
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | ---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- << TEST SQL >> CASE1 ) SELECT * FROM V$VERSION WHERE ROWNUM < 2; SELECT /*+ TEST_TG_SQL_01 */ 'TEST GOGO ' FROM DUAL ; SELECT A.SQL_ID, B.PLAN_HASH_VALUE, A.SQL_TEXT FROM ( SELECT /*+ NO_MERGE */ SQL_TEXT, SQL_ID FROM V$SQLAREA WHERE ( INSTR(SQL_TEXT,'TEST_TG_SQL_01',1,1) >= 1 ) ) A, V$SQL B WHERE A.SQL_ID = B.SQL_ID AND A.SQL_TEXT NOT LIKE '%V$SQLAREA%' ; ---------------------------------------------------------------------------------------------- CASE2 ) SELECT * FROM V$VERSION WHERE ROWNUM < 2; select /*+ TEST_TG_SQL_02 full(a) */ count(*) from cyy_container a ; SELECT A.SQL_ID, B.PLAN_HASH_VALUE, A.SQL_TEXT FROM ( SELECT /*+ NO_MERGE */ SQL_TEXT, SQL_ID FROM V$SQLAREA WHERE ( INSTR(SQL_TEXT,'TEST_TG_SQL_02',1,1) >= 1 ) ) A, V$SQL B WHERE A.SQL_ID = B.SQL_ID AND A.SQL_TEXT NOT LIKE '%V$SQLAREA%' ; ---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- <<CASE1 TEST >> ---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- <<11.2.0.4>> SQL> SQL> SELECT * FROM V$VERSION WHERE ROWNUM < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL> SQL> SELECT /*+ TEST_TG_SQL_01 */ 'TEST GOGO ' FROM DUAL ; 'TESTGOGO' ---------- TEST GOGO SQL> SELECT A.SQL_ID, B.PLAN_HASH_VALUE, A.SQL_TEXT 2 FROM 3 ( SELECT /*+ NO_MERGE */ SQL_TEXT, SQL_ID 4 FROM V$SQLAREA 5 WHERE ( INSTR(SQL_TEXT,'TEST_TG_SQL_01',1,1) >= 1 ) 6 ) A, V$SQL B 7 WHERE A.SQL_ID = B.SQL_ID 8 AND A.SQL_TEXT NOT LIKE '%V$SQLAREA%' ; SQL_ID PLAN_HASH_VALUE ------------- --------------- SQL_TEXT -------------------------------------------------------------------------------- au5t9g74cmpqj 1388734953 SELECT /*+ TEST_TG_SQL_01 */ 'TEST GOGO ' FROM DUAL SQL> ---------------------------------------------------------------------------------------------- << 12.2.0.1>> SQL> SELECT * FROM V$VERSION WHERE ROWNUM < 2; BANNER -------------------------------------------------------------------------------- CON_ID ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SQL> SQL> SELECT /*+ TEST_TG_SQL_01 */ 'TEST GOGO ' FROM DUAL ; 'TESTGOGO' ---------- TEST GOGO SQL> SQL> SELECT A.SQL_ID, B.PLAN_HASH_VALUE, A.SQL_TEXT 2 FROM 3 ( SELECT /*+ NO_MERGE */ SQL_TEXT, SQL_ID 4 FROM V$SQLAREA 5 WHERE ( INSTR(SQL_TEXT,'TEST_TG_SQL_01',1,1) >= 1 ) 6 ) A, V$SQL B 7 WHERE A.SQL_ID = B.SQL_ID 8 AND A.SQL_TEXT NOT LIKE '%V$SQLAREA%' ; SQL_ID PLAN_HASH_VALUE ------------- --------------- SQL_TEXT -------------------------------------------------------------------------------- au5t9g74cmpqj 1388734953 SELECT /*+ TEST_TG_SQL_01 */ 'TEST GOGO ' FROM DUAL SQL> ---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- <<CASE2 TEST >> ---------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- << 11.2.0.4 >> ---------------------------------------------------------------------------------------------- SQL> SELECT * FROM V$VERSION WHERE ROWNUM < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL> SQL> select /*+ TEST_TG_SQL_02 full(a) */ count(*) from tg_container a ; COUNT(*) ---------- 9576 SQL> SQL> SELECT A.SQL_ID, B.PLAN_HASH_VALUE, A.SQL_TEXT 2 FROM 3 ( SELECT /*+ NO_MERGE */ SQL_TEXT, SQL_ID 4 FROM V$SQLAREA 5 WHERE ( INSTR(SQL_TEXT,'TEST_TG_SQL_02',1,1) >= 1 ) 6 ) A, V$SQL B 7 WHERE A.SQL_ID = B.SQL_ID 8 AND A.SQL_TEXT NOT LIKE '%V$SQLAREA%' ; SQL_ID PLAN_HASH_VALUE ------------- --------------- SQL_TEXT -------------------------------------------------------------------------------- bt3072u533tmg 1346740893 select /*+ TEST_TG_SQL_02 full(a) */ count(*) from tg_container a SQL> << 12.2.0.1 >> SQL> SQL> SELECT * FROM V$VERSION WHERE ROWNUM < 2; BANNER -------------------------------------------------------------------------------- CON_ID ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SQL> SQL> select /*+ TEST_TG_SQL_02 full(a) */ count(*) from tg_container a ; COUNT(*) ---------- 2521 SQL> SQL> SELECT A.SQL_ID, B.PLAN_HASH_VALUE, A.SQL_TEXT 2 FROM 3 ( SELECT /*+ NO_MERGE */ SQL_TEXT, SQL_ID 4 FROM V$SQLAREA 5 WHERE ( INSTR(SQL_TEXT,'TEST_TG_SQL_02',1,1) >= 1 ) 6 ) A, V$SQL B 7 WHERE A.SQL_ID = B.SQL_ID 8 AND A.SQL_TEXT NOT LIKE '%V$SQLAREA%' ; SQL_ID PLAN_HASH_VALUE ------------- --------------- SQL_TEXT -------------------------------------------------------------------------------- bt3072u533tmg 1346740893 select /*+ TEST_TG_SQL_02 full(a) */ count(*) from tg_container a SQL> | cs |