Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sincere Advice on Sql Plan - Thanks

Re: Sincere Advice on Sql Plan - Thanks

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 13 Oct 2004 09:45:16 -0700
Message-ID: <bf463804101309456159562@mail.gmail.com>


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_at_yahoo.com> 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!
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 13 2004 - 11:41:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US