selection from dual table

From: Denis <denis.mirchandani_at_gmail.com>
Date: 28 Apr 2005 05:57:29 -0700
Message-ID: <4e1806da.0504280457.72c4e2ff@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. Received on Thu Apr 28 2005 - 07:57:29 CDT