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

    Object access by session



      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...






        • 1. Re: Object access by session

          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





                           Av       Av     Av                       Av     Buffer  Av Buf

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

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