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 <[EMAIL PROTECTED]> 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
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,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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
