Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: selection from dual table
"Denis" <denis.mirchandani_at_gmail.com> wrote in message
news:4e1806da.0504280457.72c4e2ff_at_posting.google.com...
> Dear All,
>
> I have been observing a very strange behaviour in regards to the
> following SQL in my database, that i have not been able to explain:
>
> select TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS') from dual;
>
> This SQL should have a very low execution time, but takes 1 second to
> complete.
>
> The v$SQLAREA has the following entries for this SQL:
>
> LOADS : 274, PARSE_CALLS : 5157567, BUFFER_GETS : 25784870, EXECUTIONS
> : 5181218
>
> Why is the behaviour like this? why are there so many buffer gets
> involved with this query? What happens internally in Oracle when this
> call is made?
>
> Please advise.
>
> Thanks
>
> Regards,
> Denis Mirchandani.
This looks like 8i, so what method are you using to find out that it takes 1 second to complete ? Is this averaged information from a log, or one or two attempts you have made to run the same query from SQL*Plus ?
One possible reason, if your timing information
is correct, may be latch contention - if this is
5,000,000 executions in a relatively short time
then you could be experiencing really serious
issues on that one latch - and the fact that
a statement like that one could have been
flushed and reloaded 246 times despite
its popularity makes this seem a possibility.
(1 second is extreme - but I've seen worse).
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated April 5th 2005Received on Thu Apr 28 2005 - 09:14:07 CDT