Re: Unique Index Re-design

From: Mohamed Houri <mohamed.houri_at_gmail.com>
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   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_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-l
Received on Fri Mar 28 2014 - 16:18:52 CET

Original text of this message