December 1, 2009
In this OTN thread:
http://forums.oracle.com/forums/thread.jspa?messageID=3697025
A user asked the following question:
How would you explain the following
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='cmrh5mk9854vs' SNAP_ID SQL_ID PLAN_HASH_VALUE 13487 cmrh5mk9854vs 3314988613 14471 cmrh5mk9854vs 3452699090Two different hash values for the same same sqls
I believe that your real question is why does that happen? There are several reasons for the change in plan, here is a demonstration on Oracle 10.2.0.2:
CREATE TABLE T1 ( C1 VARCHAR2(20), C2 VARCHAR2(255), PRIMARY KEY (C1)); INSERT INTO T1 SELECT TO_CHAR(ROWNUM), LPAD(CHR(65 + ROUND(ROWNUM/1000)),255,CHR(65 + ROUND(ROWNUM/1000))) FROM DUAL CONNECT BY LEVEL<=1000000; COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
The table created by the above contains 1,000,000 rows with an index on column C1. Column C1 is defined as a VARCHAR2, but I placed numbers into the column. Now for the first part of the test, where I request that Oracle display the execution plan for a SQL statement without actually executing the SQL statement:
SET AUTOTRACE TRACEONLY EXPLAIN
VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
SELECT
C1,
C2
FROM
T1
WHERE
C1 BETWEEN :N1 AND :N2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3390613643
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 639K| 1001 (1)| 00:00:05 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2500 | 639K| 1001 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | SYS_C0020503 | 4500 | | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
3 - access("C1">=:N1 AND "C1"<=:N2)
VARIABLE N1 VARCHAR2(20)
VARIABLE N2 VARCHAR2(20)
EXEC :N1 := '1'
EXEC :N2 := '9999'
SELECT
C1,
C2
FROM
T1
WHERE
C1 BETWEEN :N1 AND :N2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3390613643
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 639K| 1001 (1)| 00:00:05 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2500 | 639K| 1001 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | SYS_C0020503 | 4500 | | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
3 - access("C1">=:N1 AND "C1"<=:N2)
The first execution plan displayed is incorrect. I have a utility (my Toy Project for Performance Tuning) which retrieves all plans for a SQL_ID using DBMS_XPLAN and also checks V$SQL_SHARED_CURSOR and V$SQL_BIND_METADATA. This is the output:
SQL_ID cguuvrry2hx1z, child number 0
-------------------------------------
SELECT C1, C2 FROM T1 WHERE C1 BETWEEN :N1 AND :N2
Plan hash value: 3332582666
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1970 (100)| | |
|* 1 | FILTER | | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 639K| 1970 (5)| 00:00:10 | CHK |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - filter((TO_NUMBER("C1")>=:N1 AND TO_NUMBER("C1")<=:N2))
Bind Descriptions:
Pos MLen Data Type Bind Name
1 22 NUMBER N1
2 22 NUMBER N2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BIND_MISMATCH - The bind metadata does not match the existing child cursor.
SQL_ID cguuvrry2hx1z, child number 1
-------------------------------------
SELECT C1, C2 FROM T1 WHERE C1 BETWEEN :N1 AND :N2
Plan hash value: 3390613643
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1001 (100)| | |
|* 1 | FILTER | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2500 | 639K| 1001 (1)| 00:00:05 | CHK |
|* 3 | INDEX RANGE SCAN | SYS_C0020503 | 4500 | | 20 (0)| 00:00:01 | CHK |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
3 - access("C1">=:N1 AND "C1"<=:N2)
Bind Descriptions:
Pos MLen Data Type Bind Name
1 32 VARCHAR2 N1
2 32 VARCHAR2 N2
Notice the Predicate Information and Bind Descriptions for the first execution plan. Child #1 was created due to “The bind metadata does not match the existing child cursor”. Now another test:
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1000; SELECT C1, C2 FROM T1 WHERE C1 BETWEEN :N1 AND :N2; Statistics -------------------------------------------------------------------------------- 1 recursive calls 0 db block gets 102765 consistent gets 36619 physical reads 0 redo size 22387973 bytes sent via SQL*Net to client 733583 bytes received via SQL*Net from client 66661 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999890 rows processed
This is the output from my utility program which shows another child cursor:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor.
SQL_ID cguuvrry2hx1z, child number 2
-------------------------------------
SELECT C1, C2 FROM T1 WHERE C1 BETWEEN :N1 AND :N2
Plan hash value: 3332582666
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1964 (100)| | |
|* 1 | FILTER | | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 999K| 249M| 1964 (5)| 00:00:10 | CHK |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - filter(("C1"<=:N2 AND "C1">=:N1))
Bind Descriptions:
Pos MLen Data Type Bind Name
1 32 VARCHAR2 N1
2 32 VARCHAR2 N2
Notice the reason for child #2 being created “The optimizer environment does not match the existing child cursor”.
I then disconnected from the database instance and reconnected to reset the OPTIMIZER_INDEX_COST_ADJ parameter to the system default. One more test, gather statistics forcing current plans to be invalidated (there is usually a delay when statistics are gathered before plans accessing those objects are invalidated):
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE, NO_INVALIDATE=>FALSE) SET AUTOTRACE TRACEONLY STATISTICS VARIABLE N1 VARCHAR2(20) VARIABLE N2 VARCHAR2(20) EXEC :N1 := '1' EXEC :N2 := '9999' SELECT C1, C2 FROM T1 WHERE C1 BETWEEN :N1 AND :N2; Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 102765 consistent gets 35424 physical reads 0 redo size 22387973 bytes sent via SQL*Net to client 733583 bytes received via SQL*Net from client 66661 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999890 rows processed
My utility program now shows that there is a single plan, which was affected by bind peeking.
SQL_ID cguuvrry2hx1z, child number 0
-------------------------------------
SELECT C1, C2 FROM T1 WHERE C1 BETWEEN :N1 AND :N2
Plan hash value: 3332582666
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1964 (100)| | |
|* 1 | FILTER | | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 999K| 249M| 1964 (5)| 00:00:10 | CHK |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - filter(("C1"<=:N2 AND "C1">=:N1))
Bind Descriptions:
Pos MLen Data Type Bind Name
1 32 VARCHAR2 N1
2 32 VARCHAR2 N2
- I had originally planned to mention the Oracle 11g introduced adaptive cursor sharing, which is another source of different plans for the same SQL statement. So, in summary some of the sources of the plan changing:
* Parsing a SQL statement with the bind variable values not defined
* Parsing a SQL statement with the bind variables defined as the wrong type (or specified as a different type)
* Statistics collection forcing the invalidation of cursors, which forces a new hard parse on the next execution (on 10g and above, statistics collection does not cause an immediate invalidation of cursors accessing objects for which statistics were collected)
* Changes in the statistics, or even lack of change in the statistics with supplied values in the SQL statement appearing to be increasingly above or below the maximum and minimum values for the columns.
* Bind peeking, where different bind variable values were specified on each hard parse
* Different optimizer environment for the session executing the SQL statement
* Adaptive cursor sharing in 11g
Charles,
I shall be taking some time to read all the posts. As usual, very impressive and useful so need time to grasp them all. Just wanted to give a feedback about the posts that at some places, the code is not coming properly. Its coming in a very light color, making it difficult to read.
Thanks so much once again for starting the blog, really so much happy!
regards
Aman….
Thanks for the feedback Aman. I am not yet sure that I like this WordPress template. It seems that a lot of the DBMS_XPLAN output will not fit. I am still trying to learn how to configure WordPress. With Internet Explorer 8 the code sections appear in a fairly large, black, Courier type font, at least on my computer.
Charles,
The theme which Jonathan uses over his blog, that’s a good one for the code posting. Also the one which I use over my blog is okay I believe but I am not sure that’s there for the wordpress.com . Try using the template of JL’s blog.
Also, please add an About section as well :-) .
Regards
Aman….
Its much better Charles :-) .
regards
Aman….
Aman, thanks for the feedback. Scrollable code windows were the missing element in the original blog theme.
Hi,
Sorry, can you please explain this. I am not able to reproduce this.
“Parsing a SQL statement with the bind variable values not defined”
I just declare a bind variable, but not assigning anything. So i thought everytime the statement with unassigned bind variable executes, different plan will be created. Can you help?
SQL> variable B3 number;
SQL> select * from t where col1=:B3;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 84312ruuhvvws, child number 0
————————————-
select * from t where col1=:B3
Plan hash value: 1835451365
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 100 (100)| |
|* 1 | INDEX RANGE SCAN| T1_IDX | 513 | 1539 | 100 (0)| 00:00:02 |
PLAN_TABLE_OUTPUT
——————————————————————————–
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“COL1″=:B3)
18 rows selected.
SQL> select * from t where col1=:B3;
no rows selected
SQL> select sql_text, sql_id,plan_hash_value,child_number from v$sql where SQL_ID=’84312ruuhvvws';
SQL_TEXT SQL_ID PLAN_HASH_VALUE CHILD_NUMBER
——————————- ————- ————— ————
select * from t where col1=:B3 84312ruuhvvws 1835451365 0
SQL> /
SQL_TEXT SQL_ID PLAN_HASH_VALUE CHILD_NUMBER
——————————- ————- ————— ————
select * from t where col1=:B3 84312ruuhvvws 1835451365 0