Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Oracle SQLTRPT Automation, Oracle SQLTRPT Automation
Ravi T
post Jan 5 2017, 01:17 PM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 5-January 17
Member No.: 51,729



Experts,
I know the Title or the logic which I am looking for looks stupid but this is what it is, my manager is looking for understanding the consequences. The Team here does not want to wait until the Application guys get back to us reg. poor performance or 1 hour of research to be wasted in identifying the execution plan changes. They want me to automate the SQLTRPT to run without DBA/User intervention. The logic I am looking for is

1) A query(shell/perl script) which monitors the database constantly for any long running sessions..if anything found to be hanging...script should kill its execution, identifies Hash Value and compares it with the Historic Hash values for the SQLID and report if any execution plan changes occur.
2) For any long running sessions or the execution plan changes for the SQL ID, run SQLTRPT to look for the recommendations and apply the recommendations(like stats gather, creating SQL Profile or baselining the execution plan) which provide 80 to 90 percent benefit(thinking that it wont harm the performance and store the applied recommendation output in logfile) without dba intervention

I am new to scripting shell/perl. It would be great if any script for this automation can be provided or the ideas are welcome.

Databases are 11g. Can we use 11G Automatic SQL Tuning Feature and make it run not just in maintenance window but every minute to identify the Long running jobs and tune them? Also, what are the bottlenecks to do this apart from the overload on the system. Can you please let me know the steps to enable automatic sql tuning along with automatic stats gathering and creating sql profiles and to monitor every minute.
Go to the top of the page
 
+Quote Post
burleson
post Jan 13 2017, 11:34 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,295
Joined: 26-January 04
Member No.: 13



Hi Ravi and welcome to the forum!

>> automate the SQLTRPT to run without DBA/User intervention

This may help:

http://www.rampant-books.com/book_0701_shell_scripting.htm

http://www.dba-oracle.com/t_v_sql_plan_monitor.htm

*********************************************************

>> 1) A query(shell/perl script) which monitors the database constantly for any long running sessions

No. You cannot tell if the script is hung or just busy . . .

*********************************************************

>> provide 80 to 90 percent benefit(thinking that it wont harm the performance and store the applied recommendation output in logfile) without dba intervention

No. The :automated tuning advisor is buggy and often makes DBA recommendations (e.g. building an index).

I would automate the generation of AWR reports instead:

http://www.dba-oracle.com/t_awr_automating..._generation.htm

Good Luck!









--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd January 2017 - 03:01 AM