May 2010

May-June 2010

Dilbert Was a Database Professional (Part II)

Databases have figured dozens of times in Dilbert cartoons. Our favorite is Dogbert defeats the database guru (November 9, 1995). Here are some more Dilbert cartoons featuring databases:
Click here for Part I of this series

Finding Free Oracle Help Online

This month’s question came to us from a reader:

“The internet is anarchy. What are the most reliable sources of information on the internet?

From one perspective, the internet is anarchy. It is full of conflicting, outdated, and inaccurate information. The good folks at Miracle Finland have solved the problem by creating a custom search engine that promotes websites and blogs that contain high-quality information.

From another perspective, the Internet is a treasure trove of information that can help you in solving a problem. For example, user groups such as the Northern California Oracle Users Group have made vast collections of electronic presentations and white papers available on their web pages. Often a simple Google search will bring up an answer, but many specialized resources also are available.

The highest quality resource is of course the online Oracle documentation. It is freely viewable, searchable, and downloadable. Documentation for older versions of Oracle software going back to Oracle 7 is available. Both HTML and PDF versions are available.

A searchable subset of  documentation is also available at Download the documentation you need to your desktop or laptop so you can browse the documentation while offline. Because of the documents’ large size, it is more efficient to browse through them while offline.
You also can ask questions on the Oracle forums. Many Oracle experts donate a lot of time answering questions posted here.
The Oracle Technology Network is an Oracle-sponsored site filled with useful resources including articles, sample code, and tutorials. It also contains links to the Oracle documentation and Oracle forums.
Oracle author Tom Kyte has been answering Oracle questions for many years on his web site Ask Tom. He’ll answer your question if he hasn’t already answered a similar question before and if the answer would be of wide interest.
If you’ve exhausted other alternatives, consider asking your question to the subscribers of the Oracle-L mailing list. To ask a question, send an e-mail message to To subscribe or unsubscribe, send an e-mail message to with the word subscribe or unsubscribe in the subject line.

Subquery Factoring and Analytic Functions

When tuning a database, it is useful to see at a glance the top five timed events for a range of time periods as in the following listing. It shows how much time was consumed by the top five timed events as well as average active sessions (AAS) and percentage of database time that the events represent.


SNAP_TIME  EVENT                         SECONDS  AAS   PCT
———– —————————- ——– —- —–
01/07 01:00 CPU used by this session       127.05 0.04 47.32
            RMAN backup & recovery I/O      74.75 0.02 27.84
            control file parallel write    17.59 0.00  6.55
            db file sequential read         16.89 0.00  6.29
            control file sequential read    10.56 0.00  3.93
01/07 02:00 log file sync                  269.49 0.07 36.58
            log file parallel write        266.31 0.07 36.15
            CPU used by this session      151.23 0.04 20.53
            db file sequential read         31.48 0.01  4.27
            control file parallel write    15.52 0.00  2.11
01/07 03:00 log file parallel write        867.97 0.24 31.45
            CPU used by this session      653.38 0.18 23.67
            log file sync                  608.85 0.17 22.06
            db file scattered read         273.92 0.08  9.93
            log buffer space               121.16 0.03  4.39
01/07 04:00 log file parallel write      2,169.10 0.60 30.36
            CPU used by this session    1,362.13 0.38 19.06
            db file scattered read       1,308.39 0.36 18.31
            db file sequential read        871.71 0.24 12.20
            log file sequential read      606.94 0.17  8.49
01/07 05:00 db file scattered read       3,154.11 0.88 82.63
            db file sequential read        264.16 0.07  6.92
            CPU used by this session       102.78 0.03  2.69
            log file parallel write        101.61 0.03  2.66
            control file parallel write    84.06 0.02  2.20
01/07 06:00 db file scattered read       3,327.81 0.92 71.27
            CPU used by this session       827.02 0.23 17.71
            db file sequential read        261.75 0.07  5.61
            read by other session          122.51 0.03  2.62
            control file parallel write    58.31 0.02  1.25


The following query can be used to produce such a listing. It makes extensive use of “subquery factoring” and “analytic functions.” The query uses the data from the Statspack tables but can easily be modified to use AWR tables. Note that you may only use AWR if you have licenses for both Oracle Enterprise Edition as well as Diagnostics Pack.
set linesize 132
set pagesize 10000
set tab off
set trimout on
set trimspool on
set sqlblanklines on
alter session set nls_date_format = ‘mm/dd hh24:mi’;
column event format a30
column time_spent_d format 9,999,999.90
column aas format 9999.90
column percentage format 999.90
break on snap_id on snap_time skip 1
 timed_events AS
 — Get wait time from STATS$SYSTEM_EVENT.
 — Select snapshots from start_snap_id to end_snap_id.
 — Convert wait microseconds into seconds.
 — Ignore the “Idle” class (wait_class = 6).
 — Wait classifications are found in V$EVENT_NAME.
 — Get CPU usage from the STATS$SYSSTAT table.
 — Convert CPU centiseconds into seconds.
 (SELECT   snap_id,
            time_waited_micro / 1000000 AS time_spent
     FROM   stats$system_event NATURAL JOIN v$event_name
    WHERE   snap_id between
              &&start_snap_id and &&end_snap_id
      AND   wait_class# != 6
            UNION ALL
   SELECT  snap_id,
            ‘CPU used by this session’ AS event,
            VALUE / 100 as time_spent
     FROM   stats$sysstat
    WHERE   snap_id between
              &&start_snap_id and &&end_snap_id
      AND   name = ‘CPU used by this session’),
 deltas AS
 — Use the LAG function to determine the increase.
 — Partition the rows by database startup time.
 — STATS$SNAPSHOT tells us when the database was started.
 (SELECT   snap_id,
            (snap_time – LAG (snap_time)
              OVER (PARTITION BY startup_time, event
                    ORDER BY snap_id)) * 86400
              AS snap_time_d,
            time_spent – LAG (time_spent)
              OVER (PARTITION BY startup_time, event
                    ORDER BY snap_id)
              AS time_spent_d
     FROM   timed_events NATURAL JOIN stats$snapshot),
 ranks AS
 — Use the RANK function to rank the events.
 — Also compute the percentage contribution of each event.
 (SELECT   snap_id,
              OVER (PARTITION BY snap_id
                    ORDER BY time_spent_d DESC)
              AS rank,
                   0, 0,
                   time_spent_d / SUM(time_spent_d)
              OVER (PARTITION BY snap_id) * 100)
              AS percentage
     FROM   deltas
    WHERE   time_spent_d IS NOT NULL)
 — Compute Average Active Sessions for each category.
 — List the top 5 events.
   SELECT   snap_id,
            substr(event, 1, 30) as event,
            time_spent_d / snap_time_d as aas,
     FROM   ranks
    WHERE   rank <= 5
 ORDER BY   snap_id,

Fresh Off the Press
Have you been anxiously scanning the skies for signs of a new edition of Tom Kyte’s classic book Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions? Hans, you’re in luck. Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition has hit the newsstands. Get it while it’s hot.
If you’d like to try something new, check out Oracle Database 11g Underground Advice for Database Administrators by April Sims, a compendium of “real-world reflections from an experienced DBA.”

Specialists Out and About 

Senior Staff Consultant Iggy Fernandez was spotted in the July 2010 issue of Oracle Magazine. Guess which book Iggy would take with him to the International Space Station.
Database Specialists is a Gold Sponsor of the Northern California Oracle Users Group (NoCOUG) and will exhibit at the NoCOUG Summer Conference on August 19. Please stop by and say hello.

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.
David Wolff
CEO, Database Specialists, Inc.
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *