0 Replies Latest reply: Nov 8, 2013 12:30 PM by 985871 RSS

    wait events in awr

    985871

      Hi Friends ,

       

      I am using 10.2.0.4.0 & my AWR report shows the below waits

       

      enq: MS - contention - 169,199

      db file scattered read - 48,881,173

      db file sequential read33,213,972
      enq: TX - row lock contention85,565

       

      Below are the top elapsed time queries :-

      INSERT INTO ALL$QOSOBJECTSTABLE (CUS_ID, NET_ID, NET_NAME, MD_ID, MD_NAME, 
      QOSOBJ_ID, CREATED, LAST_POLLED, CEASED, POLICYINDEX, OBJECTSINDEX, CONFIGINDEX, 
      OBJECTSTYPE, PARENTOBJECTSINDEX, INT_ID, IFINDEX, IFTYPE, POLICYDIRECTION, 
      CLASS, CFGRATE, INT_DUPLEX) SELECT A.CUS_ID CUS_ID, A.NET_ID NET_ID, A.NET_NAME 
      NET_NAME, A.MD_ID MD_ID, A.MD_NAME MD_NAME, B.ID QOSOBJ_ID, B.CREATED CREATED, 
      B.LAST_POLLED LAST_POLLED, NULL CEASED, B.POLICYINDEX POLICYINDEX, 
      B.OBJECTSINDEX OBJECTSINDEX, B.CONFIGINDEX CONFIGINDEX, B.OBJECTSTYPE 
      OBJECTSTYPE, B.PARENTOBJECTSINDEX PARENTOBJECTSINDEX, D.INT_ID INT_ID, D.IFINDEX 
      IFINDEX, D.IFTYPE IFTYPE, D.POLICYDIRECTION POLICYDIRECTION, 
      TRANSFORM.WHAT_QOSCLASS(B.ID, D.POLICYDIRECTION) CLASS, 
      TRANSFORM.GET_CFGRATE(B.ID) CFGRATE, 
      NEARREALTIME.INTERFACEIOSVR.DUPLEX$STATE(D.INT_ID) INT_DUPLEX FROM 
      ALL$MANAGED_DEVICES A, QOSDEV.QOSOBJECTSTABLE B, (SELECT QOSOBJ_ID, MAX(PERIOD) 
      FROM QOSDEV.DAILY_QOSCLASSMAPSTATSTABLE WHERE PERIOD > :B1 GROUP BY 
      QOSOBJ_ID) C, QOSDEV.QOSSERVICEPOLICYTABLE D WHERE A.MD_NAME = B.HOSTNAME AND 
      B.OBJECTSTYPE = :B2 AND B.ID = C.QOSOBJ_ID AND A.MD_NAME = D.HOSTNAME AND 
      B.HOSTNAME = D.HOSTNAME AND B.POLICYINDEX = D.POLICYINDEX
      

       

      SELECT /*+ */ DISTINCT "A1"."ID" FROM "QOSDEV"."MLOG$_RTTMONADMINTABLE" "A1" 
      WHERE "A1"."ID"<>ALL (SELECT "A2"."ID" FROM "QOSDEV"."RTTMONADMINTABLE" 
      "A2" WHERE "A1"."ID"="A2"."ID") AND "A1"."SNAPTIME$$">:1 AND 
      "A1"."DMLTYPE$$"<>'I'
      

       

      DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := 
      FALSE; BEGIN DAILYAGG.POP$HOUR_CPU; :mydate := next_date; IF broken THEN :b := 
      1; ELSE :b := 0; END IF; END;
      

       

      kindly request your help in identifying the bottlenecks .

       

      Regards,

      DBApps