How to trace/diagnose complex hang( or very very slow ) query
Hi,
I have a huge query involving one partioned fact table and many dimensions table. Whenever I run this query , it hangs for hours and does not return the resultset ultimately.
I verified that All the where clause columns are indexed. I was wondering how can I diagnose the query issue from another DBA session?
Best Regards,
Anurag
It has the following explain plan :
1 Every row in the table PRODUCT_DIM is read.
2 The rows from step 1 were inserted into SYS.SYS_TEMP_0FDA01678_B83D0B07 using direct-load insert.
3 Every row in the table SYS.SYS_TEMP_0FDA01678_B83D0B07 is read.
I have a huge query involving one partioned fact table and many dimensions table. Whenever I run this query , it hangs for hours and does not return the resultset ultimately.
I verified that All the where clause columns are indexed. I was wondering how can I diagnose the query issue from another DBA session?
Best Regards,
Anurag
It has the following explain plan :
1 Every row in the table PRODUCT_DIM is read.
2 The rows from step 1 were inserted into SYS.SYS_TEMP_0FDA01678_B83D0B07 using direct-load insert.
3 Every row in the table SYS.SYS_TEMP_0FDA01678_B83D0B07 is read.
0