RE: ORDER BY increases consistent gets?
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Apr 2004 11:59:14 -0700
A well-behaved query turns into a performance nightmare with the addition of an
order by statement. I too have seen this behavior, but only once. Is
licadmin.licmgr a base table or a view?
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
-----Original Message-----
From: Thomas A. La Porte [mailto:tlaporte@xxxxxxxxx]
Sent: Saturday, April 03, 2004 10:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORDER BY increases consistent gets?
Env: Oracle 8.1.7.4 on RedHat AS2.1 (2.4.9-e.35 kernel)
A developer brought me a query yesterday in which the introduction of an ORDER
BY clause alters the performance substantially.
The query is as follows:
SELECT reserve_host, usage,
to_char(request_time, 'MM/DD/YYYY HH24:MI:SS')
FROM licadmin.licmgr_requests
WHERE request_time > to_date('3/14/2004', 'MM/DD/YYYY')
AND release_time < to_date('1970', 'YYYY') ORDER BY request_time
Without the ORDER BY clause, the query requires about 100 consistent gets,
while with the ORDER BY clause it takes over 50000 consistent gets. According
to autotrace and tkprof the execution plans are the same [see below].
--
Thomas A. La Porte
<mailto:tlaporte@xxxxxxxxx>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
