In the previous blog we have seen Cardinality Feedback in Oracle 11.2.0.1, where the reason for hard parse during second run was not tangible. Oracle overcomes this in 11.2.0.3 with an additional column named USE_FEEDBACK_STATS in v$sql_shared_cursor.
Have repeated the same test case in Oracle 11.2.0.3, lets review it
SQL> set linesize 100
SQL> set timing on
SQL>
SQL> create table tblt1 (id number,id1 number,id_val varchar2(10),id_val1 varchar2(10));
Table created.
Elapsed: 00:00:00.02
SQL>
SQL> insert into tblt1
2 select round(dbms_random.value(4,10000)) id,
3 rownum id1,
4 round(dbms_random.value(-100,40)) id_val,
5 round(dbms_random.value(4,10000)) id_val1
6 from dual connect by level < 10000;
9999 rows created.
Elapsed: 00:00:00.54
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> begin
2 dbms_stats.gather_table_stats('HARI','TBLT1');
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
SQL> select * from tblt1 where id_val1 > 18 and id < 99999;
ID ID1 ID_VAL ID_VAL1
---------- ---------- ---------- ----------
9917 694 28 4162
7489 695 -40 6788
3705 696 -80 2162
..
..
.....
9983 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 80tf1dj5fhu58, child number 0
-------------------------------------
select * from tblt1 where id_val1 > 18 and id < 99999
Plan hash value: 1166073720
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | TABLE ACCESS FULL| TBLT1 | 500 | 8500 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TBLT1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999 data-blogger-escaped------------------------------------------------------------="" data-blogger-escaped--="" data-blogger-escaped-00:00:00.09="" data-blogger-escaped-1="" data-blogger-escaped-29="" data-blogger-escaped-by="" data-blogger-escaped-column="" data-blogger-escaped-elapsed:="" data-blogger-escaped-identified="" data-blogger-escaped-information="" data-blogger-escaped-operation="" data-blogger-escaped-pre="" data-blogger-escaped-projection="" data-blogger-escaped-rows="" data-blogger-escaped-selected.="" id="">
Got the same plan as we got in 11.2.0.1, now lets check v$sql_shared_cursor
SQL> column sql_id format a20 SQL> column child_number format 99 SQL> column use_feedback_stats format a20 SQL> SQL> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id = '80tf1dj5fhu58'; SQL_ID CHILD_NUMBER USE_FEEDBACK_STATS -------------------- ------------ -------------------- 80tf1dj5fhu58 0 Y Elapsed: 00:00:00.02
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 80tf1dj5fhu58, child number 1
-------------------------------------
select * from tblt1 where id_val1 > 18 and id < 99999
Plan hash value: 1166073720
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | TABLE ACCESS FULL| TBLT1 | 9983 | 165K| 11 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TBLT1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((TO_NUMBER("ID_VAL1")>18 AND "ID"<99999))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "TBLT1"."ID1"[NUMBER,22],
"TBLT1"."ID_VAL"[VARCHAR2,10], "ID_VAL1"[VARCHAR2,10]
Note
-----
- cardinality feedback used for this statement
33 rows selected.
Elapsed: 00:00:00.08
SQL> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id = '80tf1dj5fhu58'; SQL_ID CHILD_NUMBER USE_FEEDBACK_STATS -------------------- ------------ -------------------- 80tf1dj5fhu58 0 Y 80tf1dj5fhu58 1 N Elapsed: 00:00:00.00
No comments:
Post a Comment