Re: Slow query

On Feb 7, 10:00 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "Sashi" <small..._at_gmail.com> wrote in message
>
> news:f9df77a7-e4dc-463f-a8d7-6f1fcf9ec5c9_at_e25g2000prg.googlegroups.com...
>
>
>
> > Hi all, I'm running a simple select by joining two tables.
> > I get an output of about 1.3 million rows:
>
> > SQL> SELECT count(*) from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF
> > N WHERE N.OBJ_ID=O.OBJ_ID;
>
> >  COUNT(*)
> > ----------
> >   1322139
>
> > When I run the select query as follows:
>
> > SELECT  UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
> > N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
> > from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
> > WHERE N.OBJ_ID=O.OBJ_ID
>
> > it takes almost three hours to run. The server is only hosting the
> > data server and the CPU usage is low.
>
> > Any pointers in helping me resolve this issue are appreciated.
> > Thanks in advance,
> > Sashi
>
> What is the explain plan?
>
> explain plan for
> SELECT  UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
>  N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
>  from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
>  WHERE N.OBJ_ID=O.OBJ_ID;
>
> select * from table(dbms_xplan.display)- Hide quoted text -
>
> - Show quoted text -

Sashi, are the statistics on these two tables current? Take a look at the last_analyzed columns of dba_tables and if either table has not been recently updated get the statistics updated.

Then rerun the explain plan and see if it changed.

Also for Oracle to not have to full table scan both tables then the objid column would have to be the leading column of an index for at least one of these tables. (Likely it is or should be the leading column or only column of an index on both but at least one of the two tables has to be full scanned to solve the query). It may still be possible that full scans on both and a hash join would be better than using an index but current statistics would be necessary for Oracle to make the right choice.

If the explain plan changed re-test (run and measure response time).

HTH -- Mark D Powell -- Received on Thu Feb 07 2008 - 09:12:21 CST