Exadata tips

I wanted to write up an Exadata tip that I learned.

Background :  I wanted to do a simple "select count(1) from mytable".  mytable has a primary key on it.  The count seemed to be taking a long time for an Exadata.

First the "select count(1) from mytable". You can see that it uses an index storage fast full scan.  The top wait event is "cell multiblock physical read".  The query does  2 Million Disk reads in 3 Minutes 28 seconds.

But this seems slow...

select count(1)
from
     MYTABLE MYTABLE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     56.46     208.27    2030378    2031797         20           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     56.46     208.27    2030378    2031797         20           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2031797 pr=2030378 pw=0 time=208276577 us)
 592848893  592848893  592848893   INDEX STORAGE FAST FULL SCAN PK_MYTABLE (cr=2031797 pr=2030378 pw=0 time=245927651 us cost=523897 size=0 card=572441788)(object id 312310)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache lock                              4        0.00          0.00
  Disk file operations I/O                       42        0.00          0.00
  library cache pin                               2        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  cell single block physical read                15        0.00          0.00
  cell list of blocks physical read               2        0.00          0.00
  cell multiblock physical read               15959        0.26        152.20
  latch: object queue header operation            1        0.00          0.00
  SQL*Net message from client                     2       10.38         10.38
********************************************************************************

Next I did a FTS.

"select /*+full(MYTABLE) */ count(1) from  MYTABLE MYTABLE ;

You can see this did 2.2 Million disk reads (more than the index scan), but the wait event is sql_net. With the "cell smart table scan", there were very few waits, and the wait was much shorter.

select /*+full(MYTABLE) */ count(1)
from
     MYTABLE MYTABLE  

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     65.11      66.48    2224642    2225028         21           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     65.11      66.48    2224642    2225028         21           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2225028 pr=2224642 pw=0 time=66486729 us)
 592848893  592848893  592848893   PARTITION RANGE ALL PARTITION: 1 33 (cr=2225028 pr=2224642 pw=0 time=140325566 us cost=533066 size=0 card=572441788)
 592848893  592848893  592848893    TABLE ACCESS STORAGE FULL MYTABLE PARTITION: 1 33 (cr=2225028 pr=2224642 pw=0 time=54479242 us cost=533066 size=0 card=572441788)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                      129        0.00          0.00
  gc current block 2-way                         65        0.00          0.00
  enq: KO - fast object checkpoint              101        0.00          0.02
  reliable message                               33        0.09          0.30
  gc current block 3-way                         13        0.00          0.00
  cell smart table scan                        1403        0.03          1.01
  gc cr block 3-way                               1        0.00          0.00
  gc current grant busy                          18        0.00          0.00
  gc cr block 2-way                              17        0.00          0.00
  gc cr multi block request                       5        0.00          0.00
  cell single block physical read                11        0.00          0.00
  cell list of blocks physical read               2        0.00          0.00
  gc cr grant 2-way                               3        0.00          0.00
  SQL*Net message from client                     2        9.62          9.62

Bottom line, if you want to a count on a table use the "FULL" hint.  The exadata is built for table scans, and this example shows that.

It also should make you rethink when to use indexes for an application, you see they can hurt you in some cases.