Home » SQL & PL/SQL » SQL & PL/SQL » how to get one week historical query for v$SQL
how to get one week historical query for v$SQL [message #652885] Tue, 21 June 2016 14:31 Go to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

Our management want every week all v$SQL information. Please help me how to get it.
Re: how to get one week historical query for v$SQL [message #652887 is a reply to message #652885] Tue, 21 June 2016 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT * FROM V$SQL;

Re: how to get one week historical query for v$SQL [message #652889 is a reply to message #652887] Tue, 21 June 2016 14:58 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
That would be a substantial report Smile

It would be better to collect the SQL over time (you can store it in a table) and present management with the top SQL (executed most, used the most CPU, disk reads, etc).

If you don't want to do it yourself, look at the SQL sections of the database's AWR / STATSPACK reports.
Re: how to get one week historical query for v$SQL [message #652891 is a reply to message #652889] Tue, 21 June 2016 15:22 Go to previous messageGo to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

Thanks for your all input
Re: how to get one week historical query for v$SQL [message #652924 is a reply to message #652889] Wed, 22 June 2016 06:34 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Frank Naude wrote on Tue, 21 June 2016 14:58
That would be a substantial report Smile

It would be better to collect the SQL over time (you can store it in a table) and present management with the top SQL (executed most, used the most CPU, disk reads, etc).



Even with that, I can't imagine what "management" thinks they are actually going to do with such a report.
But then, sometimes the solution to an unreasonable request is to give them exactly what they ask for . . .
Previous Topic: A Query for Seating Arrangement
Next Topic: Better query
Goto Forum:
  


Current Time: Fri Mar 29 08:15:10 CDT 2024