SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Stored procedures and other thoughts

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Stored procedures and other thoughts
Author Message
ssyed



Joined: 12 Jul 2007
Posts: 9
Country: United States

Post Stored procedures and other thoughts Reply with quote
Some questions that come to mind:
a) What kind of stored procedure editor/interface exists
b) Can I look at a particular time on a particular schema and see what queries were running
c) Can I see the explain plan of a query that has run or currently running
d) What is the performance of the tool itself .. ie... does it take long for it to bring up information or it is fast
e) What impact does istalling this tool have on the database which it is monitoring?
f) Can we run it on any db instance.

Please send a walk thru step by step answers.
Fri Jul 13, 2007 2:35 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Quote:
a) What kind of stored procedure editor/interface exists

I'm sorry I don't get this question. Can you re-phrase this or provide an example of what you are after?

Quote:
b) Can I look at a particular time on a particular schema and see what queries were running


Well queries are not attributes of any particular schema, Technically a single query can refer to many objects in many different schemas. Moreover a stored procedure created in schema A can access and update objects in schema B, C and D, when you run this procedure which schema you want?

From your previous posts, I assume you are looking to find queries that refer to objects in a particular schema. You can query tables populated by the DB Application Expert storing captured application queries and search for text containing direct references to a particular schema. The problem is what you are going to do when objects names don't have schema prefix or cross-schema aliases are used?

Quote:
c) Can I see the explain plan of a query that has run or currently running

Sure, this is available in many places, for example in DB Monitor go to Monitors/SQL Area; find the query you want than click File/Explain Plan menu. Another way, any place where you see an SQL statement you can click on the text; copy it to the clipboard (Edit/Copy menu) and then paste into DBA Notepad editor available in DB Monitor or into standalone SQL Editor and from there execute File/Explain Plan

Quote:
d) What is the performance of the tool itself .. ie... does it take long for it to bring up information or it is fast

It depends on what you are doing and how busy is your database. There is no single answer that can describe all possible situations.

Quote:
e) What impact does istalling this tool have on the database which it is monitoring?


If you install every available options and pick bad parameters, you are likely going to kill your database. You got to be careful about what you are doing. Most of the back-end packaged are provided for problem debugging and troubleshooting purposes. They should be used in a manner like; install.. run for some time to collect stats… find the problem cause; stop and disable the job running the installed package.

Quote:
f) Can we run it on any db instance.

Yes.
Fri Jul 13, 2007 2:58 pm View user's profile Send private message
ssyed



Joined: 12 Jul 2007
Posts: 9
Country: United States

Post Stored procedures and other thoughts Reply with quote
like forinstance yesterday 5:00pm what queries were running?
Can I see those?
Fri Jul 13, 2007 4:22 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
You can see yesterday's queries them if you enable auditing and turn on auditing option for capturing SQL queries. The results will be stored in SYS.AUD$ table. Please be aware that capturing all the SQL executed in a busy database requires a lot of disk space, typically several GB a day. So if you enable such thing, consider creating a job in DB Jobs to periodically purge old data from the SYS.AUD$, everything which is older then 2 or 3 days, for example DELETE FROM sys.aud$ WHERE timestamp$ < sysdate - 2

Note, that to enable time-based SQL auditing you can use either Oracle built-in AUDIT commands or use our DB Audit Expert product as a graphical console for Oracle auditing configuration. Please don't confuse this tool with the light version included in DB Tools package. The version in DB Tools doesn't support the described feature.
Fri Jul 13, 2007 5:20 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.