DBA Centra

ACCEPT SID PROMPT 'Type Session ID: '

@px Is this session part of a set of parallel processes?
@reqch What are the childs of this request_id?

@cmrun What is running now

@cmpend What is  scheduled but not started yet
@reqch Detail runtimes of the child

@sesio/@ioses/@iosql Session I/O

DBA_HIST_ACTIVE_SESS_HISTORY
WAIT_TIME = Total wait time (in microseconds) for the event for which the session last waited (0 if currently waiting)

DBA_HIST_SYSTEM_EVENT
TIME_WAITED_MICRO = Total amount of time waited for the event (in microseconds)

DBA_HIST_SQLSTAT
EXECUTIONS_TOTAL =

GV$SQL
CPU_TIME  = CPU time (in microseconds) used by this cursor for parsing, executing, and fetching
ELAPSED_TIME  = Elapsed database time (in microseconds) used by this cursor for parsing, executing, and fetching. If the cursor uses parallel execution, then ELAPSED_TIME is the cumulative time for the query coordinator, plus all parallel query slave processes. Note that this column displays database time, not wall clock time. In some cases, ELAPSED_TIME can exceed the duration of the query. For example, this can occur for parallel queries.
LAST_ACTIVE_TIME = Time at which the query plan was last active  

PLSQL_EXEC_TIME = PL/SQL execution time (in microseconds)

GV$SESS_IO

TOAD
PARALLEL.sql (Parallel) -
select * from
, GV$PX_SESSION  pxsINST_ID SID SERIAL#
, GV$PX_PROCESS  pxp INST_ID SID SERIAL#
, GV$SESSION     ses INST_ID SID SERIAL#
, GV$SQLAREA     sqla INST_ID 

where
  pxs.INST_ID = pxp.INST_ID(+) and

  pxs.INST_ID = ses.INST_ID and

  pxs.INST_ID = sqla.INST_ID and

  pxs.SID = pxp.SID(+) and

  pxs.SID = ses.SID and

  pxs.SERIAL# = pxp.SERIAL#(+) and

  pxs.SERIAL# = ses.SERIAL# and

  ses.SQL_ID = sqla.SQL_ID and

  ses.SADDR = pxs.SADDR and

 

SPSS.sql (Session detail) - GV$SESSION, GV$PROCESS, GV$SQL, GV$SQLAREA, DBA_SQL_PROFILE (Session Detail)

TRACE.sql (Which programs or sessions are being traced) - GV$SESSSION, GV$PROCESS

PLAN.sql 

SELECT

  ses.SID||'_'||ses.SERIAL#||' ('||ses.INST_ID SID_SL_I

DBA_HIST_SNAPSHOT ss
ss.SNAP_ID = st.SNAP_ID and ss.DBID = st.SBID and ss.INSTANCE_NUMBER = st.INSTANCE_NUMBER and ss.CON_ID = st.CON_ID and ss.CON_ID > 2 and

SELECT DISTINCT DBID from DBA_HIST_SNAPSHOT where CON_ID > 2
SELECT DISTINCT DBID, CON_DBID from V$DATABASE;
--2915484435 3564376057
SELECT DISTINCT DBID from DBA_HIST_SNAPSHOT where DBID = (select DBID from V$DATABASE)
--2915484435
--3564376057

select MAX(SNAP_ID) from DBA_HIST_SNAPSHOT where CON_ID > 2 and BEGIN_INTERVAL_TIME between to_date('04/11/2024 03', 'MM/DD/YYYY HH24') and to_date('04/11/2024 05', 'MM/DD/YYYY HH24') order by SNAP_ID

V$PARAMETER
BUFFER_GETS = round(q.buffer_gets*(select value "DB_BLOCK_SIZE" from v$parameter where name = 'db_block_size')/1024/1024/1024) BUF_GB

OPTIMIZER_COST = CPU usage, memory usage, and disk I/O = The cost is an internal unit

CPU_TIME_TOTAL = in microseconds

ELAPSED_TIME_TOTAL = in microseconds

LAST_CALL_ET = in sec

Frequency Visualization

, rpad('|',NVL(count(*)/1000,1),'C')||rpad('|',NVL(round(sum(((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*24)))/20,1),'H') PROFILE

select dbms_lob.substr(sql_text,4000,1) from dba_hist_sqltext where SQL_ID = '&SQL_ID'

, count(DISTINCT h.SESSION_ID||'_'||h.SESSION_SERIAL#||' ('||h.INSTANCE_NUMBER||')') SESSION_COUNT

select
  to_char(actual_completion_date,'YYYY-MM-DD HH24') COMP_TIME
, sum((nvl(actual_completion_date,sysdate) - actual_start_date)*24) TOT_HR

, rpad('|',sum((nvl(actual_completion_date,sysdate) - actual_start_date)*24)/50,'<') TOT_HRG
, count(*) TOT_NO
, rpad('|',count(*)/200,'<') TOT_NOG

********************************************************************************

SQL ID: ccx8tsk0n4zmd Plan Hash: 771506455

INSERT INTO XXMSC_LEAD_TIME_SUPPLIES (PLAN_ID, ITEM_NAME, ORGANIZATION_CODE, 

  INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID, LEAD_TIME_DATE, 

...
  MFP.DEMAND_DATE AND TRUNC (NEW_SCHEDULE_DATE) < TRUNC 

  (MSX.NEW_SCHEDULE_DATE) AND FIRM_PLANNED_TYPE = 1))

Elapsed Time includes wait time on I/O but not the SQL Net message from Client
Wait time on CPU will NOT be reflected in Wait events
Parse and Execute are parts of CPU time

1.   Parse - The translation of the SQL into an execution plan.  This step includes syntax checks, permissions, and all object dependencies.

2.   Execute - The actual execution of the statement.

3.   Fetch - The number of rows returned for a SELECT statement.

Count - The number of times a statement was parsed, executed, or fetched.

CPU - The total CPU time in seconds for all parse, execute, or fetch calls.

Elapsed - Total elapsed time in seconds for all parse, execute, or fetch calls.

Disk - The number of physical disk reads from the datafiles for all parse, execute, or fetch calls.

Query - The number of buffers retrieved for all parse, execute, or fetch calls.

Current - The number of buffers retrieved in current mode (INSERT, UPDATE, or DELETE statements).

                                                             VVVVVVV
call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1   1522.40    1785.90     385279   29728654          1           0

Fetch        0      0.00       0.00          0          0          0           0 << 0 round trip for fetching 0 rows < Default array size the query used to exchange information between the server and the client

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

total        2   1522.40    1785.91     385279   29728654          1           0

                 Sec        Sec         number of physical reads (data blocks) Zero rows in one execution = 385279*8096/1024/1024/1024 GB = 2.9 GB

                                                 number of buffer reads (memory blocks) = 222 GB

                            
                                       


https://oracle-base.com/articles/8i/tkprof-and-oracle-trace
Inefficient statements are mostly associated with a high number of block visits.
The query column indicates block visits for read consistency, including all query and subquery processing.
The current column indicates visits not related to read consistency, including segment headers and blocks that are going to be updated. 
The number of blocks read from disk is shown in the disk column. Since disk reads are slower than memory reads you would expect this value to be significantly lower than the sum of the query and current columns. If it is not you may have an issue with your buffer cache.
Locking problems and inefficient PL/SQL loops may lead to high cpu/elapsed values even when block visits are low.
Multiple parse calls for a single statement imply a library cache issue.

Misses in library cache during parse: 1 <<< One hard parse

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 173     (recursive depth: 1)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         0          0          0  LOAD TABLE CONVENTIONAL  XXMSC_LEAD_TIME_SUPPLIES (cr=112628898 pr=386369 pw=0 time=2093190965 us starts=1)

         0          0          0   HASH UNIQUE (cr=112628898 pr=386369 pw=0 time=2093190959 us starts=1 cost=3218 size=202 card=1)

         0          0          0    HASH JOIN ANTI (cr=112628898 pr=386369 pw=0 time=2093190907 us starts=1 cost=3210 size=202 card=1)

       224        224        224     NESTED LOOPS SEMI (cr=112628837 pr=386308 pw=0 time=2395091475 us starts=1 cost=3193 size=184 card=1)

     41345      41345      41345      HASH JOIN  (cr=112617344 pr=385408 pw=0 time=2158268575 us starts=1 cost=3175 size=912 card=6)

   1630029    1630029    1630029       HASH JOIN  (cr=111165403 pr=220332 pw=0 time=1897455162 us starts=1 cost=3074 size=99066 card=869)

        55         55         55            TABLE ACCESS BY INDEX ROWID XXMSC_ORG_OPTIONS (cr=75 pr=0 pw=0 time=526 us starts=75 cost=1 size=26 card=1)

   2418768    2418768    2418768       PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=175845 pr=164973 pw=0 time=67537633 us starts=1 cost=97 size=54601478 card=1436881)

...
        96         96         96      PARTITION RANGE AND PARTITION: KEY(AP) KEY(AP) (cr=11493 pr=900 pw=0 time=1010754 us starts=12199 cost=3 size=32 card=1)

      1960       1960       1960     TABLE ACCESS FULL XXMSC_LEAD_TIME_SUPPLIES (cr=61 pr=61 pw=0 time=2765 us starts=1 cost=17 size=35298 card=1961)

Here are some descriptions of the parameters that are included in TKPROF output:

cr: The number of blocks read with logical reads in consistent mode.

pr: The number of blocks read with physical reads from the disk.

pw: The number of blocks written with physical writes to the disk.

time: The cumulative elapsed time for the step and the steps that preceded it in microseconds.

cost: The estimated cost of the operation used by the optimizer for internal comparison.

size: The estimated space usage of the operation in bytes.

card: The estimated cardinality (number of rows returned) of that particular operation.

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                    321992        0.01        121.34 Sec

  ASM IO for non-blocking poll                  525        0.00          0.00

  db file parallel read                         525        0.00          0.26

  db file scattered read                        716        0.00          0.37 Sec in Full Table Scan

********************************************************************************

ASM IO for non-blocking poll:

DBA_HIST_SNAPSHOT
DBA_HIST_SNAPSHOT ss where ss.CON_ID > 2 and ss.CON_ID = st.CON_ID and ss.SNAP_ID = st.SNAP_ID and 

SELECT DISTINCT DBID from DBA_HIST_SNAPSHOT where CON_ID > 2
SELECT DISTINCT DBID, CON_DBID from V$DATABASE;
--2915484435 3564376057
SELECT DISTINCT DBID from DBA_HIST_SNAPSHOT where DBID = (select DBID from V$DATABASE)
--2915484435
--3564376057

select MAX(SNAP_ID) from DBA_HIST_SNAPSHOT where CON_ID > 2 and BEGIN_INTERVAL_TIME between to_date('04/11/2024 03', 'MM/DD/YYYY HH24') and to_date('04/11/2024 05', 'MM/DD/YYYY HH24') order by SNAP_ID

V$PARAMETER
BUFFER_GETS = round(q.buffer_gets*(select value "DB_BLOCK_SIZE" from v$parameter where name = 'db_block_size')/1024/1024/1024) BUF_GB

OPTIMIZER_COST = CPU usage, memory usage, and disk I/O = The cost is an internal unit

CPU_TIME_TOTAL = in microseconds

ELAPSED_TIME_TOTAL = in microseconds

LAST_CALL_ET = in sec

Frequency Visualization

, rpad('|',NVL(count(*)/1000,1),'C')||rpad('|',NVL(round(sum(((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*24)))/20,1),'H') PROFILE

lsof
lsof -u root (filter files by owner)
lsof -i TCP:22 (filter files of processes listening to a certain port)

vmstat
vmstat -a (Memory, Swap, IO, System, CPU)

vmstat -s

paste -d " "  - - < INPUT.txt > OUTPUT.txt

Mail Trace Outputs as attachments

$ ls file1 > /dev/null

$

$ ls file12 > /dev/null

ls: cannot access file12: No such file or directory

$ ls file12 >/dev/null 2>&1

$

sar -A > /opt/dba/sg/SAR/sar_`/bin/date +%Y-%m-%d_%H%M`

Counter
counter=0

while true; do

  if /home/hadoop/latest/bin/hadoop fs -ls /apps/hdtech/bds/quality-rt/dt=$DATE_YEST_FORMAT2 then

       echo "Files Present" | mailx -s "File Present"  -r admin@host.com admin@host.com

       exit 0

  elif [[ "$counter" -gt 20 ]]; then

       echo "Counter: $counter times reached; Exiting loop!"

       exit 1

  else

       counter=$((counter+1))

       echo "Counter: $counter time(s); Sleeping for another half an hour" | mailx -s "Time to Sleep Now"  -r admin@host.com admin@host.com

       sleep 1800

  fi

done

File Based Counter
counter.txt
0
increment.sh
#!/bin/bash

#Increment the counter in counter.txt

counter=$(cat counter.txt)

counter=$((counter + 1))

echo $counter > counter.txt 

applmgr: env | grep VERSION

APPS_VERSION=12.2.9

applmgr: env | sort

ADMIN_SCRIPTS_HOME=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/admin/scripts

ADOP_LOG_HOME=/srv/ebs/amer/prod/base/fs_ne/EBSapps/log/adop

AD_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/ad/12.0.0

APPL_CONFIG_HOME=/srv/ebs/amer/prod/base/fs2/EBSapps/appl

APPLCSF_LOG_DIR=/srv/ebs/amer/prod/concurrent/log

APPLCSF=/srv/ebs/amer/prod/concurrent

APPLFENV=SBP609_rh53934.env

APPLLIB=lib

APPLLOG=log

APPLOUT=out

APPLTMP=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/appltmp

APPL_TOP_NE=/srv/ebs/amer/prod/base/fs_ne/EBSapps/appl

APPL_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl

APPS_VERSION=12.2.9

AP_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/ap/12.0.0

AR_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/ar/12.0.0

COMMON_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/comn

CONTEXT_FILE=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/appl/admin/SBP609_rh53934.xml

CONTEXT_NAME=SBP609_rh53934

EBS_APPS_DEPLOYMENT_DIR=/srv/ebs/amer/prod/base/fs2/FMW_Home/Oracle_EBS-app1/applications

EBS_DOMAIN_HOME=/srv/ebs/amer/prod/base/fs2/FMW_Home/user_projects/domains/EBS_domain

EBS_HOSTNAME=rh53934

EBS_ORACLE_HOME=/srv/ebs/amer/prod/base/fs2/FMW_Home/Oracle_EBS-app1

FILE_EDITION=run

FND_SECURE=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/appl/fnd/12.0.0/secure

FND_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/fnd/12.0.0

FORMS_TIMEOUT=5

FORMS_TRACE_DIR=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/logs/ora/10.1.2/forms

FORMS_WEB_CONFIG_FILE=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/ora/10.1.2/forms/server/appsweb.cfg

GWYUID=APPLSYSPUB/PUB

HOME=/srv/users/dba/applmgr

HOSTNAME=rh53934.starbucks.net

IAS_ORACLE_HOME=/srv/ebs/amer/prod/base/fs2/FMW_Home/webtier

JA_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/ja/12.0.0

JAVA_BASE=/srv/ebs/amer/prod/base/fs2/EBSapps/comn/java

JAVA_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/comn/java/classes

JE_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/je/12.0.0

LANG=en_US.UTF-8

LNS_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/lns/12.0.0

LOG_HOME=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/logs

LOGNAME=applmgr

MWA_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/mwa/12.0.0

NE_BASE=/srv/ebs/amer/prod/base/fs_ne

NLS_DATE_FORMAT=DD-MON-RR

NLS_DATE_LANGUAGE=

NLS_LANG=American_America.AL32UTF8

NLS_NUMERIC_CHARACTERS=.,

NLS_SORT=BINARY

OA_JAVA=/srv/ebs/amer/prod/base/fs2/EBSapps/comn/java/classes

OA_JRE_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/comn/util/jdk32/jre

OPATCH_PLATFORM_ID=46

ORACLE_CONFIG_HOME=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/ora/10.1.2

ORACLE_HOME=/srv/ebs/amer/prod/base/fs2/EBSapps/10.1.2

ORACLE_LOCALPREFERENCE=/srv/ebs/amer/prod/base/fs2/EBSapps/10.1.2/tools/admin

ORACLE_SID=SBP609

ORACLE_TERM=vt220

ORA_CONFIG_HOME=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/ora

ORAENV_ASK=NO

ORA_NLS10=/srv/ebs/amer/prod/base/fs2/EBSapps/10.1.2/nls/data/9idata

OTA_TOP=/srv/ebs/amer/prod/base/fs2/EBSapps/appl/ota/12.0.0

PATCH_BASE=/srv/ebs/amer/prod/base/fs1

PATCH_TOP=/srv/ebs/amer/prod/base/fs_ne/EBSapps/patch

PCC=$(ORACLE_HOME)/bin/proc

PLATFORM=LINUX

RUN_BASE=/srv/ebs/amer/prod/base/fs2

RW=/srv/ebs/amer/prod/base/fs2/EBSapps/10.1.2/reports

SHELL=/bin/ksh

TNS_ADMIN=/srv/ebs/amer/prod/base/fs2/inst/apps/SBP609_rh53934/ora/10.1.2/network/admin

TWO_TASK=SBP609_SBUX_FORMS

https://taliphakanozturken.wordpress.com/2012/05/05/oracles-oswatcher-utility

GV$ASM_ALIAS

GV$ASM_ATTRIBUTE

GV$ASM_CLIENT            GR# DB_NAME SW_VER COMPATIBILITY_VER

GV$ASM_DISKGROUP

GV$ASM_DISK

GV$ASM_DISK_IOSTAT

GV$ASM_FILE

GV$ASM_FILEGROUP

GV$ASM_FILESYSTEM

GV$ASM_USER

GV$ASM_VOLUME

History                 Current
DBA_HIST_ACTIVE_SESS_HISTORY (10 sec interval) V$ACTIVE_SESSION_HISTORY (1 sec interval; preserves for 60 min)
DBA_ALERT_HISTORY             V$DIAG_ALERT_TEXT
DBA_HIST_ASH_SNAPSHOT         NA
DBA_HIST_EVENT_NAME      V$EVENT_NAME
DBA_HIST_OSSTAT               V$OSSTAT
DBA_HIST_PARAMETER       V$PARAMETER
DBA_HIST_SESSTAT              V$SESSTAT
DBA_HIST_SGA             V$SGA
DBA_HIST_SGASTAT              V$SGASTAT
DBA_HIST_SNAPSHOT             NA
DBA_HIST_SQLSTAT              GV$SQL
DBA_HIST_SYSSTAT              V$SYSSTAT
DBA_HIST_SGASTAT              V$SGASTAT
DBA_HIST_SQL_PLAN             V$SQL_PLAN
DBA_HIST_SQLTEXT              V$SQLTEXT
DBA_HIST_TABLESPACE      V$TABLESPACE
DBA_HIST_TBSPC_SPACE_USAGE
DBA_HIST_TEMPFILE             V$TEMPFILE
DBA_HIST_UNDOSTAT             V$UNDOSTAT
DBA_HIST_WAITSTAT             V$WAITSTAT

DBA_HIST_SNAPSHOT ss
ss.SNAP_ID = st.SNAP_ID and ss.DBID = st.SBID and ss.INSTANCE_NUMBER = st.INSTANCE_NUMBER and ss.CON_ID = st.CON_ID and ss.CON_ID > 2 and

Here we will find if there is any controlling session (QCSID) for the session under investigation.

select
  QCSID||'_'||QCSERIAL#||' ('||QCINST_ID||')' QCSID_SL_I
, DECODE(SID,&SID,'    '||&SID,SID)||'_'||SERIAL#||' ('||INST_ID||')' SID_SL_I
, '<<<<<>>>>>'
, px.*
from
  GV$PX_SESSION px
where
  QCSID = (select QCSID from GV$PX_SESSION where SID = &SID) order by QCSID_SL_I, SID_SL_I
/

Here we will find if there is any controlling session (QCSID) for the session under investigation.

select
  QCSID||'_'||QCSERIAL#||' ('||QCINST_ID||')' QCSID_SL_I
, DECODE(SID,&SID,'    '||&SID,SID)||'_'||SERIAL#||' ('||INST_ID||')' SID_SL_I
, '<<<<<>>>>>'
, px.*
from
  GV$PX_SESSION px
where
  QCSID = (select QCSID from GV$PX_SESSION where SID = &SID) order by QCSID_SL_I, SID_SL_I
/

ACCEPT PROGRAM_SHORT_NAME PROMPT ' Type CONC_PROG_SHORT_NAME : '

ACCEPT PROGRAM_USER_NAME PROMPT ' Type CONC_PROG_USER_NAME : '

--set define on

COLUMN filename NEW_VALUE tfn

SELECT 'inc_'||(select instance_name from v$instance)||'_'|| TO_CHAR(SYSDATE,'YYMMDD_HH24MI')||'.txt' filename FROM DUAL;

spool .\Benchmark\INCOMPATIBLE\&tfn

--set define off

http://kerryosborne.oracle-guy.com/2009/07/28/how-to-attach-a-sql-profile-to-a-different-statement/
How to Attach a SQL Profile to a Different Statement