Home » RDBMS Server » Performance Tuning » DBLink performance issues
| DBLink performance issues [message #286989] |
Mon, 10 December 2007 15:44  |
raj_mat2007
Messages: 4
Registered: December 2007
|
Junior Member |
|
|
Experts,
We have a reporting application which connects to a DATABASE A, which has got db link uses to connect to primary production DB B. We deploy jsp pages in the app server. The reports runs very fast but then it dies after sometime and it never runs after it fails. Everytime it times out. I don't see too much load on the
server which kills this report.
Database A and Database B on the same server but big tables(with 100M Rows) are on Database B.The most waited event we get
from the Database Link is as follows,
Investigate the cause for high "SQL*Net more data from dblink" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Use given SQL for further investigation
How we can improve the dbLink performance. The wired thing is that it starts again if we modify the jsp report adding some HINTS such as 'DRIVING_SITE' modify some joins and it dies again after sometime.
Here is a sample query which dies and then it fails for all following attempts. If I make chanhes such as adding DRIVING_SITE hint it starts working but breaks again.Attached is the trace file generated for the below query
-- Activity Summary Report [R12.1.rc]
SELECT tr1.action_code,
tr1.request_type_desc,
tr1.request_type,
tr1.currency_code,
tr1.number_of_merchants,
tr1.total_number_of_transactions,
tr1.total_net_amount_of_trans,
NVL(fr1.number_held_for_review, 0) Number_Held_for_Review,
NVL(fr1.amount_held_for_review, 0) Amount_Held_for_Review
FROM (SELECT /*+ USE_HASH(tra vuh) */
DECODE(tra.action_code, '000', 'Approved', 'Declined') action_code,
NVL(sta.value, tra.request_type) AS request_type_desc,
tra.request_type,
cur.curr_code_alpha3 AS currency_code,
COUNT(DISTINCT tra.mid) AS Number_of_Merchants,
COUNT(*) AS Total_Number_of_Transactions,
SUM(CASE WHEN tra.request_type IN ('0200', '0220', '0250') THEN tra.amount
WHEN tra.request_type IN ('0420') THEN -tra.amount
ELSE 0 END) AS Total_Net_Amount_of_Trans
FROM port.transact tra,
port.merchant mer,
port.master_merchant mas,
port.settlement_description stl,
port.status_code sta,
masclr.currency_code cur,
(SELECT DISTINCT entity_code
FROM vw_user_hierarchy
WHERE entity_type = 'TT'
AND :p_trans = 'R'
AND path LIKE :p_ent
) vuh
WHERE tra.authdatetime >= TO_CHAR(:p_date, 'YYYYMMDDHH24MISS')
AND tra.authdatetime < TO_CHAR(:p_edate + 1, 'YYYYMMDDHH24MISS')
AND :p_trans = 'R'
AND DECODE(tra.action_code, '000', 'A', 'D') LIKE :p_appr
AND vuh.entity_code = tra.tid
AND mer.mid = tra.mid
AND mas.mmid = mer.mmid
AND stl.settlement_facility = mas.settlement_facility
AND sta.type(+) = 'REQ'
AND sta.code(+) = tra.request_type
AND cur.curr_code = tra.currency_code
AND tra.request_type IN ('0100', '0200', '0220', '0250', '0400', '0420')
GROUP BY DECODE(tra.action_code, '000', 'Approved', 'Declined'),
NVL(sta.value, tra.request_type),
tra.request_type,
cur.curr_code_alpha3
) tr1,
(SELECT DECODE(fra.action_code, '000', 'Approved', 'Declined') action_code,
fra.request_type,
fra.currency_code,
COUNT(*) AS Number_Held_for_Review,
SUM(CASE WHEN fra.request_type IN ('0200', '0220', '0250') THEN fra.amount
WHEN fra.request_type IN ('0420') THEN -fra.amount
ELSE 0 END) AS Amount_Held_for_Review
FROM vw_fraud_listing fra,
(SELECT DISTINCT entity_code
FROM vw_user_hierarchy
WHERE entity_type = 'TT'
AND :p_trans = 'R'
AND path LIKE :p_ent
) vuh
WHERE fra.authdatetime >= TO_CHAR(:p_date, 'YYYYMMDDHH24MISS')
AND fra.authdatetime < TO_CHAR(:p_edate + 1, 'YYYYMMDDHH24MISS')
AND :p_trans = 'R'
AND DECODE(fra.action_code, '000', 'A', 'D') LIKE :p_appr
AND vuh.entity_code = fra.tid
GROUP BY DECODE(fra.action_code, '000', 'Approved', 'Declined'),
fra.request_type,
fra.currency_code
) fr1
WHERE :p_trans = 'R'
AND fr1.action_code(+) = tr1.action_code
AND fr1.currency_code(+) = tr1.currency_code
AND fr1.request_type(+) = tr1.request_type
ORDER BY tr1.action_code,
tr1.request_type,
tr1.currency_code
Please help!
Thanks in advance
rmat
|
|
|
|
|
|
| Re: DBLink performance issues [message #287219 is a reply to message #286989] |
Tue, 11 December 2007 08:06  |
raj_mat2007
Messages: 4
Registered: December 2007
|
Junior Member |
|
|
I am sooo sorry for the formatting. Please see the formatted query and trace output generated with sys=no option
-- Activity Summary Report [R12.1.rc]
SELECT tr1.action_code,
tr1.request_type_desc,
tr1.request_type,
tr1.currency_code,
tr1.number_of_merchants,
tr1.total_number_of_transactions,
tr1.total_net_amount_of_trans,
NVL(fr1.number_held_for_review, 0) Number_Held_for_Review,
NVL(fr1.amount_held_for_review, 0) Amount_Held_for_Review
FROM (SELECT /*+ USE_HASH(tra vuh) */
DECODE(tra.action_code, '000', 'Approved', 'Declined') action_code,
NVL(sta.value, tra.request_type) AS request_type_desc,
tra.request_type,
cur.curr_code_alpha3 AS currency_code,
COUNT(DISTINCT tra.mid) AS Number_of_Merchants,
COUNT(*) AS Total_Number_of_Transactions,
SUM(CASE WHEN tra.request_type IN ('0200', '0220', '0250') THEN tra.amount
WHEN tra.request_type IN ('0420') THEN -tra.amount
ELSE 0 END) AS Total_Net_Amount_of_Trans
FROM port.transact tra,
port.merchant mer,
port.master_merchant mas,
port.settlement_description stl,
port.status_code sta,
masclr.currency_code cur,
(SELECT DISTINCT entity_code
FROM vw_user_hierarchy
WHERE entity_type = 'TT'
AND :p_trans = 'R'
AND path LIKE :p_ent
) vuh
WHERE tra.authdatetime >= TO_CHAR(:p_date, 'YYYYMMDDHH24MISS')
AND tra.authdatetime < TO_CHAR(:p_edate + 1, 'YYYYMMDDHH24MISS')
AND :p_trans = 'R'
AND DECODE(tra.action_code, '000', 'A', 'D') LIKE :p_appr
AND vuh.entity_code = tra.tid
AND mer.mid = tra.mid
AND mas.mmid = mer.mmid
AND stl.settlement_facility = mas.settlement_facility
AND sta.type(+) = 'REQ'
AND sta.code(+) = tra.request_type
AND cur.curr_code = tra.currency_code
AND tra.request_type IN ('0100', '0200', '0220', '0250', '0400', '0420')
GROUP BY DECODE(tra.action_code, '000', 'Approved', 'Declined'),
NVL(sta.value, tra.request_type),
tra.request_type,
cur.curr_code_alpha3
) tr1,
(SELECT DECODE(fra.action_code, '000', 'Approved', 'Declined') action_code,
fra.request_type,
fra.currency_code,
COUNT(*) AS Number_Held_for_Review,
SUM(CASE WHEN fra.request_type IN ('0200', '0220', '0250') THEN fra.amount
WHEN fra.request_type IN ('0420') THEN -fra.amount
ELSE 0 END) AS Amount_Held_for_Review
FROM vw_fraud_listing fra,
(SELECT DISTINCT entity_code
FROM vw_user_hierarchy
WHERE entity_type = 'TT'
AND :p_trans = 'R'
AND path LIKE :p_ent
) vuh
WHERE fra.authdatetime >= TO_CHAR(:p_date, 'YYYYMMDDHH24MISS')
AND fra.authdatetime < TO_CHAR(:p_edate + 1, 'YYYYMMDDHH24MISS')
AND :p_trans = 'R'
AND DECODE(fra.action_code, '000', 'A', 'D') LIKE :p_appr
AND vuh.entity_code = fra.tid
GROUP BY DECODE(fra.action_code, '000', 'Approved', 'Declined'),
fra.request_type,
fra.currency_code
) fr1
WHERE :p_trans = 'R'
AND fr1.action_code(+) = tr1.action_code
AND fr1.currency_code(+) = tr1.currency_code
AND fr1.request_type(+) = tr1.request_type
ORDER BY tr1.action_code,
tr1.request_type,
tr1.currency_code
[CODE][/CODE]
Tkprof output below
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.07 0.27 0 20 3 0
Fetch 1 0.28 1124.40 0 56047 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.35 1124.67 0 56067 3 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
3 PX COORDINATOR (cr=56050 pr=0 pw=0 time=1124501953 us)
0 PX SEND QC (ORDER) :TQ10009 (cr=0 pr=0 pw=0 time=0 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND RANGE :TQ10008 (cr=0 pr=0 pw=0 time=0 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10007 (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us)
381 VIEW (cr=55757 pr=0 pw=0 time=1124248046 us)
381 HASH GROUP BY (cr=55757 pr=0 pw=0 time=1124248046 us)
3237 FILTER (cr=55757 pr=0 pw=0 time=790791015 us)
3237 NESTED LOOPS (cr=55757 pr=0 pw=0 time=790791015 us)
3237 NESTED LOOPS OUTER (cr=55755 pr=0 pw=0 time=790791015 us)
3237 NESTED LOOPS (cr=52516 pr=0 pw=0 time=790791015 us)
3237 NESTED LOOPS (cr=49277 pr=0 pw=0 time=790791015 us)
22980 NESTED LOOPS (cr=23058 pr=0 pw=0 time=790791015 us)
23056 REMOTE TRANSACTION (cr=0 pr=0 pw=0 time=790791015 us)
22980 TABLE ACCESS BY INDEX ROWID CURRENCY_CODE (cr=23058 pr=0 pw=0 time=107423 us)
23056 INDEX UNIQUE SCAN XPKCURRENCY_CODE (cr=2 pr=0 pw=0 time=29296 us)(object id 53627)
3237 TABLE ACCESS BY INDEX ROWID MERCHANT (cr=26219 pr=0 pw=0 time=68358 us)
3237 INDEX UNIQUE SCAN PK_MERCHANT (cr=22982 pr=0 pw=0 time=48827 us)(object id 55422)
3237 TABLE ACCESS BY INDEX ROWID MASTER_MERCHANT (cr=3239 pr=0 pw=0 time=0 us)
3237 INDEX UNIQUE SCAN PK_MASTER_MERCHANT (cr=2 pr=0 pw=0 time=0 us)(object id 55419)
3237 TABLE ACCESS BY INDEX ROWID STATUS_CODE (cr=3239 pr=0 pw=0 time=19530 us)
3237 INDEX UNIQUE SCAN SYS_C0011718 (cr=2 pr=0 pw=0 time=9765 us)(object id 55439)
3237 INDEX FULL SCAN SYS_C0011713 (cr=2 pr=0 pw=0 time=9766 us)(object id 55437)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10006 (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10005 (cr=0 pr=0 pw=0 time=0 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
58 REMOTE FRAUD (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10004 (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
1 FILTER (cr=13 pr=0 pw=0 time=0 us)
163 HASH JOIN RIGHT OUTER (cr=13 pr=0 pw=0 time=0 us)
22 VIEW index$_join$_030 (cr=6 pr=0 pw=0 time=0 us)
22 HASH JOIN (cr=6 pr=0 pw=0 time=0 us)
22 INDEX FAST FULL SCAN USER_PERSPECTIVES_PK (cr=3 pr=0 pw=0 time=0 us)(object id 55444)
22 INDEX FAST FULL SCAN XIF_USER_PERSPECTIVES_ENTITY (cr=3 pr=0 pw=0 time=0 us)(object id 56947)
163 TABLE ACCESS FULL USER_PROFILES (cr=7 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FAST FULL SCAN MASTER_ENTITY_HIER2_ROOT_IDX2 (cr=0 pr=0 pw=0 time=0 us)(object id 74086)
0 INDEX UNIQUE SCAN SYS_C0011718 (cr=0 pr=0 pw=0 time=0 us)(object id 55439)
0 TABLE ACCESS BY INDEX ROWID CURRENCY_CODE (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN XPKCURRENCY_CODE (cr=0 pr=0 pw=0 time=0 us)(object id 53627)
0 TABLE ACCESS BY INDEX ROWID MERCHANT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN PK_MERCHANT (cr=0 pr=0 pw=0 time=0 us)(object id 55422)
0 TABLE ACCESS BY INDEX ROWID MASTER_MERCHANT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN PK_MASTER_MERCHANT (cr=0 pr=0 pw=0 time=0 us)(object id 55419)
0 INDEX FULL SCAN SYS_C0011713 (cr=0 pr=0 pw=0 time=0 us)(object id 55437)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10003 (cr=0 pr=0 pw=0 time=0 us)
1 VIEW (cr=277 pr=0 pw=0 time=0 us)
1 HASH UNIQUE (cr=277 pr=0 pw=0 time=0 us)
1 HASH JOIN (cr=277 pr=0 pw=0 time=0 us)
1 FILTER (cr=13 pr=0 pw=0 time=0 us)
163 HASH JOIN RIGHT OUTER (cr=13 pr=0 pw=0 time=0 us)
22 VIEW index$_join$_016 (cr=6 pr=0 pw=0 time=0 us)
22 HASH JOIN (cr=6 pr=0 pw=0 time=0 us)
22 INDEX FAST FULL SCAN USER_PERSPECTIVES_PK (cr=3 pr=0 pw=0 time=0 us)(object id 55444)
22 INDEX FAST FULL SCAN XIF_USER_PERSPECTIVES_ENTITY (cr=3 pr=0 pw=0 time=0 us)(object id 56947)
163 TABLE ACCESS FULL USER_PROFILES (cr=7 pr=0 pw=0 time=0 us)
1 TABLE ACCESS FULL MASTER_ENTITY_HIERARCHY2 (cr=264 pr=0 pw=0 time=0 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 97 0.00 0.00
SQL*Net message from dblink 97 790.45 790.85
SQL*Net more data from dblink 500 327.50 333.09
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.00 0.00
latch: shared pool 1 0.00 0.00
PX Deq: Join ACK 4 0.00 0.00
PX Deq Credit: send blkd 4 0.00 0.00
PX Deq: Parse Reply 5 0.17 0.18
PX Deq: Execute Reply 63 0.00 0.02
PX Deq: Table Q qref 1 0.00 0.00
PX Deq: Signal ACK 4 0.09 0.09
enq: PS - contention 3 0.00 0.00
********************************************************************************
The most interesting thing is the query works just fine if I make some modifications such as adding some hints /*+DRIVING_SITE(tra)*/ hints appended but agin it breaks after sometime. Once it breake, it can't be run after unless until I makse some changes to the query.
any help would be really appreciated
rajmat
|
|
|
|
| Re: DBLink performance issues [message #287376 is a reply to message #287219] |
Wed, 12 December 2007 02:04 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Your problem is same to me!
With OAS, Crystal Report are at the independent servers, I am tired with DBLink which make all of DB's performance impact strongly. Please keep a keen on eyes to my brief of statspack:
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 10,580.3 79.4
DB CPU 7,053.6 52.9
parse time elapsed 1,111.5 8.3
hard parse elapsed time 808.2 6.1
PL/SQL execution elapsed time 107.3 .8
PL/SQL compilation elapsed time 104.2 .8
connection management call elapsed 12.6 .1
hard parse (sharing criteria) elaps 11.6 .1
failed parse elapsed time 2.4 .0
repeated bind elapsed time 0.7 .0
sequence load elapsed time 0.1 .0
inbound PL/SQL rpc elapsed time 0.1 .0
hard parse (bind mismatch) elapsed 0.0 .0
DB time 13,330.6
background elapsed time 69,891.7
background cpu time 62,177.0
-------------------------------------------------------------
Wait Events DB/Inst: TINHCUOC/TINHCUOC Snaps: 51-71
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 43,545 0 3,386 78 1.2
db file parallel write 44,943 0 2,788 62 1.2
log buffer space 2,664 74 2,254 846 0.1
log file sync 33,073 5 2,088 63 0.9
SQL*Net message from dblink 462,129 0 1,137 2 12.4
control file parallel write 35,611 0 751 21 1.0
log file switch completion 500 51 342 684 0.0
os thread startup 1,725 12 292 169 0.0
buffer busy waits 700 31 221 316 0.0
Data file init write 648 0 134 207 0.0
log file switch (checkpoint incom 245 44 127 519 0.0
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 10,580.3 [b]<--[/b] 79.4
DB CPU 7,053.6 52.9
parse time elapsed 1,111.5 8.3
hard parse elapsed time 808.2 6.1
PL/SQL execution elapsed time 107.3 .8
PL/SQL compilation elapsed time 104.2 .8
connection management call elapsed 12.6 .1
hard parse (sharing criteria) elaps 11.6 .1
failed parse elapsed time 2.4 .0
repeated bind elapsed time 0.7 .0
sequence load elapsed time 0.1 .0
inbound PL/SQL rpc elapsed time 0.1 .0
hard parse (bind mismatch) elapsed 0.0 .0
DB time 13,330.6
background elapsed time 69,891.7
background cpu time 62,177.0
-------------------------------------------------------------
Wait Events DB/Inst: TINHCUOC/TINHCUOC Snaps: 51-71
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 43,545 0 3,386 78 1.2
db file parallel write 44,943 0 2,788 62 1.2
log buffer space 2,664 74 2,254 846 0.1
log file sync 33,073 5 2,088 63 0.9
SQL*Net message from dblink 462,129 0 1,137 2 12.4
control file parallel write 35,611 0 751 21 1.0
log file switch completion 500 51 342 684 0.0
os thread startup 1,725 12 292 169 0.0
buffer busy waits 700 31 221 316 0.0
Data file init write 648 0 134 207 0.0
log file switch (checkpoint incom 245 44 127 519 0.0
But all of the SQL was called by packages. In the Oracle, the package is not parse, but only the call parse. I think, you may find the application or module which use this query, tune it again.
[Updated on: Wed, 12 December 2007 02:05] Report message to a moderator |
|
|
|
| Re: DBLink performance issues [message #287540 is a reply to message #287376] |
Wed, 12 December 2007 10:08 |
raj_mat2007
Messages: 4
Registered: December 2007
|
Junior Member |
|
|
trantuananh24hg,
Thank you sooo much for your reponse. Please see the most wait events from the statuspack which we took when we had the issue
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
SQL*Net more data from dblink 821,175 6,927 8 59.4 Network
CPU time 685 5.9
PX Deq Credit: send blkd 30,305 191 6 1.6 Other
cursor: pin S wait on X 5,324 51 10 .4 Concurrency
PX Deq: Signal ACK 652 14 22 .1 Other
Wait Class
* s - second
* cs - centisecond - 100th of a second
* ms - millisecond - 1000th of a second
* us - microsecond - 1000000th of a second
* ordered by wait time desc, waits desc
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
Network 939,384 0.00 6,927 7 947.92
Other 95,763 63.08 215 2 96.63
Concurrency 6,495 79.98 55 8 6.55
System I/O 25,375 0.00 16 1 25.61
User I/O 825,569 0.00 5 0 833.07
Commit 234 0.00 1 5 0.24
Configuration 2 0.00 0 78 0.00
Wait Events
* s - second
* cs - centisecond - 100th of a second
* ms - millisecond - 1000th of a second
* us - microsecond - 1000000th of a second
* ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
SQL*Net more data from dblink 821,175 0.00 6,927 8 828.63
PX Deq Credit: send blkd 30,305 0.08 191 6 30.58
cursor: pin S wait on X 5,324 97.58 51 10 5.37
PX Deq: Signal ACK 652 45.25 14 22 0.66
control file parallel write 1,222 0.00 6 5 1.23
log file parallel write 1,435 0.00 6 4 1.45
db file sequential read 572 0.00 4 7 0.58
os thread startup 63 0.00 3 52 0.06
RFS dispatch 704 0.00 3 5 0.71
reliable message 204 0.00 3 15 0.21
RFS write 644 0.00 3 5 0.65
PX qref latch 63,347 94.84 2 0 63.92
log file sync 234 0.00 1 5 0.24
direct path read temp 824,958 0.00 1 0 832.45
latch free 66 0.00 1 12 0.07
log file sequential read 14,386 0.00 1 0 14.52
kksfbc child completion 17 88.24 1 43 0.02
RFS ping 60 0.00 1 12 0.06
control file sequential read 7,666 0.00 1 0 7.74
SQL*Net more data from client 375 0.00 0 1 0.38
log file switch completion 2 0.00 0 78 0.00
latch: cache buffers chains 9 0.00 0 17 0.01
latch: library cache 585 0.00 0 0 0.59
SQL*Net more data to client 2,915 0.00 0 0 2.94
enq: PS - contention 274 0.00 0 0 0.28
SQL*Net message to dblink 79,734 0.00 0 0 80.46
Log archive I/O 20 0.00 0 2 0.02
latch: shared pool 217 0.00 0 0 0.22
SQL*Net message to client 35,185 0.00 0 0 35.50
log file single write 2 0.00 0 15 0.00
direct path write 20 0.00 0 1 0.02
row cache lock 2 0.00 0 10 0.00
cursor: mutex X 244 0.00 0 0 0.25
latch: library cache lock 47 0.00 0 0 0.05
rdbms ipc reply 24 0.00 0 0 0.02
latch: enqueue hash chains 3 0.00 0 3 0.00
PX Deq: Table Q qref 51 0.00 0 0 0.05
PX Deq: Table Q Get Keys 20 0.00 0 0 0.02
direct path read 19 0.00 0 0 0.02
latch: session allocation 15 0.00 0 0 0.02
cursor: pin S 11 0.00 0 0 0.01
LGWR wait for redo copy 10 0.00 0 0 0.01
latch: row cache objects 4 0.00 0 0 0.00
PX Idle Wait 19,686 96.84 37,700 1915 19.86
SQL*Net message from client 35,180 0.00 29,039 825 35.50
PX Deq: Execution Msg 16,199 38.49 12,511 772 16.35
PX Deq: Table Q Normal 6,412,576 0.09 11,970 2 6,470.81
Streams AQ: qmn coordinator idle wait 346 38.15 3,532 10208 0.35
LogMiner: wakeup event for preparer 1,506 90.64 3,530 2344 1.52
Streams AQ: waiting for messages in the queue 723 100.00 3,529 4882 0.73
LogMiner: wakeup event for builder 3,517 79.95 3,526 1003 3.55
virtual circuit status 121 100.00 3,525 29136 0.12
Streams AQ: qmn slave idle wait 129 0.00 3,525 27325 0.13
LogMiner: client waiting for transaction 2,144 96.08 2,037 950 2.16
Streams fetch slave: waiting for txns 1,527 100.00 1,491 977 1.54
SQL*Net message from dblink 79,734 0.00 1,227 15 80.46
Streams AQ: waiting for time management or cleanup tasks 2 100.00 875 437591 0.00
PX Deq Credit: need buffer 91,471 0.01 80 1 92.30
PX Deq: Execute Reply 7,899 0.10 27 3 7.97
PX Deq: Parse Reply 520 0.00 20 39 0.52
single-task message 221 0.00 15 68 0.22
wait for unread message on broadcast channel 48 0.00 2 46 0.05
PX Deq: Msg Fragment 1,563 0.00 1 1 1.58
PX Deq: Table Q Sample 131 0.00 1 5 0.13
PX Deq: Join ACK 561 0.00 1 1 0.57
LogMiner: wakeup event for reader 5 20.00 0 51 0.01
class slave wait 1 0.00 0 0 0.0
The interesting thing is that report runs quickly yesterday and until now today.No reports broken yet. The only change I did was to added this hint
/*+DRIVING_HINT*/ to problematic reports.I have few questions
1) Is it a good idea both databases A & B on the same server?
2) Any network issue on this server such the trafic or the bandwidth? Talked to network team and they said no issues.I also tried increasing SDU and TDU for database A but no luck. also i tried TCP_NODELAY=yes.
3)Gather statistics everyday for the invloved tables may help? becuase we get at least 100,000 records every day on main tables which comes from the database B.
4) There are few outer joins used in all reports.I think that might be the cause but again,how they all are running fine now?
5) Our report application is oracle portal,which has got a proxy server,app server,reporting server db,and the 2 production dbs(A&B). Reporting server db also talks to DATABASE A using a db link.
Thanks,
rajmat
|
|
|
|
| Re: DBLink performance issues [message #287541 is a reply to message #287540] |
Wed, 12 December 2007 10:10 |
raj_mat2007
Messages: 4
Registered: December 2007
|
Junior Member |
|
|
sorry for the bad formatiing
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
SQL*Net more data from dblink 821,175 6,927 8 59.4 Network
CPU time 685 5.9
PX Deq Credit: send blkd 30,305 191 6 1.6 Other
cursor: pin S wait on X 5,324 51 10 .4 Concurrency
PX Deq: Signal ACK 652 14 22 .1 Other
Wait Class
* s - second
* cs - centisecond - 100th of a second
* ms - millisecond - 1000th of a second
* us - microsecond - 1000000th of a second
* ordered by wait time desc, waits desc
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
Network 939,384 0.00 6,927 7 947.92
Other 95,763 63.08 215 2 96.63
Concurrency 6,495 79.98 55 8 6.55
System I/O 25,375 0.00 16 1 25.61
User I/O 825,569 0.00 5 0 833.07
Commit 234 0.00 1 5 0.24
Configuration 2 0.00 0 78 0.00
Wait Events
* s - second
* cs - centisecond - 100th of a second
* ms - millisecond - 1000th of a second
* us - microsecond - 1000000th of a second
* ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
SQL*Net more data from dblink 821,175 0.00 6,927 8 828.63
PX Deq Credit: send blkd 30,305 0.08 191 6 30.58
cursor: pin S wait on X 5,324 97.58 51 10 5.37
PX Deq: Signal ACK 652 45.25 14 22 0.66
control file parallel write 1,222 0.00 6 5 1.23
log file parallel write 1,435 0.00 6 4 1.45
db file sequential read 572 0.00 4 7 0.58
os thread startup 63 0.00 3 52 0.06
RFS dispatch 704 0.00 3 5 0.71
reliable message 204 0.00 3 15 0.21
RFS write 644 0.00 3 5 0.65
PX qref latch 63,347 94.84 2 0 63.92
log file sync 234 0.00 1 5 0.24
direct path read temp 824,958 0.00 1 0 832.45
latch free 66 0.00 1 12 0.07
log file sequential read 14,386 0.00 1 0 14.52
kksfbc child completion 17 88.24 1 43 0.02
RFS ping 60 0.00 1 12 0.06
control file sequential read 7,666 0.00 1 0 7.74
SQL*Net more data from client 375 0.00 0 1 0.38
log file switch completion 2 0.00 0 78 0.00
latch: cache buffers chains 9 0.00 0 17 0.01
latch: library cache 585 0.00 0 0 0.59
SQL*Net more data to client 2,915 0.00 0 0 2.94
enq: PS - contention 274 0.00 0 0 0.28
SQL*Net message to dblink 79,734 0.00 0 0 80.46
Log archive I/O 20 0.00 0 2 0.02
latch: shared pool 217 0.00 0 0 0.22
SQL*Net message to client 35,185 0.00 0 0 35.50
log file single write 2 0.00 0 15 0.00
direct path write 20 0.00 0 1 0.02
row cache lock 2 0.00 0 10 0.00
cursor: mutex X 244 0.00 0 0 0.25
latch: library cache lock 47 0.00 0 0 0.05
rdbms ipc reply 24 0.00 0 0 0.02
latch: enqueue hash chains 3 0.00 0 3 0.00
PX Deq: Table Q qref 51 0.00 0 0 0.05
PX Deq: Table Q Get Keys 20 0.00 0 0 0.02
direct path read 19 0.00 0 0 0.02
latch: session allocation 15 0.00 0 0 0.02
cursor: pin S 11 0.00 0 0 0.01
LGWR wait for redo copy 10 0.00 0 0 0.01
latch: row cache objects 4 0.00 0 0 0.00
PX Idle Wait 19,686 96.84 37,700 1915 19.86
SQL*Net message from client 35,180 0.00 29,039 825 35.50
PX Deq: Execution Msg 16,199 38.49 12,511 772 16.35
PX Deq: Table Q Normal 6,412,576 0.09 11,970 2 6,470.81
Streams AQ: qmn coordinator idle wait 346 38.15 3,532 10208 0.35
LogMiner: wakeup event for preparer 1,506 90.64 3,530 2344 1.52
Streams AQ: waiting for messages in the queue 723 100.00 3,529 4882 0.73
LogMiner: wakeup event for builder 3,517 79.95 3,526 1003 3.55
virtual circuit status 121 100.00 3,525 29136 0.12
Streams AQ: qmn slave idle wait 129 0.00 3,525 27325 0.13
LogMiner: client waiting for transaction 2,144 96.08 2,037 950 2.16
Streams fetch slave: waiting for txns 1,527 100.00 1,491 977 1.54
SQL*Net message from dblink 79,734 0.00 1,227 15 80.46
Streams AQ: waiting for time management or cleanup tasks 2 100.00 875 437591 0.00
PX Deq Credit: need buffer 91,471 0.01 80 1 92.30
PX Deq: Execute Reply 7,899 0.10 27 3 7.97
PX Deq: Parse Reply 520 0.00 20 39 0.52
single-task message 221 0.00 15 68 0.22
wait for unread message on broadcast channel 48 0.00 2 46 0.05
PX Deq: Msg Fragment 1,563 0.00 1 1 1.58
PX Deq: Table Q Sample 131 0.00 1 5 0.13
PX Deq: Join ACK 561 0.00 1 1 0.57
LogMiner: wakeup event for reader 5 20.00 0 51 0.01
class slave wait 1 0.00 0 0 0.0 |
|
|
|
| Re: DBLink performance issues [message #287596 is a reply to message #287541] |
Wed, 12 December 2007 20:04 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
In the statspack, there are tons of things, however, you might consider about the %. If it's high, your system (include DB, OS, Network, etc) should be tuned. I don't know about the hint which took your performance improving, because, maybe it was useful , may be it was influence to the other.
Take a look following to Tom guide:
| Quote: |
Knowledge of the physical organization of what I'm asked to query against. That is - the schema. Knowledge that the physical organization was actually designed in order to help me answer my frequently asked questions (refer back to the chapter on designing an efficient schema for advice in that arena)
o Knowledge of what the database is capable of doing. If I did not know about "skip scan indexes" and what they did - I might look at a schema and say "ah hah, we are missing an index" when in fact we are not.
o Knowledge of all of the intricacies of SQL - from the lowly "WHERE" clause on up to analytics and psuedo columns. Knowledge of what using a particular construct will do to my runtime processing.
o And most importantly of all - a solid understanding of the goal, of what the question is. Tuning a query or process is really hard (impossible I would say) - unless you understand the question in the first place. I cannot tell you how many times I've not been able to tune a query until I had the question in hand. Certainly you can derive a question from a query - however, many times that derived question is much more
confining then the real question being asked. For example, many people use outer joins in all queries - they are "afraid" of losing a row (perhaps they got "burned" in some
past experience and now use outer joins everywhere). If the objects are related in a one to one mandatory fashion - we don't need an outer join at all. The question derived from the query is much more confining then reality.
|
[Updated on: Wed, 12 December 2007 23:17] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Nov 26 21:32:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
