1 Reply Latest reply on Apr 28, 2015 10:53 AM by Cobert

    Object access by session

    1486048

      Hi!

      Is there any way to figure out what sql causes large amount of data read accessing objects in a specific tablespace?

       

      When looking in "Windows Resource Monitor", one of our tablespaces have a normal read of about ~30-50MB/s (as it should be).

       

      But every now and then, this peaks to 100-300 MB/s for a minute or two.

       

      And just curios what causes this, if there is any full table scans, badly written SQL statements etc.

       

      So I want to find out which sessions (preferable sql) has large read when accessing objects in a specific tablespace.

      Many sessions access multiple tablespaces and can have large amount of read data in other tablespaces, so can't just filter on session_reads...

       

       

      Regards

       

      Richard

        • 1. Re: Object access by session
          Cobert

          Hi Richard,

          Have you checked your awr report (or if not using tuning and diagnostics pack a lot of the data will be in your statspack report).

          There is a section on SQL by User I/O wait time, also for tablespaces further down in the report a section on tablespace IO statistics:

           

          -> ordered by IOs (Reads + Writes) desc

           

           

          Tablespace

          ------------------------------

                           Av       Av     Av                       Av     Buffer  Av Buf

                   Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)

          -------------- ------- ------- ------- ------------ -------- ---------- -------