If u can persuade them, create better ones. F.e.:
Issue this query to look for better indexes:
select u.name || '.' || ot.name "TABLE",
oi.name "INDEX",
ic.pos# "POS",
c.name "COLUMN",
h.distcnt "DIST VAL"
from sys.user$ u,
sys.obj$ ot,
sys.obj$ oi,
sys.hist_head$ h,
sys.col$ c,
sys.icol$ ic,
sys.ind$ i
where i.cols > 1
and i.obj# = ic.obj#
and i.bo# = ic.bo#
and i.bo# = c.obj#
and ic.col# = c.col#
and i.bo# = h.obj#
and ic.col# = h.col#
and ot.obj# = i.bo#
and oi.obj# = i.obj#
and u.user# = ot.owner#
and exists (select null
from sys.icol$ ic2,
sys.hist_head$ h2,
sys.icol$ ic3,
sys.hist_head$ h3
where ic2.obj# = ic.obj#
and ic2.bo# = ic.bo#
and ic2.bo# = h2.obj#
and ic3.obj# = ic.obj#
and ic3.bo# = ic.bo#
and ic3.bo# = h3.obj#
and ic2.col# = h2.col#
and ic3.col# = h3.col#
and ic2.pos# > ic3.pos#
and h2.distcnt > h3.distcnt)
order by 1, 2, 3
/
This it will tell you if the index could be better in
another sequence of fields.
Salu2.
- Veronica Levin <vlevin_at_victoria.com.ni> wrote:
> what would you do with an application that has 3 GB
> of data and 6 GB of
> indexes????
> developers keep telling me that they can't get rid
> of any index because they
> use them all..... how can I prove wich indexes are
> never used??
>
> Saludos,
> Veronica Levin Enriquez
> Administrador AIX
> Compañía Cervecera de Nicaragua
>
>
> -----Mensaje original-----
> De: Christian Trassens [mailto:ctrassens_at_yahoo.com]
> Enviado el: Martes, 07 de Agosto de 2001 01:26 p.m.
> Para: Multiple recipients of list ORACLE-L
> Asunto: Re: Optimizer Mode......how to choose the
> right one?
>
>
> NEVER change an optimizer since the application have
> some time working with it. I should leave it in
> CHOOSE
> and then analyze what are doing the transactions.
>
> Look up the worst events through v$system_Event or
> from time to time through v$session_wait. Issue an
> utlbstat/utlestat or statspack. Then when you know
> which is the worst wait. Maybe "db file scattered
> read" or most of the times "enqueue" or "latch free"
> and sometimes a huge amount of "db file sequential
> read" because of bad indexes.
>
> Look up the indexes. Doing that consider their
> clustering_factor, their blevel and the most
> important
> thing their selectivity. ETC.....
>
>
> --- Veronica Levin <vlevin_at_victoria.com.ni> wrote:
> > Morning listers!
> >
> > I am having performance problems with this
> database,
> > transactions ar running
> > very slow and I am not sure if I have choose the
> > right optimizer mode...
> >
> > AIX 4.2.1, Oracle 7.3.4, 30 GB Database, 1 GB Real
> > Memory,
> > 500 MB SGA, 70 concurrent users, mostly OLTP
> > transactions.
> >
> > I have tunned init parameters the best I can we
> the
> > resources I have:
> >
> > db_files = 70
> > db_writers = 4
> > db_file_multiblock_read_count = 16
> > db_block_buffers = 57600
> > db_block_size = 8192
> > shared_pool_size = 157286400
> > processes = 200
> > dml_locks = 1500
> > log_buffer = 655360
> > sequence_cache_entries = 800
> > sequence_cache_hash_buckets = 89
> > log_checkpoint_interval = 80000
> > optimizer_mode=CHOOSE
> > session_cached_cursors = 300
> > sort_area_size=1048576
> > hash_area_size=262144
> > hash_multiblock_io_count=4
> > hash_join_enabled=TRUE
> > always_anti_join=HASH
> > job_queue_processes=8
> >
> > 35 rollback segments, inital 1MB, next 1MB,
> optimal
> > 30 MB
> > 8 multiplexed redologs, 30 MB each
> >
> > I tried FIRST_ROWS, analyzing the tables, but
> users
> > claimed that performance
> > was getting worse, so I chaged it to Choose.
> Always
> > analyzing the tables....
> >
> > but, everytime I analyze the tables, performance
> > gets worse.
> > Is this a normal behavior?
> >
> > Any advice will be welcome!
> >
> > thanks is advance,
> >
> > Saludos,
> > Veronica Levin Enriquez
> > Administrador AIX
> > Compañía Cervecera de Nicaragua
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Veronica Levin
> > INET: vlevin_at_victoria.com.ni
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> =====
> Eng. Christian Trassens
> Senior DBA
> Systems Engineer
> ctrassens_at_yahoo.com
> ctrassens_at_hotmail.com
> Phone : 541149816062
>
> __________________________________________________
> Do You Yahoo!?
> Make international calls for as low as $.04/minute
> with Yahoo! Messenger
> http://phonecard.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Christian Trassens
> INET: ctrassens_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Veronica Levin
> INET: vlevin_at_victoria.com.ni
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
INET: ctrassens_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Aug 07 2001 - 15:36:16 CDT
