Oracle Database - Scripts - SID to PID and PID to SID

Oracle Database - Scripts - SID to PID and PID to SID

scripts Vineet Sachdeva   January 17, 2019

In this blog I am going to cover a simple script which converts the SID  you mentioned to OS PID . 

SID to PID
set lines 500 pages 500
column sid format 99999
column spid format 99999
column program format a30
column osuser format a30

select a.sid,b.spid,a.username,a.osuser
from v$session a,
v$process b
where a.paddr = b.addr
and a.sid = &SID
order by b.spid;

When Prompted Enter SID

  1  select a.sid,b.spid,a.username,a.osuser
  2  from v$session a,
  3  v$process b
  4  where a.paddr = b.addr
  5  and a.sid = &SID
  6* order by b.spid
Enter value for sid: 31
old   5: and a.sid = &SID
new   5: and a.sid = 31

   SID SPID                     USERNAME                                           OSUSER
------ ------------------------ -------------------------------------------------- ------------------------------

    31 9936                     AS                                                 iisuser

PID to SID

set lines 500 pages 500
column sid format 99999
column spid format 99999
column program format a30
column osuser format a30

select a.sid,b.spid,a.username,a.osuser
from v$session a,
v$process b
where a.paddr = b.addr
and b.spid = &PID
order by b.spid;

When Prompted Enter PID

  1  select a.sid,b.spid,a.username,a.osuser
  2  from v$session a,
  3  v$process b
  4  where a.paddr = b.addr
  5  and b.spid = &PID
  6* order by b.spid
Enter value for pid: 9936
old   5: and b.spid = &PID
new   5: and b.spid = 9936

   SID SPID                     USERNAME                                           OSUSER
------ ------------------------ -------------------------------------------------- ------------------------------

    31 9936                     AS                                                 iisuser

To List all SIDs with their PIDs
set lines 500 pages 500
column sid format 99999
column spid format 99999
column program format a30
column osuser format a30

select a.sid,b.spid,a.username,a.osuser
from v$session a,
v$process b
where a.paddr = b.addr

order by b.spid

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

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

scripts Vineet Sachdeva   January 15, 2019

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.

Oracle Enterprise Manager - 13c Reporting from Management Views

Oracle Enterprise Manager - 13c Reporting from Management Views

scripts Vineet Sachdeva   January 07, 2019

In this blog I discuss on how to extract data from OEM management views. 

I am discussing a task of extracting SGA / PGA monitoring data from OEM views

There 3 key views to keep eye on 
1. MGMT$METRIC_CURRENT - Keeps record of current values
2. MGMT$METRIC_HOURLY - Hourly records
3. MGMT$METRIC_DAILY - Aggregated Daily Records

You can get  more information on views from below - https://docs.oracle.com/cd/E63000_01/EMVWS/monview.htm#EMVWS12357

So to extract data from OEM view I use the below query to get my current SGA / PGA data

col DB_NAME  for a15
col SGA_Size_MB for a18
col value for a15
col DATA_COLLECTED for a30
break on DB_NAME ON DATA_COLLECTED skip 1

SELECT TARGET_NAME AS DB_NAME, COLUMN_LABEL AS METRIC_NAME, 
VALUE, to_char(COLLECTION_TIMESTAMP, 'dd-mm-yyyy hh24:mi') as DATA_COLLECTED
FROM MGMT$METRIC_CURRENT
WHERE 
TARGET_TYPE = 'oracle_database' AND
METRIC_NAME = 'memory_usage_sga_pga' AND
METRIC_LABEL = 'SGA and PGA usage' AND 
COLUMN_LABEL in  ('SGA Size(MB)' , 'PGA Total(MB)')
ORDER BY 
DB_NAME, METRIC_NAME;

Oracle Database - Backup Status (Check) Query

Oracle Database - Backup Status (Check) Query

scripts Vineet Sachdeva   September 10, 2018

This query is to check on Database backups history from v$ views. 
It provides below

1. BACKUP NAME 
2. STATUS 
3. START TIME                
4. TIME TAKEN TYPE            
5. OUTPUT DEVICE
6. INPUT SIZE   
7. OUTPUT SIZE 

SET ECHO ON
set linesize 222
column "START TIME" format a40
column "END TIME" format a40
col "BACKUP NAME" for a20
col STATUS for a21
col "START TIME" for a25
col "END TIME" for a25
col "TIME TAKEN" for a10
col "TYPE" for a15
col "OUTPUT DEVICES" for a10
col "INPUT SIZE" for a12
col "OUTPUT SIZE" for a12
col "OUTPUT BYTES PER SECOND" for a10

select command_id "BACKUP NAME",
STATUS,
to_char(start_time,'Mon DD,YYYY HH24:MI:SS') "START TIME",
time_taken_display "TIME TAKEN",
input_type "TYPE",
output_device_type "OUTPUT DEVICES",
input_bytes_display "INPUT SIZE",
output_bytes_display "OUTPUT SIZE",
output_bytes_per_sec_display "OUTPUT BYTES PER SECOND"
FROM V$RMAN_BACKUP_JOB_DETAILS where trunc(start_time) between trunc(sysdate-4) and  trunc(sysdate) ORDER BY END_TIME DESC;

Query Output

SQL> r
1 select command_id "BACKUP NAME",
2 STATUS,
3 to_char(start_time,'Mon DD,YYYY HH24:MI:SS') "START TIME",
4 time_taken_display "TIME TAKEN",
5 input_type "TYPE",
6 output_device_type "OUTPUT DEVICES",
7 input_bytes_display "INPUT SIZE",
8 output_bytes_display "OUTPUT SIZE",
9 output_bytes_per_sec_display "OUTPUT BYTES PER SECOND"
10* FROM V$RMAN_BACKUP_JOB_DETAILS where trunc(start_time) between trunc(sysdate-4) and trunc(sysdate) ORDER BY END_TIME DESC

BACKUP NAME STATUS START TIME TIME TAKEN TYPE OUTPUT DEV INPUT SIZE OUTPUT SIZE OUTPUT BYT
-------------------- --------------------- ------------------------- ---------- --------------- ---------- ------------ ------------ ----------
ARCH_BKP COMPLETED Sept. 10,2018 12:00:03 00:12:40 ARCHIVELOG DISK 27.65G 7.83G 10.55M
ARCH_BKP FAILED Sept. 10,2018 08:00:03 00:01:39 ARCHIVELOG DISK 3.72G 997.12M 10.07M
ARCH_BKP FAILED Sept. 10,2018 04:00:02 00:01:39 ARCHIVELOG DISK 3.72G 997.12M 10.07M
ARCH_BKP FAILED Sept. 10,2018 00:00:02 00:01:49 ARCHIVELOG DISK 3.72G 997.12M 9.15M
DB_INCREMENTAL_L0 FAILED Sept. 09,2018 01:00:02 04:37:20 DB INCR DISK 971.06G 140.76G 8.66M
ARCH_BKP FAILED Sept. 08,2018 20:00:02 00:00:40 ARCHIVELOG DISK 1015.20M 301.32M 7.53M
ARCH_BKP FAILED Sept. 08,2018 16:00:03 00:02:01 ARCHIVELOG DISK 3.82G 1.19G 10.07M
ARCH_BKP FAILED Sept. 08,2018 12:00:02 00:04:33 ARCHIVELOG DISK 10.04G 2.98G 11.19M
ARCH_BKP FAILED Sept. 08,2018 08:00:02 00:03:43 ARCHIVELOG DISK 7.98G 2.36G 10.85M
ARCH_BKP FAILED Sept. 08,2018 04:00:02 00:02:10 ARCHIVELOG DISK 4.71G 1.29G 10.18M
ARCH_BKP FAILED Sept. 08,2018 00:00:03 00:01:01 ARCHIVELOG DISK 1.42G 440.86M 7.23M
ARCH_BKP FAILED Sept. 07,2018 20:00:03 00:01:30 ARCHIVELOG DISK 3.04G 969.58M 10.77M
ARCH_BKP FAILED Sept. 07,2018 16:00:03 00:03:03 ARCHIVELOG DISK 6.25G 1.95G 10.94M
ARCH_BKP FAILED Sept. 07,2018 12:00:02 00:02:21 ARCHIVELOG DISK 5.11G 1.43G 10.38M
ARCH_BKP FAILED Sept. 07,2018 08:00:03 00:04:03 ARCHIVELOG DISK 8.85G 2.59G 10.93M
ARCH_BKP COMPLETED Sept. 07,2018 04:00:02 00:02:08 ARCHIVELOG DISK 4.73G 1.29G 10.36M
ARCH_BKP COMPLETED Sept. 07,2018 00:00:02 00:01:03 ARCHIVELOG DISK 1.82G 618.67M 9.82M
ARCH_BKP COMPLETED Sept. 06,2018 20:00:02 00:01:33 ARCHIVELOG DISK 2.74G 927.82M 9.98M
ARCH_BKP COMPLETED Sept. 06,2018 16:00:02 00:02:17 ARCHIVELOG DISK 4.32G 1.39G 10.38M
ARCH_BKP COMPLETED Sept. 06,2018 12:00:02 00:11:19 ARCHIVELOG DISK 24.07G 7.07G 10.66M

20 rows selected.

Oracle Database - Log Switches Per hour

Oracle Database - Log Switches Per hour

scripts Anonymous   July 28, 2017

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;

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

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

scripts Anonymous   May 29, 2017

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.