DBA Centra
FAQ
Oracle DBA - Google Slides
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
QUERIES
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
TKPROF
********************************************************************************
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:
Snapshot, Buffer, Time
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
Buffer
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
Frequency Visualization
, rpad('|',NVL(count(*)/1000,1),'C')||rpad('|',NVL(round(sum(((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*24)))/20,1),'H') PROFILE
Version & ENV
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
ASM Alias
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
Check whether a running SID is part of parallel processing
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
/
GetKey
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
/