Visualizing Oracle data is a critical task and it can be done with a
variety of tools:
So, how do we add a third dimension to an Oracle
query and visualize it? Let's start with this sample Ion
screenshot of a two-dimensional plot of tablespace activity over time.
Note the three variables (tablespace_name, physical_writes, and time):
Ion screenshot for tablespace
physical writes over time
Creating 2d charts in Excel is easy, and the ability to visualize
Oracle performance is critical for Oracle
troubleshooting, in cases where the database performance has changed.
What kind of changes can cause unexpected performance problems?
Here are some common examples:
- SQL- changes to optimizer parameters
(optimizer_mode, optimizer_index_cost_adj,
db_file_multiblock_read_count )(before 11g)) and CBO statistics
can cause system-wide performance changes. This will manifest
as changes in logical I/O, and the problem can be spotted by
graphing consistent gets by top SQL ordered by gets, plotted along
the time dimension.
- Disk changes - Changing disk characteristics
(RAID level, stripe size, controllers) will impact performance.
The salient metrics are physical_reads and
file_name,
plotted over time. We can also plot I/O time and top-5
tablespace, total I/O and top-5 sessions.
- Buffer efficiency - You can visualize the
buffer changes over time in two dimensions by plotting the ratio of
logical reads (consistent_gets) to physical disk reads over
time (the
data buffer hit ratio). To enhance this in three
dimensions we add (top-5 SQL ordered by reads, or buffer busy waits)
and plot over time.
- Network performance -
Oracle network bottleneck analysis is well understood, and you
can plot SQL*Net messages to client + sql*Net message.
- CPU performance - We can also plot CPU time and
consistent gets over time to evaluate CPU efficiency.
We are allowing the end-user to choose the x, y values as well as the
start and end snapshots. We then dynamically create the SQL, run
the query and pipe the results to a three dimensional charting tool.
Network performance - This plots "SQL*Net"
waits over time
set pages 999;
column mydate heading 'Yr. Mo Dy Hr'
format a13;
column event
format a30;
column waits
format 999,999;
column secs_waited
format 999,999,999;
column avg_wait_secs
format 99,999;
select
to_char(snap_time,'yyyy-mm-dd
HH24')
mydate,
e.event,
e.total_waits - nvl(b.total_waits,0)
waits,
((e.time_waited - nvl(b.time_waited,0))/100) /
nvl((e.total_waits - nvl(b.total_waits,0)),.01) avg_wait_secs
from
stats$system_event b,
stats$system_event e,
stats$snapshot
sn
where
e.snap_id = sn.snap_id
and
b.snap_id = e.snap_id-1
and
b.event = e.event
and
e.event like 'SQL*Net%'
and
e.total_waits -
b.total_waits > 100
and
e.time_waited -
b.time_waited > 100
