Re: Unique Index Re-design
Date: Fri, 28 Mar 2014 16:18:52 +0100
Message-ID: <CAJu8R6jVNB7o8CeTAOJVXmftenp9XXzYSpH-x_wghdYMUa61qg_at_mail.gmail.com>
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 204548692778762
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 TotalWaited
- Waited ----------
Disk file operations I/O 13 0.000.00
db file sequential read 71008 0.16152.21
direct path write temp 209 0.040.46
direct path read temp 2106 0.041.02
buffer busy waits 10 0.000.00
log buffer space 7 0.020.11
latch: cache buffers chains 6 0.000.00
log file switch completion 12 0.100.24
log file switch (checkpoint incomplete) 2 1.83 1.84
log file sync 1 0.000.00
undo segment extension 2 0.010.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.exe
PLSQL 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>:
> > 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 [oracle-l-bounce_at_freelists.org] on > behalf of Mohamed Houri [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 >
-- Bien Respectueusement Mohamed Houri -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 28 2014 - 16:18:52 CET