Practice 2 - Using Time Model Views P a g e |1

Practice 2

Using Time Model Views

Practice Target
In this practice, you will use the time model views to perform the following:
• Measure the entire database workload

• Measure the entire database wait time ratio to DB CPU

• Obtain the sessions with top waiting times

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |2

Preparing for the Practice

In this section of the practice, you will prepare your environment for this practice.

1. In Oracle VirtualBox make sure the virtual appliance srv1 is up and running. If it is shut down,
start it up.

2. Start Putty, login to srv1 as oracle

3. Download the file scripts.zip from the practice downloadable resources section.
Note: scripts.zip contains script files that will be used in multiple course practices.

4. Copy the file to the staging folder.

5. Decompress the file into the staging folder then delete the zip file.
cd /media/sf_extdisk
unzip scripts.zip
rm scripts.zip

6. Add to the oracle profile file a variable that points to the script directory.
echo "export SD=/media/sf_extdisk/scripts" >> ~/.bash_profile
source ~/.bash_profile
echo $SD

7. Run the following code to create three script files. Those script files will be used only in this
practice.
cat > $SD/create_tm_history.sql <<EOL

-- create table to save DBTIME, DB CPU, and total waits in it

DROP TABLE TM_HISTORY;
DROP SEQUENCE S;
CREATE SEQUENCE S;
CREATE TABLE TM_HISTORY AS SELECT S.NEXTVAL AS SNAP_ID,
DBTIME.VALUE/1000000 DBTIME,
DBCPU.VALUE/1000000 DBCPU,
(DBTIME.VALUE-DBCPU.VALUE)/1000000 WAIT_TIME,
(SELECT COUNT(*) FROM V\$SESSION WHERE USERNAME IS NOT NULL) USERS_CNT
FROM V\$SYS_TIME_MODEL DBTIME, V\$SYS_TIME_MODEL DBCPU
WHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU';
EOL

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |3

cat > $SD/display_tm_history.sql << EOL

-- retrieve data from TM_HISTORY

set linesize 180
SELECT
TO_CHAR(DBTIME,'999,999,999') DBTIME,
TO_CHAR(DBCPU,'999,999,999') DBCPU,
ROUND(DBCPU - LAG(DBCPU, 1, 0) OVER (ORDER BY DBCPU)) AS DBCPU_DIFF,
TO_CHAR(WAIT_TIME,'999,999,999,999') WAIT_TIME,
ROUND(WAIT_TIME - LAG(WAIT_TIME, 1, 0) OVER (ORDER BY WAIT_TIME)) AS
WAIT_TIME_DIFF,
TO_CHAR((DBTIME-DBCPU)/DBTIME*100,'99.99') || '%' WAIT_PCT,
USERS_CNT,
ROUND((DBTIME-DBCPU)/USERS_CNT) WAIT_USER_SHARE
FROM TM_HISTORY
ORDER BY SNAP_ID;
EOL

cat > $SD/take_tm_snapshot.sql <<EOL

-- save the current time model basic statistics into the TM_HISTORY table
INSERT INTO TM_HISTORY
SELECT
S.NEXTVAL AS SNAP_ID,
DBTIME.VALUE/1000000 DBTIME,
DBCPU.VALUE/1000000 DBCPU,
(DBTIME.VALUE-DBCPU.VALUE)/1000000 WAIT_TIME,
(SELECT COUNT(*) FROM V\$SESSION WHERE USERNAME IS NOT NULL) USERS_CNT
FROM V\$SYS_TIME_MODEL DBTIME, V\$SYS_TIME_MODEL DBCPU
WHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU';
COMMIT;
EOL

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |4

Demonstrating Time Model

In this section of the practice, you will demonstrate using the time model views: V$SYS_TIME_MODEL
and V$SESS_TIME_MODEL.

Using Time Model to know which operation type is mostly used

8. Invoke SQL*Plus and connect to ORADB as sysdba

sqlplus / as sysdba

9. Run the following script to display the top 7 “time model statistics” operations at the system
level.
The output of this script helps you on knowing on which operation type the database time was
mostly spent on. When analyzing this view figures, keep in mind that its figures are cumulative
since instance last startup.
@ $SD/time_model.sql

10. Run the following query script to display the percentage of each operation type from the total DB
time.
This query makes it easy to know which operation type is mostly spent by the database.
Note: The total of the percentage figures in the children rows is not 100. That is normal.
@ $SD/time_model_pct.sql

11. Run the following query script to display the time model statistics in a hierarchy structure.
Displaying time model in tree structure allows you to understand the relationship between the
operation types.
@ $SD/time_model_tree.sql

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |5

Using Time Model to measure the database workload and performance

In the following steps, you will gradually increase the workload on the database and measure the
change impact on the time model statistics.

You will create a table to save snapshots of the DB time, DB CPU, and total waits in it. You will apply
different workload levels against the database and save the time model figures at each level into the
table. You will then analyze the table contents.

We do not perform those steps in real life scenario. The target of these steps is to understand the time
model statistics.

12. Run create_tm_history script to create a table to save history of the basic time model
statistics in it.
@ $SD/create_tm_history.sql

13. Display the contents of the table.

If the system was started short time ago, the view contents do not represent the actual database
workload.
@ $SD/display_tm_history.sql

14. In the hosting PC, open a command prompt window and start Swingbench.
D:
cd swingbench\winbin
set PATH=D:\oracle\product\12.1.0\client_1\jdk\jre\bin;%PATH%
swingbench.bat

15. In Swingbench, make sure that the Number of Users field is set to 10 then click on the Start
Benchmark Run button.

16. Keep the sessions in Swingbench running for a minute. Do not rely on your estimation. Use a
stop watch to make sure the stress testing is running for a minute.

17. Save the current time model basic statistics into the history table.
@ $SD/take_tm_snapshot.sql

18. Stop the Swingbench benchmark run.

19. Display the contents of the table and analyze the output. Compare between the statistics of the
most recent run with the statistics of the previous runs.
@ $SD/display_tm_history.sql

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |6

20. In Swingbench, re-run the benchmark for a minute after setting the Number of Users field to
30 and then re-run it again after it is set to 60. With each run, save a snapshot of the time
model statistics into history table by running the script take_tm_snapshot

21. Display the contents of the table and analyze the output.
In your analysis, consider the following:
o The total wait percentage (WAIT_PC) compares between the time spent by the system
waiting and the time it was serving the sessions. The higher, the worse.
o How much the DB CPU increased (DBCPU_DIFF) and, for that increase, how much the total
wait increased (WAIT_TIME_DIFF).
o The wait time per user (WAIT_USER_SHARE) represents the wait time share per user.
Note: In your analysis, you might ignore the first row statistics.
@ $SD/display_tm_history.sql

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |7

Using Time Model to obtain list of the top sessions

22. In Swingbench, re-run the benchmark run after setting the Number of Users field to 20. Wait
for a minute.

23. Run the following query to retrieve the sessions with top total wait times.
@ $SD/time_model_topsessions.sql

24. To cleanup, perform the following actions:

o Shutdown the benchmark run in Swingbench and exit from it
o Drop the created objects.
DROP TABLE TM_HISTORY;
DROP SEQUENCE S;

o Exit from SQL*Plus

o Delete the practice scripts:
rm $SD/create_tm_history.sql
rm $SD/display_tm_history.sql
rm $SD/take_tm_snapshot.sql

Oracle Database Performance Tuning, a course by Ahmed Baraka

 Practice 2 - Using Time Model Views P a g e |8

Summary
Time model views (V$SYS_TIME_MODEL and V$SESS_TIME_MODEL) can be used to:
o Measure the total workload on the database
o Measure the total wait time ratio to the total DB CPU time
o Obtain list of the current sessions with top wait time periods

Oracle Database Performance Tuning, a course by Ahmed Baraka