Solved

How to tune a query with a join over a DBLink. It takes longer amount of time to get the result.

Posted on 2011-03-03
12
2,218 Views
Last Modified: 2012-05-11
Hi Experts,
I have a typical problem at hand.
SELECT
    'RAD' FILETYPE,R.ACCT_NO,R.TRADE_CYMD,R.TRADE_QTY,R.NET_AMT,
  R.CUSIP,R.SYMBOL,R.SRCE_CODE,R.REC_CLASS_CODE,R.CHANGE_WHO_CODE
FROM
  CDS_PROD_01.BETA_RAD_01_OV R
JOIN
  BETA_ACT_01_OV A
ON
  R.ACCT_NO = A.ACCT_NO
WHERE
  A.PRODUCT_CLASS = 'SMAS' AND
  A.MANOX = 'FA'

We did some modification using Hint and got it explained, the result was better. Below is the query.
SELECT                                                          /*+ ORDERED */
      a.*, c.*
  FROM SHODS01.BETA_CONTROL_DLY@HODS01 B
       JOIN SHODS01.BETA_ACT_DLY@HODS01 C
          ON     c.BATCH_DTE_CYMD = b.BATCH_DTE_CYMD
 AND c.PRODUCT_CLASS = 'SMAS'
             AND c.MANOX = 'FA'
       JOIN SHODS01.BETA_RAD_DLY@HODS01 A
          ON b.BATCH_DTE_CYMD = a.BATCH_DTE_CYMD AND c.acct_no = a.acct_no
           WHERE b.TABLE_NME IN ('BETA_RAD_DLY','BETA_ACT_DLY')
 and b.LAST_PROC_BCH_IND = 'Y';
 
As stated this query runs over a DBLink and takes a long time. Do we need to use hint..something like.../*+ordered*/ or  /*+use_hash*/   to get the good result?  Or is there any way we can improve this query? Its sort of urgent.

Kind regards
Shashi singh
0
Comment
Question by:dba_shashi
  • 6
  • 5
12 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 35030405
what's happening on the remote system?

I would hope with a query like that would get pushed to the remote system it its entirety since there is nothing local needed.

Is that happening?  If not,  what is left behind to be applied locally? Your where filters?

If so, what does the plan look like on the remote system?
0
 

Author Comment

by:dba_shashi
ID: 35030741
Yes...your observation is correct. Database is  using DB link which in-turns use network to send and receive the data. The network is saturated due to the bad code where a single user query is making a multiple call to distributed databases to process the user request and if the number of user requests are more it will saturate the network and it will affect the queries response time.
How can we improve our query?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 35030767
I don't know what to do to improve it without feedback on the questions I asked.
I'll try to rephrase

What are the explain plains both locally and remotely?  please post using  dbms_xplan.display
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 35030797
First at all figure out if the needed for the joins indexes exist on the remote node and if the statistics over the tables and indexes involved is collected. This will speed up the processing - see the execution plan and try to avoid full table scans.

If this doesn't help then try to run this query as procedure on the remote computer and receive the data as result set (reference cursor):


/* On remote node */
CREATE OR REPLACE FUNCTION usp RETURN SYS_REFCURSOR
IS
  result SYS_REFCURSOR;
BEGIN
  open result for SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees  ;
  return result;

END;
/


/* On Local Instance */
SELECT usp@HODS01 FROM dual;
 
0
 

Author Comment

by:dba_shashi
ID: 35038370
HI Experts,
Here is Top Wait Events on Remote
DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6124 02-Mar-11 15:00:25        60       2.4
  End Snap:      6125 02-Mar-11 16:00:35        57       1.5
   Elapsed:               60.16 (mins)
   DB Time:              203.17 (mins)
   
   Top 5 Timed Foreground Events
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                              Avg
                                                             wait   % DB
   Event                                 Waits     Time(s)   (ms)   time Wait Class
   ------------------------------ ------------ ----------- ------ ------ ----------
   db file sequential read           1,235,920       5,904      5   48.4 User I/O
   gc cr multi block request           221,281       2,638     12   21.6 Cluster
   DB CPU                                            1,720          14.1
   gc buffer busy acquire                2,116         551    260    4.5 Cluster
   direct path read                     61,654         324      5    2.7 User I/O
    Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
   ~~~~~~~~         Load Average
                  Begin       End     %User   %System      %WIO     %Idle
              --------- --------- --------- --------- --------- ---------
                   1.93      2.11       3.3       1.3       9.3      94.9

====================================================================================================================                


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6125 02-Mar-11 16:00:35        57       1.5
  End Snap:      6126 02-Mar-11 17:00:05        58       1.5
   Elapsed:               59.49 (mins)
   DB Time:              214.09 (mins)



Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            4,512          35.1
db file sequential read             408,035       2,117      5   16.5 User I/O
gc cr multi block request           202,808       1,976     10   15.4 Cluster
DFS lock handle                       2,801       1,185    423    9.2 Other
direct path read                    221,871       1,143      5    8.9 User I/O
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.11      0.83       9.1       1.4       6.0      88.9

====================================================================================================================                


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6126 02-Mar-11 17:00:05        58       1.5
  End Snap:      6127 02-Mar-11 18:00:02        60       1.5
   Elapsed:               59.95 (mins)
   DB Time:              206.74 (mins)
   
   Top 5 Timed Foreground Events
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                              Avg
                                                             wait   % DB
   Event                                 Waits     Time(s)   (ms)   time Wait Class
   ------------------------------ ------------ ----------- ------ ------ ----------
   DFS lock handle                       1,493       3,124   2092   25.2 Other
   DB CPU                                            2,625          21.2
   db file sequential read             422,448       2,158      5   17.4 User I/O
   gc cr multi block request           128,163       2,112     16   17.0 Cluster
   gc buffer busy acquire                4,039         850    210    6.9 Cluster
    Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
   ~~~~~~~~         Load Average
                  Begin       End     %User   %System      %WIO     %Idle
              --------- --------- --------- --------- --------- ---------
                   0.83      1.61       5.3       1.3       4.9      92.8



====================================================================================================================                

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6127 02-Mar-11 18:00:02        60       1.5
  End Snap:      6128 02-Mar-11 19:00:10        61       1.8
   Elapsed:               60.13 (mins)
   DB Time:              177.94 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DFS lock handle                         590       3,140   5321   29.4 Other
db file sequential read             746,030       2,901      4   27.2 User I/O
DB CPU                                            2,671          25.0
inactive transaction branch             539         470    873    4.4 Other
PX Deq: reap credit              18,714,380         369      0    3.5 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                1.61      0.82       4.7       1.5       5.1      92.9

====================================================================================================================                

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6128 02-Mar-11 19:00:10        61       1.8
  End Snap:      6129 02-Mar-11 20:00:06        66       1.4
   Elapsed:               59.95 (mins)
   DB Time:              208.44 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DFS lock handle                         654       3,112   4759   24.9 Other
db file scattered read              131,828       2,975     23   23.8 User I/O
db file sequential read             802,421       2,560      3   20.5 User I/O
DB CPU                                            1,638          13.1
gc cr multi block request           152,974         963      6    7.7 Cluster
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                0.82      1.21       3.4       1.1       7.0      95.1

====================================================================================================================                

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6129 02-Mar-11 20:00:06        66       1.4
  End Snap:      6130 02-Mar-11 21:00:09        59       1.7
   Elapsed:               60.04 (mins)
   DB Time:              258.69 (mins)
   
   Top 5 Timed Foreground Events
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                              Avg
                                                             wait   % DB
   Event                                 Waits     Time(s)   (ms)   time Wait Class
   ------------------------------ ------------ ----------- ------ ------ ----------
   DB CPU                                            5,735          36.9
   DFS lock handle                      17,608       3,128    178   20.2 Other
   direct path read                    352,367       2,548      7   16.4 User I/O
   db file scattered read               93,407       1,431     15    9.2 User I/O
   gc cr multi block request           785,952         917      1    5.9 Cluster
    Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
   ~~~~~~~~         Load Average
                  Begin       End     %User   %System      %WIO     %Idle
              --------- --------- --------- --------- --------- ---------
                   1.21      3.34      18.2       2.2       6.4      78.7



====================================================================================================================                

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6130 02-Mar-11 21:00:09        59       1.7
  End Snap:      6131 02-Mar-11 22:00:15        61       1.5
   Elapsed:               60.10 (mins)
   DB Time:              300.77 (mins)
   
   
   Top 5 Timed Foreground Events
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                              Avg
                                                             wait   % DB
   Event                                 Waits     Time(s)   (ms)   time Wait Class
   ------------------------------ ------------ ----------- ------ ------ ----------
   DB CPU                                            7,192          39.9
   direct path read                    500,779       3,621      7   20.1 User I/O
   DFS lock handle                       4,130       3,119    755   17.3 Other
   gc cr multi block request           255,559       2,927     11   16.2 Cluster
   inactive transaction branch             536         491    917    2.7 Other
    Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
   ~~~~~~~~         Load Average
                  Begin       End     %User   %System      %WIO     %Idle
              --------- --------- --------- --------- --------- ---------
                   3.34      4.54      17.6       1.9       4.7      79.9


====================================================================================================================                


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6131 02-Mar-11 22:00:15        61       1.5
  End Snap:      6132 02-Mar-11 23:00:19        57       1.3
   Elapsed:               60.07 (mins)
   DB Time:              121.31 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DFS lock handle                         675       3,110   4608   42.7 Other
DB CPU                                            2,693          37.0
inactive transaction branch             534         494    926    6.8 Other
gc cr multi block request            40,821         376      9    5.2 Cluster
PX Deq: reap credit              15,848,801         301      0    4.1 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                4.54      1.49      10.6       1.3       0.7      87.5

====================================================================================================================                
DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POHODSS       3978573848 POHODSS1            1 27-Feb-11 04:44 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb005amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6132 02-Mar-11 23:00:19        57       1.3
  End Snap:      6133 03-Mar-11 00:00:24        56       1.4
   Elapsed:               60.08 (mins)
   DB Time:              144.14 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DFS lock handle                       1,064       3,120   2933   36.1 Other
DB CPU                                            2,346          27.1
gc cr multi block request            69,160         934     14   10.8 Cluster
direct path read                     57,474         628     11    7.3 User I/O
db file sequential read             220,995         501      2    5.8 User I/O
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                1.49      1.09       7.7       0.9       2.0      91.2


====================================================================================================================                

 

And here is Top 5 Wait events on Local server

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8070 03-Mar-11 15:00:14        56       1.5
  End Snap:      8071 03-Mar-11 16:00:17        58       1.6
   Elapsed:               60.05 (mins)
   DB Time:               49.31 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink       3,811,730       2,616      1   88.4 Network
DB CPU                                              280           9.5
SQL*Net more data from dblink         6,555          38      6    1.3 Network
db file sequential read               1,557           8      5     .3 User I/O
PX Deq: Slave Session Stats           3,349           7      2     .2 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                1.11      3.29       8.3       1.8       8.3      89.0


####################################################################################################

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8071 03-Mar-11 16:00:17        58       1.6
  End Snap:      8072 03-Mar-11 17:00:20        57       1.5
   Elapsed:               60.05 (mins)
   DB Time:              254.39 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink       6,638,167      14,663      2   96.1 Network
DB CPU                                              574           3.8
db file sequential read               1,554           7      5     .0 User I/O
SQL*Net message to dblink         6,638,165           6      0     .0 Network
IPC send completion sync             11,247           3      0     .0 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                3.29      2.39       8.0       1.6       8.0      89.7


####################################################################################################


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8072 03-Mar-11 17:00:20        57       1.5
  End Snap:      8073 03-Mar-11 18:00:22        56       1.8
   Elapsed:               60.05 (mins)
   DB Time:               55.39 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink       2,079,856       2,904      1   87.4 Network
SQL*Net more data from dblink         9,071         222     24    6.7 Network
DB CPU                                              168           5.0
db file sequential read               1,588           9      5     .3 User I/O
PX Deq: Slave Session Stats           3,038           8      3     .2 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.39      2.89       8.5       2.1       6.5      88.3


####################################################################################################

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8073 03-Mar-11 18:00:22        56       1.8
  End Snap:      8074 03-Mar-11 19:00:25        58       1.7
   Elapsed:               60.05 (mins)
   DB Time:                3.02 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                               79          43.5
SQL*Net message from dblink             541          76    141   42.1 Network
db file sequential read               1,563           7      5    4.0 User I/O
SQL*Net more data from dblink        40,450           7      0    4.0 Network
PX Deq: Slave Session Stats           3,103           5      1    2.5 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.89      1.63      20.7       2.2       6.4      75.9


####################################################################################################

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8074 03-Mar-11 19:00:25        58       1.7
  End Snap:      8075 03-Mar-11 20:00:28        61       1.5
   Elapsed:               60.05 (mins)
   DB Time:                9.60 (mins)


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink          12,366         462     37   80.2 Network
SQL*Net more data from dblink        38,320          51      1    8.9 Network
DB CPU                                               39           6.7
PX Deq: Slave Session Stats           3,310           8      2    1.4 Other
db file sequential read               1,566           7      4    1.2 User I/O
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                1.63      1.21       4.5       1.2       5.6      93.8


####################################################################################################


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8075 03-Mar-11 20:00:28        61       1.5
  End Snap:      8076 03-Mar-11 21:00:31        55       1.6
   Elapsed:               60.05 (mins)
   DB Time:               26.22 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink          20,597       1,042     51   66.2 Network
SQL*Net more data from dblink        42,177         436     10   27.7 Network
DB CPU                                               65           4.1
control file sequential read          9,824           9      1     .6 System I/O
db file sequential read               1,578           8      5     .5 User I/O
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                1.21      2.78       7.4       1.6       6.1      90.3


####################################################################################################

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8076 03-Mar-11 21:00:31        55       1.6
  End Snap:      8077 03-Mar-11 22:00:34        56       1.6
   Elapsed:               60.05 (mins)
   DB Time:               11.73 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink         536,472         407      1   57.8 Network
DB CPU                                              148          21.0
SQL*Net more data from dblink        88,394          90      1   12.7 Network
db file sequential read               1,566          18     11    2.5 User I/O
control file sequential read          9,150          10      1    1.4 System I/O
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.78      2.26       8.6       2.1       6.7      88.1

####################################################################################################


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8077 03-Mar-11 22:00:34        56       1.6
  End Snap:      8078 03-Mar-11 23:00:36        56       1.6
   Elapsed:               60.04 (mins)
   DB Time:               39.13 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
SQL*Net message from dblink               4       2,299   5.7E+05   97.9 Network
DB CPU                                               22            .9
db file sequential read               1,556           9      6     .4 User I/O
PX Deq: Slave Session Stats           3,224           7      2     .3 Other
control file sequential read          5,581           7      1     .3 System I/O
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.26      2.36      10.0       2.2       8.1      86.6


####################################################################################################


DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
POCDSSS       4181781421 POCDSSS3            3 27-Feb-11 04:41 11.1.0.7.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
uss1udb009amprb  Linux x86 64-bit                   16    16       4      35.33

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      8078 03-Mar-11 23:00:36        56       1.6
  End Snap:      8079 04-Mar-11 00:00:39        52       1.5
   Elapsed:               60.05 (mins)
   DB Time:                0.84 (mins)

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                               21          41.6
db file sequential read               1,578           9      5   17.1 User I/O
PX Deq: Slave Session Stats           3,159           7      2   13.1 Other
control file sequential read          5,574           7      1   13.0 System I/O
reliable message                      1,510           3      2    6.0 Other
Host CPU (CPUs:   16 Cores:   16 Sockets:    4)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.36      1.51       6.6       1.7       8.0      90.9

####################################################################################################

Please Suggest:-
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35039478
What are the explain plains both locally and remotely?  please post using  dbms_xplan.display

still waiting for this
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dba_shashi
ID: 35060299
         ADDM Report for Task 'TASK_29845'
          ---------------------------------
 
Analysis Period
---------------
AWR snapshot range from 8152 to 8169.
Time period starts at 07-MAR-11 01.00.03 AM
Time period ends at 07-MAR-11 06.00.45 PM
 
Analysis Target
---------------
Database 'POCDSSS' with DB ID 4181781421.
Database version 11.1.0.7.0.
ADDM performed an analysis of instance POCDSSS3, numbered 3 and hosted at
uss1udb009amprb.
 
Activity During the Analysis Period
-----------------------------------
Total database time was 11145 seconds.
The average number of active sessions was .18.
 
Summary of Findings
-------------------
   Description                   Active Sessions      Recommendations
                                 Percent of Activity  
   ----------------------------  -------------------  ---------------
1  Unusual "Network" Wait Event  .12 | 65.49          4
2  Top SQL by DB Time            .12 | 64.87          5
3  Unusual "Network" Wait Event  .03 | 13.99          4
4  CPU Usage                     .01 | 3.72           1
5  Top SQL By I/O                0 | 2.15             1
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
          Findings and Recommendations
          ----------------------------
 
Finding 1: Unusual "Network" Wait Event
Impact is .12 active sessions, 65.49% of total activity.
--------------------------------------------------------
Wait event "SQL*Net message from dblink" in wait class "Network" was consuming
significant database time.
 
   Recommendation 1: Application Analysis
   Estimated benefit is .12 active sessions, 65.49% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net message from dblink" waits.
      Refer to Oracle's "Database Reference" for the description of this wait
      event. Use given SQL for further investigation.
   Rationale
      The SQL statement with SQL_ID "fa0050gsv60mq" was found waiting for
      "SQL*Net message from dblink" wait event.
      Related Object
         SQL statement with SQL_ID fa0050gsv60mq.
         SELECT DISTINCT
         T.TRADE_CYMD, T.ASOF_CYMD, T.BIG_PRX, T.SEC_NO, T.CUSIP,
         T.CONTROL_NO, T.ORDER_NO
         FROM BETA_TRD_01_CV_DLY_LH T
         WHERE (T.TRADE_CYMD = :"SYS_B_00" OR T.ASOF_CYMD = :"SYS_B_01") AND
         T.ACCT_NO In (:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B
         _06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SY
         S_B_12",:"SYS_B_13") AND
         CONCAT(T.NEW_SEC_TYPE, T.SEC_TYPE) In
         (:"SYS_B_14",:"SYS_B_15",:"SYS_B_16") AND
         T.BUY_SELL_IND = :"SYS_B_17"
         UNION SELECT DISTINCT SS.TRADE_CYMD, SS.ASOF_CYMD, SS.BIG_PRX,
         SS.SEC_NO, SS.CUSIP, SS.CONTROL_NO, SS.ORDER_NO
         FROM (SELECT DISTINCT
         JT.TRADE_CYMD , JT.ASOF_CYMD, JT.BIG_PRX, JT.SEC_NO, JT.CONTROL_NO,
         JT.ORDER_NO
         FROM BETA_TRD_01_CV_DLY_LH JT
         WHERE (JT.TRADE_CYMD = :"SYS_B_18" OR JT.ASOF_CYMD = :"SYS_B_19") AND
         JT.ACCT_NO In (:"SYS_B_20",:"SYS_B_21",:"SYS_B_22",:"SYS_B_23",:"SYS_
         B_24",:"SYS_B_25",:"SYS_B_26",:"SYS_B_27",:"SYS_B_28",:"SYS_B_29",:"S
         YS_B_30",:"SYS_B_31") AND
         CONCAT(JT.NEW_SEC_TYPE, JT.SEC_TYPE) In
         (:"SYS_B_32",:"SYS_B_33",:"SYS_B_34") AND
         JT.BUY_SELL_IND = :"SYS_B_35") ST
         JOIN (SELECT RS.ASOF_CYMD, RS.TRADE_CYMD, RS.BIG_PRX, RS.SEC_NO,
         RS.CUSIP, RS.CONTROL_NO, RS.ORDER_NO
         FROM BETA_TRD_01_CV_DLY_LH RS
         WHERE ((RS.TRADE_CYMD >= :"SYS_B_36" AND RS.TRADE_CYMD < :"SYS_B_37")
         AND
         ((RS.ASOF_CYMD >= :"SYS_B_38" AND RS.ASOF_CYMD < :"SYS_B_39") Or
         RS.ASOF_CYMD Is Null)) AND
         RS.ACCT_NO In (:"SYS_B_40",:"SYS_B_41",:"SYS_B_42",:"SYS_B_43",:"SYS_
         B_44",:"SYS_B_45",:"SYS_B_46",:"SYS_B_47",:"SYS_B_48",:"SYS_B_49",:"S
         YS_B_50",:"SYS_B_51") AND
         RS.BUY_SELL_IND = :"SYS_B_52") SS
         ON
         ST.SEC_NO = SS.SEC_NO
   Rationale
      The SQL statement with SQL_ID "76s0d7ys3z7k1" was found waiting for
      "SQL*Net message from dblink" wait event.
      Related Object
         SQL statement with SQL_ID 76s0d7ys3z7k1.
         SELECT
         MAX(NVL(T.ASOF_CYMD,T.TRADE_CYMD)) T_DATE, MIN(T.BIG_PRX) PRX,
         T.SEC_NO, T.CUSIP, T.ACCT_NO
         FROM
         BETA_TRD_01_CV_DLY_LH T
         WHERE
         ((T.TRADE_CYMD >= :"SYS_B_00" AND T.TRADE_CYMD <= :"SYS_B_01") AND
         ((T.ASOF_CYMD >= :"SYS_B_02" AND T.ASOF_CYMD <= :"SYS_B_03"))) AND
         T.ACCT_NO In (:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B
         _08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",:"SYS_B_13",:"SY
         S_B_14",:"SYS_B_15") AND
         T.SEC_NO In (:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19",:"SYS_B_
         20",:"SYS_B_21",:"SYS_B_22",:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS
         _B_26",:"SYS_B_27",:"SYS_B_28",:"SYS_B_29",:"SYS_B_30",:"SYS_B_31",:"
         SYS_B_32",:"SYS_B_33",:"SYS_B_34",:"SYS_B_35",:"SYS_B_36",:"SYS_B_37"
         ,:"SYS_B_38",:"SYS_B_39",:"SYS_B_40",:"SYS_B_41",:"SYS_B_42",:"SYS_B_
         43",:"SYS_B_44",:"SYS_B_45",:"SYS_B_46",:"SYS_B_47",:"SYS_B_48",:"SYS
         _B_49",:"SYS_B_50",:"SYS_B_51",:"SYS_B_52",:"SYS_B_53") AND
         T.BUY_SELL_IND = :"SYS_B_54"
         GROUP BY T.SEC_NO, T.CUSIP, T.ACCT_NO
   Rationale
      The SQL statement with SQL_ID "agyt7wsrbu1ns" was found waiting for
      "SQL*Net message from dblink" wait event.
      Related Object
         SQL statement with SQL_ID agyt7wsrbu1ns.
         SELECT
         :"SYS_B_0" FILETYPE,R.ACCT_NO,R.TRADE_CYMD,R.TRADE_QTY,
         R.NET_AMT,R.CUSIP,R.SYMBOL,R.SRCE_CODE,R.REC_CLASS_CODE,R.CHANGE_WHO_
         CODE
         FROM
         BETA_RAD_01_OV R
         JOIN
         BETA_ACT_01_OV A
         ON
         R.ACCT_NO = A.ACCT_NO
         WHERE
         PRODUCT_CLASS = :"SYS_B_1" AND
         MANOX = :"SYS_B_2"
   Rationale
      The SQL statement with SQL_ID "3ax1rbjpuwfk1" was found waiting for
      "SQL*Net message from dblink" wait event.
      Related Object
         SQL statement with SQL_ID 3ax1rbjpuwfk1.
         SELECT
         ACCT_NO, ACCT_CLASS_CODE AS ACCT_CLASS, S.SEC_NO AS SEC_NO, S.CUSIP,
         ORDER_NO,R.FUND_DEALER,
         Trim(S.SEC_TYPE) AS SEC_TYPE, SEC_CLASS_CODE AS SEC_CLASS,
         Trim(S.NEW_SEC_CODE) AS NEW_SEC_TYPE, T.PRODUCT_TYPE AS PRODUCT_CODE,
         TRADE_CYMD, BUY_SELL_IND AS PURCH_OR_SALE, TOTAL_ORD_QTY, NET_AMT,
         CANCEL_IND, SYNDICATE_IND, AUTO_TRADE_CODE AS AUTO_TRADE_IND,
         BIG_QTY,
         SETTLE_CYMD,BIG_EXCH_TYPE,OFFSET_ACCT_NO,PRINCIPAL_AMT,INTEREST_AMT,
         SEC_FEE_AMT, COMM_AMT,CONCESSION_AMT,LAL_COMM_AMT,REBILL_IND,
         CANCEL_REASON_TYPE, ORDER_SRCE_CODE,
         PROGRAM_NO,WIRE_CODE,PAYOUT_CODE,TTO_IND,TTO_REP
         FROM
         (BETA_TRD_01_CV_DLY_LH T
         JOIN
         BETA_SEC_01_OV S
         ON
         T.SEC_NO = S.SEC_NO)
         JOIN
         BETA_RTE_01_OV R
         ON
         R.SEC_NO = T.SEC_NO
         WHERE
         NOT (trim(S.NEW_SEC_CODE) Like :"SYS_B_0" AND trim(S.SEC_TYPE) Like
         :"SYS_B_1") AND
         ACCT_NO > :"SYS_B_2" And ACCT_NO < :"SYS_B_3" AND
         T.TRADE_CYMD >= :"SYS_B_4" AND
         T.TRADE_CYMD <= :"SYS_B_5"
   Rationale
      The SQL statement with SQL_ID "fcnqmzkpsk2vm" was found waiting for
      "SQL*Net message from dblink" wait event.
      Related Object
         SQL statement with SQL_ID fcnqmzkpsk2vm.
         select
         a.acct_no "SPS_ACCT_NO",
         a.acct_type,
         a.trade_cymd "JOURNAL_DATE",
         TRIM(d.client_last_nm)||:"SYS_B_00"||TRIM(d.client_first_nm)
         "CLIENT_NAME",
         e.acct_no "BRK_ACCT_NO",
         e.sec_no
         from
         beta_rad_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no left join cola_account_01_cv_beta d on
         a.acct_no = d.beta_acct_no left join (select
         a.acct_no,
         a.sec_no
         from
         beta_trd_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no  
         where
         a.acct_no >= :"SYS_B_01" and a.acct_no <= :"SYS_B_02" and
         (trim(c.product_class) in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05",
         :"SYS_B_06", :"SYS_B_07", :"SYS_B_08") or c.product_class is null)
         and
         a.trade_cymd >= :"SYS_B_09" and trade_cymd <= :"SYS_B_10" and
         (b.new_sec_code = :"SYS_B_11" or
         (b.new_sec_code || b.sec_type = :"SYS_B_12" and
         b.mf_share_class_id = :"SYS_B_13") or
         a.cusip in (
         :"SYS_B_14",
         :"SYS_B_15",
         :"SYS_B_16",
         :"SYS_B_17",
         :"SYS_B_18",
         :"SYS_B_19",
         :"SYS_B_20",
         :"SYS_B_21",
         :"SYS_B_22",
         :"SYS_B_23")))e on
         a.acct_no = e.acct_no
         where
         a.acct_no >= :"SYS_B_24" and a.acct_no <= :"SYS_B_25" and
         ((a.srce_code like :"SYS_B_26") or (a.srce_code like :"SYS_B_27") and
         (trim(c.product_class) in (:"SYS_B_28", :"SYS_B_29", :"SYS_B_30",
         :"SYS_B_31", :"SYS_B_32") or c.product_class is null) and
         a.trade_cymd >= :"SYS_B_33" and trade_cymd <= :"SYS_B_34" and
         (b.new_sec_code = :"SYS_B_35" or
         (b.new_sec_code || b.sec_type = :"SYS_B_36" and
         b.mf_share_class_id = :"SYS_B_37") or
         trim(a.cusip) in (
         :"SYS_B_38",
         :"SYS_B_39",
         :"SYS_B_40",
         :"SYS_B_41",
         :"SYS_B_42",
         :"SYS_B_43",
         :"SYS_B_44",
         :"SYS_B_45",
         :"SYS_B_46",
         :"SYS_B_47")))
 
   Recommendation 2: Application Analysis
   Estimated benefit is .12 active sessions, 65.49% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net message from dblink" waits in
      Service "POCDSSS".
 
   Recommendation 3: Application Analysis
   Estimated benefit is .12 active sessions, 65.49% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net message from dblink" waits with
      P1 ("driver id") value "1413697536" and P2 ("#bytes") value "1".
 
   Recommendation 4: Application Analysis
   Estimated benefit is .09 active sessions, 47.73% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net message from dblink" waits in
      Module "MSACCESS.EXE".
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Network" was consuming significant database time.
      Impact is .14 active sessions, 79.51% of total activity.
 
 
Finding 2: Top SQL by DB Time
Impact is .12 active sessions, 64.87% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .06 active sessions, 33.02% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "fa0050gsv60mq" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID fa0050gsv60mq.
         SELECT DISTINCT
         T.TRADE_CYMD, T.ASOF_CYMD, T.BIG_PRX, T.SEC_NO, T.CUSIP,
         T.CONTROL_NO, T.ORDER_NO
         FROM BETA_TRD_01_CV_DLY_LH T
         WHERE (T.TRADE_CYMD = :"SYS_B_00" OR T.ASOF_CYMD = :"SYS_B_01") AND
         T.ACCT_NO In (:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B
         _06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SY
         S_B_12",:"SYS_B_13") AND
         CONCAT(T.NEW_SEC_TYPE, T.SEC_TYPE) In
         (:"SYS_B_14",:"SYS_B_15",:"SYS_B_16") AND
         T.BUY_SELL_IND = :"SYS_B_17"
         UNION SELECT DISTINCT SS.TRADE_CYMD, SS.ASOF_CYMD, SS.BIG_PRX,
         SS.SEC_NO, SS.CUSIP, SS.CONTROL_NO, SS.ORDER_NO
         FROM (SELECT DISTINCT
         JT.TRADE_CYMD , JT.ASOF_CYMD, JT.BIG_PRX, JT.SEC_NO, JT.CONTROL_NO,
         JT.ORDER_NO
         FROM BETA_TRD_01_CV_DLY_LH JT
         WHERE (JT.TRADE_CYMD = :"SYS_B_18" OR JT.ASOF_CYMD = :"SYS_B_19") AND
         JT.ACCT_NO In (:"SYS_B_20",:"SYS_B_21",:"SYS_B_22",:"SYS_B_23",:"SYS_
         B_24",:"SYS_B_25",:"SYS_B_26",:"SYS_B_27",:"SYS_B_28",:"SYS_B_29",:"S
         YS_B_30",:"SYS_B_31") AND
         CONCAT(JT.NEW_SEC_TYPE, JT.SEC_TYPE) In
         (:"SYS_B_32",:"SYS_B_33",:"SYS_B_34") AND
         JT.BUY_SELL_IND = :"SYS_B_35") ST
         JOIN (SELECT RS.ASOF_CYMD, RS.TRADE_CYMD, RS.BIG_PRX, RS.SEC_NO,
         RS.CUSIP, RS.CONTROL_NO, RS.ORDER_NO
         FROM BETA_TRD_01_CV_DLY_LH RS
         WHERE ((RS.TRADE_CYMD >= :"SYS_B_36" AND RS.TRADE_CYMD < :"SYS_B_37")
         AND
         ((RS.ASOF_CYMD >= :"SYS_B_38" AND RS.ASOF_CYMD < :"SYS_B_39") Or
         RS.ASOF_CYMD Is Null)) AND
         RS.ACCT_NO In (:"SYS_B_40",:"SYS_B_41",:"SYS_B_42",:"SYS_B_43",:"SYS_
         B_44",:"SYS_B_45",:"SYS_B_46",:"SYS_B_47",:"SYS_B_48",:"SYS_B_49",:"S
         YS_B_50",:"SYS_B_51") AND
         RS.BUY_SELL_IND = :"SYS_B_52") SS
         ON
         ST.SEC_NO = SS.SEC_NO
   Rationale
      SQL statement with SQL_ID "fa0050gsv60mq" was executed 2 times and had
      an average elapsed time of 1826 seconds.
   Rationale
      Waiting for event "SQL*Net message from dblink" in wait class "Network"
      accounted for 100% of the database time spent in processing the SQL
      statement with SQL_ID "fa0050gsv60mq".
 
   Recommendation 2: SQL Tuning
   Estimated benefit is .02 active sessions, 9.59% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "fcnqmzkpsk2vm".
      Related Object
         SQL statement with SQL_ID fcnqmzkpsk2vm.
         select
         a.acct_no "SPS_ACCT_NO",
         a.acct_type,
         a.trade_cymd "JOURNAL_DATE",
         TRIM(d.client_last_nm)||:"SYS_B_00"||TRIM(d.client_first_nm)
         "CLIENT_NAME",
         e.acct_no "BRK_ACCT_NO",
         e.sec_no
         from
         beta_rad_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no left join cola_account_01_cv_beta d on
         a.acct_no = d.beta_acct_no left join (select
         a.acct_no,
         a.sec_no
         from
         beta_trd_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no  
         where
         a.acct_no >= :"SYS_B_01" and a.acct_no <= :"SYS_B_02" and
         (trim(c.product_class) in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05",
         :"SYS_B_06", :"SYS_B_07", :"SYS_B_08") or c.product_class is null)
         and
         a.trade_cymd >= :"SYS_B_09" and trade_cymd <= :"SYS_B_10" and
         (b.new_sec_code = :"SYS_B_11" or
         (b.new_sec_code || b.sec_type = :"SYS_B_12" and
         b.mf_share_class_id = :"SYS_B_13") or
         a.cusip in (
         :"SYS_B_14",
         :"SYS_B_15",
         :"SYS_B_16",
         :"SYS_B_17",
         :"SYS_B_18",
         :"SYS_B_19",
         :"SYS_B_20",
         :"SYS_B_21",
         :"SYS_B_22",
         :"SYS_B_23")))e on
         a.acct_no = e.acct_no
         where
         a.acct_no >= :"SYS_B_24" and a.acct_no <= :"SYS_B_25" and
         ((a.srce_code like :"SYS_B_26") or (a.srce_code like :"SYS_B_27") and
         (trim(c.product_class) in (:"SYS_B_28", :"SYS_B_29", :"SYS_B_30",
         :"SYS_B_31", :"SYS_B_32") or c.product_class is null) and
         a.trade_cymd >= :"SYS_B_33" and trade_cymd <= :"SYS_B_34" and
         (b.new_sec_code = :"SYS_B_35" or
         (b.new_sec_code || b.sec_type = :"SYS_B_36" and
         b.mf_share_class_id = :"SYS_B_37") or
         trim(a.cusip) in (
         :"SYS_B_38",
         :"SYS_B_39",
         :"SYS_B_40",
         :"SYS_B_41",
         :"SYS_B_42",
         :"SYS_B_43",
         :"SYS_B_44",
         :"SYS_B_45",
         :"SYS_B_46",
         :"SYS_B_47")))
   Action
      Investigate the SQL statement with SQL_ID "fcnqmzkpsk2vm" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID fcnqmzkpsk2vm.
         select
         a.acct_no "SPS_ACCT_NO",
         a.acct_type,
         a.trade_cymd "JOURNAL_DATE",
         TRIM(d.client_last_nm)||:"SYS_B_00"||TRIM(d.client_first_nm)
         "CLIENT_NAME",
         e.acct_no "BRK_ACCT_NO",
         e.sec_no
         from
         beta_rad_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no left join cola_account_01_cv_beta d on
         a.acct_no = d.beta_acct_no left join (select
         a.acct_no,
         a.sec_no
         from
         beta_trd_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no  
         where
         a.acct_no >= :"SYS_B_01" and a.acct_no <= :"SYS_B_02" and
         (trim(c.product_class) in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05",
         :"SYS_B_06", :"SYS_B_07", :"SYS_B_08") or c.product_class is null)
         and
         a.trade_cymd >= :"SYS_B_09" and trade_cymd <= :"SYS_B_10" and
         (b.new_sec_code = :"SYS_B_11" or
         (b.new_sec_code || b.sec_type = :"SYS_B_12" and
         b.mf_share_class_id = :"SYS_B_13") or
         a.cusip in (
         :"SYS_B_14",
         :"SYS_B_15",
         :"SYS_B_16",
         :"SYS_B_17",
         :"SYS_B_18",
         :"SYS_B_19",
         :"SYS_B_20",
         :"SYS_B_21",
         :"SYS_B_22",
         :"SYS_B_23")))e on
         a.acct_no = e.acct_no
         where
         a.acct_no >= :"SYS_B_24" and a.acct_no <= :"SYS_B_25" and
         ((a.srce_code like :"SYS_B_26") or (a.srce_code like :"SYS_B_27") and
         (trim(c.product_class) in (:"SYS_B_28", :"SYS_B_29", :"SYS_B_30",
         :"SYS_B_31", :"SYS_B_32") or c.product_class is null) and
         a.trade_cymd >= :"SYS_B_33" and trade_cymd <= :"SYS_B_34" and
         (b.new_sec_code = :"SYS_B_35" or
         (b.new_sec_code || b.sec_type = :"SYS_B_36" and
         b.mf_share_class_id = :"SYS_B_37") or
         trim(a.cusip) in (
         :"SYS_B_38",
         :"SYS_B_39",
         :"SYS_B_40",
         :"SYS_B_41",
         :"SYS_B_42",
         :"SYS_B_43",
         :"SYS_B_44",
         :"SYS_B_45",
         :"SYS_B_46",
         :"SYS_B_47")))
   Rationale
      SQL statement with SQL_ID "fcnqmzkpsk2vm" was executed 1 times and had
      an average elapsed time of 1059 seconds.
   Rationale
      Waiting for event "SQL*Net message from dblink" in wait class "Network"
      accounted for 39% of the database time spent in processing the SQL
      statement with SQL_ID "fcnqmzkpsk2vm".
   Rationale
      Waiting for event "SQL*Net more data from dblink" in wait class
      "Network" accounted for 22% of the database time spent in processing the
      SQL statement with SQL_ID "fcnqmzkpsk2vm".
 
   Recommendation 3: SQL Tuning
   Estimated benefit is .02 active sessions, 8.6% of total activity.
   -----------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "76s0d7ys3z7k1" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 76s0d7ys3z7k1.
         SELECT
         MAX(NVL(T.ASOF_CYMD,T.TRADE_CYMD)) T_DATE, MIN(T.BIG_PRX) PRX,
         T.SEC_NO, T.CUSIP, T.ACCT_NO
         FROM
         BETA_TRD_01_CV_DLY_LH T
         WHERE
         ((T.TRADE_CYMD >= :"SYS_B_00" AND T.TRADE_CYMD <= :"SYS_B_01") AND
         ((T.ASOF_CYMD >= :"SYS_B_02" AND T.ASOF_CYMD <= :"SYS_B_03"))) AND
         T.ACCT_NO In (:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B
         _08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",:"SYS_B_13",:"SY
         S_B_14",:"SYS_B_15") AND
         T.SEC_NO In (:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19",:"SYS_B_
         20",:"SYS_B_21",:"SYS_B_22",:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS
         _B_26",:"SYS_B_27",:"SYS_B_28",:"SYS_B_29",:"SYS_B_30",:"SYS_B_31",:"
         SYS_B_32",:"SYS_B_33",:"SYS_B_34",:"SYS_B_35",:"SYS_B_36",:"SYS_B_37"
         ,:"SYS_B_38",:"SYS_B_39",:"SYS_B_40",:"SYS_B_41",:"SYS_B_42",:"SYS_B_
         43",:"SYS_B_44",:"SYS_B_45",:"SYS_B_46",:"SYS_B_47",:"SYS_B_48",:"SYS
         _B_49",:"SYS_B_50",:"SYS_B_51",:"SYS_B_52",:"SYS_B_53") AND
         T.BUY_SELL_IND = :"SYS_B_54"
         GROUP BY T.SEC_NO, T.CUSIP, T.ACCT_NO
   Rationale
      SQL statement with SQL_ID "76s0d7ys3z7k1" was executed 2 times and had
      an average elapsed time of 479 seconds.
   Rationale
      Waiting for event "SQL*Net message from dblink" in wait class "Network"
      accounted for 99% of the database time spent in processing the SQL
      statement with SQL_ID "76s0d7ys3z7k1".
 
   Recommendation 4: SQL Tuning
   Estimated benefit is .01 active sessions, 7.87% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "agyt7wsrbu1ns" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID agyt7wsrbu1ns.
         SELECT
         :"SYS_B_0" FILETYPE,R.ACCT_NO,R.TRADE_CYMD,R.TRADE_QTY,
         R.NET_AMT,R.CUSIP,R.SYMBOL,R.SRCE_CODE,R.REC_CLASS_CODE,R.CHANGE_WHO_
         CODE
         FROM
         BETA_RAD_01_OV R
         JOIN
         BETA_ACT_01_OV A
         ON
         R.ACCT_NO = A.ACCT_NO
         WHERE
         PRODUCT_CLASS = :"SYS_B_1" AND
         MANOX = :"SYS_B_2"
   Rationale
      SQL statement with SQL_ID "agyt7wsrbu1ns" was executed 1 times and had
      an average elapsed time of 867 seconds.
   Rationale
      Waiting for event "SQL*Net message from dblink" in wait class "Network"
      accounted for 91% of the database time spent in processing the SQL
      statement with SQL_ID "agyt7wsrbu1ns".
   Rationale
      Waiting for event "SQL*Net more data from dblink" in wait class
      "Network" accounted for 8% of the database time spent in processing the
      SQL statement with SQL_ID "agyt7wsrbu1ns".
 
   Recommendation 5: SQL Tuning
   Estimated benefit is .01 active sessions, 6.24% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "81hdnqz16xpwx" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 81hdnqz16xpwx.
         Create table ftz3618.MNG_Beta_FI_CLIENT_TRADES AS
         select
         TRD.TRADE_CYMD,
         TRD.ACCT_NO,
         TRD.ACCT_CLASS_CODE,
         TRD.NEW_SEC_TYPE,
         TRD.SEC_TYPE,
         TRD.REP,
         TRD.SEC_NO,
         TRD.CUSIP,
         TRD.SYMBOL,
         TRD.ASOF_CYMD,
         TRD.SETTLE_CYMD,
         TRD.CONTROL_NO,
         TRD.BIG_QTY,
         TRD.BIG_PRX,
         TRD.BUY_SELL_IND,
         TRD.BIG_EXCH_TYPE,
         TRD.NET_AMT,
         TRD.OFFSET_ACCT_NO,
         TRD.OFFSET_TYPE,
         TRD.TAG_NO,
         TRD.BROKER_NO,
         TRD.CONV_FACTOR,
         TRD.ORDER_NO,
         TRD.PRICE_FACTOR,
         TRD.PRINCIPAL_AMT,
         TRD.INTEREST_AMT,
         TRD.SEC_FEE_AMT,
         TRD.COMM_AMT,
         TRD.GROSS_COMM_PCT,
         TRD.GROSS_PCNT_IND,
         TRD.CONCESSION_AMT,
         TRD.TOTAL_ORD_QTY,
         TRD.SEC_CLASS_CODE,
         TRD.CANCEL_IND,
         TRD.REBILL_IND,
         TRD.EXECUTION_TIME,
         TRD.PRODUCT_TYPE,
         TRD.DEALER_NO,
         TRD.ATD_UNIQUE_KEY,
         TRD.NET_FEE_AMT,
         TRD.TRD_EXEC_TMS,
         TRD.EXEC_COUNT,
         TRD.ORIGINATOR_ID,
         TRD.SORT_ALPHA_KEY,
         TRD.SPCL_TYPE_CODE,
         TRD.SPCL_TYPE_CODE_2,
         TRD.SPCL_TYPE_CODE_3,
         TRD.CONFIRM_NOTE
         from
         CDS_PROD_01.BETA_TRD_01_HV_DLY  TRD  
         Where
         TRD.TRADE_CYMD between '2009-10-05' and '2011-03-04'
         and TRD.TRADE_CYMD between '2010-06-28' and '2011-03-04'  
         and TRD.ACCT_CLASS_CODE in
         ('ACCS','IAEX','IAMF','IASM','MFA','PAA','SMAS','SMAV','SPS','SPSD','
         SPSO','SSIA','UMAI','UMAS')
         and TRD.NEW_SEC_TYPE || TRD.SEC_TYPE in ('DB','DM', 'D T','D B','D
         M', 'DT','UTB','UTM')
         Union all
         select
         TRD.TRADE_CYMD,
         TRD.ACCT_NO,
         TRD.ACCT_CLASS_CODE,
         SEC.NEW_SEC_TYPE,
         SEC.SEC_TYPE,
         TRD.REP,
         TRD.SEC_NO,
         TRD.CUSIP,
         TRD.SYMBOL,
         TRD.ASOF_CYMD,
         TRD.SETTLE_CYMD,
         TRD.CONTROL_NO,
         TRD.BIG_QTY,
         TRD.BIG_PRX,
         TRD.BUY_SELL_IND,
         TRD.BIG_EXCH_TYPE,
         TRD.NET_AMT,
         TRD.OFFSET_ACCT_NO,
         TRD.OFFSET_TYPE,
         TRD.TAG_NO,
         TRD.BROKER_NO,
         TRD.CONV_FACTOR,
         TRD.ORDER_NO,
         TRD.PRICE_FACTOR,
         TRD.PRINCIPAL_AMT,
         TRD.INTEREST_AMT,
         TRD.SEC_FEE_AMT,
         TRD.COMM_AMT,
         TRD.GROSS_COMM_PCT,
         TRD.GROSS_PCNT_IND,
         TRD.CONCESSION_AMT,
         TRD.TOTAL_ORD_QTY,
         TRD.SEC_CLASS_CODE,
         TRD.CANCEL_IND,
         TRD.REBILL_IND,
         TRD.EXECUTION_TIME,
         TRD.PRODUCT_TYPE,
         TRD.DEALER_NO,
         TRD.ATD_UNIQUE_KEY,
         TRD.NET_FEE_AMT,
         TRD.TRD_EXEC_TMS,
         TRD.EXEC_COUNT,
         TRD.ORIGINATOR_ID,
         TRD.SORT_ALPHA_KEY,
         TRD.SPCL_TYPE_CODE,
         TRD.SPCL_TYPE_CODE_2,
         TRD.SPCL_TYPE_CODE_3,
         TRD.CONFIRM_NOTE
         from
         CDS_PROD_01.BETA_TRD_01_HV_DLY  TRD  
         join PROD_BETA.BETA_secD_MASTER@HDS01  sec on sec.cusip = trd.cusip
         Where
         TRD.TRADE_CYMD between '2009-10-05' and '2011-03-04'
         and TRD.TRADE_CYMD between '2009-10-05' and '2010-06-25'  
         and TRD.ACCT_CLASS_CODE in
         ('ACCS','IAEX','IAMF','IASM','MFA','PAA','SMAS','SMAV','SPS','SPSD','
         SPSO','SSIA','UMAI','UMAS')
         and sec.NEW_SEC_TYPE || sec.SEC_TYPE in ('DB','DM', 'D T','D B','D
         M', 'DT','UTB','UTM')
   Rationale
      SQL statement with SQL_ID "81hdnqz16xpwx" was executed 1 times and had
      an average elapsed time of 690 seconds.
   Rationale
      Waiting for event "SQL*Net more data from dblink" in wait class
      "Network" accounted for 49% of the database time spent in processing the
      SQL statement with SQL_ID "81hdnqz16xpwx".
   Rationale
      Waiting for event "SQL*Net message from dblink" in wait class "Network"
      accounted for 46% of the database time spent in processing the SQL
      statement with SQL_ID "81hdnqz16xpwx".
 
 
Finding 3: Unusual "Network" Wait Event
Impact is .03 active sessions, 13.99% of total activity.
--------------------------------------------------------
Wait event "SQL*Net more data from dblink" in wait class "Network" was
consuming significant database time.
 
   Recommendation 1: Application Analysis
   Estimated benefit is .03 active sessions, 13.99% of total activity.
   -------------------------------------------------------------------
   Action
      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.
   Rationale
      The SQL statement with SQL_ID "fd2xq42sujq1q" was found waiting for
      "SQL*Net more data from dblink" wait event.
      Related Object
         SQL statement with SQL_ID fd2xq42sujq1q.
         select TRD.NEW_SEC_TYPE || TRD.SEC_TYPE, count(*)
         from
         CDS_PROD_01.BETA_TRD_01_HV_DLY  TRD  
         Where
         TRD.TRADE_CYMD between :"SYS_B_00" and :"SYS_B_01"
         and TRD.TRADE_CYMD between :"SYS_B_02" and :"SYS_B_03"  
         and TRD.ACCT_CLASS_CODE in
         (:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_
         09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",:"SYS_B_13",:"SYS_B_14",:"SYS
         _B_15",:"SYS_B_16",:"SYS_B_17")
         and TRD.NEW_SEC_TYPE || TRD.SEC_TYPE in (:"SYS_B_18",:"SYS_B_19",
         :"SYS_B_20",:"SYS_B_21",:"SYS_B_22",
         :"SYS_B_23",:"SYS_B_24",:"SYS_B_25")
         group by
         TRD.NEW_SEC_TYPE || TRD.SEC_TYPE
   Rationale
      The SQL statement with SQL_ID "81hdnqz16xpwx" was found waiting for
      "SQL*Net more data from dblink" wait event.
      Related Object
         SQL statement with SQL_ID 81hdnqz16xpwx.
         Create table ftz3618.MNG_Beta_FI_CLIENT_TRADES AS
         select
         TRD.TRADE_CYMD,
         TRD.ACCT_NO,
         TRD.ACCT_CLASS_CODE,
         TRD.NEW_SEC_TYPE,
         TRD.SEC_TYPE,
         TRD.REP,
         TRD.SEC_NO,
         TRD.CUSIP,
         TRD.SYMBOL,
         TRD.ASOF_CYMD,
         TRD.SETTLE_CYMD,
         TRD.CONTROL_NO,
         TRD.BIG_QTY,
         TRD.BIG_PRX,
         TRD.BUY_SELL_IND,
         TRD.BIG_EXCH_TYPE,
         TRD.NET_AMT,
         TRD.OFFSET_ACCT_NO,
         TRD.OFFSET_TYPE,
         TRD.TAG_NO,
         TRD.BROKER_NO,
         TRD.CONV_FACTOR,
         TRD.ORDER_NO,
         TRD.PRICE_FACTOR,
         TRD.PRINCIPAL_AMT,
         TRD.INTEREST_AMT,
         TRD.SEC_FEE_AMT,
         TRD.COMM_AMT,
         TRD.GROSS_COMM_PCT,
         TRD.GROSS_PCNT_IND,
         TRD.CONCESSION_AMT,
         TRD.TOTAL_ORD_QTY,
         TRD.SEC_CLASS_CODE,
         TRD.CANCEL_IND,
         TRD.REBILL_IND,
         TRD.EXECUTION_TIME,
         TRD.PRODUCT_TYPE,
         TRD.DEALER_NO,
         TRD.ATD_UNIQUE_KEY,
         TRD.NET_FEE_AMT,
         TRD.TRD_EXEC_TMS,
         TRD.EXEC_COUNT,
         TRD.ORIGINATOR_ID,
         TRD.SORT_ALPHA_KEY,
         TRD.SPCL_TYPE_CODE,
         TRD.SPCL_TYPE_CODE_2,
         TRD.SPCL_TYPE_CODE_3,
         TRD.CONFIRM_NOTE
         from
         CDS_PROD_01.BETA_TRD_01_HV_DLY  TRD  
         Where
         TRD.TRADE_CYMD between '2009-10-05' and '2011-03-04'
         and TRD.TRADE_CYMD between '2010-06-28' and '2011-03-04'  
         and TRD.ACCT_CLASS_CODE in
         ('ACCS','IAEX','IAMF','IASM','MFA','PAA','SMAS','SMAV','SPS','SPSD','
         SPSO','SSIA','UMAI','UMAS')
         and TRD.NEW_SEC_TYPE || TRD.SEC_TYPE in ('DB','DM', 'D T','D B','D
         M', 'DT','UTB','UTM')
         Union all
         select
         TRD.TRADE_CYMD,
         TRD.ACCT_NO,
         TRD.ACCT_CLASS_CODE,
         SEC.NEW_SEC_TYPE,
         SEC.SEC_TYPE,
         TRD.REP,
         TRD.SEC_NO,
         TRD.CUSIP,
         TRD.SYMBOL,
         TRD.ASOF_CYMD,
         TRD.SETTLE_CYMD,
         TRD.CONTROL_NO,
         TRD.BIG_QTY,
         TRD.BIG_PRX,
         TRD.BUY_SELL_IND,
         TRD.BIG_EXCH_TYPE,
         TRD.NET_AMT,
         TRD.OFFSET_ACCT_NO,
         TRD.OFFSET_TYPE,
         TRD.TAG_NO,
         TRD.BROKER_NO,
         TRD.CONV_FACTOR,
         TRD.ORDER_NO,
         TRD.PRICE_FACTOR,
         TRD.PRINCIPAL_AMT,
         TRD.INTEREST_AMT,
         TRD.SEC_FEE_AMT,
         TRD.COMM_AMT,
         TRD.GROSS_COMM_PCT,
         TRD.GROSS_PCNT_IND,
         TRD.CONCESSION_AMT,
         TRD.TOTAL_ORD_QTY,
         TRD.SEC_CLASS_CODE,
         TRD.CANCEL_IND,
         TRD.REBILL_IND,
         TRD.EXECUTION_TIME,
         TRD.PRODUCT_TYPE,
         TRD.DEALER_NO,
         TRD.ATD_UNIQUE_KEY,
         TRD.NET_FEE_AMT,
         TRD.TRD_EXEC_TMS,
         TRD.EXEC_COUNT,
         TRD.ORIGINATOR_ID,
         TRD.SORT_ALPHA_KEY,
         TRD.SPCL_TYPE_CODE,
         TRD.SPCL_TYPE_CODE_2,
         TRD.SPCL_TYPE_CODE_3,
         TRD.CONFIRM_NOTE
         from
         CDS_PROD_01.BETA_TRD_01_HV_DLY  TRD  
         join PROD_BETA.BETA_secD_MASTER@HDS01  sec on sec.cusip = trd.cusip
         Where
         TRD.TRADE_CYMD between '2009-10-05' and '2011-03-04'
         and TRD.TRADE_CYMD between '2009-10-05' and '2010-06-25'  
         and TRD.ACCT_CLASS_CODE in
         ('ACCS','IAEX','IAMF','IASM','MFA','PAA','SMAS','SMAV','SPS','SPSD','
         SPSO','SSIA','UMAI','UMAS')
         and sec.NEW_SEC_TYPE || sec.SEC_TYPE in ('DB','DM', 'D T','D B','D
         M', 'DT','UTB','UTM')
   Rationale
      The SQL statement with SQL_ID "fcnqmzkpsk2vm" was found waiting for
      "SQL*Net more data from dblink" wait event.
      Related Object
         SQL statement with SQL_ID fcnqmzkpsk2vm.
         select
         a.acct_no "SPS_ACCT_NO",
         a.acct_type,
         a.trade_cymd "JOURNAL_DATE",
         TRIM(d.client_last_nm)||:"SYS_B_00"||TRIM(d.client_first_nm)
         "CLIENT_NAME",
         e.acct_no "BRK_ACCT_NO",
         e.sec_no
         from
         beta_rad_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no left join cola_account_01_cv_beta d on
         a.acct_no = d.beta_acct_no left join (select
         a.acct_no,
         a.sec_no
         from
         beta_trd_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no  
         where
         a.acct_no >= :"SYS_B_01" and a.acct_no <= :"SYS_B_02" and
         (trim(c.product_class) in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05",
         :"SYS_B_06", :"SYS_B_07", :"SYS_B_08") or c.product_class is null)
         and
         a.trade_cymd >= :"SYS_B_09" and trade_cymd <= :"SYS_B_10" and
         (b.new_sec_code = :"SYS_B_11" or
         (b.new_sec_code || b.sec_type = :"SYS_B_12" and
         b.mf_share_class_id = :"SYS_B_13") or
         a.cusip in (
         :"SYS_B_14",
         :"SYS_B_15",
         :"SYS_B_16",
         :"SYS_B_17",
         :"SYS_B_18",
         :"SYS_B_19",
         :"SYS_B_20",
         :"SYS_B_21",
         :"SYS_B_22",
         :"SYS_B_23")))e on
         a.acct_no = e.acct_no
         where
         a.acct_no >= :"SYS_B_24" and a.acct_no <= :"SYS_B_25" and
         ((a.srce_code like :"SYS_B_26") or (a.srce_code like :"SYS_B_27") and
         (trim(c.product_class) in (:"SYS_B_28", :"SYS_B_29", :"SYS_B_30",
         :"SYS_B_31", :"SYS_B_32") or c.product_class is null) and
         a.trade_cymd >= :"SYS_B_33" and trade_cymd <= :"SYS_B_34" and
         (b.new_sec_code = :"SYS_B_35" or
         (b.new_sec_code || b.sec_type = :"SYS_B_36" and
         b.mf_share_class_id = :"SYS_B_37") or
         trim(a.cusip) in (
         :"SYS_B_38",
         :"SYS_B_39",
         :"SYS_B_40",
         :"SYS_B_41",
         :"SYS_B_42",
         :"SYS_B_43",
         :"SYS_B_44",
         :"SYS_B_45",
         :"SYS_B_46",
         :"SYS_B_47")))
   Rationale
      The SQL statement with SQL_ID "0q561w0b2m5y6" was found waiting for
      "SQL*Net more data from dblink" wait event.
      Related Object
         SQL statement with SQL_ID 0q561w0b2m5y6.
         select trade_cymd,count(*)
         from beta_trd_01_hv_dly
         where
         NEW_SEC_TYPE || SEC_TYPE in (:"SYS_B_0",:"SYS_B_1",
         :"SYS_B_2",:"SYS_B_3",:"SYS_B_4", :"SYS_B_5",:"SYS_B_6",:"SYS_B_7")
         group by trade_cymd
   Rationale
      The SQL statement with SQL_ID "1yu3664xp2uh2" was found waiting for
      "SQL*Net more data from dblink" wait event.
      Related Object
         SQL statement with SQL_ID 1yu3664xp2uh2.
         SELECT
         T.ACCT_NO, ACCT_TYPE, T.ACCT_CLASS_CODE, T.REP, T.CONTROL_NO,
         T.ORDER_NO, T.ASOF_CYMD, T.TRADE_CYMD, NVL(T.ASOF_CYMD, T.TRADE_CYMD)
         AS TRADE_DATE,
         T.BUY_SELL_IND , T.SEC_NO, T.CUSIP, T.SEC_TYPE, S.NEW_SEC_CODE,
         T.DESC_TXT, T.DESC_2, T.BIG_QTY, T.BIG_PRX, T.NET_AMT, T.CANCEL_IND,
         T.REBILL_IND, T.OFFSET_ACCT_NO
         FROM
         BETA_TRD_01_HV_DLY T
         JOIN
         BETA_SEC_01_OV S
         ON
         T.SEC_NO = S.SEC_NO
         WHERE
         T.BATCH_DTE_CYMD >= :"SYS_B_00" AND T.BATCH_DTE_CYMD <= :"SYS_B_01"
         AND
         T.ACCT_NO BETWEEN :"SYS_B_02" AND :"SYS_B_03" AND
         T.ACCT_CLASS_CODE In (:"SYS_B_04", :"SYS_B_05", :"SYS_B_06",
         :"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :"SYS_B_11",
         :"SYS_B_12", :"SYS_B_13", :"SYS_B_14", :"SYS_B_15", :"SYS_B_16",
         :"SYS_B_17") AND
         T.SEC_TYPE In (:"SYS_B_18", :"SYS_B_19", :"SYS_B_20")
 
   Recommendation 2: Application Analysis
   Estimated benefit is .03 active sessions, 13.99% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net more data from dblink" waits in
      Service "POCDSSS".
 
   Recommendation 3: Application Analysis
   Estimated benefit is .01 active sessions, 5.21% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net more data from dblink" waits
      with P1 ("driver id") value "1413697536" and P2 ("#bytes") value "1".
 
   Recommendation 4: Application Analysis
   Estimated benefit is .01 active sessions, 3.66% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net more data from dblink" waits
      with P1 ("driver id") value "1413697536" and P2 ("#bytes") value "2".
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Network" was consuming significant database time.
      Impact is .14 active sessions, 79.51% of total activity.
 
 
Finding 4: CPU Usage
Impact is .01 active sessions, 3.72% of total activity.
-------------------------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .02 active sessions, 9.59% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "fcnqmzkpsk2vm".
      Related Object
         SQL statement with SQL_ID fcnqmzkpsk2vm.
         select
         a.acct_no "SPS_ACCT_NO",
         a.acct_type,
         a.trade_cymd "JOURNAL_DATE",
         TRIM(d.client_last_nm)||:"SYS_B_00"||TRIM(d.client_first_nm)
         "CLIENT_NAME",
         e.acct_no "BRK_ACCT_NO",
         e.sec_no
         from
         beta_rad_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no left join cola_account_01_cv_beta d on
         a.acct_no = d.beta_acct_no left join (select
         a.acct_no,
         a.sec_no
         from
         beta_trd_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no  
         where
         a.acct_no >= :"SYS_B_01" and a.acct_no <= :"SYS_B_02" and
         (trim(c.product_class) in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05",
         :"SYS_B_06", :"SYS_B_07", :"SYS_B_08") or c.product_class is null)
         and
         a.trade_cymd >= :"SYS_B_09" and trade_cymd <= :"SYS_B_10" and
         (b.new_sec_code = :"SYS_B_11" or
         (b.new_sec_code || b.sec_type = :"SYS_B_12" and
         b.mf_share_class_id = :"SYS_B_13") or
         a.cusip in (
         :"SYS_B_14",
         :"SYS_B_15",
         :"SYS_B_16",
         :"SYS_B_17",
         :"SYS_B_18",
         :"SYS_B_19",
         :"SYS_B_20",
         :"SYS_B_21",
         :"SYS_B_22",
         :"SYS_B_23")))e on
         a.acct_no = e.acct_no
         where
         a.acct_no >= :"SYS_B_24" and a.acct_no <= :"SYS_B_25" and
         ((a.srce_code like :"SYS_B_26") or (a.srce_code like :"SYS_B_27") and
         (trim(c.product_class) in (:"SYS_B_28", :"SYS_B_29", :"SYS_B_30",
         :"SYS_B_31", :"SYS_B_32") or c.product_class is null) and
         a.trade_cymd >= :"SYS_B_33" and trade_cymd <= :"SYS_B_34" and
         (b.new_sec_code = :"SYS_B_35" or
         (b.new_sec_code || b.sec_type = :"SYS_B_36" and
         b.mf_share_class_id = :"SYS_B_37") or
         trim(a.cusip) in (
         :"SYS_B_38",
         :"SYS_B_39",
         :"SYS_B_40",
         :"SYS_B_41",
         :"SYS_B_42",
         :"SYS_B_43",
         :"SYS_B_44",
         :"SYS_B_45",
         :"SYS_B_46",
         :"SYS_B_47")))
   Action
      Investigate the SQL statement with SQL_ID "fcnqmzkpsk2vm" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID fcnqmzkpsk2vm.
         select
         a.acct_no "SPS_ACCT_NO",
         a.acct_type,
         a.trade_cymd "JOURNAL_DATE",
         TRIM(d.client_last_nm)||:"SYS_B_00"||TRIM(d.client_first_nm)
         "CLIENT_NAME",
         e.acct_no "BRK_ACCT_NO",
         e.sec_no
         from
         beta_rad_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no left join cola_account_01_cv_beta d on
         a.acct_no = d.beta_acct_no left join (select
         a.acct_no,
         a.sec_no
         from
         beta_trd_01_hv_dly a left join beta_sec_01_ov b on
         a.sec_no = b.sec_no left join beta_act_01_ov c on
         a.acct_no = c.acct_no  
         where
         a.acct_no >= :"SYS_B_01" and a.acct_no <= :"SYS_B_02" and
         (trim(c.product_class) in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05",
         :"SYS_B_06", :"SYS_B_07", :"SYS_B_08") or c.product_class is null)
         and
         a.trade_cymd >= :"SYS_B_09" and trade_cymd <= :"SYS_B_10" and
         (b.new_sec_code = :"SYS_B_11" or
         (b.new_sec_code || b.sec_type = :"SYS_B_12" and
         b.mf_share_class_id = :"SYS_B_13") or
         a.cusip in (
         :"SYS_B_14",
         :"SYS_B_15",
         :"SYS_B_16",
         :"SYS_B_17",
         :"SYS_B_18",
         :"SYS_B_19",
         :"SYS_B_20",
         :"SYS_B_21",
         :"SYS_B_22",
         :"SYS_B_23")))e on
         a.acct_no = e.acct_no
         where
         a.acct_no >= :"SYS_B_24" and a.acct_no <= :"SYS_B_25" and
         ((a.srce_code like :"SYS_B_26") or (a.srce_code like :"SYS_B_27") and
         (trim(c.product_class) in (:"SYS_B_28", :"SYS_B_29", :"SYS_B_30",
         :"SYS_B_31", :"SYS_B_32") or c.product_class is null) and
         a.trade_cymd >= :"SYS_B_33" and trade_cymd <= :"SYS_B_34" and
         (b.new_sec_code = :"SYS_B_35" or
         (b.new_sec_code || b.sec_type = :"SYS_B_36" and
         b.mf_share_class_id = :"SYS_B_37") or
         trim(a.cusip) in (
         :"SYS_B_38",
         :"SYS_B_39",
         :"SYS_B_40",
         :"SYS_B_41",
         :"SYS_B_42",
         :"SYS_B_43",
         :"SYS_B_44",
         :"SYS_B_45",
         :"SYS_B_46",
         :"SYS_B_47")))
   Rationale
      SQL statement with SQL_ID "fcnqmzkpsk2vm" was executed 1 times and had
      an average elapsed time of 1059 seconds.
   Rationale
      Waiting for event "SQL*Net message from dblink" in wait class "Network"
      accounted for 39% of the database time spent in processing the SQL
      statement with SQL_ID "fcnqmzkpsk2vm".
   Rationale
      Waiting for event "SQL*Net more data from dblink" in wait class
      "Network" accounted for 22% of the database time spent in processing the
      SQL statement with SQL_ID "fcnqmzkpsk2vm".
   Rationale
      Average CPU used per execution was 414 seconds.
 
 
Finding 5: Top SQL By I/O
Impact is 0 active sessions, 2.15% of total activity.
-----------------------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is 0 active sessions, 2.33% of total activity.
   ----------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "da7znc4df0sph".
      Related Object
         SQL statement with SQL_ID da7znc4df0sph.
         SELECT UTSNAME, FREEBYTES FROM ( SELECT DDF.TABLESPACE_NAME UTSNAME,
         SUM(NVL(DFS.BYTES, :"SYS_B_0")) FREEBYTES FROM SYS.DBA_TABLESPACES
         DDF, SYS.DBA_FREE_SPACE DFS WHERE DDF.TABLESPACE_NAME =
         DFS.TABLESPACE_NAME(+) AND DDF.CONTENTS<>:"SYS_B_1" GROUP BY
         DDF.TABLESPACE_NAME UNION ALL SELECT T1.TABLESPACE_NAME
         UTSNAME,T1.SUM_BYTES-NVL(T2.SUM_BYTES,:"SYS_B_2") FREEBYTES FROM
         (SELECT TS.NAME TABLESPACE_NAME,SUM(TF.BYTES) SUM_BYTES FROM
         V$TEMPFILE TF,SYS.TS$ TS WHERE TF.TS#=TS.TS# AND
         TS.ONLINE$<>:"SYS_B_3" GROUP BY TS.NAME) T1, (SELECT SU.TABLESPACE
         TABLESPACE_NAME,SUM(SU.BLOCKS*TF.BLOCK_SIZE) SUM_BYTES FROM
         V$SORT_USAGE SU,V$TEMPFILE TF WHERE SU.SEGFILE#=TF.FILE# GROUP BY
         SU.TABLESPACE) T2 WHERE T1.TABLESPACE_NAME=T2.TABLESPACE_NAME(+) )  
         ORDER BY UTSNAME    
   Rationale
      SQL statement with SQL_ID "da7znc4df0sph" was executed 34 times and had
      an average elapsed time of 7.5 seconds.
   Rationale
      Average time spent in User I/O wait events per execution was 7 seconds.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is .01 active sessions, 3.24% of total activity.
 
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
          Additional Information
          ----------------------
 
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
 
The database's maintenance windows were active during 35% of the analysis
period.
 
I have asked my peer members to take out the Explain Plan the way you have suggested. Further the above finding says Merge not being done in View...
Help..

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 35060400
The explain plan for your query of on the local system and the sql and plan of the remote session running your query are all I'm asking for right now.

The reports really aren't helping much, in fact, without having the plans I don't really have anything of much use to see in them.

Make sure you have the plan on BOTH systems
0
 

Author Comment

by:dba_shashi
ID: 35148707
Hello Experts,

I did manage to get the Explain plan...not the same way you wanted.

  Text
 
 Hello Experts..
I did manage to get the Explain plan However not the way you suggested..
Kind regards
shashi singh
 
 
 
   

  Text.docx
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 35154021
I can't read your document,  please use dbms_xplan.display  to generate the text
0
 

Author Closing Comment

by:dba_shashi
ID: 35283471
My fault in not providing the details needed by the experts.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35283565
why the C?

That is a penalty grade.  If you wanted more information, just ask.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now