How are you doing? Long time. Hope all is well with you and family!
Glad to see your new site… miss the scripts I “borrow” from the site and looking forward to seeing them again! I am looking for the top 10 queries script you used to have for 11gr2. O’well dealing with a company with 2 node RAC with constant Cluster Reconfiguration… and yikes things take longer all the time so looking to identify and start tuning the top queries. Looking forward to your RMOUG 2013 presentations.
Ravi, Good to hear from you! Sorry my papers and scripts are off-line at the moment — I’m just doing a million things at once right now, including learning more about WordPress. I’ll try have the papers and scripts posted in a media-gallery widget over the next 2 weeks.
More soon…
Hi,
Very informative and helpful scripts you have posted here.
I am looking for trigger referred in a post (http://www.evdbt.com/trclvl12.ddl) which is helpful to find the column name in case of ora-01438, but not able to find it. Can you please share it.
looking forward to hear from you soon.
Sorry for breaking the link; I switched over from my old HTML-based website to the present one powered by WordPress back in November, and I’m slowly getting the new website re-constituted with all the stuff that was on the old. You should find the TRCLVL12 script available for download on this page now.
Good collection of scripts !!! I have not gone through all but few of them. Wants to know do you have any script or data for statistics ? or else if you can guide me about what is the optimal way to look for statistics then good for me.
Sonia,
There is my bare-bones “cbo_stats.sql” script posted here, but if you want the complete picture on statistics used by and relevant for a specific SQL statement, look toward SQLT by Carlos Sierra of Oracle and available for download from http://support.oracle.com.
Hope this helps…
-Tim
Could you please add the sphistory.sql script? In the comments of sqlhistory.sql the script is mentioned.
The AWR script has been really useful for me!
Hello Tim
Thank you for sharing useful scripts. I downloaded ash_xplan.sql and installed it successfully but i can’t get it working. Can you please help?
linux7-0vbox:SYS@GENP> select * from table(ash_xplan.display_cursor(‘3c1kubcdjnppq’,0,’TYPICAL’));
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “SYS.ASH_XPLAN”, line 17
ORA-06512: at “SYS.ASH_XPLAN”, line 375
ORA-06512: at line 1
no rows selected
————–
the sql_id exists in gv$active_session_history below is from plan_table_output
Hi Tim
Thank you for sharing the useful scripts. I donwloaded ash_xplan.sql and successfully installed. However, I can’t get it working as expected. Below is the errors reported
linux7-0vbox:SYS@GENP> select * from table(ash_xplan.display_cursor(‘3c1kubcdjnppq’,0,’TYPICAL’));
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “SYS.ASH_XPLAN”, line 17
ORA-06512: at “SYS.ASH_XPLAN”, line 375
ORA-06512: at line 1
Hey Tim –
How are you doing? Long time. Hope all is well with you and family!
Glad to see your new site… miss the scripts I “borrow” from the site and looking forward to seeing them again! I am looking for the top 10 queries script you used to have for 11gr2. O’well dealing with a company with 2 node RAC with constant Cluster Reconfiguration… and yikes things take longer all the time so looking to identify and start tuning the top queries. Looking forward to your RMOUG 2013 presentations.
Regards,
Ravi
Ravi, Good to hear from you! Sorry my papers and scripts are off-line at the moment — I’m just doing a million things at once right now, including learning more about WordPress. I’ll try have the papers and scripts posted in a media-gallery widget over the next 2 weeks.
More soon…
About time you upgraded 🙂
I agree. Thanks Vie!
TIM, EXCELLENT POST ….. YOUR SCRIPTS ARE VERY USEFULL TO LEARN AND TO RESEARCH IN MY JOB.
I’M FROM ARGENTINA
BEST REGARDS AND THANKS A LOT!
NICOLAS
Hi,
Very informative and helpful scripts you have posted here.
I am looking for trigger referred in a post (http://www.evdbt.com/trclvl12.ddl) which is helpful to find the column name in case of ora-01438, but not able to find it. Can you please share it.
looking forward to hear from you soon.
Regards,
Sahir Nadeem
Sorry for breaking the link; I switched over from my old HTML-based website to the present one powered by WordPress back in November, and I’m slowly getting the new website re-constituted with all the stuff that was on the old. You should find the TRCLVL12 script available for download on this page now.
Thanks!!!
Perhaps it is too much to ask, but would be nice to have the scripts in one zip file 🙂
Hi Ravi,
Good collection of scripts !!! I have not gone through all but few of them. Wants to know do you have any script or data for statistics ? or else if you can guide me about what is the optimal way to look for statistics then good for me.
Thanks, Sonia
Sonia,
There is my bare-bones “cbo_stats.sql” script posted here, but if you want the complete picture on statistics used by and relevant for a specific SQL statement, look toward SQLT by Carlos Sierra of Oracle and available for download from http://support.oracle.com.
Hope this helps…
-Tim
Hi Tim,
Thanks for your reply. From your reply I try to find on metalink but did not find “cbo_Stat.sql” in their search tool. can you guide me with link?
Thanks again.
Sonia
Sonia,
“cbo_stats.sql” is my script, available on this page. SQLT (or SQLTXPLAIN) is a package available from Oracle Support on their website.
Sorry for the confusion…
-Tim
Hi Tim,
no issues. Thanks again. will try to understand and implement your script in my test database.
Hi!
Could you please add the sphistory.sql script? In the comments of sqlhistory.sql the script is mentioned.
The AWR script has been really useful for me!
-Jari
Jari,
Posted. Thanks!
-Tim
Hi Tim,
I am also looking for the sphistory.sql file, but I couldn’t find it. Can you please share the URL as I couldn’t find it in http://evdbt.com/scripts/.
Thanks.
Kwok
Hello Tim
Thank you for sharing useful scripts. I downloaded ash_xplan.sql and installed it successfully but i can’t get it working. Can you please help?
linux7-0vbox:SYS@GENP> select * from table(ash_xplan.display_cursor(‘3c1kubcdjnppq’,0,’TYPICAL’));
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “SYS.ASH_XPLAN”, line 17
ORA-06512: at “SYS.ASH_XPLAN”, line 375
ORA-06512: at line 1
no rows selected
————–
the sql_id exists in gv$active_session_history below is from plan_table_output
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID 3c1kubcdjnppq, child number 0
————————————-
update sys.col_usage$ set equality_preds = equality_preds +
decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoin_preds
+ decode(bitand(:flag,2),0,0,1), nonequijoin_preds =
nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_preds
= range_preds + decode(bitand(:flag,8),0,0,1), like_preds
= like_preds + decode(bitand(:flag,16),0,0,1), null_preds
= null_preds + decode(bitand(:flag,32),0,0,1), timestamp =
:time where obj# = :objn and intcol# = :coln
Plan hash value: 477378112
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | COL_USAGE$ | | | | |
|* 2 | INDEX UNIQUE SCAN| I_COL_USAGE$ | 1 | 30 | 1 (0)| 00:00:01 |
———————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJ#”=:OBJN AND “INTCOL#”=:COLN)
Thank you.
Hi Tim
Thank you for sharing the useful scripts. I donwloaded ash_xplan.sql and successfully installed. However, I can’t get it working as expected. Below is the errors reported
linux7-0vbox:SYS@GENP> select * from table(ash_xplan.display_cursor(‘3c1kubcdjnppq’,0,’TYPICAL’));
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “SYS.ASH_XPLAN”, line 17
ORA-06512: at “SYS.ASH_XPLAN”, line 375
ORA-06512: at line 1
no rows selected
—–
The sql_id existed.
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID 3c1kubcdjnppq, child number 0
————————————-
update sys.col_usage$ set equality_preds = equality_preds +
decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoin_preds
+ decode(bitand(:flag,2),0,0,1), nonequijoin_preds =
nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_preds
= range_preds + decode(bitand(:flag,8),0,0,1), like_preds
= like_preds + decode(bitand(:flag,16),0,0,1), null_preds
= null_preds + decode(bitand(:flag,32),0,0,1), timestamp =
:time where obj# = :objn and intcol# = :coln
Plan hash value: 477378112
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | COL_USAGE$ | | | | |
|* 2 | INDEX UNIQUE SCAN| I_COL_USAGE$ | 1 | 30 | 1 (0)| 00:00:01 |
———————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJ#”=:OBJN AND “INTCOL#”=:COLN)