Home » RDBMS Server » Performance Tuning » DBLink performance issues
DBLink performance issues [message #286989] Mon, 10 December 2007 15:44 Go to next message
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 #287008 is a reply to message #286989] Mon, 10 December 2007 22:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Please use "code"tags with your sql statement.
Post again your tkprof output on forms instead of text file.
with "sys=no" option generate tkprof output.
Re: DBLink performance issues [message #287219 is a reply to message #286989] Tue, 11 December 2007 08:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
trantuananh24hg
Messages: 668
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

Previous Topic: Need to improve query performance
Next Topic: takes more time to open lov
Goto Forum:
  


Current Time: Thu Dec 08 00:33:17 CST 2016

Total time taken to generate the page: 0.11425 seconds