Diagnose Database Performance Issues With Real-Time Scenarios

Diagnose Database Performance Issues

With Real-Time Scenarios

 Database performance is slow

Check server level performance  from topas for CPU utilization  and nmon then press m for physical memory utilization

topas

                                                Syscall   20055  Writech   43815

CPU  User%  Kern%  Wait%  Idle%  Physc   Entc   Reads       805  Rawin         0

ALL    8.6    5.5    0.0   85.9   0.49   24.3   Writes      317  Ttyout      817

                                                Forks         4  Igets         0

Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out  Execs         6  Namei       507

Total   538.2    867.6   819.4   268.5   269.7  Runqueue    7.1  Dirblk        0

                                                Waitqueue   0.0

Disk    Busy%     KBPS     TPS KB-Read KB-Writ                   MEMORY

Total     0.1    162.5    12.0   121.0    41.5  PAGING           Real,MB   92160

                                                Faults     2631  % Comp     60

FileSystem        KBPS     TPS KB-Read KB-Writ  Steals        0  % Noncomp  11

Total            529.4   453.5  528.4    1.0    PgspIn        0  % Client   11

                                                PgspOut       0

Name            PID  CPU%  PgSp Owner           PageIn        0  PAGING SPACE

oraroota   63373722   1.0  59.5 root            PageOut       0  Size,MB   30720

BESClien   19595944   1.0  55.8 root            Sios          0  % Used      1

ocssd.bi    8454208   0.5  94.2 oracrs                           % Free     99

stat_dae    9044190   0.4  31.7 root            NFS (calls/sec)

oracle     32768810   0.3   5.7 oracrs          SerV2         0  WPAR Activ     0

oracle     17629754   0.3  18.6 fubprd          CliV2         0  WPAR Total     0

oracle     26083880   0.3  29.4 fuoprd          SerV3         0  Press: "h"-help

oracle     10158382   0.3  10.8 oracrs          CliV3         0         "q"-quit

oraroota   56230570   0.3  65.4 root            SerV4         0

oracle     26346194   0.3  25.8 fuoprd          CliV4         0

oracle     60490344   0.3  28.6 fusprod

oracle     36897450   0.3  36.9 fuoprd

oracle     41484692   0.2   3.6 fusprod

oracle      1835388   0.2  11.9 oracrs

oracle     65667270   0.2  10.9 oracrs

oracle     49021256   0.2  10.3 oracrs

oracle     10551908   0.2  36.9 fubprd

oracle     56886260   0.2  25.7 fuoprd

oraagent   59114002   0.2  46.3 fubprd

oracle     45286116   0.2   3.5 fusprod

Check any problematic process

buxton[/home/fubprd]$ ps -ef|grep 41484692

 fusprod 41484692        1   1 11:09:16      -  0:00 ora_q002_FSPROD2

  fubprd 18285276  5767774   0 11:42:38  pts/1  0:00 grep 41484692

buxton[/home/fubprd]$

Map OS process ID with Database session

select s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),machine,module,sql_id  from v$session s, v$process p

where p.addr=s.paddr and p.spid in ('41484692','49021256');

Check query details

 select sql_id,rows_processed,upper(sql_fulltext) from v$sqlarea where sql_id='&sql_id';

Check memory usage

nmon then press m for memory statistic

Check session detail userwise

select count(1),username,status  from v$session group by username,status;

Check any problematic even

 select count(1),event from v$session group by event;

  COUNT(1) EVENT

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

       122 db file sequential read

        53 Streams AQ: waiting for messages in the queue

        26 PL/SQL lock timer

         5 EMON slave idle wait

         8 Space Manager: slave idle wait

         1 SQL*Net message to client

      1167 SQL*Net message from client

         1 pmon timer

        59 pipe get

         2 read by other session

         1 smon timer

  COUNT(1) EVENT

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

         2 DIAG idle wait

        28 rdbms ipc message

         1 VKTM Logical Idle Wait

        22 TCP Socket (KGAS)

         3 db file parallel read

         1 Streams AQ: qmn slave idle wait

         3 library cache: mutex X

         1 Streams AQ: emn coordinator idle wait

         1 Streams AQ: qmn coordinator idle wait

         1 JOX Jit Process Sleep

         1 Streams AQ: waiting for time management or cleanup tasks

To check long running Query

select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"

from v$session_longops where sofar <> totalwork;

 Generate explain plan to understand SQL

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

select * from table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'))

session details

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

select sid,serial#,,username,machine,sql_id,to_char(s.logon_time,'dd-mon:hh24:mi') from v$session where sid in ('141','84','860');

Check session level lock

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||

sid sess, id1, id2, lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

ORDER BY id1, request;

Check object level locking

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

select a.sid, a.serial#,OBJECT_NAME

from v$session a, v$locked_object b, dba_objects c =

where b.object_id = c.object_id

and a.sid = b.session_id;

To kill blocking session blocking session if require

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

ALTER SYSTEM KILL SESSION '55,3161' immediate;

Check object size and index details including statistics

select a.owner, a.table_name, a.num_rows, b.bytes/1048576 MB, a.last_analyzed,b.tablespace_name from dba_tables a, dba_segments b 

where  a.owner=b.owner and a.table_name=b.segment_name and a.table_name='&table_name' order by b.bytes desc;

SELECT t.table_name, i.index_name,i.blevel,i.status,t.last_analyzed FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND i.index_name in ('PF_ORD_I_08'

,'PF_INT_CHG_I_01','PF_ORD_I_07');

Generate AWR ,ADDM and ASH for further Analysis

SQL script for getting AWR Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

SQL script for getting AWR Report for  single instance:

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL script for getting ASH Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpti.sql

SQL script for getting ASH Report for single Instance:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

SQL script for getting ADDM Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql

SQL script for getting ADDM Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

to generate an AWR Compare

SQL>@$ORACLE_HOME/rdbms/admin/ awrddrpt.sql

   

Spike issue on Database

We got call for spike on database on specific time ,We checked AWR,ADDM and OEM as part of investigation, From OEM ,We can see spike clearly

Details for SQL

1) 0b5bpfksvy2fv        SELECT 'exist' FROM V$LOCK WHERE ID1 = :B1 AND SID = SYS_CONTEXT ('USERENV', 'SID')

2) bd205c2pnzrq7        SELECT DISTINCT PARM.* , CASE WHEN PRD.PROD_REVIEW_ID IS NOT NULL AND PRD.STATUS = 'U' THEN 'P' ELSE 'U' END AS NEW_STATUS FROM PROD_REVIEW_DETAIL PRD , TABLE(CAST(:B1 AS TY_INS_PROD_DETS_TAB)) PARM WHERE NVL(PARM.CA_SKU_CREATE , 'a') = NVL(PRD.SKU(+) , 'a') AND NVL(PARM.CA_STYLE_CREATE , 'a') = NVL(PRD.STYLE(+) , 'a') AND NVL(PARM.CA_HIERARCHY_LEVEL_CREATE, 0) = NVL(PRD.HIERARCHY_LEVEL(+), 0) AND NVL(PARM.CA_HIERARCHY_ID_CREATE , 0) = NVL(PRD.HIERARCHY_ID(+) , 0) AND NVL(PARM.CA_SUPPLIER_CODE_CREATE , 'a') = NVL(PRD.SUPPLIER_CODE(+) , 'a') AND NVL(PARM.CA_DC_NUMBER_CREATE , 0) = NVL(PRD.DC_NUMBER(+) , 0) AND PRD.STATUS(+) = 'U'

From AWR

From ADDM Report

SQL Profile is just like gathering statistics for a query, it stores additional information which help optimizer to determine the correct execution plan.
and SQL profile is stored in the data dictionary. The optimizer uses this information during optimization to determine the most optimal plan.

Recommendation

Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile.
 execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_157526',
         task_owner => 'SYS', replace => TRUE);

After implemendation ,above recommendation ,We compare the report to know exact performance improvement.

I have compared report for particular SQL between  31-Jul-2017 and 31-Aug-2017  and noticed improvement in particular SQL

Buffer get reduced from 76,346,719 to 74,694,175  and cluster wait  from 2,227.10 to 1,817.69 but SQL ID 6szx5jytmv463 have consumed more resource on Database on 31 aug

31 Aug analysis

Identified New SQL which consumed high resource on database on 31 aug

6szx5jytmv463       JDBC Thin Client     SELECT /*+ PARALLEL(4) */ A.AL...

6szx5jytmv463       SELECT /*+ PARALLEL(4) */ A.ALERT_ID, A.ALERT_GROUP_ID FROM ALERT_GROUP AG, ALERT A WHERE A.SUPPLY_ID IN ( SELECT SUPPLY_ID FROM TABLE( CAST( :B2 AS TY_SUPPLY_ID_TAB))) AND A.ALERT_GROUP_ID = AG.ALERT_GROUP_ID AND AG.ALERT_TYPE_CODE = :B1 AND AG.EXPIRED_IND != 1 AND A.EXPIRED_IND != 1

31 July Report

High utilization on Database

As multiple times we observed spike on CAMPRD database and resulted AMZ alert

Therefore Yesterday we closely monitored database CAMPRD during   15:30 to 16:45

and observed create table statement was consuming high resource on database and surprised to see DDL statement in business hour

During investigation ,We are not able to get complete statement  of SQL ID(Table name=HKO_ORD_I_DETAIL)

Can please check from your end and provide your input.

From AWR

From ADDM

from ASH

From 12 C Grid

Peak Stress test

Can you please create AWR and ADDM report(CAM44PPT and CFRPPT) and share your observations for below timings?

Objective of the test was executed to capture baseline NFT's performance in Pipe2 refresh and with new ATG server mix (131 servers). Also note that we have executed 80% load test of Cybermonday Volumes.
Duration - 8/18/2017 10:28:09 AM to 8/18/2017 2:28:09 PM
Peak Hour - 8/18/2017 11:33:00 AM to 8/18/2017 12:33:00 PM

Also, can you please share the DB snapshot report

Note: We saw few listeners in Sawley/Spalding, failing during load test from 13:37 to 14:07

Upon investigation ,We found that SQL  SQL fu6mmf9w716qj  consumed more resource on Database CAM44PPT during  load test

Request you please create index as per below recommendation and we did not observed any performance slowness on CFRPPT database

Please see attached AWR

From OEM

Apart from above ,I also observed ITL(Interested Transaction List) on index PK_RH  is high due to massive amount of concurrent demand on this index.

Therefore  recommend to increase INI_TRANS from 2 to 20.

SQL Details

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

Schema Name: PILOT_DBA

SQL ID     : fu6mmf9w716qj

SQL Text   : SELECT AXD.*, BU.ISO_CURRENCY_CODE FROM BUSINESS_UNIT BU,

             ATG_XREF_DELETED AXD WHERE AXD.BU_REF =

             BU.BUSINESS_UNIT_REFERENCE AND (BU.ISO_CURRENCY_CODE = :B2 OR

             :B2 IS NULL) AND AXD.ATG_REFERENCE = :B1

Bind Variables :

 1 -  (VARCHAR2(32)):NULL

 2 -  (VARCHAR2(32)):NULL

 3 -  (VARCHAR2(128)):02182981

  Recommendation (estimated benefit: 99.89%)

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

 creating the recommended index.

    create index PILOT_DBA.IDX$$_117AB30001 on

    PILOT_DBA.ATG_XREF_DELETED("ATG_REFERENCE");

ANALYSIS

Alert might be triggered due to high User I/O on database,upon investigation from AWR and ASH ,We found that below SQLs are top consumer of database resource.

1) gpy47j2rr22p5

Can you please involved application team and let us know if PARALLEL can be reduced from 8 to 4 for IDT_TPDCtable creation

2) 09s1rfrgpb1g2


The PAYMENT_PLAN_CREDIT_SHARE table is 370GB and indexes on this are also huge in size Therefore running query on this table are taking more resources .
I would recommend for partition of this table or housekeeping on this table or Query need to be optimized for better execution Plan

FROM AWR ANALYSIS

Completed SQL

gpy47j2rr22p5

CREATE TABLE IDT_TPDC TABLESPACE TSP_SMALL PARALLEL (DEGREE 8)STORAGE (INITIAL 640K NEXT 640K) AS ( select rowid row_to_delete from ODT_ML1257E where timestamp_created <(sysdate - 15) )

From ASH report

09s1rfrgpb1g2

SELECT * FROM ( SELECT PPCS.PAYMENT_PLAN_SID, PPCS.DATE_TC_ACCOUNTING, PPCS.MOVEMENT_ITEM_TYPE_CODE, PPCS.OBJECT_REF_SID_ORIG_OBJ, PPCS.OBJECT_REF_CODE_ORIG_OBJ, PPCS.TIMESTAMP_CREATED, PPCS.CONTRACT_NUMBER, PPCS.PAY_ALLOC_SEQUENCE_NUM, PPCS.SUB_ACCOUNT_TYPE_CODE, PPCS.DATE_CREDIT_ACCEPTED, PPCS.ENTERPRISE_WIDE_SID, PPCS.TRANSACTION_REFNO, PPCS.CHARGE_AMT_SIGNED, PPCS.PURCHASE_MECHANISM_NUMBER, PPCS.PPCS_TYPE, PPCS.TOT_REVERSED_AMT_SIGNED, PPCS.CHARGING_LFCYS_CODE, PPCS.STATEMENTING_LFCYS_CODE, PPCS.MIV_CODE, PPCS.MIV_TYPE_CODE, PPCS.APR, PPCS.SOURCE_OBJECT_REF_CODE, PPCS.SOURCE_OBJECT_REF_SID, PPCS.OPENING_BALANCE_AMT_SIGNED, PPCS.CASH_PRICE_AMT, PPCS.CREDIT_OFFER_CODE, PPCS.ROWID PPCS_ROWID FROM PAYMENT_PLAN_CREDIT_SHARE PPCS WHERE PPCS.CONTRACT_NUMBER = :B1 ORDER BY PPCS.DATE_TC_ACCOUNTING DESC, PPCS.PAY_ALLOC_SEQUENCE_NUM DESC ) WHERE ROWNUM = 1

Table Details


OWNER TABLE_NAME NUM_ROWS MB LAST_ANALYZ
------------------------------ ------------------------------ ---------- ---------- -----------------------------------------------------------------------
PILOT_DBA PAYMENT_PLAN_CREDIT_SHARE 2334869164 380400 23-nov-2016


TABLE_NAME INDEX_NAME BLEVEL STATUS LAST_ANALYZ
------------------------------ ------------------------------ ---------- -------- --------------------------------------------------------------------------------
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_03 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_05 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_04 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PK_PAY_PLAN_CRED_S 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_01 3 VALID 23-nov-2016
PAYMENT_PLAN_CREDIT_SHARE PF_PAY_PLAN_CRED_S_02 3 VALID 23-nov-2016

DML on Table

TABLE_NAME TIMESTAMP SUM(M.INSERTS) SUM(M.UPDATES) SUM(M.DELETES) NUM_ROWS LAST_ANALYZ
------------------------------ ----------- -------------- -------------- -------------- ---------- ---------------------------------------------------------------------------------
PAYMENT_PLAN_CREDIT_SHARE 23-may-2017 266574883 265166199 21923 2334869164 23-nov-2016


Size of Indexes


OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------------------------------------------------------------------
PILOT_DBA PF_PAY_PLAN_CRED_S_05 INDEX 136.523438
PILOT_DBA PF_PAY_PLAN_CRED_S_04 INDEX 125.683594
PILOT_DBA PF_PAY_PLAN_CRED_S_03 INDEX 166.503906
PILOT_DBA PF_PAY_PLAN_CRED_S_02 INDEX 114.648438
PILOT_DBA PF_PAY_PLAN_CRED_S_01 INDEX 143.75
PILOT_DBA PK_PAY_PLAN_CRED_S INDEX 184.082031