RMAN> alter database open resetlogs;
Statement processed
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.23.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.23.00.00 in TARGET database is not current
My backup was Oracle version 19.23 and restored on 19.24 binaries
set linesize 400 pagesize 300
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN source_version FORMAT A10
COLUMN target_version FORMAT A10
select CON_ID,
TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;
run below command !!!
sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/dbmsrman.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtrmns.plb
SQL> @$ORACLE_HOME/rdbms/admin/prvtbkrs.plb
[oracle@srv1 RmanBackup]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 1 11:42:59 2024
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2803372975)
RMAN>
no more error !!!!
Now apply datapatch again !!!!
cd $ORACLE_HOME/OPatch
[oracle@srv1 OPatch]$ pwd
/u01/app/oracle/product/19.0.0/db_1/OPatch
[oracle@srv1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.24.0.0.0 Production on Fri Nov 1 11:56:22 2024
Copyright (c) 2012, 2024, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12423_2024_11_01_11_56_22/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 31219897 (OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)):
Binary registry: Installed
PDB CDB$ROOT: Applied successfully on 10-SEP-20 12.34.31.168250 PM
PDB PDB$SEED: Applied successfully on 10-SEP-20 12.34.40.711871 PM
PDB PDB1: Applied successfully on 10-SEP-20 12.41.43.472665 PM
Current state of release update SQL patches:
Binary registry:
19.24.0.0.0 Release_Update 240627235157: Installed
PDB CDB$ROOT:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 20-APR-24 10.05.35.931655 PM
PDB PDB$SEED:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 20-APR-24 10.09.18.365114 PM
PDB PDB1:
Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 20-APR-24 10.09.18.242487 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1
No interim patches need to be rolled back
Patch 36582781 (Database Release Update : 19.24.0.0.240716 (36582781)):
Apply from 19.23.0.0.0 Release_Update 240406004238 to 19.24.0.0.0 Release_Update 240627235157
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 36582781 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_ORADB_CDBROOT_2024Nov01_11_58_41.log (no errors)
Patch 36582781 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_ORADB_PDBSEED_2024Nov01_11_59_57.log (no errors)
Patch 36582781 apply (pdb PDB1): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36582781/25751445/36582781_apply_ORADB_PDB1_2024Nov01_11_59_57.log (no errors)
SQL Patching tool complete on Fri Nov 1 12:01:43 2024
[oracle@srv1 OPatch]$
set linesize 400 pagesize 300
COLUMN status FORMAT A10
COLUMN description FORMAT A70
COLUMN source_version FORMAT A15
COLUMN target_version FORMAT A15
select CON_ID,
TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;
CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION
---------- ----------------------------- ---------- ---------- --------------- ---------------------------------------------------------------------- --------------- ---------------
1 01-NOV-2024 11:59:39 36582781 RU APPLY Database Release Update : 19.24.0.0.240716 (36582781) 19.23.0.0.0 19.24.0.0.0
1 03-SEP-2020 00:49:24 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0
1 10-SEP-2020 12:34:31 31219897 INTERIM APPLY OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897) 19.3.0.0.0 19.3.0.0.0
1 10-SEP-2020 12:34:31 31281355 RU APPLY Database Release Update : 19.8.0.0.200714 (31281355) 19.3.0.0.0 19.8.0.0.0
1 20-APR-2024 00:32:33 34429835 RUR APPLY Database Release Update Revision : 19.15.2.0.221018 (34429835) 19.8.0.0.0 19.15.2.0.0
1 20-APR-2024 20:42:02 34429835 RUR ROLLBACK Database Release Update Revision : 19.15.2.0.221018 (34429835) 19.15.2.0.0 19.15.0.0.0
1 20-APR-2024 20:42:03 34419443 RU APPLY Database Release Update : 19.17.0.0.221018 (34419443) 19.15.0.0.0 19.17.0.0.0
1 20-APR-2024 22:05:35 36233263 RU APPLY Database Release Update : 19.23.0.0.240416 (36233263) 19.17.0.0.0 19.23.0.0.0
3 01-NOV-2024 12:00:49 36582781 RU APPLY Database Release Update : 19.24.0.0.240716 (36582781) 19.23.0.0.0 19.24.0.0.0
3 03-SEP-2020 00:58:10 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0
3 10-SEP-2020 12:41:43 31219897 INTERIM APPLY OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897) 19.3.0.0.0 19.3.0.0.0
3 10-SEP-2020 12:41:43 31281355 RU APPLY Database Release Update : 19.8.0.0.200714 (31281355) 19.3.0.0.0 19.8.0.0.0
3 20-APR-2024 00:32:47 34429835 RUR APPLY Database Release Update Revision : 19.15.2.0.221018 (34429835) 19.8.0.0.0 19.15.2.0.0
3 20-APR-2024 20:42:06 34429835 RUR ROLLBACK Database Release Update Revision : 19.15.2.0.221018 (34429835) 19.15.2.0.0 19.15.0.0.0
3 20-APR-2024 20:42:08 34419443 RU APPLY Database Release Update : 19.17.0.0.221018 (34419443) 19.15.0.0.0 19.17.0.0.0
3 20-APR-2024 22:09:18 36233263 RU APPLY Database Release Update : 19.23.0.0.240416 (36233263) 19.17.0.0.0 19.23.0.0.0
16 rows selected.
SQL> SQL> SQL>
Friday 1 November 2024
PL/SQL package SYS.DBMS_BACKUP_RESTORE version in TARGET database is not current
Wednesday 30 October 2024
RamPant scripts
From RamPant web
Chapter 2
< oracle_quick.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
echo "Please enter the number of seconds between snapshots."
read elapsed
$ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<!
execute statspack.snap snap" ;
exit
!
sleep $elapsed
$ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<!
execute statspack.snap;
select
name,
snap_id,
to_char(snap_time,' dd Mon YYYY HH24:mi:ss')
from
stats\$snapshot,
v\$database
where
snap_id > (select max(snap_id)-2 from stats\$snapshot)
;
< features_used.sql
select
samp.dbid,
fu.name,
samp.version,
detected_usages,
total_samples,
decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'),
NULL, 'FALSE',
to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE',
'FALSE')
currently_used,
first_usage_date,
last_usage_date,
aux_count,
feature_info,
last_sample_date,
last_sample_period,
sample_interval,
mt.description
from
wri$_dbu_usage_sample samp,
wri$_dbu_feature_usage fu,
wri$_dbu_feature_metadata mt
where
samp.dbid = fu.dbid and
samp.version = fu.version and
fu.name = mt.name and
fu.name not like '_DBFUS_TEST%' and /* filter test features */
bitand(mt.usg_det_method, 4) != 4 /* filter disabled feat */;
< phys_disk_reads.sql
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
;
< display_statistics.sql
prompt
prompt This will query the dba_hist_sysstat to display all values
prompt that exceed the value specified in
prompt the "where" clause of the query.
prompt
set pages 999
break on snap_time skip 2
accept stat_name char prompt 'Enter Statistic Name: ';
accept stat_value number prompt 'Enter Statistics Threshold value: ';
col snap_time format a19
col value format 999,999,999
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
and
value > &stat_value
order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi');
< track_hot_write.sql
prompt
prompt This will identify any single file who's write I/O
prompt is more than 25% of the total write I/O of the database.
prompt
set pages 999
break on snap_time skip 2
col filename format a40
col phywrts format 999,999,999
col snap_time format a20
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
filename,
phywrts
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
where
phywrts > 0
and
phywrts * 4 >
(
select
avg(value) all_phys_writes
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = 'physical writes'
and
value > 0
)
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
phywrts desc;
< rpt_sysstat_hr.sql
prompt This will query the dba_hist_sysstat view to
prompt display average values by hour of the day
set pages 999
break on snap_time skip 2
accept stat_name char prompt 'Enter Statistics Name: ';
col snap_time format a19
col avg_value format 999,999,999
select
to_char(begin_interval_time,'hh24') snap_time,
avg(value) avg_value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
group by
to_char(begin_interval_time,'hh24')
order by
to_char(begin_interval_time,'hh24');
< plot_values_by_hour.sql
prompt
prompt This will query the dba_hist_sysstat view to display
prompt average values by day-of-the-week
prompt
set pages 999
accept stat_name char prompt 'Enter Statistic Name: ';
col snap_time format a19
col avg_value format 999,999,999
select
to_char(begin_interval_time,'day') snap_time,
avg(value) avg_value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
group by
to_char(begin_interval_time,'day')
order by
decode(
to_char(begin_interval_time,'day'),
'sunday',1,
'monday',2,
'tuesday',3,
'wednesday',4,
'thursday',5,
'friday',6,
'saturday',7
)
;
Chapter 3
< sess_waits_ash.sql
select
b.username,
a.stat_name,
round((a.value / 1000000),3) time_secs
from
v$sess_time_model a,
v$session b
where
a.sid = b.sid
and
b.sid = 123
order by
3 desc;
< elapsed_time.sql
select
s.sid,
s.username,
s.module,
round(t.value/1000000,2) "elapsed processing time (sec)"
from
v$sess_time_model t,
v$session s
where
t.sid = s.sid
and
t.stat_name = 'db time'
and
s.username IS NOT NULL
and
t.value/1000000 >= 1;
< pct_of_tot_elapsed_time.sql
select
e.stat_name "E.STAT_NAME"
, Round((e.value - b.value)/1000000,2)"Time (s)"
, Round(decode( e.stat_name,'DB time'
, to_number(null)
, 100*(e.value - b.value)
)/
(select nvl((e1.value - b1.value),-1)
FROM dba_hist_sys_time_model e1
, dba_hist_sys_time_model b1
WHERE b1.snap_id = b.snap_id
AND e1.snap_id = e.snap_id
AND b1.dbid = b.dbid
AND e1.dbid = e.dbid
AND b1.instance_number = b.instance_number
AND e1.instance_number = e.instance_number
AND e1.stat_name = 'DB time'
AND b1.stat_id = e1.stat_id ),2) "Percent of Total DB Time"
from
dba_hist_sys_time_model e,
dba_hist_sys_time_model b
WHERE
b.snap_id = &pBgnSnap
AND e.snap_id = &pEndSnap
AND b.dbid = &pDbId
AND e.dbid = &pDbId
AND b.instance_number = &pInstNum
AND e.instance_number = &pInstNum
AND b.stat_id = e.stat_id
AND e.value - b.value > 0
ORDER BY 2 DESC
< avg_workload_hour_of_the_day.sql
select
to_char(end_interval_time,'HH24') "Hour of Day",
Round(avg(newtime.value-oldtime.value)/1000000,2) "Avg DB Time (Sec)"
from
dba_hist_sys_time_model oldtime,
dba_hist_sys_time_model newtime,
dba_hist_snapshot sn
where
newtime.snap_id = sn.snap_id
and
oldtime.snap_id = sn.snap_id-1
and
newtime.stat_name = 'DB time'
and
oldtime.stat_name = 'DB time'
having
avg(newtime.value-oldtime.value) > 0
group by
to_char(end_interval_time,'HH24');
Chapter 4
< cache_advice.sql
column c1 heading 'Cache Size (meg)' format 999,999,999,999
column c2 heading 'Buffers' format 999,999,999
column c3 heading 'Estd Phys|Read Factor' format 999.90
column c4 heading 'Estd Phys| Reads' format 999,999,999
select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
from
v$db_cache_advice
where
name = 'DEFAULT'
and
block_size = (select value from v$parameter
where name = 'db_block_size')
and
advice_status = 'ON';
< display_stat.sql
prompt
prompt This will query the dba_hist_sysstat view to display all values
prompt that exceed the value specified in the "where" clause of the query.
prompt
set pages 999
break on snap_time skip 2
accept stat_name char prompt 'Enter Statistic Name: ';
accept stat_value number prompt 'Enter Statistics Threshold value: ';
col snap_time format a19
col value format 999,999,999
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
and
value > &stat_value
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi')
;
< wait_time_detail_10g.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
prompt
prompt This will compare values from dba_hist_waitstat with
prompt detail information from dba_hist_active_sess_history.
prompt
set pages 999
set lines 80
break on snap_time skip 2
col snap_time heading 'Snap|Time' format a20
col file_name heading 'File|Name' format a40
col object_type heading 'Object|Type' format a10
col object_name heading 'Object|Name' format a20
col wait_count heading 'Wait|Count' format 999,999
col time heading 'Time' format 999,999
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
object_type,
object_name,
wait_count,
time
from
dba_hist_waitstat wait,
dba_hist_snapshot snap,
dba_hist_active_sess_history ash,
dba_data_files df,
dba_objects obj
where
wait.snap_id = snap.snap_id
and
wait.snap_id = ash.snap_id
and
df.file_id = ash.current_file#
and
obj.object_id = ash.current_obj#
and
wait_count > 50
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
file_name;
< file_io_hogs.sql
prompt This will identify any single file who's write I/O
prompt is more than 25% of the total write I/O of the database.
prompt
set pages 999
break on snap_time skip 2
col filename format a40
col phywrts format 999,999,999
col snap_time format a20
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
filename,
phywrts
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
where
phywrts > 0
and
phywrts * 4 >
(
select
avg(value) all_phys_writes
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = 'physical writes'
and
value > 0
)
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
phywrts desc;
Chapter 5
< sql_shared_cursor.sql
select
version_count,
address,
hash_value,
parsing_schema_name,
reason,
sql_text from (
select
address,''
||decode(max( UNBOUND_CURSOR),'Y', ' UNBOUND_CURSOR')
||decode(max( SQL_TYPE_MISMATCH),'Y', ' SQL_TYPE_MISMATCH')
||decode(max( OPTIMIZER_MISMATCH),'Y', ' OPTIMIZER_MISMATCH')
||decode(max( OUTLINE_MISMATCH),'Y', ' OUTLINE_MISMATCH')
||decode(max( STATS_ROW_MISMATCH),'Y', ' STATS_ROW_MISMATCH')
||decode(max( LITERAL_MISMATCH),'Y', ' LITERAL_MISMATCH')
||decode(max( SEC_DEPTH_MISMATCH),'Y', ' SEC_DEPTH_MISMATCH')
||decode(max( EXPLAIN_PLAN_CURSOR),'Y', ' EXPLAIN_PLAN_CURSOR')
||decode(max( BUFFERED_DML_MISMATCH),'Y', ' BUFFERED_DML_MISMATCH')
||decode(max( PDML_ENV_MISMATCH),'Y', ' PDML_ENV_MISMATCH')
||decode(max( INST_DRTLD_MISMATCH),'Y', ' INST_DRTLD_MISMATCH')
||decode(max( SLAVE_QC_MISMATCH),'Y', ' SLAVE_QC_MISMATCH')
||decode(max( TYPECHECK_MISMATCH),'Y', ' TYPECHECK_MISMATCH')
||decode(max( AUTH_CHECK_MISMATCH),'Y', ' AUTH_CHECK_MISMATCH')
||decode(max( BIND_MISMATCH),'Y', ' BIND_MISMATCH')
||decode(max( DESCRIBE_MISMATCH),'Y', ' DESCRIBE_MISMATCH')
||decode(max( LANGUAGE_MISMATCH),'Y', ' LANGUAGE_MISMATCH')
||decode(max( TRANSLATION_MISMATCH),'Y', ' TRANSLATION_MISMATCH')
||decode(max( ROW_LEVEL_SEC_MISMATCH),'Y', ' ROW_LEVEL_SEC_MISMATCH')
||decode(max( INSUFF_PRIVS),'Y', ' INSUFF_PRIVS')
||decode(max( INSUFF_PRIVS_REM),'Y', ' INSUFF_PRIVS_REM')
||decode(max( REMOTE_TRANS_MISMATCH),'Y', ' REMOTE_TRANS_MISMATCH')
||decode(max( LOGMINER_SESSION_MISMATCH),'Y', ' LOGMINER_SESSION_MISMATCH')
||decode(max( INCOMP_LTRL_MISMATCH),'Y', ' INCOMP_LTRL_MISMATCH')
||decode(max( OVERLAP_TIME_MISMATCH),'Y', ' OVERLAP_TIME_MISMATCH')
||decode(max( SQL_REDIRECT_MISMATCH),'Y', ' SQL_REDIRECT_MISMATCH')
||decode(max( MV_QUERY_GEN_MISMATCH),'Y', ' MV_QUERY_GEN_MISMATCH')
||decode(max( USER_BIND_PEEK_MISMATCH),'Y', ' USER_BIND_PEEK_MISMATCH')
||decode(max( TYPCHK_DEP_MISMATCH),'Y', ' TYPCHK_DEP_MISMATCH')
||decode(max( NO_TRIGGER_MISMATCH),'Y', ' NO_TRIGGER_MISMATCH')
||decode(max( FLASHBACK_CURSOR),'Y', ' FLASHBACK_CURSOR')
||decode(max( ANYDATA_TRANSFORMATION),'Y', ' ANYDATA_TRANSFORMATION')
||decode(max( INCOMPLETE_CURSOR),'Y', ' INCOMPLETE_CURSOR')
||decode(max( TOP_LEVEL_RPI_CURSOR),'Y', ' TOP_LEVEL_RPI_CURSOR')
||decode(max( DIFFERENT_LONG_LENGTH),'Y', ' DIFFERENT_LONG_LENGTH')
||decode(max( LOGICAL_STANDBY_APPLY),'Y', ' LOGICAL_STANDBY_APPLY')
||decode(max( DIFF_CALL_DURN),'Y', ' DIFF_CALL_DURN')
||decode(max( BIND_UACS_DIFF),'Y', ' BIND_UACS_DIFF')
||decode(max( PLSQL_CMP_SWITCHS_DIFF),'Y', ' PLSQL_CMP_SWITCHS_DIFF')
||decode(max( CURSOR_PARTS_MISMATCH),'Y', ' CURSOR_PARTS_MISMATCH')
||decode(max( STB_OBJECT_MISMATCH),'Y', ' STB_OBJECT_MISMATCH')
||decode(max( ROW_SHIP_MISMATCH),'Y', ' ROW_SHIP_MISMATCH')
||decode(max( PQ_SLAVE_MISMATCH),'Y', ' PQ_SLAVE_MISMATCH')
||decode(max( TOP_LEVEL_DDL_MISMATCH),'Y', ' TOP_LEVEL_DDL_MISMATCH')
||decode(max( MULTI_PX_MISMATCH),'Y', ' MULTI_PX_MISMATCH')
||decode(max( BIND_PEEKED_PQ_MISMATCH),'Y', ' BIND_PEEKED_PQ_MISMATCH')
||decode(max( MV_REWRITE_MISMATCH),'Y', ' MV_REWRITE_MISMATCH')
||decode(max( ROLL_INVALID_MISMATCH),'Y', ' ROLL_INVALID_MISMATCH')
||decode(max( OPTIMIZER_MODE_MISMATCH),'Y', ' OPTIMIZER_MODE_MISMATCH')
||decode(max( PX_MISMATCH),'Y', ' PX_MISMATCH')
||decode(max( MV_STALEOBJ_MISMATCH),'Y', ' MV_STALEOBJ_MISMATCH')
||decode(max( FLASHBACK_TABLE_MISMATCH),'Y', ' FLASHBACK_TABLE_MISMATCH')
||decode(max( LITREP_COMP_MISMATCH),'Y', ' LITREP_COMP_MISMATCH')
reason
from
v$sql_shared_cursor
group by
address
) join v$sqlarea using(address)
where
version_count>&versions
order by
version_count desc,
address;
< check_bind_sensitive_sql.sql
select
sql_id,
child_number,
is_bind_sensitive,
is_bind_aware
from
v$sql
where
sql_text =
'select
max(id)
from
acs_test_tab
where
record_type = :l_record_type';
The following are additional sample queries for 11g adaptive cursor sharing:
select
hash_value,
sql_id,
child_number,
range_id,
low,
high,
predicate
from
v$sql_cs_selectivity;
And:
select
hash_value,
sql_id,
child_number,
bucket_id,
count
from
v$sql_cs_histogram;
And:
select
sql_id,
hash_value,
plan_hash_value,
is_bind_sensitive,
is_bind_aware,
sql_text
from
v$sql;
And:
select
hash_value,
sql_id,
child_number,
bind_set_hash_value,
peeked,
executions,
rows_processed,
buffer_gets
cpu_time
from
v$sql_cs_statistics;
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
colimn "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
< plan9i.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set echo off;
set feedback on
set pages 999;
column nbr_FTS format 999,999
column num_rows format 999,999,999
column blocks format 999,999
column owner format a14;
column name format a24;
column ch format a1;
column object_owner heading "Owner" format a12;
column ct heading "# of SQL selects" format 999,999;
select
object_owner,
count(*) ct
from
v$sql_plan
where
object_owner is not null
group by
object_owner
order by
ct desc
;
--spool access.lst;
set heading off;
set feedback off;
set heading on;
set feedback on;
ttitle 'full table scans and counts| |The "K" indicates that the table is in the KEEP Pool (Oracle8).'
select
p.owner,
p.name,
t.num_rows,
-- ltrim(t.cache) ch,
decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
s.blocks blocks,
sum(a.executions) nbr_FTS
from
dba_tables t,
dba_segments s,
v$sqlarea a,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
a.address = p.address
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
having
sum(a.executions) > 9
group by
p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
sum(a.executions) desc;
column nbr_RID format 999,999,999
column num_rows format 999,999,999
column owner format a15;
column name format a25;
ttitle 'Table access by ROWID and counts'
select
p.owner,
p.name,
t.num_rows,
sum(s.executions) nbr_RID
from
dba_tables t,
v$sqlarea s,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'BY ROWID') p
where
s.address = p.address
and
t.table_name = p.name
and
t.owner = p.owner
having
sum(s.executions) > 9
group by
p.owner, p.name, t.num_rows
order by
sum(s.executions) desc;
--*************************************************
< plan10g.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
spool plan.lst
set echo off
set feedback on
set pages 999;
column nbr_FTS format 99,999
column num_rows format 999,999
column blocks format 9,999
column owner format a10;
column name format a30;
column ch format a1;
column time heading "Snapshot Time" format a15
column object_owner heading "Owner" format a12;
column ct heading "# of SQL selects" format 999,999;
break on time
select
object_owner,
count(*) ct
from
dba_hist_sql_plan
where
object_owner is not null
group by
object_owner
order by
ct desc
;
--spool access.lst;
set heading on;
set feedback on;
ttitle 'full table scans and counts| |The "K" indicates that the table is in the KEEP Pool (Oracle8).'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
p.name,
t.num_rows,
-- ltrim(t.cache) ch,
decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
s.blocks blocks,
sum(a.executions_delta) nbr_FTS
from
dba_tables t,
dba_segments s,
dba_hist_sqlstat a,
dba_hist_snapshot sn,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
a.snap_id = sn.snap_id
and
a.sql_id = p.sql_id
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
having
sum(a.executions_delta) > 1
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
1 asc;
column nbr_RID format 999,999,999
column num_rows format 999,999,999
column owner format a15;
column name format a25;
ttitle 'Table access by ROWID and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
p.name,
t.num_rows,
sum(a.executions_delta) nbr_RID
from
dba_tables t,
dba_hist_sqlstat a,
dba_hist_snapshot sn,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'TABLE ACCESS'
and
options = 'BY USER ROWID') p
where
a.snap_id = sn.snap_id
and
a.sql_id = p.sql_id
and
t.table_name = p.name
and
t.owner = p.owner
having
sum(a.executions_delta) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows
order by
1 asc;
--*************************************************
-- Index Report Section
--*************************************************
column nbr_scans format 999,999,999
column num_rows format 999,999,999
column tbl_blocks format 999,999,999
column owner format a9;
column table_name format a20;
column index_name format a20;
ttitle 'Index full scans and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
d.table_name,
p.name index_name,
seg.blocks tbl_blocks,
sum(s.executions_delta) nbr_scans
from
dba_segments seg,
dba_indexes d,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'INDEX'
and
options = 'FULL SCAN') p
where
d.index_name = p.name
and
s.snap_id = sn.snap_id
and
s.sql_id = p.sql_id
and
d.table_name = seg.segment_name
and
seg.owner = p.owner
having
sum(s.executions_delta) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name, seg.blocks
order by
1 asc;
ttitle 'Index range scans and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
d.table_name,
p.name index_name,
seg.blocks tbl_blocks,
sum(s.executions_delta) nbr_scans
from
dba_segments seg,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_indexes d,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'INDEX'
and
options = 'RANGE SCAN') p
where
d.index_name = p.name
and
s.snap_id = sn.snap_id
and
s.sql_id = p.sql_id
and
d.table_name = seg.segment_name
and
seg.owner = p.owner
having
sum(s.executions_delta) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name, seg.blocks
order by
1 asc;
ttitle 'Index unique scans and counts'
select
to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
p.owner,
d.table_name,
p.name index_name,
sum(s.executions_delta) nbr_scans
from
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_indexes d,
(select distinct
pl.sql_id,
object_owner owner,
object_name name
from
dba_hist_sql_plan pl
where
operation = 'INDEX'
and
options = 'UNIQUE SCAN') p
where
d.index_name = p.name
and
s.snap_id = sn.snap_id
and
s.sql_id = p.sql_id
having
sum(s.executions_delta) > 9
group by
to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name
order by
1 asc;
spool off
< optimizer_index_cost_adj.sql
col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;
< delete_forall.sql
set serveroutput on
declare
type t_id_tab is table of forall_test.id%TYPE;
type t_code_tab is table of forall_test.code%TYPE;
l_id_tab t_id_tab := t_id_tab();
l_code_tab t_code_tab := t_code_tab();
l_start number;
l_size number := 10000;
begin
-- Populate collections.
for i in 1 .. l_size loop
l_id_tab.extend;
l_code_tab.extend;
l_id_tab(l_id_tab.last) := i;
l_code_tab(l_code_tab.last) := to_char(i);
end loop;
-- Time regular updates.
l_start := dbms_utility.get_time;
for i in l_id_tab.first .. l_id_tab.last loop
delete from
forall_test
where
id = l_id_tab(i)
and
code = l_code_tab(i);
end loop;
rollback;
dbms_output.put_line('Normal Deletes : ' ||
(dbms_utility.get_time - l_start));
l_start := dbms_utility.get_time;
-- Time bulk updates.
forall i in l_id_tab.first .. l_id_tab.last
delete
from forall_test
where
id = l_id_tab(i)
and
code = l_code_tab(i);
dbms_output.put_line('Bulk Deletes : ' ||
(dbms_utility.get_time - l_start));
rollback;
end;
/
< update_forall.sql
set serveroutput on
declare
type t_id_tab is table of forall_test.id%TYPE;
type t_forall_test_tab is table of forall_test%ROWTYPE;
l_id_tab t_id_tab := t_id_tab();
l_tab t_forall_test_tab := t_forall_test_tab ();
l_start number;
l_size number := 10000;
begin
-- Populate collections.
for i in 1 .. l_size loop
l_id_tab.extend;
l_tab.extend;
l_id_tab(l_id_tab.last) := i;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := to_char(i);
l_tab(l_tab.last).description := 'Description: ' || to_char(i);
end loop;
-- Time regular updates.
l_start := dbms_utility.get_time;
for i in l_tab.first .. l_tab.last loop
update
forall_test
set
row = l_tab(i)
where
id = l_tab(i).id;
end loop;
dbms_output.put_line('Normal Updates : ' ||
(dbms_utility.get_time - l_start));
l_start := dbms_utility.get_time;
-- Time bulk updates.
-- ****************************************************
-- *** Here is the forall
-- ****************************************************
forall i in l_tab.first .. l_tab.last
update
forall_test
set
row = l_tab(i)
where
id = l_id_tab(i);
dbms_output.put_line('Bulk Updates : ' ||
(dbms_utility.get_time - l_start));
commit;
end;
/
< oracle10g_quick.ksh
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
spool rpt_last.lst
set pages 9999;
set feedback on;
set verify off;
column reads format 999,999,999
column writes format 999,999,999
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24'),
(newreads.value-oldreads.value) reads,
(newwrites.value-oldwrites.value) writes
from
dba_hist_sysstat oldreads,
dba_hist_sysstat newreads,
dba_hist_sysstat oldwrites,
dba_hist_sysstat newwrites,
dba_hist_snapshot sn
where
newreads.snap_id = (select max(sn.snap_id)
from dba_hist_snapshot)
and newwrites.snap_id = (select max(sn.snap_id)
from dba_hist_snapshot)
and oldreads.snap_id = sn.snap_id-1
and oldwrites.snap_id = sn.snap_id-1
and oldreads.stat_name = 'physical reads'
and newreads.stat_name = 'physical reads'
and oldwrites.stat_name = 'physical writes'
and newwrites.stat_name = 'physical writes'
;
prompt ***********************************************************
prompt This will identify any single file who's read I/O
prompt is more than 10% of the total read I/O of the database.
prompt
prompt The "hot" file should be examined, and the hot table/index
prompt should be identified using STATSPACK.
prompt
prompt - The busy file should be placed on a disk device with
prompt "less busy" files to minimize read delay and channel
prompt contention.
prompt
prompt - If small file has a hot small table, place the table
prompt in the KEEP pool
prompt
prompt - If the file has a large-table full-table scan, place
prompt the table in the RECYCLE pool and turn on parallel query
prompt for the table.
prompt ***********************************************************
column mydate format a16
column file_name format a40
column reads format 999,999,999
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate,
new.filename file_name,
new.phyrds-old.phyrds reads
from
dba_hist_filestatxs old, dba_hist_filestatxs new, dba_hist_snapshot snwhere sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.filename = old.filename
--and
-- new.phyrds-old.phyrds > 10000
and
(new.phyrds-old.phyrds)*10 >
(
select
(newreads.value-oldreads.value) reads
from
dba_hist_sysstat oldreads,
dba_hist_sysstat newreads,
dba_hist_snapshot sn1
where
sn.snap_id = sn1.snap_id
and newreads.snap_id = sn.snap_id
and oldreads.snap_id = sn.snap_id-1
and oldreads.stat_name = 'physical reads'
and newreads.stat_name = 'physical reads'
and (newreads.value-oldreads.value) > 0)
;
prompt ***********************************************************
prompt This will identify any single file who's write I/O
prompt is more than 10% of the total write I/O of the database.
prompt
prompt The "hot" file should be examined, and the hot table/index
prompt should be identified using STATSPACK.
prompt
prompt - The busy file should be placed on a disk device with
prompt "less busy" files to minimize write delay and channel
prompt channel contention.
prompt
prompt - If small file has a hot small table, place the table
prompt in the KEEP pool
prompt
prompt ***********************************************************
column mydate format a16
column file_name format a40
column writes format 999,999,999
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate,
new.filename file_name,
new.phywrts-old.phywrts writes
from
dba_hist_filestatxs old,
dba_hist_filestatxs new,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and new.snap_id = sn.snap_id
and old.snap_id = sn.snap_id-1
and new.filename = old.filename
--and
---- new.phywrts-old.phywrts > 10000
and (new.phywrts-old.phywrts)*10 >
(select(newwrites.value-oldwrites.value) writes
from
dba_hist_sysstat oldwrites,
dba_hist_sysstat newwrites,
dba_hist_snapshot sn1
where
sn.snap_id = sn1.snap_id
and newwrites.snap_id = sn.snap_id
and oldwrites.snap_id = sn.snap_id-1
and oldwrites.stat_name = 'physical writes'
and newwrites.stat_name = 'physical writes'
and (newwrites.value-oldwrites.value) > 0)
;
prompt ***********************************************************
prompt The data buffer hit ratio is controlled by the db_block_buffer or db_cache_size parameters.
prompt ***********************************************************
column logical_reads format 999,999,999
column phys_reads format 999,999,999
column phys_writes format 999,999,999
column "BUFFER HIT RATIO" format 999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
d.value "phys_writes",
round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value)) /
((a.value-e.value)+(b.value-f.value)))
"BUFFER HIT RATIO"
from
dba_hist_sysstat a,
dba_hist_sysstat b,
dba_hist_sysstat c,
dba_hist_sysstat d,
dba_hist_sysstat e,
dba_hist_sysstat f,
dba_hist_sysstat g,
dba_hist_snapshot sn
where
-- (round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value))
--/ ((a.value-e.value)+(b.value-f.value))) ) < 90
--and sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and a.snap_id = sn.snap_id
and b.snap_id = sn.snap_id
and c.snap_id = sn.snap_id
and d.snap_id = sn.snap_id
and e.snap_id = sn.snap_id-1
and f.snap_id = sn.snap_id-1
and g.snap_id = sn.snap_id-1
and a.stat_name = 'consistent gets'
and e.stat_name = 'consistent gets'
and b.stat_name = 'db block gets'
and f.stat_name = 'db block gets'
and c.stat_name = 'physical reads'
and g.stat_name = 'physical reads'
and d.stat_name = 'physical writes'
;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column reloads format 999,999,999
column hit_ratio format 999.99
column pin_hit_ratio format 999.99
break on mydate skip 2;
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate,
new.namespace,
(new.gethits-old.gethits)/(new.gets-old.gets) hit_ratio,
(new.pinhits-old.pinhits)/(new.pins-old.pins) pin_hit_ratio,
new.reloads
from
dba_hist_librarycache old,
dba_hist_librarycache new,
dba_hist_snapshot sn
where
new.snap_id = sn.snap_id
and old.snap_id = new.snap_id-1
and old.namespace = new.namespace
and new.gets-old.gets > 0
and new.pins-old.pins > 0
;
prompt ***********************************************************
prompt When there are high disk sorts, you should investigate
prompt increasing sort_area_size, or adding indexes to force index_full scans
prompt ***********************************************************
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .9999999999999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
newmem.value-oldmem.value sorts_memory,
newdsk.value-olddsk.value sorts_disk,
(newdsk.value-olddsk.value)/(newmem.value-oldmem.value) ratio
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_sysstat newdsk,
dba_hist_sysstat olddsk,
dba_hist_snapshot sn
where
-- Where there are more than 100 disk sorts per hour
-- newdsk.value-olddsk.value > 100
--and
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and newdsk.snap_id = sn.snap_id
and olddsk.snap_id = sn.snap_id-1
and newmem.snap_id = sn.snap_id
and oldmem.snap_id = sn.snap_id-1
and oldmem.stat_name = 'sorts (memory)'
and newmem.stat_name = 'sorts (memory)'
and olddsk.stat_name = 'sorts (disk)'
and newdsk.stat_name = 'sorts (disk)'
and newmem.value-oldmem.value > 0
;
prompt ***********************************************************
prompt When there is high I/O waits, disk bottlenecks may exist
prompt Run iostats to find the hot disk and shuffle files to
prompt remove the contention
prompt
prompt See p. 191 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
break on snapdate skip 2
column snapdate format a16
column filename format a40
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
old.filename,
new.wait_count -old.wait_count waits
from
dba_hist_filestatxs old,
dba_hist_filestatxs new,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and new.wait_count -old.wait_count > 0
and new.snap_id = sn.snap_id
and old.filename = new.filename
and old.snap_id = sn.snap_id-1
;
prompt ***********************************************************
prompt Buffer Bury Waits may signal a high update table with too
prompt few freelists. Find the offending table and add more freelists.
prompt ***********************************************************
column buffer_busy_wait format 999,999,999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and new.snap_id = sn.snap_id
and new.snap_id = sn.snap_id
and old.snap_id = sn.snap_id-1
--having
-- avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100
group by
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss')
;
prompt ***********************************************************
prompt High redo log space requests indicate a need to increase
prompt the log_buffer parameter
prompt ***********************************************************
column redo_log_space_requests format 999,999,999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
newmem.value-oldmem.value redo_log_space_requests
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
--and
-- newmem.value-oldmem.value > 30
and newmem.snap_id = sn.snap_id
and oldmem.snap_id = sn.snap_id-1
and oldmem.stat_name = 'redo log space requests'
and newmem.stat_name = 'redo log space requests'
and newmem.value-oldmem.value > 0
;
prompt ***********************************************************
prompt Table fetch continued row indicates chained rows, or
prompt fetches of long datatypes (long raw, blob)
prompt
prompt Investigate increasing db_block_size or reorganizing tables
prompt with chained rows.
prompt
prompt ***********************************************************
column table_fetch_continued_row format 999,999,999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
avg(newmem.value-oldmem.value) table_fetch_continued_row
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and newmem.snap_id = sn.snap_id
and oldmem.snap_id = sn.snap_id-1
and oldmem.stat_name = 'table fetch continued row'
and newmem.stat_name = 'table fetch continued row'
--and
-- newmem.value-oldmem.value > 0
--having
-- avg(newmem.value-oldmem.value) > 10000
group by
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss')
;
prompt ***********************************************************
prompt Enqueue Deadlocks indicate contention within the Oracle
prompt shared pool.
prompt
prompt Investigate increasing shared_pool_size
prompt ***********************************************************
column enqueue_deadlocks format 999,999,999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
a.value enqueue_deadlocks
from
dba_hist_sysstat a,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and a.snap_id = sn.snap_id
and a.stat_name = 'enqueue deadlocks'
;
prompt ***********************************************************
prompt Long-table full table scans can indicate a need to:
prompt
prompt - Make the offending tables parallel query
prompt (alter table xxx parallel degree yyy;)
prompt - Place the table in the RECYCLE pool
prompt - Build an index on the table to remove the FTS
prompt
prompt To locate the table, run access.sql
prompt
prompt ***********************************************************
column fts format 999,999,999
select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
newmem.value-oldmem.value fts
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_snapshot sn
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and newmem.snap_id = sn.snap_id
and oldmem.snap_id = sn.snap_id-1
and oldmem.stat_name = 'table scans (long tables)'
and newmem.stat_name = 'table scans (long tables)'
;
spool off;
Chapter 6
< awr_disk_reads.sql
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot;
< Creation Script for dba_hist_sysstat
create table
dba_hist_sysstat
as
select
s.snap_id,
s.dbid,
s.instance_number,
s.statistic#,
s.statistic_hash,
nm.statistic_name, value
from
wrm$_snapshot sn,
wrh$_sysstat s,
dba_hist_stat_name nm
where
s.statistic_hash = nm.statistic_hash
and s.statistic# = nm.statistic#
and s.dbid = nm.dbid
and s.snap_id = sn.snap_id
and s.dbid = sn.dbid
and s.instance_number = sn.instance_number
and sn.status = 0
and sn.bl_moved = 0
union all
select
s.snap_id,
s.dbid,
s.instance_number,
s.statistic#,
s.statistic_hash,
nm.statistic_name, value
from
WRM$_SNAPSHOT sn,
WRH$_SYSSTAT_BL s,
DBA_HIST_STAT_NAME nm
where
s.statistic_hash = nm.statistic_hash
and s.statistic# = nm.statistic#
and s.dbid = nm.dbid
and s.snap_id = sn.snap_id
and s.dbid = sn.dbid
and s.instance_number = sn.instance_number
and sn.status = 0
and sn.bl_moved = 1;
select
s1.ucomment,
w1.event,
s1.snap_id,
w1.total_waits,
lag(w1.total_waits)
over (order by s1.snap_id) prev_val,
w1.total_waits -
lag(w1.total_waits)
over (order by s1.snap_id) delta_val
from
stats$snapshot s1,
stats$system_event w1
where
s1.snap_id between 313 and 320
and
s1.snap_id = w1.snap_id
and
w1.event = 'db file sequential read'
order by
w1.event, s1.snap_id;
select
sy.snap_id,
sy.statistic# statistic#,
sy.name statname,
sy.value - (LAG(sy.value)
over (partition by sy.name
order by sy.snap_id)) statdelta
from
stats$sysstat sy
where
sy.snap_id in (12208,12599,13480,13843)
and
sy.name IN
('consistent gets','consistent changes',
'db block gets', 'db block changes')
order by
sy.name, sy.snap_id;
< wait_alert_email.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=proderp
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep \^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME
# sample every 10 seconds
SAMPLE_TIME=10
while true
do
#*************************************************************
# Test to see if Oracle is accepting connections
#*************************************************************
$ORACLE_HOME/bin/sqlplus -s /<<! > /tmp/check_$ORACLE_SID.ora
select * from v\$database;
exit
!
#*************************************************************
# If not, exit immediately . . .
#*************************************************************
check_stat=`cat /tmp/check_$ORACLE_SID.ora|grep -i error|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -gt 0 ]
then
exit 0
fi
rm -f /export/home/oracle/statspack/busy.lst
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!> /tmp/busy.lst
set feedback off;
select
sysdate,
event,
substr(tablespace_name,1,14),
p2
from
v\$session_wait a,
dba_data_files b
where
a.p1 = b.file_id
;
!
var=`cat /tmp/busy.lst|wc -l`
echo $var
if [[ $var -gt 1 ]];
then
echo
*******************************************************************"
echo "There are waits"
cat /tmp/busy.lst|mailx -s "Prod block wait found"\
info@remote-dba.net \
Larry_Ellison@oracle.com
echo
*******************************************************************"
exit
fi
sleep $SAMPLE_TIME
done
< event_read_waits.sql
select to_char(snap_time,'mm/dd/yyyy hh24:mi:ss') snaptime
, max(decode(event,'db file scattered read', nvl(wait_ms,0), null)) wait_ms_dbfscatrd
, max(decode(event,'db file sequential read',nvl(wait_ms,0), null)) wait_ms_dbfseqrd
, max(decode(event,'db file scattered read', nvl(waits,0), null)) waits_dbfscatrd
, max(decode(event,'db file sequential read',nvl(waits,0), null)) waits_dbfseqrd
from
(
select ps.snap_time
, event
, case
when (total_waits - lag_total_waits > 0)
then round(( (time_waited_micro - lag_time_waited_micro) / (total_waits - lag_total_waits)) / 1000)
else -1
end wait_ms
, (total_waits - lag_total_waits) waits
, (time_waited_micro - lag_time_waited_micro) time_waited
from (
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file sequential read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) - 1
)
union all
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file scattered read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) -1
)
order by event, snap_id
) a
, perfstat.stats$snapshot ss
, perfstat.stats$snapshot ps
where a.lag_snap_id = ps.snap_id
and a.snap_id = ss.snap_id
and a.lag_total_waits != a.total_waits
and a.event = a.lag_event
order by a.snap_id, event
)
group by snap_time
;
< wt_events_int.sql
select event
, waits "Waits"
, time "Wait Time (s)"
, pct*100 "Percent of Tot"
, waitclass "Wait Class"
from (select e.event_name event
, e.total_waits - nvl(b.total_waits,0) waits
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/
(select sum(e1.time_waited_micro - nvl(b1.time_waited_micro,0)) from dba_hist_system_event b1 , dba_hist_system_event e1
where b1.snap_id(+) = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid(+) = b.dbid
and e1.dbid = e.dbid
and b1.instance_number(+) = b.instance_number
and e1.instance_number = e.instance_number
and b1.event_id(+) = e1.event_id
and e1.total_waits > nvl(b1.total_waits,0)
and e1.wait_class <> 'Idle'
) pct
, e.wait_class waitclass
from
dba_hist_system_event b ,
dba_hist_system_event e
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> 'Idle'
order by time desc, waits desc
);
< awr_system_events.sql
select
event "Event Name",
waits "Waits",
timeouts "Timeouts",
time "Wait Time (s)",
avgwait "Avg Wait (ms)",
waitclass "Wait Class"
from
(select e.event_name event
, e.total_waits - nvl(b.total_waits,0) waits
, e.total_timeouts - nvl(b.total_timeouts,0) timeouts
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time
, decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL),
((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait
, e.wait_class waitclass
from
dba_hist_system_event b ,
dba_hist_system_event e
where
b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> 'Idle' )
order by time desc, waits desc;
< show_background_waits.sql
select
event "Event Name",
waits "Waits",
timeouts "Timeouts",
time "Wait Time (s)",
avgwait "Avg Wait (ms)",
waitclass "Wait Class"
from
(select e.event_name event
, e.total_waits - nvl(b.total_waits,0) waits
, e.total_timeouts - nvl(b.total_timeouts,0) timeouts
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time
, decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL),
((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait
, e.wait_class waitclass
from
dba_hist_bg_event_summary b ,
dba_hist_bg_event_summary e
where
b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> 'Idle' )
order by time desc, waits desc;
< wait_stat_int.sql
select e.class "E.CLASS"
, e.wait_count - nvl(b.wait_count,0) "Waits"
, e.time - nvl(b.time,0) "Total Wait Time (cs)"
, (e.time - nvl(b.time,0)) /
(e.wait_count - nvl(b.wait_count,0)) "Avg Time (cs)"
from dba_hist_waitstat b
, dba_hist_waitstat e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.dbid = e.dbid
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number = e.instance_number
and b.class = e.class
and b.wait_count < e.wait_count
order by 3 desc, 2 desc;
< enq_stat_int.sql
select
ety “Enqueue”,
reqs "Requests",
sreq "Successful Gets",
freq "Failed Gets",
waits "Waits",
wttm "Wait Time (s)",
awttm "Avg Wait Time(ms)"
from (
select /*+ ordered */
e.eq_type || '-' || to_char(nvl(l.name,' '))
|| decode( upper(e.req_reason)
, 'CONTENTION', null
, '-', null
, ' ('||e.req_reason||')') ety
, e.total_req# - nvl(b.total_req#,0) reqs
, e.succ_req# - nvl(b.succ_req#,0) sreq
, e.failed_req# - nvl(b.failed_req#,0) freq
, e.total_wait# - nvl(b.total_wait#,0) waits
, (e.cum_wait_time - nvl(b.cum_wait_time,0))/1000 wttm
, decode( (e.total_wait# - nvl(b.total_wait#,0))
, 0, to_number(NULL)
, ( (e.cum_wait_time - nvl(b.cum_wait_time,0))
/ (e.total_wait# - nvl(b.total_wait#,0))
)
) awttm
from dba_hist_enqueue_stat e
, dba_hist_enqueue_stat b
, v$lock_type l
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.dbid(+) = e.dbid
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number(+) = e.instance_number
and b.eq_type(+) = e.eq_type
and b.req_reason(+) = e.req_reason
and e.total_wait# - nvl(b.total_wait#,0) > 0
and l.type(+) = e.eq_type
order by wttm desc, waits desc);
< metric_summary.sql
select
metric_name “Metric Name”,
metric_unit "Metric Unit",
minval "Minimum Value",
maxval "Maximum Value",
average "Average Value"
from
dba_hist_sysmetric_summary
where
snap_id = &pEndSnap
and
dbid = &pDbId
and
instance_number = &pInstNum;
< sys_time_model_int.sql
column "Statistic Name" format A40
column "Time (s)" format 999,999
column "Percent of Total DB Time" format 999,999
select e.stat_name "Statistic Name"
, (e.value - b.value)/1000000 "Time (s)"
, decode( e.stat_name,'DB time'
, to_number(null)
, 100*(e.value - b.value)
)/
( select nvl((e1.value - b1.value),-1)
from dba_hist_sys_time_model e1
, dba_hist_sys_time_model b1
where b1.snap_id = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid = b.dbid
and e1.dbid = e.dbid
and b1.instance_number = b.instance_number
and e1.instance_number = e.instance_number
and b1.stat_name = 'DB time'
and b1.stat_id = e1.stat_id
)
"Percent of Total DB Time"
from dba_hist_sys_time_model e
, dba_hist_sys_time_model b
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.instance_number = &pInst_Num
and e.instance_number = &pInst_Num
and b.stat_id = e.stat_id
and e.value - b.value > 0
order by 2 desc;
< sys_stat_int.sql
select e.stat_name "Statistic Name"
, e.value - b.value "Total"
, round((e.value - b.value)/
( select
avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+
extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+
extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+
extract( second from (e1.end_interval_time-b1.end_interval_time)) )
from dba_hist_snapshot b1
,dba_hist_snapshot e1
where b1.snap_id = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid = b.dbid
and e1.dbid = e.dbid
and b1.instance_number = b.instance_number
and e1.instance_number = e.instance_number
and b1.startup_time = e1.startup_time
and b1.end_interval_time < e1.end_interval_time ),2) "Per Second"
from dba_hist_sysstat b
, dba_hist_sysstat e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.stat_id = e.stat_id
and e.stat_name not in ( 'logons current'
, 'opened cursors current'
, 'workarea memory allocated'
)
and e.value >= b.value
and e.value > 0
order by 1 asc;
< latch_int.sql
select e.latch_name "Latch Name"
, e.gets - b.gets "Get Requests"
, to_number(decode(e.gets, b.gets, null,
(e.misses - b.misses) * 100/(e.gets - b.gets))) "Percent Get Misses"
, to_number(decode(e.misses, b.misses, null,
(e.sleeps - b.sleeps)/(e.misses - b.misses))) "Avg Sleeps / Miss"
, (e.wait_time - b.wait_time)/1000000 "Wait Time (s)"
, e.immediate_gets - b.immediate_gets "No Wait Requests"
, to_number(decode(e.immediate_gets,
b.immediate_gets, null,
(e.immediate_misses - b.immediate_misses) * 100 /
(e.immediate_gets - b.immediate_gets))) "Percent No Wait Miss"
from dba_hist_latch b
, dba_hist_latch e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.dbid = e.dbid
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number = e.instance_number
and b.latch_hash = e.latch_hash
and e.gets - b.gets > 0
order by 1, 4;
< latch_miss_int.sql
select latchname "Latch Name",
nwmisses "No Wait Misses",
sleeps "Sleeps",
waiter_sleeps "Waiter Sleeps"
From (
select e.parent_name||' '||e.where_in_code latchname
, e.nwfail_count - nvl(b.nwfail_count,0) nwmisses
, e.sleep_count - nvl(b.sleep_count,0) sleeps
, e.wtr_slp_count - nvl(b.wtr_slp_count,0) waiter_sleeps
from dba_hist_latch_misses_summary b
, dba_hist_latch_misses_summary e
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.dbid(+) = e.dbid
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number(+) = e.instance_number
and b.parent_name(+) = e.parent_name
and b.where_in_code(+) = e.where_in_code
and e.sleep_count > nvl(b.sleep_count,0)
)
order by 1, 3 desc;
< lib_cache_int.sql
select b.namespace "Name Space"
, e.gets - b.gets "Get Requests"
, to_number(decode(e.gets,b.gets,null,
100 - (e.gethits - b.gethits) * 100/(e.gets - b.gets))) "Get Pct Miss"
, e.pins - b.pins "Pin Requests"
, to_number(decode(e.pins,b.pins,null,
100 - (e.pinhits - b.pinhits) * 100/(e.pins - b.pins))) "Pin Pct Miss"
, e.reloads - b.reloads "Reloads"
, e.invalidations - b.invalidations "Invalidations"
from dba_hist_librarycache b
, dba_hist_librarycache e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.dbid = e.dbid
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number = e.instance_number
and b.namespace = e.namespace;
< rowcache_int.sql
select
param "Parameter",
gets "Get Requests",
getm "Pct Miss"
From
(select lower(b.parameter) param
, e.gets - b.gets gets
, to_number(decode(e.gets,b.gets,null,
(e.getmisses - b.getmisses) * 100/(e.gets - b.gets))) getm
, e.scans - b.scans scans
, to_number(decode(e.scans,b.scans,null,
(e.scanmisses - b.scanmisses) * 100/(e.scans - b.scans))) scanm
, e.modifications - b.modifications mods
, e.usage usage
from dba_hist_rowcache_summary b
, dba_hist_rowcache_summary e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.dbid = e.dbid
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number = e.instance_number
and b.parameter = e.parameter
and e.gets - b.gets > 0 )
order by param;
< buf_pool_int.sql
select
name
, numbufs "Number of Buffers"
, buffs "Buffer Gets"
, conget "Consistent Gets"
, phread "Physical Reads"
, phwrite "Physical Writes"
, fbwait "Free Buffer Waits"
, bbwait "Buffer Busy Waits"
, wcwait "Write Complete Waits"
, poolhr "Pool Hit %"
From
(select e.name
, e.set_msize numbufs
, decode( e.db_block_gets - nvl(b.db_block_gets,0)
+ e.consistent_gets - nvl(b.consistent_gets,0)
, 0, to_number(null)
, (100* (1 - ( (e.physical_reads - nvl(b.physical_reads,0))
/ ( e.db_block_gets - nvl(b.db_block_gets,0)
+ e.consistent_gets - nvl(b.consistent_gets,0))
)
)
)
) poolhr
, e.db_block_gets - nvl(b.db_block_gets,0)
+ e.consistent_gets - nvl(b.consistent_gets,0) buffs
, e.consistent_gets - nvl(b.consistent_gets,0) conget
, e.physical_reads - nvl(b.physical_reads,0) phread
, e.physical_writes - nvl(b.physical_writes,0) phwrite
, e.free_buffer_wait - nvl(b.free_buffer_wait,0) fbwait
, e.write_complete_wait - nvl(b.write_complete_wait,0) wcwait
, e.buffer_busy_wait - nvl(b.buffer_busy_wait,0) bbwait
from dba_hist_buffer_pool_stat b
, dba_hist_buffer_pool_stat e
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.dbid(+) = e.dbid
and b.instance_number(+) = &pInst_Num
and e.instance_number = &pInst_Num
and b.instance_number(+) = e.instance_number
and b.id(+) = e.id)
order by 1;
< os_stat_int.sql
select e.stat_name "Statistic Name"
, decode(e.stat_name, 'NUM_CPUS', e.value, e.value - b.value) "Total"
, decode( instrb(e.stat_name, 'BYTES'), 0, to_number(null)
, round((e.value - b.value)/( select
avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+
extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+
extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+
extract( second from (e1.end_interval_time-b1.end_interval_time)) )
from dba_hist_snapshot b1
,dba_hist_snapshot e1
where b1.snap_id = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid = b.dbid
and e1.dbid = e.dbid
and b1.instance_number = b.instance_number
and e1.instance_number = e.instance_number
and b1.startup_time = e1.startup_time
and b1.end_interval_time < e1.end_interval_time ),2)) "Per Second"
from dba_hist_osstat b
, dba_hist_osstat e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
and e.dbid = &pDbId
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.stat_id = e.stat_id
and e.value >= b.value
and e.value > 0
order by 1 asc;
< high_sql_buf_gets.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
sql_id
, buffer_gets_total "Buffer Gets"
, executions_total "Executions"
, buffer_gets_total/executions_total "Gets / Exec"
, pct*100 "% Total"
, cpu_time_total/1000000 "CPU Time (s)"
, elapsed_time_total/1000000 "Elapsed Time (s)"
, module "SQL Module"
, stmt "SQL Statement"
from
(select
e.sql_id sql_id
, e.buffer_gets_total - nvl(b.buffer_gets_total,0) buffer_gets_total
, e.executions_total - nvl(b.executions_total,0) executions_total
, (e.buffer_gets_total - nvl(b.buffer_gets_total,0))/
( select e1.value - nvl(b1.value,0)
from dba_hist_sysstat b1 , dba_hist_sysstat e1
where b1.snap_id(+) = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid(+) = b.dbid
and e1.dbid = e.dbid
and b1.instance_number(+) = b.instance_number
and e1.instance_number = e.instance_number
and b1.stat_id = e1.stat_id
and e1.stat_name = 'session logical reads'
) pct
, e.elapsed_time_total - nvl(b.elapsed_time_total,0) elapsed_time_total
, e.cpu_time_total - nvl(b.cpu_time_total,0) cpu_time_total
, e.module
, t.sql_text stmt
from dba_hist_sqlstat e
, dba_hist_sqlstat b
, dba_hist_sqltext t
where b.snap_id(+) = @pBgnSnap
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.sql_id(+) = e.sql_id
and e.snap_id = &pEndSnap
and e.dbid = &pDBId
and e.instance_number = &pInstNum
and (e.executions_total - nvl(b.executions_total,0)) > 0
and t.sql_id = b.sql_id
)
order by 2 desc;
< seg_top_logreads.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
object_name "Object Name"
, tablespace_name "Tablespace Name"
, object_type "Object Type"
, logical_reads_total "Logical Reads"
, ratio "%Total"
from(
select n.owner||'.'||n.object_name||decode(n.subobject_name,null,null,'.'||n.subobject_name) object_name
, n.tablespace_name
, case when length(n.subobject_name) < 11 then
n.subobject_name
else
substr(n.subobject_name,length(n.subobject_name)-9)
end subobject_name
, n.object_type
, r.logical_reads_total
, round(r.ratio * 100, 2) ratio
from dba_hist_seg_stat_obj n
, (select *
from (select e.dataobj#
, e.obj#
, e.dbid
, e.logical_reads_total - nvl(b.logical_reads_total, 0) logical_reads_total
, ratio_to_report(e.logical_reads_total - nvl(b.logical_reads_total, 0)) over () ratio
from dba_hist_seg_stat e
, dba_hist_seg_stat b
where b.snap_id = 2694
and e.snap_id = 2707
and b.dbid = 37933856
and e.dbid = 37933856
and b.instance_number = 1
and e.instance_number = 1
and e.obj# = b.obj#
and e.dataobj# = b.dataobj#
and e.logical_reads_total - nvl(b.logical_reads_total, 0) > 0
order by logical_reads_total desc) d
where rownum <= 100) r
where n.dataobj# = r.dataobj#
and n.obj# = r.obj#
and n.dbid = r.dbid
)
order by logical_reads_total desc;
< db_tbsp_io.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select tbsp “Tablespace”
, ios "I/O Activity"
From (
select e.tsname tbsp
, sum (e.phyrds - nvl(b.phyrds,0)) +
sum (e.phywrts - nvl(b.phywrts,0)) ios
from dba_hist_filestatxs e
, dba_hist_filestatxs b
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.dbid(+) = e.dbid
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number(+) = e.instance_number
and b.file# = e.file#
and ( (e.phyrds - nvl(b.phyrds,0) ) +
(e.phywrts - nvl(b.phywrts,0)) ) > 0
group by e.tsname
union
select e.tsname tbsp
, sum (e.phyrds - nvl(b.phyrds,0)) +
sum (e.phywrts - nvl(b.phywrts,0)) ios
from dba_hist_tempstatxs e
, dba_hist_tempstatxs b
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.dbid(+) = e.dbid
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.instance_number(+) = e.instance_number
and b.file# = e.file#
and ( (e.phyrds - nvl(b.phyrds,0) ) +
(e.phywrts - nvl(b.phywrts,0) ) ) > 0
group by e.tsname
);
Chapter 7
< display_sql_captured.sql
display_sql_captured.sql
select aa, hv
from ( select /*+ ordered use_nl (b st) */
decode( st.piece
, 0
, lpad(to_char((e.buffer_gets - nvl(b.buffer_gets,0))
,'99,999,999,999')
,15)||' '||
lpad(to_char((e.executions - nvl(b.executions,0))
,'999,999,999')
,12)||' '||
lpad((to_char(decode(e.executions - nvl(b.executions,0)
,0, to_number(null)
,(e.buffer_gets - nvl(b.buffer_gets,0)) /
(e.executions - nvl(b.executions,0)))
,'999,999,990.0'))
,14) ||' '||
lpad((to_char(100*(e.buffer_gets - nvl(b.buffer_gets,0))/:gets
,'990.0'))
, 6) ||' '||
lpad( nvl(to_char( (e.cpu_time - nvl(b.cpu_time,0))/1000000
, '9990.00')
, ' '),8) || ' ' ||
lpad( nvl(to_char( (e.elapsed_time - nvl(b.elapsed_time,0))/1000000
, '99990.00')
, ' '),9) || ' ' ||
lpad(e.old_hash_value,10)||''||
decode(e.module,null,st.sql_text
,rpad('Module: '||e.module,80)||st.sql_text)
, st.sql_text) aa
, e.old_hash_value hv
from stats$sql_summary e
, stats$sql_summary b
, stats$sqltext st
where b.snap_id(+) = :bid
and b.dbid(+) = e.dbid
and b.instance_number(+) = e.instance_number
and b.old_hash_value(+) = e.old_hash_value
and b.address(+) = e.address
and b.text_subset(+) = e.text_subset
and e.snap_id = :eid
and e.dbid = :dbid
and e.instance_number = :inst_num
and e.old_hash_value = st.old_hash_value
and e.text_subset = st.text_subset
and st.piece <= &&num_rows_per_hash
and e.executions > nvl(b.executions,0)
and 100*(e.buffer_gets - nvl(b.buffer_gets,0))/:gets > &&top_pct_sql
order by (e.buffer_gets - nvl(b.buffer_gets,0)) desc, e.old_hash_value, st.piece
)
where rownum < &&top_n_sql;
Chapter 8
< Redo_allocation_latches.sql
select
round(
greatest(
(sum(decode(ln.name,'redo allocation',misses,0))
/greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)),
(sum(decode(ln.name,'redo allocation',immediate_misses,0))
/greatest(sum(decode(ln.name,'redo allocation',immediate_gets,0))
+sum(decode(ln.name,'redo allocation',immediate_misses,0)),1))
)*100,2)
from
v$latch l,
v$latchname ln
where
l.latch#=ln.latch#;
Chapter 9
--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
-- ********************************
-- Create column histograms
-- ********************************
exec dbms_stats.gather_table_stats
(null, ‘sales’ method_opt=> ‘for all columns size skewonly);
-- ********************************
-- Verify existence of histograms
-- ********************************
select
column_name,
histogram
from
user_tab_sol_statistics
where
table_name = ‘SALES’;
-- ****************************************
- Create the extended optimizer statistics
-- ****************************************
select
dbms_stats.create_extended_stats
(NULL, 'sales', '(product_price+sales_tax)')
from dual;
-- *********************************************************
-- Display extended statistics extensions
-- *********************************************************
column extension format a30
select
extension_name,
extension
from
dba_stat_extensions
where
table_name = 'EMP';
EXTENSION_NAME EXTENSION
------------------------------ ------------------------------
SYS_STU3VG629OEYG6FN0EKTGV_HQ6 ("JOB","DEPTNO")
SYS_STULPA1A#B6YL4KQ59DQO3OADQ ("JOB","MGR")
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
SYS_STUOK75YSL165W#_X8GUYL0A1X (UPPER("ENAME"))
-- *********************************************************
-- Display extended statistics distinct values and histograms
-- *********************************************************
column col_group format a30
select
e.extension col_group,
t.num_distinct,
t.histogram
from
dba_stat_extensions e
join
dba_tab_col_statistics t on e.extension_name=t.column_name
and t.table_name = 'EMP';
COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
("JOB","DEPTNO") 9 NONE
("JOB","MGR") 8 NONE
(LOWER("ENAME")) 14 NONE
(UPPER("ENAME")) 14 NONE
-- *********************************************************
-- Display extended statistics distinct values and histograms
-- *********************************************************
column col_group format a30
select
e.extension col_group,
t.num_distinct,
t.histogram
from
dba_stat_extensions e
join
dba_tab_col_statistics t
on
e.extension_name=t.column_name
and
t.table_name = 'EMP';
COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
("JOB","DEPTNO") 9 NONE
("JOB","MGR") 8 NONE
(LOWER("ENAME")) 14 NONE
(UPPER("ENAME")) 14 NONE
select
sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
(
sum(a.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c3,
(
sum(b.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c4,
(
sum(b.time_waited_micro) /
sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)
) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where
a.snap_id = b.snap_id
and
a.event_name = 'db file scattered read'
and
b.event_name = 'db file sequential read';
Chapter 10
< top_20_sessions.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select * from
(select b.sid sid,
decode (b.username,null,e.name,b.username) user_name,
d.spid os_id,
b.machine machine_name,
to_char(logon_time,'dd-mon-yy hh:mi:ss pm') logon_time,
(sum(decode(c.name,'physical reads',value,0)) +
sum(decode(c.name,'physical writes',value,0)) +
sum(decode(c.name,'physical writes direct',value,0)) +
sum(decode(c.name,'physical writes direct (lob)',value,0))+
sum(decode(c.name,'physical reads direct (lob)',value,0)) +
sum(decode(c.name,'physical reads direct',value,0)))
total_physical_io,
(sum(decode(c.name,'db block gets',value,0)) +
sum(decode(c.name,'db block changes',value,0)) +
sum(decode(c.name,'consistent changes',value,0)) +
sum(decode(c.name,'consistent gets',value,0)) )
total_logical_io,
(sum(decode(c.name,'session pga memory',value,0))+
sum(decode(c.name,'session uga memory',value,0)) )
total_memory_usage,
sum(decode(c.name,'parse count (total)',value,0)) parses,
sum(decode(c.name,'cpu used by this session',value,0))
total_cpu,
sum(decode(c.name,'parse time cpu',value,0)) parse_cpu,
sum(decode(c.name,'recursive cpu usage',value,0))
recursive_cpu,
sum(decode(c.name,'cpu used by this session',value,0)) -
sum(decode(c.name,'parse time cpu',value,0)) -
sum(decode(c.name,'recursive cpu usage',value,0))
other_cpu,
sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,
sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,
sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted,
sum(decode(c.name,'user commits',value,0)) commits,
sum(decode(c.name,'user rollbacks',value,0)) rollbacks,
sum(decode(c.name,'execute count',value,0)) executions
from sys.v_$sesstat a,
sys.v_$session b,
sys.v_$statname c,
sys.v_$process d,
sys.v_$bgprocess e
where a.statistic#=c.statistic# and
b.sid=a.sid and
d.addr = b.paddr and
e.paddr (+) = b.paddr and
c.NAME in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)',
'db block gets',
'db block changes',
'consistent changes',
'consistent gets',
'session pga memory',
'session uga memory',
'parse count (total)',
'CPU used by this session',
'parse time cpu',
'recursive cpu usage',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user commits',
'user rollbacks',
'execute count'
)
group by b.sid,
d.spid,
decode (b.username,null,e.name,b.username),
b.machine,
to_char(logon_time,'dd-mon-yy hh:mi:ss pm')
order by 6 desc)
where rownum < 21
< high_scan_sql.sql
select
c.username username,
count(a.hash_value) scan_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.dba_users c,
sys.v_$sql d
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type IN ('TABLE', 'TABLE PARTITION')
and
a.operation like '%TABLE%'
and
a.options = 'FULL'
and
c.user_id = d.parsing_user_id
and
d.hash_value = a.hash_value
and
b.bytes / 1024 > 1024
group by
c.username
order by
2 desc
;
< find_cartesian_joins.sql
select
username,
count(distinct c.hash_value) nbr_stmts
from
sys.v_$sql a,
sys.dba_users b,
sys.v_$sql_plan c
where
a.parsing_user_id = b.user_id
and
options = 'CARTESIAN'
and
operation like '%JOIN%'
and
a.hash_value = c.hash_value
group by
username
order by
2 desc
;
< high_resource_sql.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.executions,0,1,
a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 - round(100 *
a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('sys','system')
order by 3 desc)
where rownum < 21
< cartesian_sum.sql
select
count(distinct hash_value) carteisan_statements,
count(*) total_cartesian_joins
from
sys.v_$sql_plan
where
options = 'CARTESIAN'
and
operation like '%JOIN%'
< sql_cartesian.sql
select *
from
sys.v_$sql
where
hash_value in
(select hash_value
from
sys.v_$sql_plan
where
options = 'CARTESIAN'
and
operation LIKE '%JOIN%' )
order by hash_value;
< large_scan_count.sql
select
sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
from
(select
sql_text,
count(*) total_large_scans,
executions
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type IN ('TABLE', 'TABLE PARTITION')
and
a.operation LIKE '%TABLE%'
and
a.options = 'FULL'
and
c.hash_value = a.hash_value
and
b.bytes / 1024 > 1024
group by
sql_text, executions)
order by
4 desc;
< awr_sqlstat_deltas.sql
col c1 heading ‘Begin|Interval|time’ format a8
col c2 heading ‘SQL|ID’ format a13
col c3 heading ‘Exec|Delta’ format 9,999
col c4 heading ‘Buffer|Gets|Delta’ format 9,999
col c5 heading ‘Disk|Reads|Delta’ format 9,999
col c6 heading ‘IO Wait|Delta’ format 9,999
col c7 heading ‘Application|Wait|Delta’ format 9,999
col c8 heading ‘Concurrency|Wait|Delta’ format 9,999
break on c1
select
to_char(s.begin_interval_time,’mm-dd hh24’) c1,
sql.sql_id c2,
sql.executions_delta c3,
sql.buffer_gets_delta c4,
sql.disk_reads_delta c5,
sql.iowait_delta c6,
sql.apwait_delta c7,
sql.ccwait_delta c8
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
order by
c1,
c2;
< awr_sqlstat_deltas_detail.sql
col c1 heading ‘Begin|Interval|time’ format a8
col c2 heading ‘Exec|Delta’ format 999,999
col c3 heading ‘Buffer|Gets|Delta’ format 999,999
col c4 heading ‘Disk|Reads|Delta’ format 9,999
col c5 heading ‘IO Wait|Delta’ format 9,999
col c6 heading ‘App|Wait|Delta’ format 9,999
col c7 heading ‘Cncr|Wait|Delta’ format 9,999
col c8 heading ‘CPU|Time|Delta’ format 999,999
col c9 heading ‘Elpsd|Time|Delta’ format 999,999
accept sqlid prompt ‘Enter SQL ID: ‘
ttitle ‘time series execution for|&sqlid’
break on c1
select
to_char(s.begin_interval_time,’mm-dd hh24’) c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.apwait_delta c6,
sql.ccwait_delta c7,
sql.cpu_time_delta c8,
sql.elapsed_time_delta c9
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
sql_id = ‘&sqlid’
order by
c1;
< sqlstathist.sql
set echo off feed off lines 100 pages 9999
clear col
clear break
col beginttm head 'Begin|Interval' format a14
col sqlid head 'SQL|ID' format a13
col execsdlt head 'Delta|Execs' format 99990
col bufgetwaitdlt head 'Delta|Buffer|Gets' format 9999990
col dskrdwaitdlt head 'Delta|Disk|Reads' format 999990
col iowaitdlt head 'Delta|IO Wait' format 9999990
col appwaitdlt head 'Delta|Wait|App' format 9999990
col concurwaitdlt head 'Delta|Wait|Concur' format 99990
break on beginttm skip 1
spool sqlstathist.lis
set echo off feed off lines 100 pages 9999
clear col
clear break
col beginttm head 'Begin|Interval' format a14
col sqlid head 'SQL|ID' format a13
col execsdlt head 'Delta|Execs' format 99990
col bufgetwaitdlt head 'Delta|Buffer|Gets' format 9999990
col dskrdwaitdlt head 'Delta|Disk|Reads' format 999990
col iowaitdlt head 'Delta|IO Wait' format 9999990
col appwaitdlt head 'Delta|Wait|App' format 9999990
col concurwaitdlt head 'Delta|Wait|Concur' format 99990
break on beginttm skip 1
spool sqlstathist.lis
select
to_char(begin_interval_time,'mm-dd hh24:mi:ss') beginttm,
sql_id sqlid,
executions_delta execsdlt,
buffer_gets_delta bufgetwaitdlt,
disk_reads_delta dskrdwaitdlt,
iowait_delta iowaitdlt,
apwait_delta appwaitdlt,
ccwait_delta concurwaitdlt
from
dba_hist_snapshot sn,
dba_hist_sqlstat ss
where
ss.snap_id = sn.snap_id and
begin_interval_time > (sysdate - 4/24)
order by
beginttm,
( executions_delta + buffer_gets_delta +
disk_reads_delta + iowait_delta +
apwait_delta + ccwait_delta ) desc
/
spool off
clear break
clear col
< awr_high_cost_sql.sql
col c1 heading ‘SQL|ID’ format a13
col c2 heading ‘Cost’ format 9,999,999
col c3 heading ‘SQL Text’ format a200
select
p.sql_id c1,
p.cost c2,
to_char(s.sql_text) c3
from
dba_hist_sql_plan p,
dba_hist_sqltext s
where
p.id = 0
and
p.sql_id = s.sql_id
and
p.cost is not null
order by
p.cost desc
;
< awr_sql_object_char.sql
col c1 heading ‘Owner’ format a13
col c2 heading ‘Object|Type’ format a15
col c3 heading ‘Object|Name’ format a25
col c4 heading ‘Average|CPU|Cost’ format 9,999,999
col c5 heading ‘Average|IO|Cost’ format 9,999,999
break on c1 skip 2
break on c2 skip 2
select
p.object_owner c1,
p.object_type c2,
p.object_name c3,
avg(p.cpu_cost ) c4,
avg(p.io_cost ) c5
from
dba_hist_sql_plan p
where
p.object_name is not null
and
p.object_owner <> 'SYS'
group by
p.object_owner,
p.object_type,
p.object_name
order by
1,2,4 desc;
< awr_sql_object_char_detail.sql
accept tabname prompt ‘Enter Table Name:’
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Owner’ format a10
col c2 heading ‘Object|Type’ format a10
col c3 heading ‘Object|Name’ format a15
col c4 heading ‘Average|CPU|Cost’ format 9,999,999
col c5 heading ‘Average|IO|Cost’ format 9,999,999
break on c1 skip 2
break on c2 skip 2
select
to_char(sn.begin_interval_time,'mm-dd hh24') c0,
p.object_owner c1,
p.object_type c2,
p.object_name c3,
avg(p.cpu_cost ) c4,
avg(p.io_cost ) c5
from
dba_hist_sql_plan p,
dba_hist_sqlstat st,
dba_hist_snapshot sn
where
p.object_name is not null
and
p.object_owner <> 'SYS'
and
p.object_name = 'CUSTOMER_DETS'
and
p.sql_id = st.sql_id
and
st.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'mm-dd hh24'),
p.object_owner,
p.object_type,
p.object_name
order by
1,2,3 desc;
< awr_nested_join_alert.sql
col c1 heading ‘Date’ format a20
col c2 heading ‘Nested|Loops|Count’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
col c4 heading ‘Disk|Reads’ format 99,999,999
col c5 heading ‘CPU|Time’ format 99,999,999
accept nested_thr char prompt ‘Enter Nested Join Threshold: ‘
ttitle ‘Nested Join Threshold|&nested_thr’
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(*) c2,
sum(st.rows_processed_delta) c3,
sum(st.disk_reads_delta) c4,
sum(st.cpu_time_delta) c5
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.operation = ‘NESTED LOOPS’
having
count(*) > &hash_thr
group by
begin_interval_time;
< awr_sql_index.sql
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Index|Name’ format a20
col c2 heading ‘Disk|Reads’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
select
to_char(s.begin_interval_time,'mm-dd hh24') c0,
p.object_name c1,
sum(t.disk_reads_total) c2,
sum(t.rows_processed_total) c3
from
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where
p.sql_id = t.sql_id
and
t.snap_id = s.snap_id
and
p.object_type like '%INDEX%'
group by
to_char(s.begin_interval_time,'mm-dd hh24'),
p.object_name
order by
c0,c1,c2 desc;
< awr_sql_index_access.sql
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Index|Range|Scans’ format 999,999
col c3 heading ‘Index|Unique|Scans’ format 999,999
col c4 heading ‘Index|Full|Scans’ format 999,999
select
r.c1 c1,
r.c2 c2,
u.c2 c3,
f.c2 c4
from
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%RANGE%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) r,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%UNIQUE%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) u,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) f
where
r.c1 = u.c1
and
r.c1 = f.c1;
< awr_sql_object_avg_dy.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Week Day’ format a15
col c3 heading ‘Invocation|Count’ format 99,999,999
break on c1 skip 2
break on c2 skip 2
select
decode(c2,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday') c2,
c1,
c3
from
(
select
p.object_name c1,
to_char(sn.end_interval_time,'d') c2,
count(1) c3
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
p.object_name,
to_char(sn.end_interval_time,'d')
order by
c2,c1
)
;
< awr_sql_details.sql
accept sqlid prompt ‘Please enter SQL ID: ‘
col c1 heading ‘Operation’ format a20
col c2 heading ‘Options’ format a20
col c3 heading ‘Object|Name’ format a25
col c4 heading ‘Search Columns’ format 999,999
col c5 heading ‘Cardinality’ format 999,999
select
operation c1,
options c2,
object_name c3,
search_columns c4,
cardinality c5
from
dba_hist_sql_plan p
where
p.sql_id = '&sqlid'
order by
p.id;
< awr_full_table_scans.sql
ttitle ‘Large Full-table scans|Per Snapshot Period’
col c1 heading ‘Begin|Interval|time’ format a20
col c4 heading ‘FTS|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
where
p.object_owner <> 'SYS'
and
p.object_owner = o.owner
and
p.object_name = o.segment_name
and
o.blocks > 1000
and
p.operation like '%TABLE ACCESS%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1;
< awr_sql_access_hr.sql
ttitle ‘Large Table Full-table scans|Averages per Hour’
col c1 heading ‘Day|Hour’ format a20
col c2 heading ‘FTS|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
to_char(sn.begin_interval_time,'hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
where
p.object_owner <> 'SYS'
and
p.object_owner = o.owner
and
p.object_name = o.segment_name
and
o.blocks > 1000
and
p.operation like '%TABLE ACCESS%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'hh24')
order by
1;
< awr_sql_access_day.sql
ttitle ‘Large Table Full-table scans|Averages per Week Day’
col c1 heading ‘Week|Day’ format a20
col c2 heading ‘FTS|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
to_char(sn.begin_interval_time,'day') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
where
p.object_owner <> 'SYS'
and
p.object_owner = o.owner
and
p.object_name = o.segment_name
and
o.blocks > 1000
and
p.operation like '%TABLE ACCESS%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'day')
order by
1;
< awr_sql_scan_sums.sql
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Large|Table|Full Table|Scans’ format 999,999
col c3 heading ‘Small|Table|Full Table|Scans’ format 999,999
col c4 heading ‘Total|Index|Scans’ format 999,999
select
f.c1 c1,
f.c2 c2,
s.c2 c3,
i.c2 c4
from
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
where
p.object_owner <> 'SYS'
and
p.object_owner = o.owner
and
p.object_name = o.segment_name
and
o.blocks > 1000
and
p.operation like '%TABLE ACCESS%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) f,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
where
p.object_owner <> 'SYS'
and
p.object_owner = o.owner
and
p.object_name = o.segment_name
and
o.blocks < 1000
and
p.operation like '%INDEX%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) s,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) i
where
f.c1 = s.c1
and
f.c1 = i.c1
;
< awr_sql_full_scans_avg_dy.sql
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Index|Table|Scans’ format 999,999
col c3 heading ‘Full|Table|Scans’ format 999,999
select
i.c1 c1,
i.c2 c2,
f.c2 c3
from
(
select
to_char(sn.begin_interval_time,'day') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%TABLE ACCESS%'
and
p.options like '%INDEX%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'day')
order by
1 ) i,
(
select
to_char(sn.begin_interval_time,'day') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%TABLE ACCESS%'
and
p.options = 'FULL'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'day')
order by
1 ) f
where
i.c1 = f.c1;
< awr_hash_join_alert.sql
col c1 heading ‘Date’ format a20
col c2 heading ‘Hash|Join|Count’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
col c4 heading ‘Disk|Reads’ format 99,999,999
col c5 heading ‘CPU|Time’ format 99,999,999
accept hash_thr char prompt ‘Enter Hash Join Threshold: ‘
ttitle ‘Hash Join Threshold|&hash_thr’
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(*) c2,
sum(st.rows_processed_delta) c3,
sum(st.disk_reads_delta) c4,
sum(st.cpu_time_delta) c5
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.operation = 'HASH JOIN'
having
count(*) > &hash_thr
group by
begin_interval_time;
< monitor_hash_join_ful_ram.sql
select
tempseg_size
from
v$sql_workarea_active;
Chapter 11
< display_session_process_details.sql
select
a.sid,
a.serial#,
b.spid,
b.pid,
a.username,
a.osuser,
a.machine
from
v$session a,
v$process b
where
a.username IS NOT NULL
and
a.paddr=b.addr;
< create_10046_wait_table.sql
create directory
load_directory
as
'c:\oracle\product\admin\BOOKTST\udump';
drop table ext_10046_table_wait_events;
create table ext_10046_table_wait_events
(event_type varchar2(10),
cursor_number number,
wait_event_name varchar2(60),
total_elapsed_time number,
p1 varchar2(100),
p2 varchar2(100),
p3 varchar2(100),
trace_row_num number
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY load_directory
access parameters
( RECORDS DELIMITED BY NEWLINE
badfile load_directory:'bad_10046.log'
logfile load_directory:'load_10046.log'
skip 24
LOAD WHEN event_type="WAIT"
FIELDS RTRIM
(
event_type CHAR terminated by '#',
cursor_number CHAR terminated by ': nam=',
wait_event_name CHAR terminated by 'ela=',
total_elapsed_time CHAR terminated by 'p1=',
p1 CHAR terminated by 'p2=',
p2 CHAR terminated by 'p3=',
p3 CHAR terminated by WHITESPACE,
trace_row_num recnum
)
)
location ('booktst_ora_3640.trc')
)
reject limit unlimited;
< create_10046_wait_format.sql
drop table ext_10046_table_pef_events;
create table ext_10046_table_pef_events
(event_type varchar2(10),
cursor_number number,
pef_cpu_time number,
pef_elap number,
pef_blocks number,
pef_blocks_cm number,
pef_blocks_curmode number,
pef_lib_cache_misses number,
pef_rows_returned number,
pef_depth number,
pef_goal number,
pef_tim number,
trace_row_num number
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY load_directory
access parameters
( RECORDS DELIMITED BY NEWLINE
badfile load_directory:'bad_10046.log'
logfile load_directory:'load_10046.log'
skip 24
LOAD WHEN ( event_type="EXEC"
or event_type="FETCH"
or event_type="PARSE" )
FIELDS RTRIM
(
event_type CHAR terminated by '#',
cursor_number CHAR terminated by ':c=',
pef_cpu_time CHAR terminated by ',e=',
pef_elap CHAR terminated by ',p=',
pef_blocks CHAR terminated by ',cr=',
pef_blocks_cm CHAR terminated by ',cu=',
pef_blocks_curmode CHAR terminated by ',mis=',
pef_lib_cache_misses CHAR terminated by ',r=',
pef_rows_returned CHAR terminated by ',dep=',
pef_depth CHAR terminated by ',og=',
pef_goal CHAR terminated by ',tim=',
pef_tim CHAR terminated by WHITESPACE,
trace_row_num recnum
)
)
location ('booktst_ora_3640.trc')
)
reject limit unlimited;
< view_10046_trace.sql
create or replace view
vw_10046_view as
select
trace_row_num,
event_type,
cursor_number,
wait_event_name,
to_char(total_elapsed_time) wait_time,
-1 command
from
ext_10046_table_wait_events
union
select
trace_row_num,
event_type,
cursor_number,
null,
to_char(pef_tim),
-1
from
ext_10046_table_pef_events
union
select
distinct
b.trace_row_num,
null,
null,
null,
sql_text,
to_number(cur_oct) command
from
v$sql
a,
ext_10046_table_cursor_events b
where
upper(b.cur_ad)=a.address (+)
order by 1;
create table tab_10046 as
select * from vw_10046_view;
Create index ix_tab_10046_01 on tab_10046(trace_row_num);
Create index ix_tab_10046_02 on tab_10046(command);
Create index ix_tab_10046_03 on tab_10046(event_type);
Create index ix_tab_10046_04 on tab_10046
(event_type, trace_row_num, cursor_number, wait_time);
< trace_exec_parse_fetch_sql.sql
column time_between_events heading “Time|Between|Events”
column wait_time format 9999999999
select
event_type,
cursor_number,
to_number(wait_time) wait_time,
(wait_time/1000000)-lag(wait_time/1000000, 1)
over
(order by trace_row_num) "time_between_events"
from
tab_10046
where
event_type in ('EXEC','FETCH','PARSE')
order by
trace_row_num;
< filter_trace_file.sql
select
trace_row_num,
event_type,
cursor_number,
to_number(wait_time) wait_time,
event_time
from
(select trace_row_num, event_type, cursor_number, wait_time,
(wait_time/1000000)-lag(wait_time/1000000, 1)
over (order by trace_row_num) "EVENT_TIME" from tab_10046
where
event_type in ('EXEC','FETCH','PARSE')
order by
trace_row_num )
where
trace_row_num in
( select trace_row_num
from
(select trace_row_num,
(wait_time/1000000)-
lag(wait_time/1000000, 1)
over (order by trace_row_num)
"EVENT_TIME"
from
tab_10046
where
event_type in ('EXEC','FETCH','PARSE') )
where
event_time > .25 );
< filter_trace_file_range.sql
select
’E’ operation,
trace_row_num,
event_type,
cursor_number,
wait_time,
event_time
from
select
trace_row_num,
event_type,
cursor_number,
to_number(wait_time) wait_time,
(wait_time/1000000)-lag(wait_time/1000000, 1)
over (order by trace_row_num) "EVENT_TIME" from tab_10046
where
event_type in ('EXEC','FETCH','PARSE')
order by trace_row_num )
where
trace_row_num in
( select trace_row_num
from
(select trace_row_num,
(wait_time/1000000)-
lag(wait_time/1000000, 1)
over (order by trace_row_num) "EVENT_TIME"
from tab_10046
where event_type in ('EXEC','FETCH','PARSE') )
where event_time > .25 )
UNION
select
’B’ Operation,
trace_row_num,
event_type,
cursor_number,
to_number(wait_time) wait_time,
event_time
from
(select
trace_row_num,
event_type,
cursor_number,
wait_time,
(wait_time/1000000)-lag(wait_time/1000000, 1)
over (order by trace_row_num) "EVENT_TIME“ from tab_10046
where
event_type in ('EXEC','FETCH','PARSE')
order by trace_row_num )
where
rowid in
(select q_rowid
from (select trace_row_num,
(wait_time/1000000)-
lag(wait_time/1000000, 1)
over (order by trace_row_num) "EVENT_TIME“,
lag(rowid) over
(order by trace_row_num) q_rowid
from tab_10046
where
event_type in ('EXEC','FETCH','PARSE') )
where event_time > .25 )
order by trace_row_num;
< trace_detail.sql
select
trace_row_num,
event_type,
wait_event_name,
cursor_number,
wait_time/1000000 wait_time
from
tab_10046
where
trace_row_num between 4851699 and 4851727
order by
trace_row_num;
create table test (
pkey number(15) not null,
pdate date not null,
pchar varchar2(4000) not null
);
create sequence test_seq;
create procedure pop_test(p_pass in number)
as begin
for i in 1 .. p_pass loop
insert into test
select test_seq.nextval, sysdate, object_name||'.'||object_type||'.'||owner
from sys.dba_objects;
commit;
end loop;
end;
/
exec pop_test(500);
Chapter 12
< statspack_osstats_rollup.sql
select *
from
(select
snap_id,
osstat_id,
value
from
stats$osstat
where
snap_id in (10046, 10047)) – filter for desired snapshots
pivot
sum(value) for osstat;
< display_os_stats.sql
select
sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
(
sum(a.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c3,
(
sum(b.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c4,
(
sum(b.time_waited_micro) /
sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)
) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where
a.snap_id = b.snap_id
and
a.event_name = 'db file scattered read'
and
b.event_name = 'db file sequential read';
< awr_osstats.sql
select e.stat_name "Statistic Name"
, decode(e.stat_name, 'NUM_CPUS', e.value, e.value - b.value) "Total"
, decode( instrb(e.stat_name, 'BYTES'), 0, to_number(null)
, round((e.value - b.value)/( select
avg( extract( day from (e1.end_interval_time-b1.end_interval_time) )*24*60*60+
extract( hour from (e1.end_interval_time-b1.end_interval_time) )*60*60+
extract( minute from (e1.end_interval_time-b1.end_interval_time) )*60+
extract( second from (e1.end_interval_time-b1.end_interval_time)) )
from dba_hist_snapshot b1
,dba_hist_snapshot e1
where b1.snap_id = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid = b.dbid
and e1.dbid = e.dbid
and b1.instance_number = b.instance_number
and e1.instance_number = e.instance_number
and b1.startup_time = e1.startup_time
and b1.end_interval_time < e1.end_interval_time ),2)) "Per Second"
from
dba_hist_osstat b,
dba_hist_osstat e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
order by 1 asc;
< awr_os_stats_load_cpu_ram.sql
select
s1t0.snap_id,
to_char(s0.BEGIN_INTERVAL_TIME,'YYYY-Mon-DD HH24:MI:SS') time,
s1t1.value - s1t0.value as busy_time,
s2t1.value as load,
s3t1.value as num_cpus,
s4t1.value as physical_memory_bytes
from dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_osstat s1t0,
dba_hist_osstat s1t1,
dba_hist_osstat s2t1,
dba_hist_osstat s3t1,
dba_hist_osstat s4t1
where
s0.dbid = (select dbid from v$database)
and s1t0.dbid = s0.dbid
and s1t1.dbid = s0.dbid
and s2t1.dbid = s0.dbid
and s3t1.dbid = s0.dbid
and s4t1.dbid = s0.dbid
and s0.instance_number = 1
and s1t0.instance_number = s0.instance_number
and s1t1.instance_number = s0.instance_number
and s2t1.instance_number = s0.instance_number
and s3t1.instance_number = s0.instance_number
and s4t1.instance_number = s0.instance_number
and s1.snap_id = s0.snap_id + 1
and s1t0.snap_id = s0.snap_id
and s1t1.snap_id = s0.snap_id + 1
and s2t1.snap_id = s0.snap_id
and s3t1.snap_id = s0.snap_id
and s4t1.snap_id = s0.snap_id
and s1t0.stat_name = 'BUSY_TIME'
and s1t1.stat_name = s1t0.stat_name
and s2t1.stat_name = 'LOAD'
and s3t1.stat_name = 'NUM_CPUS'
and s4t1.stat_name = 'PHYSICAL_MEMORY_BYTES'
order by snap_id asc;
< awr_stats_deltas.sql
select
to_char(s.end_interval_time,'DD-MON-YYYY HH24:MI') SNAP_TIME,
os.stat_name,
os.value,
lag(os.value,1) over
(PARTITION BY os.STAT_NAME order by os.snap_id) prev,
case
when os.stat_name = 'LOAD'
then os.value
when os.stat_name = 'PHYSICAL_MEMORY_BYTES'
then os.value
else
os.value - lag(os.value,1) over
(PARTITION BY os.STAT_NAME order by
os.snap_id)
end value
from
dba_hist_snapshot s,
dba_hist_osstat os
where
s.snap_id = os.snap_id
order by
os.snap_id,os.stat_name;
< cr_vmstat_tab.sql
connect perfstat/perfstat;
drop table stats$vmstat;
create table stats$vmstat
(
start_date date,
duration number,
server_name varchar2(20),
runque_waits number,
page_in number,
page_out number,
user_cpu number,
system_cpu number,
idle_cpu number,
wait_cpu number
)
tablespace perfstat;
< get_vmstat.ksh (Linux version)
#!/bin/ksh
# This is the Linux version
#!/bin/ksh
# This is the Linux version
ORACLE_HOME=/usr/app/oracle/admin/product/8/1/6
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME
# sample every five minutes (300 seconds) . . . .
SAMPLE_TIME=300
while true
do
vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$
# run vmstat and direct the output into the Oracle table . . .
cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU
DLE_CPU
do
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@testsys1< /dev/null 2>&1 &
fi
< vmstat_exception_rpt.sql
set lines 80;
set pages 999;
set feedback off;
set verify off;
column my_date heading 'date hour' format a20
column c2 heading runq format 999
column c3 heading pg_in format 999
column c4 heading pg_ot format 999
column c5 heading usr format 999
column c6 heading sys format 999
column c7 heading idl format 999
column c8 heading wt format 999
ttitle 'run queue > 2|May indicate an overloaded CPU|When runqueue exceeds
the number of CPUs| on the server, tasks are waiting for service.';
select
server_name,
to_char(start_date,'YY/MM/DD HH24') my_date,
avg(runque_waits) c2,
avg(page_in) c3,
avg(page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
runque_waits > 2
and start_date > sysdate-&&1
group by
server_name,
to_char(start_date,'YY/MM/DD HH24')
ORDER BY
server_name,
to_char(start_date,'YY/MM/DD HH24')
;
ttitle 'page_in > 1|May indicate overloaded memory|Whenever Unix performs
a page-in, the RAM memory | on the server has been exhausted and swap pages are being used.';
select
server_name,
to_char(start_date,'YY/MM/DD HH24') my_date,
avg(runque_waits) c2,
avg(page_in) c3,
avg(page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
page_in > 1
and start_date > sysdate-&&1
group by
server_name,
to_char(start_date,'YY/MM/DD HH24')
ORDER BY
server_name,
to_char(start_date,'YY/MM/DD HH24')
;
ttitle 'user+system CPU > 70%|Indicates periods with a fully-loaded CPU subssystem.|Periods of 100% utilization are only a | concern when runqueue values exceeds the number of CPs on the server.';
select
server_name,
to_char(start_date,'YY/MM/DD HH24') my_date,
avg(runque_waits) c2,
avg(page_in) c3,
avg(page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
(user_cpu + system_cpu) > 70
and start_date > sysdate-&&1
group by
server_name,
to_char(start_date,'YY/MM/DD HH24')
ORDER BY
server_name,
to_char(start_date,'YY/MM/DD HH24')
;
< phys_reads.sql
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
where
phyrds > 10000;
Chapter 13
< display_hot_cold_files.sql
select
dg.name diskgroup,
t.name,
t.stripe,
t.redundancy,
t.primary_region,
t.mirror_region
from
v$asm_diskgroup dg,
v$asm_template t
where
dg.group_number = t.group_number
and
dg.name = 'DATA' ORDER BY t.name;
select
dg.name diskgroup,
f.file_number,
f.primary_region,
f.mirror_region,
f.hot_reads,
f.hot_writes,
f.cold_reads,
f.cold_writes
from
v$asm_diskgroup dg,
v$asm_file f
where
dg.group_number = f.group_number and dg.name = 'DATA';
set serveroutput on
declare
lat integer;
iops integer;
mbps integer;
begin
dbms_resource_manager.calibrate_io (2, 10, iops, mbps, lat);
dbms_output.put_line ('max_iops = ' || iops);
dbms_output.put_line ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/
drop table perfstat.stats$iostat;
create table
perfstat.stats$iostat
(
snap_time date,
elapsed_seconds number(4),
hdisk varchar2(8),
kb_read number(9,0),
kb_write number(9,0)
)
tablespace perfstat
storage (initial 20m next 1m );
create index
perfstat.stats$iostat_date_idx
on
perfstat.stats$iostat
(snap_time)
tablespace perfstat
storage (initial 5m next 1m);
create index
perfstat.stats$iostat_hdisk_idx
on
perfstat.stats$iostat
(hdisk)
tablespace perfstat
storage (initial 5m next 1m);
< get_iostat_solaris.ksh
#!/bin/ksh
while true
do
iostat -x 300 1|\
sed 1,2d|\
awk '{ printf("%s %s %s\n", $1, $4, $5) }' |\
while read HDISK VMSTAT_IO_R VMSTAT_IO_W
do
if [ $VMSTAT_IO_R -gt 0 ] and [ $VMSTAT_IO_W -gt 0 ]
then
sqlplus -s perfstat/perfstat <<!
insert into
perfstat.stats\$iostat
values
(SYSDATE, 5, '$HDISK', $VMSTAT_IO_R,$VMSTAT_IO_W);
exit
!
fi
done
sleep 300
done
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=prodz1
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'`
PATH=$ORACLE_HOME/bin:$PATH
MON=`echo ~oracle/iostat`
#----------------------------------------
# If it is not running, then start it . . .
#----------------------------------------
check_stat=`ps -ef|grep get_iostat|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 2 ]
then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 &
fi
< rpt_disk.sql
column hdisk format a10;
column mydate format a15;
column sum_kb_read format 999,999;
column sum_kb_write format 999,999;
set pages 999;
break on hdisk skip 1;
select
hdisk,
-- to_char(snap_time,'yyyy-mm-dd HH24:mi:ss') mydate,
-- to_char(snap_time,'yyyy-mm-dd HH24') mydate,
to_char(snap_time,'day') mydate,
sum(kb_read) sum_kb_read,
sum(kb_write) sum_kb_write
from
stats$iostat
group by
hdisk
,to_char(snap_time,'day')
-- ,to_char(snap_time,'yyyy-mm-dd HH24:mi:ss')
-- ,to_char(snap_time,'yyyy-mm-dd HH24');
< asm_disk_iostat.sql
col instname format a08 heading 'inst|name'
col dbname format a08 heading 'db name'
col group_name format a08 heading 'disk|group|name'
col disk_number format 9999 heading 'asm|disk|#'
col reads format 99999999 heading 'disk|reads'
col read_time format 999999 heading 'read|time|(s)'
col read_errs format 999999 heading 'read|errors'
col mb_read format 999999.9 heading 'bytes|read|(mb)'
col writes format 99999999 heading 'disk|writes'
col write_errs format 999999 heading 'write|errors'
col write_time format 999999 heading 'write|time|(s)'
col mb_wrtn format 999999.9 heading 'bytes|written|(mb)'
ttitle 'ASM disk I/O statistics'
select
a.dbname,
a.instname,
b.name group_name,
a.disk_number,
a.reads,
a.read_errs,
a.read_time,
( a.bytes_read / (1024*1024)) mb_read,
a.writes,
a.write_errs,
a.write_time,
( a.bytes_written / (1024*1024)) mb_wrtn,
-- **************************
-- new data columns in 11gr2
-- **************************
(a.hot_bytes_read / (1024*1024)) hot_mb_read,
(a.cold_bytes_read / (1024*1024)) cold_mb_read,
(a.hot_bytes_written / (1024*1024)) hot_mb_wrtn,
(a.cold_bytes_written / (1024*1024)) cold_mb_wrtn
from
v$asm_disk_iostat a,
v$asm_diskgroup b
where a.group_number = b.group_number
order by
a.dbname,
a.instname,
adg.name;
< blocks_to_buffers.sql
select
decode(
pd.bp_id,
1,'KEEP',
2,'RECYCLE',
3,'DEFAULT',
4,'2K SUBCACHE',
5,'4K SUBCACHE',
6,'8K SUBCACHE',
7,'16K SUBCACHE',
8,'32K SUBCACHE',
'UNKNOWN') subcache,
bh.object_name,
bh.blocks
from
x$kcbwds ds,
x$kcbwbpd pd,
(select /*+ use_hash(x) */
set_ds,
o.name object_name,
count(*) BLOCKS
from
obj$ o,
x$bh x
where
o.dataobj# = x.obj
and
x.state !=0
and
o.owner# !=0
group by
set_ds,o.name) bh
where
ds.set_id >= pd.bp_lo_sid
and
ds.set_id <= pd.bp_hi_sid
and
pd.bp_size != 0 and ds.addr=bh.set_ds;
< ora_phys_io.sql
select
d.name file_name,
c.name tablespace_name,
b.phyrds,
b.phywrts,
b.phyblkrd,
b.phyblkwrt,
b.readtim,
b.writetim
from
sys.v_$datafile a,
sys.v_$filestat b,
sys.ts$ c,
sys.v_$dbfile d,
sys.file$ e
where
a.file# = b.file#
and
a.file# = d.file#
and
e.ts# = c.ts#
and
e.file# = d.file#
union all
select
v.fnnam file_name,
c.name tablespace_name,
b.phyrds,
b.phywrts,
b.phyblkrd,
b.phyblkwrt,
b.readtim,
b.writetim
from
sys.v_$tempfile a,
sys.v_$tempstat b,
sys.ts$ c,
sys.x$kccfn v,
sys.x$ktfthc hc
where
a.file# = b.file#
and
a.file# = hc.ktfthctfno
and
hc.ktfthctsn = c.ts#
and
v.fntyp = 7
and
v.fnnam is not null
and
v.fnfno = hc.ktfthctfno
and
hc.ktfthctsn = c.ts#
order by
3 desc;
< io_overview.sql
select
name,
value
from
sys.v_$sysstat
where
name in
('consistent changes',
'consistent gets',
'db block changes',
'db block gets',
'physical reads',
'physical writes',
'sorts (disk)',
'user commits',
'user rollbacks'
)
order by
1;
< v_dollar_phys_reads_ratio.sql
select
100 -
100 *
(round ((sum (decode (name, 'physical reads',
value, 0))
-
sum (decode (name, 'physical reads direct',
value, 0)) -
sum (decode (name,
'physical reads direct (lob)',
value, 0))) /
(sum (decode (name, 'session logical reads',
value, 1))
),3)) hit_ratio
from
sys.v_$sysstat
where
name in
('session logical reads',
'physical reads direct (lob)',
'physical reads',
'physical reads direct');
< fileio.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
rem
rem NAME: fileio.sql
rem
rem FUNCTION: Reports on the file io status of all of the
rem FUNCTION: datafiles in the database.
rem
column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 999.999 heading 'Percent|Of IO'
column brratio format 999.99 heading 'Block|Read|Ratio'
column bwratio format 999.99 heading 'Block|Write|Ratio'
column phyrds heading 'Physical | Reads'
column phywrts heading 'Physical | Writes'
column phyblkrd heading 'Physical|Block|Reads'
column phyblkwrt heading 'Physical|Block|Writes'
column name format a45 heading 'File|Name'
column file# format 9999 heading 'File'
column dt new_value today noprint
select to_char(sysdate,'ddmonyyyyhh24miss') dt from dual;
set feedback off verify off lines 132 pages 60 sqlbl on trims on
rem
select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1
from
sys.v_$filestat a;
select nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
sys.v_$tempstat b;
select &st1+&st2 sum_io from dual;
rem
@title132 'File I/O Statistics Report'
spool rep_out\&db\fileio&&today
select
a.file#,b.name, a.phyrds, a.phywrts,
(100*(a.phyrds+a.phywrts)/÷_by) Percent,
a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
(a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from
sys.v_$filestat a, sys.v_$dbfile b
where
a.file#=b.file#
union
select
c.file#,d.name, c.phyrds, c.phywrts,
(100*(c.phyrds+c.phywrts)/÷_by) Percent,
c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
(c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from
sys.v_$tempstat c, sys.v_$tempfile d
where
c.file#=d.file#
order by
1
/
spool off
pause Press enter to continue
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
< get_io.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set serveroutput on
declare
cursor get_io is select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1,to_number(null) sum_io2
from sys.gv_$filestat a
union
select
to_number(null) sum_io1, nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
sys.gv_$tempstat b;
now date;
elapsed_seconds number;
sum_io1 number;
sum_io2 number;
sum_io12 number;
sum_io22 number;
tot_io number;
tot_io_per_sec number;
fixed_io_per_sec number;
temp_io_per_sec number;
begin
open get_io;
for i in 1..2 loop
fetch get_io into sum_io1, sum_io2;
if i = 1 then sum_io12:=sum_io1;
else
sum_io22:=sum_io2;
end if;
end loop;
select sum_io12+sum_io22 into tot_io from dual;
select sysdate into now from dual;
select ceil((now-max(startup_time))*(60*60*24)) into elapsed_seconds from gv$instance;
fixed_io_per_sec:=sum_io12/elapsed_seconds;
temp_io_per_sec:=sum_io22/elapsed_seconds;
tot_io_per_sec:=tot_io/elapsed_seconds;
dbms_output.put_line('Elapsed Sec :'||to_char(elapsed_seconds, '9,999,999.99'));
dbms_output.put_line('Fixed IO/SEC:'||to_char(fixed_io_per_sec,'9,999,999.99'));
dbms_output.put_line('Temp IO/SEC :'||to_char(temp_io_per_sec, '9,999,999.99'));
dbms_output.put_line('Total IO/SEC:'||to_char(tot_io_Per_Sec, '9,999,999.99'));
end;
/
< wait_report.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
col event format a30 heading 'Event Name'
col waits format 999,999,999 heading 'Total|Waits'
col average_wait format 999,999,999 heading 'Average|Waits'
col time_waited format 999,999,999 heading 'Time Waited'
col total_time new_value divide_by noprint
col value new_value val noprint
col percent format 999.990 heading 'Percent|Of|Non-Idle Waits'
col duration new_value millisec noprint
col p_of_total heading 'Percent|of Total|Uptime' format 999.9999
set lines 132 feedback off verify off pages 50
select to_number(sysdate-startup_time)*86400*1000 duration from v$instance;
select
sum(time_waited) total_time
from v$system_event
where total_waits-total_timeouts>0
and event not like 'SQL*Net%'
and event not like 'smon%'
and event not like 'pmon%'
and event not like 'rdbms%'
and event not like 'PX%'
and event not like 'sbt%'
and event not in ('gcs remote message','ges remote message','virtual circuit status','dispatcher timer') ;
select value from v$sysstat where name ='CPU used when call started';
@title132 'System Events Percent'
break on report
compute sum of time_waited on report
spool rep_out/&db/sys_events
select name event,
0 waits,
0 average_wait,
value time_waited,
value/(&÷_by+&&val)*100 Percent,
value/&&millisec*100 p_of_total
from v$sysstat
where name ='CPU used when call started'
union
select event,
total_waits-total_timeouts waits,
time_waited/(total_waits-total_timeouts) average_wait,
time_waited,
time_waited/(&÷_by+&&val)*100 Percent,
time_waited/&&millisec*100 P_of_total
from v$system_event
where total_waits-total_timeouts>0
and event not like 'SQL*Net%'
and event not like 'smon%'
and event not like 'pmon%'
and event not like 'rdbms%'
and event not like 'PX%'
and event not like 'sbt%'
and event not in ('gcs remote message','ges remote message','virtual circuit status','dispatcher timer')
and time_waited>0
order by percent desc
/
spool off
clear columns
ttitle off
clear computes
clear breaks
col instname format a08 heading 'inst|name'
col dbname format a08 heading 'db name'
col group_name format a08 heading 'disk|group|name'
col disk_number format 9999 heading 'asm|disk|#'
col reads format 99999999 heading 'disk|reads'
col read_time format 999999 heading 'read|time|(s)'
col read_errs format 999999 heading 'read|errors'
col mb_read format 999999.9 heading 'bytes|read|(mb)'
col writes format 99999999 heading 'disk|writes'
col write_errs format 999999 heading 'write|errors'
col write_time format 999999 heading 'write|time|(s)'
col mb_wrtn format 999999.9 heading 'bytes|written|(mb)'
ttitle 'ASM disk I/O statistics'
select
a.dbname,
a.instname,
b.name group_name,
a.disk_number,
a.reads,
a.read_errs,
a.read_time,
( a.bytes_read / (1024*1024)) mb_read,
a.writes,
a.write_errs,
a.write_time,
( a.bytes_written / (1024*1024)) mb_wrtn,
-- **************************
-- new data columns in 11gr2
-- **************************
(a.hot_bytes_read / (1024*1024)) hot_mb_read,
(a.cold_bytes_read / (1024*1024)) cold_mb_read,
(a.hot_bytes_written / (1024*1024)) hot_mb_wrtn,
(a.cold_bytes_written / (1024*1024)) cold_mb_wrtn
from
v$asm_disk_iostat a,
v$asm_diskgroup b
where
a.group_number = b.group_number
order by
a.dbname,
a.instname,
adg.name;
select
event,
wait_time_milli,
wait_count
from
v$event_histogram
where
event in
(select
name
from
v$event_name
where
wait_class not in ('Idle')
)
order by 1,2;
-- Sequential read wait times
select
wait_time_milli,
wait_count
from
v$event_histogram
where
event = 'db file sequential read'
order by
wait_time_milli;
-- Scattered read wait times (full scans)
select
wait_time_milli,
wait_count
from
v$event_histogram
where
event = 'db file scattered read'
order by
wait_time_milli;
< awr_waits_over_time.sql
col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select
sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
(
sum(a.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c3,
(
sum(b.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c4,
(
sum(b.time_waited_micro) /
sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)
) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where
a.snap_id = b.snap_id
and
a.event_name = 'db file scattered read'
and
b.event_name = 'db file sequential read';
< toptables.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
table_owner "table owner",
table_name "table name",
command "command issued",
0 - executions "executions",
disk_reads "disk reads",
gets "buffer gets",
rows_processed "rows processed"
from
(select
distinct executions,
command,
table_owner,
table_name,
gets,
rows_processed,
disk_reads
from
(select
decode (a.command_type ,
2, 'insert ' ,
3,'select ',
6, 'update ' ,
7, 'delete ' ,
26,'table lock ') command ,
c.owner table_owner,
c.name table_name ,
sum(a.disk_reads) disk_reads ,
sum(0 - a.executions) executions ,
sum(a.buffer_gets) gets ,
sum(a.rows_processed) rows_processed
from
sys.v_$sql a ,
sys.v_$object_dependency b ,
sys.v_$db_object_cache c
where
a.command_type in (2,3,6,7,26)and
b.from_address = a.address and
b.to_owner = c.owner and
b.to_name= c.name and
c.type = 'table' and
c.owner not in ('SYS','SYSTEM')
group by
a.command_type , c.owner , c.name ) )
where
rownum <= 100;
< sql_to_tables.sql
select
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type IN ('TABLE', 'TABLE PARTITION')
and
a.operation LIKE '%TABLE%'
and
a.options = 'FULL'
and
a.hash_value = c.hash_value
and
b.bytes / 1024 > 1024
group by
a.object_owner,
a.object_name,
a.operation,
b.bytes / 1024,
b.segment_type
order by
4 desc, 1, 2 );
select
name,
block_size,
(1-(physical_reads/ decode(db_block_gets+consistent_gets, 0, .001, db_block_gets+consistent_gets)))*100 cache_hit_ratio
from
v$buffer_pool_statistics;
< snapfileio_awr.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
rem FUNCTION: Reports on the file io status of all of the
rem FUNCTION: datafiles in the database for a single snapshot.
column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 999.999 heading 'Percent|Of IO'
column brratio format 999.99 heading 'Block|Read|Ratio'
column bwratio format 999.99 heading 'Block|Write|Ratio'
column phyrds heading 'Physical | Reads'
column phywrts heading 'Physical | Writes'
column phyblkrd heading 'Physical|Block|Reads'
column phyblkwrt heading 'Physical|Block|Writes'
column filename format a45 heading 'File|Name'
column file# format 9999 heading 'File'
set feedback off verify off lines 132 pages 60 sqlbl on trims on
select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1
from
dba_hist_filestatxs a where snap_id=&&snap;
select nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
dba_hist_tempstatxs b where snap_id=&&snap;
select &st1+&st2 sum_io from dual;
rem
@title132 'Snap&&snap File I/O Statistics Report'
spool rep_out\&db\fileio&&snap
select
a.filename, a.phyrds, a.phywrts,
(100*(a.phyrds+a.phywrts)/÷_by) Percent,
a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
(a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from
dba_hist_filestatxs a
where
a.snap_id=&&snap
union
select
c.filename, c.phyrds, c.phywrts,
(100*(c.phyrds+c.phywrts)/÷_by) Percent,
c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
(c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from
dba_hist_tempstatxs c
where
c.snap_id=&&snap
order by
1
/
spool off
pause Press enter to continue
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
undef snap
< snapdeltafileio_awr.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
rem FUNCTION: Reports on the file io status of all of
rem FUNCTION: the datafiles in the database across
rem FUNCTION: two snapshots.
column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 999.999 heading 'Percent|Of IO'
column brratio format 999.99 heading 'Block|Read|Ratio'
column bwratio format 999.99 heading 'Block|Write|Ratio'
column phyrds heading 'Physical | Reads'
column phywrts heading 'Physical | Writes'
column phyblkrd heading 'Physical|Block|Reads'
column phyblkwrt heading 'Physical|Block|Writes'
column filename format a45 heading 'File|Name'
column file# format 9999 heading 'File'
set feedback off verify off lines 132 pages 60 sqlbl on trims on
select
nvl(sum((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts)),0) sum_io1
from
dba_hist_filestatxs a, dba_hist_filestatxs b
where
a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id
and a.filename=b.filename;
select
nvl(sum((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts)),0) sum_io2
from
dba_hist_tempstatxs a, dba_hist_tempstatxs b
where
a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id
and a.filename=b.filename;
select &st1+&st2 sum_io from dual;
rem
@title132 'Snap &&first_snap_id to &&sec_snap_id File I/O Statistics Report'
spool rep_out\&db\fileio'&&first_snap_id'_to_'&&sec_snap_id'
select
a.filename, b.phyrds -a.phyrds phyrds, b.phywrts-a.phywrts phywrts,
(100*((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts))/÷_by) Percent,
b.phyblkrd- a.phyblkrd phyblkrd, b.phyblkwrt-a.phyblkwrt phyblgwrt,
((b.phyblkrd-a.phyblkrd)/greatest((b.phyrds-a.phyrds),1)) brratio,
((b.phyblkwrt-a.phyblkwrt)/greatest((b.phywrts-a.phywrts),1)) bwratio
from
dba_hist_filestatxs a, dba_hist_filestatxs b
where
a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id
and a.filename=b.filename
union
select
c.filename, d.phyrds-c.phyrds phyrds, d.phywrts-c.phywrts phywrts,
(100*((d.phyrds-c.phyrds)+(d.phywrts-c.phywrts))/÷_by) Percent,
d.phyblkrd-c.phyblkrd phyblkrd, d.phyblkwrt-c.phyblkwrt phyblgwrt,
((d.phyblkrd-c.phyblkrd)/greatest((d.phyrds-c.phyrds),1)) brratio,
((d.phyblkwrt-c.phyblkwrt)/greatest((d.phywrts-c.phywrts),1)) bwratio
from
dba_hist_tempstatxs c, dba_hist_tempstatxs d
where
c.snap_id=&&first_snap_id and d.snap_id=&&sec_snap_id
and c.filename=d.filename
order by
1
/
spool off
pause Press enter to continue
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
undef first_snap_id
undef sec_snap_id;
< avg_stats_doy.sql
prompt Copyright 2004 by Donald K. Burleson
prompt
prompt
prompt This will query the dba_hist_sysstat view to
prompt display average values by hour of the day
prompt
set pages 999
break on snap_time skip 2
accept stat_name char prompt 'Enter Statistics Name: ';
col snap_time format a19
col avg_value format 999,999,999
select
decode(snap_time1,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday') snap_time,
avg_value
from (
select
to_char(begin_interval_time,'d') snap_time1,
avg(value) avg_value
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = 'physical reads'
group by
to_char(begin_interval_time,'d')
order by
to_char(begin_interval_time,'d')
);
Chapter 14
< display_pga_size.sql
set pages 999;
column pga_size format 999,999,999
select
1048576+a.value+b.value pga_size
from
v$parameter a,
v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size';
set pages 999;
column pga_size format 999,999,999
accept hwm number prompt 'Enter high-water mark of connected users:'
select
&hwm*(2048576+a.value+b.value) pga_size
from
v$parameter a,
v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size';
< track_hash_joins.sql
col c1 heading ‘Date’ format a20
col c2 heading ‘Hash|join|Count’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
col c4 heading ‘Disk|Reads’ format 99,999,999
col c5 heading ‘CPU|Time’ format 99,999,999
accept nested_thr char prompt ‘Enter Nested Join Threshold: ‘
ttitle ‘Nested Join Threshold|&nested_thr’
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(*) c2,
sum(st.rows_processed_delta) c3,
sum(st.disk_reads_delta) c4,
sum(st.cpu_time_delta) c5
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
p.operation like ‘%HASH%’
having
count(*) > &hash_thr
group by
begin_interval_time;
select
'alter session set hash_area_size='||trunc(sum(bytes)*1.6)||';'
from
dba_segments
where
segment_name = upper('&1');
spool off;
@run_hash
< ash__view_events.sql
select
ash.event,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from
v$active_session_history ash
where
ash.sample_time between sysdate - 60/2880 and sysdate
group by
ash.event
order by 2;
< ash_session_details.sql
col wait_time format 999,999,999
select
sess.sid,
sess.username,
sum(ash.wait_time + ash.time_waited) wait_time
from
v$active_session_history ash,
v$session sess
where
ash.sample_time > sysdate-1
and
ash.session_id = sess.sid
group by
sess.sid,
sess.username
order by 3;
< ash_display_sql_wait_time.sql
select
ash.user_id,
u.username,
sqla.sql_text,
sum(ash.wait_time + ash.time_waited) wait_time
from
v$active_session_history ash,
v$sqlarea sqla,
dba_users u
where
ash.sample_time > sysdate-1
and
ash.sql_id = sqla.sql_id
and
ash.user_id = u.user_id
group by
ash.user_id,
sqla.sql_text,
u.username
order by 4;
select
obj.object_name,
obj.object_type,
ash.event,
sum(ash.wait_time + ash.time_waited) wait_time
from
v$active_session_history ash,
dba_objects obj
where
ash.sample_time > sysdate -1
and
ash.current_obj# = obj.object_id
group by
obj.object_name,
obj.object_type,
ash.event
order by 4 desc;
select
1 - ((a.value - (b.value))/d.value) "Cache Hit Ratio"
from
v$sysstat a,
v$sysstat b,
v$sysstat d
where
a.name='physical reads'
and
b.name='physical reads direct'
and
d.name='session logical reads';
< rpt_bhr_all.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
new.name buffer_pool_name,
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets)) bhr
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_sga sn
where
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets)) < .90
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
;
< rpt_bhr_all_awr.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(end_interval_time,'yyyy-mm-dd HH24') mydate,
new.name buffer_pool_name,
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets)) bhr
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_snapshot sn
where
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets)) < .90
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
;
< display_buffer_cache_advice.sql
column c1 heading 'Cache Size (m)' format 999,999,999,999
column c2 heading 'Buffers' format 999,999,999
column c3 heading 'Estd Phys|Read Factor' format 999.90
column c4 heading 'Estd Phys| Reads' format 999,999,999
select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
from
v$db_cache_advice
where
name = 'DEFAULT'
and
block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
and
advice_status = 'ON';
< rpt_bhr_awr_hr.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999;
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'HH24') mydate,
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) bhr
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_sga sn
where
new.name in ('DEFAULT','FAKE VIEW')
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.consistent_gets > 0
and
old.consistent_gets > 0
having
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) < 1
group by
to_char(snap_time,'HH24');
set pages 999;
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(end_interval_time,'day') mydate,
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) bhr
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_snapshot sn
where
new.name in ('DEFAULT','FAKE VIEW')
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.consistent_gets > 0
and
old.consistent_gets > 0
having
avg(
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))
) < 1
group by
to_char(end_interval_time,'day')
;
set lines 80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = new.snap_id-1
and
old.namespace = new.namespace
group by
to_char(snap_time,'yyyy-mm-dd HH24');
SELECT
obj object,
dbarfil file#,
dbablk block#,
tch touches
FROM
x$bh
WHERE
tch > 10
ORDER BY
tch desc;
< buf_blocks.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
ttitle 'Contents of Data Buffers'
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
column c0 heading "Owner" format a12
column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a8
column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer" format 999
column c5 heading "Buffer|Pool" format a7
column c6 heading "Block|Size" format 99,999
select
t1.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART'
when object_type = 'INDEX PARTITION' then 'IDX PART'
else object_type end c2,
sum(num_blocks) c3,
(sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
buffer_pool c5,
sum(bytes)/sum(blocks) c6
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
t1.owner,
object_name,
object_type,
buffer_pool
having
sum(num_blocks) > 10
order by
sum(num_blocks) desc;
< all_vbh_status.sql
set pages 50
@title80 'All Buffers Status'
spool rep_out\&&db\all_vbh_status
select
'32k '||status as status,
count(*) as num
from
v$bh
where file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=32768))
group by '32k '||status
union
select
'16k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=16384))
group by '16k '||status
union
select
'8k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=8192))
group by '8k '||status
union
select
'4k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=4096))
group by '4k '||status
union
select
'2k '||status as status,
count(*) as num
from
v$bh
where
file# in(
select file_id
from dba_data_files
where tablespace_name in (
select tablespace_name
from dba_tablespaces
where block_size=2048))
group by '2k '||status
union
select
status,
count(*) as num
from
v$bh
where status='free'
group by status
order by 1
/
spool off
ttitle off
< obj_xtab.sql
-- Crosstab of object and statistic for an owner
-- by Mike Ault www.oracle-script.com
col "Object" format a20
set numwidth 12
set lines 132
set pages 50
@title132 'Object Wait Statistics'
spool rep_out\&&db\obj_stat_xtab
select * from
(
select
DECODE
(GROUPING(a.object_name), 1, 'All Objects', a.object_name)
AS "Object",
sum(case when
a.statistic_name = 'ITL waits'
then
a.value else null end) "ITL Waits",
sum(case when
a.statistic_name = 'buffer busy waits'
then
a.value else null end) "Buffer Busy Waits",
sum(case when
a.statistic_name = 'row lock waits'
then
a.value else null end) "Row Lock Waits",
sum(case when
a.statistic_name = 'physical reads'
then
a.value else null end) "Physical Reads",
sum(case when
a.statistic_name = 'logical reads'
then
a.value else null end) "Logical Reads"
from
v$segment_statistics a
where
a.owner like upper('&owner')
group by
rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0)
/
spool off
clear columns
ttitle off
< resize_sga.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
# *********************************************************
#
# This will reduce the size of the data buffer
# immediately preceding a large truncate or data purge
#
# *********************************************************
$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=10m;
alter system flush buffer_cache;
exit
!
# *********************************************************
# Now we can invoke the specialty task.
# *********************************************************
nohup purge_job.ksh > /tmp/purge.lst 2>&1 &
$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=1500m;
exit
!
-- You MUST connect as SYS to run this script
connect sys/manager;
set lines 80;
set pages 999;
column avg_touches format 999
column myname heading 'Name' format a30
column mytype heading 'Type' format a10
column buffers format 999,999
SELECT
object_type mytype,
object_name myname,
blocks,
COUNT(1) buffers,
AVG(tch) avg_touches
FROM
sys.x$bh a,
dba_objects b,
dba_segments s
WHERE
a.obj = b.object_id
and
b.object_name = s.segment_name
and
b.owner not in ('SYS','SYSTEM')
GROUP BY
object_name,
object_type,
blocks,
obj
HAVING
AVG(tch) > 5
AND
COUNT(1) > 20;
< buf_keep_pool.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
spool off;
< get_keep_pool.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
'alter table '||p.owner||'.'||p.name||' storage (buffer_pool keep);'
from
dba_tables t,
dba_segments s,
dba_hist_sqlstat a,
(select distinct
pl.sql_id,
pl.object_owner owner,
pl.object_name name
from
dba_hist_sql_plan pl
where
pl.operation = 'TABLE ACCESS'
and
pl.options = 'FULL') p
where
a.sql_id = p.sql_id
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
and
t.buffer_pool <> 'KEEP'
having
s.blocks < 50
group by
p.owner, p.name, t.num_rows, s.blocks
UNION
-- ***********************************************************
-- Next, get the index names
-- ***********************************************************
select
'alter index '||owner||'.'||index_name||' storage (buffer_pool keep);'
from
dba_indexes
where
owner||'.'||table_name in
(
select
p.owner||'.'||p.name
from
dba_tables t,
dba_segments s,
dba_hist_sqlstat a,
(select distinct
pl.sql_id,
pl.object_owner owner,
pl.object_name name
from
dba_hist_sql_plan pl
where
pl.operation = 'TABLE ACCESS'
and
pl.options = 'FULL') p
where
a.sql_id = p.sql_id
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
and
t.buffer_pool <> 'KEEP'
having
s.blocks < 50
group by
p.owner, p.name, t.num_rows, s.blocks
)
< keep_syn.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
< size_keep_pool.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=mon1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/manager<<!
spool myfile.sql
set heading off
spool run.sql
select
' alter system set db_keep_cache_size = '||trunc(sum(s.bytes)*1.5)||' scope=both;'
from
dba_segments s
where
s.buffer_pool = 'KEEP';
spool off;
exit
!
@run.sql
< flash_cache_waits.sql
select
name,
value
from
v$sysstat
where
name in
('physical read flash cache hits',
'physical reads',
'consistent gets',
'db block gets',
'flash cache inserts');
select
owner||'.'||object_name
from
v$bh, dba_objects
where
v$bh.status like ('flash%');
< gen_recycle_syntax.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999;
set heading off;
set feedback off;
ttitle off;
spool keep_syntax.sql
-- ***********************************************************
-- First, get the table list
-- ***********************************************************
select
'alter table '||p.owner||'.'||p.name||' storage (buffer_pool recycle);'
from
dba_tables t,
dba_segments s,
v$sqlarea a,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
a.address = p.address
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
and
t.buffer_pool <> 'RECYCLE'
having
s.blocks > 10000
group by
p.owner, p.name, t.num_rows, s.blocks
UNION
-- ***********************************************************
-- Next, get the index names
-- ***********************************************************
select
'alter index '||owner||'.'||index_name||' storage (buffer_pool recycle);'
from
dba_indexes
where
owner||'.'||table_name in
(
select
p.owner||'.'||p.name
from
dba_tables t,
dba_segments s,
v$sqlarea a,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
a.address = p.address
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
and
t.buffer_pool <> 'RECYCLE'
having
s.blocks > 10000
group by
p.owner, p.name, t.num_rows, s.blocks
)
;
spool off;
set lines 80;
set pages 999;
column avg_touches format 999
column myname heading 'Name' format a30
column mytype heading 'Type' format a10
column buffers format 999,999
select
object_type mytype,
object_name myname,
blocks,
COUNT(1) buffers,
100*(COUNT(1)/totsize) pct_cache
from
sys.x$bh a,
dba_objects b,
dba_segments s,
(select value totsize from v$parameter
where name = 'db_cache_size')
where
a.obj = b.object_id
and
tch=1 -- This line only works in 8.1.6 and above
and
b.object_name = s.segment_name
and
b.owner not in ('SYS','SYSTEM')
group by
object_type,
object_name,
blocks,
totsize
having
100*(COUNT(1)/totsize) > 5
;
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
;
set lines 80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where
new.snap_id = sn.snap_id
and old.snap_id = new.snap_id-1
and old.namespace = new.namespace
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
set lines 80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
from
dba_hist_librarycache old,
dba_hist_librarycache new,
dba_hist_snapshot sn
where
new.snap_id = sn.snap_id
and old.snap_id = new.snap_id-1
and old.namespace = new.namespace
group by
to_char(sn.end_interval_time,'yyyy-mm-dd HH24')
;
set lines 100
set pages 999
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
shared_pool_size_factor c2,
estd_lc_size c3,
estd_lc_memory_objects c4,
estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,
estd_lc_memory_object_hits c7
FROM
v$shared_pool_advice;
< pga_program_used_ram.sql
col c1 heading 'Program|Name' format a30
col c2 heading 'PGA|Used|Memory' format 999,999,999
col c3 heading 'PGA|Allocated|Memory' format 999,999,999
col c4 heading 'PGA|Maximum|Memory' format 999,999,999
select
program c1,
pga_used_mem c2,
pga_alloc_mem c3,
pga_max_mem c4
from
v$process
order by
c4 desc;
column name format a40
column value format 999,999,999
select
name,
value
from
v$pgastat
order by
value desc;
< pga_workareas.sql
col c1 heading 'Workarea|Profile' format a35
col c2 heading 'Count' format 999,999,999
col c3 heading 'Percentage' format 99
select
name c1,
cnt c2,
decode(total, 0, 0, round(cnt*100/total)) c3
from
(
select name,value cnt,(sum(value) over ()) total
from
v$sysstat
where
name like 'workarea exec%'
);
< show_pga_ram_details.sql
select
to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(WORK_AREA_SIZE/1024) WSIZE,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
number_passes PASS
from
v$sql_workarea_active
order by 1,2;
< track_hash_joins.sql
select
to_char(
sn.begin_interval_time,
'yy-mm-dd hh24'
) snap_time,
count(*) ct,
sum(st.rows_processed_delta) row_ct,
sum(st.disk_reads_delta) disk,
sum(st.cpu_time_delta) cpu
from
dba_hist_snapshot sn,
dba_hist_sqlstat st,
dba_hist_sql_plan sp
where
st.snap_id = sn.snap_id
and
st.dbid = sn.dbid
and
st.instance_number = sn.instance_number
and
sp.sql_id = st.sql_id
and
sp.dbid = st.dbid
and
sp.plan_hash_value = st.plan_hash_value
and
sp.operation = 'HASH JOIN'
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
having
count(*) > &hash_thr;
< show_ram_plan.sql
select
operation,
options,
object_name name,
trunc(bytes/1024/1024) "input(MB)",
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) opt_mem,
trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_exections) "O/1/M"
from
v$sql_plan p,
v$sql_workarea w
where
p.address=w.address(+)
and
p.hash_value=w.hash_value(+)
and
p.id=w.operation_id(+)
and
p.address='88BB460C';
select *
from
v$system_event
where
event like ‘%wait%’;
select
p1 "File #".
p2 "Block #",
p3 "Reason Code"
from
v$session_wait
where
event = 'buffer busy waits';
select
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = &P1
and
&P2 between block_id and block_id + blocks -1;
select
object_name,
statistic_name,
value
from
V$SEGMENT_STATISTICS
where
object_name = 'SOURCE$';
select
h.p3 "Reason Code",
h.time_waited "Time Waited",
o.object_name "Object",
f.file_name "Datafile",
h.current_block# "Block Waited"
from
v$active_session_history h,
dba_objects o,
dba_data_files f
where
h.event = 'buffer busy waits' and
h.current_obj# = o.object_id and
h.current_file# = f.file_id and
h.session_state = 'WAITING';
Chapter 15
< block_count.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
Set serveroutput on
DECLARE
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
BEGIN
dbms_space.space_usage ('SYSTEM', 'TEST', 'TABLE', v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
The script yields the following output:
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 9
Where:
FS1 means 0-25% free space within a block
FS2 means 25-50% free space within a block
FS3 means 50-75% free space within a block
FS4 means 75-100% free space within a block
< tsfrag.sql
select
tablespace_name,
count(*) free_chunks,
decode(round((max(bytes) / 1024000),2),null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)) )),2),0)
fragmentation _index
from
sys.dba_free_space
group by
tablespace_name
order by
2 desc, 1;
< dffrag.sql
select
b.file_name, b.tablespace_name,
nvl(round(sqrt(max(a.blocks)/
sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)) )),2),0)
fragmentation_index,
decode(c.inc,null,'no','yes') autoextend ,
count (*) free_chunks,
decode (
round ((max (a.bytes) / 1024000), 2),
null, 0,
round ((max (a.bytes) / 1024000), 2)) largest_chunk
from
sys.dba_free_space a,
sys.dba_data_files b,
sys.filext$ c
where
b.tablespace_name = a.tablespace_name (+) and
c.file# (+)= a.file_id and
b.file_id = a.file_id (+)
group
by b.file_name,
decode(c.inc,null,'no','yes'),
b.tablespace_name
order
by 5 desc, 1;
< tsmap.sql
select
'free space' object_owner,
' ' object_type,
' ' object_name,
file_id,
block_id,
bytes / 1024 size_kb,
blocks
from
sys.dba_free_space
where
tablespace_name = '&tablespacename'
union all
select
owner,
segment_type,
decode (partition_name,null,segment_name,segment_name ||
'.' || partition_name),
file_id,
block_id,
bytes / 1024,
blocks
from
sys.dba_extents
where
tablespace_name = '&tablespacename'
order by
4,5;
Chapter 16
< pctused.sql
set heading off;
set pages 9999;
set feedback off;
spool pctused.lst;
define spare_rows = 2;
define blksz = 8192;
select
‘ alter table ‘||owner||’.’||table_name||
‘ pctused ‘||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||
‘ ‘||
‘ pctfree ‘||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
‘;’
from
dba_tables
where
avg_row_len > 1
and
avg_row_len < 2000
and
table_name not in
(select table_name from dba_tab_columns b
where
data_type in (‘RAW’,’LONG RAW’, ‘CLOB’, ‘BLOB’, ‘XMLTYPE’)
)
order by owner, table_name
;
spool off;
column table_fetch_continued_row format 999,999,999
select
to_char(snap_time,'yyyy-mm-dd hh24'),
avg(newmem.value-oldmem.value)table_fetch_continued_row
from
perfstat.stats$sysstat oldmem,
perfstat.stats$sysstat newmem,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
newmem.snap_id = sn.snap_id
and
oldmem.snap_id = sn.snap_id-1
and
oldmem.name = 'table fetch continued row'
and
newmem.name = 'table fetch continued row'
and
newmem.value-oldmem.value > 0
having
avg(newmem.value-oldmem.value) > 10000
group by
to_char(snap_time,'yyyy-mm-dd hh24');
< chained_row.sql
-- ********************************************
-- This script relies on current CBO statistics
-- ********************************************
spool chain.lst;
set pages 9999;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows" format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct" format .99;
set heading off;
select 'Tables with migrated/chained rows and no BLOB columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW', 'BLOB', 'CLOB')
)
and
chain_cnt > 0
order by chain_cnt desc
;
< awr_list_seg_block_space.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
drop type BlckFreeSpaceSet;
drop type BlckFreeSpace;
create type BlckFreeSpace as object
(
seg_owner varchar2(30),
seg_type varchar2(30),
seg_name varchar2(100),
fs1 number,
fs2 number,
fs3 number,
fs4 number,
fb number
);
create type BlckFreeSpaceSet as table of BlckFreeSpace;
create or replace function BlckFreeSpaceFunc (seg_owner IN varchar2, seg_type in varchar2 default null) return BlckFreeSpaceSet
pipelined
is
outRec BlckFreeSpace := BlckFreeSpace(null,null,null,null,null,null,null,null);
fs1_b number;
fs2_b number;
fs3_b number;
fs4_b number;
fs1_bl number;
fs2_bl number;
fs3_bl number;
fs4_bl number;
fulb number;
fulbl number;
u_b number;
u_bl number;
begin
for rec in (select s.owner,s.segment_name,s.segment_type from dba_segments s where owner = seg_owner and segment_type = nvl(seg_type,segment_type) )
loop
dbms_space.space_usage (
segment_owner => rec.owner,
segment_name => rec.segment_name,
segment_type => rec.segment_type,
fs1_bytes => fs1_b,
fs1_blocks => fs1_bl,
fs2_bytes => fs2_b,
fs2_blocks => fs2_bl,
fs3_bytes => fs3_b,
fs3_blocks => fs3_bl,
fs4_bytes => fs4_b,
fs4_blocks => fs4_bl,
full_bytes => fulb,
full_blocks => fulbl,
unformatted_blocks => u_bl,
unformatted_bytes => u_b
);
outRec.seg_owner := rec.owner;
outRec.seg_type := rec.segment_type;
outRec.seg_name := rec.segment_name;
outRec.fs1 := fs1_bl;
outRec.fs2 := fs2_bl;
outRec.fs3 := fs3_bl;
outRec.fs4 := fs4_bl;
outRec.fb := fulbl;
Pipe Row (outRec);
end loop;
return;
end;
/
col seg_owner heading 'Segment|Owner' format a10
col seg_type heading 'Segment|Type' format a10
col seg_name heading 'Segment|Name' format a30
col fs1 heading '0-25%|Free Space' format 9,999
col fs2 heading '25-50%|Free Space' format 9,999
col fs3 heading '50-75%|Free Space' format 9,999
col fs4 heading '75-100%|Free Space' format 9,999
col fb heading 'Full|Blocks' format 9,999
accept user_name prompt ‘Enter Segment Owner: ‘
break on seg_owner
select
*
from
Table ( BlckFreeSpaceFunc ('&user_name', 'TABLE' ) )
order by
fs4 desc
;
create cluster
orders_cluster
(
ordor_nbr number,
ordor_date date,
customer_nbr number sort
)
hashkeys 10000
hash is ora_hash(customer_nbr)
size 256;
create table ordor
(
ordor_nbr number,
ordor_date date,
customer_nbr number sort
)
cluster
ordor_cluster
(
customer_nbr, transdate
);
create table
emp_ext
(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
organization external
(
type oracle_loader
default directory testdir
access parameters
(
records delimited by newline
fields terminated by ‘,’
)
location (‘emp_ext.csv’)
)
reject limit 1000;
ttitle 'Employee Salary|Cubic Rollup'
col deptno heading 'Department|Number'
col job heading 'Job|Title'
col num_emps heading 'Number of|Employees' format 9,999
col sum_sal heading 'Total|Salary' format $99,999
select
deptno,
job,
count(*) num_emps,
sum(sal) sum_sal
from
emp_ext
group by
rollup
(
deptno,
job
);
select
bp.name pool_name,
ob.name object,
ob.subname sub_name,
sum(buf_count) buffer_blocks
from
(select set_ds, obj, count(*) buf_count
from x$bh group by set_ds, obj) bh,
obj$ ob,
x$kcbwds ws,
v$buffer_pool bp
where
ob.dataobj# = bh.obj
and
ob.owner# > 0
and
bh.set_ds = ws.addr
and
ws.set_id between bp.lo_setid and bp.hi_setid
group by
bp.name,
ob.name,
ob.subname
order by
bp.name,
ob.name,
ob.subname
;
Chapter 17
col table_bytes format 999,999,999,999
col index_bytes format 999,999,999,999
select
sum(bytes) table_bytes
from
user_segments
where
segment_type = 'TABLE';
select
sum(bytes) index_bytes
from
user_segments
where
segment_type = 'INDEX';
< index_range_scans.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Option’ format a15
col c3 heading ‘Index|Usage|Count’ format 999,999
select
p.object_name c1,
p.options c2,
count(1) c3
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.options like '%RANGE SCAN%'
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
< busy_table_io.sql
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Table|Name’ format a20
col c2 heading ‘Disk|Reads’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
select
*
from (
select
to_char(s.begin_interval_time,'mm-dd hh24') c0,
p.object_name c1,
sum(t.disk_reads_total) c2,
sum(t.rows_processed_total) c3,
DENSE_RANK() OVER (PARTITION BY to_char(s.begin_interval_time,'mm-dd hh24') ORDER BY SUM(t.disk_reads_total) desc) AS rnk
from
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where
p.sql_id = t.sql_id
and
t.snap_id = s.snap_id
and
p.object_type like '%TABLE%'
group by
to_char(s.begin_interval_time,'mm-dd hh24'),
p.object_name
order by
c0 desc, rnk
)
where rnk <= 5
;
< count_table_access.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Object|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
Set heading off;
Set pages 9999;
Spool run_rebuild.sql;
select 'alter index sapr3.'||
index_name||
' rebuild tablespace '||
tablespace_name||';'
from dba_indexes
where owner = ‘SAPR3’;
spool off;
@run_rebuild
The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:
alter index index_name
rebuild
tablespace tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
create table
emp_iot
(
emp_id number,
ename varchar2(20),
sal number(9,2),
deptno number,
constraint
pk_emp_iot_index
primary key
(emp_id)
)
organization
index
tablespace
demo_ts_01
pcthreshold
20
including
ename
;
< find_bif.sql
set lines 2000;
select
sql_text,
disk_reads,
executions,
parse_calls
from
v$sqlarea
where
lower(sql_text) like '% substr%'
or
lower(sql_text) like '% to_char%'
or
lower(sql_text) like '% decode%'
order by
disk_reads desc
;
< awr_sql_index_access.sql
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Index|Range|Scans’ format 999,999
col c3 heading ‘Index|Unique|Scans’ format 999,999
col c4 heading ‘Index|Full|Scans’ format 999,999
select
r.c1 c1,
r.c2 c2,
u.c2 c3,
f.c2 c4
from
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%RANGE%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) r,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%UNIQUE%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) u,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) f
where
r.c1 = u.c1
and
r.c1 = f.c1
;
< awr_count_index_details.sql
col c1 heading ‘Begin|Interval|time’ format a20
col c2 heading ‘Search Columns’ format 999
col c3 heading ‘Invocation|Count’ format 99,999,999
break on c1 skip 2
accept idxname char prompt ‘Enter Index Name: ‘
ttitle ‘Invocation Counts for index|&idxname’
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
p.search_columns c2,
count(*) c3
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_name = ‘&idxname'
group by
begin_interval_time,search_columns;
< index_range_scans.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Option’ format a15
col c3 heading ‘Index|Usage|Count’ format 999,999
select
p.object_name c1,
p.options c2,
count(1) c3
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.options like '%RANGE SCAN%'
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
< busy_table_io.sql
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Table|Name’ format a20
col c2 heading ‘Disk|Reads’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
select
*
from (
select
to_char(s.begin_interval_time,'mm-dd hh24') c0,
p.object_name c1,
sum(t.disk_reads_total) c2,
sum(t.rows_processed_total) c3,
DENSE_RANK() OVER (PARTITION BY to_char(s.begin_interval_time,'mm-dd hh24') ORDER BY SUM(t.disk_reads_total) desc) AS rnk
from
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where
p.sql_id = t.sql_id
and
t.snap_id = s.snap_id
and
p.object_type like '%TABLE%'
group by
to_char(s.begin_interval_time,'mm-dd hh24'),
p.object_name
order by
c0 desc, rnk
)
where rnk <= 5
;
< count_table_access.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Object|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
< awr_sql_index_freq.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Index|Usage|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
< awr_access_counts.sql
ttile ‘Table Access|Operation Counts|Per Snapshot Period’
col c1 heading ‘Begin|Interval|time’ format a20
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Object|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24'),
p.operation,
p.options
order by
1,2,3;
< awr_sql_index_access.sql
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Index|Range|Scans’ format 999,999
col c3 heading ‘Index|Unique|Scans’ format 999,999
col c4 heading ‘Index|Full|Scans’ format 999,999
select
r.c1 c1,
r.c2 c2,
u.c2 c3,
f.c2 c4
from
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%RANGE%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) r,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%UNIQUE%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) u,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and
p.options like '%FULL%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) f
where
r.c1 = u.c1
and
r.c1 = f.c1
;
< awr_sql_full_scans.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Index|Table|Scans’ format 999,999
col c3 heading ‘Full|Table|Scans’ format 999,999
select
i.c1 c1,
i.c2 c2,
f.c2 c3
from
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%TABLE ACCESS%'
and
p.options like '%INDEX%'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) i,
(
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(1) c2
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and
p.operation like '%TABLE ACCESS%'
and
p.options = 'FULL'
and
p.sql_id = s.sql_id
and
s.snap_id = sn.snap_id
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) f
where
i.c1 = f.c1
;
< find_full_scans.sql
To download this script, please do a web search for “plan9i.sql”
< get_sql.sql
set lines 2000;
select
sql_text,
disk_reads,
executions,
parse_calls
from
v$sqlarea
where
lower(sql_text) like '% page %'
order by
disk_reads desc
;
< get_sub_optimal_cached_sql.sql
set linesize 80 pagesize 80 trimspool on
ttitle "Top 10 Expensive SQL | Consistent Gets per Rows Fetched"
column sql_id heading "SQL ID"
column c2 heading "Avg Gets per Row"
column c3 heading "Total Gets"
column c4 heading "Total Rows"
select
*
from
(select
sq.sql_id,
round(sum(buffer_gets_delta) /
decode(sum(rows_processed_delta), 0, 1,
sum(rows_processed_delta))) c2,
sum(buffer_gets_delta) c3,
sum(rows_processed_delta) c4
from
dba_hist_snapshot sn,
dba_hist_sqlstat sq,
dba_hist_sqltext st
where
sn.snap_id = sq.snap_id
and
sn.dbid = sq.dbid
and
sn.instance_number = sq.instance_number
and
sn.dbid = st.dbid
and
sq.sql_id = st.sql_id
and
lower(sql_text) not like '%sum(%'
and
lower(sql_text) not like '%min(%'
and
lower(sql_text) not like '%max(%'
and
lower(sql_text) not like '%avg(%'
and
lower(sql_text) not like '%count(%'
and
sn.snap_id between &beginsnap and &endsnap
and
sq.parsing_schema_name not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
group by
sq.sql_id
order by
2 desc)
where
rownum < 11
/
< awr_expensive_sql.sql
set linesize 80 pagesize 80 trimspool on
ttitle "Top 10 Expensive SQL | Disk Reads per Rows Fetched"
column sql_id heading "SQL ID"
column c2 heading "Avg Reads per Row"
column c3 heading "Total Reads"
column c4 heading "Total Rows"
select
*
from
(select
sq.sql_id,
round(sum(disk_reads_delta) /
decode(sum(rows_processed_delta),
0,
1,
sum(rows_processed_delta))) c2,
sum(disk_reads_delta) c3,
sum(rows_processed_delta) c4
from
dba_hist_snapshot sn,
dba_hist_sqlstat sq,
dba_hist_sqltext st
where
sn.snap_id = sq.snap_id
and
sn.dbid = sq.dbid
and
sn.instance_number = sq.instance_number
and
sn.dbid = st.dbid and sq.sql_id = st.sql_id
and
lower(sql_text) not like '%sum(%'
and
lower(sql_text) not like '%min(%'
and
lower(sql_text) not like '%max(%'
and
lower(sql_text) not like '%avg(%'
and
lower(sql_text) not like '%count(%'
and
sn.snap_id between &beginsnap and &endsnap
and
sq.parsing_schema_name not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
group by
sq.sql_id
order by
2 desc)
where
rownum < 11
/
< statspack_unused_indexes.sql
ttitle "Unused Indexes by Time Period"
col owner heading "Index Owner" format a30
col index_name heading "Index Name" format a30
set linesize 95 trimspool on pagesize 80
select *
from
(select
owner,
index_name
from
dba_indexes di
where
di.index_type != 'LOB'
and
owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
minus
select
index_owner owner,
index_name
from
dba_constraints dc
where
index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
minus
select
p.object_owner owner,
p.object_name index_name
from
stats$snapshot sn,
stats$sql_plan p,
stats$sql_summary st,
stats$sql_plan_usage spu
where
st.sql_id = spu.sql_id
and
spu.plan_hash_value = p.plan_hash_value
and
st.hash_value = p.plan_hash_value
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
sn.snap_id = spu.snap_id
and
sn.dbid = spu.snap_id
and
sn.instance_number = spu.instance_number
and
sn.snap_id between &begin_snap and &end_snap
and
p.object_type = 'INDEX'
)
where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
order by 1, 2
/
< awr_unused_indexes.sql
ttitle "Unused Indexes by Time Period"
col owner heading "Index Owner" format a30
col index_name heading "Index Name" format a30
set linesize 95 trimspool on pagesize 80
select * from
(select owner, index_name
from dba_indexes di
where
di.index_type != 'LOB'
minus
select index_owner owner, index_name
from dba_constraints dc
minus
select
p.object_owner owner,
p.object_name index_name
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id and sn.dbid = st.dbid and sn.instance_number = st.instance_number
and
sn.snap_id between &begin_snap and &end_snap
and
p.object_type = 'INDEX'
)
where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
order by 1, 2
/
< awr_infrequent_indexes.sql
ttitle "Infrequently-used indexes by month"
col c1 heading "Month" format a20
col c2 heading "Index Owner" format a30
col c3 heading "Index Name" format a30
col c4 heading "Invocation|Count" format 99
set linesize 95 trimspool on pagesize 80
select
to_char(sn.begin_interval_time,'Month') c1,
p.object_owner c2,
p.object_name c3,
sum(executions_delta) c4
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
p.object_type = 'INDEX'
and
p.object_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP')
group by
to_char(sn.begin_interval_time, 'Month'),
p.object_owner,
p.object_name
having
sum(executions_delta) < 50
order by
1, 4 desc, 2, 3
/
< Find_duplicate_index_columns.sql
set linesize 150 trimspool on pagesize 80
column index_owner format a20
column column_name format a30
column position format 9
column nextcol format a18 heading "Next Column Match?"
select
a.index_owner,
a.column_name,
a.index_name index_name1,
b.index_name index_name2,
a.column_position position,
(select
'YES'
from
dba_ind_columns x,
dba_ind_columns y
where
x.index_owner = a.index_owner
and
y.index_owner = b.index_owner
and
x.index_name = a.index_name
and
y.index_name = b.index_name
and
x.column_position = 2
and
y.column_position = 2
and
x.column_name = y.column_name) nextcol
from
dba_ind_columns a,
dba_ind_columns b
where
a.index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP')
and
a.index_owner = b.index_owner
and
a.column_name = b.column_name
and
a.table_name = b.table_name
and
a.index_name != b.index_name
and
a.column_position = 1
and
b.column_position = 1;
< display_composite_index_columns.sql
-- *******************************
-- Display composite indexes
-- *******************************
break on index_name skip 1
col index_name format a30
col column_name format a30
select
index_name,
column_position,
column_name
from
user_ind_columns i
where
(select
count(*)
from
user_ind_columns u
where
u.index_name = i.index_name) > 1
group by
index_name,
column_position,
column_name
order by
index_name,
column_position;
< display_composite_index_overall_usage.sql
-- *********************************
-- Are composite indexes being used?
-- *********************************
select
to_char(sn.begin_interval_time,'yyyy-mm') c1,
p.object_name c2,
p.search_columns c3,
count(*)
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
-- **************************************************
-- Only show data for multi-column composite indexes
-- **************************************************
(select
count(*)
from
user_ind_columns u
where
u.index_name = p.object_name) > 1
and
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_owner = 'PUBS'
and
-- ************************************************
-- This is supposed to just show the last 30 days
-- ************************************************
sysdate > sysdate - 30
group by
begin_interval_time,
object_name,
search_columns;
Once we see if composite indexes are being used, we can drill-down an look at a list of all multi-column indexes.
-- ***********************************************
-- Show frequency of use of multi-column indexes
-- ***********************************************
with
composite_index_name_list
as
(select
i.index_name
from
user_ind_columns i
where
(select
count(*)
from
user_ind_columns u
where
u.index_name = i.index_name) > 1
group by
index_name
order by
index_name)
select
to_char(sn.begin_interval_time,'yyyy-mm') c1,
p.object_name c2,
p.search_columns c3,
count(*)
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st,
composite_index_name_list i
where
-- ****************************************************
-- Only show multi-column indexes from the WITH clause
-- ****************************************************
i.index_name = p.object_name
and
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_owner = 'PUBS'
and
-- ************************************************
-- only for the last 30 days . . .
-- ************************************************
sysdate > sysdate - 30
group by
begin_interval_time,
object_name,
search_columns;
< display_composite_index_columns_invocation.sql
col c1 heading ‘Begin|Interval|time’ format a20
col c2 heading ‘Search Columns’ format 999
col c3 heading ‘Invocation|Count’ format 99,999,999
break on c1 skip 2
accept idxname char prompt ‘Enter Index Name: ‘
ttitle ‘Invocation Counts for index|&idxname’
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
p.search_columns c2,
count(*) c3
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_name = ‘&idxname'
group by
begin_interval_time,search_columns;
< find_sparse_indexes.sql
select
‘exec analyzedb.reorg_a_table4(’||””||rtrim(t.table_owner)||””||’,'||””||
rtrim(t.table_name)||””||’);’,
t.table_owner||’.'||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,’999999.99′) per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from
analyzedb.table_modifications t,
all_tables a
where
t.timestamp >= to_date(’&from_date’,'dd-mon-yyyy’)
and
t.table_owner = a.owner
and
t.table_owner not in (’SYS’,'SYSTEM’)
and
t.table_name=a.table_name
having
(sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by
t.table_owner, t.table_name, a.num_rows
order by
num_rows desc, t.table_owner, t.table_name;
select
sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
(
sum(a.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c3,
(
sum(b.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c4,
(
sum(b.time_waited_micro) /
sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)
) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where
a.snap_id = b.snap_id
and
a.event_name = 'db file scattered read'
and
b.event_name = 'db file sequential read';
Chapter 18
< session.sql script
rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;
ttitle "dbname Database|UNIX/Oracle Sessions";
set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
v$session b,
v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;
< awr_quick.ksh (partial)
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
spool rpt_last.lst
set pages 9999;
set feedback on;
set verify off;
column reads format 999,999,999
column writes format 999,999,999
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24'),
(newreads.value-oldreads.value) reads,
(newwrites.value-oldwrites.value) writes
from
dba_hist_sysstat oldreads,
dba_hist_sysstat newreads,
dba_hist_sysstat oldwrites,
dba_hist_sysstat newwrites,
dba_hist_snapshot sn
where
newreads.snap_id = (select max(sn.snap_id)
from dba_hist_snapshot)
and newwrites.snap_id = (select max(sn.snap_id)
from dba_hist_snapshot)
and oldreads.snap_id = sn.snap_id-1
and oldwrites.snap_id = sn.snap_id-1
and oldreads.stat_name = 'physical reads'
and newreads.stat_name = 'physical reads'
and oldwrites.stat_name = 'physical writes'
and newwrites.stat_name = 'physical writes'
;
prompt ***********************************************************
prompt This will identify any single file who's read I/O
prompt is more than 10% of the total read I/O of the database.
prompt
prompt The "hot" file should be examined, and the hot table/index
prompt should be identified using STATSPACK.
prompt
prompt - The busy file should be placed on a disk device with
prompt "less busy" files to minimize read delay and channel
prompt contention.
prompt
prompt - If small file has a hot small table, place the table
prompt in the KEEP pool
prompt
prompt - If the file has a large-table full-table scan, place
prompt the table in the RECYCLE pool and turn on parallel query
prompt for the table.
prompt ***********************************************************
column mydate format a16
column file_name format a40
column reads format 999,999,999
select
to_char(sn.end_interval_time,'yyyy-mm-dd HH24') mydate,
new.filename file_name,
new.phyrds-old.phyrds reads
from
dba_hist_filestatxs old, dba_hist_filestatxs new, dba_hist_snapshot snwhere sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.filename = old.filename
--and
-- new.phyrds-old.phyrds > 10000
and
(new.phyrds-old.phyrds)*10 >
(
select
(newreads.value-oldreads.value) reads
from
dba_hist_sysstat oldreads,
dba_hist_sysstat newreads,
dba_hist_snapshot sn1
where
sn.snap_id = sn1.snap_id
and newreads.snap_id = sn.snap_id
and oldreads.snap_id = sn.snap_id-1
and oldreads.stat_name = 'physical reads'
and newreads.stat_name = 'physical reads'
and (newreads.value-oldreads.value) > 0)
;
prompt ***********************************************************
prompt This will identify any single file who's write I/O
prompt is more than 10% of the total write I/O of the database.
prompt
prompt The "hot" file should be examined, and the hot table/index
prompt should be identified using STATSPACK.
prompt
prompt - The busy file should be placed on a disk device with
prompt "less busy" files to minimize write delay and channel
prompt channel contention.
prompt
prompt - If small file has a hot small table, place the table
prompt in the KEEP pool
prompt
prompt ***********************************************************
select
100 -
100 *
(round((sum (decode (name, 'physical reads', value, 0)) -
sum (decode (name, 'physical reads direct', value, 0))) /
(sum (decode (name, 'db block gets', value, 1)) +
sum (decode (name, 'consistent gets', value, 0))),3))
from
sys.v_$sysstat
where
name in ('db block gets', 'consistent gets',
'physical reads', 'physical reads direct')
< syswaits.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
event,
total_waits,
round(100 * (total_waits / sum_waits),2) pct_tot_waits,
time_wait_sec,
round(100 * (time_wait_sec / sum_secs),2) pct_secs_waits,
total_timeouts,
avg_wait_sec
from
(select
event,
total_waits,
round((time_waited / 100),2) time_wait_sec,
total_timeouts,
round((average_wait / 100),2) avg_wait_sec
from
sys.v_$system_event
where
event not in
('lock element cleanup ',
'pmon timer ',
'rdbms ipc message ',
'smon timer ',
'SQL*Net message from client ',
'SQL*Net break/reset to client ',
'SQL*Net message to client ',
'SQL*Net more data to client ',
'dispatcher timer ',
'Null event ',
'parallel query dequeue wait ',
'parallel query idle wait - Slaves ',
'pipe get ',
'PL/SQL lock timer ',
'slave wait ',
'virtual circuit status ',
'WMON goes to sleep') and
event not like 'DFS%' and
event not like 'KXFX%'),
(select
sum(total_waits) sum_waits,
sum(round((time_waited / 100),2)) sum_secs
from
sys.v_$system_event
where
event not in
('lock element cleanup ',
'pmon timer ',
'rdbms ipc message ',
'smon timer ',
'SQL*Net message from client ',
'SQL*Net break/reset to client ',
'SQL*Net message to client ',
'SQL*Net more data to client ',
'dispatcher timer ',
'Null event ',
'parallel query dequeue wait ',
'parallel query idle wait - Slaves ',
'pipe get ',
'PL/SQL lock timer ',
'slave wait ',
'virtual circuit status ',
'WMON goes to sleep') and
event not like 'DFS%' and
event not like 'KXFX%')
order by
2 desc;
< sesswaits.sql
select
b.sid,
decode(b.username,NULL,c.name,b.username) process_name,
event,
a.a.total_waits,
round((a.time_waited / 100),2)
time_wait_sec,a.total_timeouts,
round((average_wait / 100),2)
average_wait_sec,
round((a.max_wait / 100),2) max_wait_sec
from
sys.v_$session_event a,
sys.v_$session b,
sys.v_$bgprocess c
where
a.event not in
('lock element cleanup ',
'pmon timer ',
'rdbms ipc message ',
'smon timer ',
'SQL*Net message from client ',
'SQL*Net break/reset to client ',
'SQL*Net message to client ',
'SQL*Net more data to client ',
'dispatcher timer ',
'Null event ',
'parallel query dequeue wait ',
'parallel query idle wait - Slaves ',
'pipe get ',
'PL/SQL lock timer ',
'slave wait ',
'virtual circuit status ',
'WMON goes to sleep'
)
and a.event not like 'DFS%'
and a.event not like 'KXFX%'
and a.sid = b.sid
and b.paddr = c.paddr (+)
order by
4 desc;
< csesswaits.sql
select
a.sid,
decode(b.username,null,c.name,b.username) process_name,
a.event,
a.seconds_in_wait,
a.wait_time,
a.state,
a.p1text,
a.p1,
a.p1raw,
a.p2text,
a.p2,
a.p2raw,
a.p3text,
a.p3,
a.p3raw
from
sys.v_$session_wait a,
sys.v_$session b,
sys.v_$bgprocess c
where
a.event not in
('lock element cleanup ',
'pmon timer ',
'rdbms ipc message ',
'smon timer ',
'SQL*Net message from client ',
'SQL*Net break/reset to client ',
'SQL*Net message to client ',
'SQL*Net more data to client ',
'dispatcher timer ',
'Null event ',
'parallel query dequeue wait ',
'parallel query idle wait - Slaves ',
'pipe get ',
'PL/SQL lock timer ',
'slave wait ',
'virtual circuit status ',
'WMON goes to sleep'
)
and a.event not like 'DFS%'
and a.event not like 'KXFX%'
and a.sid = b.sid
and b.paddr = c.paddr (+)
order by
4 desc;
< objwaits.sql
select
sid,
username,
machine,
program,
b.owner,
b.object_type,
b.object_name,
c.file_name
from sys.v_$session a,
sys.dba_objects b,
sys.dba_data_files c
where a.row_wait_obj# > 0 and
a.row_wait_obj# = b.object_id and
a.row_wait_file# = c.file_id
order by sid;
< spacesum.sql
select
tablespace_name,
autoextend ,
round ((total_space / 1024 / 1024), 2) as
total_space,
round ((total_free_space /
1024 / 1024), 2) as total_free,
round (((total_space - total_free_space) /
1024 / 1024), 2) as used_space,
to_char (
nvl (
round (
(100 *
sum_free_blocks /
sum_alloc_blocks),2),0)) || '%'
as pct_free
from (select
tablespace_name,
max (autoextensible) autoextend,
sum (blocks) sum_alloc_blocks,
sum (bytes) as total_space
from
dba_data_files
group by tablespace_name),
(select
b.tablespace_name fs_ts_name,
nvl (sum (bytes), 0) as total_free_space,
sum (blocks) as sum_free_blocks
from
dba_free_space a, dba_tablespaces b
where
a.tablespace_name (+) = b.tablespace_name
group by b.tablespace_name, status)
where
tablespace_name = fs_ts_name
union all
select
d.tablespace_name, autoextend,
round ((a.bytes / 1024 / 1024), 2),
round ((a.bytes / 1024 / 1024) -
(nvl (t.bytes, 0) / 1024 / 1024), 2),
round (nvl (t.bytes, 0) / 1024 / 1024, 2),
to_char (100 - (nvl (t.bytes /
a.bytes * 100, 0)), '990.00')
from
sys.dba_tablespaces d,
(select
tablespace_name,
max (autoextensible) autoextend,
sum (bytes) bytes
from
dba_temp_files
group by tablespace_name) a,
(select
tablespace_name, sum (bytes_cached) bytes
from
sys.v_$temp_extent_pool
group by tablespace_name) t
where
d.tablespace_name = a.tablespace_name (+)
and d.tablespace_name = t.tablespace_name (+)
and d.extent_management like 'LOCAL'
and d.contents like 'TEMPORARY'
order by 1;
< datafileae.sql
select
b.file_name,
b.tablespace_name,
decode(c.inc,null,'no','yes') autoextend
from
sys.dba_data_files b,
sys.filext$ c
where
c.file# (+)= b.file_id
order by
2, 1;
< maxext.sql
select
owner,
decode(partition_name,NULL,segment_name,segment_name ||
'.' || partition_name) segment_name,
segment_type,
extents,
max_extents,
initial_extent,
next_extent,
tablespace_name
from
sys.dba_segments
where
max_extents - extents <= 5 and
segment_type <> 'CACHE'
order by
1,2,3;
< objdef.sql
select
a.owner,
a.segment_name,
a.segment_type,
a.tablespace_name,
a.next_extent,
max(c.bytes) max_contig_space
from
sys.dba_segments a,
sys.dba_free_space c
where
a.tablespace_name = c.tablespace_name and
a.next_extent >
(select
max(bytes)
from
sys.dba_free_space b
where
a.tablespace_name = b.tablespace_name and
b.tablespace_name = c.tablespace_name)
group by
a.owner,
a.segment_name,
a.tablespace_name,
a.segment_type,
a.next_extent;
< tabreorg.sql
select
/*+ RULE */
owner,
segment_name table_name,
segment_type,
round(bytes/1024,2) table_kb,
num_rows,
blocks,
empty_blocks,
hwm highwater_mark,
avg_used_blocks,
greatest(round(100 * (nvl(hwm - avg_used_blocks,0) /
greatest(nvl(hwm,1),1) ),2),0) block_inefficiency,
chain_pct,
max_extent_pct,
extents,
max_extents,
decode(greatest(max_free_space -
next_extent,0),0,'n','y') can_extend_space,
next_extent,
max_free_space,
o_tablespace_name tablespace_name
from
(select
a.owner owner,
segment_name,
segment_type,
bytes,
num_rows,
a.blocks blocks,
b.empty_blocks empty_blocks,
a.blocks - b.empty_blocks - 1 hwm,
decode(round((b.avg_row_len * num_rows *
(1 + (pct_free/100))) /
c.blocksize,0),0,1,round((b.avg_row_len * num_rows *
(1 + (pct_free/100))) / c.blocksize,0)) + 2
avg_used_blocks,
round(100 * (nvl(b.chain_cnt,0) /
greatest(nvl(b.num_rows,1),1)),2)
chain_pct,
a.extents extents,
round(100 * (a.extents / a.max_extents),2) max_extent_pct,
a.max_extents max_extents,
b.next_extent next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_all_tables b,
sys.ts$ c
where
( a.owner = b.owner ) and
( segment_name = table_name ) and
( ( segment_type = 'TABLE' ) ) and
b.tablespace_name = c.name
union all
select
a.owner owner,
segment_name || '.' || b.partition_name,
segment_type,
bytes,
b.num_rows,
a.blocks blocks,
b.empty_blocks empty_blocks,
a.blocks - b.empty_blocks - 1 hwm,
decode(round((b.avg_row_len * b.num_rows * (1 +
(b.pct_free/100))) /
c.blocksize,0),0,1,round((b.avg_row_len * b.num_rows *
(1 + (b.pct_free/100))) / c.blocksize,0)) + 2
avg_used_blocks,
round(100 * (nvl(b.chain_cnt,0) /
greatest(nvl(b.num_rows,1),1)),2)
chain_pct,
a.extents extents,
round(100 * (a.extents / a.max_extents),2) max_extent_pct,
a.max_extents max_extents,
b.next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_tab_partitions b,
sys.ts$ c,
sys.dba_tables d
where
( a.owner = b.table_owner ) and
( segment_name = b.table_name ) and
( ( segment_type = 'TABLE PARTITION' ) ) and
b.tablespace_name = c.name and
d.owner = b.table_owner and
d.table_name = b.table_name and
a.partition_name = b.partition_name),
( select
tablespace_name f_tablespace_name,
max(bytes) max_free_space
from
sys.dba_free_space
group by tablespace_name)
where
f_tablespace_name = o_tablespace_name and
greatest(round(100 * (nvl(hwm - avg_used_blocks,0) /
greatest(nvl(hwm,1),1) ),2),0) > 25
order by 10 desc, 1 asc,2 asc;
< idxreorg.sql
select
/*+ RULE */
owner,
segment_name index_name,
segment_type,
round(bytes/1024,2) index_kb,
num_rows,
clustering_factor,
blevel,
blocks,
max_extent_pct,
extents,
max_extents,
decode(greatest(max_free_space -
next_extent,0),0,'n','y') can_extend_space,
next_extent,
max_free_space,
o_tablespace_name
from
(select
a.owner owner,
segment_name,
segment_type,
bytes,
num_rows,
b.clustering_factor,
b.blevel,
a.blocks blocks,
a.extents extents,
round(100 * (a.extents / a.max_extents),2)
max_extent_pct,
a.max_extents max_extents,
b.next_extent next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_indexes b,
sys.ts$ c
where
( a.owner = b.owner ) and
( segment_name = index_name ) and
( ( segment_type = 'INDEX' ) ) and
b.tablespace_name = c.name
union all
select
a.owner owner,
segment_name || '.' || b.partition_name,
segment_type,
bytes,
b.num_rows,
b.clustering_factor,
b.blevel,
a.blocks blocks,
a.extents extents,
round(100 * (a.extents / a.max_extents),2)
max_extent_pct,
a.max_extents max_extents,
b.next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_ind_partitions b,
sys.ts$ c,
sys.dba_indexes d
where
( a.owner = b.index_owner ) and
( segment_name = b.index_name ) and
( ( segment_type = 'INDEX PARTITION' ) ) and
b.tablespace_name = c.name and
d.owner = b.index_owner and
d.index_name = b.index_name and
a.partition_name = b.partition_name),
( select
tablespace_name f_tablespace_name,
max(bytes) max_free_space
from
sys.dba_free_space
group by tablespace_name)
where
f_tablespace_name = o_tablespace_name
order
by 1,2;
< sgasize.sql
select
db_size_in_mb - db_caches db_buffers_in_mb,
db_caches db_caches_mb,
fixed_size_in_mb,
lb_size_in_mb,
sp_size_in_mb,
lp_size_in_mb,
jp_size_in_mb
from
(select
round (max(a.bytes) / 1024 / 1024, 2) db_size_in_mb
from
sys.v_$sgastat a
where
(a.name = 'db_block_buffers ' or a.name = 'buffer_cache')),
(select
nvl(round (sum (b.value) / 1024 / 1024, 2),0) db_caches
from
sys.v_$parameter b
where
b.name like '%k_cache_size'),
(select
round (sum (b.bytes) / 1024 / 1024, 2) fixed_size_in_mb
from
sys.v_$sgastat b
where
b.name = 'fixed_sga'),
(select
round (sum (c.bytes) / 1024 / 1024, 2) lb_size_in_mb
from
sys.v_$sgastat c
where
c.name= 'log_buffer ' ),
(select
round (sum (d.value) / 1024 / 1024, 2) sp_size_in_mb
from
sys.v_$parameter d
where
d.name = 'shared_pool_size '),
(select
round (sum (e.value) / 1024 / 1024, 2) lp_size_in_mb
from
sys.v_$parameter e
where
e.name = 'large_pool_size ' ),
(select
round (sum (f.value) / 1024 / 1024, 2) jp_size_in_mb
from
sys.v_$parameter f
where
f.name = 'java_pool_size ');
< memsnap.sql
select
buffer_hit_ratio,
percent_shared_pool_free,
lib_cache_hit_ratio,
object_reloads,
dd_cache_hit_ratio,
redo_log_space_waits,
redo_log_space_wait_time,
mem_sort_ratio,
parse_execute_ratio,
buffer_busy_waits,
latch_miss_ratio
from
(select
100 -
100 *
(round ((sum (decode (name, 'physical reads', value, 0)) -
sum (decode (name, 'physical reads direct', value, 0)) -
sum (decode (name,
'physical reads direct (lob)', value, 0))) /
(sum (decode (name,
'session logical reads', value, 1))),3)) buffer_hit_ratio
from
sys.v_$sysstat
where
name in ('session logical reads',
'physical reads direct (lob)',
'physical reads', 'physical reads direct')),
(select
round (100 * (free_bytes / shared_pool_size ), 2)
percent_shared_pool_free
from
(select
sum (bytes) free_bytes
from
sys.v_$sgastat
where
name = 'free memory'
and
pool = 'shared pool '),
(select
value shared_pool_size
from
sys.v_$parameter
where
name = 'shared_pool_size')),
(select
100 - round ((sum (reloads) /
sum (pins)) * 100, 2) lib_cache_hit_ratio
from
sys.v_$librarycache),
(select
100 - round ((sum (getmisses) /
(sum (gets) + sum (getmisses)) * 100), 2) dd_cache_hit_ratio
from sys.v_$rowcache),
(select round (
(100 * b.value) /
decode ((a.value + b.value), 0, 1, (a.value + b.value)),
2)mem_sort_ratio
from
v$sysstat a,
v$sysstat b
where
a.name = 'sorts (disk)'
and b.name = 'sorts (memory)'),
(select
round(100 * (sum (sys.v_$latch.misses) /
sum (sys.v_$latch.gets)),2) latch_miss_ratio
from
sys.v_$latch),
(select
round (100 * (a.value - b.value) /
decode (a.value, 0, 1, a.value), 2) parse_execute_ratio
from
sys.v_$sysstat a,
sys.v_$sysstat b
where
a.name = 'execute count'
and b.name = 'parse count (hard)'),
(select
nvl(sum(total_waits),0) buffer_busy_waits
from
sys.v_$system_event a,
sys.v_$event_name b
where
a.event = 'buffer busy waits' and
a.event (+) = b.name),
(select
sum(reloads) object_reloads
from
sys.v_$librarycache),
(select
value redo_log_space_waits
from
sys.v_$sysstat
where
name = 'redo log space requests'),
(select
value redo_log_space_wait_time
from
sys.v_$sysstat
where
name = 'redo log space wait time');
< poolhit.sql
select
name,
100 * (1 - (physical_reads / (db_block_gets +
consistent_gets))) hit_ratio
from
sys.v$buffer_pool_statistics
where
db_block_gets + consistent_gets > 0;
< sesshitrate.sql
select
b.sid sid,
decode (b.username,null,e.name,b.username)
user_name,
d.spid os_id,
b.machine machine_name,
to_char(logon_time,'mm/dd/yy hh:mi:ss pm')
logon_time,
100 - 100 *
(round ((sum (decode (c.name,
'physical reads', value, 0)) -
sum (decode (c.name,
'physical reads direct', value, 0)) –
sum(decode (c.name,
'physical reads direct (lob)', value, 0))) /
(sum (decode (c.name,
'db block gets', value, 1)) +
sum (decode (c.name,
'consistent gets', value, 0))),3)) hit_ratio
from
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$statname c,
sys.v_$process d,
sys.v_$bgprocess e
where
a.statistic#=c.statistic# and
b.sid=a.sid and
d.addr = b.paddr and
e.paddr (+) = b.paddr and
c.name in ('physical reads',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)',
'db block gets',
'consistent gets')
group by
b.sid,
d.spid,
decode (b.username,null,e.name,b.username),
b.machine,
to_char(logon_time,'mm/dd/yy hh:mi:ss pm')
order by
6 desc;
< sqlhitrate.sql
select
sql_text ,
b.username ,
100 - round(100 *
a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM')
order by
3 desc;
< cacheobjcnt.sql
select
decode(cachehint, 0, 'default', 1,
'keep', 2, 'recycle', null) cache,
count(*) objects
from
sys.seg$ s
where
s.user# in
(select
user#
from
sys.user$
where
name not in ('sys','system'))
group by
decode(cachehint, 0, 'default', 1,
'keep', 2, 'recycle', null)
order by
1;
< buffutl.sql
select
'free' buffer_state,
nvl(sum(blocksize) / 1024 ,0) amt_kb
from
sys.x$bh a,
sys.ts$ b
where
state = 0 and
a.ts# = b.ts#
union all
select
'read/mod' buffer_state,
nvl(sum(blocksize) / 1024 ,0) amt_kb
from
sys.x$bh a,
sys.ts$ b
where
state = 1 and
a.ts# = b.ts#
union all
select
'read/notmod',
nvl(sum(blocksize) / 1024 ,0) amt_kb
from
sys.x$bh a,
sys.ts$ b
where
state = 2 and
a.ts# = b.ts#
union all
select
'being read' buffer_state,
nvl(sum(blocksize) / 1024 ,0) amt_kb
from
sys.x$bh a,
sys.ts$ b
where
state = 3 and
a.ts# = b.ts#
order by
1;
< libdet.sql
select
namespace,
gets,
round(gethitratio*100,2) gethitratio,
pins,
round(pinhitratio*100,2) pinhitratio,
reloads,
invalidations
from
sys.v_$librarycache
order by
1;
< libwait.sql
select
b.name,
nvl(max(a.total_waits),0)
from
sys.v_$system_event a,
sys.v_$event_name b
where
a.event (+) = b.name and
b.name in ('latch free ','library cache load lock',
'library cache lock','library cache pin')
group by
b.name
< libobj.sql
select
owner,
name,
type,
sharable_mem,
loads,
executions,
locks,
pins,
kept
from
sys.v_$db_object_cache
order by
type asc;
< dictdet.sql
select
parameter,
usage,
gets,
getmisses,
100 - round((getmisses/
(gets + getmisses) * 100),2) hit_ratio
from
sys.v_$rowcache
where
gets + getmisses <> 0
order by
5 desc;
< memhog.sql
select
sid,
username,
round(total_user_mem/1024,2) mem_used_in_kb,
round(100 * total_user_mem/total_mem,2) mem_percent
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_mem
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('session pga memory','session uga memory')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_mem
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('session pga memory','session uga memory'))
order by
3 desc;
< bufobjwaits.sql
select
owner,
object_name,
object_type,
value waits
from
sys.v_$segment_statistics
where
(statistic_name = 'buffer busy waits' and value > 0)
order by
1,2;
< latchdet.sql
select
name,
gets,
round(misses*100/decode(gets,0,1,gets),2) misses,
round(spin_gets*100/decode(misses,0,1,misses),2) spins,
immediate_gets igets,
round(immediate_misses*100/
decode(immediate_gets,0,1,immediate_gets),2) imisses,
sleeps
from
sys.v_$latch
order by
2 desc;
< currlwaits.sql
select
a.sid,
username,
a.event,
a.p1text,
a.p1,
a.p2text,
a.p2,
a.seq#,
a.wait_time,
a.state
from
sys.v_$session_wait a,
sys.v_$session b,
sys.v_$latchname c
where
a.sid = b.sid and
a.p2 = c.latch# and
a.event in
(select
name
from
sys.v_$event_name
where
name like '%latch%')
order by
1;
< globiostats.sql
select
name,
value
from
sys.v_$sysstat
where
name in
('consistent changes',
'consistent gets',
'db block changes',
'db block gets',
'physical reads',
'physical writes',
'sorts (disk)',
'user commits',
'user rollbacks'
)
order by
1;
< syswaits.sql
select
event,
total_waits,
round(100 * (total_waits / sum_waits),2) pct_waits,
time_wait_sec,
round(100 * (time_wait_sec / greatest(sum_time_waited,1)),2)
pct_time_waited,
total_timeouts,
round(100 * (total_timeouts / greatest(sum_timeouts,1)),2)
pct_timeouts,
average_wait_sec
from
(select
event,
total_waits,
round((time_waited / 100),2) time_wait_sec,
total_timeouts,
round((average_wait / 100),2) average_wait_sec
from
sys.v_$system_event
where
event not in
('lock element cleanup ',
'pmon timer ',
'rdbms ipc message ',
'rdbms ipc reply',
'smon timer ',
'SQL*Net message from client ',
'SQL*Net break/reset to client ',
'SQL*Net message to client ',
'SQL*Net more data from client',
'dispatcher timer ',
'Null event ',
'parallel query dequeue wait ',
'parallel query idle wait - Slaves ',
'pipe get ',
'PL/SQL lock timer ',
'slave wait ',
'virtual circuit status ',
'WMON goes to sleep') and
event not like 'DFS%' and
event not like 'KXFX%'),
(select
sum(total_waits) sum_waits,
sum(total_timeouts) sum_timeouts,
sum(round((time_waited / 100),2)) sum_time_waited
from
sys.v_$system_event
where
event not in
('lock element cleanup ',
'pmon timer ',
'rdbms ipc message ',
'rdbms ipc reply',
'smon timer ',
'SQL*Net message from client ',
'SQL*Net break/reset to client ',
'SQL*Net message to client ',
'SQL*Net more data from client',
'dispatcher timer ',
'Null event ',
'parallel query dequeue wait ',
'parallel query idle wait - Slaves ',
'pipe get ',
'PL/SQL lock timer ',
'slave wait ',
'virtual circuit status ',
'WMON goes to sleep') and
event not like 'DFS%' and
event not like 'KXFX%')
order by
2 desc, 1 asc;
< globaccpatt.sql
select
name,
value
from
sys.v_$sysstat
where
name in
('table scans (cache partitions)',
'table scans (direct read)',
'table scans (long tables)',
'table scans (rowid ranges)',
'table scans (short tables)',
'table fetch by rowid',
'table fetch continued row ')
order by
1;
< fileio.sql
select
d.name file_name,
c.name tablespace_name,
b.phyrds,
b.phywrts,
b.phyblkrd,
b.phyblkwrt,
b.readtim,
b.writetim
from
sys.v_$datafile a,
sys.v_$filestat b,
sys.ts$ c,
sys.v_$dbfile d,
sys.file$ e
where
a.file# = b.file#
and
a.file# = d.file#
and
e.ts# = c.ts#
and
e.file# = d.file#
union all
select
v.fnnam file_name,
c.name tablespace_name,
b.phyrds,
b.phywrts,
b.phyblkrd,
b.phyblkwrt,
b.readtim,
b.writetim
from
sys.v_$tempfile a,
sys.v_$tempstat b,
sys.ts$ c,
sys.x$kccfn v,
sys.x$ktfthc hc
where
a.file# = b.file#
and
a.file# = hc.ktfthctfno
and
hc.ktfthctsn = c.ts#
and
v.fntyp = 7
and
v.fnnam is not null
and
v.fnfno = hc.ktfthctfno
and
hc.ktfthctsn = c.ts#
order by
3 desc;
< toptables.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
table_owner "table owner",
table_name "table name",
command "command issued",
0 - executions "executions",
disk_reads "disk reads",
gets "buffer gets",
rows_processed "rows processed"
from
(select
distinct executions,
command,
table_owner,
table_name,
gets,
rows_processed,
disk_reads
from
(select
decode (a.command_type ,
2, 'insert ' ,
3,'select ',
6, 'update ' ,
7, 'delete ' ,
26,'table lock ') command ,
c.owner table_owner,
c.name table_name ,
sum(a.disk_reads) disk_reads ,
sum(0 - a.executions) executions ,
sum(a.buffer_gets) gets ,
sum(a.rows_processed) rows_processed
from
sys.v_$sql a ,
sys.v_$object_dependency b ,
sys.v_$db_object_cache c
where
a.command_type in (2,3,6,7,26)and
b.from_address = a.address and
b.to_owner = c.owner and
b.to_name= c.name and
c.type = 'table' and
c.owner not in ('SYS','SYSTEM')
group by
a.command_type , c.owner , c.name ) )
where
rownum <= 100;
< largescan.sql
select
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type IN ('TABLE', 'TABLE PARTITION')
and
a.operation LIKE '%TABLE%'
and
a.options = 'FULL'
and
a.hash_value = c.hash_value
and
b.bytes / 1024 > 1024
group by
a.object_owner,
a.object_name,
a.operation,
b.bytes / 1024,
b.segment_type
order by
4 desc, 1, 2 );
< physpctio.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;
< totpctio.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
SELECT
SID,
USERNAME,
ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) TOT_IO_PCT
FROM
(SELECT
b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
SUM(VALUE) TOTAL_USER_IO
FROM
sys.V_$STATNAME c,
sys.V_$SESSTAT a,
sys.V_$SESSION b,
sys.v_$bgprocess p
WHERE
a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID and
c.NAME in ('physical reads','physical writes',
'consistent changes','consistent gets',
'db block gets','db block changes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
GROUP BY
b.SID, nvl(b.USERNAME,p.name)),
(select
sum(value) TOTAL_IO
from
sys.V_$STATNAME c,
sys.V_$SESSTAT a
WHERE
a.STATISTIC#=c.STATISTIC# and
c.NAME in ('physical reads','physical writes',
'consistent changes',
'consistent gets','db block gets',
'db block changes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
ORDER BY
3 DESC;
< topiousers.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
b.sid sid,
decode (b.username,null,e.name,b.username)
user_name,
d.spid os_id,
b.machine machine_name,
to_char(logon_time,'mm/dd/yy hh:mi:ss pm')
logon_time,
(sum(decode(c.name,'physical reads',value,0))
+
sum(decode(c.name,'physical writes',value,0))
+
sum(decode(c.name,
'physical writes direct',value,0)) +
sum(decode(c.name,
'physical writes direct (lob)',value,0)) +
sum(decode(c.name,
'physical reads direct (lob)',value,0)) +
sum(decode(c.name,
'physical reads direct',value,0)))
total_physical_io,
(sum(decode(c.name,'db block gets',value,0))
+
sum(decode(c.name,
'db block changes',value,0)) +
sum(decode(c.name,'consistent changes',value,0)) +
sum(decode(c.name,'consistent gets',value,0)) )
total_logical_io,
100 – 100 *(round ((sum (decode
(c.name, 'physical reads', value, 0)) –
sum (decode (c.name,
'physical reads direct', value, 0))) /
(sum (decode (c.name, 'db block gets',
value, 1)) +
sum (decode (c.name, 'consistent gets',
value, 0))),3)) hit_ratio,
sum(decode(c.name,'sorts (disk)',value,0))
disk_sorts,
sum(decode(c.name,'sorts (memory)',value,0))
memory_sorts,
sum(decode(c.name,'sorts (rows)',value,0))
rows_sorted,
sum(decode(c.name,'user commits',value,0))
commits,
sum(decode(c.name,'user rollbacks',value,0))
rollbacks,
sum(decode(c.name,'execute count',value,0))
executions,
sum(decode(c.name,'physical reads',value,0))
physical_reads,
sum(decode(c.name,'db block gets',value,0))
db_block_gets,
sum(decode(c.name,'consistent gets',value,0))
consistent_gets,
sum(decode(c.name,'consistent changes',value,0))
consistent_changes
from
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$statname c,
sys.v_$process d,
sys.v_$bgprocess e
where
a.statistic#=c.statistic#
and
b.sid=a.sid
and
d.addr = b.paddr
and
e.paddr (+) = b.paddr
and
c.name in
('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)',
'db block gets',
'db block changes',
'consistent changes',
'consistent gets',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user commits',
'user rollbacks',
'execute count'
)
group by
b.sid,
d.spid,
decode (b.username,null,e.name,b.username),
b.machine,
to_char(logon_time,'mm/dd/yy hh:mi:ss pm')
order by
6 desc;
< bgact.sql
select
name,
value
from
sys.v_$sysstat
where
(name like '%DBWR %'
or
name in
('dirty buffers inspected',
'summed dirty queue length',
'write requests'))
or
(name like '%redo%')
order by
1;
< archhist.sql
select
to_char(completion_time,'mm/dd/yy') completion_time,
count(*) log_count
from
sys.v_$archived_log
where
sysdate - completion_time < 31
group by
to_char(completion_time,'mm/dd/yy')
order by
1 desc;
< rolldet.sql
select
name,
round ((rssize / 1024), 2) size_kb,
shrinks,
extends,
gets,
waits,
writes,
xacts,
status,
round ((hwmsize / 1024), 2) hw_kb
from
sys.v_$rollstat a,
sys.v_$rollname b
where
(a.usn = b.usn)
order by
name;
< totuserspace.sql
select
owner,
round((byte_count / 1024 / 1024),2) space_used_mb,
round(100 * (byte_count / tot_bytes),2) pct_of_database
from
(select
owner ,
sum(bytes) as byte_count
from
sys.dba_segments
where
segment_type not in ('TEMPORARY','CACHE')
group by
owner
order by
2 desc),
(select
sum(bytes) as tot_bytes
from
sys.dba_segments);
< sortusage.sql
select
tablespace_name,
current_users,
total_extents,
used_extents,
free_extents,
max_used_size,
max_sort_size
from
sys.v_$sort_segment
order by 1;
< sortdet.sql
select
sql_text,
sid,
c.username,
machine,
tablespace,
extents,
blocks
from
sys.v_$sort_usage a,
sys.v_$sqlarea b,
sys.v_$session c
where
a.sqladdr = b.address and
a.sqlhash = b.hash_value and
a.session_addr = c.saddr
order by
sid;
< topsess.sql
select
'top physical i/o process' category,
sid,
username,
total_user_io amt_used,
round(100 * total_user_io/total_io,2) pct_used
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads','physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)')
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads','physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)'))
where
rownum < 2
union all
select
'top logical i/o process',
sid,
username,
total_user_io amt_used,
round(100 * total_user_io/total_io,2) pct_used
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('consistent gets','db block gets')
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('consistent gets','db block gets'))
where
rownum < 2
union all
select
'top memory process',
sid,
username,
total_user_mem,
round(100 * total_user_mem/total_mem,2)
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_mem
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('session pga memory','session uga memory')
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_mem
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('session pga memory','session uga memory') )
where
rownum < 2
union all
select
'top cpu process',
sid,
username,
total_user_cpu,
round(100 * total_user_cpu/greatest(total_cpu,1),2)
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_cpu
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name = 'CPU used by this session'
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_cpu
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name = 'CPU used by this session' )
where
rownum < 2;
< topsessdet.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select *
from
(select
b.sid sid,
decode (b.username,null,e.name,b.username) user_name,
d.spid os_id,
b.machine machine_name,
to_char(logon_time,'mm/dd/yy hh:mi:ss pm') logon_time,
(sum(decode(c.name,'physical reads',value,0)) +
sum(decode(c.name,'physical writes',value,0)) +
sum(decode(c.name,'physical writes direct',value,0)) +
sum(decode(c.name,'physical writes direct (lob)',value,0))
+
sum(decode(c.name,'physical reads direct (lob)',value,0))
+
sum(decode(c.name,'physical reads direct',value,0)))
total_physical_io,
(sum(decode(c.name,'db block gets',value,0)) +
sum(decode(c.name,'db block changes',value,0)) +
sum(decode(c.name,'consistent changes',value,0)) +
sum(decode(c.name,'consistent gets',value,0)) )
total_logical_io,
100 -
100 *
(round ((sum (decode (c.name, 'physical reads', value,
0)) -
sum (decode (c.name, 'physical reads direct', value,
0))) /
(sum (decode (c.name, 'db block gets', value, 1)) +
sum (decode (c.name, 'consistent gets', value, 0))
),3)) hit_ratio,
(sum(decode(c.name,'session pga memory',value,0))+
sum(decode(c.name,'session uga memory',value,0)) )
total_memory_usage,
sum(decode(c.name,'parse count (total)',value,0)) parses,
sum(decode(c.name,'CPU used by this session',value,0))
total_cpu,
sum(decode(c.name,'parse time cpu',value,0)) parse_cpu,
sum(decode(c.name,'recursive cpu usage',value,0))
recursive_cpu,
sum(decode(c.name,'CPU used by this session',value,0)) -
sum(decode(c.name,'parse time cpu',value,0)) -
sum(decode(c.name,'recursive cpu usage',value,0))
other_cpu,
sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,
sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,
sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted,
sum(decode(c.name,'user commits',value,0)) commits,
sum(decode(c.name,'user rollbacks',value,0)) rollbacks,
sum(decode(c.name,'execute count',value,0)) executions,
sum(decode(c.name,'physical reads',value,0))
physical_reads,
sum(decode(c.name,'db block gets',value,0)) db_block_gets,
sum(decode(c.name,'consistent gets',value,0))
consistent_gets,
sum(decode(c.name,'consistent changes',value,0))
consistent_changes
from
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$statname c,
sys.v_$process d,
sys.v_$bgprocess e
where
a.statistic#=c.statistic# and
b.sid=a.sid and
d.addr = b.paddr and
e.paddr (+) = b.paddr and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)',
'db block gets',
'db block changes',
'consistent changes',
'consistent gets',
'session pga memory',
'session uga memory',
'parse count (total)',
'CPU used by this session',
'parse time cpu',
'recursive cpu usage',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user commits',
'user rollbacks',
'execute count'
)
group by
b.sid,
d.spid,
decode (b.username,null,e.name,b.username),
b.machine,
to_char(logon_time,'mm/dd/yy hh:mi:ss pm')
order by
6 desc);
< userscans.sql
select
sid,
username,
total_user_scans,
round(100 * total_user_scans/total_scans,2) pct_scans
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_scans
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name = 'table scans (long tables)'
group by
b.sid,
nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_scans
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name = 'table scans (long tables)');
< scatwait.sql
select
b.sid,
nvl(b.username,c.name) username,
b.machine,
a.total_waits,
round((a.time_waited / 100),2)
time_wait_sec,a.total_timeouts,
round((average_wait / 100),2)
average_wait_sec,
round((a.max_wait / 100),2) max_wait_sec
from
sys.v_$session_event a,
sys.v_$session b,
sys.v_$bgprocess c
where
a.event = 'db file scattered read '
and a.sid = b.sid
and c.paddr (+) = b.paddr
order by
3 desc,
1 asc;
< large_scanusers.sql
select
c.username username,
count(a.hash_value) scan_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.dba_users c,
sys.v_$sql d
where
a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and c.user_id = d.parsing_user_id
and d.hash_value = a.hash_value
and b.bytes / 1024 > 1024
group by
c.username
order by
2 desc;
< lockcnt.sql
select
count(*)
from
sys.v_$session
where
lockwait is not null;
The return of any non-zero number indicates a current blocking lock situation and can be investigated further by running this query:
select
a.username blocked_user,
b.username blocking_user,
w.sid waiting_session,
h.sid holding_session,
w.type,
decode(h.lmode, 1,'no lock',
2,'row share',
3,'row exclusive',
4,'share',
5,'share row exclusive',
6,'exclusive','none') lmode,
decode(w.request, 1,'no lock',
2,'row share',
3,'row exclusive',
4,'share',
5,'share row exclusive',
6,'exclusive','none') request,
a.row_wait_row# row_waited_on,
w.id1,
w.id2,
w.ctime blocked_user_wait_secs,
u1.name || '.' || t1.name locked_object
from
sys.v_$lock w,
sys.v_$lock h,
sys.v_$session a,
sys.v_$session b,
sys.v_$locked_object o,
sys.user$ u1,
sys.obj$ t1
where
h.lmode != 0 and
w.request != 0 and
w.type = h.type and
w.id1 = h.id1 and
w.id2 = h.id2 and
b.sid = h.sid and
a.sid = w.sid and
h.sid = o.session_id and
o.object_id = t1.obj# and
u1.user# = t1.owner#
order by
4,3;
< curriosql.sql
select
sid,
username,
sql_text
from
sys.v_$sqltext a,
sys.v_$session b
where
b.sql_address = a.address
and b.sid =
(select
sid
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads','physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)')
group by
b.sid,
nvl(b.username,p.name)
order by
3 desc)
where
rownum < 2)
order by
a.piece;
< cartsession.sql
select
username,
count(distinct c.hash_value) nbr_stmts
from
sys.v_$sql a,
sys.dba_users b,
sys.v_$sql_plan c
where
a.parsing_user_id = b.user_id
and options = 'cartesian'
and operation like '%join%'
and a.hash_value = c.hash_value
group by
username
order by
2 desc;
< cartsql.sql
select
*
from
sys.v_$sql
where
hash_value in
(select
hash_value
from
sys.v_$sql_plan
where
options = 'CARTESIAN'
and operation LIKE '%JOIN%' )
order by
hash_value;
< topsql.sql
select
sql_text ,
username ,
disk_reads_per_exec,
buffer_gets_per_exec,
buffer_gets ,
disk_reads,
parse_calls ,
sorts ,
executions ,
loads,
rows_processed ,
hit_ratio,
first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time_secs,
cpu_time_secs_per_execute,
elapsed_time_secs,
elapsed_time_secs_per_execute,
address,
hash_value
from
(select
sql_text ,
b.username ,
round((a.disk_reads/
decode(a.executions,0,1,a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
round((a.buffer_gets/
decode(a.executions,0,1,a.executions)),2)
buffer_gets_per_exec,
a.parse_calls ,
a.sorts ,
a.executions ,
a.loads,
a.rows_processed ,
100 - round(100 *
a.disk_reads/
greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
round(cpu_time / 1000000,3) cpu_time_secs,
round((cpu_time / 1000000)/
decode(a.executions,0,1,a.executions),3)
cpu_time_secs_per_execute,
round(elapsed_time / 1000000,3) elapsed_time_secs,
round((elapsed_time /
1000000)/decode(a.executions,0,1,a.executions),3)
elapsed_time_secs_per_execute,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM')
order by 3 desc)
where
rownum < 21;
< tabscan.sql
select
sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
from
(select
sql_text,
count(*) total_large_scans,
executions
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and c.hash_value = a.hash_value
and b.bytes / 1024 > 1024
group by
sql_text, executions)
order by
4 desc;
< planstats.sql
select
operation,
options,
object_owner,
object_name,
executions,
last_output_rows,
last_cr_buffer_gets,
last_cu_buffer_gets,
last_disk_reads,
last_disk_writes,
last_elapsed_time
from
sys.v_$sql_plan a,
sys.v_$sql_plan_statistics b
where
a.sql_id = b.sql_id and
a.id = b.operation_id and
a.sql_id = '&sql_id'
order by a.id;
< tabscan.sql
select
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and a.hash_value = c.hash_value
and b.bytes / 1024 > 1024
group by
a.object_owner, a.object_name, a.operation,
b.bytes / 1024, b.segment_type
order by
4 desc, 1, 2 );
< unused_indx.sql
select distinct
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
d.index_name
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.dba_indexes d
where
a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and b.bytes / 1024 > 1024
and b.segment_name = d.table_name
and b.owner = d.table_owner
order by
1, 2;
< ash_spikes.sql
select
to_char(round(subline.sample_time, ‘HH24′), ‘YYYY-MM-DD HH24:MI’) as sample_hour,
round(avg(subline.on_cpu),1) as cpu_avg,
round(avg(subline.waiting),1) as wait_avg,
round(avg(subline.active_sessions),1) as act_avg,
round((variance(subline.active_sessions)/avg(subline.active_sessions)),1) as act_var_mean
from
(
select
sample_id,
sample_time,
sum(decode(session_state, ‘ON CPU’, 1, 0)) as on_cpu,
sum(decode(session_state, ‘WAITING’, 1, 0)) as waiting,
count(*) as active_sessions
from
dba_hist_active_sess_history
where
sample_time > sysdate – (&hours/24)
group by
sample_id,
sample_time
) subline
group by
round(subline.sample_time, ‘HH24′)
order by
round(subline.sample_time, ‘HH24′);
< session_waits.sql
select
se.event,
sum(se.total_waits),
sum(se.total_timeouts),
sum(se.time_waited/100) time_waited
from
v$session_event se,
v$session sess
where
sess.username = 'SAPR3'
and
sess.sid = se.sid
group by
se.event
order by 2 DESC;
< ash_user_wait_time.sql
select
s.sid,
s.username,
sum(h.wait_time + h.time_waited ) "total wait time"
from
v$active_session_history h,
v$session s,
v$event_name e
where
h.session_id = s.sid
and
e.event_id = h.event_id
and
e.wait_class <> 'Idle'
and
s.username IS NOT NULL
group by
s.sid, s.username
order by 3;
< ash_event_rollup.sql
title 'High waits on events|Rollup by hour'
column mydate heading 'Yr. Mo Dy Hr' format a13;
column event format a30;
column total_waits heading 'tot waits' format 999,999;
column time_waited heading 'time wait' format 999,999;
column total_timeouts heading 'timeouts' format 9,999;
break on to_char(snap_time,'yyyy-mm-dd') skip 1;
select
to_char(e.sample_time,'yyyy-mm-dd HH24') mydate,
e.event,
count(e.event) total_waits,
sum(e.time_waited) time_waited
from
v$active_session_history e
where
e.event not like '%timer'
and
e.event not like '%message%'
and
e.event not like '%slave wait%'
having
count(e.event) > 100
group by
to_char(e.sample_time,'yyyy-mm-dd HH24'),
e.event
order by 1
;
< ash_object_wait_time.sql
select
o.owner,
o.object_name,
o.object_type,
sum(h.wait_time + h.time_waited ) "total wait time"
from
v$active_session_history h,
dba_objects o,
v$event_name e
where
h.current_obj# = o.object_id
and
e.event_id = h.event_id
and
e.wait_class <> 'Idle'
group by
o.owner,
o.object_name,
o.object_type
order by 4 DESC;
< hot_files_ash.sql
select
f.file_name “Data File”,
count(*) “Wait Number”,
sum(h.time_waited) “Total Time Waited”
from
v$active_session_history h,
dba_data_files f
where
h.current_file# = f.file_id
group by f.file_name
order by 3 desc
< events_waits_hr_ash.sql
select
h.event "Wait Event",
sum(h.wait_time + h.time_waited) "Total Wait Time"
from
v$active_session_history h,
v$event_name e
where
h.sample_time between sysdate - 1/24 and sysdate
and h.event_id = e.event_id
and e.wait_class <> 'Idle'
group by h.event
order by 2 desc
< ash_sql_counts.sql
col c1 heading “invocation|count” format 9,999
col c2 heading “percentage|of|load” format 99
select
sql_id, count(*) c1,
round(count(*)/sum(count(*)) over (), 2) c2
from
v$active_session_history
where
sample_time > sysdate - 1/24/60
and
session_type <> ‘BACKGROUND’
group by
sql_id
order by
count(*) desc;
< ash_sql_waiting_io.sql
select
ash.sql_id,
count(*)
from
v$active_session_history ash,
v$event_name evt
where
ash.sample_time > sysdate – 1/24/60
and
ash.session_state = ‘WAITING’
and
ash.event_id = evt.event_id
and
evt.wait_class = ‘User I/O’
group by
sql_id
order by
count(*) desc;
< ash_high_wait_events.sql
ttitle 'High waits on events|Rollup by hour'
column mydate heading 'Yr. Mo Dy Hr' format a13;
column event format a30;
column total_waits heading 'tot waits' format 999,999;
column time_waited heading 'time wait' format 999,999;
column total_timeouts heading 'timeouts' format 9,999;
break on to_char(snap_time,'yyyy-mm-dd') skip 1;
select
to_char(e.sample_time,'yyyy-mm-dd HH24') mydate,
e.event,
count(e.event) total_waits,
sum(e.time_waited) time_waited
from
v$active_session_history e
where
e.event not like '%timer'
and
e.event not like '%message%'
and
e.event not like '%slave wait%'
having
count(e.event) > 100
group by
to_char(e.sample_time,'yyyy-mm-dd HH24'),
e.event
order by 1
;
< ash_count_table_usage.sql
select
h.sql_id,
count(*)
from
dba_hist_active_sess_history h,
v$sql s
where
h.sql_id = s.sql_id
and
s.sql_fulltext like ‘%orders%’
having
count(*) > 1
group by
h.sql_id
order by
2 desc;
< session_wait_history_events.sql
select
swh.seq# seq_nbr,
sess.sid sid,
sess.username username,
swh.event event,
swh.p1,
swh.p2
from
v$session sess,
v$session_wait_history swh
where
sess.sid = 74
and
sess.sid = swh.sid
order by
swh.seq#;
< ash_display_table_index_wait_counts.sql
set pages 999
set lines 80
break on snap_time skip 2
col snap_time heading 'Snap|Time' format a20
col file_name heading 'File|Name' format a40
col object_type heading 'Object|Type' format a10
col object_name heading 'Object|Name' format a20
col wait_count heading 'Wait|Count' format 999,999
col time heading 'Time' format 999,999
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
-- file_name,
object_type,
object_name,
wait_count,
time
from
dba_hist_waitstat wait,
dba_hist_snapshot snap,
dba_hist_active_sess_history ash,
dba_data_files df,
dba_objects obj
where
wait.snap_id = snap.snap_id
and
wait.snap_id = ash.snap_id
and
df.file_id = ash.current_file#
and
obj.object_id = ash.current_obj#
and
wait_count > 50
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
file_name
;
< ash_cpu_foregound_events.sql
select
TO_CHAR(h.sample_time,'HH24') "Hour",
Sum(h.wait_time/100) "Total Wait Time (Sec)"
from
v$active_session_history h,
v$event_name n
where
h.session_state = 'ON CPU'
and
h.session_type = 'FOREGROUND'
and
h.event_id = n.EVENT_ID
and
n.wait_class <> 'Idle'
group by
TO_CHAR(h.sample_time,'HH24');
< ash_cpu_foregound_events_dow.sql
select
TO_CHAR(h.sample_time,'Day') "Hour",
sum(h.wait_time/100) "Total Wait Time (Sec)"
from
v$active_session_history h,
v$event_name n
where
h.session_state = 'ON CPU'
and
h.session_type = 'FOREGROUND'
and
h.event_id = n.EVENT_ID
and
n.wait_class <> 'Idle'
group by
TO_CHAR(h.sample_time,'Day');
< ash_total_event_wait_time.sql
select
h.event "Wait Event",
SUM(h.wait_time/100) "Wait Time (Sec)"
from
v$active_session_history h,
v$event_name n
where
h.session_state = 'ON CPU'
and
h.session_type = 'FOREGROUND'
and
h.event_id = n.EVENT_ID
and
to_char(h.sample_time,'HH24') = '12'
and
n.wait_class <> 'Idle'
group by
h.event
order by
2 DESC;
< ash_datafile_waits.sql
select
f.file_name “Data File”,
COUNT(*) “Wait Number”,
SUM(h.time_waited) “Total Time Waited”
from
v$active_session_history h,
dba_data_files f
where
h.current_file# = f.file_id
group by
f.file_name
order by 3 DESC;
select
e.name "Wait Event",
sum(h.wait_time + h.time_waited) "Total Wait Time"
from
v$active_session_history h,
v$event_name e
where
h.event_id = e.event_id
and
e.wait_class <> 'Idle'
group by
e.name
order by 2 DESC;
select
s.sid,
s.username,
sum(h.wait_time + h.time_waited) "total wait time"
from
v$active_session_history h,
v$session s,
v$event_name e
where
h.session_id = s.sid
and
e.event_id = h.event_id
and
e.wait_class <> 'Idle'
and
s.username IS NOT NULL
group by
s.sid, s.username
order by 3;
< ash_object_wait_time.sql
select
o.owner,
o.object_name,
o.object_type,
SUM(h.wait_time + h.time_waited) "total wait time"
from
v$active_session_history h,
dba_objects o,
v$event_name e
where
h.current_obj# = o.object_id
and
e.event_id = h.event_id
and
e.wait_class <> 'Idle'
group by
o.owner,
o.object_name,
o.object_type
order by 4 DESC;
Subscribe to:
Posts (Atom)
