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

I played with some data and was unable to get Oracle to exhibit this type of behavior. I'm intrigued as to what's causing it. Can you post the column statistics as well as the index statistics?

select column_name, num_distinct, num_nulls,   num_buckets, density
from user_tab_col_statistics
where table_name = 'PO'
  and column_name in ('POS_MWBT', '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', 'POS_ART') I'd also be interested to know if Oracle thinks there's a tie between these indexes. Can you try renaming PO_INDEX016 to PO_INDEX999 and explain the query again (Oracle breaks ties by index name)?

Richard

Richard Kuhler wrote:
>
> 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 - 17:35:54 CDT