Re: Help! This group by stmt takes AGES

From: warren <warren.bal_at_devonport.co.uk>
Date: 29 Apr 2002 07:45:18 -0700
Message-ID: <22bec371.0204290645.1953cf13@posting.google.com>

frankzaum_at_yahoo.de (Frank Zaum) wrote in message news:<4c1e5bbe.0204261414.4ab35772_at_posting.google.com>...

> frankzaum_at_yahoo.de (Frank Zaum) wrote in message news:<4c1e5bbe.0204260540.5a186793_at_posting.google.com>...
> > "Stephan Bressler" <stephan.bressler_at_pdb.sbs.de> wrote in message news:<aab0o4$t1a$1_at_news.mch.sbs.de>...
> > > Hi Frank,
> > >
> > > did you analyzed space_values? I saw cases where the RBO selected a
> > > nested-loop with 2 full-table-scans. This is usually _very_ expensive. Even
> > > more if the inner table is a dictionary view.
> > > CBO (used if tables are analyzed) probably will choose a hash join for this
> > > query.
> > >
> > > Another solution might be to create a temporary copy of dba_tables, index it
> > > properly and use this table for your space evaluation.
> > >

> Hi Frank,

> What I usually do in a case like this is look at what the query is waiting for
> Log in run the query and find the SID for that session.
> Once you have the SID run the following query

> select sid, event,

       decode(state,'WAITING','WG','WAITING UNKNOWN','W UN',
                    'WAITED KNOWN TIME','W KN','WAITED SHORT TIME','W SH',
                    'WAITED','WD','*') state,
       seconds_in_wait siw,
       wait_time wt,
       p1, p2, p3

> from v$session_wait
> where sid = '&your_sid'
> order by event,sid,p1,p2;

> This will show you exactly what the query is waiting for e.g 'db file
> scattered read' event for a full table scan. You can then investigate why the > query is waiting for that
> event by looking at the P1,P2,P3 values - different for each wait event.
>
> If this particular query is waiting for a full table scan then an explain
> plan as somebody suggested will also prove very useful. However, the query
> could be waiting for sorts to disk or locks, the above query will show that.

> Run the query a number of times over the course of the 'problem querys' execution to see if the wait event changes.

HTH
> > > Regards
> > > Stephan
> >
> > hi again,
> >
> > yes i actually analyzed it (with all indexes, too) right before execution.
> >
> >
> > regards, frank zaum
>
> when i remove the group by , the query
> is pretty fast and returns the right result for the sum per tablespace.
> using count gives the right values.
>
> perhaps the statement can be changed to something not using the group by clause.
> but how?. in the end, i just want to select the values per day and tablespace,
> not the day itself (column datum contains trunc(sysdate))
> but i dont know how to figure the correct select statement for that.
>
>
> regards frank zaum
Received on Mon Apr 29 2002 - 09:45:18 CDT