If you are an IT person, I am sure you know what I am talking about. You have a SQL statement or a program/job that you run every day and it completes in a few minutes but today it has been running for hours. Nothing has changed as far as you know. It is still supposed to process the similar amount of volume. No change in the code yet the SQL does not seem to be finishing.
The answer is simple. It's execution plan has changed. Your DBA should be able to confirm that or you could check it out yourself provided you know the hashvalue or the SQL_ID of the SQL in question.
Run the below SQL for the sql_id in question. Oracle optimizer generates a plan hash value for every execution plan it generates and executes. If you see different plan hash values for the same SQL hashvalue or SQL_ID, this will confirm that SQL statement plan has changed and hence it is running long.
select
inst_id,sql_id,plan_hash_value,child_number,executions,round(buffer_gets/executions,2)
"buffer_gets/exec" , cpu_time/1000000 "Cpu Time(s)",round(rows_processed/executions,2) "Rows",
elapsed_time/1000000 "Elpased Time(s)" from gv$sql where
sql_id='&sql_id'
Once you know that SQL plan did change, you would obviously want to know, what are the new and the old execution plans.
Run the below SQL Statement from the SQL_ID in question and for each child_cursor taken from above sql.
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',&child_cursor,'PEEKED_BINDS'))
The answer is simple. It's execution plan has changed. Your DBA should be able to confirm that or you could check it out yourself provided you know the hashvalue or the SQL_ID of the SQL in question.
Run the below SQL for the sql_id in question. Oracle optimizer generates a plan hash value for every execution plan it generates and executes. If you see different plan hash values for the same SQL hashvalue or SQL_ID, this will confirm that SQL statement plan has changed and hence it is running long.
select
inst_id,sql_id,plan_hash_value,child_number,executions,round(buffer_gets/executions,2)
"buffer_gets/exec" , cpu_time/1000000 "Cpu Time(s)",round(rows_processed/executions,2) "Rows",
elapsed_time/1000000 "Elpased Time(s)" from gv$sql where
sql_id='&sql_id'
Once you know that SQL plan did change, you would obviously want to know, what are the new and the old execution plans.
Run the below SQL Statement from the SQL_ID in question and for each child_cursor taken from above sql.
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',&child_cursor,'PEEKED_BINDS'))
Have a question? Please leave a comment and I would be glad to answer.
No comments:
Post a Comment