SQL Grouping – Generating Comma Separated Lists

30 11 2009

The following question appeared on the OTN forums (http://forums.oracle.com/forums/thread.jspa?messageID=3901658):

I want to select Multiple rows into a single line in ‘Single Column Table’ .

For ex:

Employee table has only one column , named as empname . it has three rows

Select empname from emp;



My expected result: thambi,peter,antony

i did see some post regarding pivot query but did not get righ post for Single Column table.. Can someone help me.

One way to do this is with analytic functions.  The set up:






 DEPT EMPNAME            RN
----- ---------- ----------
   10 THAMBI              1
   10 PETER               2
   10 ANTHONY             3
   20 MICHAEL             1
   20 GEORGE              2 

Now that the employees are separated by department, SYS_CONNECT_BY_PATH is used to draw the related employees into a single row:



----- ------------------------------

Toy Project for Performance Tuning

30 11 2009

Over the years I have experimented with creating a custom performance tuning tool.  Below are a couple screen shots of the program.

Part 2:
https://hoopercharles.wordpress.com/2009/12/13/toy-project-for-performance-tuning-2/ Statspack Report Contents

30 11 2009

The following had to be cut from the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book, as it was about 4-5 pages of bulleted text.  Someone might find this useful:

 A level 7 Statspack report created by Oracle includes the following sections (see the file spcpkg.sql in the rdbms/admin directory of the Oracle home).
• Database and host information – instance name, start up time, and Oracle release.
• Snapshot summary – starting and ending times for the statistics, number of sessions, elapsed time, CPU time used by the instance, and total waits with CPU time experienced by the sessions.
• Cache sizes – buffer cache size, shared pool size, standard block size, and log buffer size.
• Load profile – rates per second and per transaction for redo generation, physical reads/writes, parses, executes, rollbacks, DB time, and a handful of other statistics.
• Instance efficiency indicators – several ratios including execute to parse %, parse CPU to parse elapsed %, % non-parse CPU, and latch hit %.
• Top timed events – top five elapsed time wait events unioned to the CPU time.
• Host CPU – user mode CPU time, kernel mode CPU time, and unused CPU time accumulated across all processes running on the server.
• Instance CPU – available CPU time, busy system-wide CPU time, and CPU time used by the instance.
• Memory statistics – system memory, SGA, and PGA used in the start and end periods.
• Time model statistics – allocation of CPU time and elapsed time used by the instance (V$SYS_TIME_MODEL).
• Foreground wait events – wait time experienced by the end-user sessions.
• Background wait events – wait time encountered by Oracle’s background processes (DBWn, LGWR, PMON, SMON, etc.)
• Wait events (fg and bg) – wait time for all foreground and background processes.
• Wait event histogram – indicates the percentage of each wait on a wait event which completed in a time range (less than 1ms, between 1ms and 1.999ms, between 2ms and 3.999ms, etc), listing the delta values from V$EVENT_HISTOGRAM.
• SQL ordered by CPU – lists the top SQL statements consuming the greatest number of CPU seconds in the time period, with a default minimum of 1% of the DB CPU statistic. Statistics for SQL statements are grouped on the OLD_HASH_VALUE and ADDRESS columns, which allows the same SQL statement to appear multiple times in the Statspack report if the plan for the SQL statement changed between the selected start and end Statspack snapshots (this is true for the remaining SQL sections as well).
• SQL ordered by gets – lists the top SQL statements requiring the largest number of consistent gets in the time period, with a default minimum of 10,000 consistent gets.
• SQL ordered by reads – lists the top SQL statements requiring the largest number of physical block reads in the time period, with a default minimum of 1,000 physical block reads.
• SQL ordered by executions – lists the top SQL statements with the greatest number of executions in the time period, with a default minimum of 100 executions.
• SQL ordered by parse calls – lists the top SQL statements with the greatest number of hard or soft parse calls in the time period, with a default minimum of 1,000 parse calls.
• SQL ordered by sharable memory – lists the top SQL statements with the greatest SHARABLE_MEM summed over the OLD_HASH_VALUE and ADDRESS columns (V$SQL), with a default minimum of 1MB of sharable memory.
• SQL ordered by version count – lists the SQL statements with the greatest number of child cursors in the library cache, with a default minimum of 20 child cursors.
• Instance activity statistics – lists the delta values of changed system-wide statistics (V$SYSSTAT), showing the delta value over the time period, the average delta value per second, and the average delta value per transaction (defined as user commits + user rollbacks).
• Instance activity statistics (absolute values) – number of sessions connected to the instance, number of open cursors, number of cursors held open in the session cursor cache, and the approximate number of redo log switches.
• OS statistics – CPU usage and memory statistics for the server (V$OSSTAT).
• OS statistics detail – lists the server CPU usage statistics for intermediate Statspack snapshots between the start and end snap IDs.
• IO statistics by function – lists the delta values of the view V$IOSTAT_FUNCTION, which indicates disk I/O characteristics for various read and write type operations (ARCH, Buffer Cache Reads, Data Pump, DBWR, Direct Reads, Direct Writes, LGWR, RMAN, Recovery, Smart Scan, Streams AQ, XDB, and Others).
• Tablespace IO statistics – number of block reads and writes, and average performance by tablespace.
• File IO statistics – number of block reads and writes, and average performance per datafile and temp file, showing the delta values from V$FILESTAT and V$TEMPSTAT.
• File read histogram statistics – indicates the number of single block reads per datafile which completed in a time range (less than 2ms, between 2ms and 3.999ms, between 4ms and 7.999ms, etc), showing the delta values from V$FILE_HISTOGRAM.
• Instance recovery statistics – estimated time to recover from a shutdown abort.
• Memory dynamic components – amount of SGA memory used by components (buffer cache, shared pool, java pool, large pool) and the PGA_AGGREGATE_TARGET at the start and end snap IDs.
• Memory resize operations – indicates dynamic resizing events of objects in the SGA when the SGA_TARGET initialization parameter is set (V$MEMORY_RESIZE_OPS).
• Buffer pool advisory – provides estimated changes in the number of physical reads and read times with smaller and larger memory allocations for the DEFAULT, KEEP, and RECYCLE buffer pools (V$DB_CACHE_ADVICE).
• Buffer pool statistics – delta values from the view V$BUFFER_POOL_STATISTICS, showing the number of consistent gets, physical block reads, physical block writes, buffer busy waits, free buffer waits, and write complete waits by buffer pool (DEFAULT, KEEP, RECYCLE, etc.).
• Buffer wait statistics – delta values from the view V$WAITSTAT, showing by block class (data block, extent map, file header block, free list, segment header, undo block, etc.) the number of buffer busy waits and wait time.
• PGA aggregate target statistics – shows the amount of memory specified for the PGA_AGGREGATE_TARGET as well as the amount of memory actually used.
• PGA aggregate target histogram – number of in-memory optimal memory executions, as well as one pass, and multi-pass temp tablespace executions for various memory size ranges.
• PGA memory advisory – provides estimated changes in the amount of megabytes written to the temp tablespace with smaller and larger allocations for the PGA_AGGREGATE_TARGET (V$PGA_TARGET_ADVICE).
• Process memory summary statistics, and by component – PGA memory allocations for SQL processing, PL/SQL processing, and other activities for the beginning and ending snap IDs.
• Enqueue activity – lists enqueues (such as row lock contention, sequence cache, etc.) which impacted database instance performance in the collection period.
• Undo Segment Statistics – lists the number of transactions, maximum query execution time, and number of undo blocks for each of the intermediate snap IDs between the start and end snap ID.
• Latch activity – get requests, percentage of misses, and wait time for latches, showing delta values from V$LATCH (buffer pool, cache buffers chains, redo allocation, shared pool, etc.).
• Latch sleep breakdown – lists latches which could not be immediately acquired after spinning while waiting for thelatch to become available.
• Latch miss sources – number of sleeps and misses for latches, shows the delta values from V$LATCH_MISSES.
• Mutex sleep – lists sleeps and wait time for mutexes (Cursor Pin – kkslce, Cursor Pin – kksfbc, Cursor Pin – kksSetBindType, Library Cache – kglkhfs1 52, etc.), shows the delta values from V$MUTEX_SLEEP.
• Segments by logical reads – ranks the tables, indexes, and other objects by the number of logical reads experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 10,000 consistent gets.
• Segments by physical reads – ranks the tables, indexes, and other objects by the number of physical block reads experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 1,000 physical block reads.
• Segments by row lock waits – ranks the tables, indexes, and other objects by the number of row lock enqueues experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 100 row lock waits.
• Segments by buffer busy waits – ranks the tables, indexes, and other objects by the number of buffer busy wait experienced for blocks in the segment during the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 100 buffer busy waits.
• Dictionary cache statistics – delta values from V$ROWCACHE, showing statistics for types including dc_awr_control, dc_constraints, dc_database_links, dc_files, dc_free_extents, etc.
• Library cache activity – delta values from V$LIBRARYCACHE, showing summarized BODY, INDEX, OBJECT, SQL AREA, TRIGGER, etc. requests, misses, invalidations, and reloads.
• Shared pool advisory – provides estimated changes in the number of library cache hits and load times with smaller and larger memory allocations for the shared pool (V$SHARED_POOL_ADVICE).
• Cache size changes – lists the default buffer cache size and the shared pool size at each intermediate Statspack snapshot, which indicates the effectiveness of the value specified for the SGA_TARGET and the minimum values specified for the DB_CACHE_SIZE and SHARED_POOL_SIZE.
• SGA target advisory – provides estimated changes in the number of physical reads and DB time with smaller and larger memory allocations for the shared pool (V$SGA_TARGET_ADVICE).
• SGA memory summary – lists the starting and ending memory sizes for memory regions in the SGA including Database Buffers, Fixed Size, Redo Buffers, and Variable Size (V$SGA).
• SGA breakdown difference – lists the starting and ending memory sizes for the first 35 sub-memory regions sorted by pool and then sub-pool in the SGA including shared pool – row cache, shared pool – sql area, and buffer_cache (V$SGASTAT).
• SQL memory statistics – average memory utilization per cursor in the library cache, total number of unique SQL_IDs, and total number of SQL_IDs including child cursors (V$SQLSTATS).
• init.ora parameters – shows all non-default initialization parameters.


To understand how to read Statspack reports, review the following links:

Book Review: Misc. Oracle Related Books

30 11 2009

Oracle Performance Tuning 101 (Oracle Press) by Gaja Krishna Vaidyanatha
This is the book which introduced me to correct methods for Oracle performance tuning through system and session level wait events. Throwing out the old methods of performance tuning (mostly cache-hit ratios), and focusing in on the real problem made significant differences in the production database (3.25 hour Scheduler run time dropped to roughly 29 minutes with the same hardware). But the book does have a couple errors. Page 44: “One of the classic myths about CPU utilization is that a system with 0 percent idle is categorized as a system undergoing CPU bottlenecks. The real issue here is how many processes are waiting for the CPU? It is perfectly okay to have a system with 0 percent idle, so long as the average runnable queue for the CPU is less than (2 x number of CPUs).” Queuing Theory, Metalink note:148176.1, Cary Millsap, Jonathan Lewis, Craig Shallahamer, and others have suggested that 100% utilization is not desirable for various reasons. But the book was written in 2001, so there are bound to be a couple mistakes which were found later.

Optimizing Oracle Performance (O’Reilly) by Cary Millsap
This is the book which introduced me to focused performance tuning methods through the use of 10046 traces, rather than just flipping a series of knobs and switches in the hope of reducing the system-wide wait events related to the knobs and switches. The book drives to the heart of the problem – what is the important process, is that important process too slow, if it is too slow exactly what is causing it to be too slow? Is it a bad execution plan, too many logical IOs, large sorts to disk, contention with other sessions, high server CPU utilization, slow redo log writing, etc. Page 264: ”But be careful: pegging CPU utilization at 100% over long periods often causes OS scheduler thrashing, which can reduce throughput. On interactive-only application systems, CPU utilization that stays to the right of the knee (queuing theory reference) over long periods of time is bad. When CPU utilization exceeds the knee in the response time curve, response time fluctuations become unbearable.”

Oracle Database Performance Tuning Guide by Oracle Corporation
The version for 11g R1/R2 for this title is much improved over previous versions. Covers wait events, execution plans, tracing, and more.

Expert One on One Oracle (Wrox, now available from Apress) by Thomas Kyte
Describes the problems related to database independent applications, differences between Oracle and other database platforms, internal architecture of Oracle, reading 10046 trace files, writing PL/SQL, and more. The most memorable part of the book for me was the Oracle war stories described in the book, and how to overcome the problems.

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress) by Thomas Kyte
I was initially disappointed with the book as the first chapter or two was nearly identical to those chapters in “Expert One on One Oracle”, but that disappointment faded quickly once the third chapter was reached. While the book lacked many of the Oracle war stories from the earlier title, the rest of the contents were interesting enough that I recently bought a second copy of the book. I have actually purchased two copies of “Expert Oracle Database Architecture”, and have read both book titles cover to cover. His books are excellent, highly detailed, and include a number of test cases which allow the reader to understand concepts, while reproducing the output using their databases. The test cases also offer a method to determine if something was true in Oracle 9i R1 or in 10g R1 is no longer true in 11g R2. I do not know if it is a fair comparison, but you might be able to consider his books as the Oracle “Concepts Guide” written so that it is easy to understand, with real-world examples, and real-world “war stories” (quite a number of these appear in “Expert One on One”). I consider Tom Kyte’s “Expert Oracle Database Architecture” as the critical starting point, conveying the foundation knowledge needed to understand the material presented in other books including “Optimizing Oracle Performance”, “Troubleshooting Oracle Performance”, “Cost-Based Oracle Fundamentals”, “Forecasting Oracle Performance”, and the “Expert Oracle Practices” book.

Expert Oracle Database 10g Administration (Apress) by Sam R. Alapati
This book served as the final reference for my conversion from Oracle 8i ( to Oracle 10g R2 ( The book is quite literally a dictionary for Oracle – just about any task that needs to be completed is described very well in this book. There are, however, a couple errors in the book. In addition to a couple cases where information was simply omitted, there is this quote from page 202: [i]“Indexes often become too large if there are many deletions, because the space used by the deleted values is not reused automatically by the index.” That statement is simply not true.

Oracle Insights Tales of the Oak Table (Apress) by several members of the Oak Table
Oracle war stories, performance tuning and more from the world’s most respected database tuning experts – what more could any one want except a follow up to the original book?

Oracle Database 2 Day DBA by Oracle Corporation
The basics of Oracle database administration, helpful when upgrading from a prior release, or if suddenly pushed into a DBA role.

Oracle Database Administrator’s Guide 10g by Oracle Corporation
Essentially, covers the foundation knowledge required to properly administer Oracle 10g R2. Much of the contents of the book may be found in the “Expert Oracle Database 10g Administration” and “Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions” books, but sometimes it is nice to read the official documentation.

Recovery Manager Reference and Backup and Recovery Advanced User’s Guide by Oracle Corporation
I read the versions of these books for Oracle 8i and was impressed with the depth of coverage in the books. At the time I felt that no book could be purchased which exceeded the depth, accuracy, nor easy of locating information as was contained in these two books. Things change.

Forecasting Oracle Performance (Apress) by Craig Shallahamer
This is a great book, with a lot of queuing theory and mathematics. Despite the review by Donald Burleson, there is no coverage of Statspack or AWR in the book. Paraphrased from the book: Once the CPU utilization reaches around 75%, queuing becomes significant, leading to performance problems. The book also describes why a 16 CPU server might not yield 8 or even 4 times the performance of a 2 CPU server. Great book, but a little too much theory.

Oracle Database 10g Insider Solutions (SAMS) by Arun Kumar
Of the Oracle books that I have read, this is the first that I read in about a week. Every page seemed to say “Hey, look at this neat feature” without a sufficient amount of supporting details to determine if the solution applies to servers costing less than ¾ of a million dollars with every available Oracle option, if the solutions are helpful or harmful, etc. This is one of the few books I read which I have never considered taking off the shelf to help implement an Oracle feature.

Oracle SQL High Performance Tuning (Prentice Hall) by Guy Harrison
I was very impressed with this book when I initially read the book in 2002. Since that time I have learned that some of the book contents are just plain wrong. What is wrong? A couple quotes come to mind: “On Windows NT/Windows 2000, the maximum number of blocks that can be read [in a multiblock read] cannot exceed 128K (for 32 bit versions).” “The size of the Oracle block can have an influence on the efficiency of full-table scans. Larger block sizes can often improve scan performance.” “Improving the hit rate in the buffer cache” section title.

Applied Mathematics for Database Professionals (Apress) by Lex de Haan and Toon Koppelaars
If ever I needed a book to remind me how much mathematics I have forgotten (my major was mathematics), this is the book. I started reading this book a year ago, but had to put it down to read more exciting titles once I made it half way through the book. I will finish this book, eventually.

Special Edition Using SQL by Que
A fairly thorough book (inline views are not covered), but also a bit old. Not specific to Oracle, but does include Oracle specific content.

Joe Celko’s SQL for Smarties: Advanced SQL Programming
Contains advanced set operations performed in SQL.  Not specific to Oracle.

Oracle Database SQL Language Reference
Primarily useful to cover the items missed by the above SQL books, such as using Oracle analytical functions.

Edit December 16, 2009: Fixed a couple of non-functional links.

Book Review: Expert Oracle Database 11g Administration

30 11 2009

Great Reference, Even with a Couple Accuracy Problems, September 5, 2009

This is a huge book, which could possibly best be described as the condensed encyclopedia of Oracle databases. The book covers everything from administration to performance tuning to using SQL*Plus to installing Oracle on Linux to using RMAN. While I did not read this book cover to cover, I did page through the book looking for interesting topics to read. I did read the author’s “Expert Oracle Database 10g Administration” book cover to cover a couple years ago and was at the time very impressed with that book. There were a couple small errors in the 10g book, repeated a couple times, but I commented to a couple people that the 10g book is by far the best and most thorough Oracle reference book that I had run across. The appendix at the back of the 10g book was very effective in helping me find exactly the right topic, and usually the right syntax for just about any task. The appendix in the 11g version of the book is just about as good. It appears that the author may have rushed the job of updating the 10g book for 11g R1 as quite a few screen captures still show Oracle versions such as and a couple other sections of the book also seem to be more specific to 10g R1 than 11g R1 (or 11g R2).

This book contains a lot of great and/or very helpful information, but while paging through the book I found a couple problems. Overlooking the problems, I would still recommend this book as a reference for Oracle 11g R1. The section on performance tuning is not my first choice for performance tuning information.

Problems found when paging through the book (I know that I probably missed several issues):

  • Page 90 mentions RAID 0+1 but not the more robust RAID 10.
  • Page 92 states “RAID 5 offers many advantages over the other levels of RAID. The traditional complaint about the `write penalty’ should be discounted because of sophisticated advances in write caches and other strategies that make RAID 5 more efficient than in the past.” Visit http://www.baarf.com/ to see the opinions of other DBAs.
  • Page 166 states “if you use an Oracle block size of 64KB (65,536 bytes)…” The maximum supported block size is 32KB, not 64KB, and some platforms support a maximum of a 16KB block size.
  • Page 171 states when suggesting the use of multiple block sizes in a single database “if you have large indexes in your database, you will need a large block size for their tablespaces.” “Oracle provides separate pools for the various block sizes, and this leads to better use of Oracle memory.” For those who have followed the multiple block size discussions over the years, it should be fairly clear that it is not a good idea to use multiple block sizes in a single database. Oracle’s documentation states that multiple block sizes are intended to be used only to support transportable tablespaces.
  • Page 181 states “The database writer process writes dirty blocks to disk under the following conditions… Every 3 seconds.” A check of the Oracle documentation will quickly confirm that this is not the case.
  • Page 182 states “Oracle further recommends that you first ensure that your system is using asynchronous I/O before deploying additional database writer processes beyond the default number – you might not need multiple database writer processes if so.” I think that I misread this several times as saying “do not enable multiple database writers unless you also plan to enable asynchronous I/O,” which would be an incorrect statement.
  • Page 190 states “this is why the buffer cache hit ratio, which measures the percentage of time users accessed the data they needed from the buffer cache (rather than requiring a disk read), is such an important indicator of performance of the Oracle instance.” The author provides a link on page 1161 to an article authored by Cary Millsap which discusses why a higher buffer cache hit ratio may not be ideal. This is definitely a step in the right direction regarding the buffer cache hit ratio, but it might be better to simply ignore the statistic.
  • Page 190 when describing the buffer cache hit ratio states that the formula for calculating the hit ratio is “hit rate = (1 – (physical reads)/(logical reads))*100”. The Oracle Database Performance Tuning Guide 11g Release 1 manual states that the correct formula is “1 – ((‘physical reads cache’)/(‘consistent gets from cache’+’db block gets from cache’))”
  • Page 395 states “11.1.0 is an alternative name for Oracle Database 11g Release 2.” Oracle 11g R2 was just released on September 1, 2009 and its version is
  • Page 402 correctly (according to the documentation) states that Oracle Enterprise Linux 4 and 5, as well as Red Hat Enterprise Linux are supported platforms for Oracle Database 11g, and correctly (according to the documentation) does not list Red Hat Enterprise Linux 3. Page 403 lists the required RPM packages for Red Hat Enterprise Linux 3, but ignores the supported Linux platforms.
  • Page 405 shows parameters that need to be set on Linux. This appears to be a direct copy of the parameters in the Oracle documentation, but the author did not include the net.core.wmem-max parameter. Note that Oracle will require different parameters than those specified in this book, but that is not the fault of the author.
  • Page 452 states that the COMPATIBLE parameter may be set to “10.2 so the untested features of the new Oracle version won’t hurt your application.” I think that this is misleading at best.
  • Page 466 states “If you’re supporting data warehouse applications, it makes sense to have a very large DB_BLOCK_SIZE – something between 8KB and 32KB. This will improve database performance when reading huge chunks from disk.” This is not quite a correct statement, especially if the DB_FILE_MULTIBLOCK_READ_COUNT is set correctly, or not set in the case Oracle or above is in use. 8KB is the standard block size, so I am not sure why the author groups it with the other block sizes in the very large block size group.
  • Page 477 states “the default value for the STATISTICS_LEVEL initialization parameter is TYPICAL. You need to use this setting if you want to use several of Oracle’s features, including Automatic Shared Memory Management.” This is an incomplete statement as a setting of ALL will also work.
  • Page 1055 shows the use of both CASCADE>=YES and CASCADE=>’TRUE’ with DBMS_STATS. I believe that TRUE is the correct syntax, but it should not be specified in single quotes.
  • Page 1067 states “subqueries perform better when you use IN rather than EXISTS.” The reality is that Oracle may very well automatically transform queries using IN syntax into queries using EXISTS syntax (or vice-versa), and may even transform both IN syntax queries and EXISTS syntax queries into standard join syntax.
  • Page 1074 stated that “inline stored functions can help improve the performance of your SQL statement.” The author then demonstrated this concept by converting a SQL statement with a three table equijoin accessing apparent primary and foreign keys of the tables into a SQL statement which accessed a single table and called two PL/SQL functions (per row), each of which queried one of the other two tables which were included in the original equijoin SQL statement. This approach does not seem to be a good idea given the number of additional context switches which will result, as well as the additional number of parse calls. In short, do not solve something in PL/SQL when it may be easily solved in SQL alone.
  • Page 1075 states “you should avoid the use of inequality and the greater than or equal to predicates, as they may also bypass indexes.” Something does not look right about that statement.
  • Page 1089 states “the indexes could become unbalanced in a database with a great deal of DML. It is important to rebuild such indexes regularly so queries can run faster.” Interesting suggestion – I believe that standard SELECT statements are classified as DML as they are definitely not DDL. An index cannot become unbalanced, and indexes rarely need to be rebuilt – see Richard Foote’s blog for the more details.
  • Page 1089 states “when you rebuild the indexes, include the COMPUTE STATISTICS statement so you don’t have to gather statistics after the rebuild.” Oracle 10g and above automatically collect statistics when building indexes, and I would assume that the same is true when rebuilding indexes.
  • Page 1108 states that “cpu_time is the total parse and execute time” when describing the columns found in V$SQL. It is actually the time measure in centiseconds (100th of a second) of the CPU utilization when executing the SQL statement, and probably also includes the CPU time for parsing the SQL statement, but I have not verified this.
  • Page 1154 provides a demonstration of finding sessions consuming a lot of CPU time. The example used the `CPU used by this session’ statistic rather than drilling through V$SYS_TIME_MODEL into V$SESS_TIME_MODEL. The example used the `CPU used by this session’ as a reason for examining parse CPU usage and recursive CPU usage. While it is good to check CPU usage of sessions, there are a couple problems with this approach. First, the `CPU used by this session’ statistic is not updated until the first fetch from a SQL statement is returned to the client. If the client was waiting for the last hour for the first row to be returned, the CPU utilization will show close to 0 seconds difference between the start of the SQL statement and the check of the `CPU used by this session’ statistic in V$SESSTAT – this is not the case for the statistics in V$SESS_TIME_MODEL, which are updated in near real-time. Second, the change (delta) in the statistic for the sessions was not determined – if one session had been connected for 120 days, it probably should have consumed more CPU time than a session connected for 30 minutes, and probably should not be investigated.

Even with the above issues, I would still recommend this book as a reference. For specific information on performance tuning, using RMAN, or installing on Linux, it might be a good idea to use a second book for deeper understanding of the process.

Book Review: Secrets of the Oracle Database

30 11 2009

Useful Book Even without a Lot of Real Undocumented Secrets, July 1, 2009

The “Secrets of the Oracle Database” book with the subtitle of “Advanced administration, tuning, and troubleshooting using undocumented features” follows the same pattern as most of the other Apress books, with very well researched and verified information with careful references to well regarded books and external resources. Many code samples and demonstrations are provided throughout the book in the format of: tell me about something, and then show me/prove to me that it actually works. The topics in this book reminded me a bit of the topics discussed in the book “Oracle 10g Insider Solutions,” except for the fact that the “Secrets of the Oracle Database” book actually makes an effort (a very thorough effort) at indicating which features are available with each version of Oracle (through, which features require additional cost licenses (such as the use of AWR), and provides a good enough example of the features so that the feature may be utilized with an understanding of why the feature should be used. The “Secrets of the Oracle Database” book even does a thorough job indicating the permissions and/or roles needed to leverage the various features. Specific items that I found to be helpful:

  • In most cases, commands are provided for Linux/Unix and the equivalents for Windows.
  • Good description of SYS.AUX_STATS and the various functions to view and populate the CPU stats are described in the book.
  • Very detailed description of raw 10046 trace files.
  • Detailed listing of the purpose of the various database tables related to Statspack.
  • Good summary of ORADEBUG functionality.
  • Provides a warning not to adjust the hidden (underscore) parameters unless under the supervision of Oracle support.

With the above in mind, why not give the book a 5 out of 5 rating?

Several Oracle features/behavior which are described as undocumented are in fact fairly well documented and/or discussed in Metalink notes, on various Oracle related blogs, and various Internet forums:

  • Page 143 describes ALTER USER IDENTIFIED BY VALUES as being undocumented while it is documented in Metalink (279355.1 last modified 27-OCT-2008 and 1051962.101 last modified 16-OCT-2008) and on several websites.
  • Page 136 states that the TRACE=TRUE parameter for the exp/imp and expdp/impdp utilities is undocumented while it is documented in a couple Metalink notes (271782.1 last modified 17-JAN-2005 and 286496.1 last modified 21-APR-2009) .
  • Page 337 states that DBMS_SYSTEM is undocumented while it is documented in a couple Metalink notes (286496.1 last modified 21-APR-2009 and 103267.1 last modified 20-NOV-2002 and 436036.1 last modified 09-MAR-2009 and DBMS_SUPPORT is described in 62294.1 last modified 25-OCT-2002), several books, and several websites.
  • Pages 271 and 371 state that the 10046 trace file format is undocumented while it is documented in a couple Metalink notes (39817.1 last modified 09-DEC-2008 and 376442.1 last modified 25-JUN-2009), two books referenced by this book (“Optimizing Oracle Performance” and “Troubleshooting Oracle Performance“), and several websites.
  • Page 299 states that “it is undocumented which parameter changes force the optimizer to consider a new plan,” after showing how changing OPTIMIZER_INDEX_COST_ADJ forced a change in the execution plan – but the book never went on to suggest checking V$SES_OPTIMIZER_ENV, V$SQL.OPTIMIZER_ENV_HASH_VALUE/V$SQL.OPTIMIZER_ENV, or a 10053 trace file.

There also appear to be a couple errors, or at least exceptions to some of the broad rules discussed in the book:

  • Page 29 states that “V$PARAMETER is built in such a way that it lists documented (non-hidden) parameters only,” with documented parameters being those which do not begin with one or two underscore characters. This is a correct statement, until one of the hidden parameters is modified, with a command such as the following: ALTER SYSTEM SET “_OPTIMIZER_UNDO_COST_CHANGE”=’′; (_OPTIMIZER_UNDO_COST_CHANGE is one of those parameters which are adjusted automatically when OPTIMIZER_FEATURES_ENABLE is set to a different value). Once the _OPTIMIZER_UNDO_COST_CHANGE parameter (or likely any _ parameter) is modified, it will then be listed along with the documented parameters in V$PARAMETER (tested on Oracle and
  • Page 45 states “Since SELECT statements don’t benefit from unused indexes… it may be worthwhile to drop unused indexes.” Richard Foote’s blog provides evidence that those indexes which appear to be unused indexes may actually provide the cost based optimizer statistical information that it would not otherwise have. Also, not every use of an index is recorded as a use of that index.

Given the title and subtitle of the book, I expected much more insight into the internals of Oracle databases. For instance on page 277 when describing the content of a 10046 trace file, the book stated that cr is defined simply as “consistent reads”, while cu is defined simply as “current blocks processed”. Page 357 gives a little more detail on the cu statistic “call parameter cu (current read) corresponds to the statistic db block gets.” This limited description is disappointing as a book described as providing advanced administration, tuning, and troubleshooting techniques should be able to tell much more about the cr and cu statistics. The same might also be stated about the coverage of V$SYS_TIME_MODEL, V$LOCK, V$SQL_SHARED_CURSOR and several other features discussed in the book.

Several of the chapters (5, 6, 8, 13, 14, 15, etc.) are very short, ranging from two to five pages in length. It might have been a better idea to combine several of these small chapters and/or provide more knowledge of Oracle’s internal behavior in those chapters. The author seems to favor the word “contrary” as it appears many times throughout the book. The back cover of the book indicates that this book should be read after “Troubleshooting Oracle Performance” and/or “Expert Oracle Database 11g Administration”. Reading either of these two books before reading the book “Secrets of the Oracle Database” might make some of the secrets disclosed in the book seem quite ordinary.

In summary, this book’s contents would likely seem as “secrets” for those DBA who have worked with Oracle for only a couple years and for those DBAs whose last book read was an Oracle 8.0.5 administrator’s guide. This book contains a great collection of disconnected information about Oracle (no building process from one item to the next), but it sometimes stops short of providing undocumented secrets to those DBAs who have read a couple good books recently, read a couple good blogs, and followed along on the discussions of a couple Oracle related forums. Much of the information may be obtained from other sources, but it is helpful that so much of the information is contained in a single book.

Book Review: Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning

30 11 2009

Even Five Years After Publication, this Book Still Belongs on the Bookshelf Next to the Other Worthy Books, June 16, 2009

For the last couple years I debated whether or not to buy this book, or for that matter any additional book, on the topic of Oracle’s wait event interface. I felt that between my copy of “Oracle Performance Tuning 101”, the enhanced wait event documentation in the free Oracle 11g Performance Tuning Guide, and the notes that I have collected from various web sites, that there was no need for another book in my collection which solely described wait events. To put it lightly, I was surprised by the depth of detail, the range of topics, the clarity of concepts, and the level of accuracy in this Oracle Press book, something I have come to expect usually only of the books offered by Apress.

This book, now five years old and covering through (apparently) Oracle, makes a very good attempt at covering a large percentage of the most frequently occurring of the 800+ wait events in Oracle 10g (there are nearly 1,000 in Oracle Not only does the book provide a typically detailed explanation of what each wait event means, but it also describes the related system/session statistics, related system parameters, and what the background processes might wait on while the foreground process waits on a different wait event (for example LOG FILE PARALLEL WRITE and LOG FILE SYNC, respectively).

Utilizing a wide range of system views while identifying performance problems and methods for correcting those performance problems, the book describes views including: v$system_event, v$session_event, v$session_wait, v$session_wait_history, v$system_wait_class, v$session_wait_class, v$sql_workarea, v$sql_workarea_active, v$latch_misses, v$sess_time_model, v$sql_shared_cursor, v$rowcache, x$kcqrs, v$resource, v$lock, v$session, v$segment_statistics, x$kcbfwait, x$kcbwds, x$ksolsfts, and several others.

Many system parameters, both normal and hidden, were described throughout the book. Better definitions of the system parameters are hard to find from other sources, especially in the case of the hidden parameters. The hidden parameters (those beginning with an _) should not be altered without first consulting Oracle support – I believe that the book only mentioned this fact once near the end. Having a good description of the meaning of the hidden parameters might help determine if another DBA working with the same database instance attempted to implement some sort of `silver bullet’ fix by adjusting one of the hidden parameters, but it might also be helpful when trying to troubleshoot a situation where the Oracle instance is behaving in an unexpected way. The book describes the following parameters: hash_area_size, sort_area_size, db_writer_max_writes, db_writer_processes, session_cached_cursors, _db_writer_chunk_writes, _log_io_size, _max_exponential_sleep, _max_sleep_holding_latch, _spin_count, _kghdsidx_count, _kgl_latch_count, _optim_peek_user_binds, _db_block_hash_latches, _db_block_hash_buckets, _wait_for_sync, _log_io_size, fast_start_mttr_target, _lm_dynamic_remastering, _db_block_max_cr_dba, _db_aging_freeze_cr, _db_aging_touch_time, _fairness_threshold, _bump_highwater_mark_count, _ash_enable, _ash_disk_write_enable, _ash_size, and several others.

There are a few sections of the book which may benefit from a re-write to update the information for Oracle 10g R2, 11g R1 or 11g R2 (currently in beta) versions of Oracle. There are also a few sections of the book which could use a bit more supporting information, and a few more sections which contain information which might not be 100% correct based on information made available on the Internet and in various books in the last five years. Regardless of any small problems which might be present in the book, it is an excellent companion to the books “Oracle Performance Tuning 101”, “Optimizing Oracle Performance”, “Cost-Based Oracle Fundamentals”, “Troubleshooting Oracle Performance”, and to some extent “Forecasting Oracle Performance”.

Possibly interesting comments that I noted as I read the book:

  • The discussion on page 28 and 29 of the V$SESSION_WAIT view is limited, and does not answer the question of when the times reported should be ignored. For example, it would have been helpful if the book stated something to the effect of: if the STATE column indicates that the session is Waiting, Waited Unknown Time, Waited Short Time, then the WAIT_TIME column is mostly meaningless. If the entry is reporting a STATE of Waited Known Time, then the WAIT_TIME is the number of 1/100th of a second that the session waited on the event. If the STATE is Waiting, then the SECONDS_IN_WAIT column is the actual number of seconds that the session has been in the wait, otherwise that column shows the approximate number of seconds since the last wait started.
  • A SQL statement is provided to decode the P1 value of an ENQUEUE type wait.
  • The SQL*Net message to client description on page 65 seems to be incorrect – the wait actually ends as soon as the data is handed off to the server’s network stack – the event cannot be used to detect network latency as stated: “… or the network latency delays may be causing the message delivery to take longer.”
  • Pages 143-144 contain a very nice summary of the differences between latches and locks.
  • The book frequently uses the adjective “high” when stating the boundary values between OK and problem conditions for wait events, but the book usually does not indicate the contextual meaning of “high” – for example on pages 151 and 157.
  • On page 184, the book states “Unindexed foreign keys are no longer an issue starting in Oracle 9i Database.” This does not appear to be 100% correct as sessions will still block other sessions with TM enqueues on the child table without the foreign key index if the primary key (or a column defined as unique) on the parent table, to which the foreign key on the child table points, is modified (even when modified to the same value).
  • Page 190 suggests creating all data segments with a minimum of 2 freelist groups, even in a single-instance database. It would have been helpful if the authors discussed some of the possible side effects related to multiple freelist groups, although a nice table was provided to show some of the performance benefits.
  • It might have been helpful if the book discussed how to correlate the average time spent on the LOG FILE SYNC wait event with the average time spent on the LOG FILE PARALLEL WRITE wait event – what does it mean if the average times are roughly the same, what does it mean if the average times are significantly different. What happens to the average LOG FILE SYNC wait time when the CPUs in the server become very busy?
  • Pages 202-203 state “There are many things that you and your system administrator can do to provide some relief to the log file sync wait by increasing the I/O throughput to the log files… placing your log files on raw device and binding the LUN in RAID 0 or 0+1 instead of RAID 5.” This advice comes a couple dozen pages after the book warned to make certain that the redo logs need to be on very reliable mount points. With that in mind, avoid RAID 0, and use RAID 10 rather than RAID 0+1.
  • The book states that a log buffer larger than 1MB is likely oversized, yet on recent versions of Oracle the log buffer size is controlled by the granule size, meaning that a 14MB log buffer is not out of the ordinary for database instances with large SGA sizes (resulting in a 16MB granule size).
  • The book suggests several times to increase the number of DBWR processes even on systems which support asynchronous writes. For example, on page 210 the book suggests modifying the number of DBWR processes before increasing the buffer cache size when encountering FREE BUFFER WAITS. It would have been helpful if the book explored some of the potential problems related to increasing the number of DBWR processes.
  • Very well written section on delayed block cleanout.
  • The section describing wait events in a RAC environment contains helpful information that is useful in non-RAC environments, so don’t skip this section of the book if RAC is not used in your environment.
  • The indented time model statistics section in the book looks nearly identical to the indented time model in the Oracle documentation, yet that section of the book lacks the descriptions of the time model statistics which are now present in the Oracle documentation.
  • The _ASH_ENABLED parameter mentioned on page 254 is not the Oracle approved method of disabling ASH collection. Oracle has since released an approved method of disabling ASH collection, see Metalink for the DBMS_AWR.DISABLE_AWR function.
  • Aside from the brief hint on page 259, the book fails to mention that the use of AWR/ADDM requires an additional cost license.
  • Appendix B contains a list of Oracle 10g enqueue wait events and a brief description of each wait event. Any description of the more obscure 10g enqueue waits is difficult to locate from other sources, including Oracle documentation, Metalink, and searches of the Internet.
  • Appendix C shows how to create a variety of dumps using both ALTER SYSTEM/SESSION and ORADEBUG. Dumps include data block dumps, file headers, control files, library cache, processstate, systemstate, and more.
  • The book often lists in which Oracle version a specific feature or change in behavior takes place.