Re: Sincere Advice on Sql Plan - Thanks
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Wed, 13 Oct 2004 18:54:22 +0100
Okay I am Going to Piggy Back on this ..
I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
to take a Better plan to execute it .. Any ideas why this is
happening.
There is a Total Change in the plan and it works much faster than the
ANSI Counterpart
Any help is appreciated.
Thanks.
Cheers
Ganesh R
On Wed, 13 Oct 2004 09:45:16 -0700, Jared Still <jkstill@xxxxxxxxx> wrote:
> Yup, lots of parsing.
>
> If you eliminate all the parsing, you will have saved
> .32 seconds on a 3.78 second query.
>
> Do you think the user will notice?
>
> The query is spending 2.17 seconds retrieving 26472 rows
> of data, only 3262 of which you are using.
>
> 26472 rows fetched / 120 fetches = 220 rows per block.
>
> 3262 rows retrieved / 120 fetches = 27 rows per block
>
> The data being retrieved is fairly well scattered across the table.
>
> Perhaps a different index is in order?
>
> Or maybe the query is limited by the design of the table?
>
> Or the query is malformed?
>
> Anyway, reducing IO would be in order here if possible.
>
> Lots of maybes.
>
> You might consider running a 10046 trace on the user running
> this form, and find out where and why the time is being used.
>
> Tkprof does not provided sufficient detail other than what is
> needed to drive lots of speculation. :)
>
>
> On Tue, 12 Oct 2004 11:42:38 -0700 (PDT), Sanjay Mishra
>
>
> <smishra_97@xxxxxxxxx> wrote:
> > I had one FOrms application running using Oracle 9iAS Releas1 and 9i Rel
> > 1DB. I got user complaining that one forms is very slow and after tracing I
> > got the following kind of TKPROF output for lot of queries
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 3262 0.32 0.41 0 0 0
> > 0
> > Execute 3262 0.97 1.19 0 0 0
> > 0
> > Fetch 3262 0.84 2.17 120 26472 0
> > 3262
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 9786 2.13 3.78 120 26472 0
> > 3262
> > I don't understand as why there is so much of Parsing. Can somebody advice
> > as what is this and why it is so.
> >
> > TIA
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > vote.yahoo.com - Register online to vote today!
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
