Re: Using DBMS_STATS makes query execute WORSE.

groups_at_karsten-schmidt.com (Karsten Schmidt) wrote in message news:<c6711ac4.0410042343.75015099_at_posting.google.com>...
> Hi,
> you can try to play around with optimizer plan stability.
> i.e. create a stored outline with the correct (i.e. indexed)
> execution plan,
> and use a logon trigger to to the approbriate 'alter session
> use_stored_outlines' to force that execution plan for that particular
> query.
>
> I believe there is even a gui in enterprise manager that lets you
> graphically edit the stored outline.
>
> Hope that helps
> Karsten
>
> obimbus_at_poczta.onet.pl (Alek Lapuc) wrote in message news:<7d3c208.0410040041.2fa7b8e9_at_posting.google.com>...
> > Hi.
> >
> > This subject returned from time to time on this group.
> > I have searched the archive of groups on goole -- but suggested
> > anwsers helped me little.
> > I cannot add any hints to the statement nor tell DB to use specific
> > index. Query is hardcoded in application, that is delivered as
> > out-of-the-box program with no source code. (To answer an obvious
> > question: yes, we have a technical support license, however we failed
> > to convince them that this is a bug.)
> >
> >
> > I have the following problem:
> >
> > We have a Oracle 9.2.0 database on our system. There is a bunch of
> > data in it (at this moment more than 150 GigaBytes) in approx. 500
> > tables.
> >
> > And there is a problem with one querry on one table. Let's call the
> > table TABLE_T.
> >
> > Table TABLE_T has something around 36,000,000 (thirty six million)
> > rows.
> > There are several columns, but one of them, let's call it COLUMN_C, is
> > the most significant. It is of VARCHAR2(255) type. Table holds 4
> > (four) different values in this column. Each value is in around 25% of
> > rows (giving around 8,000,000 rows per value).
> >
> > One of client application performs a following query every time it
> > starts:
> >
> > SELECT DISTINCT a, b, c, d, COLUMN_C
> > FROM TABLE_T
> > WHERE TABLE_T.COLUMN_C = :1;
> >
> > Initially there was no index on COLUMN_C -- and the query resulted in
> > table scan, which is expected. Time of execution: approx. 15-20
> > minutes.
> >
> > Than we have added nonunique index on COLUMN_C. Time of execution
> > shrinked to approx. 2-3 minutes, which is acceptable.
> >
> > But then again we have estimated statistics with following query: (in
> > fact statistics were estimated for all 500 tables with statements like
> > following)
> >
> > BEGIN
> > SYS.DBMS_STATS.GATHER_TABLE_STATS (
> > OwnName => 'DBUSER'
> > ,TabName => 'TABLE_T'
> > ,Estimate_Percent => 33
> > ,Degree => 4
> > ,Cascade => TRUE
> > ,No_Invalidate => FALSE);
> > END;
> > /
> >
> > After that optimizer always chooses to do full scan on TABLE_T,
> > ignoring the index on COLUMN_C.
> >
> > It cannot startup time longer than 10 minutes -- this is a timeout in
> > a clustered system after which a program gets launched again. The
> > previous copy is being killed -- so as a result the program never
> > starts (and we have orphaned table full scans in DB...)
> >
> > To end in an optimistic way, two things helped:
> >
> > 1. Deleting estimated statistics on TABLE_T. Index on COLUMN_C is used
> > again. However having statistics gathered for all the table is
> > suggested by the supplier of client applications that we are
> > deploying/customizing.
> >
> > 2. Setting a nonunique index on ALL columns of TABLE_T in order that
> > is used in the mentioned query (a, b, c, d, COLUMN_C). This is not
> > acceptable from obvious reasons ;)
> >
> > If someone should have any suggestions I would be more than gratful to
> > hear them.
> >
> > Best regards,
> >
> > Aleksander Lapuc

Hi,

if disk space doesn't matter try to satisfy the data retrieval complete over the index and avoid table lookups:

Create index INDX on (COLUMN_C , a, b, c, d) ...

You can use this with other options mentioned in the answers...(complete stats, COMPRESS )

hope that helps

 YK Received on Thu Oct 07 2004 - 04:51:35 CDT