This content has been marked as final. Show 8 replies
thanks for the reply Antonio.
1. Let me rephrase my understanding based on your answer: plan hash value can be same for different query and if there is difference in the execution plan then oracle will generate child hash number. Is that correct? But one question: if plan hash value is same then how Oracle decides which hash plan belongs to which query? Could you please elaborate it a bit more.
2. I did not got your second point. Let say I have one query SELECT /*+ ORDERED */ EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO
and SELECT EMPNO,ENAME,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO and execution plan for both of these queries are same except for the no of rows and bytes scanned are different then which query is better? I mean if rows scanned in case of query with hint is say 10 and no of rows scanned in case of query without hint is 100 then which one is better i.e. the one with hint or without hint?
They will be simply distinguished with their child numbers so the plan hash value can surely be same ... you should see why a seperate child cursor was created means the reason for a mismatch in select sql_text,sql_id from v$sql where hash_value = '3535363636'; select * from v$sql_shared_cursor where sql_id = 'sgfsd42342'; Above your statements look similar and for plan hash value calculation anyway object_id is not used.. its the text which matters
there are two different things. One is EXPLAIN PLAN, which is how the optimizer thinks the query will be executed. It contains some estimates of cost, cardinalities etc. There is also EXECUTION PLAN. It also contains all this information regarding the optimizer estimates, but on the top of that, it also contains information about actual I/O incurred, actual cardinalities, actual timings etc.
So if a hint is changing optimizer estimates, but the plan stays the same, then impact on query's performance is zero.
If the actual numbers are changing, this is probably also irrelevant to the hint (e.g. you can have less physical reads because more blocks are found in the buffer cache the second time you're running the query, or you less work because you don't have to parse the statement etc.).
Actually, most of optimizer hints don't affect optimizer estimates; rather, they try to get the optimizer to use a certain access method or a certain join order etc. regardless of the cost. So you must be talking about such hints as cardinality, dynamic_sampling etc. If that's not the case -- please clarify, because this means that something wrong is going on here (e.g. an INDEX hint may work or it may fail to work, but if it fails, optimizer estimates shouldn't change).
1. Can two queries have same plan hash value?
Yes - as already answered - but it isn't just due to hints.
Here is example code I use to show that Oracle does NOT simply use a text version of the query you submit and compute a hash from it.
These two queries look radically different but have the same hash value; to Oracle they are not different at all.
set serveroutput on set autotrace traceonly select * from DEPT A, EMP B where A.DEPTNO=B.DEPTNO SQL> select * from DEPT A, EMP B 2 where A.DEPTNO=B.DEPTNO 3 / 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 select * from (select * from DEPT) A, (select * from EMP) B WHERE A.DEPTNO = B.DEPTNO SQL> select * from 2 (select * from DEPT) A, (select * from EMP) B 3 WHERE A.DEPTNO = B.DEPTNO 4 / 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907