Hands-On 13 (Diagnostic and Tuning Tools)

Another one of your responsibilities as a DBA is to use and read the STATSPACK tool in a case of a performance problem. Your job’s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Installing and Configuring the STATSPACK

Running the SPCREATE script

Creating user PERFSTAT

Connecting to the PERFSTAT user

Cleaning all the STATSPACK table

Producing a performance report

Taking SNAPSHOTS

Setting the TIMED_STATISTICS parameter

Understanding of the X$, and V$ tables

Using the V$FIXED_TABLE

Understanding the PERFSTAT scripts:

SPCREATE.SQL

SPTRUNC.SQL

SPREPORT.SQL

Reading the output PERFSTAT file sp_1_6

Commands:

SHO PARAMETER TIMED_STATISTICS

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

START %ORACLE_HOME%\rdbms\admin\spcreate

START %ORACLE_HOME%\rdbms\admin\sptrunc

START %ORACLE_HOME%\rdbms\admin\spreport

EXECUTE statspack.snap;

-- Hands-On 13 (Diagnostic and Tuning Tools)
-- Preparation
-- Diagnostic and Tuning Tools.
-- Installing and configuring Statspack
SET ECHO OFF
--preparation...
CONNECT system/manager AS SYSDBA
start %ORACLE_HOME%\rdbms\admin\spdrop
ALTER SYSTEM SET TIMED_STATISTICS=FALSE;
pause
SET echo on

--Start

CLEAR SCR
-- In this exercise you will learn how to: install and
-- configure the STATSPACK, create user PERFSTAT,
-- and take a snapshot.

-- We also learn more about the X$ and V$ along with the
-- TIMED_STATISTICS parameter value and more.

-- So, let's connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause


CLEAR SCR 
-- Query the number of X$, and V$ tables you have.

pause


SELECT COUNT(1) FROM v$fixed_table
WHERE SUBSTR(name,1,2) = 'X$'
/

pause

CLEAR SCR
-- The X$ tables have cryptic names and should not be queried directly.

pause


SELECT COUNT(1) FROM v$fixed_table
WHERE SUBSTR(name,1,2) = 'V$'
/

-- The V$ views are based on the X$ tables, which are actually memory 
-- structures that are populated at instance startup and cleared at
-- instance shutdown.

pause

CLEAR SCR
-- Check the TIMED_STATISTICS parameter value.
ue.

pause


SHO PARAMETER TIMED_STATISTICS

pause


CLEAR SCR
-- Set the TIMED_STATISTICS parameter to TRUE to collect timing
-- information in the V$ view.

pause


ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

pause

CLEAR SCR
-- Create the PERFSTAT user with its objects.
-- Make the TOOLS tablespace as its default tablespace and 
-- the TEMP tablespace as its temporary tablespace.

-- Then, run the following script. Make sure that you have
-- at least approximately 75 Megabytes of disk space for the
-- installation.

pause
pause


START %ORACLE_HOME%\rdbms\admin\spcreate

-- The PERFSTAT schema was created.

pause
pause

CLEAR SCR
-- Connect as the PERFSTAT user that was created from SPCREATE script.

pause


CONNECT PERFSTAT/PERFSTAT@SCHOOL

pause

CLEAR SCR
-- Now, clean all the STATSPACK tables.
>
pause
pause


START %ORACLE_HOME%\rdbms\admin\sptrunc

-- The STATSPACK tables were truncated.

pause
pause

CLEAR SCR
-- Take a snapshot.
-- In this example, we will take six snapshots.

pause

EXECUTE statspack.snap;
EXECUTE statspack.snap;
EXECUTE statspack.snap;
EXECUTE statspack.snap;
EXECUTE statspack.snap;
EXECUTE statspack.snap;

pause

CLEAR SCR
-- Produce a performance report.

pause


start %ORACLE_HOME%\rdbms\admin\spreport

pause

CLEAR SCR
-- Open and read the output file sp_1_6.

-- Watch the following columns:

-- In the Shared pool statistics:
-- The Pct Misses in the Library cache should be very low such as 2%. 
-- On the other hand the Hit Ratio should be very high.
-- Also, the Pct Misses in the Dictionary cache should be low such as 15%.

-- In the Buffer Cache statistics:
-- The Cache Hit% or Hit Ratio in the Buffer cache should be above 90%.
-- If it is 70 to 90 - might need tuning.
-- If less than 70 - it needs tuning.

-- In the latches statistics:
-- The Pct Misses in the latch activity should be very close to 0.

-- In the Rollback or UNDO segments statistics:
the Rollback or UNDO segments statistics:
-- The Pct Waits in the Rollback segment stats should be very low.
-- If the Pct Waits column is high then you may need more UNDO segments.

pause
pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause