WeblogNotes

Introduction

Schema Architecture

1st Schema Range-List partitioning on RUNNR range, 1 partition=4 runs, 10 subpartitions=10 GOLDEN1 Total 500Gb of data.

2nd Schema - being tested now. Range partitioning on RUNNR 40 runs per partition, 1Gb per partition. 10 different GOLDEN1 tables. Total 1TB of data.

Queries used

SQL tricks

  Hints

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm

Hints used so far:

PARALLEL - needed for parallelization of full table scan or partition range scan.

PARALLEL_INDEX - needed for parallelization of index access

INDEX_JOIN - for hash joins with b-tree indexes

INDEX_COMBINE - for bitmap indexes

opt_param('_INDEX_JOIN_ENABLED','false') - Can enable and disable session parameters for a single SQL

Status Log

  07 May 2007

We saw CPU time being wasted in bitmap to rowid conversions, and vice-versa when we chose INDEX_JOIN or INDEX_COMBINE, but the choice of filters included a mix of both type of indexes. So, we tried to "decompose" the SQL into two separate SQLs, separating the filters into bitmap fields and b-tree fields, so Oracle would choose an optimal path for each. The results are as follows:

select /*+ index_join(e ICMG1_1_nor1num01 ICMG1_1_nor10num01 ICMG1_1_nor100num01 ICMG1_1_ID) */
id from event_g1_1_o100m  partition (runnr_1_40) e
where id > 1 and nor1num01 > 498000 and nor10num01 > 485000 and nor100num01 > 300000
INTERSECT
select /*+ index(e2 ICMG1_1_ID)*/ id from event_g1_1_o100m  partition (runnr_1_40) e2 where id is not null and rowid in (
select /*+ index_combine(e) */
rowid from event_g1_1_o100m  partition (runnr_1_40) e
where  enumuni1000num01 > 25 and enumuni100num01 > 3 and
uni10Knum01 < 9900)

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      168     23.17      27.48      11780      14378          0      833260
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      170     23.17      27.48      11780      14378          0      833260

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 66

Rows     Row Source Operation
-------  ---------------------------------------------------
 833260  INTERSECTION  (cr=14378 pr=11780 pw=0 time=32446592 us)
 889500   SORT UNIQUE (cr=10214 pr=10214 pw=0 time=24377292 us)
 889500    VIEW  index$_join$_001 (cr=10214 pr=10214 pw=0 time=31019009 us)
 889500     HASH JOIN  (cr=10214 pr=10214 pw=0 time=22123995 us)
 889500      HASH JOIN  (cr=7616 pr=7616 pw=0 time=17947498 us)
 910330       HASH JOIN  (cr=5037 pr=5037 pw=0 time=13444715 us)
 976200        PARTITION RANGE SINGLE PARTITION: 2 2 (cr=2576 pr=2576 pw=0 time=17574808 us)
 976200         INDEX RANGE SCAN ICMG1_1_NOR1NUM01 PARTITION: 2 2 (cr=2576 pr=2576 pw=0 time=7812765 us)(object id 1509902)
 932480        PARTITION RANGE SINGLE PARTITION: 2 2 (cr=2461 pr=2461 pw=0 time=16787143 us)
 932480         INDEX RANGE SCAN ICMG1_1_NOR10NUM01 PARTITION: 2 2 (cr=2461 pr=2461 pw=0 time=8394791 us)(object id 1509900)
 977250       PARTITION RANGE SINGLE PARTITION: 2 2 (cr=2579 pr=2579 pw=0 time=16616720 us)
 977250        INDEX RANGE SCAN ICMG1_1_NOR100NUM01 PARTITION: 2 2 (cr=2579 pr=2579 pw=0 time=6844166 us)(object id 1509898)
1000000      PARTITION RANGE SINGLE PARTITION: 2 2 (cr=2598 pr=2598 pw=0 time=17001646 us)
1000000       INDEX RANGE SCAN ICMG1_1_ID PARTITION: 2 2 (cr=2598 pr=2598 pw=0 time=8001625 us)(object id 1509848)
 937230   SORT UNIQUE (cr=4164 pr=1566 pw=0 time=14239914 us)
 937230    HASH JOIN  (cr=4164 pr=1566 pw=0 time=36520140 us)
1000000     PARTITION RANGE SINGLE PARTITION: 2 2 (cr=2598 pr=0 pw=0 time=16000060 us)
1000000      INDEX FULL SCAN ICMG1_1_ID PARTITION: 2 2 (cr=2598 pr=0 pw=0 time=6000048 us)(object id 1509848)
 937230     PARTITION RANGE SINGLE PARTITION: 2 2 (cr=1566 pr=1566 pw=0 time=15296058 us)
 937230      BITMAP CONVERSION TO ROWIDS (cr=1566 pr=1566 pw=0 time=6860965 us)
     27       BITMAP AND  (cr=1566 pr=1566 pw=0 time=2403752 us)
     27        BITMAP MERGE  (cr=429 pr=429 pw=0 time=392836 us)
    857         BITMAP INDEX RANGE SCAN ICMG1_1_ENUMUNI100NUM01 PARTITION: 2 2 (cr=429 pr=429 pw=0 time=173323 us)(object id 1509878)
     27        BITMAP MERGE  (cr=525 pr=525 pw=0 time=634527 us)
   1253         BITMAP INDEX RANGE SCAN ICMG1_1_ENUMUNI1000NUM01 PARTITION: 2 2 (cr=525 pr=525 pw=0 time=183859 us)(object id 1509876)
     27        BITMAP MERGE  (cr=612 pr=612 pw=0 time=1534647 us)
   9900         BITMAP INDEX RANGE SCAN ICMG1_1_UNI10KNUM01 PARTITION: 2 2 (cr=612 pr=612 pw=0 time=347635 us)(object id 1509884)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     168        0.00          0.00
  gc cr grant 2-way                            5422        0.00          1.85
  db file sequential read                     11780        0.00          4.60
  gc cr grant congested                           1        0.00          0.00
  SQL*Net message from client                   168        0.00          0.72
  SQL*Net more data to client                  3540        0.00          0.07
  library cache lock                             42        0.00          0.02
********************************************************************************

Responsible: FlorbelaViegas
Last reviewed by: Never reviewed