Query Slow when adding ORDER BY clause

[daph]

Hi guys,

I have a simple query using the following table:

 COL1         NUMBER(5)
 COL2         NUMBER(3)
 COL3         NUMBER(4)
 COL4         VARCHAR2(20)
 COL5         VARCHAR2(200)
 COL6         VARCHAR2(200)
 COL7         NUMBER(8,2)
 COL8         NUMBER(8,2)
 COL9         NUMBER(8,2)
 COL10        NUMBER(8,2)
 COL11        NUMBER(8,2)
 COL12        NUMBER(8,2)
 COL13        NUMBER(8,2)
 COL14        DATE
 COL15        DATE
 COL16        DATE
 COL17        DATE
 COL18        DATE
 COL19        NUMBER(10)
 COL20        DATE
 COL21        NUMBER(10)
 COL22        DATE

This table contains about 25000 records.

If I use the following query:

SELECT col1, col2, col3 FROM my_table;

The data returns in an instant. But if I add the clause ORDER BY COL22 DESC, it takes like 30 seconds. I've tried using hints to see if it would help but no luck. This never happened to me before and the query isn't very complicated. Anybody has any idea what the problem could be?

Thanks a lot!

Daph

 

[SantaMufasa]

Daph,

Without the ORDER BY clause, your Oracle dedicated server needs only to dump block contents straight to output, regardless of any logical order...That's pretty simple for Oracle.

The ORDER BY (presuming that the blocks containing the 25000 rows cannot fit into memory all at once) causes the following to occur:

1) Confirm that the necessary blocks are in the database buffer cache (which happens with or without the ORDER BY).
2) Write the sort data to the TEMP tablespace.
3) Re-order (sort) the data in the TEMP tablespace.
4) Output the data to the display device (whi8ch happens with or without the ORDER BY).

So, Steps 2 & 3 are causing your extra 30 seconds of elapsed time. Depending upon the average row size, then, yes, that could be reasonable.

I would, however, do a sanity check on your execution plan.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

Do you also have a WHERE clause? Remember, many functions (substr, to_char, etc.) will often suppress index usage.

Can you share your ORDER BY clause and the indexes that are on this table?

Mufasa is right - run an explain plan and see how your data is being gathered.


[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[daph]

Hi guys,

Thanks for the quick response. I appreciate it. There is no WHERE clause or any functions in the query. It's a very simple query.

I ran tkprof. Here are the results from that.

Without ORDER BY clause:

call    count  cpu   elapsed  disk query current rows
------  ------ ----  -------- ---- ----- ------- ------
Parse   1      0.00  0.00     0    0     0       0
Execute 1      0.00  0.00     0    0     0       0
Fetch   1728   0.35  0.52     24   2178  4       25896
------  ------ ----  -------- ---- ----- ------- -------
total   1730   0.35  0.52     24   2178  4       25896

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22  

Rows   Row Source Operation
-----  -------------------------------------------------
25896  TABLE ACCESS FULL QL_REPS_DATA

With ORDER BY clause:

call    count  cpu   elapsed  disk  query current  rows
------  -----  ----  -------  ----- ----- -------  -----
Parse   1      0.00  0.01     0     0     0        0
Execute 1      0.00  0.00     0     0     0        0
Fetch   1728   0.73  7.99     728   480   47       25896
------  -----  ----- -------- ----- ----- -------  -----
total   1730   0.73  8.00     728   480   47       25896

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22  

Rows     Row Source Operation
-------  ----------------------------------------------
  25896  SORT ORDER BY 
  25896   TABLE ACCESS FULL QL_REPS_DATA

 

[sem]

Most probably you're sorting in permanent rather than temporary tablespace. Check whether your user temporary tablespace is really temporary. Check also sort_area_size.

Regards, Dima

http://seminihin.narod.ru