long time for explain plan(execution path)

From: Michael Norbert <michaelnorbert_at_hotmail.com>
Date: 30 Jan 2002 08:38:22 -0800
Message-ID: <c2880966.0201300838.5eeb808d@posting.google.com>

Hello

HP-UX 9000 version 11.0
Oracle 8.0.5

I have users using jdbc thin client to access one of my databases. The = user clinks on a link which is a preformatted query(no bind) and a graph = is made. It's semi-production and I am in the process of migration to = 8.1.7.2 so I don't pay too much attention to this database but our whole = system will be heading this way. People are complaining about slow = queries so I decided to check a few of them out. The wait seems to be in = the execution plan for the statement. The query take 6.7 seconds with = the explain plan taking 6.61 seconds. I have a parse to execute ratio of = 30% and a shared_pool_size of 100 Mb with lots of room. Any ideas on how to reduce the parse time?

thanks
Mike(michaelnorbert_at_hotmail.com)

SQL> set timi on
SQL> set autotrace traceonly explain
SQL> @test

Elapsed: 00:00:06.61

Execution Plan

0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D5837 Card=3D1 =Bytes=3D29 8) 1 0 SORT (GROUP BY) (Cost=3D5837 Card=3D1 Bytes=3D298)

   2    1     NESTED LOOPS (Cost=3D5831 Card=3D1 Bytes=3D298)
   3    2       INDEX (RANGE SCAN) OF 'ADMIN_REPORTDATES_IX1' (NON-UNI
          QUE) (Cost=3D2 Card=3D1 Bytes=3D51)
   4    2       TABLE ACCESS (FULL) OF 'MRTG_INT_ROLL' (Cost=3D5829 Card
          =3D9 Bytes=3D2223)

Received on Wed Jan 30 2002 - 10:38:22 CST