Home » RDBMS Server » Performance Tuning » I/O issue (11g r2 HP-UX)
I/O issue [message #533461] Wed, 30 November 2011 02:26 Go to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hi Expert,

Last days, user complaining for sudden slow response. When checked number of session waiting with I/O wait event. Please have a look to the output and suggest what to investigate further.

Thanks

SQL> SELECT s.inst_id,
  2         NVL(s.username, '(oracle)') AS username,
  3         s.sid,
  4         s.serial#,
  5         sw.event,
  6         sw.wait_class,
  7         sw.wait_time,
  8         sw.seconds_in_wait,
  9         sw.state
 10  FROM   gv$session_wait sw,
 11         gv$session s
 12  WHERE  s.sid     = sw.sid
 13  AND    s.inst_id = sw.inst_id
AND SW.wait_class not in  ('Idle')
 14   15  ORDER BY sw.seconds_in_wait DESC;

   INST_ID USERNAME                    SID    SERIAL# EVENT                          WAIT_CLASS       WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------- ---------- ---------- ------------------------------ --------------- ---------- --------------- -------------------
         1 BA01NETREC                 1060      62511 enq: KO - fast object checkpoi Application              0             119 WAITING
                                                      nt

         1 YNEMTS000                  1255      52147 enq: KO - fast object checkpoi Application              0              84 WAITING
                                                      nt

         1 BA01NETREC                 1163       2881 enq: KO - fast object checkpoi Application              0              69 WAITING
                                                      nt

         1 BA01NETREC                  588      31203 enq: KO - fast object checkpoi Application              0              54 WAITING
                                                      nt

         1 DSTUTT8243C                1455      25669 enq: KO - fast object checkpoi Application              0              52 WAITING
                                                      nt

         1 RRIVER002                  1356      26515 enq: KO - fast object checkpoi Application              0              38 WAITING
                                                      nt

         1 BA01NETREC                  583      44271 enq: KO - fast object checkpoi Application              0              34 WAITING
                                                      nt

         1 (oracle)                   1249          1 db file async I/O submit       System I/O               0              34 WAITING
         1 (oracle)                    961          3 db file async I/O submit       System I/O               0               8 WAITING
         1 CBOWIE001                  1075      14163 enq: KO - fast object checkpoi Application              0               8 WAITING
                                                      nt

         1 (oracle)                   1153          1 db file async I/O submit       System I/O               0               7 WAITING
         1 (oracle)                    385         25 db file async I/O submit       System I/O               0               7 WAITING
         1 DCLUBB00C                  1170      45365 db file parallel read          User I/O                 0               2 WAITING
         1 BA01PDSS                    881       2723 db file parallel read          User I/O                 0               2 WAITING
         1 (oracle)                   1441          3 db file async I/O submit       System I/O               0               1 WAITING
         1 BA01NETREC                  401      24563 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                         404      52507 direct path read               User I/O                 0               0 WAITING
         1 SBANKS9601                  499      13553 db file sequential read        User I/O                 0               0 WAITING
         1 HGISH9302                   501      30467 db file parallel read          User I/O                 0               0 WAITING
         1 GGS                         579       5727 direct path read               User I/O                 0               0 WAITING
         1 GGS                         587      36481 db file sequential read        User I/O                 0               0 WAITING
         1 HGISH9302                   592      64053 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                         677      28029 direct path read               User I/O                 0               0 WAITING
         1 BA01XCALIBUR                679      13899 direct path read               User I/O                 0               0 WAITING
         1 GGS                         683      26311 direct path read               User I/O                 0               0 WAITING
         1 BA01NETREC                  686      39331 db file sequential read        User I/O                 0               0 WAITING
         1 DBCHECK                     687      25661 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                         688       9777 direct path read               User I/O                 0               0 WAITING
         1 SYS                         783      53911 SQL*Net message to client      Network                 -1               0 WAITED SHORT TIME
         1 BA01WATCHTOWER              887        607 db file sequential read        User I/O                 0               0 WAITING
         1 BA01PDSS                    964      15527 db file sequential read        User I/O                 0               0 WAITING
         1 JNORWO001                   967      47307 db file sequential read        User I/O                 0               0 WAITING
         1 BA01PDSS                   1063       9935 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                        1070      62841 db file sequential read        User I/O                 0               0 WAITING
         1 BBENTO001                  1159      23543 db file parallel read          User I/O                 0               0 WAITING
         1 GGS                        1164      24051 db file sequential read        User I/O                 0               0 WAITING
         1 BA01WATCHTOWER             1166      14093 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                        1173      35589 log file sync                  Commit                   0               0 WAITING
         1 GGS                        1252      13841 db file sequential read        User I/O                 0               0 WAITING
         1 ARUSS001                   1261       9723 db file sequential read        User I/O                 0               0 WAITING
         1 (oracle)                   1345          1 db file async I/O submit       System I/O               0               0 WAITING
         1 BA01WATCHTOWER             1347      13433 db file parallel read          User I/O                 0               0 WAITING
         1 GGS                        1349        971 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                        1358      47663 db file sequential read        User I/O                 0               0 WAITING
         1 BA01PDSS                   1361       3377 db file parallel read          User I/O                 0               0 WAITING
         1 GGS                        1449      40975 db file sequential read        User I/O                 0               0 WAITING
         1 BA01CEMP                    391      39905 direct path read               User I/O                 0               0 WAITING
         1 BA01NETREC                  303      24201 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                         298      45331 direct path read               User I/O                 0               0 WAITING
         1 BA01CEMP                    213      55429 direct path read               User I/O                 0               0 WAITING
         1 GGS                         195       4631 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                         114      63151 direct path read               User I/O                 0               0 WAITING
         1 RJEELA6262K                 112      13983 direct path read               User I/O                 0               0 WAITING
         1 GGS                         111      24377 direct path read               User I/O                 0               0 WAITING
         1 BA01CEMP                    107       2839 direct path read               User I/O                 0               0 WAITING
         1 GGS                          18      12961 db file sequential read        User I/O                 0               0 WAITING
         1 BA01WATCHTOWER               14       8749 db file sequential read        User I/O                 0               0 WAITING
         1 JSCIBI00                     10      56667 db file sequential read        User I/O                 0               0 WAITING
         1 GGS                           6      50555 db file sequential read        User I/O                 0               0 WAITING
         1 CDAVIS07                   1457      19845 db file sequential read        User I/O                 0               0 WAITING
         1 (oracle)                      1          3 log file parallel write        System I/O               0               0 WAITING
         1 BA01WCDEV                     5      27117 db file sequential read        User I/O                 0               0 WAITING

62 rows selected.

SQL>
Re: I/O issue [message #533462 is a reply to message #533461] Wed, 30 November 2011 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most likely many TRUNCATE statements have been executed at this moment.

Regards
Michel
Re: I/O issue [message #533464 is a reply to message #533462] Wed, 30 November 2011 03:03 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel,

How to check this to the above reference issue "TRUNCATE", i mean i have to drop mail with justification point to client.
Re: I/O issue [message #533470 is a reply to message #533464] Wed, 30 November 2011 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"enq: KO - fast object checkpoint", this event occurs when you execute TRUNCATE.
But it can also occur when you use direct reads like in parallel full table scan.

Regards
Michel
Re: I/O issue [message #533928 is a reply to message #533461] Thu, 01 December 2011 13:43 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
your output isn't enough for analysis. Please send an awr or a statspack report for the interesting period of time.

[Updated on: Thu, 01 December 2011 13:43]

Report message to a moderator

Previous Topic: memory_max_target and 11g memory utilization - question
Next Topic: parallel_index
Goto Forum:
  


Current Time: Thu Mar 28 12:46:32 CDT 2024