RE: Unique Index Re-design
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- Date: Fri, 28 Mar 2014 16:09:41 +0000
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
@jloracle
________________________________
From: Mohamed Houri [mohamed.houri@xxxxxxxxx]
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.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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>:
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Mohamed Houri [mohamed.houri@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx>]
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
Other related posts:
