RE: "direct path read" and "db file sequential read" used for full table scans in 11g

  • From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 31 Aug 2012 01:12:31 +0000

Yes Sayan, thank you very much for the ideas, but my small table threshold is 
6540, which means that direct path read should be used for tables over 32700 
blocks (_small_table_threshold x 5) if I understand correctly, and the table 
I'm working with has 123872 blocks so it should qualify (details below).  I 
also tried flushing the buffer cache with "alter system flush buffer_cache", 
but it still does "db file sequential read" as shown below.

SQL> select ksppstvl from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and 
ksppinm='_small_table_threshold';

KSPPSTVL
--------
6540

SQL> select 6540*5 from dual;

    6540*5
----------
     32700

SQL> select blocks, empty_blocks from dba_tables where table_name = 
'T_TRIWORKTASK';

    BLOCKS EMPTY_BLOCKS
---------- ------------
    123872            0

SQL> select object_id from dba_objects where object_name = 'T_TRIWORKTASK';

 OBJECT_ID
----------
    279095

SQL> select status, dirty, stale, direct, temp, count(*) from v$bh where 
objd=279095 group by status, dirty, stale, direct, temp;

STATUS     D S D T   COUNT(*)
---------- - - - - ----------
xcur       Y N N N          2
free       N N N N     104106
xcur       N N N N      11413

SQL> alter system flush buffer_cache;

System altered.

SQL> select status, dirty, stale, direct, temp, count(*) from v$bh where 
objd=279095 group by status, dirty, stale, direct, temp;

STATUS     D S D T   COUNT(*)
---------- - - - - ----------
free       N N N N     114137


Plan and wait events from tkprof:
--------------------------------

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  
---------------------------------------------------
    600815     600815     600815  SORT ORDER BY (cr=1352370 pr=180699 pw=24956 
time=777998 us cost=69947 size=199593540 card=599380)
    600815     600815     600815   TABLE ACCESS FULL T_TRIWORKTASK (cr=1352370 
pr=155743 pw=0 time=96932776 us cost=27251 size=199593540 card=599380)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   40056        0.00          0.02
  db file sequential read                     32667        0.02         74.75
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.01          0.01
  db file scattered read                          1        0.01          0.01
  direct path read                             1798        0.01          5.29
  direct path write temp                        807        0.02          1.53
  asynch descriptor resize                        3        0.00          0.00
  direct path read temp                         346        0.08          1.19
  SQL*Net message from client                 40056       29.28        311.98

-----Original Message-----
From: Sayan Malakshinov [mailto:xt.and.r@xxxxxxxxx]

About 1: have you tried to decrease _small_table_threshold? What size of your 
tables on which you testing?
About 2: have you tried to flush buffer_cache?



________________________________

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2012