Re: large SORT_AREA_SIZE usefullness

From: Johnson Poovathummoottil <joni_65_at_yahoo.com>
Date: Thu, 21 Jun 2001 10:06:00 -0700
Message-ID: <F001.003323D6.20010621100953@fatcity.com>

I did sql_trace and submitted the trace file for analysis and got the results that are pasted below ofr both statements.

I do not find the advice very usefull as I am trying to improve the sorting.

Greg Moore <sqlgreg_at_pacbell.net> wrote:
> > the queries which used the larger sort_area_size
> > actually ran slower
>
> Why don't you run a trace for the small
> sort_area_size, do the same
> with the larger sort_area_size, and then submit the
> trace files to that web
> site
>
http://www.unal-bilisim.com/products/itrprof/itrprof.html
> to see what
> analysis and advice you get.
>
> I'm sure many of the people on this list are curious
> about this itrprof tool
> and would be interested in hearing if it helps you
> answer this question.
> You could probably put the output up to the list and
> get advice, especially
> since the guy who runs the site is on this list.
> I'd love to see a
> real-life example of this tool in action.
>

Here goes the advice for statement with Large SORT_AREA_SIZE .

itrprof SQL Analyzer 1.0.0 ANALYSIS RESULTS

STATEMENT alter session set sql_trace = true

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 0.00 0.00 0.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 2 0.00 0.01 0 0 0 1 0
Exec 3 0.00 0.17 0 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0 0
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 5 0.00 0.18 0 0 0 1 0

STATEMENT alter session set sort_area_size = 86M

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 0.00 0.00 0.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 0 0.00 0.00 0 0 0 0 0
Exec 0 0.00 0.00 0 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0 0
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 0 0.00 0.00 0 0 0 0 0

ERRORs FOR STATEMENT

Type Code Count
------ ------ ------
Parse 2248 1

STATEMENT select 'x' from dual

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 0.00 0.00 0.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 2 0.00 0.07 0 0 0 0 0
Exec 2 0.00 0.00 0 0 0 0 0
Fetch 2 0.00 0.00 0 2 8 0 2
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 6 0.00 0.07 0 2 8 0 2

EXECUTION PLANs FOR STATEMENT

Parse number : 0
Parsing User ID : 158
Relative time started : 75,419
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID

1 TABLE ACCESS FULL DUAL 195 STATEMENT alter session set sort_area_size = 88084384

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 0.00 0.00 0.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 2 0.00 0.00 0 0 0 1 0
Exec 2 0.00 0.00 0 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0 0
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 4 0.00 0.00 0 0 0 1 0

STATEMENT alter session set sort_multiblock_read = 4

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 0.00 0.00 0.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 0 0.00 0.00 0 0 0 0 0
Exec 0 0.00 0.00 0 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0 0
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 0 0.00 0.00 0 0 0 0 0

ERRORs FOR STATEMENT

Type Code Count
------ ------ ------
Parse 2248 1

STATEMENT alter session set sort_multiblock_read_count = 4

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 0.00 0.00 0.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 1 0.00 0.00 0 0 0 1 0
Exec 1 0.00 0.00 0 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0 0
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 2 0.00 0.00 0 0 0 1 0

STATEMENT select count(distinct(a.rcpt_id)) users from claims_fact a,bdgt_off_serv_grp_dim b where
a.bdgt_off_serv_grp_code=b.bdgt_off_serv_grp_code and a.ADJCTN_DATE>= to_date('01/01/2001','mm/dd/yyyy')

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT Event Elapsed in Statement(%) in Overall(%)

------  ----------  ----------------  --------------

CPU 1,516.22 100.00 100.00
Wait 0.00 0.00 0.00

------  ----------  ----------------  --------------

total 1,516.22 100.00 100.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 1 0.00 0.00 0 0 0 0 0
Exec 1 0.00 0.00 0 0 0 0 0
Fetch 1 1,516.22 1,632.94 288,567 296,627 26 0 1
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 3 1,516.22 1,632.94 288,567 296,627 26 0 1

TUNING ADVISEs FOR STATEMENT

Time cost Amount cost Event Tuning advise 151,622 296,653 Fetch Number of Fetch : 1 Number of fetched rows : 1
Number of logical reads for Fetch: 296653

fetched rows per logical read :0.00
This ratio should get close to 1. If They are unacceptable, check missing index.

fetched rows per fetch : 1.00
This ratio should be as high as possible. If it's unacceptable, arrayfetching should be configured.

Hit ratio for Fetch : 0.03
if there is no IO related wait event, ignore it. Otherwise, 80% and higher are recommended.

OVERALL CPU AND NON-IDLE WAIT TIMES TOTALS

Event  NON-SYS  SYS  Total  in Overall(%)  
------  ----------  ----------  ---------- 
--------------

CPU 1,516.22 0.00 1,516.22 100.00
Wait 0.00 0.00 0.00 0.00
------ ---------- ---------- ----------

total 1,516.22 0.00 1,516.22 100.00

OVERALL STATISTICS TOTALS FOR ALL NON-SYS STATEMENTS call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 8 0.00 0.08 0 0 0 3 0
Exec 9 0.00 0.17 0 0 0 0 0
Fetch 3 1,516.22 1,632.94 288,567 296,629 34 0 3
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 20 1,516.22 1,633.19 288,567 296,629 34 3 3

OVERALL TUNING ADVISEs FOR ALL NON-SYS STATEMENTS

Time cost Amount cost Event Tuning advise 151,622 296,663 Fetch Number of Fetch : 3 Number of fetched rows : 3
Number of logical reads for Fetch: 296663

fetched rows per logical read :0.00
This ratio should get close to 1. If They are unacceptable, check missing index.

fetched rows per fetch : 1.00
This ratio should be as high as possible. If it's unacceptable, arrayfetching should be configured.

Hit ratio for Fetch : 0.03
if there is no IO related wait event, ignore it. Otherwise, 80% and higher are recommended.

OVERALL STATISTICS TOTALS FOR ALL SYS STATEMENTS call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- Parse 0 0.00 0.00 0 0 0 0 0
Exec 0 0.00 0.00 0 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0 0
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0 ---------- ------ -------- ---------- ---------- ---------- ---------- ----- ---------- total 0 0.00 0.00 0 0 0 0 0

TRACE FILE STATISTICS

Name  Value-Count  
--------------------------------------- 
---------------------------------------

Trace file name edwp_ora_29647.trc
Trace file size in bytes 4,044
NON-SYS Statements 11
SYS Statements 0
Commit 0
Rollback 0
Read only transactions 0
Read/Write transactions 0
Parse error 2
Other error 0
Different sessions 1
Binds 0
Non-idle and idle waits 0

Return to itrprof menu

For Small SORT_AREA_SIZE

itrprof SQL Analyzer 1.0.0 ANALYSIS RESULTS

STATEMENT alter session set sql_trace = true

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT

Event   Elapsed         in Statement(%)         in Overall(%) 
------  ----------      ----------------        -------------- 
CPU     0.01    100.00  0.00 
Wait    0.00    0.00    0.00 
------  ----------      ----------------        --------------

total 0.01 100.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows

----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
Parse   0       0.00    0.00    0       0       0       0       0 
Exec    1       0.01    0.18    0       0       0       1       0 
Fetch   0       0.00    0.00    0       0       0       0       0 
Unmap   0       0.00    0.00    0       0       0       0       0 
Sort Unmap      0       0.00    0.00    0       0       0       0       0 
----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
total   1       0.01    0.18    0       0       0       1       0

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT

Event   Elapsed         in Statement(%)         in Overall(%) 
------  ----------      ----------------        -------------- 
CPU     1,284.85        100.00  99.99 
Wait    0.00    0.00    0.00 
------  ----------      ----------------        -------------- 
total   1,284.85        100.00  99.99

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows

----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
Parse   1       0.12    0.11    0       35      0       1       0 
Exec    1       0.00    0.00    0       0       0       0       0 
Fetch   1       1,284.73        1,471.65        321,198         296,627         502    
 0
        1 
Unmap   1       0.00    0.00    0       0       0       0       0 
Sort Unmap      0       0.00    0.00    0       0       0       0       0 
----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
total   4       1,284.85        1,471.76        321,198         296,662         502    
 1
        1

EXECUTION PLANs FOR STATEMENT

Parse number : 0

Parsing User ID :       158 
Relative time started :         33 
Recursive depth :       0 
Optimizer goal :        Choose 

Rows    Operation       Object ID

1       SORT GROUP BY   0 
21,967,670        NESTED LOOPS          0 
21,967,671          PARTITION RANGE ITERATOR PARTITION:
START=38 STOP=42        0 
21,967,675            TABLE ACCESS FULL CLAIMS_FACT
PARTITION: START=38 STOP=42     5,988 
21,967,670          INDEX UNIQUE SCAN   6,094

TUNING ADVISEs FOR STATEMENT

Time cost       Amount cost     Event   Tuning advise 
128,473         297,129         Fetch   Number of Fetch : 1Number of

fetched rows : 1Number of logical reads for Fetch: 297129fetched rows per logical read :0.00This ratio should get close to 1. If They are unacceptable, check missing index.fetched rows per fetch : 1.00This ratio should be as high as possible. If it's unacceptable, arrayfetching should be configured.Hit ratio for Fetch : -0.08if there is no IO related wait event, ignore it. Otherwise, 80% and higher are recommended. 12 35 Parse Number of soft parse : 0Number of hard parse : 1Number of total parse: 1Number of execution : 2hard parse per parse : 1.00This ratio should get close to 0. If it's unacceptable, increase SHARED_POOL_SIZE of init.oraexecution per parse :2.00This ratio should be as high as possible (parse once,execute many). It should not get close to 1.Use bind variables.Set CURSOR_SHARING=FORCE in init.ora for 8.1.6 and onwards.Increase SESSION_CACHED_CURSORS of init.ora by starting from 100.Set RELEASE_CURSOR=NO and HOLD_CURSOR=YES in init.ora for pre-compiler applicationsNumber of dictionary lookup: 35This should not be a problem on Oracle7+.If it's unacceptable, check Oracle bugs

STATEMENT select 'x' from dual

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT

Event   Elapsed         in Statement(%)         in Overall(%) 
------  ----------      ----------------        -------------- 
CPU     0.00    0.00    0.00 
Wait    0.00    0.00    0.00 
------  ----------      ----------------        --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows

----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
Parse   1       0.00    0.01    0       0       0       1       0 
Exec    1       0.00    0.00    0       0       0       0       0 
Fetch   1       0.00    0.00    0       1       4       0       1 
Unmap   0       0.00    0.00    0       0       0       0       0 
Sort Unmap      0       0.00    0.00    0       0       0       0       0 
----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
total   3       0.00    0.01    0       1       4       1       1

STATEMENT alter session set sql_trace = false

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT

Event   Elapsed         in Statement(%)         in Overall(%) 
------  ----------      ----------------        -------------- 
CPU     0.00    0.00    0.00 
Wait    0.00    0.00    0.00 
------  ----------      ----------------        --------------

total 0.00 0.00 0.00

STATISTICS TOTALS FOR STATEMENT call count cpu elapsed disk query current miss rows

----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
Parse   1       0.00    0.00    0       0       0       1       0 
Exec    1       0.00    0.00    0       0       0       0       0 
Fetch   0       0.00    0.00    0       0       0       0       0 
Unmap   0       0.00    0.00    0       0       0       0       0 
Sort Unmap      0       0.00    0.00    0       0       0       0       0 
----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
total   2       0.00    0.00    0       0       0       1       0

OVERALL CPU AND NON-IDLE WAIT TIMES TOTALS

Event   NON-SYS         SYS     Total   in Overall(%) 
------  ----------      ----------      ---------- 
-------------- 
CPU     1,284.86        0.08    1,284.94        100.00 
Wait    0.00    0.00    0.00    0.00 
------  ----------      ----------      ---------- 
-------------- 
total   1,284.86        0.08    1,284.94        100.00

OVERALL STATISTICS TOTALS FOR ALL NON-SYS STATEMENTS call count cpu elapsed disk query current miss rows

----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
Parse   3       0.12    0.12    0       35      0       3       0 
Exec    4       0.01    0.18    0       0       0       1       0 
Fetch   2       1,284.73        1,471.65        321,198         296,628         506    
 0
        2 
Unmap   1       0.00    0.00    0       0       0       0       0 
Sort Unmap      0       0.00    0.00    0       0       0       0       0 
----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
total   10      1,284.86        1,471.95        321,198         296,663         506 
4       2

OVERALL TUNING ADVISEs FOR ALL NON-SYS STATEMENTS

Time cost       Amount cost     Event   Tuning advise 
128,473         297,134         Fetch   Number of Fetch : 2Number of

fetched rows : 2Number of logical reads for Fetch: 297134fetched rows per logical read :0.00This ratio should get close to 1. If They are unacceptable, check missing index.fetched rows per fetch : 1.00This ratio should be as high as possible. If it's unacceptable, arrayfetching should be configured.Hit ratio for Fetch : -0.08if there is no IO related wait event, ignore it. Otherwise, 80% and higher are recommended. 12 35 Parse Number of soft parse : 0Number of hard parse : 3Number of total parse: 3Number of execution : 5hard parse per parse : 1.00This ratio should get close to 0. If it's unacceptable, increase SHARED_POOL_SIZE of init.oraexecution per parse :1.67This ratio should be as high as possible (parse once,execute many). It should not get close to 1.Use bind variables.Set CURSOR_SHARING=FORCE in init.ora for 8.1.6 and onwards.Increase SESSION_CACHED_CURSORS of init.ora by starting from 100.Set RELEASE_CURSOR=NO and HOLD_CURSOR=YES in init.ora for pre-compiler applicationsNumber of dictionary lookup: 35This should not be a problem on Oracle7+.If it's unacceptable, check Oracle bugs
1 Exec Number of searched blocks to find the rows to update : 0Number of updated blocks : 0Number of updated rows : 0searched blocks per updated blocks :0.00This ratio should get close to 1. If They are unacceptable, check missing index.searched blocks per updated rows :0.00This ratio should get close to 1. If They are unacceptable, check missing index.Hit ratio for Exec : 1.00if there is no IO related wait event, ignore it. Otherwise, 80% and higher are recommended.

OVERALL STATISTICS TOTALS FOR ALL SYS STATEMENTS call count cpu elapsed disk query current miss rows

----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
Parse   10      0.05    0.03    0       0       0       6       0 
Exec    11      0.02    0.02    0       0       0       0       0 
Fetch   24      0.01    0.00    0       35      0       0       15 
Unmap   0       0.00    0.00    0       0       0       0       0 
Sort Unmap      0       0.00    0.00    0       0       0       0       0 
----------      ------  --------        ----------      ---------- 
----------      ----------      -----   ---------- 
total   45      0.08    0.05    0       35      0       6       15

TRACE FILE STATISTICS

Name    Value-Count 
--------------------------------------- 
--------------------------------------- 
Trace file name         edwp_ora_28213.trc 
Trace file size in bytes        9,010 
NON-SYS Statements      4

SYS Statements 10
Commit 0
Rollback 0
Read only transactions 0

Read/Write transactions         0 
Parse error     0 
Other error     0 
Different sessions      1 
Binds   0 
Non-idle and idle waits         0

Return to itrprof menu

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnson Poovathummoottil
  INET: joni_65_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Jun 21 2001 - 12:06:00 CDT