Oracle DB Monitoring and Performance Tuning SQL Scripts Data Guard, ASM, DATAPUMP ...
Find CPU and memory usage of oracle DB server
Below script is useful
in getting CPU, memory and core, socket
information of a database server
from SQL prompt.
SCRIPT:
set pagesize 299
set lines 299
select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS
from v$osstat where
stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB' ,COMMENTS from v$osstat where
stat_name IN ('PHYSICAL_MEMORY_BYTES')
OUTPUT:
STAT_NAME VALUE
COMMENTS NUM_CPUS 256
Number
of active CPUs
NUM_CPU_CORES 32
Number
of CPU cores NUM_CPU_SOCKETS 4
Number
of physical CPU sockets PHYSICAL_MEMORY_BYTES 255.5 GB
Physical memory size in bytes
Find sessions that are consuming
lot of CPU
Use below query to find
the sessions using a
lot of CPU.
col program
form a30 heading
"Program"
col CPUMins
form 99990 heading
"CPU in Mins"
select rownum as rank,
a.* from (
SELECT v.sid, program,
v.value / (100 * 60) CPUMins FROM
v$statname s , v$sesstat v, v$session sess WHERE s.name
= 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic# and v.value>0
ORDER BY v.value DESC) a where rownum < 11;
Find CPU usage and wait event information in oracle database
set lines 288
col sample_time for a14
col CONFIGURATION head "CONFIG" for 99.99 col ADMINISTRATIVE head "ADMIN" for 99.99
col OTHER for 99.99
SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME, ROUND(OTHER / 60, 3) AS OTHER,
ROUND(CLUST / 60, 3) AS CLUST,
ROUND(QUEUEING / 60, 3) AS QUEUEING,
ROUND(NETWORK / 60, 3) AS NETWORK,
ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
ROUND(COMMIT / 60, 3) AS COMMIT,
ROUND(APPLICATION / 60, 3) AS APPLICATION,
ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
ROUND(SIO / 60, 3) AS SYSTEM_IO,
ROUND(UIO / 60, 3) AS USER_IO,
ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
ROUND(CPU / 60, 3) AS CPU,
ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME, DECODE(SESSION_STATE,
'ON CPU',
DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'), WAIT_CLASS) AS WAIT_CLASS
FROM V$ACTIVE_SESSION_HISTORY
WHERE SAMPLE_TIME > SYSDATE - INTERVAL '2' HOUR
AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*) FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER, 'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY, 'Application' AS APPLICATION, 'Commit' AS COMMIT,
'Configuration' AS CONFIGURATION, 'Administrative' AS ADMINISTRATIVE, 'Network' AS NETWORK,
'Queueing' AS QUEUEING, 'Cluster' AS CLUST, 'Other' AS OTHER))
OUTPUT WILL LOOK AS BELOW:
Monitor parallel queries
in oracle db
Use below query to monitor currently running queries with parallel threads.
col username for a9 col sid for a8
set lines 299 select
s.inst_id, decode(px.qcinst_id,NULL,s.username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) )
"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", px.req_degree "Requested DOP",
px.degree "Actual DOP", p.spid from
gv$px_session px, gv$session s, gv$process p
where
px.sid=s.sid (+) and px.serial#=s.serial# and px.inst_id = s.inst_id
and p.inst_id = s.inst_id and p.addr=s.paddr
order by 5 , 1 desc
/
Find user commits
per minute in oracle DB
Below script is useful in getting
user commit statistics information in the oracle database. user commits is the number of commits
happening the database. It will be helpful in tracking the number of transactions in the database.
STAT_PER_MIN -Number of commits per minutes,
during that snap time
col STAT_NAME for a20
col VALUE_DIFF for
9999,999,999 col STAT_PER_MIN for 9999,999,999
set lines 200 pages 1500
long 99999999 col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for
a30 set pagesize 40
set pause on
select hsys.SNAP_ID,
hsnap.BEGIN_INTERVAL_TIME, hsnap.END_INTERVAL_TIME,
hsys.STAT_NAME, hsys.VALUE,
hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS "VALUE_DIFF",
round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY
hsys.SNAP_ID)) /
round(abs(extract(hour from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))*60 +
extract(minute from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME)) +
extract(second from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))/60),1)) "STAT_PER_MIN"
from dba_hist_sysstat hsys, dba_hist_snapshot hsnap
where hsys.snap_id = hsnap.snap_id
and hsnap.instance_number in (select instance_number from v$instance) and hsnap.instance_number = hsys.instance_number
and hsys.STAT_NAME='user
commits' order by 1;
OUTPUT:
Find active transactions in oracle database
Below script can be used to find
the active transactions in the oracle
database.
col name format a10 col username format a8 col osuser format
a8
col start_time format a17 col status format
a12
tti 'Active
transactions'
select s.sid,username,t.start_time, r.name,
t.used_ublk "USED BLKS", decode(t.space, 'YES',
'SPACE TX',
decode(t.recursive,
'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s where t.xidusn = r.usn
and t.ses_addr = s.saddr
/
Find distributed pending transactions in oracle
DB
Below script will display
information about the distributed pending
transactions in oracle.
COL local_tran_id FORMAT
a13 COL in_out FORMAT a6
COL database FORMAT
a25
COL dbuser_owner FORMAT
a15 COL interface FORMAT a3
SELECT local_tran_id, in_out, database, dbuser_owner, interface FROM dba_2pc_neighbors
/
Active/ Standby Data Guard useful SQL scripts
1. Basic information of database
(primary or standby)
SQL> SELECT
DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; DATABASE_ROLE INSTANCE OPEN_MODE
PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
–––––––– ––––––––––––––– ––––––––––
PHYSICAL STANDBY stdby READ ONLY MAXIMUM
PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
2. Check for messages/errors
SQL> SELECT
MESSAGE FROM V$DATAGUARD_STATUS; MESSAGE
––––––––––––––––––––––––––––––––––––––––
ARC0:
Archival started ARC1: Archival
started ARC2: Archival
started
ARC2: Becoming
the ‘no FAL’ ARCH
ARC1: Becoming the heartbeat ARCH
ARC1:
Becoming the active
heartbeat ARCH ARC3:
Archival started
3. To display current status information for specific physical
standby database background processes.
SQL> SELECT PROCESS, STATUS, THREAD#,
SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD#
SEQUENCE# BLOCK# BLOCKS
––––- ––––––
––––– ––––– –––––
–––––
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 54 45056 755
ARCH CLOSING 1 57 1 373
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 58 30239
1
8 rows selected.
4. Show received
archived logs on physical standby
Run this query on
physical standby
SQL> select registrar, creator, thread#,
sequence#, first_change#, next_change# from v$archived_log;
REGISTR CREATOR THREAD#
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
–––-
–––- ––––– –––––
––––––- ––––––
|
RFS |
ARCH |
1 |
29 |
1630326 |
1631783 |
|
RFS |
ARCH |
1 |
30 |
1631783 |
1632626 |
|
RFS |
LGWR |
1 |
31 |
1632626 |
1669359 |
|
RFS |
ARCH |
1 |
33 |
1676050 |
1676124 |
|
RFS |
ARCH |
1 |
32 |
1669359 |
1676050 |
|
RFS |
ARCH |
1 |
35 |
1681145 |
1681617 |
|
RFS |
ARCH |
1 |
34 |
1676124 |
1681145 |
|
RFS |
ARCH |
1 |
37 |
1688494 |
1688503 |
|
RFS |
ARCH |
1 |
36 |
1681617 |
1688494 |
|
RFS |
ARCH |
1 |
38 |
1688503 |
1689533 |
|
RFS |
LGWR |
1 |
39 |
1689533 |
1697243 |
5. To check the log status
SQL> select ‘Last
Log applied : ‘ Logs,
to_char(next_time,‘DD-MON-
YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where
applied=’YES’)
union
select ‘Last Log received
: ‘ Logs, to_char(next_time,‘DD-MON-YY:HH24:MI:SS’)
Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log); LOGS
TIME
–––––––––– –––––––––––––-
Last Log applied : 24-MAR-14:10:11:10 Last Log received
: 27-MAR-14:12:40:17
6. To display various
information about the redo data. This includes redo data generated by the primary database
that is not yet available on the standby
database and how much redo has not yet been applied to the standby
database.
set lines 132
col value format a20
SQL> select
name, value from V$DATAGUARD_STATS; NAME VALUE
––––––––––––––– ––––––––––
transport lag +00 00:00:00 apply
lag
apply finish time estimated
startup time 23
7. to monitor efficient recovery
operations as well as to estimate the time
required to complete the current
operation in progress:
SQL> select to_char(start_time, ‘DD-MON-RR HH24:MI:SS’) start_time, item,
round(sofar/1024,2) “MB/Sec”
from v$recovery_progress
where (item=‘Active Apply Rate’ or item=‘Average Apply Rate’);
START_TIME ITEM MB/SEC
–––––- –––––––––––––––- ––––
27-MAR-14 15:49:44 Active
Apply Rate 8.5
27-MAR-14 15:49:44 Average
Apply Rate 6.30
8. To find last applied log
SQL> select to_char(max(FIRST_TIME),‘hh24:mi:ss dd/mm/yyyy’) FROM V$ARCHIVED_LOG where applied=‘YES’;
TO_CHAR(MAX(FIRST_T
–––––––––-
10:11:08 24/03/2014
9. To see if standby
redo logs have been
created. The standby redo logs should be the same size
as the online redo logs. There should be (( # of online logs per thread + 1) *
# of threads) standby redo logs. A
value of 0 for the thread# means the log has never been allocated.
SQL> SELECT thread#, group#, sequence#, bytes, archived, status
FROM v$standby_log order
by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
––––– –––––
––––– ––––– –- –––––––––-
1 8 0 104857600 NO UNASSIGNED 1 9 58 104857600 YES ACTIVE
1 10 0 104857600 NO UNASSIGNED
1 11 0 104857600 YES UNASSIGNED
10. To produce a list of defined
archive destinations. It shows if they are enabled, what process is servicing that destination, if the destination is
local or remote, and if remote what
the current mount ID is. For a physical standby we should have at least one
remote destination that points the primary set.
column destination format a35 wrap
column process format
a7
column ID format 99 column
mid format 99
SQL> SELECT
thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM
gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id
= gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE
PROCESS MID
–––– –––- –––––––––––––––––––––––––––––––––––––––––
1 1 USE_DB_RECOVERY_FILE_DEST VALID
LOCAL ACTIVE ARCH 0
1 2 brij VALID REMOTE PENDING LGWR 0
1 32 USE_DB_RECOVERY_FILE_DEST VALID
LOCAL ACTIVE RFS 0
11. Verify the last sequence# received and the last sequence#
applied to standby database.
SQL>
SELECT al.thrd “Thread”, almax “Last Seq Received”,
lhmax “Last Seq Applied” FROM (select thread#
thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM
v$database) GROUP BY thread#) al,
(SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT
resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;
Thread Last Seq Received Last Seq Applied
––––– ––––––––- ––––––––
1 57 53
Oracle EXPDP/IMPDP (DATAPUMP) Monitoring
Scripts
Usually we monitor the EXPDP/IMPDP
jobs by monitoring the log files generated by
expdp/impdp process. Also we
monitor alert log too just in case some error pops up. This helps most of the time. If you have a long
running expdp/impdp sessions as you are exporting/importing
huge GBs then it helps to have a more detailed monitoring of the expdp/impdp jobs. Some of the useful
queries which can be used to monitor the Data
Pump Jobs are mentioned
below.
To start with some of the important tables/views that you should refer to monitor
Data Pump Jobs are:
DBA_DATAPUMP_JOBS DBA_DATAPUMP_SESSIONS DBA_RESUMABLE V$SESSION_LONGOPS V$SESSION V$DATAPUMP_JOB
1.
Script to find status of work done
select x.job_name,ddj.state,ddj.job_mode,ddj.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs ddj
left join
dba_datapump_sessions x on (x.job_name = ddj.job_name) left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id) WHERE
y.module='Data Pump Worker'
AND p.time_remaining > 0;
2.
Another simple script
using only longops
view
select
round(sofar/totalwork*100,2) percent_completed, v$session_longops.*
from v$session_longops
where
sofar <> totalwork order by
target, sid;
3.
Procedure to find the status
of job in terms of percentage & number of rows
SET SERVEROUTPUT ON DECLARE
ind NUMBER; h1 NUMBER;
percent_done NUMBER; job_state VARCHAR2(30); js ku$_JobStatus;
ws ku$_WorkerStatusList; sts ku$_Status;
BEGIN
h1 := DBMS_DATAPUMP.attach('&JOB_NAME', '&JOB_OWNER');
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error
+ dbms_datapump.ku$_status_job_status
+ dbms_datapump.ku$_status_wip, 0, job_state, sts);
js := sts.job_status;
ws := js.worker_status_list;
dbms_output.put_line('** Job percent done = ' ||
to_char(js.percent_done)); dbms_output.put_line('restarts - '||js.restart_count);
ind := ws.first;
while ind is not null loop
dbms_output.put_line('rows
completed - '||ws(ind).completed_rows); ind := ws.next(ind);
end loop; DBMS_DATAPUMP.detach(h1); end;
/
This package will need JOB_NAME and JOB_OWNER as input parameter. You can fetch this information from your export/import log
or you can use the previous SQL script to get this information.
Remember that if you are doing
expdp/impdp by SYSDBA
then execute this package using the same
SYSDBA privilege.
Oracle Scripts
for Improving Database
Performance
1. This script invokes the
tracker utility to capture the UTLESTAT information into permanent tables and then drop the temporary tables.
Remember This SQL script will
load the tracking tables.
insert into track_stats
( oracle_sid,
collection_started) select '&1',min(stats_gather_times) from
sys.stats$dates;
update track_stats set collection_ended =
(select max(stats_gather_times) from
sys.stats$dates),
run_date = to_date(substr(collection_started,1,12),'DD-MON-YY
HH24'), consistent_gets =
(select change
from sys.stats$stats
where name = 'consistent gets'), block_gets =
(select change
from sys.stats$stats
where name = 'db block gets'),
physical_reads =
(select change
from sys.stats$stats
where name = 'physical reads'),
buffer_busy_waits =
(select change
from sys.stats$stats
where name = 'buffer busy waits'), buffer_free_needed =
(select change
from sys.stats$stats
where name = 'free buffer requested'), free_buffer_waits =
(select change
from sys.stats$stats
where name = 'free buffer waits'), free_buffer_scans =
(select change
from sys.stats$stats
where name = 'free buffer scans'), enqueue_timeouts =
(select change
from sys.stats$stats
where name = 'enqueue timeouts'), redo_space_wait =
(select change
from sys.stats$stats
where name = 'redo log space wait time'), write_wait_time =
(select change
from sys.stats$stats
where name = 'write wait time'), write_complete_waits =
(select change
from sys.stats$stats
where name = 'write complete waits'), rollback_header_gets =
(select sum(trans_tbl_gets) from sys.stats$roll),
rollback_header_waits =
(select sum(trans_tbl_waits) from
sys.stats$roll)
where collection_ended is null;
insert into latches
(ls_latch_name, ls_latch_gets, ls_latch_misses, ls_latch_sleeps, ls_latch_immed_gets,
ls_latch_immed_misses)
select name, gets, misses,
sleeps, immed_gets, immed_miss from sys.stats$latches;
update latches set
ls_collection_started =
(select min(stats_gather_times) from
sys.stats$dates)
where ls_oracle_sid is null;
update latches set
run_date = to_date(substr(ls_collection_started, 1,12),'DD-MON-YY HH24')
where ls_oracle_sid is null;
update latches
set ls_oracle_sid =
(select '&1' from sys.dual),
ls_collection_ended =
(select max(stats_gather_times) from
sys.stats$dates)
where ls_oracle_sid is null;
2. This routine interrogates
all tablespaces and dumps the information into a statistical table.
insert into tablespace_stat values
(
select
dfs.tablespace_name, round(sum(dfs.bytes)/1048576,2), round(max(dfs.bytes)/1048576,2)
from sys.dba_free_space dfs group by dfs.tablespace_name order by dfs.tablespace_name);
3. Attach this script to a cron
process to gather table-extent information at a specified time interval.
insert into tab_stat values(
select ds.tablespace_name, dt.owner,
dt.table_name, ds.bytes/1024, ds.extents, dt.max_extents, dt.initial_extent/1024, dt.next_extent/1024, dt.pct_increase, dt.pct_free, dt.pct_used
from
sys.dba_segments ds, sys.dba_tables dt
where ds.tablespace_name = dt.tablespace_name and ds.owner = dt.owner
and
ds.segment_name = dt.table_name order by 1,2,3);
4. This table-extents-report
script joins the extents table against itself
to show growth
in extents.
break
on c0 skip 2 on c1
skip 1
ttitle "
Table Report| > 50
Extents or new extents"; spool
/tmp/rpt10
select distinct
b.sid c0,
substr(b.owner,1,6)
c1, substr(b.tablespace_name,1,10) c2, substr(b.table_name,1,20)
c3, (b.blocks_alloc*2048)/1024 c4, c.next_extent/1024 c5,
a.extents
c6, b.extents c7 from tab_stat a, tab_stat
b, dba_tables c where
rtrim(c.table_name) = rtrim(b.table_name)
and
a.sid = b.sid and
rtrim(a.tablespace_name)
<> 'SYSTEM' and
a.tablespace_name = b.tablespace_name and
a.table_name = b.table_name and
to_char(a.run_date) = to_char(b.run_date-7)
-- compare
to one week prior and
(
a.extents < b.extents
-- where
extents has increased
or
b.extents > 50
)
order by b.sid;
5. Use this script to get a fast overview
of the state of a troubled system.
spool
/tmp/snap;
prompt**************************************************** prompt Hit Ratio Section prompt**************************************************** prompt
prompt ========================= prompt
BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
--(sum(decode(name,'db block gets',value,0))+
--(sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
column "logical_reads" format 99,999,999,999 column "phys_reads" format
999,999,999 column
"phy_writes" format 999,999,999 select
a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))
"BUFFER HIT RATIO"
from
v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d where
a.statistic# = 37 and
b.statistic#
= 38 and
c.statistic# = 39 and
d.statistic# = 40;
prompt prompt
prompt ========================= prompt
DATA DICT HIT RATIO
prompt =========================
prompt (should
be higher than 90 else increase shared_pool_size in init.ora) prompt
column
"Data Dict. Gets" format
999,999,999 column "Data Dict.
cache misses" format 999,999,999 select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"
from v$rowcache; prompt
prompt ========================= prompt LIBRARY CACHE MISS RATIO prompt =========================
prompt (If >
.1, i.e., more than 1% of the pins
prompt resulted in reloads, then increase the shared_pool_size in init.ora) column
"LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format
999,999,999
column "Cache misses while executing" format
999,999,999
select sum(pins)
"executions", sum(reloads) "Cache misses while executing", (((sum(reloads)/sum(pins))))
"LIBRARY CACHE MISS
RATIO"
from v$librarycache;
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should
be > 70, and pin ratio > 70 ... prompt
column "reloads" format 999,999,999
select
namespace, trunc(gethitratio * 100) "Hit ratio", trunc(pinhitratio * 100) "pin hit
ratio", reloads "reloads" from v$librarycache;
prompt prompt
prompt =========================
prompt REDO LOG BUFFER
prompt =========================
prompt
set heading off
column
value format 999,999,999 select substr(name,1,30),
value
from v$sysstat where name = 'redo
log space requests';
set heading
on prompt prompt prompt
column bytes format 999,999,999
select name, bytes from v$sgastat where name = 'free memory';
prompt prompt**************************************************** prompt SQL Summary Section prompt**************************************************** prompt
column
"Tot SQL run since startup" format 999,999,999 column "SQL executing now" format 999,999,999 select
sum(executions) "Tot SQL run since startup", sum(users_executing) "SQL executing now"
from v$sqlarea;
prompt prompt
prompt**************************************************** prompt Lock Section prompt**************************************************** prompt
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches prompt
=========================
prompt
select substr(username,1,12) "User", substr(lock_type,1,18) "Lock
Type", substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type
not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers
or other DDL prompt =========================
prompt
select
substr(username,1,12) "User", substr(owner,1,8)
"Owner", substr(name,1,15)
"Name", substr(a.type,1,20)
"Type", substr(mode_held,1,11)
"Mode held" from sys.dba_ddl_locks a, v$session b where a.session_id = b.sid;
prompt
prompt =========================
prompt DML LOCKS - These are table
and row locks... prompt
=========================
prompt
select
substr(username,1,12) "User", substr(owner,1,8)
"Owner", substr(name,1,20)
"Name", substr(mode_held,1,21)
"Mode held" from sys.dba_dml_locks a, v$session b where a.session_id = b.sid;
prompt prompt
prompt**************************************************** prompt Latch Section prompt**************************************************** prompt
if miss_ratio or
immediate_miss_ratio > 1 then
latch
prompt
contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora prompt
column "miss_ratio" format .99
column
"immediate_miss_ratio" format .99 select
substr(l.name,1,30) name, (misses/(gets+.001))*100
"miss_ratio", (immediate_misses/(immediate_gets+.001))*100 "immediate_miss_ratio"
from
v$latch l, v$latchname ln where l.latch# = ln.latch#
and
( (misses/(gets+.001))*100 > .2 or
(immediate_misses/(immediate_gets+.001))*100 > .2
)
order by l.name;
prompt prompt
prompt**************************************************** prompt Rollback Segment Section prompt**************************************************** prompt
if any count below is > 1% of the
total number of requests for data prompt then more rollback segments are needed
--column count format
999,999,999 select class, count
from v$waitstat
where class in ('free
list','system undo header','system undo block', 'undo
header','undo block')
group by class,count;
column "Tot
# of Requests for Data" format 999,999,999
select sum(value) "Tot # of Requests for Data" from v$sys stat where name
in ('db block gets',
'consistent gets');
prompt
prompt ========================= prompt ROLLBACK SEGMENT CONTENTION prompt
=========================
prompt
prompt If any ratio is > .01 then
more rollback segments are needed
column "Ratio" format 99.99999
select name,
waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b
where a.usn = b.usn;
column
"total_waits" format 999,999,999 column "total_timeouts" format 999,999,999 prompt
prompt
set
feedback on; prompt**************************************************** prompt Session Event Section prompt**************************************************** prompt
if average-wait > 0 then
contention exists
prompt
select substr(event,1,30) event, total_waits, total_timeouts, average_wait from v$session_event
where average_wait > 0 ;
--or total_timeouts > 0;
prompt prompt
prompt**************************************************** prompt Queue Section prompt**************************************************** prompt
average wait for queues should
be near zero ...
prompt
column
"totalq" format 999,999,999 column "#
queued" format 999,999,999
select
paddr, type "Queue type", queued "# queued", wait, totalq, decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;
set feedback on; prompt
prompt
--prompt****************************************************
--prompt Multi-threaded Server Section
--prompt****************************************************
--prompt
--prompt If the
following number is > 1
--prompt then increase MTS_MAX_SERVERS parm in init.ora
--prompt
-- select decode( totalq,
0, 'No Requests',
-- wait/totalq
|| ' hundredths of seconds')
-- "Avg
wait per request
queue"
-- from v$queue
-- where type = 'COMMON';
--prompt
--prompt If the following
number increases, consider
adding dispatcher processes
--prompt
-- select decode( sum(totalq), 0, 'No Responses',
-- sum(wait)/sum(totalq) || ' hundredths of seconds')
-- "Avg wait per response queue"
-- from v$queue q, v$dispatcher
d
-- where q.type = 'DISPATCHER'
-- and q.paddr = d.paddr;
--set feedback
off;
--prompt
--prompt
--prompt =========================
--prompt DISPATCHER USAGE
--prompt =========================
--prompt (If Time Busy > 50, then change
MTS_MAX_DISPATCHERS in init.ora)
--column "Time Busy" format 999,999.999
--column busy format 999,999,999
--column idle format 999,999,999
--select name, status, idle,
busy,
-- (busy/(busy+idle))*100 "Time Busy"
--from v$dispatcher;
--prompt
--prompt
--select count(*)
"Shared Server Processes"
-- from v$shared_server
-- where status = 'QUIT';
--prompt
--prompt
--prompt high-water mark for the multi-threaded server
--prompt
--select * from v$mts;
--prompt
--prompt****************************************************
--prompt file i/o should
be evenly distributed across drives.
--prompt
--select
--substr(a.file#,1,2) "#",
--substr(a.name,1,30) "Name",
--a.status,
--a.bytes,
--b.phyrds,
--b.phywrts
--from v$datafile a, v$filestat b
--where a.file# = b.file#;
--select substr(name,1,55) system_statistic, value
-- from v$sysstat
-- order by name;
spool
off; [px009u]: 99 99 52
No comments:
Post a Comment