SWAP USAGE Analysis
Issue with 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
easy44ppt =600 MB
intern02ppt =3GB
cca44ppt =1GB
abcppt =8GB
testppt =8GB
=========
Total=21 GB approx
internserver:dummy:/home/oracle $
internserver:dummy:/home/oracle $ps -ef|grep pmon
oracle 20250704 1 0 Jun 10 - 9:39 ora_pmon_easy44ppt
oracle 30671020 1 0 Mar 04 - 23:22 ora_pmon_intern02ppt
oracle 35979502 1 0 Aug 02 - 5:25 ora_pmon_cca44ppt
oracle 42860754 1 0 Jul 13 - 3:44 ora_pmon_abcppt
oracle 54198338 1 0 Sep 12 - 0:14 ora_pmon_testppt
internserver: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 =easyserver
Job= udmd4090
Search job location and log
easyserver:easyprd:/home/oracle $cd /prod
easyserver:easyprd:/prod $find . -name udmd409* 2>/dev/null
./dmdata/log/udmd4090.sh.out
./dmprod/jobs_exec/udmd4090.sh
./dmprod/jobs_mast/udmd4090.sh
easyserver:easyprd:/prod $
check any problematic 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
We can create explain plan with different command
- 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 substr(value_string,1,40) from DBA_HIST_SQLBIND where sql_id='dup0ph9mdm3fc';
To check Bind variable
- 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;
We fetch complete scrip with 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 easyprd_ora_22675482.trc
-rw-r--r-- 1 oracle dba 3754 Mar 06 05:46 easyprd_ora_30474446.trc
-rw-r--r-- 1 oracle dba 3566 Mar 16 15:30 easyprd_ora_63635668.trc
-rw-rw-r-- 1 oracle dba 781 Apr 01 14:00 easyprd_ora_44695754.trc
-rw-r--r-- 1 oracle dba 64959 May 03 23:14 easyprd_ora_37880058.log
-rw-r--r-- 1 oracle dba 4171471 May 03 23:34 easyprd_ora_37880058.trc
-rw-r--r-- 1 oracle dba 77447 May 03 23:35 easyprd_ora_37880058-longer.log
easyserver:easyprd:/ora/easyprd/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);
To check logon time for oracle session
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
or
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
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 691M
SQL>
Current Value
1,382== Pga
3,072 == Sga
Recommended value
-----------------------
sga_max_size =5,740
sga_target =2,419
shared_pool_size= 896
check 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 SQLtrpt -It will help DBA to get recommendation for particular s
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
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 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
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------
