Re: Why doesn't my hint work?
Date: Tue, 3 Dec 2013 15:07:38 -0500
Message-ID: <CAPXEL0+DPM-m9rtKktdzAhTodWei7OQ-zgXAHTXH-rXqiZjOpA_at_mail.gmail.com>
Try cursor_sharing = SIMILAR and let adaptive cursor sharing take care of the data skew (you will need the histogram). Here is a primer http://www.oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1.php.
Thanks
Paresh
416-688-1003
On Tue, Dec 3, 2013 at 12:43 PM, Paul Houghton < Paul.Houghton_at_admin.cam.ac.uk> wrote:
> Thanks for your help!
>
> Incidentally, thanks to Mark - I was wondering if there was an optimum
> order to create the index where it was not dictated by the query. In the
> event I created all six possible indexes in a test system, and the
> clustering factor was very similar. This is definitely something I will
> bear in mind in the future.
>
> I gathered histograms and the plan did not change. By default Oracle chose
> not to gather a histogram on OPRID_SEC.OPRID. When I forced it to, the
> estimated cardinality was unaffected. The main problem seems to be that in
> this case it has to loop through less data than expected, i.e. less rows
> come out of the join that the optimiser expects. Hinting is bad because if
> I pick an oprid with more rows in OPRID_SEC the plan the optimiser picked
> originally is the best one, but a literal is used for oprid, so the
> optimiser will treat each oprid as a different query meaning we should not
> get the problems I am concerned about with histograms and bind variables in
> this case.
>
> At this stage I think I have come to the conclusion that the optimiser is
> probably doing the right thing. But to increase my knowledge of Oracle:
>
> Does anyone have any idea of a way to fix this with stats? (The oprid in
> this query is actually a literal).
>
> This is the plan forced with hints thanks to Gaja and Riyaj, with
> histograms in place as below.
>
>
> ----------------------------------------------------------------------------
> |Id|Operation |Name
> |Strt|ERows|ARows|ATime|Bufs|
>
> ----------------------------------------------------------------------------
> | 0|SELECT STATEMENT | | 1| |7548
> |00.08|4290|
> | 1| NESTED LOOPS | | 1| |7548
> |00.08|4290|
> | 2| NESTED LOOPS | | 1| 105K|7548 |00.04|
> 557|
> |*3| INDEX RANGE SCAN |OPRID_SEC | 1| 232 | 10 |00.01|
> 6|
> |*4| INDEX RANGE SCAN |EMP_APP_SEC_I| 10|1261 |7548 |00.03|
> 551|
> | 5| TABLE ACCESS BY INDEX ROWID|EMP_APP_SEC |7548| 455 |7548
> |00.03|3733|
>
> ----------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - access("A"."OPRID"=:1)
> 4 - access("A"."CAREER"="B"."CAREER" AND "A"."ORG"="B"."ORG" AND
> "B"."LATEST_ROW"='Y')
>
> Here is a representation of the skew in OPRID_SEC.
>
> SQL> select cnt, count(*) from (select oprid, count(*) as cnt from
> OPRID_SEC group by oprid) group by cnt
>
> CNT COUNT(*)
> ---------- ----------
> 1 98
> 2 201
> 3 405
> 4 739
> 5 455
> 6 254
> 7 10
> 8 252
> 9 71
> 10 213
> 11 1
> 12 185
> 14 2
> 15 38
> 16 53
> 18 7
> 20 38
> 21 7
> 24 17
> 25 5
> 27 1
> 28 10
> 30 22
> 32 10
> 33 23
> 35 1
> 36 7
> 39 5
> 40 12
> 42 2
> 44 8
> 45 1
> 48 10
> 50 7
> 52 3
> 55 2
> 56 3
> 60 8
> 63 1
> 70 3
> 72 2
> 78 1
> 80 1
> 84 1
> 85 1
> 86 1
> 88 2
> 92 10
> 96 10
> 99 1
> 100 2
> 104 2
> 110 1
> 112 1
> 115 1
> 120 4
> 130 4
> 132 1
> 135 3
> 140 5
> 144 32
> 148 1
> 152 1
> 165 1
> 180 13
> 196 24
> 204 1
> 210 4
> 213 1
> 216 5
> 280 7
> 284 1
> 304 1
> 308 2
> 392 74
> 588 32
> 784 473
> 980 94
> 1176 104
> 1372 140
> 1568 47
> 1764 35
>
>
> Histograms for relevant columns:
>
> SQL> select table_name, column_name, num_distinct, histogram from
> all_tab_col_statistics where owner = 'SYSADM' and table_name in
> ('OPRID_SEC','EMP_APP_SEC') order by table_name;
>
> TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
> ------------ ----------- ------------ ---------------
> EMP_APP_SEC EMPLID 258608 HEIGHT BALANCED
> EMP_APP_SEC CAREER 5 FREQUENCY
> EMP_APP_SEC LATEST_ROW 2 FREQUENCY
> EMP_APP_SEC ORG 160 FREQUENCY
> OPRID_SEC CAREER 9 FREQUENCY
> OPRID_SEC OPRID 4337 HEIGHT BALANCED
> OPRID_SEC ORG 196 FREQUENCY
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 03 2013 - 21:07:38 CET