RE: Unique Index Re-design
Date: Fri, 28 Mar 2014 16:09:41 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE6627_at_exmbx05.thus.corp>
The rowsource stats from the tkprof output and the monitoring report say the same thing - you identify a lot of random data very accurately using that index, with little wasted effort, and there's not a lot you can do about it. You see more reads on the index than on the table because the query is "recent data" so all, currently, in the "new" end of the table. but the index scatters wagh_ids uniformly across the whole index.
The plan shows that the optimizer is unnesting the subquery, then doing complex view merging, then postponing the aggregation (the IN subquery becomes an inline "select distinct" view, but then changed from "aggregate then join" to "join then aggregate") and that's why the HASH UNIQUE comes at the end of the run, and why the data size grows (in the tkprof, for example) from 2 thousand to 110 thousand then drops back to 5 thousand). You can see that the optimizer has made a complete mess of the cardinality estimates (from tkprof).
Your best bet is probably to put a no_merge into the subquery as this (I think) will aggregate early and then join, and that may do a lot less work.
The change from SYSDATE in the subquery to :B1 in the main query looks a little suspect, by the way.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Mohamed Houri [mohamed.houri_at_gmail.com] Sent: 28 March 2014 15:18
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: Unique Index Re-design
Jonathan,
My questions took its motivation from an Oracle overnight batch job I have been asked to tune. When this job has been traced and ‘tkprofed’ it shows a consuming insert/select statement
SQL ID: 89h295xmu825c
Plan Hash: 984770912
INSERT INTO X604_CHECK_WAGON_BUSES SELECT :B2 , WD.WAGD_ID, WH.WAGH_ID,
WD.WAG_ID, WD.TRANSIT, WD.CHECK_REPERAGE, WD.FROM_BUSES_NUM,
WD.FROM_BUSES_START_DATE, WH.PCAR_ID, WD.FROM_PCAR_ID FROM
X604_WAGON_HISTORIC WH, X604_WAGON_DETAILS WD WHERE WH.WAGD_ID = WD.WAGD_ID
AND :B1 BETWEEN WH.ARRIVAL_DATE AND WH.DEPARTURE_DATE AND CHECK_REPERAGE IN
(0,-1,-2) AND PDES_ID IS NULL AND WD.WAG_ID IN (SELECT WD.WAG_ID FROM
X604_WAGON_HISTORIC WH, X604_WAGON_DETAILS WD WHERE WH.WAGD_ID = WD.WAGD_ID
AND SYSDATE BETWEEN WH.ARRIVAL_DATE AND WH.DEPARTURE_DATE AND WH.PCAR_ID =
:B3 )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 304 256.51 415.13 106729 73943132 20454869 2778762 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 305 256.51 415.13 106729 73943132 20454869 2778762
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 110 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=519982 pr=7 pw=0 time=1365086 us)
5006 VIEW VM_NWVW_2 (cr=519588 pr=7 pw=0 time=1268253 us cost=3119 size=41923 card=371)
5006 HASH UNIQUE (cr=519588 pr=7 pw=0 time=1267376 us cost=3119 size=43407 card=371)
115340 NESTED LOOPS (cr=519588 pr=7 pw=0 time=1180115 us) 115340 NESTED LOOPS (cr=404401 pr=7 pw=0 time=896361 us cost=3118 size=43407 card=371) 115340 NESTED LOOPS (cr=172999 pr=6 pw=0 time=408008 us cost=1703 size=35400 card=472) 2259 NESTED LOOPS (cr=4107 pr=6 pw=0 time=74710 us cost=283 size=4386 card=129) 2259 TABLE ACCESS BY INDEX ROWID X604_WAGON_HISTORIC (cr=207 pr=0 pw=0 time=3436 us cost=25 size=2838 card=129) 2259 INDEX RANGE SCAN X604_WAGH_PCAR_ARR_DEP_NI (cr=20 pr=0 pw=0 time=1294 us cost=5 size=0 card=129)(object id91157) 2259 TABLE ACCESS BY INDEX ROWID X604_WAGON_DETAILS (cr=3900 pr=6 pw=0 time=21427 us cost=2 size=12 card=1) 2259 INDEX UNIQUE SCAN X604_WAGD_PK (cr=1641 pr=6 pw=0 time=16673 us cost=1 size=0 card=1)(object id 91153) 115340 TABLE ACCESS BY INDEX ROWID X604_WAGON_DETAILS (cr=168892 pr=0 pw=0 time=397714 us cost=11 size=164 card=4) 174081 INDEX RANGE SCAN X604_WAGD_WAG_FK_I (cr=5131 pr=0 pw=0 time=64678 us cost=2 size=0 card=9)(object id 91154) 115340 INDEX RANGE SCAN X604_WAGH_ARRIVAL_DATE_UK (cr=231402 pr=1 pw=0 time=439122 us cost=2 size=0 card=1)(object id 91155) 115340 TABLE ACCESS BY INDEX ROWID X604_WAGON_HISTORIC (cr=115187 pr=0 pw=0 time=224311 us cost=3 size=42 card=1) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 13 0.00 0.00 db file sequential read 71008 0.16 152.21 direct path write temp 209 0.04 0.46 direct path read temp 2106 0.04 1.02 buffer busy waits 10 0.00 0.00 log buffer space 7 0.02 0.11 latch: cache buffers chains 6 0.00 0.00 log file switch completion 12 0.10 0.24 log file switch (checkpoint incomplete) 2 1.83 1.84 log file sync 1 0.00 0.00 undo segment extension 2 0.01 0.01 ********************************************************************************
One execution if this insert took more than 5 seconds so that it has been monitored as shown below (you need to copy past in another document to have a nice formatting)
I am not sure if this insert corresponds to the time where the job has been traced. Because I asked to have its SQL monitoring report the day after. And franckly speaking when I saw the Module/Action I realized that the next monitored insert has been taken in another time frame and not during the same batch job
SQL Monitoring Report
SQL Text
Global Information
Status : DONE Instance ID : 1 Session : A107 (903:1545) SQL ID : 89h295xmu825c SQL Execution ID : 16784889 Execution Started : 03/27/2014 04:34:09 First Refresh Time : 03/27/2014 04:34:13 Last Refresh Time : 03/27/2014 04:34:16 Duration : 7s Module/Action : w3wp.exe/- Service : IOICTR3P Program : w3wp.exePLSQL Entry Ids (Object/Subprogram) : 91235,2 PLSQL Current Ids (Object/Subprogram) : 91243,4
Binds
| Name | Position | Type | Value | ======================================================================================================================== | :B1 | 2 | DATE | 03/27/2014 04:34:09 | | :B3 | 3 | NUMBER | 1034 | ========================================================================================================================
Global Stats
| Elapsed | Cpu | IO | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
| 6.96 | 0.43 | 6.54 | 95667 | 1075 | 8MB |
SQL Plan Monitoring Details (Plan Hash Value=984770912)
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ================================================================================================================================================================================================== | 0 | INSERT STATEMENT | | | | | | 1 | | | | | | | | 1 | LOAD TABLE CONVENTIONAL | | | | 1 | +7 | 1 | 0 | 142 | 1MB | | 14.29 | Cpu (1) | | 2 | VIEW | VM_NWVW_2 | 372 | 3146 | 1 | +7 | 1 | 5400 | | | | | | | 3 | HASH UNIQUE | | 372 | 3146 | 4 | +4 | 1 | 5400 | | | 1M | | | | 4 | NESTED LOOPS | | | | 4 | +4 | 1 | 9887 | | | | | | | 5 | NESTED LOOPS | | 372 | 3145 | 4 | +4 | 1 | 9887 | | | | | | | 6 | NESTED LOOPS | | 472 | 1729 | 4 | +4 | 1 | 9963 | | | | | | | 7 | NESTED LOOPS | | 131 | 287 | 4 | +4 | 1 | 1254 | | | | | | | 8 | TABLE ACCESS BY INDEX ROWID | X604_WAGON_HISTORIC | 131 | 25 | 4 | +4 | 1 | 1254 | | | | | | | 9 | INDEX RANGE SCAN | X604_WAGH_PCAR_ARR_DEP_NI | 131 | 5 | 4 | +4 | 1 | 1254 | | | | | | | 10 | TABLE ACCESS BY INDEX ROWID | X604_WAGON_DETAILS | 1 | 2 | 4 | +4 | 1254 | 1254 | | | | | | | 11 | INDEX UNIQUE SCAN | X604_WAGD_PK | 1 | 1 | 5 | +3 | 1254 | 1254 | 42 | 336KB | | 14.29 | db file sequential read (1) | | 12 | TABLE ACCESS BY INDEX ROWID | X604_WAGON_DETAILS | 4 | 11 | 4 | +4 | 1254 | 9963 | | | | | | | 13 | INDEX RANGE SCAN | X604_WAGD_WAG_FK_I | 9 | 2 | 7 | +1 | 1254 | 26529 | 109 | 872KB | | 14.29 | db file sequential read (1) | | 14 | INDEX RANGE SCAN | X604_WAGH_ARRIVAL_DATE_UK | 1 | 2 | 6 | +2 | 9963 | 9887 | 277 | 2MB | | 57.14 | db file sequential read (4) | | 15 | TABLE ACCESS BY INDEX ROWID | X604_WAGON_HISTORIC | 1 | 3 | 4 | +4 | 9887 | 9887 | | | | | | ==================================================================================================================================================================================================
My unique index is the one I have highlighted in yellow where 57% of Activity has been done within this index range scan. This index comes from the table X604_WAGON_HISTORIC and the my two indexed columns are (WAGD_ID, ARRIVAL_DATE)
Best regards
Mohamed Houri
2014-03-28 11:42 GMT+01:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>:
In principle if you hide the 90996518 values you're only going to reduce the volume of the index by 5% - which doesn't sound like the solution to "a lot of" single block reads. Why are you seeing a lot of reads ? What's the nature of the queries (or DML) that causes them to happen ? When you say this index is "generating" a lot of single block reads, do you mean that index blocks are read randomly, or that table blocks identified by the index are read randomly ?
Looking at the states (8,000 to 9,000 rows per date) I'd guess that inserts (or updated) for a date may be happening around the same time requiring a constant volume of random reads of the index to find the blocks that need updating. But are the queries then: fetch me everything for a date, fetch me everything for an "a" value, or fetch me an "a" value across a range of dates ? And what queries do you have that address the nulls in the data column ? Is some of you db file sequential read the result of index fast full scans where lots of index blocks are already in memory ?
Based on details supplied so far I'd be considering the two indexes (b,a) for the uniqueness and (a) for precision when only (a) has been supplied in predicate (knowing, of course) that there should be no production queries for a=90996518 - but that's just based on a few guesses about what the system might be trying to achieve.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Mohamed Houri [mohamed.houri_at_gmail.com<mailto:mohamed.houri_at_gmail.com>] Sent: 28 March 2014 09:53DML)
To: ORACLE-L
Subject: Unique Index Re-design
I don’t know if this is a good question worth an answer or not; nevertheless I couldn’t resist the temptation to ask it. I have a real life unique two columns composite index ind_uk (a,b) having the following picture
select num_rows, distinct_keys, clustering_factor from all_indexes where index_name = ‘IND_UK’;
num_rows, distinct_keys, clustering_factor 1,350,375 1,350,375 146,386
The data repartition of these two indexed columns are
select a, count(1)
from table_t
group by a
order by count(1) desc;
shows one unique extreme count
a count(1) 90996518 67977 ---> this is my concern 106628306 8 104585295 8 105558779 8 105243015 8 84407427 8 106183944 7 … 73262355 1 73262392 1 73393305 1 73393309 1 73393325 1 73469367 1
The majority of the remaining records are with count = 1
select
b
, count(1)
from table_t
group by b
order by count(1) desc;
b count(1) null 432500 ---> this is my concern 13/11/2013 00:00:00 9075 14/11/2013 00:00:00 9030 08/11/2013 00:00:00 8780 15/11/2013 00:00:00 8721 12/11/2013 00:00:00 8060 19/11/2013 00:00:00 7772 22/11/2013 00:00:00 7696
21/11/2013 00:00:00 7618
26/11/2013 00:00:00 7539
…
Etc..
This index when used by the CBO is generating a lot of time consuming db file sequential read wait events
When I asked the client what is the particularity of this a value (90996518) he answered that this a dummy value used for testing (yes for testing in PRODUCTION).
My question finally is: I want to reengineer this index so that (a) it will still be unique (b) do not contain a = 90996518 value and (c) do not contains column b having null values.
I created the following index to honor my question
create unique index mho_ind on t4 (case when a = 90996518 then null else a end, case when b is not null then b end);
Have you any other suggestions?
Thanks
Mohamed Houri
www.hourim.wordpress.com<http://www.hourim.wordpress.com/>
-- Bien Respectueusement Mohamed Houri -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 28 2014 - 17:09:41 CET