EasyReliableDBA: 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

SWAP USAGE Analysis

Issue High SWAP

Swap/paging is used when there is no sufficient free space available on physical memory.Excessive SGA and PGA usage can lead to memory swapping issue and have impact on the database performance .Most of environment ,Monitoring is set to get alert on high usage on swap.

.Therefore We started investigation and found that SIX Databases are running on this server
and Total Memory on this server is Approx 24 GB and Most of memory is used by Oracle PGA and SGA and causing consistent high usages of swap and optimal value of SGA and PGA cannot decreased

Therefore We require capacity analysis to smooth functioning and to reduce memory and swap usage within threshold on Production Server.

Memory Allocation from Each Database

shp44ppt =600 MB
fus02ppt =3GB
dsa44ppt =1GB
dpcppt =8GB
colppt =8GB
=========
Total=21 GB approx

crewe:dummy:/home/oracle $
crewe:dummy:/home/oracle $ps -ef|grep pmon
oracle 20250704        1   0   Jun 10      - 9:39 ora_pmon_shp44ppt
oracle 30671020        1   0   Mar 04      - 23:22 ora_pmon_fus02ppt
oracle 35979502        1   0   Aug 02      - 5:25 ora_pmon_dsa44ppt
oracle 42860754        1   0   Jul 13      - 3:44 ora_pmon_dpcppt
oracle 54198338        1   0   Sep 12      - 0:14 ora_pmon_colppt
crewe:dummy:/home/oracle $

Determine the free physical memory usage of an AIX system

Use the commands Nmon then press m

Determine the memory/swap usage of an AIX system

Use the commands lsps -s.

Job Slowness issue

We got job slowness with server and job name details

Servrer =calver

Job= udmd4090

Search job location and log

calver:dmsprd:/home/oracle $cd /prod

calver:dmsprd:/prod $find . -name udmd409* 2>/dev/null

./dmdata/log/udmd4090.sh.out

./dmprod/jobs_exec/udmd4090.sh

./dmprod/jobs_mast/udmd4090.sh

calver:dmsprd:/prod $

check any problemetic event at database level

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

COUNT(1) EVENT

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

10 PL/SQL lock timer

228 SQL*Net message from client

1 SQL*Net message from dblink

1 SQL*Net message to client

1 Streams AQ: qmn coordinator idle wait

1 Streams AQ: qmn slave idle wait

1 Streams AQ: waiting for time management or cleanup tasks

1 TCP Socket (KGAS)

1 db file scattered read

1 db file sequential read

2 pipe get

COUNT(1) EVENT

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

1 pmon timer

14 rdbms ipc message

1 smon timer

14 rows selected.

SQL> select count(1) ,username,event from v$session group by username,event

COUNT(1) USERNAME EVENT

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

1 pmon timer

1 smon timer

14 rdbms ipc message

1 Streams AQ: qmn slave idle wait

1 Streams AQ: qmn coordinator idle wait

1 Streams AQ: waiting for time management or cleanup tasks

1 SYS SQL*Net message to client

3 DBSNMP SQL*Net message from client

5 PICPRD SQL*Net message from client

2 SYSTEM SQL*Net message from client

1 TIVOLI SQL*Net message from client

COUNT(1) USERNAME EVENT

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

44 WMSPRD SQL*Net message from client

173 DMS_USER SQL*Net message from client

2 CLOUD_USER SQL*Net message from client

1 DMS_BROWSE SQL*Net message from client

1 OPS$BPRD001 pipe get

10 OPS$BPRD001 PL/SQL lock timer

1 OPS$BPRD001 TCP Socket (KGAS)

2 OPS$BPRD001 db file scattered read

10158450

If you found top process from OS command topas command then map it with

database session as below

SQL>

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

where p.addr=s.paddr and p.spid=10158450

check login time and sql id from below command.

SQL>

SQL> select to_char(logon_time,'dd-mon:hh24:mi'),sid,serial#,username,status,sql_id,event from v$session where sid =1631 order by 1;

TO_CHAR(LOGO SID SERIAL# USERNAME STATUS SQL_ID EVENT

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

06-may:15:40 1631 31917 OPS$BPRD001 ACTIVE d3rcjjtj06xy0 TCP Socket (KGAS)

SQL>

Generate explain Plan as below

awrsqrpt.sql

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

select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));

select * from table(dbms_xplan.display_awr('&SQL_ID',NULL,NULL,'ALL'));

select substr(value_string,1,40) from v$sql_bind_capture where sql_id='dup0ph9mdm3fc';

select substr(value_string,1,40) from DBA_HIST_SQLBIND where sql_id='dup0ph9mdm3fc';

select sn.END_INTERVAL_TIME,sb.NAME,sb.VALUE_STRING from DBA_HIST_SQLBIND sb,DBA_HIST_SNAPSHOT sn where sb.sql_id='dup0ph9mdm3fc' and sb.WAS_CAPTURED='YES' and sn.snap_id=sb.snap_id order by sb.snap_id,sb.NAME;

SELECT a.sql_text,b.name,b.position,b.datatype_string,b.value_string FROM v$sql_bind_capture b,v$sqlarea a WHERE b.sql_id = 'dup0ph9mdm3fc' AND b.sql_id = a.sql_id;

take complete scrip from below sql

SELECT a.sql_text FROM v$sqlarea a WHERE a.sql_id = 'dup0ph9mdm3fc' ;

if require generate trace for session

exec dbms_support.start_trace_in_session (1631,31917,binds=>true,waits=>true);

-rw-r--r-- 1 oracle dba 836 Mar 04 18:01 dmsprd_ora_22675482.trc

-rw-r--r-- 1 oracle dba 3754 Mar 06 05:46 dmsprd_ora_30474446.trc

-rw-r--r-- 1 oracle dba 3566 Mar 16 15:30 dmsprd_ora_63635668.trc

-rw-rw-r-- 1 oracle dba 781 Apr 01 14:00 dmsprd_ora_44695754.trc

-rw-r--r-- 1 oracle dba 64959 May 03 23:14 dmsprd_ora_37880058.log

-rw-r--r-- 1 oracle dba 4171471 May 03 23:34 dmsprd_ora_37880058.trc

-rw-r--r-- 1 oracle dba 77447 May 03 23:35 dmsprd_ora_37880058-longer.log

calver:dmsprd:/ora/dmsprd/trace/udump $

OS process id will help in identify newly generate trace file.

SQL> select p.PID,p.SPID,s.SID

from v$process p,v$session s

where s.paddr = p.addr

and s.sid = &SESSION_ID 2 3 4

5 ;

Enter value for session_id: 1631

old 4: and s.sid = &SESSION_ID

new 4: and s.sid = 1631

PID SPID SID

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

195 58851386 1631

SQL>

Stop trace file

exec dbms_support.stop_trace_in_session (1631,31917);

exec dbms_system.set_sql_trace_in_session(1279,62705,true);

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

exec dbms_system.set_sql_trace_in_session(1631,31917,true);

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>1631, serial#=>31917, sql_trace=>FALSE);

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> select to_char(logon_time,'dd-mon:hh24:mi'),sid,serial#,username,status,sql_id,event from v$session where sid =1631 order by 1;

TO_CHAR(LOGO SID SERIAL# USERNAME STATUS SQL_ID EVENT

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

06-may:15:40 1631 31917 OPS$BPRD001 ACTIVE d3rcjjtj06xy0 TCP Socket (KGAS)

------Generate trace file in readable format for further ananlysis--

tkprof rcc1_ora_3695308.trc rcc1_ora_3695308.prf SYS=NO SORT= EXECPU,FCHCPU

tkprof rcc2_ora_1294546.trc thirdnewelapsechela.txt waits=yes sys=no sort=exeela,fchela explain='system/'

for database level analysis check PGA and SGA value and generate AWR report as well

show parameter max

NAME TYPE VALUE

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

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 2G

sga_target big integer 2G

SQL> show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 691M

SQL>

1,382== Pga

3,072 == Sga

recommended value(atpprd)

current value

sga_max_size 4815060992

sga_target 4815060992

shared_pool_size 419430400

pga_aggregate_target 1811939328

recomended value

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

sga_max_size =5,740

sga_target =2,419

shared_pool_size= 896

table size if require

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='GAZ_DESTINATION_STATION';

SUM(BYTES/1024/1024)

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

51.625

SQL>

Run SQLtrp for any recommendation

SQL>

SQL> @?/rdbms/admin/sqltrpt.sql

15 Most expensive SQL in the cursor cache

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID ELAPSED SQL_TEXT_FRAGMENT

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

5rf0k04hs5491 ########## SELECT * FROM GAZ_DESTINATION_STATION WHERE VERSION_NO

gdy0dnwknz384 ########## BEGIN CARRIER_ROUTING_DETAILS.PC_MAIN(CA_META_CLOB=>:1

avufag1k1k5fk ########## BEGIN DC_ROUTING.PC_MAIN(CA_META_CLOB=>:1 , CA_DATA_CLO

7b4r2t7vn5t4h ########## BEGIN get_atp_carrier_label_details.pc_main(:1, :2, :3,

d2w3a132p21f7 69,137.34 BEGIN GET_ATP_TRACKING_DETAILS.PC_MAIN(CA_META_CLOB=>:1

drvqacc5j0ta8 48,054.86 BEGIN ATP_DBA.PACK_GAZ0004.pc_main('I'); END;

cx99x6vjnz1p6 26,062.83 BEGIN GETTRACKINGMESSAGES.PC_MAIN(CA_IN_REQ_META_DATA=>

873742mkttpu1 24,543.07 BEGIN GET_ATP_DELIVERY_STATUS.PC_MAIN(CA_META_CLOB=>:1

0hqrdn4gwvwxa 15,782.96 BEGIN CARRIER_ROUTING_DETAILS.PC_MAIN(:1, :2, :3, :4);

7j6n2v74q4bm2 12,916.31 BEGIN PACK_CALCULATE_DC_DEL_PROMISE.PC_MAIN(CA_ATG_SITE

bg71sruhqpxta 12,285.01 SELECT PT.TIMESTAMP_CREATED, PT.LOG_DATE, PT.EVENT_CODE

SQL_ID ELAPSED SQL_TEXT_FRAGMENT

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

03ns1wz1nx4r2 12,098.69 BEGIN ATP_DBA.PACK_PAN_DATA_EXTRACT.pc_main('ALL'); END

2wz9rpn84kw1c 11,445.90 SELECT * FROM ( SELECT PAR_T.* FROM PARCEL PAR , PARCEL

03135qyarm618 9,973.96 SELECT EXTRACTVALUE(VALUE(PARAM_ARRAY),'//TrackingDetai

bhyf4zy55r2rx 8,544.46 INSERT INTO PARCEL_TRACKING ( LOG_DATE,UPI,CARR

15 Most expensive SQL in the workload repository

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Sql id

~~~~~~~~~~~~~~~~~~

Enter value for sqlid: 5rf0k04hs5491

Sql Id specified: 5rf0k04hs5491

Tune the sql

~~~~~~~~~~~~

GENERAL INFORMATION SECTION

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

Tuning Task Name : TASK_162079

Tuning Task Owner : SYS

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

Started at : 05/06/2016 17:45:37

Completed at : 05/06/2016 17:45:46

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

Schema Name: ATP_DBA

SQL ID : 5rf0k04hs5491

SQL Text : SELECT * FROM GAZ_DESTINATION_STATION WHERE VERSION_NO = :B5 AND

:B4 ||:B3 ||:B2 ||:B1 BETWEEN FROM_AREA||FROM_DISTRICT||FROM_SECT

OR||FROM_STREET AND TO_AREA||TO_DISTRICT||TO_SECTOR||TO_STREET

Bind Variables :

1 - (NUMBER):136

2 - (VARCHAR2(32)):BA

3 - (VARCHAR2(32)):13

4 - (NUMBER):3

5 - (VARCHAR2(32)):HF

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

FINDINGS SECTION (2 findings)

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

1- SQL Profile Finding (see explain plans section below)

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

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 93.89%)

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

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_162079',

task_owner => 'SYS', replace => TRUE);

Validation results

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

The SQL profile was tested by executing both its plan and the original plan

and measuring their respective execution statistics. A plan may have been

only partially executed if the other could be run to completion in less time.

Original Plan With SQL Profile % Improved

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

Completion Status: COMPLETE COMPLETE

Elapsed Time (s): 3.224566 .529278 83.58 %

CPU Time (s): .351035 .084862 75.82 %

User I/O Time (s): 2.679023 .396901 85.18 %

Buffer Gets: 107149 6540 93.89 %

Physical Read Requests: 2462 63 97.44 %

Physical Write Requests: 0 0

Physical Read Bytes: 20168704 5963776 70.43 %

Physical Write Bytes: 0 0

Rows Processed: 1 1

Fetches: 1 1

Executions: 1 1

Notes

-----

1. Statistics for the original plan were averaged over 1 executions.

2. Statistics for the SQL profile plan were averaged over 2 executions.

2- Alternative Plan Finding

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

Some alternative execution plans for this statement were found by searching

the system's real-time and historical performance data.

The following table lists these plans ranked by their average elapsed time.

See section "ALTERNATIVE PLANS SECTION" for detailed information on each

plan.

id plan hash last seen elapsed (s) origin note

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

1 4282425958 2016-05-04/03:00:35 0.265 AWR

2 2850782741 2016-05-06/09:13:24 0.519 Cursor Cache original plan

Recommendation

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

- Consider creating a SQL plan baseline for the plan with the best average

elapsed time.

execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_162079',

owner_name => 'SYS', plan_hash_value => 4282425958);

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

EXPLAIN PLANS SECTION

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

1- Original With Adjusted Cost

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

Plan hash value: 2850782741

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

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

| 0 | SELECT STATEMENT | | 1 | 63 | 1781 (1)| 00:00:22 |

|* 1 | TABLE ACCESS BY INDEX ROWID| GAZ_DESTINATION_STATION | 1 | 63 | 1781 (1)| 00:00:22 |

|* 2 | INDEX RANGE SCAN | GA_DE_ST_PK | 174K| | 625 (1)| 00:00:08 |

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

Predicate Information (identified by operation id):

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

1 - filter("FROM_AREA"||"FROM_DISTRICT"||"FROM_SECTOR"||"FROM_STREET"<=:B4||:B3||TO_CHAR(:B2

)||:B1 AND "TO_AREA"||"TO_DISTRICT"||"TO_SECTOR"||"TO_STREET">=:B4||:B3||TO_CHAR(:B2)||:B1)

2 - access("VERSION_NO"=:B5)

2- Using SQL Profile

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

Plan hash value: 4282425958

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

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

| 0 | SELECT STATEMENT | | 1 | 63 | 987 |

|* 1 | TABLE ACCESS FULL| GAZ_DESTINATION_STATION | 1 | 63 | 987 |

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

Predicate Information (identified by operation id):

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

1 - filter("VERSION_NO"=:B5 AND "FROM_AREA"||"FROM_DISTRICT"||"FROM_S

ECTOR"||"FROM_STREET"<=:B4||:B3||TO_CHAR(:B2)||:B1 AND

"TO_AREA"||"TO_DISTRICT"||"TO_SECTOR"||"TO_STREET">=:B4||:B3||TO_CHAR(:B

2)||:B1)

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

ALTERNATIVE PLANS SECTION

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

EasyReliableDBA

Friday 27 July 2018

Diagnose Database Performance Issues With Real-Time Scenarios

No comments:

Post a Comment

Search This Blog