Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to reduce numbers of wait events of DBLINK from 4:00 AM to 6:00 AM during that time period every

Quanwen ZhaoNov 24 2017 — edited Dec 7 2017

Hello,experts

Now,there have plenty of wait events of DBLINK from 4:00 AM to 6:00 AM during that time period every day on Oracle DB 11.2.0.4.0 Data Guard primary For Linux x64.

I have captured an AWR report for comparing during that time period.

See my snapshot as follows,

6.png

7.png

8.png

9.png

Next,it's AWRSQLRPT report about two sql(from snapshot previous using red rectangular box to mark).

  1. SQL_ID is 5hv12nch90c64

10.png

11.png

12.png

  1. SQL_ID is 3dvhmxnrh4pku

13.png

14.png

15.png

Finally,I have used VIEW v$active_session_history(ASH) to check numbers of wait events about DBLINK during that time period.

SYS@orcl29> set linesize 400

SYS@orcl29> set pagesize 500

SYS@orcl29> col sql_id for a15

SYS@orcl29> col event for a30

SYS@orcl29> col count(*) for 999999999

SYS@orcl29> select sql_id,event,count(*) from v$active_session_history where sample_time between to_date('2017-11-24 04:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2017-11-24 06:00:00','yyyy-mm-dd hh24:mi:ss') and event like '%dblink%' group by sql_id,event order by 3 desc,1;

SQL_ID EVENT COUNT(*)

--------------- ------------------------------ ----------

5hv12nch90c64 SQL*Net message from dblink 18820

3dvhmxnrh4pku SQL*Net message from dblink 18321

7gtx71zyhtkza SQL*Net message from dblink 2431

c6fk526c9fxyu SQL*Net message from dblink 2429

5hv12nch90c64 SQL*Net more data from dblink 602

3dvhmxnrh4pku SQL*Net more data from dblink 552

1v43bvmr5jf9m SQL*Net message from dblink 163

078f2p3q4y0p4 SQL*Net message from dblink 161

c6fk526c9fxyu SQL*Net more data from dblink 37

            SQL\*Net message from dblink            24

7gtx71zyhtkza SQL*Net more data from dblink 23

4vk24sfq0hbxs SQL*Net message from dblink 13

55xu2ckbah1st SQL*Net message from dblink 13

078f2p3q4y0p4 SQL*Net more data from dblink 1

1v43bvmr5jf9m SQL*Net more data from dblink 1

15 rows selected.

SYS@orcl29> select sql_id,event,count(*) from v$active_session_history where sample_time between to_date('2017-11-24 06:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2017-11-24 07:00:00','yyyy-mm-dd hh24:mi:ss') and event like '%dblink%' group by sql_id,event order by 3 desc,1;

no rows selected

Now,how to analyze it continually for me?

Please giving me some effective suggestion,thanks

Best Regards

Quanwen Zhao

This post has been answered by Jonathan Lewis on Nov 30 2017
Jump to Answer

Comments

843811
i have stripped the code to the minimum to produce the 403 error, and here it is:
import java.io.*;
import java.net.*;
import java.util.StringTokenizer;
import com.sun.net.ssl.HttpsURLConnection;
import com.sun.net.ssl.KeyManager;
import com.sun.net.ssl.TrustManager;
import com.sun.net.ssl.SSLContext;
import com.sun.net.ssl.X509TrustManager;
import javax.net.ssl.SSLSocketFactory;

public class httpsquery {
    
    public static void main(String[] args) throws Exception {
        URL url;
        HttpsURLConnection urlConn;
        String sessionCookie = null;
        BufferedInputStream httpResponseStream;
        DataOutputStream httpRequestStream;
        System.setProperty("https.proxyHost","proxy.at.int.atosorigin.com");
        System.setProperty("https.proxyPort","8080");
        System.setProperty("java.protocol.handler.pkgs", "com.sun.net.ssl.internal.www.protocol");
        java.security.Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());
        try {
            javax.net.ssl.SSLSocket sock = null;
            X509TrustManager tm = new DummyX509TrustManager();
            KeyManager[] km = null;
            TrustManager[]tma = { tm };
            SSLContext sc = SSLContext.getInstance("SSLv3");
            sc.init(km,tma,new java.security.SecureRandom());
            SSLSocketFactory sf1 = sc.getSocketFactory();
            HttpsURLConnection.setDefaultSSLSocketFactory(sf1);

            System.out.println("opening connection");
            url = new URL("https://ebmxdev.extra.daimlerchrysler.com/servlet/ebmxcontroller?function=basictest&username=BASICTEST");
            urlConn = (HttpsURLConnection)url.openConnection();
            urlConn.setDoInput (true);
            urlConn.setDoOutput (true);
            urlConn.setUseCaches (false);
            urlConn.setRequestMethod("GET");
            urlConn.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            urlConn.setRequestProperty("Authorization", "Basic");
            if (sessionCookie != null) urlConn.setRequestProperty("Cookie",sessionCookie);
            System.out.println("connection opened");
            for (int i = 1; i <= 6; ++i) { System.out.println(" Header " + i + ": " + urlConn.getHeaderFieldKey(i) + ": " + urlConn.getHeaderField(i)); } 
            if (sessionCookie == null) {
                String setCookie = urlConn.getHeaderField("Set-Cookie");
                if (setCookie == null) setCookie = urlConn.getHeaderField("Set-cookie");
                if (setCookie == null) System.out.println("no cookie!!!!!!");
                StringTokenizer st = new StringTokenizer("" + setCookie,";");
                sessionCookie = st.nextToken();
            }
            System.out.println("cookie is " + sessionCookie);

            httpResponseStream = new BufferedInputStream(urlConn.getInputStream ());
            int c;
            byte[] buffer = new byte[1024];
            while ((c = httpResponseStream.read(buffer)) != -1) {
                System.out.write(buffer,0,c);
            }
            httpResponseStream.close();

            // close connection
            urlConn.disconnect();
            System.out.println("connection closed");
            System.out.println("called basictest");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

class DummyX509TrustManager implements X509TrustManager { 
    public boolean isClientTrusted(java.security.cert.X509Certificate[] chain) { return true; } 
    public boolean isServerTrusted(java.security.cert.X509Certificate[] chain) { return true; } 
    public java.security.cert.X509Certificate[] getAcceptedIssuers() { return null; } 
}
hope a java guru will find what i'm doing wrong
i have also tried it with POST, but the effect was the same

any help welcome

thanks,
big nell
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 4 2018
Added on Nov 24 2017
37 comments
2,545 views