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

But it's all about performance. Isn't it. If index access path in your query is faster then oracle would have choose in first place.

Hi Kellyn,

What does tkprof says. Can you please post tkprof along with waits

On Mon, Feb 8, 2010 at 11:28 AM, Stefan Knecht <knecht.stefan_at_gmail.com>wrote:

> 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.
>>
>>
>>
>

-- 
Thanks & Regards,
Taral Desai

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