FTS waiting on db file sequential reads?

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Thu, 18 Sep 2014 14:05:50 -0500
Message-ID: <CAP79kiTNiEyEQa5x688QZRdDV3qy0jVxOguyi173Fyggju9UZA_at_mail.gmail.com>

Ok, this is a new one to me.

Version: 11.2.0.3
OS: AIX 6.1 (I think)

*Plan:*

Plan hash value: 3070881581

---

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |

---

|   0 | SELECT STATEMENT  |                    |        |       |  4159
(100)|          |

| 1 | TABLE ACCESS FULL| PR_ACCOUNT_STAGING | 304K| 88M| 4159 (1)| 00:00:50 |

---

Query Block Name / Object Alias (identified by operation id):

---

   1 - SEL$1 / PR_ACCOUNT_STAGING_at_SEL$1

Outline Data

---

  /*+

      BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')       DB_VERSION('11.2.0.3')       ALL_ROWS       OUTLINE_LEAF(_at_"SEL$1")

      FULL(_at_"SEL$1" "PR_ACCOUNT_STAGING"_at_"SEL$1")

      END_OUTLINE_DATA   */

Note

---

• Warning: basic plan statistics not available. These are only collected when:
  • hint 'gather_plan_statistics' is used for the statement or
  • parameter 'statistics_level' is set to 'ALL', at session or system level

*SNAPPER.SQL* *--------------*

    416, SMIT_APP , STAT, SQL*Net roundtrips to/from

client                         ,             1,        .09,
,             ,          ,           ,

    416, SMIT_APP  , TIME, DB
CPU                                                    ,       5741231,
521.93ms,    52.2%, [_at__at_@@@@    ],          ,           ,

    416, SMIT_APP  , TIME, sql execute elapsed
time                                  ,     206506466,     18.77s,
1877.3%, [##########],          ,           ,

    416, SMIT_APP  , TIME, DB
time                                                   ,     206506710,
18.77s,  1877.3%, [##########],          ,           ,

    416, SMIT_APP  , WAIT, latch: cache buffers
chains                               ,          6692,   608.36us,      .1%,
[          ],       143,         13,     46.8us

    416, SMIT_APP  , WAIT, read by other
session                                     ,       4545380,   413.22ms,
41.3%, [WWWWW     ],       810,      73.64,     5.61ms

    416, SMIT_APP  , WAIT, db file sequential
read                                   ,       5325203,   484.11ms,
48.4%, [WWWWW     ],      1061,      96.45,     5.02ms

    416, SMIT_APP  , WAIT, SQL*Net message to
client                                 ,             3,      .27us,
.0%, [          ],         1,        .09,        3us

    416, SMIT_APP  , WAIT, SQL*Net more data to
client                               ,           211,    19.18us,      .0%,
[          ],         2,        .18,    105.5us

    416, SMIT_APP  , WAIT, SQL*Net message from
client                               ,          1784,   162.18us,      .0%,
[          ],         1,        .09,     1.78ms

• End of Stats snap 1, end=2014-09-18 14:53:18, seconds=11

  ---

Active% | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS

---

    58% | f6tyu3fwzkbz8 | 0 | db file sequential read | User I/O

    42% | f6tyu3fwzkbz8 | 0 | read by other session | User I/O

• End of ASH snap 1, end=2014-09-18 14:53:18, seconds=10, samples_taken=98

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 18 2014 - 21:05:50 CEST