-
Tuning Help-Urgent
Folks,
I'm trying to execute a simple qry like this takes 35 mins
Code:
1.select * from building_info where bld_seq_id=21261023;
120 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=37)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'BUILDING_INFO' (Cost=5 Card=1 Bytes=37)
2 1 INDEX (RANGE SCAN) OF 'BD_SEQ_ID' (NON-UNIQUE) (Cost=4 Card=1)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
6123550 consistent gets
6058079 physical reads
8144 redo size
8121 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
120 rows processed
When I pass a hint of the same Index , it returns in seconds.All the statistics are current, also I increased arraysize to 2000 still consistent gets/pIO is same. Give me some suggestions guys.Oracle 9i on Windows,Table Partitioned and Global partitioned Index.
Thanks:confused:
-
What is the setting of OPTIMIZER_INDEX_COST_ADJ in your init.ora file?
You should set this parameter to a low value (maybe 10).
Try it with your query:
Code:
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;
SELECT * FROM BUILDING_INFO WHERE BLD_SEQ_ID=21261023;
;)
-
Currently my Optimizer_Index_cost_adj is 100 and even after as per your sugegstions like setting it to low value- It did'nt helped.Anything else do I need to look into
1* ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10
Session altered.
120 rows selected.
Elapsed: 00:22:52.09
Statistics
----------------------------------------------------------
798 recursive calls
1 db block gets
6166391 consistent gets
6062185 physical reads
120 redo size
7247 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
38 sorts (memory)
0 sorts (disk)
120 rows processed
-
What's your partition key?
-
I'm sorry my partition key is the same BLD_SEQ_ID and this ID falls in last partition of the Index.My concern is if I give a hint for the same Index , retunrs result in 2 seconds. I'm confused.
-
> When I pass a hint of the same Index , it returns in seconds
> 1 recursive calls
ok, that's for parsing - if you execute the statment just using "/" a second time, that disappears. But that's neglectible.
> 1 db block gets
> 6,123,550 consistent gets
6.1 Mio x 8kb = 48.8 GB
How big is your table and how big is the index ?
Is this one partition or all partitions of index or table ?
> 6,058,079 physical reads
35 min = 2,100,000 ms / 6 mio phys reads = 0.35 ms/read => your operating system cache works fine :-) - but of course you should not have such much blocks accessed at all.
Please send this output (explain plan for...) for BOTH executions - with and without hint.
And what happens if you force it to a full-table scan ?
(I am just wondering if you get a INDEX-FULL scan or a Full-Table-Scan)
A full-table scan on a 6 GB table should 5 minutes (or 1 minute if you do it in parallel :-)
> 8144 redo size
==> That's most likeley from some delayed block cleanout from previous update changing more blocks than those which are "immediately on commit" cleaned.
But even if all 120 rows are in different blocks and you must visit the rollback-segment 120 times you should not get such many blocks to access.
> 8121 bytes sent via SQL*Net to client
> 120 rows processed
67 bytes/row (reasonable number)
> 9 SQL*Net roundtrips to/from client
Some of the 9 are for the parsing - but neglectible in our case
> 0 sorts (memory)
> 0 sorts (disk)
In your 2nd mail you post:
> 1* ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10
> Session altered.
> 120 rows selected.
> Elapsed: 00:22:52.09
> Statistics
> ----------------------------------------------------------
> 798 recursive calls
!!!! 798 !!!! recursive calls !!!!!
> 38 sorts (memory)
do you see parallel Query slaves ?
Once I did run into "parallel Index Range Scan" problem (after upgrade from 8.0.4 to 8.1.7 :-)) on an partitioned table.
Please make sure that you don't run into this situation
How do your data look like ?
select /*+ full(B) parallel(B 4) */ count(*), bld_seq_id B from building_info group by bld_seq_id order by 1;
(if data are not equally distributed -> Histograms...)
How small is your DB_BLOCK_BUFFER ? (used by optimizer to calculate index-cache-hit ratio)
OPTIMIZER_INDEX_COST_ADJ was already posted by LKBrwn_DBA
There is a second parameter:
OPTIMIZER_INDEX_CACHING default 0
(alter session set ...)
OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based
optimization to favor nested loops joins and IN-list iterators.
The cost of executing an index using an IN-list iterator or of executing a nested
loops join when an index is used to access the inner table depends on the caching of
that index in the buffer cache. The amount of caching depends on factors that the
optimizer cannot predict, such as the load on the system and the block access
patterns of different users.
You can modify the optimizer’s assumptions about index caching for nested loops
joins and IN-list iterators by setting this parameter to a value between 0 and 100 to
indicate the percentage of the index blocks the optimizer should assume are in the
cache. Setting this parameter to a higher value makes nested loops joins and IN-list
iterators look less expensive to the optimizer. As a result, it will be more likely to
pick nested loops joins over hash or sort-merge joins and to pick indexes using
IN-list iterators over other indexes or full table scans. The default for this parameter
is 0, which results in default optimizer behavior.
-
To quote Tom Kyte: "Autotrace lies".
http://asktom.oracle.com/pls/ask/f?p...31036568157622
http://asktom.oracle.com/pls/ask/f?p...:1560806455855
search for "autotrace lies"
Looking Beyond Execution Plans
The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:
The columns of the index being used
Their selectivity (fraction of table being accessed)
It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption. Use Oracle Trace or the SQL trace facility and TKPROF to examine individual SQL statement performance.
http://download-west.oracle.com/docs...x_plan.htm#838
-
6123550 consistent gets.. so many gets, doesnt it ring bell??
I am suspecting that the run time plan is going for Index Full Scan as against your explain pan showing Index Range Scan.. And when you give the hint, its obiviously going for Index Range scans and hence seeing o/p in 2 secs..
What you need to check is the SQL address and check the plan from v$sql_plan
Rgds
Abhay