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
|
SQL> create table t2
2 (col1 number
3 ,col2 varchar2(50)
4 ,flag varchar2(2));
SQL> insert into t2
2 select rownum
3 , lpad('X',50,'X')
4 , case when rownum = 1
5 then 'Y1'
6 when rownum = 2
7 then 'Y2'
8 when mod(rownum,2) = 0
9 then 'N1'
10 else 'N2'
11 end
12 from dual
13 connect by rownum <= 100000;
100000 rows created.
SQL> create index i1 on t2 (flag);
Index created.
SQL> var n varchar2(2)
SQL> exec :n := 'Y1';
SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
SQL> select count(1) from t2 where flag = :n;
COUNT(1)
----------
1
SQL> select count(1) from dba_sql_plan_baselines where sql_text like '%t2%';
COUNT(1)
----------
0
SQL> select count(1) from t2 where flag = :n;
COUNT(1)
----------
1
SQL> select count(1) from dba_sql_plan_baselines where sql_text like '%t2%';
COUNT(1)
----------
1 -- faut executer deux fois avant de le voir être capturé
SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
SQL> select sql_handle, accepted from dba_sql_plan_baselines where sql_text like '%t2 where flag%';
SQL_HANDLE ACC
------------------------------ ---
SQL_38c7f32b9603f5d8 YES
SQL> select count(1) from t2 where flag = :n;
COUNT(1)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 9zr7y0b3d8h5m, child number 2
-------------------------------------
select count(1) from t2 where flag = :n
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| I1 | 9 | 27 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement
SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
SQL> select /*+ full (t2) */ count(1) from t2 where flag = :n;
COUNT(1)
----------
1
SQL> select /*+ full (t2) */ count(1) from t2 where flag = :n;
COUNT(1)
----------
1
SQL> select sql_handle, accepted from dba_sql_plan_baselines where sql_text like '%t2 where flag%';
SQL_HANDLE ACC
------------------------------ ---
SQL_38c7f32b9603f5d8 YES
SQL_d82a3703500d9194 YES
SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
SQL> select count(1) from t2 where flag = :n;
COUNT(1)
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 9zr7y0b3d8h5m, child number 2
-------------------------------------
select count(1) from t2 where flag = :n
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| I1 | 9 | 27 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement |
Partager