Basic Tuning Diagnostics

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Objectives

After completing this lesson, you should be able to do the

following:
• View the top wait events to determine the highest wait
• View the time model to diagnose performance issues
• Use dynamic performance views to view statistics and wait
events
• Identify the key tuning components of alert logs
• Identify the key tuning components of user trace files

2-2 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Performance Tuning: Diagnostics

Diagnostic tools gather and format the following types of

performance data:
• Cumulative statistics:
– Wait events with time information
– Time model
• Metrics: Statistic rates
• Sampled statistics: Active Session History, part of the
Oracle Diagnostics Pack
– Statistics by session
– Statistics by SQL
– Statistics by service
– Other dimensions

2-3 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Performance Tuning: Features and Tools

• Automatic performance tuning features:

– Automatic Workload Repository (AWR)
– Automatic Database Diagnostic Monitor (ADDM)
– Server-generated alerts
– Advisors
– Database Performance page of Enterprise Manager
• Additional tools:
– V$ performance views
– Alert log
– Trace files
– Statspack

2-4 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Tuning Objectives

• Minimizing response time

• Increasing throughput
• Increasing load capabilities
• Reducing recovery time

2-5 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 DB Time

DB Time =

DB Wait Time +
The wait time is the sum of all the waits
for various database instance resources

DB CPU Time
The sum of the time that is spent
actually working on the request

2-6 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 CPU and Wait Time Tuning Dimensions

DB time = DB CPU time + DB wait time

CPU
time
Possibly Scalable
needs SQL application
tuning

Needs No gain
instance/RAC achieved
tuning by adding
CPUs/nodes
Wait
time

2-7 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Time Model: Overview

• The time model is a set of statistics that give an overview

of where time is spent inside the Oracle database.
• All statistics use the same dimension: time.
DB time
• The statistics are accessible through:
Parse
– V$SYS_TIME_MODEL
– V$SESS_TIME_MODEL
• DB time represents the total time
SQL
• spent in database calls by user sessions.
• A tuning goal is to reduce DB time.
• Using DB time, you can gauge the performance impact of
any entity of the database.

2-8 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Time Model Statistics Hierarchy

DB time Background elapsed time

DB CPU Background CPU time
Connection management call elapsed time RMAN CPU time
Sequence load elapsed time (backup/restore)
SQL execute elapsed time
Repeated bind elapsed time
Parse time elapsed
Hard parse elapsed time
Hard parse (sharing criteria) elapsed time
Hard parse (bind mismatch) elapsed time
Failed parse elapsed time
Failed parse (out of shared memory) elapsed time
PL/SQL execution elapsed time
Inbound PL/SQL RPC elapsed time
PL/SQL compilation elapsed time
Java execution elapsed time

2-9 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
 Dynamic Performance Views

Dynamic performance views

provide access to information
about changing states and
Session data
conditions in the instance. Wait events
Memory allocations
Running SQL
UNDO usage
Open cursors
Redo log usage
And so on

Oracle instance

2 - 10 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Dynamic Performance Views:
Usage Examples

SQL> SELECT sql_text, executions

a 2 FROM v$sqlstats
3 WHERE cpu_time > 200000;

SQL> SELECT * FROM v$session

b 2 WHERE machine = 'ED9P1' and
3 logon_time > SYSDATE - 1;

SQL> SELECT sid, ctime

c 2 FROM v$lock WHERE block > 0;

2 - 11 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Dynamic Performance Views: Considerations

• These views are owned by SYS.

• Different views are available at different times:
– The instance has been started.
– The database is mounted.
– The database is open.
• You can query V$FIXED_TABLE to see all the view
names.
• These views are often referred to as “v-dollar views.”
• All reads on these views are current reads.

2 - 12 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Statistic Levels

V$STATISTICS_LEVEL

STATISTICS_LEVEL

BASIC TYPICAL ALL

Disable all Additional statistics

Recommended
self-tuning for manual
default value
capabilities SQL diagnostics

2 - 13 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Displaying Statistics

Instance activity statistics are collected for:

• Sessions
– All sessions: V$SESSTAT
– Current session: V$MYSTAT
• Services: V$SERVICE_STATS
• System: V$SYSSTAT

2 - 14 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Displaying SGA Statistics

SQL> SELECT * FROM v$sgainfo;

NAME BYTES RES CON_ID
-------------------------------- ---------- --- --------
Fixed SGA Size 2291280 No 0
Redo Buffers 7303168 No 0
Buffer Cache Size 339738624 Yes 0
Shared Pool Size 301989888 Yes 0
Large Pool Size 8388608 Yes 0
Java Pool Size 4194304 Yes 0
Streams Pool Size 0 Yes 0
Shared IO Pool Size 29360128 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
Maximum SGA Size 663908352 No 0
Startup overhead in Shared Pool 124364176 No 0
Free SGA Memory Available 0 0

2 - 15 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Wait Classes
V$SESSION_WAIT_CLASS V$SYSTEM_WAIT_CLASS
Internal database resources
Concurrency
Background
log file sync Commit System I/O processes I/O

Foreground
Network User I/O processes I/O
Network
messaging

Inactive Wait DBA

Idle Administrative commands
sessions classes
User Should be
application Application Other
rare
code

RAC Cluster Configuration Inadequate database/

resources instance configuration
Scheduler
V$SERVICE_WAIT_CLASS Resource manager V$EVENT_NAME

2 - 16 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Commonly Observed Wait Events

Wait Event Area

Buffer busy waits Buffer cache, DBWR

Free buffer waits Buffer cache, DBWR, I/O

Db file scattered read, I/O, SQL Tuning

Db file sequential read
Enqueue waits (enq:) Locks

Library cache waits Mutexes/Latches

Log buffer space Log buffer I/O

Log file sync Over-commit, I/O

2 - 17 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Precision of System Statistics

• Views that include microsecond timings:

– V$SESSION_WAIT, V$SYSTEM_EVENT,
V$SERVICE_EVENT,
V$SESSION_EVENT (TIME_WAITED_MICRO column)
– V$SQL, V$SQLAREA (CPU_TIME, ELAPSED_TIME
columns)
– V$LATCH, V$LATCH_PARENT, V$LATCH_CHILDREN
(WAIT_TIME column)
– V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE
(ACTIVE_TIME column)
• Views that include millisecond timings:
– V$ENQUEUE_STAT (CUM_WAIT_TIME column)

2 - 18 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Oracle Enterprise Manager Cloud
Control Components
Oracle Management Target-specific
Agent plug-in

Managed
Hosts

Oracle Management
Service
Cloud Control
Console
Oracle Management
Repository

2 - 19 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Using Features of the Oracle Management Packs
Monitoring and tuning by using packs Monitoring and tuning
Oracle Diagnostics Pack without the use of packs
• Performance monitoring and diagnostics
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM) • SQL traces
• Compare Period/Real Time ADDM
• Active Session History (ASH) • Statspack
• Exadata Administration/Performance/Resource Utilization
•
•
Blackouts
Notifications
• System statistics
•
•
Metric and Alert/Event History
Dynamic metric baselines/adaptive metric thresholds
• Wait model
•
•
Monitoring Templates/Template Collections
Bottleneck detection/component top wait event analysis • Time model
• Memory access mode
• OS statistics
Oracle Tuning Pack Database Lifecycle Management Pack
• SQL Access Advisor For Oracle Database • Metrics
• SQL Tuning Advisor • Client system analyzer
• SQL Tuning Sets •
•
Compliance
Configuration Collection Extensibility
• Service statistics
• Automatic SQL Tuning
• SQL Profiles •
•
Configuration Compare/History/Save/Search
Configuration Instance Browser
• Histograms
• Real-time Database
Operations Monitoring
• Real-time SQL and PL/SQL
•
•
Configuration Topology
Copy (database) objects
• Optimizer statistics
•
Monitoring
• Reorganize objects •
Database Patching/Provisioning/Upgrade
File Synchronization • SQL statistics
• Automatic Plan Evolution of • Notifications
SQL Plan Management • Real-time config/schema change detection
• Schema/data comparisons/synchronization
• Schema Change Plans

2 - 20 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Using Alert Log Information
as an Aid in Tuning
The alert log file contains the following information that can be
used to aid in tuning the database:
• Time to perform archiving
• Instance recovery start and complete times
• Deadlock and timeout errors
• Incomplete checkpoints
• Checkpoint start and end times

2 - 21 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Administering the DDL Log File

• Enable the capture of certain DDL statements to a DDL log

file by setting ENABLE_DDL_LOGGING to TRUE.
• DDL log contains one log record for each DDL statement.
• Two DDL logs containing the same information:
– XML DDL log: named [Link]
– Text DDL: named ddl_<sid>.log
• Example: $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log
$ more ddl_orcl.log
Thu Nov 15 08:35:47 2012
diag_adl:drop user app_user

2 - 22 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Quiz

Which diagnostic tool would you use to discover when the last
database startup and backup occurred?
a. Trace files
b. Alert log
c. Enterprise Manager Cloud Control database home page
d. V$ views

2 - 23 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

 Summary

In this lesson, you should have learned how to:

• View the top wait events to determine the highest wait
• View the time model to diagnose performance issues
• Use dynamic performance views to view statistics and wait
events
• Identify the key tuning components of alert logs
• Identify the key tuning components of user trace files

2 - 24 Copyright © 2019, Oracle and/or its affiliates. All rights reserved.