Re: How to get Oracle to an Index without hints?

Since bind variables are being used, I don't believe Oracle can calculate selectivity (no column stats or histograms are used). As I read it, "the optimizer heuristically guesses a small value for the selectivity." Furthermore, each expression should compound the selectivity for the index prefixed columns. Given that, Oracle should desire the addition of the POS_MWBT expression with that index. I would guess that the index statistics are skewing Oracle towards using the undesired index (maybe a significantly lower clustering factor). Can you post the index statistics?

select index_name, blevel, leaf_blocks,   distinct_keys, avg_leaf_blocks_per_key,   avg_data_blocks_per_key, clustering_factor from user_indexes
where index_name in ('PO_INDEX016', 'PO_INDEX018')

One other possibility is that Oracle has just given up... "the plan generator uses an internal cutoff to reduce the number of plans it tries". However, I doubt that's the case given the simplicity of this query.

Richard

Frank wrote:
>
> Daniel Morgan wrote:
>
> > Joerg Jost wrote:
> >
> >
> >>Hi there,
> >>
> >>we are a company that are developing an ERP - Software with Unify -
> >>Vision Environment
> >>
> >>We have a performance problem with a "normal" select - statement.
> >>
> >>Here the select - statement:
> >>SELECT
> >>POS_ALTART, POS_ALTNUM, POS_ALTPOS, POS_ART, POS_AUSW, POS_BERMG,
> >>POS_BERVK, POS_BESTF, POS_BESTKZ, POS_BETRAG, POS_BETRAG2, POS_BEZ1,
> >>POS_BEZ2, POS_BKEY, POS_CNT, POS_CSBS, POS_DAT, POS_DRUMG,
> >>POS_EINHEIT, POS_EK, POS_EKVBO, POS_GEW, POS_ISTAW, POS_ISTMG,
> >>POS_ISTVK, POS_KOEK, POS_KOMNR, POS_KOND, POS_KOSTST, POS_KOVK,
> >>POS_KZ, POS_LFBED, POS_LFID, POS_LIEFD, POS_LISTE, POS_MATBAS,
> >>POS_MERK, POS_MWBT, POS_MWNO, POS_MWST, POS_NUM, POS_PABW, POS_PLMIN,
> >>POS_POS, POS_PREIN, POS_RABAT, POS_RGEK, POS_ROHDM, POS_RSVW,
> >>POS_SKF, POS_SKTFHG, POS_SOFUEB, POS_SOLLAW, POS_SPANNE, POS_STATUS,
> >>POS_STPL, POS_TECH, POS_USER, POS_VOL, POS_VORMG, PO_AT_AT_NO,
> >>PO_AT_AT_SE_SE_AG_AG_NO, PO_AT_AT_SE_SE_AG_AG_WG, PO_AT_AT_SE_SE_NO,
> >>ROWID
> >>FROM
> >>PO
> >>WHERE ( POS_MWBT = :1 )
> >>AND ( PO_AT_AT_NO = :2 )
> >>AND ( PO_AT_AT_SE_SE_AG_AG_NO = :3 )
> >>AND ( PO_AT_AT_SE_SE_AG_AG_WG = :4 )
> >>AND ( PO_AT_AT_SE_SE_NO = :5 )
> >>ORDER BY pos_mwbt ASC, po_at_at_se_se_ag_ag_wg ASC,
> >>po_at_at_se_se_ag_ag_no ASC, po_at_at_se_se_no ASC, po_at_at_no ASC,
> >>pos_dat DESC
> >>
> >>Sorry for the tremendous number of variables, but we have to fill out
> >>a screen with the infos :-)
> >>
> >>The problem is, we have an index on table po which looks like the
> >>following:
> >>INDEX_NAME COLUMN_NAME COLUMN_POSITION
> >>
> >>PO_INDEX018 POS_MWBT 1
> >>PO_INDEX018 PO_AT_AT_SE_SE_AG_AG_WG 2
> >>PO_INDEX018 PO_AT_AT_SE_SE_AG_AG_NO 3
> >>PO_INDEX018 PO_AT_AT_SE_SE_NO 4
> >>PO_INDEX018 PO_AT_AT_NO 5
> >>
> >>But this index won´t be used by oracle to fetch the data.
> >>Instead of po_index018 oracle decides to use po_index016 which looks
> >>like the following:
> >>
> >>INDEX_NAME COLUMN_NAME COLUMN_POSITION
> >>
> >>PO_INDEX016 PO_AT_AT_SE_SE_AG_AG_WG 1
> >>PO_INDEX016 PO_AT_AT_SE_SE_AG_AG_NO 2
> >>PO_INDEX016 PO_AT_AT_SE_SE_NO 3
> >>PO_INDEX016 PO_AT_AT_NO 4
> >>PO_INDEX016 POS_ART 5
> >>
> >>Which index is used by oracle to get the data is figured out with the
> >>tkprof - utility.
> >>
> >>My question is:
> >>The table po is normally very big (around 1,000,000 - 15,000,000
> >>records)
> >>The analyze table statement is running every night for this table.
> >>But the sql - statement needs a lot of time to get the data. And even
> >>the rest of the system is getting slow, because the hard disks are
> >>very busy and effects the hole System.
> >>Unfortunately with the Unify Vision environment we are not able to put
> >>hints in the SQL - Statement.
> >>How do i have to design the index exactly for this statement.
> >>
> >>Thx a lot for your help
> >>
> >>Joerg Jost
> >>
> >
> > What I don't understand about your question is why, in your Subject, you
> > reject the simplest solution to the problem. Why don't you want to try
> > using a Hint?
> >
> > Daniel Morgan
> >
> >
>
> Because he's using a tool that generates SQL; it does so 'on the fly',
> as he stated in his fore-last line...
> How about:
> - analyzing the index (sounds obvious, but is it done?)
> - using histograms on your index (is your data evenly distibuted?)
> - rewriting the where clause to:
> WHERE ( PO_AT_AT_NO = :2 )
> AND ( PO_AT_AT_SE_SE_NO = :5 )
> AND ( PO_AT_AT_SE_SE_AG_AG_NO = :3 )
> AND ( PO_AT_AT_SE_SE_AG_AG_WG = :4 )
> AND ( POS_MWBT = :1 )
>
> provided your tool allows you to do that.
> If all else fails - replace tool!
Received on Tue Apr 30 2002 - 15:00:26 CDT