Wait Events 2 – What is Wrong with this Quote?

8 12 2010

December 8, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

While reading the “Oracle Tuning the Definitive Reference Second Edition” book I found a handful of interesting suggestions regarding Oracle wait events.  Take 60 seconds to analyze the following quote from page 656:

“There are three columns of the v$session_wait view that are of particular interest for a buffer busy wait event: …  The following is an Oracle data dictionary query for these values:

select
  p1 "File #".
  p2 "Block #",
  p3 "Reason Code"
from
  v$session_wait
where
  event = 'buffer busy waits'; 

“

Keeping in mind that the book is printed after the release of Oracle Database 11.2.0.1 (and possibly 11.2.0.2 for some operating system platforms), what, if anything, is wrong with the above quote?

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Other pages found during a Google search of the phrase:

  • Previous edition of the book
  • dba-oracle.com/art_builder_bbw.htm
  • praetoriate.com/t_oracle_wait_events_v$system_event.htm
  • dba-oracle.com/t_open_file_waits_event_tuning.htm
  • remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm

« »


Actions

Information

10 responses

9 12 2010
Donatello Settembrino (04:10:24) :

Hi Charles,
I think there are 2 errors 🙂 in


select
  p1 "File #".
  p2 "Block #",
  p3 "Reason Code"
from
  v$session_wait
where
  event = 'buffer busy waits'

the first [p1 (“File #”.), the ‘.’ should be replaced with ‘,’]

Seriously …

if the book was written after the release of Oracle Database 11.2.0.1
the query must be corrected:


select
  p1 "File #", 
  p2 "Block #",
  p3 "Class#"
from
  v$session_wait
where
  event = 'buffer busy waits'

In fact, with Oracle 10g the parameter p3 (parameter3)

select name, parameter1, parameter2, parameter3 
from V$EVENT_NAME 
where name = 'buffer busy waits'

NAME                                   PARAMETER1     PARAMETER2   PARAMETER3 
------------------------ -------------- ------------ -----------
buffer busy waits                   file#                   block#            class#

class# refers to the class of the block (example, class# 1 refers to the Data Blocks)
and not to the “Reason Code” (ID, such as 100, 110 etc.).

I think you are referring to this

Regards

Reply
9 12 2010
Charles Hooper (07:09:42) :

Donatello,

You provided very good descriptions of the problems, and how to fix those problems. I found those same two problems in about 60 seconds, although your answers are much more complete than the ones that I had put together.

Now for the bonus points, what other problems are in the quote? I think that I spent about 3 minutes on this part of the test (that is, 3 minutes after the initial 60 seconds). 🙂

Reply
9 12 2010
Niall Litchfield (09:13:28) :

I’d have thought the statement “there are 3 columns of v$session_wait that are of particular interest for a buffer busy wait event” might be expanded a bit. You would likely also include state (to see if the wait was a red herring having already completed and time_waited. There is of course an easier way to ensure you know what P1,P2,P3 refer to for any given row in v$session_wait and that is to use the P1TEXT,P2TEXT,P3TEXT columns as well (they’ve been around since 9.2 at least so there’s no excuse really). As it stands the books query tells you that there was a wait of unknown duration at an unknown time (might not be now) on this event. If that’s of particular interest I’ve got a chocolate teapot for sale.

Reply
9 12 2010
Taral Desai (10:22:20) :

It won’t say the state if it’s waiting or on CPU. Because if not waiting then it’s meaningless.

Reply
9 12 2010
Charles Hooper (10:48:09) :

I think that Niall is making a very important comment in this blog article about the V$SESSION_WAIT performance view. I thought that there might be a couple of more columns that are also important in the V$SESSION_WAIT performance view – I think that I previously mentioned the lack of a detailed description of this performance view in at least two other book reviews (for the record, I had not yet made the conclusion about this quote that the STATE column should be retrieved, as both Niall and Taral mentioned).

There was an OTN thread a couple of years ago that finally helped me understand this performance view. See Jonathan Lewis’ description of the columns (and the follow up by Hemant):
http://forums.oracle.com/forums/thread.jspa?threadID=684852

So, anything else wrong with the quote? 🙂

Reply
9 12 2010
Jonathan Lewis (13:52:07) :

Charles,

I would have said that v$session_wait was a dynamic performance view, not a data dictionary view.

Reply
9 12 2010
Charles Hooper (14:24:53) :

Jonathan,

Thanks for the terminology correction. That “data dictionary query” phrase and the missing “s” at the end of the wait event name in the first sentence of the quote were the final two problems that I found in the quote.

Reply
10 12 2010
Niall Litchfield (05:53:12) :

At the risk of splitting hairs with Jonathan (“what do you mean by hint anyway?”) I don’t object to the “data dictionary” terminology too much. Whilst it is obviously true that its a dynamic performance view, its fairly common usage to refer to all v$.. views as data dictionary views, oracle does it in the old reference material which is headed “Catalog Views / Data Dictionary Views” eg http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage which is perfectly readable as meaning that the two definitions are interchangeable. In fact it was precisely this sort of common understanding that made me so annoyed when the otherwise excellent Performance Instrumentation became chargeable in 10g. It isn’t unreasonable (though pedantically incorrect) to consider v$active_session_history as a part of the data dictionary – especially as it is referenced as such in the docs – that we are now being charged to write queries against. (I don’t mind being charged extra for Oracle’s use of this information in EM by the way, but being charged to write my own SQL!) .

so yes I understand the distinction, but its a bit too hair-splitting for me (and I am generally rather prone to hair splitting myself).

Reply
10 12 2010
Charles Hooper (06:47:05) :

I wonder if the distinction has been clarified better in recent versions of the documentation? When I clicked on the V$SESSION_WAIT link that was embedded in the link supplied by Niall, the window title showed “Dynamic Performance (V$) Views, 177 of 238”, but the web page itself did not indicate that V$SESSION_WAIT is a dynamic performance view.

If we jump forward to Oracle Database 10.1:
http://download.oracle.com/docs/cd/B13789_01/server.101/b10743/datadict.htm

“One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:
•The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
•How much space has been allocated for, and is currently used by, the schema objects
•Default values for columns
•Integrity constraint information
•The names of Oracle users
•Privileges and roles each user has been granted
•Auditing information, such as who has accessed or updated various schema objects
•Other general database information”

And then to 11.2:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datadict.htm

“An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:
•The definitions of every schema object in the database, including default values for columns and integrity constraint information
•The amount of space allocated for and currently used by the schema objects
•The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users (see “User Accounts”)

The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:
•Accesses the data dictionary to find information about users, schema objects, and storage structures
•Modifies the data dictionary every time that a DDL statement is issued (see “Data Definition Language (DDL) Statements”)

Because Oracle Database stores data dictionary data in tables, just like other data, users can query the data with SQL. For example, users can run SELECT statements to determine their privileges, which tables exist in their schema, which columns are in these tables, whether indexes are built on these columns, and so on.
…
Overview of the Dynamic Performance Views
Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity. These views are called dynamic performance views because they are continuously updated while a database is open and in use. The views, also sometimes called V$ views, contain information such as the following:
•System and session parameters
•Memory usage and allocation
•File states (including RMAN backup files)
•Progress of jobs and tasks
•SQL execution
•Statistics and metrics

The dynamic performance views have the following primary uses:
•Oracle Enterprise Manager uses the views to obtain information about the database (see “Oracle Enterprise Manager”).
•Administrators can use the views for performance monitoring and debugging.”

So, at least in recent versions of the documentation, anything performance related that is “continuously updated while a database is open and in use” should probably be called a “dynamic performance view” – but then I could quite possibly be wrong (I still intentionally mispronounce the word gigabyte as gig-a-bite, rather than as jig-a-bite).

Reply
10 12 2010
Niall Litchfield (07:38:21) :

That is clear – it is only the 11.2 version that explicitly makes that distinction though 11.1 has the old wording as well – though to be fair the Reference Manual (which is what I generally use) has split Data Dictionary views and Dynamic Performance views for years. I still stand by my view though that describing a v$ view as a data dictionary object is sufficiently widespread that I wouldn’t be quibbling with its use in a book review. I emphatically would quibble with selecting just those 3 columns as columns of interest though 🙂

Reply

Leave a reply to Niall Litchfield Cancel reply