Re: Minimize Performance Hit on Sort...Help!

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Mon, 8 Feb 2010 18:28:49 +0100
Message-ID: <486b2b611002080928x608caf8bh5b448fffa6955133_at_mail.gmail.com>

Hi Kellyn

Just one more thing, as it hasn't been mentioned here. You can use indexes to avoid sorts entirely in Oracle... This may not be 100% applicable here (I didn't dig into your setup), but I figured it was worth throwing into the loop:

sys_at_M> create table t tablespace users as select * from all_objects;

Table created.

sys_at_M> create index ti on t (object_id desc);

Index created.

sys_at_M> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

And then, without the index:

sys_at_M> select * from t order by object_id desc;

82408 rows selected.

Execution Plan

---

Plan hash value: 961378228

---

| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 82408 |  8530K|       |  2317   (1)|
00:00:28 |
|   1 |  SORT ORDER BY     |      | 82408 |  8530K|    11M|  2317   (1)|
00:00:28 |
|   2 |   TABLE ACCESS FULL| T    | 82408 |  8530K|       |   334   (1)|

00:00:05 |

---

Statistics

---

          1  recursive calls
          0  db block gets
       1196  consistent gets
          0  physical reads
          0  redo size
    4308020  bytes sent via SQL*Net to client
      60843  bytes received via SQL*Net from client
       5495  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      82408  rows processed

Note the 1 sorts (memory)

But with the index:

sys_at_M> select /*+ index (t ti) */ * from t order by object_id desc;

82408 rows selected.

Execution Plan

---

Plan hash value: 3230515022

---

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 82408 |  8530K|  4827   (1)|

00:00:58 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 82408 | 8530K| 4827 (1)| 00:00:58 |
| 2 | INDEX FULL SCAN | TI | 82408 | | 197 (1)| 00:00:03 |

---

Statistics

---

          1  recursive calls
          0  db block gets
      15501  consistent gets
         72  physical reads
          0  redo size
    4308020  bytes sent via SQL*Net to client
      60843  bytes received via SQL*Net from client
       5495  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      82408  rows processed

0 sorts (and the SORT ORDER BY is also not included in the execution plan).

Now if you can get your query to run in a decent speed, while using a sorted index. You might be able to achieve the same, and completely eliminate the SORT ORDER BY -- this may be lots faster than actually sorting the data, even if it reduces your query speed a bit.

Just my spontaneous CHF0.02 :-)

Cheers

Stefan

---

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Mon, Feb 8, 2010 at 6:11 PM, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:

> Oh Adar, you are sooooo preaching to the choir here... :) As a follow up,
> we rewrote the query completely and then I was able to convince them that
> sorting 1.3TB of data in Oracle was NOT a good idea. They are about to move
> the SAS servers from Windows to Linux, (yes, this was part of the reason to
> perform the sort on the database instead of in SAS...) and once they move
> over in the next month, they will be sorting on the SAS side, one way or the
> other.
> DBA said enough is enough, I don't care WHAT claims the last DBA's made...
> :)
>
> Kellyn Pedersen
> Multi-Platform DBA
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
> www.dbakevlar.blogspot.com
>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
> --- On *Sun, 2/7/10, Yechiel Adar <adar666_at_inter.net.il>* wrote:
>
>
> From: Yechiel Adar <adar666_at_inter.net.il>
>
> Subject: Re: Minimize Performance Hit on Sort...Help!
> To: kjped1313_at_yahoo.com
> Cc: "oracle Freelists" <oracle-l_at_freelists.org>
> Date: Sunday, February 7, 2010, 11:07 PM
>
>
> Remove the order by and tell them to sort the data in SAS.
>
> Adar Yechiel
> Rechovot, Israel
>
>
>
> Kellyn Pedersen wrote:
>
> IQuery with a order by that MUST be done in Oracle as the file is an
> output to a dataset in SAS.
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 08 2010 - 11:28:49 CST