I have a weird question ... what's the DB_FILE_MULTIBLOCK_READ_COUNT
set to? I've seen table scans and the like when it's set too high.
Thx!
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1020264625.16495.0.nnrp-12.9e984b29_at_news.demon.co.uk...
>
> One quick thought - are you sure that the
> types of the bind variables match the types
> of the columns - in particular the bind variable
> for
> >> WHERE ( POS_MWBT = :1 )
>
>
> Check also the avg_data_blocks_per_key
> and avg_leaf_blocks_per_key for the twi
> indexes, and the clustering_factor.
>
> If these are unrealistic you could try using
> dbms_stats.set_index_stats to write
> suitable stats to the indexes. (Much cheaper
> than running analyze every night).
>
> At what level are you running the analyze ?
> estimate, estimate percentage, compute ?
>
> How accurate are the low/high values on
> user_tab_columns that you get for the
> 5 columns in the where clause ?
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
> >"Joerg Jost" <jost_at_unitrade.com> wrote in message
> >news:g39tcu0i7oid3diilu9j13c8hdhioti8ag_at_4ax.com...
> >> 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
> >
> >
>
>
Received on Tue May 07 2002 - 01:12:33 CDT
