Re: SQLs run in any period

You could try my scripts awr_topsql and awr_topsqlx which I've uploaded on this link http://karlarao.wordpress.com/scripts-resources/

The default for these scripts is get the top 5 SQLs across SNAP_IDs and "order by" the top 20 according to the total elapsed time.. if you "order by" SNAP_ID you'll get the same output as the AWR reports you've generated manually using awrrpt.sql across SNAP_IDs you could check it by comparing the output..

So this makes the task of searching for top SQL easier.. plus, I've added some metrics to have better view/info of that top SQL..

here are the info/sections you'll get from the script (& some short description):

1. - snap_id, time, instance, snap duration # The time period and snap_id could be used to show the SQLs for a given workload period..let's say you usual work hours is 9-6pm, you could just show the particular SQLs on that period.. there's a data range section on the bottom of the script you could make use of it if you want to filter.
2. - sql_id, plan_hash_value, module # You could make use of this info if you want to know where the SQL was executed (SQL*Plus, OWB, Toad, etc.).. plus you could compare the plan_hash_value but I suggest you make use of Kerry Osborne's awr_plan_change.sql script if you'd like to search for unstable plans.
3. - total elapsed time, elapsed time per exec
   - cpu time
   
   • io time
   • app wait time
   • concurrency wait time
   • cluster wait time # These are the time info.. at least without tracing the SQL you'd know what time component is consuming the elapsed time of that particular SQL.. so let's say your total elapsed time is 1000sec, and cpu time of 30sec, and io time of 300sec... you would know that it is consuming significant IO but you have to look for the other 670sec which could be attributed by "other" wait events (like PX Deq Credit: send blkd,etc,etc)
4. - LIOs
   - PIOs
   
   • direct writes
   • rows
   • executions
   • parse count
   • PX # Some other statistics about the SQL.. if your incurring a lot of PIOs, how many times this SQL was executed on that period, the # of PX spawed.. just be careful about these numbers if you have "executions" of let's say 8.. you have to divide these values to 8 as well as on the time section.. only the "elapsed time per exec" is the per execution value.. this is for formatting reasons I can't fit them all on my screen.. :p
5. - AAS (Average Active Sessions)
   - Time Rank
   
   • SQL type, SQL text # This is one of my favorites... this will measure how's the SQL is performing against my database server.. I'm using the AAS & CPU count as my yardstick for a possible performance problem (I suggest reading Kyle's stuff about this): if AAS < 1
     • Database is not blocked AAS ~= 0
     • Database basically idle
     • Problems are in the APP not DB AAS < # of CPUs
     • CPU available
     • Database is probably not blocked
     • Are any single sessions 100% active? AAS > # of CPUs
     • Could have performance problems AAS >> # of CPUS
     • There is a bottleneck so having the AAS as another metric on the TOP SQL is good stuff.. I've also added the "time rank" column to know what is the SQLs ranking on the top SQL.. normally the default settings of the script will show time rank 1 and 2.. this could be useful also if you are finding a particular SQL that is on rank #15 and you are seeing that there's an adhoc query that is time rank #1 and #2 affecting the database performance..

And.... this script could also show SQLs that span across SNAP_IDs... I would order the output by SNAP_ID and filter on that particular SQL then you would see that if the SQL is still running and span across let's say 2 SNAP_IDs then the exec count would be 0 (zero) and elapsed time per exec is 0 (zero).. only the time when the query is finished you'll see these values populated.. I've noticed this behavior and it's the same thing that is shown on the AWR reports.. you could go here for that scenario http://karlarao.tiddlyspot.com/#%5B%5BTopSQL%20on%20AWR%5D%5D

-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com

--
http://www.freelists.org/webpage/oracle-l