Find below few useful scripts for Performance Management::
# Important MOS notes for TRACING:
There are several ways to trace a particular session.
REFER : General SQL_TRACE / 10046 trace Gathering Examples (Note 1274511.1)
REFER : How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Note 376442.1)
REFER : How To Use SQL Trace And TKPROF For Performance Issues with EBusiness Suite (Note 980711.1)
# sqlplus settings
set linesize 1000
set pagesize 600
set time on
set timing on
define _editor=vi
set sqlprompt "&_user@&_CONNECT_IDENTIFIER> "
# Increase PGA
ALTER SYSTEM SET pga_aggregate_target='4G' SCOPE=BOTH;
# To create Snapshot
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
# sessions with the highest time for a certain wait::
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
# Sessions with highest CPU consumption::
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
# Sessions with highest DB Time usage::
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module,
st.value/100 as "DB Time(sec)", stcpu.value/100 as "CPU Time (sec)",
round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu,
v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
# Find Hidden Parameters::
===========================
SELECT
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_opt%' escape '/' ;
# Compile schema::
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
EXEC UTL_RECOMP.recomp_serial('&schema_name');
select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','SYNONYM') ;
## Find Type of SQL from v$sqlarea ::
select sql_fulltext,executions,loads,first_load_time,rows_processed,
optimizer_mode,parsing_schema_name,module,
DECODE(command_type,
2, 'INSERT',
3, 'SELECT',
6, 'UPDATE',
7, 'DELETE',
26, 'LOCK',
42, 'DDL',
44, 'COMMIT',
47, 'PL/SQL BLOCK',
command_type) CommandType
from v$sqlarea
where
command_type != 3
and upper(sql_fulltext) like '%IE_SE_EXPENSES%'
order by first_load_time desc
/
########################################################
PL/SQL block to increase CPU usage . For testing only.
########################################################
DECLARE
L_n NUMBER;
BEGIN
WHILE (TRUE)
LOOP
L_ n:= dbms_random.random();
END LOOP;
END;
/
########################################################
This code above will burn all CPU. Use it wisely.
########################################################
#######################################
Script to insert 1M rows into a table:
########################################
CREATE TABLE test_table TABLESPACE USERS AS
( SELECT LEVEL id,
SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) date_value,
DBMS_RANDOM.string('A', 20) text_value
FROM dual
CONNECT BY LEVEL <= 1000000);
