Hi
After reading all document mention in following link and many other i'm still confused about setting the parameter
pgamax_size,
smmmax_size
846027
I'm on oracle 11gr2 windows 2008 64bit 48GB installed ram and 40GB allocate to Oracle memory_target.
During execution i can see Has join operation split on disk. only 3gb Ram use for hash join 15 gb on temp (i get this info from sql monitoring tab in EM). There is 50% Ram available on server which not going to use. only 5GB ram use for PGA during this execution (info from sql monitoring tab in EM) fro more detail have look the following and suggest me how to set memory? parallel=4
TKPROF: Release 11.2.0.3.0 - Development on Fri Mar 8 01:01:36 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: D:\app\IDM\diag\rdbms\orcl\orcl\trace\usman.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SELECT *
FROM
(SELECT "F2" ,
"F1"
FROM
(SELECT ROUND( SUM(
CASE
WHEN ( "F22" - "F23" ) < 0
THEN ( "F23" - "F22" )
ELSE (0 )
END),2) AS "F2" ,
ANTABLE.F1 AS "F1" ,
"F21" AS "F21"
FROM
(SELECT Clicks01v1.Clicks01v1_DG1.Line_Date AS "F1" ,
Clicks01v1.Clicks01v1_DG0.Revenue_Estd_Net_Revenue AS "F22" ,
Clicks01v1.Clicks01v1_DG1.Line_Cost AS "F23" ,
ROUND(( MAX( Clicks01v1.Clicks01v1_DG1.Line_Date ) over ())- Clicks01v1.Clicks01v1_DG1.Line_Date ,0) AS "F21"
FROM Clicks01v1.Clicks01v1_JT
INNER JOIN Clicks01v1.Clicks01v1_DG0
ON Clicks01v1.Clicks01v1_DG0.DG0_ID =Clicks01v1.Clicks01v1_JT.DG0_ID
INNER JOIN Clicks01v1.Clicks01v1_DG1
ON Clicks01v1.Clicks01v1_DG1.DG1_ID =Clicks01v1.Clicks01v1_JT.DG1_ID
INNER JOIN Clicks01v1.Clicks01v1_DG5
ON Clicks01v1.Clicks01v1_DG5.DG5_ID =Clicks01v1.Clicks01v1_JT.DG5_ID
INNER JOIN
(SELECT Clicks01v1_T65.Account_Site_Name AS "F4" ,
Clicks01v1_T65.Line_Date AS "F7" ,
Clicks01v1_T65.Account_Partner_Name AS "F3" ,
Clicks01v1_T65.Line_Campaign AS "F6" ,
Clicks01v1_T65.Line_Market AS "F8" ,
Clicks01v1_T65.Line_AdGroup AS "F5"
FROM
( SELECT * FROM Clicks01v1_T65
) Clicks01v1_T65
GROUP BY Clicks01v1_T65.Account_Site_Name ,
Clicks01v1_T65.Line_Date ,
Clicks01v1_T65.Account_Partner_Name ,
Clicks01v1_T65.Line_Campaign ,
Clicks01v1_T65.Line_Market ,
Clicks01v1_T65.Line_AdGroup
HAVING ( ROUND( SUM(Fca33500a732e55c2) ,2) < 0 )
) rpt1
ON Clicks01v1.Clicks01v1_DG1.Account_Partner_Name =rpt1."F3"
AND Clicks01v1.Clicks01v1_DG1.Account_Site_Name =rpt1."F4"
AND Clicks01v1.Clicks01v1_DG1.Line_AdGroup =rpt1."F5"
AND Clicks01v1.Clicks01v1_DG1.Line_Campaign =rpt1."F6"
AND Clicks01v1.Clicks01v1_DG1.Line_Date =rpt1."F7"
AND Clicks01v1.Clicks01v1_DG5.Line_Market =rpt1."F8"
INNER JOIN
(SELECT Clicks01v1_T65.Account_Site_Name AS "F11" ,
Clicks01v1_T65.Line_Date AS "F12" ,
Clicks01v1_T65.Account_Partner_Name AS "F9" ,
Clicks01v1_T65.Line_Campaign AS "F14" ,
Clicks01v1_T65.Line_Market AS "F10" ,
Clicks01v1_T65.Line_AdGroup AS "F13"
FROM
( SELECT * FROM Clicks01v1_T65
) Clicks01v1_T65
GROUP BY Clicks01v1_T65.Account_Site_Name ,
Clicks01v1_T65.Line_Date ,
Clicks01v1_T65.Account_Partner_Name ,
Clicks01v1_T65.Line_Campaign ,
Clicks01v1_T65.Line_Market ,
Clicks01v1_T65.Line_AdGroup
HAVING ( ROUND( SUM(Fca33500a732e55c2) ,2) < 0 )
) rpt2
ON Clicks01v1.Clicks01v1_DG1.Account_Partner_Name =rpt2."F9"
AND Clicks01v1.Clicks01v1_DG5.Line_Market =rpt2."F10"
AND Clicks01v1.Clicks01v1_DG1.Account_Site_Name =rpt2."F11"
AND Clicks01v1.Clicks01v1_DG1.Line_Date -1 =rpt2."F12"
AND Clicks01v1.Clicks01v1_DG1.Line_AdGroup =rpt2."F13"
AND Clicks01v1.Clicks01v1_DG1.Line_Campaign =rpt2."F14"
INNER JOIN
(SELECT Clicks01v1_T65.Account_Site_Name AS "F18" ,
Clicks01v1_T65.Line_Date AS "F17" ,
Clicks01v1_T65.Account_Partner_Name AS "F16" ,
Clicks01v1_T65.Line_Campaign AS "F20" ,
Clicks01v1_T65.Line_Market AS "F19" ,
Clicks01v1_T65.Line_AdGroup AS "F15"
FROM
( SELECT * FROM Clicks01v1_T65
) Clicks01v1_T65
GROUP BY Clicks01v1_T65.Account_Site_Name ,
Clicks01v1_T65.Line_Date ,
Clicks01v1_T65.Account_Partner_Name ,
Clicks01v1_T65.Line_Campaign ,
Clicks01v1_T65.Line_Market ,
Clicks01v1_T65.Line_AdGroup
HAVING ( ROUND( SUM(Fca33500a732e55c2) ,2) < 0 )
) rpt3
ON Clicks01v1.Clicks01v1_DG1.Line_AdGroup =rpt3."F15"
AND Clicks01v1.Clicks01v1_DG1.Account_Partner_Name =rpt3."F16"
AND Clicks01v1.Clicks01v1_DG1.Line_Date -2 =rpt3."F17"
AND Clicks01v1.Clicks01v1_DG1.Account_Site_Name =rpt3."F18"
AND Clicks01v1.Clicks01v1_DG5.Line_Market =rpt3."F19"
AND Clicks01v1.Clicks01v1_DG1.Line_Campaign =rpt3."F20"
WHERE ( ( ( Clicks01v1.Clicks01v1_DG1.Account_Site_Name NOT IN ( 'TheSelection.co.uk','TheSelection.com','HotBot.com','NetShoppers.com','TheSelection.co.uk','TheSelection.com','HotBot.com','NetShoppers.com','m.NetShoppers.com') ) ) )
) ANTABLE
GROUP BY ANTABLE.F1,
"F21"
) ANTABLE
WHERE ( "F21" < 10 )
ORDER BY "F1" ASC
) t
WHERE RowNum<=10000+1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.48 0.51 0 9 0 0
Execute 9 2219.12 9735.16 4564644 523051005 986 0
Fetch 1 85.97 1578.80 293000 0 2 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 2305.58 11314.49 4857644 523051014 988 7
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71
Number of plan statistics captured: 9
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
7 1 7 COUNT STOPKEY (cr=607 pr=32556 pw=32556 time=175427855 us)
7 1 7 VIEW (cr=607 pr=32556 pw=32556 time=175427854 us cost=374318 size=22 card=1)
7 1 7 SORT GROUP BY STOPKEY (cr=607 pr=32556 pw=32556 time=175427853 us cost=374318 size=47 card=1)
2565247 285027 2565247 VIEW (cr=607 pr=32556 pw=32556 time=177004506 us cost=374318 size=47 card=1)
12128320 1347591 12128320 WINDOW BUFFER (cr=607 pr=32556 pw=32556 time=175330266 us cost=374318 size=1672 card=1)
12128320 1347591 12128320 PX COORDINATOR (cr=607 pr=0 pw=0 time=162123711 us)
0 0 0 PX SEND QC (RANDOM) :TQ10008 (cr=0 pr=0 pw=0 time=0 us)
0 1347591 3043301 NESTED LOOPS (cr=57877021 pr=109659 pw=108245 time=395857798 us)
0 1347591 3043301 NESTED LOOPS (cr=56914559 pr=108421 pw=108245 time=378914374 us cost=374318 size=1672 card=1)
0 1347591 3043301 NESTED LOOPS (cr=33959679 pr=108287 pw=108245 time=311992780 us cost=374310 size=1662 card=1)
0 1347591 3043301 HASH JOIN (cr=0 pr=108245 pw=108245 time=216881496 us cost=374302 size=1653 card=1)
0 6471823 14561602 PX RECEIVE (cr=0 pr=0 pw=0 time=152713801 us cost=342123 size=2268 card=2)
0 0 0 PX SEND BROADCAST :TQ10006 (cr=0 pr=0 pw=0 time=0 us cost=342123 size=2268 card=2)
0 1617956 3667779 HASH JOIN (cr=0 pr=121393 pw=121393 time=272400583 us cost=342123 size=2268 card=2)
0 9888840 22249891 PX RECEIVE (cr=0 pr=0 pw=0 time=104408332 us cost=309944 size=7681350 card=12490)
0 0 0 PX SEND BROADCAST :TQ10004 (cr=0 pr=0 pw=0 time=0 us cost=309944 size=7681350 card=12490)
0 2472210 5564306 HASH JOIN BUFFERED (cr=0 pr=65737 pw=65731 time=186717105 us cost=309944 size=7681350 card=12490)
0 196224 441503 PX RECEIVE (cr=0 pr=0 pw=0 time=216126 us cost=32178 size=331506579 card=638741)
0 0 0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=32178 size=331506579 card=638741)
0 49056 110702 VIEW (cr=0 pr=0 pw=0 time=3814481 us cost=32178 size=331506579 card=638741)
0 49056 110702 FILTER (cr=0 pr=0 pw=0 time=3789262 us)
0 536217 1207290 HASH GROUP BY (cr=0 pr=0 pw=0 time=3768376 us cost=32178 size=47905575 card=638741)
0 1375136 3095596 PX RECEIVE (cr=0 pr=0 pw=0 time=2808183 us cost=11554 size=958111425 card=12774819)
0 0 0 PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=11554 size=958111425 card=12774819)
0 1375136 3168170 PX BLOCK ITERATOR PARTITION: 1 16 (cr=17919 pr=12225 pw=0 time=3137803 us cost=11554 size=958111425 card=12774819)
0 1375136 3168170 TABLE ACCESS FULL CLICKS01V1_T65 PARTITION: 1 16 (cr=17919 pr=12225 pw=0 time=2212566 us cost=11554 size=958111425 card=12774819)
0 10443423 23513382 HASH JOIN (cr=0 pr=47708 pw=47702 time=90844320 us cost=277685 size=9058520064 card=94359584)
0 11064233 24910012 PX RECEIVE (cr=0 pr=0 pw=0 time=20178510 us cost=42308 size=1991562000 card=99578100)
0 0 0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=42308 size=1991562000 card=99578100)
0 11064233 26293789 PX BLOCK ITERATOR PARTITION: 1 16 (cr=55170 pr=44089 pw=0 time=17491844 us cost=42308 size=1991562000 card=99578100)
0 11064233 26293789 TABLE ACCESS FULL CLICKS01V1_JT PARTITION: 1 16 (cr=55170 pr=44089 pw=0 time=17609995 us cost=42308 size=1991562000 card=99578100)
0 10050153 22628193 PX RECEIVE (cr=0 pr=0 pw=0 time=45730491 us cost=122638 size=6791668788 card=89364063)
0 0 0 PX SEND HASH :TQ10003 (cr=0 pr=0 pw=0 time=0 us cost=122638 size=6791668788 card=89364063)
0 10050153 23742815 PX BLOCK ITERATOR PARTITION: 1 16 (cr=130222 pr=129629 pw=0 time=41114283 us cost=122638 size=6791668788 card=89364063)
0 10050153 23742815 TABLE ACCESS FULL CLICKS01V1_DG1 PARTITION: 1 16 (cr=130222 pr=129629 pw=0 time=39268415 us cost=122638 size=6791668788 card=89364063)
0 49056 110702 VIEW (cr=0 pr=0 pw=0 time=4298268 us cost=32178 size=331506579 card=638741)
0 49056 110702 FILTER (cr=0 pr=0 pw=0 time=4279119 us)
0 536217 1207290 HASH GROUP BY (cr=0 pr=0 pw=0 time=4250013 us cost=32178 size=47905575 card=638741)
0 1375136 3095596 PX RECEIVE (cr=0 pr=0 pw=0 time=2493786 us cost=11554 size=958111425 card=12774819)
0 0 0 PX SEND HASH :TQ10005 (cr=0 pr=0 pw=0 time=0 us cost=11554 size=958111425 card=12774819)
0 1375136 3243963 PX BLOCK ITERATOR PARTITION: 1 16 (cr=17919 pr=12225 pw=0 time=7403964 us cost=11554 size=958111425 card=12774819)
0 1375136 3243963 TABLE ACCESS FULL CLICKS01V1_T65 PARTITION: 1 16 (cr=17919 pr=12225 pw=0 time=7612618 us cost=11554 size=958111425 card=12774819)
0 49056 110702 VIEW (cr=0 pr=0 pw=0 time=4027632 us cost=32178 size=331506579 card=638741)
0 49056 110702 FILTER (cr=0 pr=0 pw=0 time=4008953 us)
0 536217 1207290 HASH GROUP BY (cr=0 pr=0 pw=0 time=4043108 us cost=32178 size=47905575 card=638741)
0 1375136 3095596 PX RECEIVE (cr=0 pr=0 pw=0 time=2955383 us cost=11554 size=958111425 card=12774819)
0 0 0 PX SEND HASH :TQ10007 (cr=0 pr=0 pw=0 time=0 us cost=11554 size=958111425 card=12774819)
0 1375136 3199849 PX BLOCK ITERATOR PARTITION: 1 16 (cr=17919 pr=12225 pw=0 time=3316839 us cost=11554 size=958111425 card=12774819)
0 1375136 3199849 TABLE ACCESS FULL CLICKS01V1_T65 PARTITION: 1 16 (cr=17919 pr=12225 pw=0 time=3415122 us cost=11554 size=958111425 card=12774819)
0 1347591 3043301 PARTITION RANGE ALL PARTITION: 1 16 (cr=33959679 pr=42 pw=0 time=93352800 us cost=8 size=9 card=1)
0 1347591 3043301 TABLE ACCESS BY LOCAL INDEX ROWID CLICKS01V1_DG0 PARTITION: 1 16 (cr=33959679 pr=42 pw=0 time=82086397 us cost=8 size=9 card=1)
0 1347591 3043301 INDEX RANGE SCAN PKINDX_CLICKS01V1_DG0 PARTITION: 1 16 (cr=33678331 pr=9 pw=0 time=62226605 us cost=8 size=0 card=1)(object id 74340)
0 1347591 3043301 PARTITION RANGE ALL PARTITION: 1 16 (cr=22954881 pr=134 pw=0 time=65642914 us cost=8 size=0 card=1)
0 1347591 3043301 INDEX RANGE SCAN PKINDX_CLICKS01V1_DG5 PARTITION: 1 16 (cr=22954881 pr=134 pw=0 time=54089972 us cost=8 size=0 card=1)(object id 74527)
0 1347591 3043301 TABLE ACCESS BY LOCAL INDEX ROWID CLICKS01V1_DG5 PARTITION: 1 1 (cr=962462 pr=1238 pw=0 time=15560856 us cost=8 size=10 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
os thread startup 8 0.00 0.03
PX Deq: Join ACK 8 0.00 0.00
PX Deq: Parse Reply 8 0.00 0.00
SQL*Net message to client 1 0.00 0.00
PX Deq: Execute Reply 428 350.75 994.54
PX Deq: Table Q Normal 3095893 0.98 2829.63
direct path write temp 41459 1.76 153.21
PX Deq: Signal ACK RSG 8 0.13 0.13
PX Deq: Signal ACK EXT 8 0.00 0.00
PX Deq: Slave Session Stats 8 0.00 0.00
direct path read temp 173960 0.87 2498.50
SQL*Net message from client 1 50.45 50.45
PX Deq: Execution Msg 400 510.21 2713.30
Disk file operations I/O 207 0.16 0.75
direct path read 8926 1.88 561.34
PX Deq Credit: send blkd 102167 15.19 325.62
PX Deq Credit: need buffer 164813 0.73 148.14
PX qref latch 3414 0.03 1.58
PX Deq Credit: free buffer 27 0.00 0.00
db file sequential read 12723 0.81 102.31
latch: cache buffers chains 8 0.00 0.00
read by other session 1 0.00 0.00
buffer busy waits 1 0.00 0.00
resmgr:internal state change 2 0.10 0.10
********************************************************************************
SQL ID: 8pp095d4ddwjg Plan Hash: 0
BEGIN dbms_monitor.client_id_trace_enable(client_id=>'px_test2'); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.01 0.03 7 111 33 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.03 7 111 33 1
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 1 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 68.76 68.76
********************************************************************************
SQL ID: 75u5njrk0cf6f Plan Hash: 3703961084
SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune
no_monitoring optimizer_features_enable(default) */ SUM(C1)
FROM
(SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CLICKS01V1_DG1") */ 1 AS
C1 FROM "CLICKS01V1"."CLICKS01V1_DG1" SAMPLE BLOCK(0.0341753, 4) SEED(1)
"CLICKS01V1_DG1") innerQuery
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 1 0.03 0.03 0 684 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.03 0 684 2 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=684 pr=0 pw=0 time=34144 us)
37472 37472 37472 PARTITION RANGE ALL PARTITION: 1 16 (cr=684 pr=0 pw=0 time=19108 us cost=152 size=392820 card=32735)
37472 37472 37472 TABLE ACCESS SAMPLE CLICKS01V1_DG1 PARTITION: 1 16 (cr=684 pr=0 pw=0 time=10972 us cost=152 size=392820 card=32735)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.48 0.51 0 9 0 0
Execute 10 2219.14 9735.20 4564651 523051116 1019 1
Fetch 1 85.97 1578.80 293000 0 2 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 2305.60 11314.52 4857651 523051125 1021 8
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 1 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 68.76 119.21
SQL*Net more data from client 1 0.02 0.02
os thread startup 8 0.00 0.03
PX Deq: Join ACK 8 0.00 0.00
PX Deq: Parse Reply 8 0.00 0.00
PX Deq: Execute Reply 428 350.75 994.54
library cache: mutex X 1 0.00 0.00
PX Deq: Table Q Normal 289565 0.36 426.65
direct path write temp 9870 0.00 1.05
direct path read temp 15145 0.25 72.12
PX Deq: Signal ACK RSG 8 0.13 0.13
PX Deq: Signal ACK EXT 8 0.00 0.00
PX Deq: Slave Session Stats 16 0.00 0.03
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9 0.00 0.00 0 1 2 1
Fetch 8 0.03 0.03 0 705 2 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.03 0.03 0 706 4 9
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 400 510.21 2713.30
Disk file operations I/O 207 0.16 0.75
direct path read 8926 1.88 561.34
PX Deq Credit: send blkd 102167 15.19 325.62
PX Deq Credit: need buffer 164813 0.73 148.14
buffer busy waits 1 0.00 0.00
PX qref latch 3414 0.03 1.58
PX Deq: Table Q Normal 2806328 0.98 2402.98
direct path write temp 31589 1.76 152.15
PX Deq Credit: free buffer 27 0.00 0.00
direct path read temp 158815 0.87 2426.37
resmgr:internal state change 2 0.10 0.10
db file sequential read 12723 0.81 102.31
latch: cache buffers chains 8 0.00 0.00
read by other session 1 0.00 0.00
11 user SQL statements in session.
2 internal SQL statements in session.
13 SQL statements in session.
********************************************************************************
Trace file: D:\app\IDM\diag\rdbms\orcl\orcl\trace\usman.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela exeela fchela
0 session in tracefile.
11 user SQL statements in trace file.
2 internal SQL statements in trace file.
13 SQL statements in trace file.
5 unique SQL statements in trace file.
3633808 lines in trace file.
1698 elapsed seconds in trace file.