Database Reference
In-Depth Information
Analysis With Diagnostics and Tuning Pack
For an analysis with the Diagnostics Pack, I advise you to use the performance pages provided by Enterprise Manager
(whether you use Database Control , Grid Control , or Cloud Control is irrelevant). For this reason, the structure
and the examples in this section are based on Enterprise Manager. Note that in this section, all examples as well
as all references to Enterprise Manager pages are based on Cloud Control version 12.1.0.3.0. In case you can't use
Enterprise Manager, I also describe some scripts that illustrate how to get similar information from the dynamic
performance views, which will allow you to perform an equivalent analysis directly with SQL*Plus. Let me stress,
though, that if you have access to Enterprise Manager, the analysis is much more straightforward. The only advantage
of not using Enterprise Manager is the full flexibility and accessibility of all available data.
Database Server Load
To assess how much the database server is loaded, you can look at the first chart on the Performance Home page. The
chart displays either real-time or historical data. Because this chapter focuses on analyzing performance problems
while they're happening (or just after they took place), it goes without saying that you have to use the real-time data.
In this mode, data for the last hour should be available. Note that the real-time data displayed by the chart is also
available through the v$metric_history view.
When looking at the database server load, you should check not only whether the database server is CPU bound
(in other words, if all CPU cores are fully used), but also whether there are processes not related to the database instance
you're focusing on that consume a lot of CPU time. Figure 4-15 illustrates a case of a database server that was CPU bound
for about 5 minutes. However, the database instance background and foreground processes constantly consumed only
about two CPU cores. In fact, when the database server was CPU bound, about 6 CPU cores were fully used by processes
not related to the database instance. In such a case, it's possible that during those 5 minutes, the database engine
experienced a performance problem because of the other processes that were running on the database server.
Figure 4-15. The Runnable Processes chart shows the CPU utilization as well as the load average at the database
server level.
Unfortunately, up to and including version 11.1, the Runnable Processes chart only shows the load average
(on a Linux database server that value is equivalent to the load average numbers provided through /proc/loadavg ).
This is because the metrics related to the other values aren't available in previous versions.
When a database server is equipped with CPus using simultaneous multithreading, the cpu_count
initialization parameter, as well as the NUM_CPUS value in the v$osstat view, is set to the total number of threads.
as a result, in the runnable Processes chart the red line represents the number of threads, not the number of CPu cores
(as stated). be aware that using the number of threads to assess whether the database is CPu bound can be misleading.
In fact, using all threads at 100% isn't feasible. also note that virtualization introduces a similar issue.
Caution
 
 
Search WWH ::




Custom Search