Home » RDBMS Server » Performance Tuning » Query Optimization (11g)
Query Optimization [message #466473] |
Mon, 19 July 2010 17:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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,
|
|
|
|
|
Re: Query Optimization [message #466481 is a reply to message #466473] |
Mon, 19 July 2010 19:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: Query Optimization [message #466568 is a reply to message #466482] |
Tue, 20 July 2010 03:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Query Optimization [message #466694 is a reply to message #466568] |
Tue, 20 July 2010 10:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Query Optimization [message #466713 is a reply to message #466694] |
Tue, 20 July 2010 13:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Re: Query Optimization [message #466740 is a reply to message #466719] |
Tue, 20 July 2010 16:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Query Optimization [message #466746 is a reply to message #466740] |
Tue, 20 July 2010 21:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: Query Optimization [message #466749 is a reply to message #466746] |
Tue, 20 July 2010 22:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:14:25 CDT 2024
|