8 Replies Latest reply: Feb 13, 2013 8:00 AM by Jan-Marten Spit RSS

    Query to monitor sql consuming maximum resources

    monoj
      Dear All,


      Can any one help me with the query to monitor sql consuming machine resources and longest running query in my database.



      Thanks and Regards
      Monoj Das
        • 2. Re: Query to monitor sql consuming maximum resources
          ShishirTekadeR
          please confirm DB & OS version and also provide explain plan
          also check locking session on DB
          ================================================
          Best Regards,
          Shishir Tekade.
          My Blog: http://shishirtekade.blogspot.com
          • 3. Re: Query to monitor sql consuming maximum resources
            monoj
            Thank you for the reply.

            Database 10.2.0.4
            and
            OS:RedHat 4


            Regards
            Monoj
            • 4. Re: Query to monitor sql consuming maximum resources
              saratpvv
              1) The Best to find is .. Generate ADDM report for the desired time interval

              2) use the below query to find.

              select * from (select s.sid,s.serial#,s.osuser,s.username,p.spid,w.wait_time,w.SECONDS_IN_WAIT,w.event,w.state from
              (select sid,wait_time,SECONDS_IN_WAIT,event,state from v$session_wait) w,
              (select sid,serial#,osuser,username,paddr from v$session) s,(select spid,addr from v$process)p
              where s.sid=w.sid and s.paddr=p.addr order by w.SECONDS_IN_WAIT desc) where rownum <11;
              • 5. Re: Query to monitor sql consuming maximum resources
                Osama_Mustafa
                You don't need script just check OEM
                • 6. Re: Query to monitor sql consuming maximum resources
                  Moazzam
                  If you do not have OEM, then you can generate the report using:
                  @/$ORACLE_HOME/RDBMS/awrrpt.sql
                  or you can use the v$sql view or v$active_session_history to get required results e.g.
                  SELECT * FROM
                  (SELECT
                      sql_fulltext,
                      sql_id,
                      child_number,
                      disk_reads,
                      executions,
                      first_load_time,
                      last_load_time
                  FROM    v$sql
                  ORDER BY elapsed_time DESC)
                  WHERE ROWNUM < 10
                  -----------------------------------------
                  --
                  -- Top 10 CPU consumers in last 5 minutes
                  --
                  -----------------------------------------
                  SQL> select * from
                  (
                  select session_id, session_serial#, count(*)
                  from v$active_session_history
                  where session_state= 'ON CPU' and
                   sample_time > sysdate - interval '5' minute
                  group by session_id, session_serial#
                  order by count(*) desc
                  )
                  where rownum <= 10;
                  --------------------
                  --
                  -- Who is that SID?
                  --
                  --------------------
                  
                  set lines 200
                  col username for a10
                  col osuser for a10
                  col machine for a10
                  col program for a10
                  col resource_consumer_group for a10
                  col client_info for a10
                  
                  SQL> select  serial#,
                   username,
                   osuser,
                   machine,
                   program,
                   resource_consumer_group,
                   client_info
                  from v$session where sid=&sid;
                  
                  -------------------------
                  --
                  -- What did that SID do?
                  --
                  -------------------------
                  
                  SQL> select distinct sql_id, session_serial# from v$active_session_history
                  where sample_time >  sysdate - interval '5' minute
                  and session_id=&sid;
                  ----------------------------------------------
                  --
                  -- Retrieve the SQL from the Library Cache:
                  --
                  ----------------------------------------------
                  col sql_text for a80
                  SQL> select sql_text from v$sql where sql_id='&sqlid'
                  • 7. Re: Query to monitor sql consuming maximum resources
                    moreajays
                    Hi Manoj,

                    AWR would be the best tool to help you out on historical workload queries

                    To know real time TOP resource consuming sessions/sql's you need to get some OS scripts in place with top/topas/ps commands bind to it which will get TOP cpu/memory/io SPID & pass it to v$process, v$session , v$sqlarea and other performance views

                    Thanks,
                    Ajay More
                    http://www.moreajays.com
                    • 8. Re: Query to monitor sql consuming maximum resources
                      Jan-Marten Spit
                      "Can any one help me with the query to monitor sql consuming machine resources and longest running query in my database."

                      if you need to ask this question, i would be carefull calling it 'my' database, and i qould not encourage you to do anything with the output it produces until you are able to write this query yourself.