DBMS_XPLAN and V$SQLSTAT showing different plan hash value for same SQL ID - 10.2.0.4 database
edited May 18, 2011 6:26PM in Database ORA-600 / ORA-7445 Analysis , Diagnostic Repository (ADR) & Packaging (MOSC) 5 commentsAnswered
Hi,
There is a query in our application which we are working on fixing.Database is 10.2.0.4
We queried the v$sql to retrieve the SQL_ID for that sQL
SQL> select sql_id,sql_text from v$sql where sql_text like 'select SUM(il.charge_amt) totcharge from%' ;
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
8xq6axhzh9c3x
select SUM(il.charge_amt) totcharge from INVOICE_LINES il where ex
ih.FILE_NO=il.file_no and ih.SUFFIX=il.SUFFIX and ih.INVOICE_PREPARED!='Y' and ih.BILL_TO_CUST=:1 ) and il.CHARGE=99
We then tried to retrieve the execution plan of that SQL using DBMS_XPLAN as below. As you can see, it reported the plan_hash_value to be "3230287910"
0