Introduction Unfortunately, what Oracle calls “Network Waits” most often have little to do with Network but and almost exclusively to do with the time it takes to pack messages for the network before they are sent. Client = you, the tool, sqlplus, application Not the client, the other side = the shadow process is communicating to the client Of the three waits, only “more data” is possibly related to network issues and that’s not even clear, the other two are simply the time it takes to pack a message before sending it. SQL*Net message to client – time to pack…
-
-
Oracle 10 and 11 Buffer Busy Waits usually happen on Oracle 10 and 11 mainly because of insert contention into tables or Indexes. There are a few other rare cases of contention on old style RBS segments, file headers blocks and freelists. Before Oracle 10 and 11 there was one other major reason which was readers waiting for readers, ie one user does a phyiscal IO of a block into memory and a second user want to read that block. The second user waits until the IO is finished by the first user. Starting in 10g this wait has been given…
-
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…
-
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…
-
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…