Home » RDBMS Server » Performance Tuning » Query Optimization (11g)
Query Optimization [message #466473] Mon, 19 July 2010 17:51 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
Dear friends,

I am execute the following query on a table which has 2 million records. I also have a composite index on the column used in where clause but the index is never getting used. 

SQL> SELECT ComponentOrderNumber, ElementOrderNumber, Tape,        BeginPos, End
Pos, ElementSize, ComponentStopPos FROM DP_TAPEINSTANCE_COMPNT_ELT e
  2  WHERE OBJECTNAME = '060a2b340101010101010f0013-000000-49fb58579a640612-060e
2b347f7f-2a80' AND CATEGORY like 'AVID'  AND INSTANCEORDERNUMBER = 2 ORDER BY CO
MPONENTORDERNUMBER, ELEMENTORDERNUMBER;

Elapsed: 00:00:03.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=46625 Card=1454449
           Bytes=165807186)

   1    0   SORT (ORDER BY) (Cost=46625 Card=1454449 Bytes=165807186)
   2    1     TABLE ACCESS (FULL) OF 'DP_TAPEINSTANCE_COMPNT_ELT' (TAB
          LE) (Cost=9199 Card=1454449 Bytes=165807186)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      33805  consistent gets
      33779  physical reads
          0  redo size
        807  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed





Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
      33813  consistent gets
      33786  physical reads
          0  redo size
        807  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


I also tried updating the statistics but there was no difference.
SQL> exec dbms_stats.gather_schema_stats('TELEVISA_SAN_60302_1007161605',METHOD_
OPT=>'For all indexed columns size 250',CASCADE=>true);


When i used Index hints on the query,The index got used and query execute in less than a second.

SQL> SELECT /*+ index(e,DP_IDX_OBJECT_INSTANCE) */ ComponentOrderNumber, Element
OrderNumber, Tape,        BeginPos, EndPos, ElementSize, ComponentStopPos FROM D
P_TAPEINSTANCE_COMPNT_ELT e
  2  WHERE OBJECTNAME = '060a2b340101010101010f0013-000000-49fb58579a640612-060e
2b347f7f-2a80' AND CATEGORY like 'AVID'  AND INSTANCEORDERNUMBER = 2 ORDER BY CO
MPONENTORDERNUMBER, ELEMENTORDERNUMBER ;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1087154 Card=14544
          49 Bytes=165807186)

   1    0   SORT (ORDER BY) (Cost=1087154 Card=1454449 Bytes=165807186
          )

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DP_TAPEINSTANCE_COMPNT
          _ELT' (TABLE) (Cost=1049727 Card=1454449 Bytes=165807186)

   3    2       INDEX (RANGE SCAN) OF 'DP_IDX_OBJECT_INSTANCE' (INDEX)
           (Cost=17046 Card=1454449)





Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        807  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


My question is why is the Cost based optimizer in oracle not choosing to use the index.

Thanks in advance,

Report message to a moderator

Re: Query Optimization [message #466474 is a reply to message #466473] Mon, 19 July 2010 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CATEGORY like 'AVID'
WHY?
Why NOT below instead
CATEGORY = 'AVID'

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Query Optimization [message #466478 is a reply to message #466473] Mon, 19 July 2010 19:31 Go to previous messageGo to next message
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
Senior Member
What columns is the index on?

Report message to a moderator

Re: Query Optimization [message #466481 is a reply to message #466473] Mon, 19 July 2010 19:49 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Dear Friends,

Thanks for your replies.

I modified the query as you suggested but its still not using the Index.

SQL> SELECT ComponentOrderNumber, ElementOrderNumber, Tape,        BeginPos, EndPos, ElementSize, ComponentStopPos FROM DP_TAPEINSTANCE_COMPNT_ELT e WHERE OBJECTNAME = '060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e
2b347f7f-2a80' AND CATEGORY = 'AVID'  AND INSTANCEORDERNUMBER = 1 ORDER BY COMPONENTORDERNUMBER, ELEMENTORDERNUMBER;

Elapsed: 00:00:03.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=23120 Card=541374
          Bytes=61716636)

   1    0   SORT (ORDER BY) (Cost=23120 Card=541374 Bytes=61716636)
   2    1     TABLE ACCESS (FULL) OF 'DP_TAPEINSTANCE_COMPNT_ELT' (TAB
          LE) (Cost=9189 Card=541374 Bytes=61716636)





Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
      33813  consistent gets
      33779  physical reads
          0  redo size
        810  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


The Index created on the table is the following.

CREATE INDEX DP_IDX_OBJECT_INSTANCE ON DP_TAPEINSTANCE_COMPNT_ELT
(OBJECTNAME, CATEGORY, INSTANCEORDERNUMBER)
TABLESPACE DP_TS_INDEXES;


Thanks for you help again.
Regards,
Prakash R

Report message to a moderator

Re: Query Optimization [message #466482 is a reply to message #466481] Mon, 19 July 2010 20:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
To start the CBO trace enter the following command:

    ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';


Run the SQL that you wanto trace the CBO optimizer on, e.g.

    SELECT *
    FROM oe_order_headers_v
    WHERE header_id = 999
    /


When the query has completed, run the following command to switch the trace off:

    ALTER SESSION SET EVENTS '10053 trace name context off';

Report message to a moderator

Re: Query Optimization [message #466568 is a reply to message #466482] Tue, 20 July 2010 03:11 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try gathering stats as follows:

call DBMS_STATS.GATHER_TABLE_STATS(user, 'DP_TAPEINSTANCE_COMPNT_ELT');

In this way, Oracle will work out it's own optimal histogram size, which may be better than your overridden value.
Ross Leishmnan

Report message to a moderator

Re: Query Optimization [message #466694 is a reply to message #466568] Tue, 20 July 2010 10:44 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Dear Friensds,

Thanks for your help, I really appreciate it.

Blackswan,

I have the generated the trace file with event 10053. It is too big to past it here. I could not find way to attach the file, Please let me know if there is way to attach a file to the post.


rleishman,

I tried your solution but still the index is not getting used.

Thanks,
Prakash R

Report message to a moderator

Re: Query Optimization [message #466713 is a reply to message #466694] Tue, 20 July 2010 13:37 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Dear Friends,
Can anyone please help me understand the below Cost based optimizer trace (Event 10053). Why is CBO choosing to do a full table scan?
******************************************
----- Current SQL Statement for this session (sql_id=69bsavcmq2uvj) -----
SELECT ComponentOrderNumber, ElementOrderNumber, Tape,        BeginPos, EndPos, ElementSize, ComponentStopPos FROM DP_TAPEINSTANCE_COMPNT_ELT e
WHERE OBJECTNAME = '060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80' AND CATEGORY = 'AVID'  AND INSTANCEORDERNUMBER = 1 ORDER BY COMPONENTORDERNUMBER, ELEMENTORDERNUMBER
*******************************************

***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************

Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): no complex view or sub-queries.
CBQT: Validity checks failed for 69bsavcmq2uvj.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE:     OBYE performed.
CVM: Considering view merge in query block SEL$1 (#0)
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): no complex view or sub-queries.
CBQT: Validity checks failed for 69bsavcmq2uvj.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"E"."OBJECTNAME"='060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80' AND "E"."CATEGORY"='AVID' AND "E"."INSTANCEORDERNUMBER"=1
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
constraint: "E"."ELEMENTSIZE">0

constraint: "E"."ENDPOS">=0

constraint: "E"."BEGINPOS">=0

constraint: "E"."ELEMENTORDERNUMBER">0

finally: "E"."OBJECTNAME"='060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80' AND "E"."CATEGORY"='AVID' AND "E"."INSTANCEORDERNUMBER"=1

apadrv-start sqlid=7255029461091969905
  :
    call(in-use=764, alloc=16360), compile(in-use=52324, alloc=54524), execution(in-use=1628, alloc=4060)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=764, alloc=16360), compile(in-use=52964, alloc=54524), execution(in-use=1628, alloc=4060)

kkoqbc-subheap (create addr=0x07B86A94)
****************
QUERY BLOCK TEXT
****************

---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=397518 hint_alias="E"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEED: 1846 millions instructions/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: DP_TAPEINSTANCE_COMPNT_ELT  Alias:  E
    #Rows: 2027114  #Blks:  33779  AvgRowLen:  114.00
Index Stats::
  Index: DP_IDX_ELEMENT__NAME_CTG  Col#: 1 2
    LVLS: 3  #LB: 24616  #DK: 2020833  LB/K: 1.00  DB/K: 1.00  CLUF: 1523826.00
  Index: DP_IDX_ELEMENT__TAP_ENDP  Col#: 6 8
    LVLS: 2  #LB: 6812  #DK: 2046196  LB/K: 1.00  DB/K: 1.00  CLUF: 202156.00
  Index: DP_IDX_OBJECT_INSTANCE  Col#: 1 2 3
    LVLS: 3  #LB: 23727  #DK: 1918151  LB/K: 1.00  DB/K: 1.00  CLUF: 1438791.00
  Index: DP_IDX_TAPE_INS_CMP_ELT  Col#: 6
    LVLS: 2  #LB: 5243  #DK: 1190  LB/K: 4.00  DB/K: 155.00  CLUF: 184869.00
  Index: DP_IDX_TEMP_EXPORT_ELT  Col#: 11
    LVLS: 2  #LB: 3673  #DK: 1  LB/K: 3673.00  DB/K: 33751.00  CLUF: 33751.00
  Index: DP_PK_TAPEINSTANCE_COMPNT_ELT  Col#: 1 2 3 4 5
    LVLS: 3  #LB: 27175  #DK: 2089308  LB/K: 1.00  DB/K: 1.00  CLUF: 1552822.00
  Index: DP_TAPEINSTANCE_CE_TAPE_IDX  Col#: 6 7
    LVLS: 2  #LB: 6560  #DK: 2056075  LB/K: 1.00  DB/K: 1.00  CLUF: 202861.00
Access path analysis for DP_TAPEINSTANCE_COMPNT_ELT
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for DP_TAPEINSTANCE_COMPNT_ELT[E] 
  Column (#1): 
    NewDensity:0.000000, OldDensity:0.000507 BktCnt:250, PopBktCnt:249, PopValCnt:1, NDV:2014208
  Column (#2): 
    NewDensity:0.000090, OldDensity:0.000000 BktCnt:5564, PopBktCnt:5562, PopValCnt:6, NDV:15
  Column (#3): 
    NewDensity:0.001078, OldDensity:0.000000 BktCnt:5564, PopBktCnt:5564, PopValCnt:3, NDV:4
  Column (#6): 
    NewDensity:0.000834, OldDensity:0.001481 BktCnt:250, PopBktCnt:2, PopValCnt:1, NDV:1190
  Column (#7): 
    NewDensity:0.000001, OldDensity:0.000002 BktCnt:250, PopBktCnt:0, PopValCnt:0, NDV:1843584
  Column (#8): 
    NewDensity:0.000001, OldDensity:0.000000 BktCnt:250, PopBktCnt:0, PopValCnt:0, NDV:1871872
  Column (#4): 
    NewDensity:0.000026, OldDensity:0.001751 BktCnt:250, PopBktCnt:247, PopValCnt:1, NDV:467
  Column (#5): 
    NewDensity:0.500000, OldDensity:0.000000 BktCnt:5564, PopBktCnt:5564, PopValCnt:1, NDV:2
  ColGroup (#5, Index) DP_PK_TAPEINSTANCE_COMPNT_ELT
    Col#: 1 2 3 4 5    CorStregth: 54025.65
  ColGroup (#1, Index) DP_IDX_OBJECT_INSTANCE
    Col#: 1 2 3    CorStregth: 63.00
  ColGroup (#4, Index) DP_IDX_ELEMENT__TAP_ENDP
    Col#: 6 8    CorStregth: 1088.62
  ColGroup (#3, Index) DP_TAPEINSTANCE_CE_TAPE_IDX
    Col#: 6 7    CorStregth: 1067.02
  ColGroup (#2, Index) DP_IDX_ELEMENT__NAME_CTG
    Col#: 1 2    CorStregth: 14.95
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  Table: DP_TAPEINSTANCE_COMPNT_ELT  Alias: E
    Card: Original: 2027114.000000  Rounded: 541374  Computed: 541374.07  Non Adjusted: 541374.07
  Access Path: TableScan
    Cost:  9188.71  Resp: 9188.71  Degree: 0
      Cost_io: 9150.00  Cost_cpu: 857466059
      Resp_io: 9150.00  Resp_cpu: 857466059
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  Access Path: index (AllEqRange)
    Index: DP_IDX_ELEMENT__NAME_CTG
    resc_io: 1527839.00  resc_cpu: 12061931757
    ix_sel: 0.986692  ix_sel_with_filters: 0.986692 
    Cost: 1528383.53  Resp: 1528383.53  Degree: 1
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  Access Path: index (AllEqRange)
    Index: DP_IDX_OBJECT_INSTANCE
    resc_io: 390593.00  resc_cpu: 3053089834
    ix_sel: 0.267066  ix_sel_with_filters: 0.267066 
    Cost: 390730.83  Resp: 390730.83  Degree: 1
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  Access Path: index (skip-scan)
    SS sel: 0.267066  ANDV (#skips): 2006152.000000
    SS io: 2006152.000000 vs. index scan io: 9150.000000
    Skip Scan rejected
  Access Path: index (RangeScan)
    Index: DP_PK_TAPEINSTANCE_COMPNT_ELT
    resc_io: 421968.00  resc_cpu: 3300751314
    ix_sel: 0.267066  ix_sel_with_filters: 0.267066 
    Cost: 422117.01  Resp: 422117.01  Degree: 1
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 9188.71  Degree: 1  Resp: 9188.71  Card: 541374.07  Bytes: 0

***************************************


OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  DP_TAPEINSTANCE_COMPNT_ELT[E]#0
ORDER BY sort
    SORT ressource         Sort statistics
      Sort width:         435 Area size:      380928 Max Area size:    76336128
      Degree:               1
      Blocks to Sort: 9015 Row size:     136 Total Rows:         541374
      Initial runs:   2 Merge passes:  1 IO Cost / pass:       4884
      Total IO sort cost: 13899      Total CPU sort cost: 708718846
      Total Temp space used: 152953000
***********************
Best so far:  Table#: 0  cost: 23119.7052  card: 541374.0686  bytes: 61716636
***********************

****** Recost for ORDER BY (using index) ************
Access path analysis for DP_TAPEINSTANCE_COMPNT_ELT
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for DP_TAPEINSTANCE_COMPNT_ELT[E] 
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  Table: DP_TAPEINSTANCE_COMPNT_ELT  Alias: E
    Card: Original: 2027114.000000  Rounded: 541374  Computed: 541374.07  Non Adjusted: 541374.07
  Access Path: TableScan
    Cost:  9188.71  Resp: 9188.71  Degree: 0
      Cost_io: 9150.00  Cost_cpu: 857466059
      Resp_io: 9150.00  Resp_cpu: 857466059
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 
  Access Path: index (skip-scan)
    SS sel: 0.267066  ANDV (#skips): 2006152.000000
    SS io: 2006152.000000 vs. index scan io: 9150.000000
    Skip Scan rejected
  Access Path: index (RangeScan)
    Index: DP_PK_TAPEINSTANCE_COMPNT_ELT
    resc_io: 421968.00  resc_cpu: 3300751314
    ix_sel: 0.267066  ix_sel_with_filters: 0.267066 
    Cost: 422117.01  Resp: 422117.01  Degree: 1
  Best:: AccessPath: IndexRange
  Index: DP_PK_TAPEINSTANCE_COMPNT_ELT
         Cost: 422117.01  Degree: 1  Resp: 422117.01  Card: 541374.07  Bytes: 114

Join order[1]:  DP_TAPEINSTANCE_COMPNT_ELT[E]#0
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
    SORT ressource         Sort statistics
      Sort width:         435 Area size:      380928 Max Area size:    76336128
      Degree:               1
      Blocks to Sort: 9015 Row size:     136 Total Rows:         541374
      Initial runs:   2 Merge passes:  1 IO Cost / pass:       4884
      Total IO sort cost: 13899      Total CPU sort cost: 708718846
      Total Temp space used: 152953000
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate="E"."INSTANCEORDERNUMBER"=1 AND "E"."CATEGORY"='AVID' AND "E"."OBJECTNAME"='060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80'
Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 23119.7052  Degree: 1  Card: 541374.0000  Bytes: 61716636
  Resc: 23119.7052  Resc_io: 23049.0000  Resc_cpu: 1566184904
  Resp: 23119.7052  Resp_io: 23049.0000  Resc_cpu: 1566184904
kkoqbc-subheap (delete addr=0x07B86A94, in-use=14380, alloc=16192)
kkoqbc-end:
        :
    call(in-use=46664, alloc=81864), compile(in-use=57816, alloc=58648), execution(in-use=1628, alloc=4060)

kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
          :
    call(in-use=46664, alloc=81864), compile(in-use=58480, alloc=62772), execution(in-use=1628, alloc=4060)


Starting SQL statement dump

user_id=551 user_name=TELEVISA_SAN_60302_1007161605 module=SQL*Plus action=
sql_id=69bsavcmq2uvj plan_hash_value=-1778887006 problem_type=3
----- Current SQL Statement for this session (sql_id=69bsavcmq2uvj) -----
SELECT ComponentOrderNumber, ElementOrderNumber, Tape,        BeginPos, EndPos, ElementSize, ComponentStopPos FROM DP_TAPEINSTANCE_COMPNT_ELT e
WHERE OBJECTNAME = '060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80' AND CATEGORY = 'AVID'  AND INSTANCEORDERNUMBER = 1 ORDER BY COMPONENTORDERNUMBER, ELEMENTORDERNUMBER
sql_text_length=336
sql=SELECT ComponentOrderNumber, ElementOrderNumber, Tape,        BeginPos, EndPos, ElementSize, ComponentStopPos FROM DP_TAPEINSTANCE_COMPNT_ELT e
WHERE OBJECTNAME = '060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80' AND CATEGORY = 'AVID' 
sql= AND INSTANCEORDERNUMBER = 1 ORDER BY COMPONENTORDERNUMBER, ELEMENTORDERNUMBER

----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
--------------------------------------------------------+-----------------------------------+
| Id  | Operation           | Name                      | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |                           |       |       |   23K |           |
| 1   |  SORT ORDER BY      |                           |  529K |   59M |   23K |  00:05:38 |
| 2   |   TABLE ACCESS FULL | DP_TAPEINSTANCE_COMPNT_ELT|  529K |   59M |  9189 |  00:02:51 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("INSTANCEORDERNUMBER"=1 AND "CATEGORY"='AVID' AND "OBJECTNAME"='060a2b340101010101010f0013-000000-481bbc61fe9a0214-060e2b347f7f-2a80'))
 
Content of other_xml column
===========================
  db_version     : 11.1.0.6
  parse_schema   : TELEVISA_SAN_60302_1007161605
  plan_hash      : 2516080290
  plan_hash_2    : 4207972617
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      DB_VERSION('11.1.0.6')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
    END_OUTLINE_DATA
  */


Any help is highly appreciated.

Regards,
Prakash R

Report message to a moderator

Re: Query Optimization [message #466718 is a reply to message #466713] Tue, 20 July 2010 14:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Skimmed off some parts. Look at lines enclosed by [b] tag.
Table: DP_TAPEINSTANCE_COMPNT_ELT  Alias: E
    Card: Original: 2027114.000000  Rounded: 541374  Computed: 541374.07  Non Adjusted: 541374.07
[b]  Access Path: TableScan[/b]
[b] Cost:  9188.71  Resp: 9188.71  Degree: 0[/b]
...
...
[b]  Access Path: index (RangeScan)[/b]
    Index: DP_PK_TAPEINSTANCE_COMPNT_ELT
    resc_io: 421968.00  resc_cpu: 3300751314
    ix_sel: 0.267066  ix_sel_with_filters: 0.267066 
[b]    Cost: 422117.01  Resp: 422117.01  Degree: 1[/b]
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
[b]  Best:: AccessPath: TableScan[/b]
         Cost: 9188.71  Degree: 1  Resp: 9188.71  Card: 541374.07  Bytes: 0


CBO chose full table scan because cost was cheaper than index range
scan.
Out of 2,027,114 records 541,374 are included.

If you can afford to play, try the extended statistics feature in 11g.

Report message to a moderator

Re: Query Optimization [message #466719 is a reply to message #466713] Tue, 20 July 2010 14:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And please post the output of
select column_name, num_distinct, histogram from user_tab_col_statistics where table_name='yourTable';

Report message to a moderator

Re: Query Optimization [message #466740 is a reply to message #466719] Tue, 20 July 2010 16:30 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Mahesh,

Thanks for your reply. Can you please help me understand what do you mean by "Out of 2,027,114 records 541,374 are included."

Please find below the results for the query
select column_name, num_distinct, histogram from user_tab_col_statistics where table_name='DP_TAPEINSTANCE_COMPNT_ELT'; 

COLUMN_NAME,		NUM_DISTINCT,	HISTOGRAM

OBJECTNAME,		2014208,	HEIGHT BALANCED
CATEGORY,		15,		FREQUENCY
INSTANCEORDERNUMBER,	4,		FREQUENCY
COMPONENTORDERNUMBER,	467,		HEIGHT BALANCED
ELEMENTORDERNUMBER,	2,		FREQUENCY
TAPE,			1190,		HEIGHT BALANCED
BEGINPOS,		1843584,	HEIGHT BALANCED
ENDPOS,			1871872,	HEIGHT BALANCED
ELEMENTSIZE,		72736,		HEIGHT BALANCED
COMPONENTSTOPPOS,	91560,		NONE
TEMP_EXPORT,		1,		FREQUENCY


Thank you once again , I really appreciate your help.
Regards,
Prakash R

Report message to a moderator

Re: Query Optimization [message #466746 is a reply to message #466740] Tue, 20 July 2010 21:57 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Dear Friends,

A lot of queries in our application have started doing full table scan instead of using the Index since the database has grown little bigger, even updating the statistics did not help. When i investigated, all seems to be the same reason as the CBO choose full table scan because it costs less but takes more time.When i tried the same sql queries using sql hints forcing the queries to make use of the index I can see the cost more but executed in less than a second.

Since going and changing all the queries with hints in the application is not immediate solution, can anyone suggest an alternative for this problem.


Thanks in advance,
Regards,
Prakash R

Report message to a moderator

Re: Query Optimization [message #466747 is a reply to message #466746] Tue, 20 July 2010 22:11 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Check selectivity.
2/ Try rewrite the queries.

Report message to a moderator

Re: Query Optimization [message #466749 is a reply to message #466746] Tue, 20 July 2010 22:58 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try reading up on
optimizer_index_caching
optimizer_index_cost_adj

They will alter the behaviour of the optimizer with respect to calculating the cost of indexes access paths. But be warned, these changes will apply to every SQL with a potential indexed access path. Tuned improperly, you may find Oracle performs index scans when you would prefer full scans.
Ross Leishman

Report message to a moderator

Previous Topic: High CPU Cost and low optimizer cost
Next Topic: performance of cursor embedded in select
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 04 11:00:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.