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

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 31 Aug 2012 01:12:31 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A82578B_at_onews32>

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_at_gmail.com]

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.

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

Received on Thu Aug 30 2012 - 20:12:31 CDT