Re: FTS waiting on db file sequential reads?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: christopherdtaylor1994@xxxxxxxxx
- Date: Thu, 18 Sep 2014 13:18:19 -0600
chained rows
delayed block clean out
On Sep 18, 2014, at 1:05 PM, Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
wrote:
> 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@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(@"SEL$1")
> FULL(@"SEL$1" "PR_ACCOUNT_STAGING"@"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%, [@@@@@@ ], ,
> ,
> 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
Other related posts:
