This discussion is archived
2 Replies Latest reply: Sep 28, 2011 5:01 PM by 603349 RSS

Real World Queries that use SmartScan and/or StorageIndexes

Hemant K Chitale Oracle ACE
Currently Being Moderated
I am looking for Real World Queries (appropriately masked if necessary) that are proven to use SmartScan and/or StorageIndexes.

Most examples I see "out there" are single predicate queries. Which are not the sort of queries I see from OBIEE and other applications. So I am looking for Real World proofs.


Hemant K Chitale
  • 1. Re: Real World Queries that use SmartScan and/or StorageIndexes
    Marc Fielding Journeyer
    Currently Being Moderated
    I'll bite. Here's a SQL monitor report for an OBIEE query running with smart scans:
    SQL Plan Monitoring Details (Plan Hash Value=2992816917)
    ========================================================================================================================================================================================================
    | Id |                 Operation                  |      Name       |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   |  Mem  | Activity |      Activity Detail       |
    |    |                                            |                 | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload | (Max) |   (%)    |        (# samples)         |
    ========================================================================================================================================================================================================
    |  0 | SELECT STATEMENT                           |                 |         |       |         1 |     +4 |     1 |       27 |      |       |         |       |          |                            |
    |  1 |   SORT GROUP BY                            |                 |       5 | 55461 |         1 |     +4 |     1 |       27 |      |       |         |  4096 |          |                            |
    |  2 |    MERGE JOIN CARTESIAN                    |                 |       5 | 55461 |         1 |     +4 |     1 |    30477 |      |       |         |       |          |                            |
    |  3 |     PX COORDINATOR                         |                 |         |       |         1 |     +4 |    33 |    30477 |      |       |         |       |          |                            |
    |  4 |      PX SEND QC (RANDOM)                   | :TQ10002        |       5 | 55461 |         1 |     +4 |    16 |    30477 |      |       |         |       |          |                            |
    |  5 |       HASH JOIN BUFFERED                   |                 |       5 | 55461 |         3 |     +2 |    16 |    30477 |      |       |         |   18M |          |                            |
    |  6 |        BUFFER SORT                         |                 |         |       |         3 |     +2 |    16 |       60 |      |       |         | 32768 |          |                            |
    |  7 |         PART JOIN FILTER CREATE            | :BF0000         |         |       |         3 |     +2 |    16 |       60 |      |       |         |       |          |                            |
    |  8 |          PX RECEIVE                        |                 |         |       |         3 |     +2 |    16 |       60 |      |       |         |       |          |                            |
    |  9 |           PX SEND HASH LOCAL               | :TQ10000        |         |       |         1 |     +3 |     1 |       60 |      |       |         |       |          |                            |
    | 10 |            NESTED LOOPS                    |                 |         |       |         1 |     +3 |     1 |       60 |      |       |         |       |          |                            |
    | 11 |             NESTED LOOPS                   |                 |      60 |    67 |         1 |     +3 |     1 |       60 |      |       |         |       |          |                            |
    | 12 |              MERGE JOIN CARTESIAN          |                 |      60 |     7 |         1 |     +3 |     1 |       60 |      |       |         |       |          |                            |
    | 13 |               TABLE ACCESS STORAGE FULL    | DIM_ONE         |       2 |     3 |         1 |     +3 |     1 |        2 |      |       |         |       |          |                            |
    | 14 |               BUFFER SORT                  |                 |      30 |     4 |         1 |     +3 |     2 |       60 |      |       |         |  2048 |          |                            |
    | 15 |                TABLE ACCESS BY INDEX ROWID | DIM_TWO         |      30 |     2 |         1 |     +3 |     1 |       30 |      |       |         |       |          |                            |
    | 16 |                 INDEX RANGE SCAN           | IDX_DIM_TWO     |      30 |     1 |         1 |     +3 |     1 |       30 |      |       |         |       |          |                            |
    | 17 |              INDEX UNIQUE SCAN             | PK_DIM_THREE    |       1 |       |         1 |     +3 |    60 |       60 |      |       |         |       |          |                            |
    | 18 |             TABLE ACCESS BY INDEX ROWID    | DIM_THREE       |       1 |     1 |         1 |     +3 |    60 |       60 |      |       |         |       |          |                            |
    | 19 |        PX RECEIVE                          |                 |     996 | 55393 |         1 |     +4 |    16 |    48870 |      |       |         |       |          |                            |
    | 20 |         PX SEND HASH LOCAL                 | :TQ10001        |     996 | 55393 |         3 |     +2 |    16 |    48870 |      |       |         |       |          |                            |
    | 21 |          PX BLOCK ITERATOR                 |                 |     996 | 55393 |         3 |     +2 |    16 |    48870 |      |       |         |       |          |                            |
    | 22 |           TABLE ACCESS STORAGE FULL        | FACT_TABLE      |     996 | 55393 |         3 |     +2 | 22928 |    48870 | 5912 |   6GB |  99.79% |       |   100.00 | Cpu (2)                    |
    |    |                                            |                 |         |       |           |        |       |          |      |       |         |       |          | cell smart table scan (31) |
    | 23 |     BUFFER SORT                            |                 |       1 |    69 |         1 |     +4 | 30477 |    30477 |      |       |         |  2048 |          |                            |
    | 24 |      INDEX UNIQUE SCAN                     | PK_DIM_FOUR     |       1 |     1 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                            |
    ========================================================================================================================================================================================================
    
    SQL Monitoring Report
    
    SQL Text
    ------------------------------
    select /*+monitor*/ T14841.DT_KEY as c1, sum(round(T116516.SUM_COLUMN , 3)) as c2 from DW.DIM_THREE  T14841 /* DIM_THREE_TRANS */ , DW.DIM_TWO         
    T141776 /* DIM_TWO_TRANS */ , DW.DIM_ONE T2828, DW.FACT_TABLE    T116516 /* FACT_TABLE_CLI */ , DW.DIM_FOUR T141693 
    where ( T14841.DIM_THREE_KEY = T116516.DIM_THREE_KEY and T2828.DIM_ONE_KEY = T116516.DIM_ONE_KEY and T14841.DIM_THREE_KEY = T141776.DIM_THREE_KEY and T14841.LEVEL_ID = 1 
    and T14841.CURR_IND = 'Y' and T116516.TYPE_KEY = 11 and T116516.DIM_FOUR_KEY = T141693.DIM_FOUR_KEY and T116516.DIM_FOUR_KEY = 2116287.0 
    and T141693.DIM_FOUR_KEY = 2116287.0 and T141776.TIME_PERIOD = 'Last 30 Days' and case when T2828.DIM_KEY in (21, 22) then 'United States' else T2828.DIM_NM end = 'United States' ) 
    group by T14841.DT_KEY order by c1
  • 2. Re: Real World Queries that use SmartScan and/or StorageIndexes
    603349 Explorer
    Currently Being Moderated
    Perhaps you should consider the opposite: In what scenarios would Smart Scan or Strorage Indexes not be used? (this would be a much smaller list, BTW IMO).

    I've seen OBIEE, MicroStrategy, Business Objects and Cognos generated BI queries and Smart Scan works for each of them.

    --
    Regards,
    Greg Rahn
    http://structureddata.org

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points