Pages

Advertisement

Showing posts with label Performance Tuning. Show all posts
Showing posts with label Performance Tuning. Show all posts

Wednesday, May 6, 2020

DEVOPS - How to find out processes in 'D' state of uninterruptible sleep (High I/O Wait)

 
IN this blog I am going to write on how to find out processes state and specially when there is high I/O wait on the system. 


The key is to list down the process which are in 'D' or 'S' state (primarily 'D').


Run below command to list processes in 'D' state. Replace '^D' with '^S' to get list of processes in S State.
Once you get the process you can do action on it 

 for x in `seq 1 1 10`; do ps -eo state,pid,cmd | grep "^D"; echo "----"; sleep 5; done

Monday, January 28, 2019

Linux: OSWatcher

 
OSWatcher is one of the best utilities provided by Oracle (Free of cost) and runs on almost all Operating Systems (Unix based only)
To Install OSW

 Step 1. Download OSWatcher (Doc ID 301137.1)

 Step 2. Untar 
# Change version as per latest available. 

 $ tar -xf /tmp/oswbb812.tar

 Step 3.  Start OSW

 $ cd /root/osw/oswbb
$ ./startOSWbb.sh 60 10 gzip


 Step 4. Stop OSW
$ cd /root/osw/oswbb
$ ./stopOSWbb.sh 
Other Details
1. CMD OPTIONS

  • First variable is the interval in seconds between 2 samples
  • Second Variable is total hours for which data is kept
  • Third Variable is the utility to zip to compress the files generated (acceps zip,gzip, None)
  • Fourth Variable is the path (optional) where to put the filess, when left blank it is the archive directory where the files are generated and kept. 
  • Fourth variable cannot be provided without 3rd 
I personally recommend 60 seconds interval with 30 days of data kept on the system for you to analyze.

 2. Scheduling
To Schedule you can just schedule (cron) the OSWatcher to run every 10 minutes or so.
The key thing is OSWatcher does not starts if an instance of it is running. This makes it by default highly available, or if you want to be advanced you can create it as a service with help of sysadmin team or yourself.


 Latest Downloads and Other OSWatcher related information available at - 

 OSWatcher (Includes: [Video]) (Doc ID 301137.1)

Tuesday, January 15, 2019

Performance - Sar - How to Plot Excel Chart (Complete Month)

 
In this blog I am going to talk about how to excel plot a chart using sar data in an easy 2 step process.

 Step 1 - Extract Required Sar data in semicolon separated format

Below is a script which will help you extract data in the format you wish to.
The script is quite clear for the pros, however here is what I am doing

 1. Creating a temporary file name variable for data.
2. Creating a CMD variable which is basically sadf comand by sysstat package, this command outputs sar data into semicolon seperated format.
3. Creating OPTION variable - this is basically what data you want to extract from the files. All the options are valid which are used in the "sar command" (so in place of -q you can give, -R, -r, -S, -H, -p etc etc). Do not remove the double '--'
4. Print the header into the file
5. Run the command for all files 
tempfile=sadf_`date "+%Y_%m_%d_%H_%M_%S"`.out
CMD="sadf -dt "
OPTION="-- -q" #This Option is sar option for Stat
#Print header into file
$CMD /var/log/sa/sa21 $OPTION | head -1 | awk '{print $2}' > ${tempfile}

 for f in `ls -rt /var/log/sa/sa[0-9][0-9]` ; 
do 
$CMD $f $OPTION | sed -e '/LINUX-RESTART/d' -e '/hostname/d' >> ${tempfile} ;
done

 (Note - the sed command deletes the linux restart entries and the headers which follow in the sar file after the restart.)
 Now once you run this, you will have 1 month data into a single file with a semicolon format. 

 Step 2 - Download the file and copy paste to excel.
The Second step is your excel skills, you copy the data to excel file separate the data using delimiter and plot your charts. 

 1. Paste Data into excel and separate using semicolon


 2. The data is all yours now and you should use your excel skills (hint - pivot chart or normal chart) to do this.

 Here is an example 

Select The data And Create Pivot Chart


 Fill in the Chart Details (Extreme Right)
Good luck working.

Thursday, July 26, 2018

Oracle Database: Oracle Enterprise Linux : Page Allocation Failure : Performance/Kernel Tuning / - System Unresponsive

 
We recently had issue with one of our physical servers. 
It will become unresponsive and hang in a lot of situations. 

 Oracle Linux Server release 7.3
NAME="Oracle Linux Server"

VERSION="7.3"

Linux pwbdd-fe-1 4.1.12-61.1.18.el7uek.x86_64 #
256GB of RAM and 16 CPU (2socket * 8 cores)

 Looked into sar logs and found out the memory usage was around 99.7%.

 We found out Page allocations error  in the /var/log/messages

 kworker/u66:0: page allocation failure: order:1, mode:0x204020

CPU: 8 PID: 1579 Comm: kworker/u66:0 Tainted: G           OE   4.1.12-61.1.18.el7uek.x86_64 #2

 There was no entry in Oracle Database log or any trace file, so database had to do minimal with it. 

 We finally fixed it with below kernel settings.


  •  vm.min_free_kbytes = 1048576 
  •  vm.swappiness = 60
  •  vm.dirty_ratio = 20


I have to admit that I took help of an ODA with Linux 7 and similar amount of RAM as well


Referred Links and 
https://utcc.utoronto.ca/~cks/space/blog/linux/DecodingPageAllocFailures
http://tech.donghao.org/2016/03/25/avoid-page-allocation-failure-for-linux-kernel/
https://unix.stackexchange.com/questions/128539/what-would-happen-if-the-amount-of-free-memory-vm-min-free-kbytes-was-too-low
https://stackoverflow.com/questions/21374491/vm-min-free-kbytes-why-keep-minimum-reserved-memory

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/performance_tuning_guide/s-memory-tunables
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/performance_tuning_guide/sect-red_hat_enterprise_linux-performance_tuning_guide-configuration_tools-configuring_system_memory_capacity



Oracle Support Docs

Friday, July 28, 2017

Oracle Database - Log Switches Per hour

 
This script is used to find out log switches per hour in Oracle Database

ROWNUM < 31  tells to display data for last 30 days


SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <31;





Monday, May 29, 2017

Oracle Database - Mining Active Session History (ASH) for Plan/SQL Execution Wait Events

 
In this blog I am going to discuss about mining ASH for wait events for a specific sql session or specific sql or specific plan hash value execution wait events.

This can be really powerful in cases like - 

1. when sql_monitoring report is no longer available,
2. or you want to tune a particular plan and you want to know at what plan_line it is taking more time
3. you want to tune a plan and want to know at what line it is taking time with what kind of wait event.

There are many other use cases but the above are summary of what can be done. 

Now I will quickly jump onto the sql part.

First SQL is to find out the Time taken by SQL on each plan line per the options

set lines 500 pages 500
col object_name for a25

SELECT plan_hash_value,
id line,
operation,
options,
object_name,
sum(10)
FROM dba_hist_active_sess_history a,
dba_hist_sql_plan b
WHERE a.sql_plan_hash_value = b.plan_hash_value
AND a.sql_id = b.sql_id
AND a.sql_plan_line_id = b.id
AND a.sql_id = 'ah9xc371174p7'
AND a.session_id = 446
AND a.session_serial# = 26701
GROUP BY plan_hash_value, id, operation, options, object_name
ORDER by 1,2;


 

There is a sum 10 option because dba_hist_active_sess_history has a sample size of 10 seconds

 Now Lets see to get this output from V$active_session_history, remember the sample size of V$ASH is 1 second.


SELECT plan_hash_value,
id line,
operation,
options,
object_name,
sum(1)
FROM V$active_session_history a,
dba_hist_sql_plan b
WHERE a.sql_plan_hash_value = b.plan_hash_value
AND a.sql_id = b.sql_id
AND a.sql_plan_line_id = b.id
AND a.sql_id = 'ah9xc371174p7'
AND a.session_id = 6769
AND a.session_serial# = 38229
GROUP BY plan_hash_value, id, operation, options, object_name
ORDER by 1,2;


 

 I have truncated the object names for obvious reasons in both. 

The key thing to notice here is that you have the time for each plan_line and the type of operation. 


Now the next thing is to find out within the operation / plan line what was the actual wait event i.e was it really I/O or CPU or direct path read etc. 

 

select ss.sql_id,
ss.sql_plan_line_id,
case SESSION_STATE when 'WAITING' then event
else SESSION_STATE end TIME_CATEGORY,
(SUM(10)) seconds,
round(RATIO_TO_REPORT(count(*)) OVER () * 100 ,2) pct_wait
from dba_hist_active_sess_history ss
WHERE ss.sql_id = 'ah9xc371174p7'
AND ss.session_id = 446
AND ss.session_serial# = 26701
group by ss.sql_id,ss.sql_plan_line_id,session_state,event
order by seconds;


SQL_ID        SQL_PLAN_LINE_ID TIME_CATEGORY                SECONDS   PCT_WAIT

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

ah9xc371174p7                5 db file sequential read           20        .22

ah9xc371174p7                5 db file parallel read             30        .33

ah9xc371174p7                1 ON CPU                            50        .55

ah9xc371174p7                2 ON CPU                            60        .65

ah9xc371174p7                5 ON CPU                            80        .87

ah9xc371174p7                6 ON CPU                          1130      12.32

ah9xc371174p7                6 db file parallel read           2760       30.1

ah9xc371174p7                6 db file sequential read         5040      54.96


 (In case output above is not formatted properly  due to font / browser limitations - see below output) 


 Now lets see this output from V$Active Session History. 


select ss.sql_id,
ss.sql_plan_line_id,
case SESSION_STATE when 'WAITING' then event
else SESSION_STATE end TIME_CATEGORY,
(SUM(1)) seconds,
round(RATIO_TO_REPORT(count(*)) OVER () * 100 ,2) pct_wait
from  V$active_session_history ss
WHERE ss.sql_id = 'ah9xc371174p7'
AND ss.session_id = 6769
AND ss.session_serial# = 38229
group by ss.sql_id,ss.sql_plan_line_id,session_state,event
order by seconds;  



 The key thing here is to find on what step the sql took more time and take an informed decision if we want to tune that step / access path or wait_event. 


You can apply filters in V$ASH / DB_HIST_ASH to find details for different plans as a summary without a specific session or set of sessions, totally up to you. 

Popular Posts

© Copyright 2019 DBA to DA. . Powered by Blogger.