This content has been marked as final. Show 17 replies
What you will do if the EM console bug present?
As a best pratice, i will do like below
1) execute the TOP command and you see process consuming high CPU
SQL> oradebug setospid <&OSPid>
SQL> oradebug tracefile_name --This will list the name of tracefile with location
SQL> oradebug unlimit
SQL> oradebug Event 10046 trace name context forever, level 12;
Let it run for 20 mins around
SQL> oradebug Event 10046 trace name context off;
3) Tkprof with explain plan
4) check any plan change for that sql
This is a query I use a lot to see what queries are using the most CPU:
select * from (select sql_text, cpu_time/1000000000 cpu_time, elapsed_time/1000000000 elapsed_time
from v$sqlarea order by cpu_time desc, disk_reads desc ) where rownum < 11
To check what PID, I use:
select s.sid || ',' || s.serial# "SID/SERIAL"
, p.spid "OS PID"
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
I hope it helps,
yes, through OEM grid control, i can achieve that.
but what i am trying to do is, every time the server load avg is high, the shell scrip will trigger to run a query again multiple databases, and append the report into one email, and send out.
I am only interested in the TOP SQL part. I can work out to send ASH report automatically, but that's too much information.
is there a good query we can use to get the TOP SQL? so i can insert into my script?
First I think the query has an error in that cpu_time in v$sqlarea is measured in microseconds which is one millionth of a second so I think the divisor should be 1,000,000 which is 3 zeroes less than what was posted.
Second, it would appear based on a simple test on my system that adding a where clause to the sub-query like "cpu_time > 1000000" to filter out SQL not of interest from the sort can aid performance.
HTH -- Mark D Powell --
Edited by: Mark D Powell on Oct 8, 2010 12:58 PM
thanks for all the help. still my question is not answered.
I see the view where ASH get info from is V$ACTIVE_SESSION_HISTORY. but ASH report is too big to attach in email, and contains too much information.
my question is/was how to get just SQL Text from V$ACTIVE_SESSION_HISTORY ? there has to be a query can be run against V$ACTIVE_SESSION_HISTORY to retrieve this information.
i need the past 30 mins TOP sql, exactly as what displayed on ASH report.
any idea/sql query is highly appreciated.