Here are the tuning metrics tables (SQL stats are not in “metric” tables per say) (*DBA_HIST_…_HISTORY views are sort of confusing. AFAI remember they were storing alert history, but apparently they are used for adaptive thresholds – an area for future investigation) I’ve noticed a number of people posting queries using DBA_HIST_SYSSTAT instead of DBA_HIST_SYSMETRIC_SUMMARY which leads me to believe that there is some confusion or lack of information on the metric tables. Oracle 10g introduced metric tables which compute deltas and rates of statistics thus hugely simplifying the ability to answer simple questions like “what is the I/O rate on my…
-
-
Here is a quick table comparison of different types of metrics views The first line of the table is the classic wait event and statistic views. The following lines are the metric views. The metric views were introduced in Oracle 10g. Why Metrics are good Metric views compute deltas and rates which hugely simplifying the ability to answer simple questions like “what is the I/O rate on my databases right now?” This question, before 10g, was surprisingly tedious to answer. To answer the question one would have to query v$sysstat for example: Select value from v$sysstat where name=’physical reads’; but…
-
If one is seeing waits for enq: TX – row lock contention then there could be a lot of reasons. One distinguishing factor is the lock mode. If the lock mode is exclusive (mode 6) then it’s most likely a classic row lock where two sessions are trying to modify the same row. On the other hand if the lock mode is share (mode 4) it’s typically going to be Primary/Unique Key: inserting a unique key when someone else has already inserted that key but not committed Foreign Key: Inserting a foreign when then parent value has been inserted but not…
-
One thing that I have found sorely missing in the performance pages of Enterprise Manager is latency values for various types of I/O. The performance page or top activity may show high I/O waits but it won’t indicated if the latency of I/O is unusually high or not. Thus I put together a shell script that shows latency for the main I/O waits db file sequential read db file scattered read log file parallel write direct path reads direct path reads temp Of course it would be nice to add a few others like direct path writes, direct path writes…
-
Anyone who has been around Oracle performance over the years knows the grief that hard parsing SQL queries can cause on highly concurrent applications. The number one reason for hard parsing has been applications that don’t use bind variables. Without bind variables queries that would otherwise be shared get recompiled because their text is different and Oracle treats them as different queries. Oracle addressed this issue with a parameter called cursor_sharing. The parameter cursor_sharing has three values exact – the default similar – replace literals with bind variables, if a histogram keep literal in place force – replace literals with…
-
Image from Bertrand Drouvot UPDATE: thanks to a discussion in the comments it’s come to my attention that there should be some more clarification on values used and what the mean at the beginning of this post. Ever wonder where CPU wait comes from in EM performance screens as seen above? well the following discussion will give you a SQL query to calculate CPU wait. In the above image we see both CPU and CPU Wait as two colors (light green and dark green) in EM as well as two values from the output of the SQL script (CPU_ORA and…
-
Seeing more and more questions on “where do I start with Oracle if I want to be a DBA?” My perspective is a bit off since I’ve been surrounded by Oracle for over 20 years. I hardly remember what it was like to start with Oracle and starting with Oracle now in 2013 is quite different than starting with Oracle in 1990. Here is my list and everything on this list is excellent. I’m sure I missed a few good ones, but maybe people can add them in the comments. Start with Oracle Docs, they are free and good! http://www.oracle.com/pls/db112/homepage…
-
How do we know where latency comes from when there is a disparity in reported I/O latency on the I/O subsystem and that of the latency reported on the client box requesting the I/O. For example if I have an Oracle database requesting I/O and Oracle says an 8Kb request takes 50 ms yet the I/O storage subsystem says 8Kb I/Os are taking 1ms (averages) , then where does the 49 extra ms come from? When the I/O subsystem is connected to Oracle via NFS then there are a lot of layers that could be causing the extra latency. Where…
-
Oracle has a crazy mix of units of time in various v$ views seconds centi-seconds milliseconds microseconds Some are straight forward such as time_waited_micro, but what unit is “TIME_WAITED” or “WAIT_TIME” in? For example v$session WAIT_TIME – centi SECONDS_IN_WAIT – seconds v$session_wait WAIT_TIME – centi SECONDS_IN_WAIT – seconds v$system_event TIME_WAITED – centi AVERAGE_WAIT – centi TIME_WAITED_MICRO – micro v$system_wait_class TIME_WAITED – centi v$eventmetric TIME_WAITED – centi v$waitclassmetric DBTIME_IN_WAIT – “percentage of the measured wait time that was actually in foregrounds and therefore part of DB time” * TIME_WAITED – centi v$waitclassmetric_history DBTIME_IN_WAIT – “percentage of the measured wait time that…