On the Topic of Technology… 7

26 09 2014

September 26, 2014

(Back to the Previous Post in the Series)

As I was walking from the back of the facility where I work to my office just recently, I started repeatedly saying to myself as I approached the office door, “that huge computer was not here when I started calling my office a second home“.  I guess that I have worked at the same place for so long that I became blind to the interesting technology gadgets (if you can call multi-million dollar computers simply just gadgets) that surround me on a daily basis.

A couple of years ago BlackBerry released a small 8″ tablet, and Motorola released a 10″ Xoom tablet (I think that Motorola sold out their Mobility division to Google, who then sold that division to a farmer to use as fertilizer).  At the time the Xoom and BlackBerry tablets were released, my boss was really excited about the Apple iPads, but he did not care to spend $500 or more of his own money for a toy to use at home.  He had a Windows computer at home, but he seemed to always view that computer as excessively slow (mostly when viewing websites), even though he spent close to $3,000 on the computer six years earlier.  I am not much of an Apple fan, so I decided to have a little fun with my boss’ situation.

On the day that the Xoom tablet became available on Amazon, I placed an order for the tablet.  When it arrived, I brought it into work and showed the boss how quickly it could pull up web pages, along with its support of Adobe Flash playback (the iPad never supported Adobe Flash).  Yet, he continued to go on about the iPad, even showing me newspaper articles written by tech gurus that boasted about the fantastic features of the iPad.  A year earlier I had bought a small Windows netbook with a 10″ display, trying to convince him that such a netbook was even better than an iPad, so obviously that prior attempt failed.

When the BlackBerry tablet was released, I made a special trip to Best Buy just to grab the tablet.  I set the tablet up to work with the BlackBerry phone that I had at the time.  Oh neat, I am able to look at the company emails that I receive on the phone using the tablet – certainly, that will convince the boss that something is better than the iPad.  I showed my boss, who was also using a BlackBerry phone at the time, the neat BlackBerry tablet that could not only quickly pull up web pages (along with showing Adobe Flash contents), but could also show company emails and use the phone as a mobile hotspot for viewing web pages.  He spent a couple of minutes looking over the BlackBerry tablet before handing it back to me.  I found a couple more newspaper articles about the iPad on my desk in the weeks that followed.

On a Sunday afternoon, I decided to do some video testing with the two tablets, in a final attempt to convince the boss that something other than an iPad is ideal for his use at home.  I took the two tablets to my second home (that’s the place where my office, and all of those huge computers are located), and decided to do a head to head video test with the two tablets.  I planned to show the best looking video from the two tablets to the boss, and finally win him over.  I held the two tablets side-by-side as I walked down the isles of the huge computers.  As I walked, I wondered what that 40,000 pound part was doing in the big pit that was dug for one of the computers that was expected to arrive in another month or two.  No matter, I continued with my video testing, holding the tablets at head level as I walked.  I received some strange looks from the other employees as I walked about – I simply reassured the other employees that I was just trying to impress the boss.  I took the tablets home and processed the video from the tablets to eliminate meaningless portions of the video.  It seems that both tablets produced 720P video at either 29 or 30 frames per second that was virtually identical in video quality, but the BlackBerry video would playback directly in the Windows Media Player, while the Xoom video required conversion to a compatible format.  I showed the boss the resulting video, that not only could the BlackBerry tablet quickly pull up web pages (along with showing Adobe Flash contents), show company emails and use the phone as a mobile hotspot for viewing web pages, but also record 720P video that easily plays back on your Windows computer at home.  The boss thought for a minute or two, and then said, “did you have a chance to read Walt Mossberg’s latest Wall Street Journal article, there is a new iPad out now.”

Ah, fond memories.

I recently found the video clips that I recorded using the tablets back in 2011, and after reviewing the videos, I still can’t see much difference between the videos captured by either tablet.  The video looks nice when playing back, but pausing either video to take a screen capture results in a blurry single-frame mess 90% of the time.  The video showed the big pit that was dug for the large computer – yep, that pit now contains a multi-million dollar computer, and the wall that had been next to the pit was removed during a later expansion project.

In the nearly five years since I created the first article on this blog, I really have not said much about the company where I work.  I have posted a lot of Oracle Database book reviews on Amazon, as well as several reviews of security cameras.  Some readers on Amazon were convinced that I worked for a couple of book publishing companies, writing fake book reviews to promote the publishers books; people who actually read the book reviews should know better than that – the reviews are brutally honest.  Some other customers on Amazon thought that I was working for a security camera company and/or living in California; no, not the case.  As a result, I put together an article that shows some of the interesting technology and multi-million dollar computers that are located just feet from my office at work.  In the article, I included some still frames from the video that I captured in the walk through with the tablets in 2011.

Below are three pictures from the article that I recently posted.  I am still trying to come up with good captions for the last two pictures, captions such as “taking a break” and “breaking in a new truck” seem to come in mind.

Cincinnati CL-707 Laser Burner Slicing Through 1" X 10' x 20' Plate SteelIn the Deep EndNeed a Bigger TRuck

 





Book Review: Troubleshooting Oracle Performance, Second Edition

15 07 2014

July 15, 2014 (Modified August 21, 2014, August 22, 2014)

Extensively Researched with Detailed Analysis Covering a Broad Range of Oracle Database Performance Topics, Providing Insight that is Not Available from Other Sources
http://www.amazon.com/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/143025758X/

I pre-ordered this book in December 2013, having previously read the first edition of this book twice.  Once the printed copy of the second edition book arrived, I ordered the PDF companion for the book from Apress.  While the first edition of the book covered Oracle Database 9.2.0.1 through 11.1.0.6, the second edition targets versions 10.2.0.1 through 12.1.0.1+, the author successfully scrubbed the book of all information that is not relevant to the targeted Oracle versions.  Despite the removed obsolete content and the new page formatting that places approximately 17% more content per page, the page count for the second edition of the book grew by roughly 130 pages.

Some of the improvements in the second edition, when compared to the first edition of this book:

  • Extended explanation of the different definitions of the term cardinality.  Pg 19
  • Second edition of the book added a half page definition of the term cursor.  Pg 21
  • The description of V$SQL_CS_HISTOGRAM was omitted from the first edition of this book, and is now included.  Pgs 37-39
  • The Instrumentation section that was found in chapter 3 of the first edition is now relocated into chapter 2.  Pgs 42-48
  • A new section was added in this edition of the book that is intended to guide the reader in attacking performance problems using different procedures, based on whether or not the problem is reproducible.  Chapters 3 and 4
  • A new roadmap flow chart was added to the second edition, showing how to begin the performance tuning process.  Pg 104
  • Page 204 of the first edition of the book stated that it was not possible to retrieve a Statspack captured execution plan using DBMS_XPLAN – that statement was incorrect.  Page 306 of the second edition contains a corrected statement:  “Statspack stores execution plans in the stats$sql_plan repository table when a level equal to or greater than 6 is used for taking the snapshots. Even though no specific function is provided by the dbms_xplan package to query that repository table, it’s possible to take advantage of the display function to show the execution plans it contains.”
  • The second edition of the book includes a new SQL optimization techniques chapter – the book seems to be making a more dedicated effort to help the reader understand the decision process that determines when to use the various techniques to attack performance issues – explaining the decision tree for performance tuning.  Chapter 11, SQL Optimization Techniques, is a good example of the enhancements made to the second edition.

Over the last several years I have read (and reviewed) a number of Oracle Database performance related books, including the freely available Performance Tuning Guide that is part of the official Oracle Database documentation.  None of the books, including the official Performance Tuning Guide (at least three errors identified in the first 100 pages of the 12.1.0.1 version), is completely free of errors (wrong, omitted, or obsolete information).  However, this book sets the technical content accuracy bar extremely high for books that cover a broad-range of Oracle performance related topics.

As was the case for the first edition of this book, there are several factors that separate this book from the other broad-ranging Oracle Database performance books on the market:

  • For every feature that is described to help solve a problem, as many as possible of the benefits are listed, and an equal amount of attention is paid to the potentially wide-ranging problem areas of the various solutions.  Very few potential problems were overlooked in this book.  Some of the other books on the market only describe the potential benefits of implementing a feature, without discussing limitations or unintended side-effects.  One such example is the discussion of the CURSOR_SHARING parameter in two different books.  On page 434 the “Troubleshooting Oracle Performance” book the following warning is provided “Cursor sharing has a reputation for not being very stable. This is because, over the years, plenty of bugs related to it have been found and fixed… my advice is to carefully review Oracle Support note 94036.1…”  This quote is in contrast to the following quotes from pages 191 and 484 of the book “Oracle Database 12c Performance Tuning Recipes”, “Although there are some concerns about the safety of setting the CURSOR_SHARING parameter to FORCE, we haven’t seen any real issues with using this setting.”  “There are really no issues with setting the cursor_sharing parameter to a nondefault value, except minor drawbacks such as the nonsupport for star transformations, for example.”  (Reference) (Reference 2)
  • For nearly every feature described in the book, the book lists the licensing and version requirements (sometimes to a specific point release such as 10.2.0.3, 10.2.0.4, 11.2.0.4) that are required so that the reader is able to take advantage of the feature – these requirements are often listed early in the description of the feature (the monitoring/tuning discussion in chapters four and five contain several good examples).  The book commonly describes how to accomplish a task in the current Oracle Database release, as well as older releases, if the approach differs.  Some of the other books on the market inter-mix features and behaviors in various Oracle Database releases, without clearly distinguishing what will and what will not be available in the reader’s environment.
  • While many strong statements are made about Oracle Database in the book, there is no “hand waiving”, and there are very few inaccurate statements.  The book uses a “demonstrate and test in your environment” approach from cover to cover.  The downloadable scripts library is extensive with roughly 280 scripts and trace files, and those scripts often contain more performance information than what is presented in the book.  It is thus recommended to view the scripts and experiment with those scripts while the book is read.  The scripts are currently downloadable only from the author’s website.  In contrast, other books seem to take the approach of “trust me, I have performed this task 1,000 times and never had a problem” rather than the “demonstrate and test in your environment” approach as was used in this book.
  • Information in this book is densely packaged, without unnecessarily repeating information, and without giving the impression that sections of the book are a paraphrase of some other set of articles, a paraphrase of chapters in the official Oracle documentation, or a reprint of a page that was originally copyright in 1997.  Additionally, the information is well organized into a logical progression of topics, rather than each section of the book appearing as an island of unrelated information.
  • The well-placed graphics throughout the book support the contents of the book, rather than distract from the information that is described.
  • The book makes extensive use of forward and backward references to other sections in the book, as well as suggestions to review specific Oracle support documents and other books.  Some of the other books handle each chapter as an information silo, never (or rarely) mentioning specific content found elsewhere in the book.
  • In the acknowledgments section at the beginning of the previous book edition the author mentioned that his English writing ability is poor and that “I should really try to improve my English skills someday.”  While the English wording in the first edition of the book was easily understood, I took issue with the author’s repeated use of the phrase “up to” when describing features that exist in one Oracle Database release version or another.  The second edition of the book fixes that one issue that I pointed out, typically replacing the text with “up to and including”, and overall the technical grammar in the second edition of the book is among the best that I have seen in a couple years.  It appears that the author exercised great care when presenting his information on each page.  In contrast, some of the other Oracle Database book authors seem to be more concerned with slamming something onto the page so that something else that is more interesting could be introduced, in the process introducing sentences that can best be described as non-sense.
  • Almost without exception the issues that were identified as wrong, misleading, or incomplete in the first edition of the book were corrected in the second edition.  Unfortunately, the same cannot be said about other books that survived to see a second or third edition.

The second edition of “Troubleshooting Oracle Performance” is of value to Oracle Database administrators, programmers, and Oracle performance tuning specialists.  Chapter one of this book should be required reading for all people intending to be developers, regardless if the person intends to build advanced Oracle Database solutions or just simple Microsoft Access solutions.  One of my favor quotes from the book is found on page three, “Performance is not merely optional, though; it is a key property of an application.”  Ideally, this book should be read after reading the “Expert Oracle Database Architecture” book (or the Concepts Guide found in the Oracle Database documentation library), and before advancing to books such as “Cost-Based Oracle Fundamentals” or “Oracle Core: Essential Internals for DBAs and Developers”.

The full review of this book is quite long, currently covering the first 12 chapters (447 pages) of the book.  Over the next couple of weeks I will finish the remaining book chapters and post an updated review.  The index at the back of most Apress books seems to be limited in value, so I have tried to include a useful index as part of this review.

Foundation Knowledge, and Miscellaneous Tips:

  • The ten most common design problems: no formal logical database design; using generic tables (entity-attribute-value or XML); failing to use constraints, failing to implement physical design (partitioning, bitmap indexes, index organized tables, function-based indexes, etc); selecting the wrong data type for table columns (using a VARCHAR2 column to store dates); incorrect bind variable usage; failure to use RDBMS specific advanced features; avoiding PL/SQL when extensive data manipulation is required within a single database; excessive commits; non-persistent database connections.  Pgs 8-11
  • To avoid compulsive tuning disorder, there are three sources for identifying actual performance problems: user reported unsatisfactory performance, system monitoring reports time outs or unusual load, response time monitoring indicates performance that is outside of the parameters specified by the service level agreement.  Pg 11
  • Cardinality is the number of rows returned by an operation (estimated number of rows in an execution plan).  Cardinality = selectivity *num_rows  Pg 19
  • “A cursor is a handle to a private SQL area with an associated shared SQL area.”  Pg 21
  • Life cycle of a cursor is explained with a diagram.  Pgs 21-23
  • Good explanation of why hard parses and even soft parses should be minimized as much as possible.  Pg 26
  • Even though the OPTIMIZER_ENV_HASH_VALUE column value in V$SQL is different for a given SQL statement when the FIRST_ROWS, FIRST_ROWS_1, or FIRST_ROWS_1000 optimizer modes are used, that difference in the OPTIMIZER_ENV_HASH_VALUE column does not prevent a specific child cursor from being shared among sessions with those different optimizer modes.  “This fact leads to the potential problem that even though the execution environment is different, the SQL engine doesn’t distinguish that difference. As a result, a child cursor might be incorrectly shared.”  Pg 27 (Reference)
  • Example of using Oracle’s built-in XML processing to convert the REASON column found in V$SQL_SHARED_CURSOR into three separate regular Oracle columns.  Pgs 27-28
  • Benefits and disadvantages of using bind variables.  Pgs 29-31, 32-39
  • Adaptive cursor sharing (bind-aware cursor sharing) was introduced in Oracle 11.1.  The IS_BIND_SENSITIVE, IS_BIND_AWARE, and IS_SHAREABLE columns of V$SQL indicate if a specific child cursor was affected (created or made obsolete) by adaptive cursor sharing.  Pg 34
  • Bind aware cursors require the query optimizer to perform an estimation of the selectivity of predicates on each execution.   Pg 37
  • Definition of different types of database file reads and writes.  Pg 40
  • Basic definition of Exadata and the goals of Exadata smart scans.  Pg 41
  • The database engine allows dynamically setting the following attributes for a session: client identifier, client information, module name, and action name.  Pg 45
  • Example of setting the client identifier information using PL/SQL, OCI, JDBC, ODP.NET, and PHP.  Pgs 46-48
  • 10046 trace levels 0, 1, 4, 8, 16, 32, and 64 are described.  Pg 55
  • See $ORACLE_HOME/rdbms/mesg/oraus.msg for a list of all debugging event numbers – not available on all operating system platforms.  Pg 56
  • Using DBMS_PROFILER  requires the CREATE privilege on the PL/SQL code.  DBMS_HPROF just requires execute on DBMS_HPROF.  Pg 96
  • Very good description of the performance related columns in V$SESSION.  Pgs 115-116
  • The MMNL backgroup process collects active session history data once a second.  Pg 117
  • Real-time monitoring is available starting in version 11.1, and requires that the CONTROL_MANAGEMENT_PACK_ACCESS parameter to be set to diagnostic + tuning.  Pg 127
  • Real-time monitoring is enabled for SQL statements only if the executions require at least 5 seconds, if the SQL statement is executed using parallel processing, or if the MONITOR hint is specified in the SQL statement.  Pg 127
  • The author’s system_activity.sql script file produces output that is similar to the data contained in a Diagnostic Pack chart, without requiring a Diagnostic Pack license.  Pg 143
  • The author’s time_model.sql script samples the V$SYS_TIME_MODEL dynamic performance view and outputs results that show the parent, child, and grandchild relationship between the various statistics.  Pg 144
  • Use an interval of 20 to 30 minutes for the Statspack or AWR sample period to limit the distortion effects of the reported averages (important problems may be hidden if the sample period covers many hours of time).  Pg 152
  • AWR dictionary views have a DBA_HIST or CDB_HIST (12.1 multitenant environment) prefix.  Pg 152
  • While the procedure for using Statspack is no longer described in the documentation, the spdoc.txt file in the $ORACLE_HOME/rdbms/admin directory describes how to install, configure, and manage Statspack.  Pg 156
  • Statspack data can be captured at levels 0, 5, 6, 7, or 10 (see the book for an explanation of what is captured at each level).  Pg 157
  • The book provides an example of automating the collection of Statspack snaps, and automatically purging old Statspack snaps after 35 days.  Pg 159
  • The book describes in detail the various inputs that are provided to the query optimizer, including: system statistics, object statistics, constraints, physical design, stored outlines/SQL profiles/SQL plan baselines, execution environment/initialization parameters/client side environment variables, bind variable values/data types, dynamic sampling, and cardinality feedback.  The Oracle Database version, edition (Standard or Enterprise), and installed patches also potentially affect the plans generated by the query optimizer.  Pgs 170-172
  • Prior to version 11.1 the ANSI full outer join syntax was automatically translated into Oracle syntax utilizing a UNION ALL.  Pg 187
  • Access to the DBMS_STATS package is granted to public, but the GATHER_SYSTEM_STATISTICS role (automatically granted to DBA role) is required to change the system statistics in the data dictionary.  Pg 192
  • Bug 9842771 causes the SREADTIM and MREADTIM statistics to be incorrectly calculated when gathering system statistics on Oracle Database 11.2.0.1 and 11.2.0.2 unless patch 9842771 is installed.  Pg 197
  • The calculated CPU cost to access a specific table column is computed as the column position multiplied by 20.  Pg 203 (Reference)
  • When the mreadtim system statistic is null (has not been computed) or is smaller than the sreadtim system statistic, a formula is used to calculate the mreadtim static value when execution plans are generated.  When the sreadtim system statistic is 0 or not computed, a formula is used to derive a sreadtim statistic value when execution plans are generated.  If the MBRC system statistic is not set (or set to 0), the NOWORKLOAD system statistics are used.  See page 204 for the formulas.
  • The maximum number of buckets for histograms increased from 254 to 2048 in Oracle Database 12.1.  pg 213
  • Script to show tracked column usage that is used by DBMS_STATS.  Note that USER should be replaced with the schema name that contains the specified table.  Pg 242
  • When object statistics are collected using the default NO_INVALIDATE parameter value of DBMS_STATS.AUTO_INVALIDATE, cursors that depend on the object for which statistics were collected will be marked as invalidated after a random time period that is up to five hours (as determined by the value of the _OPTIMIZER_INVALIDATION_PERIOD  parameter; SQL statements using parallel execution will be immediately invalidated).  Pg 244
  • “Unfortunately, not all new features are disabled by this [OPTIMIZER_FEATURES_ENABLE] initialization parameter. For example, if you set it to 10.2.0.4 in version 11.2, you won’t get exactly the 10.2.0.4 query optimizer.”  Pg 277
  • “When the [memory utilization specified by the PGA_AGGREGATE_LIMIT] limit is reached, the database engine terminates calls or even kills sessions. To choose the session to deal with, the database engine doesn’t consider the maximum PGA utilization [for each session]. Instead, the database engine considers the session using the highest amount of untunable memory.”  Pg 296
  • EXPLAIN PLAN defines all bind variables as VARCHAR2, which may lead to unintended/unexpected data type conversion problems in the generated execution plan.  EXPLAIN PLAN also does not take advantage of bind variable peeking, further limiting EXPLAIN PLAN’s ability to accurately generate an execution plan for a previously executed SQL statement.  Unfortunately, there are times when EXPLAIN PLAN shows the correct predicate information, while the typically more reliable DBMS_XPLAN.DISPLAY_CURSOR, V$SQL_PLAN view, and V$SQL_PLAN_STATISTICS_ALL  view show incorrect predicate information for one or more lines in the execution plan.  Pgs 302-303, 336, 339, 346, 348
  • To have the query optimizer generate a 10053 trace whenever a specific SQL statement is hard parsed, execute the following command, replacing 9s5u1k3vshsw4 with the correct SQL_ID value: ALTER SYSTEM SET events ‘trace[rdbms.SQL_Optimizer.*][sql:9s5u1k3vshsw4]‘  pg 308
  • Description of the columns found in most execution plans.  Pgs 312-313
  • Description of the undocumented ADVANCED format parameter value for DBMS_XPLAN.  Pg 316
  • Adaptive execution plans, where the query optimizer in Oracle Database 12.1 is able to postpone some execution plan decisions (such as selecting a nested loops join vs. a hash join), requires the Enterprise Edition of Oracle Database.  Pg 349
  • The IS_RESOLVED_ADAPTIVE_PLAN column of V$SQL indicates whether or not an execution plan takes advantage of adaptive execution (use +ADAPTIVE in the format parameter of the DBMS_XPLAN call to see the adaptive portion of the execution plan).  Pg 351
  • Rather than just suggesting to the reader to add an index to avoid an unnecessary full table scan, the book includes the following important note: “For instance, if you add an index like in the previous example, you have to consider that the index will slow down the execution of every INSERT and DELETE statement on the indexed table as well as every UPDATE statement that modifies the indexed columns.”  Pg 361
  • “Simply put, hints are directives added to SQL statements to influence the query optimizer’s decisions. In other words, a hint is something that impels toward an action, rather than merely suggests one.”  Pg 363
  • “However, mixing comments and hints don’t always work. For example, a comment added before a hint invalidates it.”  This warning is an actual threat to intentionally included hints, and this warning was not included in the first edition of the book.  Pg 366
  • The default query block names assigned by the optimizer are: CRI$ CREATE INDEX statements, DEL$ DELETE statements, INS$ INSERT statements, MISC$ Miscellaneous SQL statements like LOCK TABLE, MRC$ MERGE statements, SEL$ SELECT statements, SET$ Set operators like UNION and MINUS, UPD$ UPDATE statements.  Use the QB_NAME hint to specify a different, non-default query block name for use with various hints.  Pg 369
  • “One of the most common mistakes made in the utilization of hints is related to table aliases. The rule is that when a table is referenced in a hint, the alias should be used instead of the table name, whenever the table has an alias.”  Pg 371
  • Cross reference between several initialization parameter values and the equivalent hint syntax.  Pg 373
  • A demonstration of creating a hacked stored outline for a SQL statement (use as a last resort when it is not possible to create a suitable outline using other techniques such as exp/imp or initialization parameter changes).  Pgs 381-387
  • SQL profiles, a feature of the Enterprise Edition with the Tuning Pack and the Diagnostic Pack options, are applied even when the upper/lowercase letters and/or the white space differs, and if the FORCE_MATCH parameter is set to true, a SQL profile may be applied even if the literals (constants) in a SQL statement differ.   While SQL profiles allow text normalization, stored outlines and SQL plan management do not support the same degree of text normalization.  Pgs 390, 394, 402
  • SQL plan management, which requires an Enterprise Edition license, could be considered an enhanced version of stored outlines.  Pg 402
  • “Inappropriate hints occur frequently in practice as the reason for inefficient execution plans. Being able to override them with the technique you’ve seen in this section [SQL plan baseline execution plan replacement (stored outlines are also capable of removing embedded hints using the techniques shown on pages 381-387)] is extremely useful.”  Pg 408
  • “What causes long parse times? Commonly, they are caused by the query optimizer evaluating too many different execution plans. In addition, it can happen because of recursive queries executed on behalf of dynamic sampling.”  Pg 433
  • “The values provided by the parse count (total) and session cursor cache hits statistics are subject to several bugs.”  Details are provided on pages 437-438

Suggestions, Problems, and Errors:

  • The following scripts are currently missing from the script library:
    — session_info.sql  Pg 45 (in the script library as session_attributes.sql per the book author).
    — ash_top_files.sql, ash_top_objects.sql, and ash_top_plsql.sql  Pg 136
    — search_space.sql  Pg 169
    — incremental_stats.sql  Pg 255  (Edit: Aug 22, 2014, now downloadable here)
    — copy_table_stats.sql  Pg 256  (Edit: Aug 22, 2014, now downloadable here)
    — optimizer_index_cost_adj.sql  Pg 288  (Edit: Aug 22, 2014, now downloadable here)
    — display_statspack.sql  Pg 306
    — dynamic_in_conditions.sql  Pg 499
    — fbi_cs.sql  Pg 506
    — reserve_index.sql should be reverse_index.sql  Pg 671 (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • Page 19 states that “selectivity is a value between 0 and 1 representing the fraction of rows filtered by an operation.”  I understand the intention of this statement, and the examples that follow the statement further clarify the author’s statement.  However, the “filtered” word in the statement seems to suggest that selectivity represents the fraction of the rows removed by an operation, rather than the rows that survived the filter at an operation.  This is just a minor wording problem that might cause the reader a little confusion when reading the book.  The author has addressed this issue in his errata list for the book.  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • Page 24, figure 2-3 has two entries for “Store parent cursor in library cache” – the second entry should show “Store child cursor in the library cache”, just as it is shown in figure 2-2 of the first edition of the book.  The author has addressed this issue in his errata list for the book.  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • Page 62 states, “The ALTER SESSION privilege required to execute the previous trigger can’t be granted through a role. Instead, it has to be granted directly to the user executing the trigger.”  I believe that the session executing the AFTER LOGON trigger, by default, would not need the ALTER SESSION privilege if the user creating the AFTER LOGON trigger had the ALTER SESSION privilege because the trigger is created by default with Definer’s Rights (Reference(Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • Page 72 states, “disk is the number of blocks read with physical reads. Be careful—this isn’t the number of physical I/O operations. If this value is larger than the number of logical reads (disk > query + current), it means that blocks spilled into the temporary tablespace.”  While the statement is correct, and supported by the test case output, it might be a good idea to also mention that prefetching (index or table) or buffer warm up could be another possible cause of the DISK statistic value exceeding the value of the QUERY statistic value (especially after the database is bounced or the buffer cache is flushed).  The PHYSICAL READS CACHE PREFETCH and PHYSICAL READS PREFETCH WARMUP statistics might be useful for monitoring this type of access.  (Reference)
  • Page 73 states, “In addition to information about the first execution, version 11.2.0.2 and higher also provides the average and maximum number of rows returned over all executions. The number of executions itself is provided by the Number of plan statistics captured value.”  It appears that the word “executions” should have been “execution plans”.  (Reference(Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • The book made the transition from views that require no additional cost licensing to views that require a Diagnostic Pack license on pages 114 and 115, without providing the reader a warning about the licensing requirements (such a warning is typically present in the book).  (Edit: August 21, 2014: I have discussed this bullet item with the book author.  Pages 114 and 115 describe the use of V$METRICV$METRICGROUP, and V$METRICNAME.  The author provided this link, which clearly describes which features, views, and scripts require a Diagnostic Pack license to access.  V$METRICV$METRICGROUP, and V$METRICNAME are not among the views that are listed, and it is possible to access data from V$METRIC when the CONTROL_MANAGEMENT_PACK_ACCESS parameter is set to NONE.  Some of the Oracle documentation (link) (link) (Oracle Support Doc ID 748642.1) indicate that “V$METRIC displays the most recent statistic values for the complete set of metrics captured by the AWR infrastructure.” and/or that V$METRIC is one of the Automatic Workload Repository views.  The book did not transition to views that require a Diagnostic Pack license on pages 114 and 115.)
  • There are a couple of minor typos in the book that do not affect the accuracy of statements made by the book.  For example, “… prevent it from happenning again” on page 149.  Most of these typos are easily missed when reading the book.  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • The book states on page 215, “This is especially true for multibyte character sets where each character might take up to three bytes.”  Per the Oracle Database globalization documentation, some charactersets, such as UTF-8 (AL32UTF8) may require up to four bytes per character.  The author has addressed this issue in his errata list for the book.
  • The book states on page 221, “For this reason, as of version 12.1, top frequency histograms and hybrid histograms replace height-balanced histograms.”  It appears based on the Oracle documentation that height-balanced histograms are not replaced if the histograms are created before the upgrade.  Additionally, if the ESTIMATE_PERCENT parameter is specified in the DBMS_STATS call, a height-balanced histogram will be created if the number of distinct values exceeds the number of buckets.  (Reference).  Page 239 makes a clarifying statement, “Also note that some features (top frequency histograms, hybrid histograms, and incremental statistics) only work when dbms_stats.auto_sample_size is specified [for the ESTIMATE_PERCENT parameter].”  “Work” may be a poor wording choice, “generated” may be a better choice of wording.  (Reference Oracle 12c Histogram Test(Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • The book states on page 282 about dynamic sampling level 11: “The query optimizer decides when and how to use dynamic sampling.  This level is available as of version 12.1 only.”  Oracle Database 11.2.0.4 also adds support for dynamic sampling level 11. (Reference(Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • When describing the output of DBMS_XPLAN, the book states, “Reads: The number of physical reads performed during the execution.”  The book should have clarified that the unit of measure for the Buffers, Reads, and Writes statistics is blocks.  Pg 313  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • The book states, “Syntactical errors in hints don’t raise errors. If the parser doesn’t manage to parse them, they’re simply considered real comments.”  That statement is correct for all hints except the oddly behaving IGNORE_ROW_ON_DUPKEY_INDEX hint, which will raise an “ORA-38917: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation” error, the CHANGE_DUPKEY_ERROR_INDEX hint which will raise an “ORA-38916: CHANGE_DUPKEY_ERROR_INDEX hint disallowed for this operation” error, and the RETRY_ON_ROW_CHANGE hint which will raise an “ORA-38918: RETRY_ON_ROW_CHANGE hint disallowed for this operation” error  if the hints are specified incorrectly.  Pg 365 (a similar comment is made at the top of page 371). (Reference) (Reference 2(Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • The book states, “The aim of using a prepared statement is to share a single cursor for all SQL statements and, consequently, to avoid unnecessary hard parses by turning them into soft parses.”  This statement should be clarified to point out that the aim is to share a single cursor for all _similar_ SQL statements (those that would have differed only by a literal/constant if bind variables were not used).  Pg 428  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
  • The book states, “Cursor sharing doesn’t replace literal values contained in static SQL statements executed through PL/SQL. For dynamic SQL statements, the replacement takes place only when literals aren’t mixed with bind variables. This isn’t a bug; it’s a design decision.”  This statement about dynamic SQL statements, at least for Oracle Database 11.2.0.2 and 12.1.0.1 (and possibly 10.2.0.2) is no longer true.  The author’s cursor_sharing_mix.sql script does shows literal value replacement when bind variables are also used for SQL statements executed outside PL/SQL.  Pg 434  (Reference Oracle Cursor Sharing Test).  (Edit: August 21, 2014: I have discussed this bullet item with the book author.  The quote from page 434 is describing the behavior of cursor sharing only as it relates to SQL statements executed within PL/SQL code.  I misinterpreted “dynamic SQL statements” to include ad-hoc SQL statements that are submitted by client-side applications (including SQL*Plus) that are built on-demand as a result of user input.  In technical terms, Dynamic implicitly implies that the SQL statements are executed within PL/SQL code.  The test script results do match what is printed in the book.  The author’s script demonstrated (specifically about the CURSOR_SHARING parameter, but likely applies to other functionality as well) that SQL executed directly may be handled differently when executed within PL/SQL code.)

Data Dictionary Views/Structures (the index at the back of the book misses most of these entries):

  • ALL_TAB_MODIFICATIONS  Pg 237
  • AUX_STATS$  (SYS schema)  Pgs 193, 196
  • CDB_ENABLED_TRACES  Pgs 58, 59, 60
  • CDB_HIST_SQL_PLAN  Pg 305
  • CDB_OPTSTAT_OPERATIONS  Pg 269
  • CDB_SQL_PLAN_BASELINES  Pg 409
  • CDB_SQL_PROFILES  Pgs 393, 399
  • CDB_TAB_MODIFICATIONS  Pg 237
  • COL$ (SYS schema)  Pg 242
  • COL_USAGE$ (SYS schema)  Pg 242
  • DBA_ADVISOR_EXECUTIONS  Pg 413
  • DBA_ADVISOR_PARAMETERS  Pg 413
  • DBA_AUTOTASK_TASK  Pg 259
  • DBA_AUTOTASK_WINDOW_CLIENTS  Pg 260
  • DBA_ENABLED_TRACES  Pgs 58, 59, 60
  • DBA_HIST_ACTIVE_SESS_HISTORY  Pg 420
  • DBA_HIST_BASELINE  Pgs 155, 156
  • DBA_HIST_COLORED_SQL  Pg 153
  • DBA_HIST_SNAPSHOT  Pg 154
  • DBA_HIST_SQL_PLAN  Pgs 305, 324
  • DBA_HIST_SQLTEXT  Pg 324
  • DBA_HIST_WR_CONTROL  Pg 153
  • DBA_OPTSTAT_OPERATION_TASKS  Pg 200
  • DBA_OPTSTAT_OPERATIONS  Pgs 200, 201, 269, 270
  • DBA_SCHEDULER_JOBS  Pgs 257-258
  • DBA_SCHEDULER_PROGRAMS  Pgs 258, 259
  • DBA_SCHEDULER_WINDOWS  Pgs 258, 260
  • DBA_SCHEDULER_WINGROUP_MEMBERS  Pg 258
  • DBA_SQL_MANAGEMENT_CONFIG  Pgs 417, 418
  • DBA_SQL_PLAN_BASELINES  Pgs 408, 409
  • DBA_SQL_PLAN_DIR_OBJECTS  Pg 230
  • DBA_SQL_PLAN_DIRECTIVES  Pg 230
  • DBA_SQL_PROFILES  Pgs 393, 399
  • DBA_TAB_MODIFICATIONS  Pg 237
  • DBA_TAB_STAT_PREFS  Pg 248
  • DBA_TAB_STATS_HISTORY  Pg 261
  • DBA_USERS  Pgs 235, 242
  • GV$INSTANCE  Pgs 60, 63
  • OBJ$ (SYS schema)  Pg 242
  • OL$ (OUTLN schema)  Pgs 381, 383
  • OL$HINTS (OUTLN schema)  Pgs 381, 383-384
  • OL$NODES (OUTLN schema)  Pg 381
  • OPTSTAT_HIST_CONTROL$ (SYS schema)  Pgs 246, 248
  • PLAN_TABLE  Pgs 203, 215, 216, 219-223, 225, 300
  • REGISTRY$ (SYS schema)  Pg 235
  • REGISTRY$SCHEMAS (SYS schema)  Pg 235
  • SQLOBJ$ (SYS schema)  Pgs 399, 410
  • SQLOBJ$DATA (SYS schema)  Pgs 399, 410
  • SQLPROF$ (SYS schema)  Pg 399
  • SQLPROF$ATTR (SYS schema)  Pg 399
  • STATS$LEVEL_DESCRIPTION  Pg 157
  • STATS$SQL_PLAN  Pg 306
  • STATS$STATSPACK_PARAMETER  Pgs 157, 158
  • USER_COL_PENDING_STATS  Pg 251
  • USER_IND_PENDING_STATS  Pg 251
  • USER_IND_STATISTICS  Pgs 209, 231-232, 251
  • USER_INDEXES  Pgs 354, 486, 487, 488, 519, 520, 523
  • USER_OBJECTS  Pg 300
  • USER_OUTLINE_HINTS pg 377
  • USER_OUTLINES  Pgs 377, 378, 379, 380
  • USER_PART_COL_STATISTICS  Pg 209
  • USER_PART_HISTOGRAMS  Pg 209
  • USER_SCHEDULER_JOBS  Pg 197
  • USER_STAT_EXTENSIONS  Pg 226
  • USER_SUBPART_COL_STATISTICS  Pgs 209, 253
  • USER_SUBPART_HISTOGRAMS  Pg 209
  • USER_TAB_COL_STATISTICS  Pgs 209, 211-213, 251, 252, 355
  • USER_TAB_COLS  Pg 227
  • USER_TAB_HISTGRM_PENDING_STATS  Pg 251
  • USER_TAB_HISTOGRAMS  Pgs 209, 214-215, 218-220, 222, 224-225, 251
  • USER_TAB_MODIFICATIONS  Pg 237
  • USER_TAB_PENDING_STATS  Pg 251
  • USER_TAB_STATISTICS  Pgs 209, 210-211, 215, 249, 251, 255, 264
  • USER_TAB_STATS_HISTORY  Pg 261
  • USER_TABLES  Pg 240
  • V$ACTIVE_SESSION_HISTORY  Pgs 119-121, 132, 138, 420, 424
  • V$BGPROCESS  Pg 63
  • V$CON_SYS_TIME_MODEL  Pgs 108, 419
  • V$CON_SYSSTAT  Pg 113
  • V$CON_SYSTEM_EVENT  Pg 111
  • V$CON_SYSTEM_WAIT_CLASS  Pg 110
  • V$DATABASE  Pg 324
  • V$DIAG_INFO  Pgs 63, 64
  • V$DISPATCHER  Pg 63
  • V$EVENT_HISTOGRAM  Pg 112
  • V$EVENT_NAME  Pg 109, 163
  • V$FILESTAT  Pg 195
  • V$FIXED_TABLE  Pg 233
  • V$METRIC  Pg 115, 142
  • V$METRIC_HISTORY  Pgs 115, 131
  • V$METRICGROUP  Pg 114
  • V$METRICNAME  Pg 114
  • V$MYSTAT  Pgs 114, 280
  • V$OSSTAT  Pgs 106, 131, 161
  • V$PARAMETER  Pgs 62, 296
  • V$PARAMETER_VALID_VALUES  Pg 278
  • V$PGASTAT  Pg 294-295
  • V$PROCESS  Pgs 64, 65
  • V$PX_PROCESS  Pg 63
  • V$SES_OPTIMIZER_ENV  Pg 374
  • V$SESS_TIME_MODEL  Pgs 107, 108, 110, 111, 144, 145, 419
  • V$SESSION  Pgs  45, 57, 61, 63, 64, 115-116, 117, 119, 145, 146, 304, 322, 390
  • V$SESSION_BLOCKERS  Pg 116
  • V$SESSION_EVENT  Pgs 111, 145
  • V$SESSION_WAIT  Pg 116
  • V$SESSION_WAIT_CLASS  Pg 110
  • V$SESSTAT  Pgs 113, 114, 145, 430, 437
  • V$SGASTAT  Pg 118
  • V$SHARED_SERVER  Pg 63
  • V$SQL  Pgs 25, 26, 126, 147, 304, 322, 378, 380, 386, 409
  • V$SQL_BIND_METADATA  Pg 31
  • V$SQL_CS_HISTOGRAM  Pgs 36, 37
  • V$SQL_CS_SELECTIVITY  Pgs 36, 37
  • V$SQL_CS_STATISTICS  Pg 36
  • V$SQL_HINT  Pgs 367, 376, 403
  • V$SQL_MONITOR  Pg 127
  • V$SQL_OPTIMIZER_ENV  Pg 374
  • V$SQL_PLAN  Pgs 303, 304, 305, 322, 336, 339,346, 348, 351
  • V$SQL_PLAN_STATISTICS  Pg 303
  • V$SQL_PLAN_STATISTICS_ALL  Pgs 303, 304, 320, 321, 322, 336, 339, 346, 348
  • V$SQL_SHARED_CURSOR  Pgs 27, 29, 31
  • V$SQL_WORKAREA  Pg 303
  • V$SQLAREA  Pgs 25,26, 126, 147, 277, 424
  • V$SQLSTATS  Pgs 126, 147, 424
  • V$STATNAME  Pgs 113, 280, 430, 437
  • V$SYS_OPTIMIZER_ENV  Pg 374
  • V$SYS_TIME_MODEL  Pgs 108, 144, 419
  • V$SYSAUX_OCCUPANTS  Pg 153
  • V$SYSMETRIC  Pg 115
  • V$SYSMETRIC_HISTORY  Pg 115
  • V$SYSSTAT  Pg 113
  • V$SYSTEM_EVENT  Pgs 111, 112
  • V$SYSTEM_WAIT_CLASS  Pgs 110, 143
  • WRI$_OPTSTAT_AUX_HISTORY  Pg 199
  • X$KSUSE  Pg 257

Initialization Parameters:

  • _CONVERT_SET_TO_JOIN  Pg 189
  • _OPTIMIZER_IGNORE_HINTS  Pg 372
  • _SQLMON_MAX_PLAN pg 127
  • _SQLMON_MAX_PLANLINES  Pg 127
  • _TRACE_FILES_PUBLIC  Pg 64
  • BACKGROUND_DUMP_DEST  Pgs 63, 259
  • BITMAP_MERGE_AREA_SIZE  Pgs 292, 297
  • CONTROL_MANAGEMENT_PACK_ACCESS  Pgs 103, 117, 127, 402
  • CPU_COUNT  Pg 131
  • CREATE_STORED_OUTLINES  Pg 377
  • CURRENT_SCHEMA  Pg 320
  • CURSOR_SHARING  Pgs 373, 434-436
  • DB_BLOCK_SIZE  Pgs 278, 281
  • DB_CACHE_SIZE  Pg 281
  • DB_FILE_MULTIBLOCK_READ_COUNT  Pgs 195, 198, 274, 278-281
  • DB_NAME  Pg 63
  • DIAGNOSTIC_DEST  Pg 63
  • HASH_AREA_SIZE  Pgs 292, 297
  • INSTANCE_NAME  Pg 63
  • JOB_QUEUE_PROCESSES  Pg 243, 261
  • MAX_DUMP_FILE_SIZE  Pgs 61, 62
  • MEMORY_MAX_TARGET  Pg 292
  • MEMORY_TARGET  Pg 292
  • NLS_SORT  Pg 28
  • OPEN_CURSORS  Pgs 432, 433, 437
  • OPTIMIZER_ADAPTIVE_FEATURES  Pgs 230, 351
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY  Pg 351
  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES  Pgs 405, 407, 417
  • OPTIMIZER_DYNAMIC_SAMPLING  Pgs 281-286, 373
  • OPTIMIZER_FEATURES_ENABLE  Pgs 202, 213, 277-278, 282, 373
  • OPTIMIZER_INDEX_CACHING  Pgs 191, 274, 289, 373
  • OPTIMIZER_INDEX_COST_ADJ  Pgs 191, 274, 286-289, 373
  • OPTIMIZER_MODE  Pgs 26, 274, 276-277, 373
  • OPTIMIZER_SECURE_VIEW_MERGING  Pgs 176, 289-291, 373
  • OPTIMIZER_USE_PENDING_STATISTICS  Pgs 250, 373
  • OPTIMIZER_USE_SQL_PLAN_BASELINES  Pg 415
  • PGA_AGGREGATE_LIMIT  Pgs 274, 293
  • PGA_AGGREGATE_TARGET  Pgs 274, 292, 295
  • PROCESSES  Pg 295
  • RESULT_CACHE_MODE  Pg 373
  • SERVICE_NAMES  Pg 59
  • SESSION_CACHED_CURSORS  Pgs 436, 437, 440
  • SESSIONS  Pg 281
  • SORT_AREA_RETAINED_SIZE  Pgs 292, 296-297
  • SORT_AREA_SIZE  Pgs 292, 296
  • SQLTUNE_CATEGORY  Pgs 395, 402
  • STAR_TRANSFORMATION_ENABLED  Pg 373
  • STATISTICS_LEVEL  Pgs 62, 106, 152, 199, 238, 261, 303, 322
  • TIMED_STATISTICS  Pgs 61, 62, 74, 105-106
  • TRACEFILE_IDENTIFIER  Pg 65
  • USE_PRIVATE_OUTLINES  Pg 384
  • USE_STORED_OUTLINES  Pgs 379, 385
  • USER_DUMP_DEST  Pg 63
  • WORKAREA_SIZE_POLICY  Pgs 274, 292

SQL Hints:

  • ALL_ROWS  Pgs 277, 373, 399
  • APPEND  Pg 367
  • APPEND_VALUES  Pg 367
  • BIND_AWARE  Pgs 38, 367
  • CACHE  Pg 367
  • CHANGE_DUPKEY_ERROR_INDEX  Pg 367
  • CLUSTER  Pg 366
  • COLUMN_STATS  Pg 401
  • CURSOR_SHARING_EXACT  Pgs 366, 373, 434
  • DRIVING_SITE  Pg 367
  • DYNAMIC_SAMPLING  Pgs 283, 366, 367, 373
  • DYNAMIC_SAMPLING_EST_CDN  Pg 283
  • ELIMINATE_JOIN  Pg 366
  • EXPAND_TABLE  Pg 366
  • FACT  Pg 366
  • FIRST_ROWS  Pg 373
  • FIRST_ROWS(n)  Pgs 277, 366, 373
  • FULL  Pgs 365, 366, 367, 368, 369, 371, 383, 406, 407, 408
  • GATHER_OPTIMIZER_STATISTICS  Pg 367
  • GATHER_PLAN_STATISTICS  Pgs 303, 320, 322, 366, 376, 403
  • HASH  Pg 366
  • IGNORE_OPTIM_EMBEDDED_HINTS  Pg 399
  • IGNORE_ROW_ON_DUPKEY_INDEX  Pg 367
  • INDEX  Pgs 366, 371, 407
  • INDEX_ASC  Pg 366
  • INDEX_COMBINE  Pg 366
  • INDEX_DESC  Pg 366
  • INDEX_FFS  Pg 366
  • INDEX_JOIN  Pg 366
  • INDEX_SS  Pg 366
  • INDEX_SS_ASC  Pg 366
  • INDEX_SS_DESC  Pg 366
  • INDEX_STATS  Pg 401
  • INLINE  Pgs 356, 376, 403
  • LEADING  Pg 366
  • MATERIALIZE  Pgs 367, 376, 403
  • MERGE  Pg 366
  • MODEL_MIN_ANALYSIS  Pg 367
  • MONITOR  Pgs 127, 367
  • NATIVE_FULL_OUTER_JOIN  Pg 366
  • NLJ_BATCHING  Pg 366
  • NO_APPEND  Pg 367
  • NO_BIND_AWARE  Pg 367
  • NO_CACHE  Pg 367
  • NO_CPU_COSTING – disables the CPU cost model, reverting to the I/O cost model.  Pg 191
  • NO_ELIMINATE_JOIN  Pg 366
  • NO_EXPAND  Pg 366
  • NO_EXPAND_TABLE  Pg 366
  • NO_FACT  Pg 366
  • NO_GATHER_OPTIMIZER_STATISTICS  Pg 367
  • NO_INDEX  Pg 366
  • NO_INDEX_FFS  Pg 366
  • NO_INDEX_SS_ASC  Pg 366
  • NO_INDEX_SS_DESC  Pg 366
  • NO_MERGE  Pg 366
  • NO_MONITOR  Pgs 127, 367
  • NO_NATIVE_FULL_OUTER_JOIN  Pg 366
  • NO_NLJ_BATCHING  Pg 366
  • NO_OUTER_JOIN_TO_INNER  Pg 366
  • NO_PARALLEL  Pg 367
  • NO_PARALLEL_INDEX  Pg 367
  • NO_PQ_CONCURRENT_UNION  Pg 367
  • NO_PQ_SKEW  Pg 367
  • NO_PUSH_PRED  Pg 366
  • NO_PUSH_SUBQ  Pg 366
  • NO_PX_JOIN_FILTER  Pg 367
  • NO_QUERY_TRANSFORMATION  Pg 366
  • NO_RESULT_CACHE  Pgs 367, 373
  • NO_REWRITE  Pg 366
  • NO_STAR_TRANSFORMATION  Pg 366
  • NO_STATEMENT_QUEUING  Pg 367
  • NO_SWAP_JOIN_INPUTS  Pg 366
  • NO_UNNEST  Pg 366
  • NO_USE_CUBE  Pg 366
  • NO_USE_HASH  Pg 366
  • NO_USE_MERGE  Pg 366
  • NO_USE_NL  Pg 366
  • NO_XML_QUERY_REWRITE  Pg 366
  • NO_XMLINDEX_REWRITE  Pg 366
  • OPT_ESTIMATE  Pg 400
  • OPT_PARAM  Pgs 250, 366, 373
  • OPTIMIZER_FEATURES_ENABLE  Pgs 278, 366, 373, 399
  • ORDERED  Pg 366
  • OUTER_JOIN_TO_INNER  Pg 366
  • PARALLEL  Pg 367
  • PARALLEL_INDEX  Pg 367
  • PQ_CONCURRENT_UNION  Pg 367
  • PQ_DISTRIBUTE  Pg 367
  • PQ_FILTER  Pg 367
  • PQ_SKEW  Pg 367
  • PUSH_PRED  Pg 366
  • PUSH_SUBQ  Pg 366
  • PX_JOIN_FILTER  Pg 367
  • QB_NAME  Pgs 367, 368
  • RESULT_CACHE  Pgs 367, 373
  • RETRY_ON_ROW_CHANGE  Pg 367
  • REWRITE  Pg 366
  • RULE  Pg 398
  • SET_TO_JOIN – used to enable the set (INTERSECT or MINUS) transformation to standard join syntax.  Pg 189
  • STAR_TRANSFORMATION  Pg 366
  • STATEMENT_QUEUING  Pg 367
  • SWAP_JOIN_INPUTS  Pg 366
  • TABLE_STATS  Pg 401
  • UNNEST  Pg 366
  • USE_CONCAT  Pg 366
  • USE_CUBE  Pg 366
  • USE_HASH  Pg 366
  • USE_MERGE  Pg 366
  • USE_MERGE_CARTESIAN  Pg 366
  • USE_NL  Pg 366
  • USE_NL_WITH_INDEX  Pg 366

Events:

  • 10046  Pgs 52-62
  • 10053  Pgs 307-309
  • 10132  Pgs 309-310, 371

Privileges:

  • ADMINISTER SQL MANAGEMENT  Pg 397
  • ADMINISTER SQL MANAGEMENT OBJECT  Pg 417
  • ADVISOR  Pg 397
  • ALTER ANY OUTLINE  Pg 385
  • ALTER ANY SQL PROFILE  Pg 397
  • ALTER SESSION  Pgs 61, 62
  • ANALYZE ANY  Pgs 200, 245, 248, 251, 261, 263
  • ANALYZE ANY DICTIONARY  Pgs 200, 245, 248, 261
  • CREATE ANY  Pg 96
  • CREATE ANY OUTLINE  Pg 385
  • CREATE ANY SQL PROFILE  Pg 397
  • CREATE JOB  Pg 244
  • CREATE TABLE  Pg 69
  • DROP ANY OUTLINE  Pg 385
  • DROP ANY SQL PROFILE  Pg 397
  • EXECUTE ANY PROCEDURE  Pg 385
  • MANAGE ANY QUEUE  Pg 244
  • MANAGE SCHEDULER  Pg 244
  • MERGE ANY VIEW  Pg 291
  • SELECT ANY DICTIONARY  Pgs 235, 246

Roles:

  • DBA  Pgs 57, 192, 244, 291, 385, 417
  • EXECUTE_CATALOG_ROLE  Pg 385
  • GATHER_SYSTEM_STATISTICS  Pg 192
  • HPROF_PROFILE (custom role)  Pg 101
  • SELECT_CATALOG_ROLE  Pgs 322, 324
  • SQL_TRACE (custom role)  Pg 61

Statistics:

  • background elapsed time  Pg 107
  • bytes received via SQL*Net from client  Pg 430
  • bytes sent via SQL*Net to client  Pgs 430-431
  • DB CPU  Pgs 108, 110, 111, 162, 163
  • DB time  Pgs 55, 107, 108, 109, 111, 120, 122, 126, 127, 132, 134, 137, 140, 162, 163, 165, 166, 419, 420, 424
  • hard parse elapsed time  Pg 107
  • parse count (total)  Pgs 437, 438
  • parse time elapsed  Pgs 107, 420
  • session cursor cache count  Pg 437
  • session cursor cache hits  Pgs 437, 438
  • sql execute elapsed time  Pg 165

Wait Events:

  • DB FILE SCATTERED READ  Pgs 75, 83
  • DB FILE SEQUENTIAL READ  Pgs 75, 83, 112, 163
  • DIRECT PATH READ TEMP  Pgs 75, 82
  • DIRECT PATH WRITE TEMP  Pgs 75, 82
  • latch free  Pg 436
  • library cache: mutex X  Pg 436
  • SQL*NET MESSAGE FROM CLIENT  Pgs 76, 111, 421-422, 423, 430, 432, 435
  • SQL*NET MESSAGE TO CLIENT  Pgs 76, 80, 430

Built-In Functions/Procedures:

  • ANALYZE  Pgs 207, 264
  • CAST  Pgs 284, 321
  • DBMS_APPLICATION_INFO.SET_ACTION  Pg 46
  • DBMS_APPLICATION_INFO.SET_CLIENT_INFO  Pg 46
  • DBMS_APPLICATION_INFO.SET_MODULE  Pgs 46, 66
  • DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER  Pg 413
  • DBMS_AUTO_TASK_ADMIN.DISABLE  Pg 260
  • DBMS_AUTO_TASK_ADMIN.ENABLE  Pg 260
  • DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE  Pg 58
  • DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE  Pg 58
  • DBMS_MONITOR.DATABASE_TRACE_DISABLE  Pg 60
  • DBMS_MONITOR.DATABASE_TRACE_ENABLE  Pg 60
  • DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE  Pg 59
  • DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE  Pg 59
  • DBMS_MONITOR.SESSION_TRACE_DISABLE  Pg 58
  • DBMS_MONITOR.SESSION_TRACE_ENABLE  Pg 57
  • DBMS_OUTLN.CLEAR_USED  Pg 380
  • DBMS_OUTLN.CREATE_OUTLINE  Pg 378
  • DBMS_OUTLN.DROP_BY_CAT  Pg 385
  • DBMS_OUTLN.UPDATE_BY_CAT  Pg 379
  • DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE  Pg 384
  • DBMS_OUTPUT.PUT  Pg 238
  • DBMS_OUTPUT.PUT_LINE  Pgs 238, 245
  • DBMS_RANDOM.NORMAL  Pg 209
  • DBMS_RANDOM.STRING  Pgs 209, 284
  • DBMS_RANDOM.VALUE  Pg 284
  • DBMS_SCHEDULER.CREATE_JOB  Pg 159
  • DBMS_SCHEDULER.DISABLE  Pg 259
  • DBMS_SCHEDULER.ENABLE  Pg 259
  • DBMS_SESSION.IS_ROLE_ENABLED  Pgs 61, 101
  • DBMS_SESSION.RESET_PACKAGE  Pg 295
  • DBMS_SESSION.SESSION_TRACE_DISABLE  Pg 61
  • DBMS_SESSION.SESSION_TRACE_ENABLE  Pg 61
  • DBMS_SESSION.SET_IDENTIFIER  Pg 46
  • DBMS_SPM.ALTER_SQL_PLAN_BASELINE  Pgs 414-415
  • DBMS_SPM.CONFIGURE  Pgs 417, 418
  • DBMS_SPM.CREATE_STGTAB_BASELINE  Pgs 415, 416
  • DBMS_SPM.DROP_SQL_PLAN_BASELINE  Pgs 408, 416
  • DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE  Pgs 411-412
  • DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE  Pgs 406, 408
  • DBMS_SPM.LOAD_PLANS_FROM_SQLSET  Pg 409
  • DBMS_SPM.PACK_STGTAB_BASELINE  Pgs 415, 416
  • DBMS_SPM.REPORT_AUTO_EVOLVE_TASK  Pg 413-414
  • DBMS_SPM.UNPACK_STGTAB_BASELINE  Pgs 415, 416
  • DBMS_SQLDIAG.DUMP_TRACE (no extra cost license required, dumps 10053 trace from library cache)  Pg 308
  • DBMS_SQLTUNE.ACCEPT_SQL_PROFILE  Pgs 392-393
  • DBMS_SQLTUNE.ALTER_SQL_PROFILE  Pgs 393-394
  • DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF  Pgs 396, 397
  • DBMS_SQLTUNE.CREATE_TUNING_TASK  Pg 390
  • DBMS_SQLTUNE.DROP_SQL_PROFILE  Pg 397
  • DBMS_SQLTUNE.DROP_TUNING_TASK  Pg 392
  • DBMS_SQLTUNE.EXECUTE_TUNING_TASK  Pg 390
  • DBMS_SQLTUNE.IMPORT_SQL_PROFILE  Pg 401
  • DBMS_SQLTUNE.PACK_STGTAB_SQLPROF  Pgs 396, 397
  • DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF  Pgs 396, 397
  • DBMS_SQLTUNE.REPORT_SQL_DETAIL  Pg 141
  • DBMS_SQLTUNE.REPORT_SQL_MONITOR  Pgs 127-130
  • DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST  Pg 127
  • DBMS_SQLTUNE.REPORT_TUNING_TASK  Pg 390
  • DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF  Pgs 396, 397
  • DBMS_STATS. GATHER_FIXED_OBJECTS_STATS  Pg 233
  • DBMS_STATS. UPGRADE_STAT_TABLE  Pg 245
  • DBMS_STATS.ALTER_STATS_HISTORY_RETENTION  Pgs 199-200, 261
  • DBMS_STATS.CONVERT_RAW_VALUE  Pg 212
  • DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR  Pg 212
  • DBMS_STATS.CONVERT_RAW_VALUE_ROWID  Pg 212
  • DBMS_STATS.COPY_TABLE_STATS  Pg 256-257
  • DBMS_STATS.CREATE_EXTENDED_STATS  Pgs 226, 229
  • DBMS_STATS.CREATE_STAT_TABLE  Pgs 200, 244
  • DBMS_STATS.DELETE_COLUMN_STATS  Pg 267, 268
  • DBMS_STATS.DELETE_DATABASE_PREFS  Pg 248
  • DBMS_STATS.DELETE_DATABASE_STATS  Pg 267
  • DBMS_STATS.DELETE_DICTIONARY_STATS  Pg 267
  • DBMS_STATS.DELETE_FIXED_OBJECTS_STATS  Pg 267
  • DBMS_STATS.DELETE_INDEX_STATS  Pg 267
  • DBMS_STATS.DELETE_PENDING_STATS  Pg 251
  • DBMS_STATS.DELETE_SCHEMA_PREFS  Pg 248
  • DBMS_STATS.DELETE_SCHEMA_STATS  Pg 267
  • DBMS_STATS.DELETE_SYSTEM_STATS  Pgs 192, 198, 201
  • DBMS_STATS.DELETE_TABLE_PREFS  Pg 248
  • DBMS_STATS.DELETE_TABLE_STATS  Pgs 252, 267
  • DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY  Pg 266
  • DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING  Pg 267
  • DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB  Pg 266
  • DBMS_STATS.DROP_EXTENDED_STATS  Pg 227
  • DBMS_STATS.DROP_STAT_TABLE  Pgs 200, 245
  • DBMS_STATS.EXPORT_SYSTEM_STATS  Pg 192
  • DBMS_STATS.GATHER_DATABASE_STATS  Pgs 233, 239
  • DBMS_STATS.GATHER_DICTIONARY_STATS  Pg 233
  • DBMS_STATS.GATHER_INDEX_STATS  Pg 233
  • DBMS_STATS.GATHER_SCHEMA_STATS  Pgs 233, 238, 240, 263
  • DBMS_STATS.GATHER_SYSTEM_STATS  Pgs 192, 194-197, 200, 201, 206
  • DBMS_STATS.GATHER_TABLE_STATS  Pgs 233, 249, 250, 252, 255, 263, 285
  • DBMS_STATS.GET_PARAM  Pgs 245, 246, 247
  • DBMS_STATS.GET_PREFS  Pg 247
  • DBMS_STATS.GET_STATS_HISTORY_RETENTION  Pgs 199, 261
  • DBMS_STATS.GET_SYSTEM_STATS  Pg 192
  • DBMS_STATS.IMPORT_SYSTEM_STATS  Pg 192
  • DBMS_STATS.LOCK_SCHEMA_STATS  Pg 262
  • DBMS_STATS.LOCK_TABLE_STATS  Pg 262
  • DBMS_STATS.OBJECTTAB  Pg 239
  • DBMS_STATS.PUBLISH_PENDING_STATS  Pgs 250-251
  • DBMS_STATS.PURGE_STATS  Pgs 199-200, 261
  • DBMS_STATS.REPORT_COL_USAGE  (note: use SET LONG 100000 in SQL*Plus before executing query)  Pgs 228, 242-243
  • DBMS_STATS.REPORT_SINGLE_STATS_OPERATION  Pg 201
  • DBMS_STATS.RESET_COL_USAGE  Pg 243
  • DBMS_STATS.RESET_GLOBAL_PREF_DEFAULTS  Pg 248
  • DBMS_STATS.RESET_PARAM_DEFAULTS  Pg 246
  • DBMS_STATS.RESTORE_DATABASE_STATS  Pg 262
  • DBMS_STATS.RESTORE_DICTIONARY_STATS  Pg 262
  • DBMS_STATS.RESTORE_FIXED_OBJECT_STATS  Pg 262
  • DBMS_STATS.RESTORE_SCHEMA_STATS  Pg 262
  • DBMS_STATS.RESTORE_SYSTEM_STATS  Pgs 192, 199-200
  • DBMS_STATS.RESTORE_TABLE_STATS  Pg 262
  • DBMS_STATS.SEED_COL_USAGE  Pg 228
  • DBMS_STATS.SET_DATABASE_PREFS  Pg 246, 247
  • DBMS_STATS.SET_GLOBAL_PREFS  Pg 246
  • DBMS_STATS.SET_PARAM  Pgs 245, 246
  • DBMS_STATS.SET_SCHEMA_PREFS  Pg 247
  • DBMS_STATS.SET_SYSTEM_STATS  Pg 192, 198
  • DBMS_STATS.SET_TABLE_PREFS  Pgs 247, 250, 251, 254, 256
  • DBMS_STATS.UNLOCK_SCHEMA_STATS  Pg 262
  • DBMS_STATS.UNLOCK_TABLE_STATS  Pg 262
  • DBMS_UTILITY.GET_TIME  Pg 280
  • DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL  Pg 153
  • DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE  Pgs 154-155
  • DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT  Pg 154
  • DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE  Pg 156
  • DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE  Pg 156
  • DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS  Pg 153
  • DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL  Pg 15
  • DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE  Pg 155
  • DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRIC  Pg 155
  • DBMS_XPLAN.DISPLAY  Pgs 301, 302, 306, 316-321, 349, 350, 368, 370, 371, 380, 382, 384, 395
  • DBMS_XPLAN.DISPLAY_AWR  Pgs 305-306, 311, 323-325
  • DBMS_XPLAN.DISPLAY_CURSOR  Pgs 249, 304-305, 311, 322-323, 336, 352, 353, 406, 407, 408
  • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE  Pgs 409, 410, 411
  • EXPLAIN PLAN  Pgs 299-302, 320, 336, 339, 346, 348, 349, 368, 370, 371, 380, 382, 384, 395
  • EXTRACT  Pgs 399, 410
  • EXTRACTVALUE  Pgs 399, 410
  • LAG  Pgs 203, 214, 224
  • LATERAL  Pg 180
  • LNNVL  Pgs 175, 346, 347, 502
  • NTILE  Pg 217
  • NULLIF  Pgs 354, 419, 424
  • PERFSTAT.STATSPACK.CLEAR_BASELINE  Pg 160
  • PERFSTAT.STATSPACK.MAKE_BASELINE  Pg 160
  • PERFSTAT.STATSPACK.SNAP  Pg 158
  • PGA_PKG.ALLOCATE  Pg 295
  • RATIO_TO_REPORT  Pgs 92, 111, 112, 120, 221, 223
  • STATSPACK.MODIFY_STATSPACK_PARAMETER  Pg 158
  • STATSPACK.PURGE  Pg 159
  • SYS_CONTEXT, SYS_CONTEXT(‘USERENV’,’SID’) , SYS_CONTEXT(‘USERENV’,’SESSION_USER’)  Pgs 45, 61, 64, 113, 321, 372, 390
  • SYS_OP_COMBINED_HASH  Pg 227
  • TABLE  Pgs 155, 249, 301, 302, 304, 305, 317-321, 323, 324, 349, 350, 352, 368, 370, 371, 380, 382, 384, 395, 399, 406-411, 625, 639, 669
  • UNPIVOT  Pg 158
  • UTL_RAW.CAST_TO_BINARY_DOUBLE  Pg 212
  • UTL_RAW.CAST_TO_BINARY_FLOAT  Pg 212
  • UTL_RAW.CAST_TO_BINARY_INTEGER  Pg 212
  • UTL_RAW.CAST_TO_NUMBER  Pg 212
  • UTL_RAW.CAST_TO_NVARCHAR2  Pg 212
  • UTL_RAW.CAST_TO_RAW  Pg 212
  • UTL_RAW.CAST_TO_VARCHAR2  Pg 212
  • XMLSEQUENCE  Pgs 399, 410
  • XMLTABLE  Pgs 201, 270
  • XMLTYPE  Pgs 399, 410

Packages:

  • DBMS_HPROF profiler  Pgs 89-96
  • DBMS_JOB  Pg 257
  • DBMS_PROFILER profiler  Pgs 89, 96-101
  • DBMS_SCHEDULER  Pg 257
  • DBMS_SQL_MONITOR  Pg 131
  • DBMS_SQLTUNE  Pgs 389-402
  • DBMS_STATS  Pgs 191-201, 207, 233-271
  • DBMS_XPLAN  Pgs 311-325

Oracle Provided Scripts:

  • ashrpt.sql  Pg 124
  • ashrpti.sql  Pg 124
  • awrddrpt.sql  Pg 161
  • awrrpt.sql  Pg 160
  • awrsqrpt.sql  Pg 306
  • coe_xfr_sql_profile.sql  Pg 401
  • dbmshptab.sql  Pg 90
  • profrep.sql  Pg 100
  • proftab.sql  Pg 97
  • spauto.sql  Pg 159
  • spcreate.sql  Pg 157
  • sppurge.sql  Pg 159
  • spreport.sql  Pg 161
  • sprepsql.sql  Pgs 166, 306
  • utlxplan.sql  Pg 300
  • utlxmv.sql  Pg 587
  • utlxrw.sql  Pg 586

Utilities:

  • /proc/loadavg command on Linux.  Pg 131
  • exp  Pg 381
  • grep  Pg 422
  • PLSHPROF (processes DBMS_HPROF output)  Pgs 89, 94
  • snapper.sql (utility script by Tanel Põder)  Pgs 145-147
  • SQL Developer  Pgs 95-96, 100
  • TKPROF  Pgs 67-79, 421-422, 426
  • TRCSESS  Pg 67
  • TVD$XTAT  Pgs 67, 79-88, 422-423, 426-427

Execution Plans:

Automatic Query Transformations:

    • Count transformation – transforms COUNT(column) to COUNT(*) when a NOT NULL constraint is present on the column.  Pg 174
    • Common sub-expression elimination – removes unnecessary/duplicate predicates from the WHERE clause.  Pgs 174-175
    • OR expansion – transforms SQL statements with OR clauses into multiple SQL blocks with UNION ALL specified between the SQL blocks. Pg 175
    • Simple view merging – merges query blocks using heuristics, used when the query blocks do not contain aggregations, set operators, hierarchical queries, MODEL clauses, or scalar subqueries (SELECT statement in the column list of a query).  Pgs 176-177
    • Complex view merging  – merges query blocks that contain aggregations using a cost-based approach, but does not work with hierarchical queries or queries containing GROUPING SETS, ROLLUP, PIVOT, or MODEL clauses.  Pgs 176-177
    • Select list pruning – removing selected columns from non-top-level query blocks that are not referenced by parent query blocks.  Pgs 177-178
    • Filter push down – pushing column restrictions from the WHERE clause of a parent query block into a child query block.  Pgs 178-179
    • Join predicate push down – push join predicates from a parent query block into an unmergeable child query block.  Pgs 179-180
    • Predicate move around – applying filter restrictions in one child query block (inline view) to another child query block due to join conditions that are specified between the parent and child query blocks.  Pg 180
    • Distinct placement – applies a DISTINCT filter to a child table before joining to a parent table, when the DISTINCT clause is specified for a SQL statement.  Pg 181
    • Distinct elimination – removes a specified DISTINCT filter when all columns of the primary key, a unique key, or the ROWID are selected from a table.  Pg 181
    • Group by placement – applies an aggregation function to a child table before joining that table to a parent table – a COUNT aggregation specified in a SQL statement is transformed into a COUNT aggregation of the child table before the join, and a SUM aggregation after the join.  Pgs 181-182
    • Order-by elimination – removes unnecessary ORDER BY clauses using heuristics from subqueries, inline views, and joined standard views.  Pg 182
    • Subquery unnesting – injects semi-join, anti-join, and scalar subqueries into the FROM clause; helps to eliminate the need to execute a subquery once for every row returned by the containing query block.  Pg 183
    • Subquery coalescing – combines equivalent semi-join and anti-join subqueries into a single query block.  Pgs 183-184
    • Subquery removal using window functions – replaces subqueries containing aggregate functions with analytic functions and standard joins.  Pgs 184-185
    • Join elimination – eliminates the parent table from a view when a declared foreign key is present between the tables, and no columns are selected from the parent table in the query block that selects from the view.  Pg 185
    • Join factorization – avoids repeatedly accessing one of the row sources by rewriting UNION ALL type queries so that predicates are applied to a second table as well as the UNION ALL operation before joining to the other row source.  Pgs 185-186
    • Outer join to inner join – when possible, converts left/right joins to equijoins, possibly due to other predicates that conflict with the left/right join syntax.  Pg 186
    • Full outer join to outer join, UNION ALL, and antijoin.  Pg 187
    • Table expansion – using an index scan on a partitioned table when the index is unusable for some table partitions.
    • Set to join conversion – converts INTERSECT or MINUS type queries to standard join queries, must specify the SET_TO_JOIN hint.  Pg 189
    • Star transformation.  Pg 189, chapter 14
    • Query rewrite with materialized views – accesses data stored in a materialized view, rather than the base tables, even though the materialized view is not referenced in the SQL statement (Enterprise Edition feature).  Pg 189, chapter 15

Execution Plan Operations:

    • AND-EQUAL  Pg 331
    • BITMAP AND  Pg 331
    • BITMAP KEY ITERATION  Pgs 333, 334
    • BITMAP MINUS  Pg 331
    • BITMAP OR  Pg 331
    • CONCATENATION  Pg 331
    • CONNECT BY PUMP  Pg 339
    • CONNECT BY WITH FILTERING  Pgs 333, 338-341
    • CONNECT BY WITHOUT FILTERING  Pg 331
    • COUNT STOPKEY  Pg 330
    • FILTER  Pgs 330, 333, 335-337
    • HASH JOIN  Pg 331
    • HASH JOIN ANTI  Pg 362
    • HASH JOIN ANTI NA  Pgs 347, 362
    • INDEX RANGE SCAN  Pg 327
    • INLIST ITERATOR  Pg 331
    • INTERSECTION  Pg 331
    • MERGE JOIN  Pgs 331, 332, 333
    • MINUS  Pgs 331, 362
    • MULTI-TABLE INSERT  Pg 331
    • NESTED LOOPS  Pgs 333, 334-335
    • PARTITION RANGE ITERATOR  Pg 331
    • RECURSIVE WITH PUMP  Pg 342
    • SELECT  Pg 327
    • SORT ORDER BY  Pg 327
    • SORT ORDER BY STOPKEY  Pg 330
    • SQL MODEL  Pg 331
    • STATISTICS COLLECTOR  Pgs 350, 351, 352
    • TABLE ACCESS BY INDEX ROWID  Pg 327
    • TEMP TABLE TRANSFORMATION  Pg 331
    • UNION-ALL  Pgs 331, 332, 333
    • UNION ALL (RECURSIVE WITH) BREADTH FIRST, DEPTH FIRST  Pgs 333, 341
    • UPDATE  Pgs 333, 337-338

Latches:

  •  CACHE BUFFERS CHAINS  Pgs 666, 668
  • LIBRARY CACHE  Pgs 25, 436
  • SHARED POOL  Pg 25

Oracle Error Messages:

  • Error: cannot fetch last explain plan from PLAN_TABLE.  Pg 320
  • ORA-00600: Internal error code, arguments.  Pg 569
  • ORA-00904: Invalid identifier.  Pg 537
  • ORA-00931: Missing identifier.  Pg 379
  • ORA-00942: Table or view does not exist.  Pg 569
  • ORA-01013: User requested cancel of current operation.  Pg 259
  • ORA-01017: invalid username/password; logon denied.  Pg 69
  • ORA-01039: Insufficient privileges on underlying objects of the view.  Pg 300
  • ORA-01408: Such column list already indexed.  Pg 524
  • ORA-01422: Exact fetch returns more than requested number of rows.  Pg 322
  • ORA-01722: Invalid number error.  Pg 657
  • ORA-02097: Parameter cannot be modified because specified value is invalid.  Pg 295
  • ORA-03113: End-of-file on communication channel.  Pg 296
  • ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT.  Pg 296
  • ORA-07445: Exception encountered: core dump [exception].  Pg 569
  • ORA-12026: Invalid filter column detected.  Pg 596
  • ORA-12827: Insufficient parallel query slaves available error.  Pgs 624, 625
  • ORA-12838: Cannot read/modify an object after modifying it in parallel.  Pgs 640, 644
  • ORA-12839: Cannot modify an object in parallel after modifying it.  Pg 640
  • ORA-12850: Could not allocate slaves on all specified instances.  Pg 614
  • ORA-13541: System moving window baseline size greater than retention.  Pg 156
  • ORA-13717: Tuning Package License is needed for using this feature.  Pg 402
  • ORA-13859: Action cannot be specified without the module specification.  Pg 59
  • ORA-20005: Object statistics are locked.  Pgs 238, 263
  • ORA-23538: Cannot explicitly refresh a NEVER REFRESH materialized view.  Pg 588
  • ORA-26040: Data block was loaded using the NOLOGGING option.  Pg 642
  • ORA-32313: REFRESH FAST of <mview> unsupported after PMOPs.  Pg 594
  • ORA-38029: Object statistics are locked.  Pg 264
  • ORA-48913: Writing into trace file failed, file size limit [] reached.  Pg 62

Blog articles that reference the “Troubleshooting Oracle Performance, Second Edition” book:

FIRST_ROWS_n Optimizer Mode – What is Wrong with this Statement?





FIRST_ROWS_n Optimizer Mode – What is Wrong with this Statement?

8 06 2014

June 8, 2014

It has been nearly two years since I last wrote a review of an Oracle Database related book, although I have recently written reviews of two Microsoft Exchange Server 2013 books and a handful of security cameras in the last two years.  My copy of the second edition of the “Troubleshooting Oracle Performance” book arrived yesterday, so I have been spending some time reading the new edition and comparing it with a PDF version of the first edition.  My initial impressions of the second edition, based on the first 30 pages, are almost entirely positive, although I did find one glitch in the second edition so far.

At the top of page 27 is the following caution, which did not appear in the first edition of the book:

“As the previous example shows, the optimizer_mode column doesn’t show the right value for child number 1. In fact, the column shows FIRST_ROWS instead of FIRST_ROWS_1. The same behavior can be observed with FIRST_ROWS_10, FIRST_ROWS_100, and FIRST_ROWS_1000 as well.  This fact leads to the potential problem that even though the execution environment is different, the SQL engine doesn’t distinguish that difference. As a result, a child cursor might be incorrectly shared.”

So, what is wrong with the above statement?  The above statement, if true, would seem to indicate a bug in Oracle Database, one that has potentially serious performance side-effects; the opening paragraph of the “Troubleshooting Oracle Performance” book states, “Performance is not merely optional, though; it is a key property of an application,” so as an extension, possibly a performance problem should be addressed in a similar fashion as a bug.  I wrote the following into my notes about the above quoted caution section from the book:

This statement might be incorrect, the OPTIMIZER_ENV_HASH_VALUE column in V$SQL should show a different value if the OPTIMIZER_MODE changes from FIRST_ROWS_1 to FIRST_ROWS_1000 and the same SQL statement is executed a second time.

So, I set up a test case to determine whether the above caution quote from the book is correct, or if what I wrote into my notes about the book is accurate.  Actually, both of the above seemingly mutually exclusive statements are correct based on the results of my test case script.  So, how is that possible?  The key is whether or not the SQL statement’s cursor is flushed out of the library cache, or if the child cursor is somehow marked as unshareable (possibly due to statistics collection) between the two executions of the same SQL statement with different FIRST_ROWS_n OPTIMIZER_MODE parameter values.  The  OPTIMIZER_ENV_HASH_VALUE column in V$SQL will be different (at least in Oracle Database 11.2.0.1) if the OPTIMIZER_MODE changes from FIRST_ROWS_1 to FIRST_ROWS_1000 and a hard parse is required, but the change of the OPTIMIZER_MODE from the first value to the second is NOT sufficient to force that hard parse.

The statement found in the “Troubleshooting Oracle Performance” book is correct, so what is wrong with the statement?  If performance problems are considered bugs, then this particular issue seems to point to a bug in Oracle Database, where an opportunity to re-optimize a SQL statement is missed.  Possibly equally important to recognize is that the OPTIMIZER_ENV_HASH_VALUE that is found in V$SQL is NOT used to determine if a SQL statement must be re-optimized (at least in Oracle Database 11.2.0.1) because the changed OPTIMIZER_MODE resulted in a different OPTIMIZER_ENV_HASH_VALUE when a hard parse happens.

So, how is this seemingly minor hard parsing issue a potential problem?  Consider a case where Oracle’s query optimizer should predict that the cardinality of an operation will be 990 rows.  With the OPTIMIZER_MODE set to FIRST_ROWS_1000, the query optimizer will optimize the SQL statement just as if the OPTIMIZER_MODE were set to ALL_ROWS because the predicted cardinality is less than 1000.  So, the optimizer might correctly select to perform a full table scan; while with the OPTIMIZER_MODE set to FIRST_ROWS_1, the predicted cardinality for the same operation might be just 2 (or 1), thus leading to a possibly inefficient index access path if one exists.

If the quote on page 27 does not contain the glitch that I found in the first 30 pages of the book, where is the glitch?  I actually found two glitches in the first 27 pages of the book, but I will not mention those glitches at this time.

It might be interesting to see if later releases of Oracle Database actually do force a hard parse for a SQL statement when the OPTIMIZER_MODE changes from FIRST_ROWS_1 to FIRST_ROWS_1000. (or if two sessions have the different FIRST_ROWS_n OPTIMIZER_MODE settings, and each execute the same SQL statement). Here is the test script that I constructed:

DROP TABLE T1 PURGE;
 
SET LINESIZE 140
SET PAGESIZE 1000
SET TRIMSPOOL ON
 
SELECT
  VERSION
FROM
  V$INSTANCE;
 
CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  MOD(ROWNUM,500) C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;
 
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
 
ALTER SYSTEM FLUSH SHARED_POOL;
 
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1;
 
SELECT C1 FROM T1 WHERE C2=2;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT
  SQL_ID,
  CHILD_NUMBER,
  OPTIMIZER_MODE,
  OPTIMIZER_ENV_HASH_VALUE,
  PLAN_HASH_VALUE
FROM
  V$SQL
WHERE
  SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1000;
 
SELECT C1 FROM T1 WHERE C2=2;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT
  SQL_ID,
  CHILD_NUMBER,
  OPTIMIZER_MODE,
  OPTIMIZER_ENV_HASH_VALUE,
  PLAN_HASH_VALUE
FROM
  V$SQL
WHERE
  SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
SELECT
  CHILD_NUMBER,
  NAME,
  VALUE
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='bqx2tj39jw1f5'
  AND NAME='optimizer_mode'
ORDER BY
  NAME;
 
ALTER SYSTEM FLUSH SHARED_POOL;
 
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1000;
 
SELECT C1 FROM T1 WHERE C2=2;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT
  SQL_ID,
  CHILD_NUMBER,
  OPTIMIZER_MODE,
  OPTIMIZER_ENV_HASH_VALUE,
  PLAN_HASH_VALUE
FROM
  V$SQL
WHERE
  SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1;
 
SELECT C1 FROM T1 WHERE C2=2;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT
  SQL_ID,
  CHILD_NUMBER,
  OPTIMIZER_MODE,
  OPTIMIZER_ENV_HASH_VALUE,
  PLAN_HASH_VALUE
FROM
  V$SQL
WHERE
  SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
SELECT
  CHILD_NUMBER,
  NAME,
  VALUE
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='bqx2tj39jw1f5'
  AND NAME='optimizer_mode'
ORDER BY
  NAME;

The script is broken into two halves, with an ALTER SYSTEM FLUSH SHARED_POOL; separating the two halves of the script.  The execution plan is displayed after each execution of the test SQL statement to show the optimizer’s predicted cardinality for the TABLE ACCESS FULL operation as well as the calculated cost and estimated number of bytes returned from that operation.  The query optimizer’s calculated cost for an operation could cause the execution plan to change, although such a change could not happen in this test case script.

Below is the output that I received on Oracle Database 11.2.0.1 for the first half of the script.  Note that I have removed excessive blank lines and the output of the test SQL statement.  Notice that the OPTIMIZER_ENV_HASH_VALUE is displayed as 1002285490 when starting with the FIRST_ROWS_1 OPTIMIZER_MODE:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
 
System altered.
 
SQL> ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1;
 
Session altered.
 
SQL> SELECT C1 FROM T1 WHERE C2=2;
 
20 rows selected.
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bqx2tj39jw1f5, child number 0
-------------------------------------
SELECT C1 FROM T1 WHERE C2=2
 
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    16 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=2)
 
SQL> SELECT
  2    SQL_ID,
  3    CHILD_NUMBER,
  4    OPTIMIZER_MODE,
  5    OPTIMIZER_ENV_HASH_VALUE,
  6    PLAN_HASH_VALUE
  7  FROM
  8    V$SQL
  9  WHERE
 10    SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
SQL_ID        CHILD_NUMBER OPTIMIZER_ OPTIMIZER_ENV_HASH_VALUE PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------ ---------------
bqx2tj39jw1f5            0 FIRST_ROWS               1002285490      3617692013
 
SQL> ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1000;
 
Session altered.
 
SQL> SELECT C1 FROM T1 WHERE C2=2;
 
20 rows selected.
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bqx2tj39jw1f5, child number 0
-------------------------------------
SELECT C1 FROM T1 WHERE C2=2
 
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    16 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=2)
 
SQL> SELECT
  2    SQL_ID,
  3    CHILD_NUMBER,
  4    OPTIMIZER_MODE,
  5    OPTIMIZER_ENV_HASH_VALUE,
  6    PLAN_HASH_VALUE
  7  FROM
  8    V$SQL
  9  WHERE
 10    SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
SQL_ID        CHILD_NUMBER OPTIMIZER_ OPTIMIZER_ENV_HASH_VALUE PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------ ---------------
bqx2tj39jw1f5            0 FIRST_ROWS               1002285490      3617692013
 
SQL>
SQL> SELECT
  2    CHILD_NUMBER,
  3    NAME,
  4    VALUE
  5  FROM
  6    V$SQL_OPTIMIZER_ENV
  7  WHERE
  8    SQL_ID='bqx2tj39jw1f5'
  9    AND NAME='optimizer_mode'
 10  ORDER BY
 11    NAME;
 
CHILD_NUMBER NAME                                     VALUE
------------ ---------------------------------------- -------------------------
           0 optimizer_mode                           first_rows_1

Below is the output that I received on Oracle Database 11.2.0.1 for the second half of the script.  Note that I have removed excessive blank lines and the output of the test SQL statement.  Notice that the OPTIMIZER_ENV_HASH_VALUE is displayed as 4271299772 (rather than 1002285490 as was seen above) when starting with the FIRST_ROWS_1000 OPTIMIZER_MODE:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
 
System altered.
 
SQL> ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1000;
 
Session altered.
 
SQL> SELECT C1 FROM T1 WHERE C2=2;
 
20 rows selected.
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bqx2tj39jw1f5, child number 0
-------------------------------------
SELECT C1 FROM T1 WHERE C2=2
 
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    20 |   160 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=2)
 
SQL> SELECT
  2    SQL_ID,
  3    CHILD_NUMBER,
  4    OPTIMIZER_MODE,
  5    OPTIMIZER_ENV_HASH_VALUE,
  6    PLAN_HASH_VALUE
  7  FROM
  8    V$SQL
  9  WHERE
 10    SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
SQL_ID        CHILD_NUMBER OPTIMIZER_ OPTIMIZER_ENV_HASH_VALUE PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------ ---------------
bqx2tj39jw1f5            0 FIRST_ROWS               4271299772      3617692013
 
SQL> ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1;
 
Session altered.
 
SQL> SELECT C1 FROM T1 WHERE C2=2;
 
20 rows selected.
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bqx2tj39jw1f5, child number 0
-------------------------------------
SELECT C1 FROM T1 WHERE C2=2
 
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    20 |   160 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=2)
 
SQL> SELECT
  2    SQL_ID,
  3    CHILD_NUMBER,
  4    OPTIMIZER_MODE,
  5    OPTIMIZER_ENV_HASH_VALUE,
  6    PLAN_HASH_VALUE
  7  FROM
  8    V$SQL
  9  WHERE
 10    SQL_TEXT='SELECT C1 FROM T1 WHERE C2=2';
 
SQL_ID        CHILD_NUMBER OPTIMIZER_ OPTIMIZER_ENV_HASH_VALUE PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------ ---------------
bqx2tj39jw1f5            0 FIRST_ROWS               4271299772      3617692013
 
SQL> SELECT
  2    CHILD_NUMBER,
  3    NAME,
  4    VALUE
  5  FROM
  6    V$SQL_OPTIMIZER_ENV
  7  WHERE
  8    SQL_ID='bqx2tj39jw1f5'
  9    AND NAME='optimizer_mode'
 10  ORDER BY
 11    NAME;
 
CHILD_NUMBER NAME                                     VALUE
------------ ---------------------------------------- -------------------------
           0 optimizer_mode                           first_rows_1000

As repeatedly demonstrated in the “Troubleshooting Oracle Performance” book, testing theories is important.  In this case, I learned something new when what was mentioned in the book did not agree with what I recalled as being Oracle Database behavior.  I anticipate that the learning (or re-learning) process will continue as I quietly question the statements found in the book.  There is enough new material in the second edition of the book to make it a compulsive buy for people who already own the first edition (I own the print hard copy and the companion PDF versions of the first edition).





On the Topic of Technology… 6

16 03 2014

March 16, 2014

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

It has been a while since my last post on this blog – I guess that the simple answer is that I was busy with a lot of non-Oracle Database related items, and was suffering from a bit of a writer’s block (nothing that a block dump can’t fix?).  I am expecting to soon receive the annual bill from WordPress for keeping this blog free of advertisements, as well as a bill for allowing the customized blog theme.

So, given the number of months since my last blog post,  I took the time to update the list of the top five most viewed articles for the past quarter.  The number one article shows how to install the Nagios network monitoring software on a Synology NAS (actually three different Synology NAS units), which means that a low cost NAS unit could be used to not only verify that a server used with Oracle Database responds to a ping request, but also that an Oracle database is reachable and healthy enough to provide a resultset for a simple SQL statement.  The number two article shows how to do a little mathematics with the help of Oracle Database, approximating the distance between two longitude and latitude coordinates.  The number three article shows how to use a programming language that was last updated in the late 1990s with the latest Microsoft operating system and what was the latest version of Oracle Database.

The advancement of technology certainly means that it is important for IT professionals to try staying on top of the advancements in their technology niche, without completely cutting ties with technology of the past, as illustrated by the current number three article on this blog.  For me, that means buying and then reading cover to cover various books, reading articles, and experimenting with technology.  It helps that I am an IT manager in addition to being an Oracle DBA, so my technology niche is rather broad.  In December 2013 I placed an order for the updated version of “Troubleshooting Oracle Performance“, in part because I enjoyed the first version of that book so much that I read it twice, and also because I have not had sufficient time to experiment with Oracle Database 12c – it appears that the second edition might ship next month.  Someone recently left a comment on another book that I reviewed here and on Amazon – I tried ordering that book twice without success, and now there is apparently a new version of the book on Amazon that includes coverage of Oracle Database 12c, and the book is in stock!  Someone will have to spend the $56, write a review, and let me know if the author fixed the items that I and readers of this blog so patiently and clearly mentioned in 2010.  Anyone interested in the challenge?

As I mentioned, the scope of my job responsibilities extends far beyond that of Oracle Database.  I just recently migrated the company’s email system from Microsoft Exchange 2007 to Microsoft Exchange 2013 SP1.  Anyone who remembers the fun of typing cryptic code on a command line would enjoy this experience.  Simply moving the public folders from the old server to the new server was an excellent example of command line fun, reminding me of the fun that I had years ago trying to compile X.509 certificate support into a Linux kernel.  One book that I read and reviewed was extensively detailed on the topic of public folders, yet the commands that were found in the book failed to execute without returning an error message at step 1.  The other book that I read and reviewed more or less skimmed the topic of public folders, so it was of no help for the task at hand.  No problem, I will just go to the source, Microsoft, for the solution.  A recent article on Microsoft’s site clearly listed all of the steps required to move the public folders from Exchange Server 2007 to Exchange Server 2013… all except for one very important step.  So, I am running command after command on the servers trying to move the public folders from the one server to the next, only having a partial idea of what these commands are doing.  Everything is going great, until I execute the last command listed here:

Get-PublicFolder -Recurse | Export-CliXML C:\PFMigration\Legacy_PFStructure.xml
Get-PublicFolderStatistics | Export-CliXML C:\PFMigration\Legacy_PFStatistics.xml
Get-PublicFolder -Recurse | Get-PublicFolderClientPermission | Select-Object Identity,User -ExpandProperty AccessRights | Export-CliXML C:\PFMigration\Legacy_PFPerms.xml
Get-PublicFolderDatabase | ForEach {Get-PublicFolderStatistics -Server $_.Server | Where {$_.Name -like "*\*"}}
Set-PublicFolder -Identity <public folder identity> -Name <new public folder name>
Get-OrganizationConfig | Format-List PublicFoldersLockedforMigration, PublicFolderMigrationComplete
Set-OrganizationConfig -PublicFoldersLockedforMigration:$false -PublicFolderMigrationComplete:$false
Get-PublicFolderMigrationRequest | Remove-PublicFolderMigrationRequest -Confirm:$false
Get-Mailbox -PublicFolder 
Get-PublicFolder
Get-Mailbox -PublicFolder | Where{$_.IsRootPublicFolderMailbox -eq $false} | Remove-Mailbox -PublicFolder -Force -Confirm:$false
Get-Mailbox -PublicFolder | Remove-Mailbox -PublicFolder -Force -Confirm:$false
.\Export-PublicFolderStatistics.ps1 <Folder to size map path> <FQDN of source server>
...

Spot the error?  Why is this server telling me that I need to provide a comma separated list of parameters when I execute the Export-PublicFolderStatistics.ps1 script?  So, I submit the script again with commas separating the parameters – no the same error is returned.  Must be a problem where I need to specify the parameters in double quotes also – no the same error is returned.  What the four letter word?  That is right, the return of trying to compile X.509 certificate support into the Linux kernel roughly a decade ago, only now on Microsoft’s premium messaging platform.

So, what is the missing step?  Exchange Server 2007 ships with Microsoft PowerShell 1.0 – this command requires Microsoft PowerShell 2.0 to execute, yet that requirement was never mentioned.  Oh yeah, we forgot a step, get over it – you have another set of 10 cryptic commands to enter – only to be greeted with a failure message during the public folder migration, stating that the migration failed because some folder name that once existed on Microsoft Exchange 5.5 contains a character that is now considered an invalid character in its name.  These problems never happen with an upgrade in the Oracle Database world, do they?  Advancement of technology, or Back to the Command Line.

I have also spent a bit of time experimenting with IP security cameras.  I put one in my vehicle and went for a drive.  Ah, 1969, someone obviously has not finished compiling the time saving feature into the camera’s firmware? (Click the picture for a larger view.)

NC-239WF-LicensePlateTest-Mod

-

Let’s try a different stop light – these two cars are either turning the wrong direction (obviously an indication of a bug in the camera’s firmware), or are running a red light. (Click the picture for a larger view.)

NC-239WF-RedLightTest

The camera did not pick up much interesting in the vehicle, so I set it up just in time to catch a game of what appears to be football… or maybe it was a game of sock-her? (Click the picture for a larger view.)

NC-239WF-DeerFightTest

Technology is fun, except when it hit you in the nose.





Oracle Enqueue Rules Leading to Deadlock Changes Again in 12.1?

1 08 2013

August 1, 2013

Four years ago while co-writing two chapters for an Oracle Database book, an interesting test case was put together to demonstrate how enqueues are handled when multiple sessions are competing for some of the same database resources – the test case was intended to demonstrate the neat, orderly, and predictable process implemented by Oracle Database.  Oracle Database 10.2.0.1 through 10.2.0.4 behaved essentially the same when executing the test case script, while Oracle Database 11.1.0.6, 11.1.0.7, and 11.2.0.1 behaved differently when executing the test case script (11.2.0.2 and 11.2.0.3 behave just like 11.2.0.1 when executing the script).

Oracle Database 11.1.0.x and 11.2.0.x terminated the test case early with a deadlock error.  Jonathan Lewis offered an exceptional explanation of what happened with my test case script in his Lock Horror article.  He also mentioned a couple of interesting notes in comments attached to my article, one of which stated that Oracle Database 9.2.0.5 (or 9.2.0.4) had also introduced a change in enqueue rules related to foreign keys.  Richard Foote put together a related article that explained the changes in locking mode with foreign keys in his Oracle11g: New Locking Modes When Policing FK Constraints article.  Trying not to forget related articles (see the note at the end of this blog article), Tom Kyte also authored a blog article titled Something I recently unlearned… that is somewhat related to the test case script that appeared in the Expert Oracle Practices book.  So, the rules have changed, deadlocks should now be expected when executing the test case script.

I just started experimenting with Oracle Database 12.1.0.1.  My very first experiment with the new database version was to test the behavior of the enqueue script to verify that it results in a deadlock on Oracle Database 12.1.0.1.  Consistency is one of the key factors in a database application – if an application executes:

SELECT 1 + 1 FROM DUAL;

a developer should be reasonably certain that Oracle Database will either return the value 2 or the binary number 10 when the above SQL statement is executed, regardless of whether Oracle Database 6.x or Oracle Database 12.x is used by an application written by the developer.  The same developer should have a reasonable expectation that his application, if it is compatible with Oracle Database 9.0.1, should work pretty much the same way on Oracle Database 12.1.0.1.  But, what happens when Oracle Corp. fixes bugs in Oracle Database?

Consistency is important, but it is also important to recognize that behavior may change consistently.

Let’s reintroduce the test case script from my earlier article (which also appeared in the Expert Oracle Practices book).  Executing this script requires four sessions, each of which is logged in as a different user (to minimize the difficulty associated with deciphering the results of the SQL statements):

  • Session 1 connected as TESTUSER (the owner of the tables)
  • Session 2 connected as USER2
  • Session 3 connected as USER3
  • Session 4 connected as SYS (only to query the various performance views)

First, we need a couple of test tables with a declared foreign key relationship, without an index on the foreign key column.  In Session 1:

CREATE TABLE T1(
  C1 NUMBER(10) PRIMARY KEY);

INSERT INTO
  T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

CREATE TABLE T2(
  C1 NUMBER(10) PRIMARY KEY,
  C2 NUMBER(10),
  CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);

INSERT INTO
  T2
SELECT
  ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

GRANT ALL ON T1 TO PUBLIC;
GRANT ALL ON T2 TO PUBLIC;

CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR T2;

In Session 1, insert a row into the parent and child tables without issuing a commit:

INSERT INTO T1 VALUES(100010);
INSERT INTO T2 VALUES(100010,100010);

In Session 2, insert a row into the parent and child tables, delete a row in the child table so that the primary key column value of the associated row in the parent table may be modified, and modify the parent row’s primary key column value:

INSERT INTO T1 VALUES(100020);
INSERT INTO T2 VALUES(100020,100020);
DELETE FROM T2 WHERE C1=50;
UPDATE T1 SET C1=100030 WHERE C1=50;

(Session 2 is hung)

Session 3 cannot see that a row was inserted by the now hung session 2, so session 3 attempts to insert a row with the same primary key value.  In Session 3:

INSERT INTO T1 VALUES(100020);

(Session 3 is hung)

The enqueues at this point on Oracle Database 10.2.0.4 differs from those found on Oracle Database 11.1.0.6, and (for consistency) those enqueues differ from what are found on Oracle Database 12.1.0.1.

In Session 4, let’s take a look at the enqueues (note that the join to the V$SESSION_WAIT performance view is unnecessary starting with Oracle Database 10.1.0.x, however I left that join in place to allow easy adaptation of the SQL statement so that it may work with older Oracle Database versions).

SET LINESIZE 165
SET PAGESIZE 1000
SET TRIMSPOOL ON
COLUMN SID FORMAT 9999
COLUMN PROGRAM FORMAT A11
COLUMN USERNAME FORMAT A8
COLUMN LMODE FORMAT 99
COLUMN REQUEST FORMAT 99
COLUMN CN FORMAT 99
COLUMN EVENT FORMAT A30
COLUMN STATE FORMAT A8
COLUMN S_I_W FORMAT 9999
COLUMN WT FORMAT 9999
COLUMN OBJ# FORMAT 999999
COLUMN FILE# FORMAT 999
COLUMN BLOCK# FORMAT 9999999
COLUMN P2 FORMAT 9999999
COLUMN P3 FORMAT 999999

SELECT
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.STATUS,
  SW.EVENT,
  SW.WAIT_TIME WT,
  SW.STATE,
  SW.SECONDS_IN_WAIT S_I_W,
  S.SQL_ID,
  S.SQL_CHILD_NUMBER CN,
  S.ROW_WAIT_OBJ# OBJ#,
  S.ROW_WAIT_FILE# FILE#,
  S.ROW_WAIT_BLOCK# BLOCK#,
  S.ROW_WAIT_ROW# ROW#,
  SW.P1,
  SW.P2,
  SW.P3
FROM
  V$SESSION_WAIT SW,
  V$SESSION S
WHERE
  S.USERNAME IS NOT NULL
  AND SW.SID=S.SID
  AND SW.EVENT NOT LIKE '%SQL*Net%'
  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');

  SID USERNAME PROGRAM     STATUS   EVENT                             WT STATE    S_I_W SQL_ID         CN    OBJ# FILE#   BLOCK# ROW#         P1       P2      P3
----- -------- ----------- -------- ------------------------------ ----- -------- ----- ------------- --- ------- ----- -------- ---- ---------- -------- -------
  184 USER2    sqlplus.exe ACTIVE   enq: TM - contention               0 WAITING     89 4rtg0hv0atfkx   0      -1     0        0    0 1414332421    91943       0
  243 USER3    sqlplus.exe ACTIVE   enq: TX - row lock contention      0 WAITING     49 cv338j6z2530g   0      -1     0        0    0 1415053316    65548    1531

For comparison, here is the output of the same SQL statement on 11.1.0.7 from the earlier blog article:

__SID USERNAME PROGRAM     STATUS   EVENT                             WT STATE    S_I_W SQL_ID         CN    OBJ# FILE#   BLOCK# ROW#         P1       P2      P3
----- -------- ----------- -------- ------------------------------ ----- -------- ----- ------------- --- ------- ----- -------- ---- ---------- -------- -------
  307 USER2    sqlplus.exe ACTIVE   enq: TM - contention               0 WAITING    422 4rtg0hv0atfkx   0      -1     0        0    0 1414332421    82913       0
  314 USER3    sqlplus.exe ACTIVE   enq: TM - contention               0 WAITING    407 cv338j6z2530g   0      -1     0        0    0 1414332419    82913       0

For comparison, here is the output of the same SQL statement on 10.2.0.4 from the earlier blog article:

__SID USERNAME PROGRAM     STATUS   EVENT                             WT STATE    S_I_W SQL_ID         CN    OBJ# FILE#   BLOCK# ROW#         P1       P2      P3
----- -------- ----------- -------- ------------------------------ ----- -------- ----- ------------- --- ------- ----- -------- ---- ---------- -------- -------
  204 USER2    sqlplus.exe ACTIVE enq: TM - contention                 0 WAITING    213 4rtg0hv0atfkx   0      -1     0        0    0 1414332421    16472       0
  217 USER3    sqlplus.exe ACTIVE enq: TM - contention                 0 WAITING    201 cv338j6z2530g   0      -1     0        0    0 1414332418    16472       0

… USER3 on 12.1.0.1 seems to not be waiting on a table level (TM) enqueue.

In Session 4, let’s try the second SQL statement to examine the lock modes:

SELECT
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.SQL_ID,
  S.SQL_CHILD_NUMBER CN,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  L.LMODE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
    (SELECT
      ID1,
      ID2,
      TYPE
    FROM
      V$LOCK
    WHERE
      REQUEST > 0)
  AND L.SID=S.SID;

  SID USERNAME PROGRAM     SQL_ID         CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST        ID1        ID2 TY    BLOCK
----- -------- ----------- ------------- --- ------------- -------------- --------------- ------------- ----- ------- ---------- ---------- -- --------
  127 TESTUSER sqlplus.exe                              -1              0               0             0     3       0      91943          0 TM        1
  184 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     3       5      91943          0 TM        1
  184 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     6       0      65548       1531 TX        1
  243 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     2       0      91943          0 TM        0
  243 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     0       4      65548       1531 TX        0

For comparison, here is the output of the same SQL statement on 11.1.0.7 from the earlier blog article:

__SID USERNAME PROGRAM     SQL_ID         CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST        ID1        ID2 TY    BLOCK
----- -------- ----------- ------------- --- ------------- -------------- --------------- ------------- ----- ------- ---------- ---------- -- --------
  320 TESTUSER sqlplus.exe 0vbusv12hnbk6   0         12517              1           29656             0     3       0      82913          0 TM        1
  307 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     3       5      82913          0 TM        1
  314 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     0       3      82913          0 TM        0

For comparison, here is the output of the same SQL statement on 10.2.0.4 from the earlier blog article:

__SID USERNAME PROGRAM     SQL_ID         CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST        ID1        ID2 TY    BLOCK
----- -------- ----------- ------------- --- ------------- -------------- --------------- ------------- ----- ------- ---------- ---------- -- --------
  213 TESTUSER sqlplus.exe 0vbusv12hnbk6   0             0              2             799             0     3       0      16472          0 TM        1
  204 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     3       5      16472          0 TM        0
  217 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     0       2      16472          0 TM        0

So, on Oracle Database 12.1.0.1, USER3 is not waiting on a table level (TM) equeue (it is holding a level 2 TM enqueue, while on 10.2.0.4 that session was trying to acquire a level 2 TM lock, and on 11.1.0.7 that session was trying to acquire a level 3 TM lock), it is instead waiting on a row level (TX – transaction) enqueue that is held in exclusive mode (level 6) by USER2.  Quite honestly, that result is similar to what I was hoping to see four years ago when the test script was put together, because USER3 really is waiting due to a potential primary key collision, if session 2 issues a commit.

Session 2 is hung, so it cannot issue a commit.  So, in Session 1:

COMMIT;

Session 2 is no longer hung, and now shows:

1 row updated.

Session 3, of course, remains hung waiting for Session 2 to either issue a COMMIT or a ROLLBACK to resolve the potential primary key collision.

For comparison, Session 3 on 11.1.0.7 from the earlier blog article showed the following:

INSERT INTO T1 VALUES(100020)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

… and Session 2 showed the following on 11.1.0.7:

1 row updated.

Is it possible that the Session 2 could have displayed a deadlock message, rather than Session 3 showing a deadlock message?  I seem to remember reading that there is a good chance that the session that has been in the enqueue the longest could be selected as the “vicitim” of the deadlock, but I might be incorrectly remembering that rule.  Oracle Database really did not completely resolve the deadlock, nor did it “kill” session 3 (as too many books and blog article state would happen) when the deadlock message appeared in that session’s SQL*Plus session.

For comparison, Oracle Database 10.2.0.4 behaved similar to Oracle Database 12.1.0.1, with Session 2 showing 1 row updated, and Session 3 remaining hung.  The more things change, the more they stay the same?

Thoughts?  Curious about the test cases found on the web pages that I linked to earlier?  Does Oracle Database 12.1.0.1 behave more like 11.2.0.3 or 10.2.0.4 when foreign key columns are not properly indexed?





Install the Oracle Client on a Synology DiskStation DS1813+ or DS412+ for Nagios to Monitor Databases

26 07 2013

July 26, 2013

(Back to the Previous Post in the Series)

In the previous article of this series I provided steps to install and run the Nagios network monitoring utility on either a Synology DiskStation DS1813+ or a DS412+ for the purpose of pinging network devices to verify that the devices respond to ping requests.  That article may have seemed a bit out of place on a blog that is primarily intended to include notes about using Oracle Database.  In the previous article I hinted that there is a check_oracle plugin for Nagios, suggesting that a Synology DiskStation DS1813+ or DS412+ could be used to monitor Oracle databases.

This blog article does NOT use the check_oracle plugin.  There are a small handful of official Nagios plugins for Oracle Database, but this article does not use any of those plugins either.  Instead, I took a chance at crafting a custom Perl script to monitor Oracle databases, the first time I have ever used Perl.  I thought back to some of the earlier blog articles where I introduced a couple of Oracle Database monitoring scripts that were written in VBScript, including Working with Oracle’s Time Model Data 3 and Oracle Statistics Chart Viewer – I started seeing the possibilities of using Nagios to alert DBAs about potential issues.  Yet, I have never used Perl, and at the time did not even have a clue how to concatenate strings in the language (it turns out that there are more than two techniques).

Verify that Perl is installed on the DiskStation by accessing the Package Center in the DiskStation’s DSM web interface.  If there is an Install button under the Perl heading, that indicates that Perl is not yet installed – click the Install button and wait until that button changes to Installed.

InstallOracleClient-1

Let’s start by installing the Oracle Client on the DiskStation.  I will use the Oracle Instant Client version 11.2.0.3 (the 12.1.0.1 Instant Client throws a different error message than did the 11.2.0.3 and 11.1.0.7 clients, so I reverted back to the 11.2.0.3 version).  To download the Oracle Instant Client for Linux, visit this link.  You will need an OTN account to download the client files – I downloaded the files using a desktop computer, and then transferred the files to a custom created Config share that I set up on the DiskStation.  This article requires the Basic Instant Client package and the SQL*Plus add-on package for the Instant Client.

Connect to the DiskStation using Telnet as the root user (see the previous article for Telnet connection directions).  For consistency, the .zip files for the Oracle Instant Client will be copied to the downloads directory that was created in the previous article.  We will create an oracle directory in the downloads directory:

mkdir /volume1/downloads/oracle/

Next, the downloaded files for the Oracle Instant Client are copied from the config share that I created on the DiskStation (to allow transporting files from a desktop computer) to the /volume1/downloads/oracle directory that was just created, and then the Oracle Instant Client files are unzipped, which automatically creates the instantclient_11_2 directory:

cp /volume1/config/instantclient-basic-linux-11.2.0.3.0.zip /volume1/downloads/oracle/
cp /volume1/config/instantclient-sqlplus-linux-11.2.0.3.0.zip /volume1/downloads/oracle/
cd /volume1/downloads/oracle/
unzip -o instantclient-basic-linux-11.2.0.3.0.zip
unzip -o instantclient-sqlplus-linux-11.2.0.3.0.zip
cd instantclient_11_2

The Oracle Instant Client 11.2.0.3 (and 11.1.0.7) require a file named libaio.so.1 that is not installed by default on the DiskStation.  After searching the Internet for a while, I found a copy of that file in the /lib directory on a Red Hat Enterprise Linux 3 server.  Using a desktop computer, I copied that file to the DiskStation’s config share, as well as a copy of the tnsnames.ora file that includes the database connection information for the databases that must be monitored.  The libaio.so.1 file will be placed in the /volume1/downloads/oracle/instantclient_11_2 directory, and the tnsnames.ora file will be placed in the /etc directory (one of the directories that is automatically searched).

cp /volume1/config/libaio.so.1 .
cp /volume1/config/tnsnames.ora /etc

Let’s start up SQL*Plus:

./sqlplus /nolog
./sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

An error…

ls
BASIC_README    adrci   glogin.sql   libclntsh.so.11.1  libocci.so.11.1  libocijdbc11.so  libsqlplusic.so  ojdbc6.jar  sqltest.sql  xstreams.jar
SQLPLUS_README  genezi  libaio.so.1  libnnz11.so        libociei.so      libsqlplus.so    ojdbc5.jar       sqlplus     uidrvci

The file mentioned in the error message exists in the correct directory.  That error is caused by an undefined environment variable.  Fixing the error and trying again:

LD_LIBRARY_PATH="/volume1/downloads/oracle/instantclient_11_2"
export LD_LIBRARY_PATH
./sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 26 13:22:16 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL>

Try to connect to one of the databases that are defined in the tnsnames.ora file, and then try executing a simple SQL statement before exiting SQL*Plus (change testuser, password, and DBName as appropriate for your environment):

CONNECT testuser/password@DBName
Connected.
SELECT SYSDATE FROM DUAL;

SYSDATE
---------
26-JUL-13

EXIT

Now that we have verified that the Instant Client version of SQL*Plus works from the DiskStation, we will create a simple SQL script to verify that the Instant Client version of SQL*Plus is able to accept script names from a command line.  The script will be created in the same directory where the Oracle Instant Client is located (see the basic directions for using vi, as found in the previous article):

vi /volume1/downloads/oracle/instantclient_11_2/sqltest.sql

The first line in the script instructs SQL*Plus to abort the execution of the script when an error is encountered; if the connection attempt fails, there is no point in attempting to execute any SQL statements that follow.  The second line connects to the database (change testuser, password, and DBName as appropriate for your environment).  The third line executes a simple SQL statement, selecting from a table (rather than the virtual table DUAL) – change the SQL statement to a valid statement for your database.  The last statement exits SQL*Plus once the SQL statement finishes executing:

WHENEVER SQLERROR EXIT SQL.SQLCODE
CONNECT testuser/password@DBName
SELECT ID, DESCRIPTION FROM T1 WHERE ID LIKE '8X%';
EXIT

Try using SQL*Plus to execute the script:

./sqlplus /nolog @/volume1/downloads/oracle/instantclient_11_2/sqltest.sql

The results from the SQL statement should display in the Telnet window, and then the normal DiskStation prompt should appear.  If the database instance was not running at the time of the execution, you would see something like this rather than the results of the SQL statement:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

Nagios apparently expects its plugins to return one of four exit codes, as explained in this article, to determine whether or not the checked object is behaving as expected:

Exit Code Status
        0 OK
        1 WARNING
        2 CRITICAL
        3 UNKNOWN

The exit function in Perl permits returning a number, so that is one problem solved.  I want the Perl plugin to accept a command line parameter from Nagios so that I am able to check more than one database using the same Perl script – the Nagios command line parameter will specify the script to execute.  This article explains how to receive a command line parameter from Nagios, so that is a second problem that is solved.

Let’s put together a simple (OK, not simple for me) script that executes a SQL*Plus script that is specified by Nagios (the sqltest.sql file that was just created), and then returns 0 if no ORA- type error message is returned when the Oracle Instant Client’s SQL*Plus executes a script.  If an ORA- type error message is returned, then the script’s exit code is set to 2 and the ORA- errors are returned to Nagios.  Nagios apparently only accepts one line of output from the script, reading what is written by Perl’s print command.  As we saw earlier, the script may return multiple nested ORA- type errors if the database instance is down, so we need these multiple error messages to appear on one line of output from the Perl script.

vi /opt/libexec/check_oracle.pl

This is the script that I crafted after spending, quite literally, hours days searching the Internet for Perl command syntax that would execute on the DiskStation:

#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case);
my $output = "";
my ($script);

my $result = GetOptions(
 "s|script=s"         => \$script,
  );

$ENV{"LD_LIBRARY_PATH"} = "/volume1/downloads/oracle/instantclient_11_2/";

my $ExitValue = 0;
print "Test Script: " . $script . ":  ";
open my $in, "/volume1/downloads/oracle/instantclient_11_2/sqlplus /nolog @/volume1/downloads/oracle/instantclient_11_2/$script |";

while (my $line = <$in>)
  {
  if ($line =~ /^(ORA-\d{5})/)
    {
    #$line =~ s/\v//g;
    chomp($line);
    $output = $output . $line . " ; ";
    $ExitValue = 2;
    }
  }

close($in);
print $output;
exit($ExitValue);

The script defines the LD_LIBRARY_PATH environment variable, so the Nagios user should (hopefully) have no problems executing the Perl script.  There are apparently a half-dozen different ways to remove the end of line characters from the SQL*Plus output, chomp($line); worked for my test, but $line =~ s/\v//g; did not work quite as expected.

Save the script and exit vi.  Next, we need to make the Perl script executable:

chmod +x /opt/libexec/check_oracle.pl

In the earlier article we had to modify a couple of the Nagios configuration files – this time we will need to modify some of the same files, starting with the commands.cfg file.

vi /opt/etc/objects/commands.cfg

Locate the ‘check_ping’ command definition that we modified in the earlier article.  Below the } character for that command definition, add the following, which will tell Nagios that our Perl script exists, and that the script command should be recognized by Nagios as check_oracle_ch:

# check_oracle_ch command definition by Charles Hooper
define command{
        command_name    check_oracle_ch
        command_line    $USER1$/check_oracle.pl -s $ARG1$
        }

Save the commands.cfg file and exit vi.  Let’s create another Nagios configuration file that will be used to list the Oracle Databases (and their scripts) that will be checked:

vi /opt/etc/objects/oracle.cfg

On a new line in the oracle.cfg file, add the following:

define service{
        use                     generic-service ; Inherit values from a template
        host_name               server123       ; This is a server name that is defined in the server.cfg file
        service_description     CHECK_DB_TEST   ; A unique name given to the server, database, and script combination
        check_command           check_oracle_ch!sqltest.sql    ; The command definition that was added to the commands.cfg file followed by ! and the name of the SQL script to execute
        normal_check_interval   5               ; Execute the script every 5 minutes under normal conditions
        retry_check_interval    1               ; Re-execute the script after receiving a return code of 2, every minute until its final/hard state is determined
        }

Additionally, we need to instruct Nagios that there is now an additional configuration file to read when starting.  Save the oracle.cfg file and exit vi.  Execute the following command:

vi /opt/etc/nagios.cfg

Below the cfg_file=/opt/etc/objects/switch.cfg line in the file (added in the previous article), add the following line:

cfg_file=/opt/etc/objects/oracle.cfg

Save the nagios.cfg file and exit vi.  Since we have modified the Nagios configuration, we must restart Nagios.  Find the first Nagios process ID, then kill that process (as was described in the previous article):

ps

kill 2683

Verify that Nagios does not return an error message when reading the configuration files:

/opt/bin/nagios -v /opt/etc/nagios.cfg

If no errors are reported, then start Nagios:

/opt/bin/nagios -d /opt/etc/nagios.cfg

Nagios should be writing log entries to a file name nagios.log (as defined in the nagios.cfg file).  The tail command permits viewing the last few (100 in this case) lines from a specified file:

tail -n 100 /opt/var/nagios.log

If Perl is not installed, Nagios may send an email with the following in the Additional Info section:

(Return code of 127 is out of bounds - plugin may be missing)

If the database instance is down, Nagios should send an email, possibly with the following in the Additional Info section (note that the script name is listed first – a different script will be used to test each database):

Test Script: sqltest.sql:  ORA-01034: ORACLE not available : ORA-27101: shared memory realm does not exist :

When the database instance is running, and the SQL statement(s) in the script executes successfully for the first time, Nagios should send an email with just the test script listed in the Additional Info section:

Test Script: sqltest.sql:

Now that the we have confirmed that Nagios and the Perl script work OK together, create additional SQL scripts in the /volume1/downloads/oracle/instantclient_11_2/ directory to connect to the other databases to be monitored (strongly consider changing the permissions for these files, since the files include usernames and passwords for your databases).  For each of those SQL scripts, create an additional service entry in the oracle.cfg file.  For example, if a script named sqltest42.sql is created to test a database on server42, the service entry might be created as shown below:

define service{
        use                     generic-service ; Inherit values from a template
        host_name               server42        ; This is a server name that is defined in the server.cfg file
        service_description     CHECK_DB_TEST42 ; A unique name given to the server, database, and script combination
        check_command           check_oracle_ch!sqltest42.sql  ; The command definition that was added to the commands.cfg file followed by ! and the name of the SQL script to execute
        normal_check_interval   5               ; Execute the script every 5 minutes under normal conditions
        retry_check_interval    1               ; Re-execute the script after receiving a return code of 2, every minute until its final/hard state is determined
        }

Don’t forget to verify the nagios configuration before restarting Nagios.

Anyone up to the challenge of rewriting my Working with Oracle’s Time Model Data 3 script in Perl so that the script will work on the DiskStation?





Install Nagios on a Synology DiskStation DS1813+ or DS412+

19 07 2013

July 19, 2013 (Modified July 27, 2013, July 28, 2013, November 19, 2013)

(Forward to the Next Post in the Series)

—-

Update July 27, 2013:

  • I now have Nagios running on an ARM based Synology DiskStation DS212+.  Most of the steps are the same as outlined below, however there are a few additional errors that must be addressed (see below additional steps).
  • All of the ./configure commands should have included –prefix=/opt (rather than –prefix=/usr/local or completely omitting that parameter).  That change eliminates the need to copy the Nagios plugins to the correct location.  Possibly related, the -i parameter was unnecessary for the snmp and Nagios plugins make and make install commands when the ./configure command included the –prefix=/opt prefix.
  • The wget http://sourceforge.net/projects/dsgpl/files/DSM%204.1%20Tool%20Chains/Intel%20×86%20Linux%203.2.11&#8230; download step for the gcc compiler is apparently unnecessary, at least on the Synology DiskStation DS212+ (see below).

—-

This article describes how to compile and run Nagios on a Synology DiskStation DS1813+ (64 bit) or Synology DiskStation DS412+ (32 bit, the 32 bit steps should also apply to the DS1812+) NAS, both of which utilize Intel Atom processors (cat /proc/cpuinfo indicates that the DS412+ is using a 2.13GHz Atom D2700, while the DS1813+ is using a 2.13GHz Atom D2701), and utilize the DSM 4.2 operating system.  Not all Synology DiskStation NAS devices use Intel based CPUs – some of the less expensive DiskStations use ARM type processors (see this link to determine the type of CPU installed in a specific DiskStation).  It may be possible to produce a working version of Nagios on NAS devices that do not have Intel 32 bit or 64 bit processors, but I have not yet fully tested the procedure.

Warning: A lot of what follows is based on experimentation, with the end goal of having Nagios running on a Synology DiskStation having the ability to ping devices on the network or the Internet, with an email sent to an administrator when a device stops responding to ping requests, and to send a second email when the device resumes responding to ping requests.  This functionality represents a small fraction of Nagios’ capabilities through the use of plugins.  File paths vary from one Linux distribution to the next, so that adds a bit of challenge to make certain that the files are placed in the required directory.  Copying a file to the wrong directory may temporarily disable the DiskStation and require the reinstallation of the Synology DSM operating system.  The directions below are not final, and quite likely do not represent the most efficient approaches to accomplish the end goal – but the directions will hopefully be “close enough to correct” to allow the average reader of this blog to ping and send email alerts from a DiskStation.

I have relied on the free Nagios network monitoring solution since 2002 to provide an early warning of problems associated with network attached equipment including servers, production floor computers, switches, printers, wireless access points, IP cameras, Internet connection stability, etc.  While I rely on Nagios’ alerting system, I am not an expert at configuring the Nagios network monitoring system; the Nagios configuration documentation may be downloaded here.

First, make certain that the Telnet Service (or SSH Service if that is preferred) is enabled on the DiskStation.  In the DiskStation’s Control Panel, click Terminal.

InstallNagiosDiskStation1

Place a checkmark next to Enable Telnet service (if the item is not already checked), and then click the Apply button.

InstallNagiosDiskStation2

Verify that the computer that you intend to use has a Telnet client.  For Windows 7, access the Programs link in the Control Panel, and then click the Turn Windows features on or off link.  Make certain that there is a checkmark next to Telnet Client, then click the OK button.

InstallNagiosDiskStation3

Open a command line (in Windows, Start – Run – type  cmd  and press the Enter key).  On the command line, type telnet followed by either the name of the DiskStation or the IP address of the DiskStation, then press the Enter key.  When prompted for a username, type root and press the Enter key.  Type the admin user’s password (that is used to access the DSM interface in a web browser) and press the Enter key.

InstallNagiosDiskStation4

The command line on the DiskStation is very similar to the command line on a Unix or Linux computer, and is somewhat similar to a Windows command line or MS-DOS command line (use / rather than \, use ls rather than dir, use vi rather than edit):

InstallNagiosDiskStation5

We first need to add ipkg support to the DiskStation, detailed directions may be viewed at this link.  The exact directions may be different for other DiskStation models, but the following directions work for both the DS1813+ and DS412+ (note that all files downloaded from the Internet will be placed on volume1 in the downloads directory – copy and paste the lines to the Telnet session, one line at a time):

cd /volume1
mkdir downloads
cd downloads
wget http://ipkg.nslu2-linux.org/feeds/optware/syno-i686/cross/unstable/syno-i686-bootstrap_1.2-7_i686.xsh
chmod +x syno-i686-bootstrap_1.2-7_i686.xsh
sh syno-i686-bootstrap_1.2-7_i686.xsh

The vi editor is used on the DiskStation to modify files; that vi editor is a bit challenging to use at first sight, so you may need help with a couple of basic commands (see this quick reference for other commands).  The commands in vi are case sensitive (i is not the same as I).  When a file is opened, press the i key on the keyboard to allow making changes to the file (such as typing commands, or deleting commands).  When finished making changes to the file press the Esc key.  Once the Esc key is pressed, type ZZ to save the changed file and quit, or :q! to quit without saving the changes.

Next, we must modify the file that establishes the environment for the root user, when that user connects to the DiskStation.  This change is needed as part of the ipkg installation.  Edit the .profile file used by the root user:

vi /root/.profile

Add a # character in front of the two lines that contain the word PATH, then save the file (see the brief directions above to switch between command and insert mode in vi):

InstallNagiosDiskStation6

Next, reboot the DiskStation by clicking the Restart button in the Synology DSM interface (note: it should be possible to type reboot in the Telnet interface, however the DiskStation locked up the one time I attempted to execute that command).

InstallNagiosDiskStation7

Once the DiskStation reboots, reconnect to the DiskStation using Telnet, connecting as the root user, just as was done earlier.

The ipkg command should now work on the command line.  First, request that an updated list of available packages is downloaded, then display that list of packages:

ipkg update
ipkg list

Next, download a couple of packages that will be used by the Nagios network monitoring tool.  Note that using ipkg to install packages is a lot easier than compiling source code, so have fun with the ipkg utility.  When installing the optware-devel package, an error may appear stating that there is an incompatibility between wget and wget-ssl – just ignore that error for now.

ipkg update wget-ssl
ipkg install optware-devel
ipkg install gcc
ipkg install libtool
ipkg install mysql

Next, we need to compile a file and copy a couple of files:

cd /opt/share/libtool/libltdl/
./configure --prefix=/opt
make all
make install

cp /usr/syno/apache/modules/mod_ext_filter.so /opt/libexec/mod_ext_filter.so
cp /usr/syno/apache/modules/*.* /opt/libexec/

Now, install the Apache package:

ipkg install apache

If an error message is displayed on screen about mod_ext_filter.so, then modify the /opt/etc/apache2/httpd.conf file and add a # in front of the line LoadModule ext_filter_module libexec/mod_ext_filter.so and save the file.  Re-execute the ipkg install apache command (note that the up arrow on the keyboard may be pressed to quickly retype one of the previously executed commands).

InstallNagiosDiskStation8

Using the DiskStation’s Control Panel, create a nagios group and a nagcmd group (the nagcmd group probably will not be used for anything specific).  These groups do not require any special DiskStation permissions.

InstallNagiosDiskStation9

Using the DiskStation’s Control Panel, create a nagios user and add that user to the nagios and nagcmd groups.  The nagios user does not require any specific DiskStation permissions.

Next, switch back to the Telnet session, download the Nagios source code, and compile the source code:

DiskStation DS212+ Notes:

The following ./configure call was used on the DS212+:

./configure --prefix=/opt --with-command-group=nagios --disable-nanosleep --enable-nanosleep=no

The ./configure aborted with the following error message:

checking for pthread_create in -lpthread... no
checking for pthread_mutex_init in -lpthread... no
checking for pthread_create in -lpthreads... no
checking for pthread_create in -llthread... no
checking if we need -pthread for threads... no
checking for library containing nanosleep... no
Error: nanosleep() needed for timing operations.

The test that threw the error is located roughly 63% of the way through the configure file (on roughly line 5635).  If the exit 1 line in the configure file is commented out, then the configure step will complete.  However, the make all command will then fail with the following error messages:

/volume1/downloads/nagios/base/nebmods.c:363: undefined reference to `dlclose'
nebmods.o: In function `neb_load_module':
/volume1/downloads/nagios/base/nebmods.c:218: undefined reference to `dlopen'
/volume1/downloads/nagios/base/nebmods.c:249: undefined reference to `dlsym'
/volume1/downloads/nagios/base/nebmods.c:266: undefined reference to `dlsym'
/volume1/downloads/nagios/base/nebmods.c:299: undefined reference to `dlsym'
/volume1/downloads/nagios/base/nebmods.c:225: undefined reference to `dlerror'
/opt/lib/gcc/arm-none-linux-gnueabi/4.2.3/../../../../arm-none-linux-gnueabi/lib/libpthread.so: undefined reference to `__default_sa_restorer_v2@GLIBC_PRIVATE'
/opt/lib/gcc/arm-none-linux-gnueabi/4.2.3/../../../../arm-none-linux-gnueabi/lib/libpthread.so: undefined reference to `__default_rt_sa_restorer_v2@GLIBC_PRIVAT
E'
/opt/lib/gcc/arm-none-linux-gnueabi/4.2.3/../../../../arm-none-linux-gnueabi/lib/libpthread.so: undefined reference to `__default_rt_sa_restorer_v1@GLIBC_PRIVAT
E'
/opt/lib/gcc/arm-none-linux-gnueabi/4.2.3/../../../../arm-none-linux-gnueabi/lib/libpthread.so: undefined reference to `__default_sa_restorer_v1@GLIBC_PRIVATE'
collect2: ld returned 1 exit status
make[1]: *** [nagios] Error 1
make[1]: Leaving directory `/volume1/downloads/nagios/base'
make: *** [all] Error 2

After a bit of searching on the Internet, I found a page that suggested making the following changes (note that I unsuccessfully tried a couple of other steps that may have also partially corrected the issue):

mkdir /opt/arm-none-linux-gnueabi/lib_disabled
mv /opt/arm-none-linux-gnueabi/lib/libpthread* /opt/arm-none-linux-gnueabi/lib_disabled

cp /lib/libpthread.so.0 /opt/arm-none-linux-gnueabi/lib/
cd /opt/arm-none-linux-gnueabi/lib/
ln -s libpthread.so.0 libpthread.so
ln -s libpthread.so.0 libpthread-2.5.so

After making the above changes, I was able to run the configure and make all commands without receiving an error.

cd /volume1/downloads
wget http://prdownloads.sourceforge.net/sourceforge/nagios/nagios-3.5.0.tar.gz
tar xzf nagios-3.5.0.tar.gz
cd nagios
./configure --prefix=/opt --with-command-group=nagios
make all
make install
make install-init
make install-config
make install-commandmode

We apparently need to copy a couple of files to different locations at this point:

cp /opt/lib/libltdl.so.3 /opt/local/lib/libltdl.so.3
cp /opt/lib/libltdl.so.3 /usr/lib/libltdl.so.3
cp /opt/lib/libltdl.so /usr/lib/

Undo the changes that were earlier made to the /root/.profile file, where # characters were added in front of any line that contained the word PATH.  Remove those # characters and save the file:

vi /root/.profile

(This part still needs some fine tuning to make the web interface work with Nagios.)  Edit the Nagios Makefile and change the line beginning with HTTPD_CONF to show HTTPD_CONF=/opt/etc/apache2/conf.d  Then save the file.

cd /volume1/downloads/nagios
vi Makefile

InstallNagiosDiskStation10

Execute the following command:

make install-webconf

Create a nagiosadmin user for the web administration, specify a password when prompted:

htpasswd -c /usr/local/etc/htpasswd.users nagiosadmin

Update November 19, 2013:

GabrielM reported in a comment below that it may be necessary to specify the full path to the htpasswd program:

/usr/syno/apache/bin/htpasswd -c /usr/local/etc/htpasswd.users nagiosadmin

Install a couple of additional ipkg packages that will be used by Nagios (the last package adds a ping utility that may be used by Nagios – the security permissions on the DiskStation prevent non-root users from using the built-in ping utility):

ipkg install openssl
ipkg install openssl-dev
ipkg install sendmail
ipkg install inetutils

A step that may or may not be required is to download a functioning C++ compiler (some of the commands below point to files provided with the C++ compiler) – it appears that there should already be a compiler on the DiskStation at this point (in /opt/bin), so the successful completion of this task of downloading a usable C++ compiler might not be required.

DiskStation DS212+ Notes:

These wget and tar steps were completely skipped on the DS212+

For the DiskStation DS1813+ 64 bit:

cd /volume1/downloads
wget http://sourceforge.net/projects/dsgpl/files/DSM%204.1%20Tool%20Chains/Intel%20x86%20Linux%203.2.11%20%28Cedarview%29/gcc420_glibc236_x64_cedarview-GPL.tgz
tar zxpf gcc420_glibc236_x64_cedarview-GPL.tgz -C /usr/local/

For the DiskStation DS412+ 32 bit:

cd /volume1/downloads
wget http://sourceforge.net/projects/dsgpl/files/DSM%204.2%20Tool%20Chains/Intel%20x86%20Linux%203.2.11%20%28Bromolow%29/gcc421_glibc236_x86_bromolow-GPL.tgz
tar zxpf gcc421_glibc236_x86_bromolow-GPL.tgz -C /usr/local/

Now the net-snmp source code is downloaded and extracted:

DiskStation DS212+ Notes:

The ./configure call on the DS212 (might also work on the other DiskStation models):

./configure –prefix=/opt

The make call threw several errors, including:

/bin/sh: arm-none-linux-gnueabi-ld: not found
make[2]: *** [../blib/arch/auto/NetSNMP/default_store/default_store.so] Error 127

Before running the make command on the DS212+, execute the following command:

ln -s /opt/bin/ld /opt/bin/arm-none-linux-gnueabi-ld

The -i parameter may be omitted when running the make and make install commands.

cd /volume1/downloads
wget http://sourceforge.net/projects/net-snmp/files/net-snmp/5.7.2/net-snmp-5.7.2.tar.gz
tar xzf net-snmp-5.7.2.tar.gz
cd net-snmp-5.7.2

For the DiskStation DS1813+ 64 bit, execute the following to compile the net-snmp source (note that this command uses the compiler that was downloaded):

env CC=/usr/local/x86_64-linux-gnu/bin/x86_64-linux-gnu-gcc \
LD=/usr/local/x86_64-linux-gnu/bin/x86_64-linux-gnu-ld \
RANLIB=/usr/local/x86_64-linux-gnu/bin/x86_64-linux-gnu-ranlib \
CFLAGS="-I/usr/local/x86_64-linux-gnu/include" \
LDFLAGS="-L/usr/local/x86_64-linux-gnu/lib" \
./configure --host=x86_64-linux-gnu --target=x86_64-linux-gnu --build=x86_64-pc-linux --prefix=/usr/local

For the DiskStation DS412+ 32 bit, execute the following to compile the net-snmp source (note: I could not use any of the different compilers that I tried downloading due to the compilers crashing with one of two error messages, so this command uses the compiler in /opt/bin):

env CC=/opt/bin/i686-linux-gnu-gcc \
LD=/usr/local/i686-linux-gnu/bin/i686-linux-gnu-ld \
RANLIB=/usr/local/i686-linux-gnu/bin/i686-linux-gnu-ranlib \
CFLAGS="-I/usr/local/i686-linux-gnu/include" \
LDFLAGS="-L/usr/local/i686-linux-gnu/lib" \
./configure --host=i686-linux-gnu --target=i686-linux-gnu --build=i686-linux-gnu --prefix=/usr/local

Several prompts will appear on the screen when either of the two commands is executed.  I entered the following for the prompts:

Default version of SNMP to use (3): 3
System Contact Information: (Enter)
System Location (Unknown): (Enter)
Location to write logfile (/var/log/snmpd.log): /opt/var/snmpd.log
Location to write persistent information (/var/net-snmp): (Enter)

Two additional commands to execute:

make -i
make install -i

Now we need to download the source code for the Nagios plugins (check_apt, check_breeze, check_by_ssh, check_clamd, check_cluster, check_dhcp, check_disk, check_disk_smb, check_dns, check_dummy, check_file_age, check_flexlm, check_ftp, check_http, check_icmp, check_ide_smart, check_ifoperstatup, check_ifstatus, check_imap, check_ircd, check_jabber, check_ldap, check_ldaps, check_load, check_log, check_mailq, check_mrtg, check_mrtgtraf, check_mysql, check_mysql_query, check_nagios, check_nntp, check_nntps, check_nt, check_ntp, check_ntp_peer, check_ntp_time, check_nwstat, check_oracle, check_overcr, check_ping, check_pop, check_procs, check_real, check_rpc, check_sensors, check_simap, check_smtp, check_snmp, check_spop, check_ssh, check_ssmtp, check_swap, check_tcp, check_time, check_udp, check_ups, check_users, check_wave) that allow Nagios to perform various monitoring tasks:

cd /volume1/downloads
wget http://prdownloads.sourceforge.net/sourceforge/nagiosplug/nagios-plugins-1.4.16.tar.gz
tar xzf nagios-plugins-1.4.16.tar.gz
cd nagios-plugins-1.4.16/

Update November 19, 2013:

GabrielM reported in a comment below that the occasionally changing “current version” of the Nagios plugins makes it difficult to download the plugins from the source shown above.  If you open the http://prdownloads.sourceforge.net/sourceforge/nagiosplug/ web page in a web browser, the web browser will be redirected to http://sourceforge.net/projects/nagiosplug/files/ which contains the following statement:

“The Nagios Plugins are no longer distributed via SourceForge. For downloads and other information, please visit: https://www.nagios-plugins.org/
Source: README.md, updated 2013-10-01″

If you follow that link and then click the Download heading at the top of the page, there should be a link on the page that allows access to the current version of the Nagios plugins.  That link is currently: https://www.nagios-plugins.org/download/nagios-plugins-1.5.tar.gz

The command that GabrielM provided should work:

wget https://www.nagios-plugins.org/download/nagios-plugins-1.5.tar.gz

DiskStation DS212+ Notes:

The following configure, make, and make install commands were used:

./configure --prefix=/opt --with-openssl=/usr/syno/bin/openssl --with-nagios-user=nagios --with-nagios-group=nagios --with-ping-command="/opt/bin/ping -c %d %s" --psdir=/bin --with-ps-varlist="&procpid,&procppid,&procvsz,&procrss,procprog,&pos" --with-ps-cols=6 --with-ps-format="%d %d %d %d %s %n" --with-ps-command="/bin/ps -w"
make
make install

For the DiskStation DS1813+ 64 bit:

./configure --with-openssl=/usr/syno/bin/openssl --with-nagios-user=nagios --with-nagios-group=nagios --with-ping-command="/opt/bin/ping -c %d %s" --psdir=/bin --with-ps-varlist="&procpid,&procppid,&procvsz,&procrss,procprog,&pos" --with-ps-cols=6 --with-ps-format="%d %d %d %d %s %n" --with-ps-command="/bin/ps -w" --host=x86_64-linux-gnu --target=x86_64-linux-gnu --build=x86_64-pc-linux
make -i 
make install -i

For the DiskStation DS412+ 32 bit:

./configure --with-openssl=/usr/syno/bin/openssl --with-nagios-user=nagios --with-nagios-group=nagios --with-ping-command="/opt/bin/ping -c %d %s" --psdir=/bin --with-ps-varlist="&procpid,&procppid,&procvsz,&procrss,procprog,&pos" --with-ps-cols=6 --with-ps-format="%d %d %d %d %s %n" --with-ps-command="/bin/ps -w" --host=i686-linux-gnu --target=i686-linux-gnu --build=i686-linux-gnu --prefix=/usr/local
make -i 
make install -i

Copy the Nagios plugins to the location expected by Nagios:

DiskStation DS212+ Notes:

The plugins were installed in the correct location on the DS212+

cp /usr/local/nagios/libexec/*.* /opt/libexec
cp /usr/local/nagios/libexec/* /opt/libexec
cp /usr/local/libexec/check_* /opt/libexec

Update November 19, 2013:

GabrielM reported in a comment below that the third command above may fail.  Depending on the compile options used, the first two commands or the third command may fail.  The first two commands are intended to accomplish the same task as the third command; the first two commands or the last command are expected to fail, but all three commands should not fail.  I should have explained this potential area of concern better.

Copy the Nagios startup script to the correct location so that Nagios will automatically start when the DiskStation is rebooted:

cp /usr/local/etc/rc.d/nagios /opt/etc/init.d/S81nagios

Verify that the ownership of the nagios directory is set correctly:

DiskStation DS212+ Notes:

The file is actually in the /opt/bin directory, so use this command instead:

chown nagios:nagios /opt/bin/nagios/nagios -R
chown nagios:nagios /usr/local/nagios -R

In addition to the main /opt/etc/nagios.cfg Nagios file, there are several other configuration files that are potentially used by Nagios (defined in the nagios.cfg file):

/opt/etc/objects/commands.cfg
/opt/etc/objects/contacts.cfg
/opt/etc/objects/timeperiods.cfg
/opt/etc/objects/templates.cfg
/opt/etc/objects/localhost.cfg
/opt/etc/objects/windows.cfg
/opt/etc/objects/server.cfg
/opt/etc/objects/switch.cfg
/opt/etc/objects/printer.cfg

We need to make a couple of adjustments in the  /opt/etc/objects/commands.cfg file.

vi /opt/etc/objects/commands.cfg

Change the ‘notify-host-by-email’ command definition section as follows:

define command{
    command_name notify-host-by-email
    command_line /usr/bin/printf "%b" "Subject: $NOTIFICATIONTYPE$ Host Alert: $HOSTNAME$ is $HOSTSTATE$\n\n***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\nHost: $HOSTNAME$\nState: $HOSTSTATE$\nAddress: $HOSTADDRESS$\nInfo: $HOSTOUTPUT$\n\nDate/Time: $LONGDATETIME$\n" | /opt/sbin/sendmail -vt $CONTACTEMAIL$
    }

Change the ‘notify-service-by-email’ command definition section as follows:

define command{
    command_name notify-service-by-email
    command_line /usr/bin/printf "%b" "Subject: $NOTIFICATIONTYPE$ Service Alert: $HOSTALIAS$/$SERVICEDESC$ is $SERVICESTATE$\n\n***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\n\nService: $SERVICEDESC$\nHost: $HOSTALIAS$\nAddress: $HOSTADDRESS$\nState: $SERVICESTATE$\n\nDate/Time: $LONGDATETIME$\n\nAdditional Info:\n\n$SERVICEOUTPUT$\n" | /opt/sbin/sendmail -vt $CONTACTEMAIL$
    }

Change the ‘check_ping’ command definition section as follows (feel free to read the documentation for check_ping and specify different values):

define command{
        command_name    check_ping
        command_line    $USER1$/check_ping -H $HOSTADDRESS$ -w 3000,25% -c 5000,90% -p 3 
        }

Save the file and exit vi.

At this point, the Nagios network monitoring utility will likely experience an error similar to the following when attempting to send an alert email:

output=collect: Cannot write ./dfr6BFFPC7027203 (bfcommit, uid=1026, gid=25): Permission denied

Execute the following commands, which should fix the above problem:

chmod g+w /opt/var/spool/clientmqueue
chmod 444 /opt/etc/mail/*.cf
chmod 7555 /opt/sbin/sendmail

We will need to use su to test the execution of various commands as the nagios user.  Without this fix (described here), you might see the following error message:

su: warning: cannot change directory to /var/services/homes/nagios: No such file or directory su: /sbin/nologin: No such file or directory

Enter the following commands:

mkdir /var/services/homes
mkdir /var/services/homes/nagios
chown nagios:nagios /var/services/homes/nagios -R
vi /etc/passwd

Locate the line in the passwd file for the Nagios user.  Near the end of the line, /sbin/nologin should appear.  Replace that text with /bin/ash then save and exit vi.

Verify that the Nagios user is able to execute the check_ping plugin.  Replace MyDeviceHere with either an IP address or a network device name that is on your network:

su - nagios -c "/opt/libexec/check_ping -H MyDeviceHere -w 5000,80% -c 5000,80% -p 5"

If the ping command (called by check_ping) is not able to resolve a network device name, and the fully qualified dns name was not specified (MyDeviceHere.MyDomainHere.com), edit the /etc/resolv.conf file:

vi /etc/resolv.conf

On a new line in the file, add the following line (replacing MyDomainHere.com with your dns domain name for the network):

search MyDomainHere.com

Verify that sendmail works for the Nagios user.  At the prompt that appears, type a short message, press the Enter key, type a period, then press the Enter key again – replace MyEmailAddressHere@MyDomainHere.com with your email address):

su - nagios -c "/opt/sbin/sendmail -vt MyEmailAddressHere@MyDomainHere.com"

—-

It is important to always verify the Nagios configuration before starting (or restarting after a configuration change) Nagios.  To verify the configuration type the following:

/opt/bin/nagios -v /opt/etc/nagios.cfg

To start up Nagios as a background task (daemon), execute the following:

/opt/bin/nagios -d /opt/etc/nagios.cfg

To stop Nagios that is executing as a background task, type:

ps

InstallNagiosDiskStation11

Then search though the list of processes for the first line that shows /opt/bin/nagios -d /opt/etc/nagios.cfg.  The number at the left of that line, 31152 in this case, is used to stop Nagios.  To stop Nagios, type the following (replace 31152 with the number shown on your screen):

kill 31152

Side note: I tried installing quite a few different C++ compilers that supposedly work with the Synology DSM (see here).  As such, I had to find a way to remove a directory, that directory’s subdirectories, and files.  The following command will completely remove the /usr/local/i686-linux-gnu directory, should the need arise:

rm -rf /usr/local/i686-linux-gnu

At this point, Nagios will hopefully run as a background task, and it should be able to ping and send email alerts.  However, if you were following the above directions, we have not yet instructed Nagios which devices to monitor, and to whom the alert emails should be sent.  The next step is to define the email contacts by modifying the /opt/etc/objects/contacts.cfg file (see the documentation for assistance):

vi /opt/etc/objects/contacts.cfg

After setting up the contacts, we should probably tell Nagios which devices to monitor.  If there are a lot of devices on your network to be monitored, you might find that using Microsoft Excel rather than vi to create the object definitions makes the task more manageable.  Set up a simple worksheet with four columns.  Column A will be used to specify the short host_name for the object to be monitored.  Column B will be used to specify the alias (long description for the object).  Column C will be used to either specify the IP address for the device or the network name for the device.  Column D will be used to identify the group to which the object belongs and the file name to which the definition is saved (the Excel macro supports the following groups: ap, camera, computer, external, other, printer, server, switch).

InstallNagiosDiskStation13

The Excel macro is set up to read a tab delimited file, rather than reading the object description directly from the Excel worksheet.  Highlight all of the rows in the worksheet except for the top header row, and press Ctrl C (or edit – Copy) to copy the definitions to the Windows clipboard in tab delimited format.  Start Notepad (Start – Run – Notepad), and then press Ctrl V (or edit – Paste) to paste the tab delimited object descriptions into Notepad.  The Excel macro code expects the text file to be saved as nagioshosts.txt.

The Excel macro code follows (I image that not many computers still have a second floppy drive installed, so change the B:\Hardware Documentation\Synology\ path as appropriate for your environment):

Private Sub cmdProcessText_Click()
    Dim intFileNumRead As Integer
    Dim intFileNumAP As Integer
    Dim intFileNumCamera As Integer
    Dim intFileNumComputer As Integer
    Dim intFileNumExternal As Integer
    Dim intFileNumOther As Integer
    Dim intFileNumPrinter As Integer
    Dim intFileNumServer As Integer
    Dim intFileNumSwitch As Integer
    Dim intFileNumWrite As Integer

    Dim strLine As String
    Dim strItem() As String

    intFileNumRead = FreeFile
    Open "B:\Hardware Documentation\Synology\nagioshosts.txt" For Input As #intFileNumRead

    intFileNumAP = FreeFile
    Open "B:\Hardware Documentation\Synology\ap.cfg" For Output As intFileNumAP
    Print #intFileNumAP, "###############################################################################"; Chr(10);
    Print #intFileNumAP, "# ap.cfg - lists the wireless access points to be monitored"; Chr(10);
    Print #intFileNumAP, "#"; Chr(10);
    Print #intFileNumAP, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumAP, "###############################################################################"; Chr(10);
    Print #intFileNumAP, "#"; Chr(10); Chr(10);
    Print #intFileNumAP, "###############################################################################"; Chr(10);
    Print #intFileNumAP, "#"; Chr(10);
    Print #intFileNumAP, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumAP, "#"; Chr(10);
    Print #intFileNumAP, "###############################################################################"; Chr(10);
    Print #intFileNumAP, "#"; Chr(10); Chr(10);
    Print #intFileNumAP, "define hostgroup{"; Chr(10);
    Print #intFileNumAP, "        hostgroup_name  ap                      ; The name of the hostgroup"; Chr(10);
    Print #intFileNumAP, "        alias           Local Access Points       ; Long name of the group"; Chr(10);
    Print #intFileNumAP, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumCamera = FreeFile
    Open "B:\Hardware Documentation\Synology\camera.cfg" For Output As intFileNumCamera
    Print #intFileNumCamera, "###############################################################################"; Chr(10);
    Print #intFileNumCamera, "# camera.cfg - lists the IP cameras to be monitored"; Chr(10);
    Print #intFileNumCamera, "#"; Chr(10);
    Print #intFileNumCamera, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumCamera, "###############################################################################"; Chr(10);
    Print #intFileNumCamera, "#"; Chr(10); Chr(10);
    Print #intFileNumCamera, "###############################################################################"; Chr(10);
    Print #intFileNumCamera, "#"; Chr(10);
    Print #intFileNumCamera, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumCamera, "#"; Chr(10);
    Print #intFileNumCamera, "###############################################################################"; Chr(10);
    Print #intFileNumCamera, "#"; Chr(10); Chr(10);
    Print #intFileNumCamera, "define hostgroup{"; Chr(10);
    Print #intFileNumCamera, "        hostgroup_name  camera                  ; The name of the hostgroup"; Chr(10);
    Print #intFileNumCamera, "        alias           Local IP Cameras          ; Long name of the group"; Chr(10);
    Print #intFileNumCamera, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumComputer = FreeFile
    Open "B:\Hardware Documentation\Synology\computer.cfg" For Output As intFileNumComputer
    Print #intFileNumComputer, "###############################################################################"; Chr(10);
    Print #intFileNumComputer, "# computer.cfg - lists the shop floor computers to be monitored"; Chr(10);
    Print #intFileNumComputer, "#"; Chr(10);
    Print #intFileNumComputer, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumComputer, "###############################################################################"; Chr(10);
    Print #intFileNumComputer, "#"; Chr(10); Chr(10);
    Print #intFileNumComputer, "###############################################################################"; Chr(10);
    Print #intFileNumComputer, "#"; Chr(10);
    Print #intFileNumComputer, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumComputer, "#"; Chr(10);
    Print #intFileNumComputer, "###############################################################################"; Chr(10);
    Print #intFileNumComputer, "#"; Chr(10); Chr(10);
    Print #intFileNumComputer, "define hostgroup{"; Chr(10);
    Print #intFileNumComputer, "        hostgroup_name  computer               ; The name of the hostgroup"; Chr(10);
    Print #intFileNumComputer, "        alias           Domain Computers          ; Long name of the group"; Chr(10);
    Print #intFileNumComputer, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumExternal = FreeFile
    Open "B:\Hardware Documentation\Synology\external.cfg" For Output As intFileNumExternal
    Print #intFileNumExternal, "###############################################################################"; Chr(10);
    Print #intFileNumExternal, "# external.cfg - lists the devices external to the LAN network to be monitored"; Chr(10);
    Print #intFileNumExternal, "#"; Chr(10);
    Print #intFileNumExternal, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumExternal, "###############################################################################"; Chr(10);
    Print #intFileNumExternal, "#"; Chr(10); Chr(10);
    Print #intFileNumExternal, "###############################################################################"; Chr(10);
    Print #intFileNumExternal, "#"; Chr(10);
    Print #intFileNumExternal, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumExternal, "#"; Chr(10);
    Print #intFileNumExternal, "###############################################################################"; Chr(10);
    Print #intFileNumExternal, "#"; Chr(10); Chr(10);
    Print #intFileNumExternal, "define hostgroup{"; Chr(10);
    Print #intFileNumExternal, "        hostgroup_name  external               ; The name of the hostgroup"; Chr(10);
    Print #intFileNumExternal, "        alias           Monitored devices External to the Network ; Long name of the group"; Chr(10);
    Print #intFileNumExternal, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumOther = FreeFile
    Open "B:\Hardware Documentation\Synology\other.cfg" For Output As intFileNumOther
    Print #intFileNumOther, "###############################################################################"; Chr(10);
    Print #intFileNumOther, "# other.cfg - lists the miscellaneous devices to be monitored"; Chr(10);
    Print #intFileNumOther, "#"; Chr(10);
    Print #intFileNumOther, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumOther, "###############################################################################"; Chr(10);
    Print #intFileNumOther, "#"; Chr(10); Chr(10);
    Print #intFileNumOther, "###############################################################################"; Chr(10);
    Print #intFileNumOther, "#"; Chr(10);
    Print #intFileNumOther, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumOther, "#"; Chr(10);
    Print #intFileNumOther, "###############################################################################"; Chr(10);
    Print #intFileNumOther, "#"; Chr(10); Chr(10);
    Print #intFileNumOther, "define hostgroup{"; Chr(10);
    Print #intFileNumOther, "        hostgroup_name  other                 ; The name of the hostgroup"; Chr(10);
    Print #intFileNumOther, "        alias           Miscellaneous Devices ; Long name of the group"; Chr(10);
    Print #intFileNumOther, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumPrinter = FreeFile
    Open "B:\Hardware Documentation\Synology\printer.cfg" For Output As intFileNumPrinter
    Print #intFileNumPrinter, "###############################################################################"; Chr(10);
    Print #intFileNumPrinter, "# printer.cfg - lists the printer devices to be monitored"; Chr(10);
    Print #intFileNumPrinter, "#"; Chr(10);
    Print #intFileNumPrinter, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumPrinter, "###############################################################################"; Chr(10);
    Print #intFileNumPrinter, "#"; Chr(10); Chr(10);
    Print #intFileNumPrinter, "###############################################################################"; Chr(10);
    Print #intFileNumPrinter, "#"; Chr(10);
    Print #intFileNumPrinter, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumPrinter, "#"; Chr(10);
    Print #intFileNumPrinter, "###############################################################################"; Chr(10);
    Print #intFileNumPrinter, "#"; Chr(10); Chr(10);
    Print #intFileNumPrinter, "define hostgroup{"; Chr(10);
    Print #intFileNumPrinter, "        hostgroup_name  printer               ; The name of the hostgroup"; Chr(10);
    Print #intFileNumPrinter, "        alias           Printers and Copiers  ; Long name of the group"; Chr(10);
    Print #intFileNumPrinter, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumServer = FreeFile
    Open "B:\Hardware Documentation\Synology\server.cfg" For Output As intFileNumServer
    Print #intFileNumServer, "###############################################################################"; Chr(10);
    Print #intFileNumServer, "# server.cfg - lists the servers to be monitored"; Chr(10);
    Print #intFileNumServer, "#"; Chr(10);
    Print #intFileNumServer, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumServer, "###############################################################################"; Chr(10);
    Print #intFileNumServer, "#"; Chr(10); Chr(10);
    Print #intFileNumServer, "###############################################################################"; Chr(10);
    Print #intFileNumServer, "#"; Chr(10);
    Print #intFileNumServer, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumServer, "#"; Chr(10);
    Print #intFileNumServer, "###############################################################################"; Chr(10);
    Print #intFileNumServer, "#"; Chr(10); Chr(10);
    Print #intFileNumServer, "define hostgroup{"; Chr(10);
    Print #intFileNumServer, "        hostgroup_name  server               ; The name of the hostgroup"; Chr(10);
    Print #intFileNumServer, "        alias           Server and Similar Devices ; Long name of the group"; Chr(10);
    Print #intFileNumServer, "        }"; Chr(10); Chr(10); Chr(10);

    intFileNumSwitch = FreeFile
    Open "B:\Hardware Documentation\Synology\switch.cfg" For Output As intFileNumSwitch
    Print #intFileNumSwitch, "###############################################################################"; Chr(10);
    Print #intFileNumSwitch, "# switch.cfg - lists the network equipment type devices to be monitored"; Chr(10);
    Print #intFileNumSwitch, "#"; Chr(10);
    Print #intFileNumSwitch, "# Last Modified: "; Now; Chr(10);
    Print #intFileNumSwitch, "###############################################################################"; Chr(10);
    Print #intFileNumSwitch, "#"; Chr(10); Chr(10);
    Print #intFileNumSwitch, "###############################################################################"; Chr(10);
    Print #intFileNumSwitch, "#"; Chr(10);
    Print #intFileNumSwitch, "# HOST GROUP DEFINITIONS"; Chr(10);
    Print #intFileNumSwitch, "#"; Chr(10);
    Print #intFileNumSwitch, "###############################################################################"; Chr(10);
    Print #intFileNumSwitch, "#"; Chr(10); Chr(10);
    Print #intFileNumSwitch, "define hostgroup{"; Chr(10);
    Print #intFileNumSwitch, "        hostgroup_name  switch               ; The name of the hostgroup"; Chr(10);
    Print #intFileNumSwitch, "        alias           Switche and Similar Devices ; Long name of the group"; Chr(10);
    Print #intFileNumSwitch, "        }"; Chr(10); Chr(10); Chr(10);

    Do While Not (EOF(intFileNumRead))
        Line Input #intFileNumRead, strLine
        strItem = Split(strLine, vbTab)
        'strItem(0) = host_name
        'strItem(1) = alias
        'strItem(2) = address
        'strItem(3) = hostgroups
        Select Case strItem(3)
            Case "ap"
                intFileNumWrite = intFileNumAP
            Case "camera"
                intFileNumWrite = intFileNumCamera
            Case "computer"
                intFileNumWrite = intFileNumComputer
            Case "external"
                intFileNumWrite = intFileNumExternal
            Case "other"
                intFileNumWrite = intFileNumOther
            Case "printer"
                intFileNumWrite = intFileNumPrinter
            Case "server"
                intFileNumWrite = intFileNumServer
            Case "switch"
                intFileNumWrite = intFileNumSwitch
        End Select

        Print #intFileNumWrite, "define host{"; Chr(10);
        Select Case strItem(3)
            Case "ap"
                Print #intFileNumWrite, "        use             ap              ; Inherit default values from a template"; Chr(10);
            Case "camera"
                Print #intFileNumWrite, "        use             camera          ; Inherit default values from a template"; Chr(10);
            Case "computer"
                Print #intFileNumWrite, "        use             computer        ; Inherit default values from a template"; Chr(10);
            Case "external"
                Print #intFileNumWrite, "        use             external        ; Inherit default values from a template"; Chr(10);
            Case "other"
                Print #intFileNumWrite, "        use             other           ; Inherit default values from a template"; Chr(10);
            Case "printer"
                Print #intFileNumWrite, "        use             printer         ; Inherit default values from a template"; Chr(10);
            Case "server"
                Print #intFileNumWrite, "        use             server          ; Inherit default values from a template"; Chr(10);
            Case "switch"
                Print #intFileNumWrite, "        use             switch          ; Inherit default values from a template"; Chr(10);
        End Select
        Print #intFileNumWrite, "        host_name       "; strItem(0); "         ; The name we're giving to this device"; Chr(10);
        Print #intFileNumWrite, "        alias           "; strItem(1); "         ; A longer name associated with the device"; Chr(10);
        Print #intFileNumWrite, "        address         "; strItem(2); "         ; IP address of the device"; Chr(10);
        Print #intFileNumWrite, "        hostgroups      "; strItem(3); "         ; Host groups this device is associated with"; Chr(10);
        Print #intFileNumWrite, "        }"; Chr(10); Chr(10);

        Print #intFileNumWrite, "define service{"; Chr(10);
        Print #intFileNumWrite, "        use                     generic-service ; Inherit values from a template"; Chr(10);
        Print #intFileNumWrite, "        host_name               "; strItem(0); "        ; The name of the host the service is associated with"; Chr(10);
        Print #intFileNumWrite, "        service_description     PING            ; The service description"; Chr(10);
        Print #intFileNumWrite, "        check_command           check_ping!3000,25%!5000,90%    ; The command used to monitor the service"; Chr(10);
        Print #intFileNumWrite, "        normal_check_interval   5               ; Check the service every 5 minutes under normal conditions"; Chr(10);
        Print #intFileNumWrite, "        retry_check_interval    1               ; Re-check the service every minute until its final/hard state is determined"; Chr(10);
        Print #intFileNumWrite, "        }"; Chr(10); Chr(10);
    Loop

    Close #intFileNumRead
    Close #intFileNumAP
    Close #intFileNumCamera
    Close #intFileNumComputer
    Close #intFileNumExternal
    Close #intFileNumOther
    Close #intFileNumPrinter
    Close #intFileNumServer
    Close #intFileNumSwitch
End Sub

The files that are created use Unix/Linux standard line feed end of line marker characters, rather than the Windows standard carriage return/line feed combination characters.  As such, opening the generated files using Notepad is not advised.  Copy the generated files back to the /opt/etc/objects/ path on the DiskStation (copy the files to a Shared Folder on the DiskStation, then use the cp command to copy the files from the share location to /opt/etc/objects/ – the Shared Folders are typically created as a subdirectory in the /volume1/ directory).

If you decided to use some of the non-standard Nagios group names (as I did), those non-standard group names must be defined in the /opt/etc/objects/templates.cfg file:

vi /opt/etc/objects/templates.cfg

A portion of the additional entries that I made in this file include the following:

define host{
       name                    ap      ; The name of this host template
       use                     generic-host    ; Inherit default values from the generic-host temp
       check_period            24x7            ; By default, access points are monitored round t
       check_interval          5               ; Actively check the access point every 5 minutes
       retry_interval          1               ; Schedule host check retries at 1 minute intervals
       max_check_attempts      10              ; Check each access point 10 times (max)
       check_command           check_ping      ; Default command to check if access points are "alive"
       notification_period     24x7            ; Send notification out at any time - day or night
       notification_interval   30              ; Resend notifications every 30 minutes
       notification_options    d,r             ; Only send notifications for specific host states
       contact_groups          admins          ; Notifications get sent to the admins by default
       hostgroups              ap ; Host groups that access points should be a member of
       register                0               ; DONT REGISTER THIS - ITS JUST A TEMPLATE
       }

define host{
       name                    camera  ; The name of this host template
       use                     generic-host    ; Inherit default values from the generic-host temp
       check_period            24x7            ; By default, cameras are monitored round t
       check_interval          60              ; Actively check the device every 60 minutes
       retry_interval          1               ; Schedule host check retries at 1 minute intervals
       max_check_attempts      10              ; Check each device 10 times (max)
       check_command           check_ping      ; Default command to check if device are "alive"
       notification_period     24x7            ; Send notification out at any time - day or night
       notification_interval   240             ; Resend notifications every 240 minutes
       notification_options    d,r             ; Only send notifications for specific host states
       contact_groups          admins          ; Notifications get sent to the admins by default
       hostgroups              camera ; Host groups that cameras should be a member of
       register                0               ; DONT REGISTER THIS - ITS JUST A TEMPLATE
       }

Nagios will not know that it should read the additional configuration files until it is told to do so by modifying the /opt/etc/nagios.cfg file.

vi /opt/etc/nagios.cfg

Add the following lines to the nagios.cfg file:

# Charles Hooper's object types
cfg_file=/opt/etc/objects/ap.cfg
cfg_file=/opt/etc/objects/camera.cfg
cfg_file=/opt/etc/objects/computer.cfg
cfg_file=/opt/etc/objects/external.cfg
cfg_file=/opt/etc/objects/other.cfg
cfg_file=/opt/etc/objects/printer.cfg
cfg_file=/opt/etc/objects/server.cfg
cfg_file=/opt/etc/objects/switch.cfg

We have made a large number of changes to the configuration files, so it is important to verify that there are no errors in the configuration:

/opt/bin/nagios -v /opt/etc/nagios.cfg

If no errors are found in the configuration, terminate (kill) nagios and then restart as described above.

—-

Update July 28, 2013:

When attempting to start Nagios in daemon mode (/opt/bin/nagios -d /opt/etc/nagios.cfg) I encountered a couple of problems related to permissions for the Nagios user.  The nagios process was not listed when I used the ps command.  I then tried executing the following commands:

touch /opt/var/nagios.log
chown nagios:nagios /opt/var/nagios.log

Nagios was then able to start in daemon mode, but wrote messages similar to the following in the /opt/var/nagios.log file:

[1375058364] Warning: Could not open object cache file ‘/opt/var/objects.cache’ for writing!
[1375058364] Failed to obtain lock on file /opt/var/nagios.lock: Permission denied
[1375058364] Bailing out due to errors encountered while attempting to daemonize… (PID=11451)
[1375058656] Nagios 3.5.0 starting… (PID=12936)
[1375058656] Local time is Sun Jul 28 20:44:16 EDT 2013
[1375058656] LOG VERSION: 2.0
[1375058656] Warning: Could not open object cache file ‘/opt/var/objects.cache’ for writing!
[1375058656] Failed to obtain lock on file /opt/var/nagios.lock: Permission denied
[1375058656] Bailing out due to errors encountered while attempting to daemonize… (PID=12936)
[1375060107] Error: Unable to create temp file for writing status data: Permission denied
[1375060117] Error: Unable to create temp file for writing status data: Permission denied
[1375060127] Error: Unable to create temp file for writing status data: Permission denied
[1375060137] Error: Unable to create temp file for writing status data: Permission denied
[1375060147] Error: Unable to create temp file for writing status data: Permission denied
[1375060157] Error: Unable to create temp file for writing status data: Permission denied

I tried to set the permissions for a couple of other files, only to find another long list of Permission denied messages:

touch /opt/var/objects.cache
touch /opt/var/nagios.lock
touch /opt/var/nagios.tmp
chown nagios:nagios /opt/var/objects.cache
chown nagios:nagios /opt/var/nagios.lock
chown nagios:nagios /opt/var/nagios.tmp

I then recalled that I had seen similar messages on the DiskStation DS412+.  I then tried a different approach, creating a nagios directory in the /opt/var directory, creating a couple of subdirectories in that directory, and then assigning nagios as the owner of that directory structure:

mkdir /opt/var/nagios
mkdir /opt/var/nagios/archives
mkdir /opt/var/nagios/spool
mkdir /opt/var/nagios/spool/checkresults
chown nagios:nagios /opt/var/nagios -R
vi /opt/etc/nagios.cfg

In the nagios.cfg file, I made the following changes:

log_file=/opt/var/nagios/nagios.log
status_file=/opt/var/nagios/status.dat
lock_file=/opt/var/nagios/nagios.lock
temp_file=/opt/var/nagios/nagios.tmp
log_archive_path=/opt/var/nagios/archives
check_result_path=/opt/var/nagios/spool/checkresults
state_retention_file=/opt/var/nagios/retention.dat
debug_file=/opt/var/nagios/nagios.debug

After saving the file and exiting vi, I restarted Nagios in daemon mode.  Reading the last 100 lines of the Nagios log file is now accomplished with this command:

tail -n 100 /opt/var/nagios/nagios.log

—-

There are a lot of seemingly interesting Nagios plugins, including check_oracle (I believe that this plugin requires the Oracle client to be installed – good luck with that install).  On one of the DiskStations the check_snmp plugin did not compile, while on the other DiskStation the check_http plugin did not compile.

It might be interesting to see what solutions readers are able to develop from the above starting point.  The above information is the result of many hours of experimentation as well as a couple minutes reading through sections of the Nagios documentation (it reads like the Oracle Database documentation, so it should be an easy read once I am in the right mood) and hopelessly scanning the ‘net for information about obscure error messages.  Have fun, and try not to put the DiskStation out of service due to a mistaken file copy.

Update November 19, 2013:

Installing an updated version of the Synology DSM operating system may temporarily disable Nagios.  Make backups of all Nagios confirguration files (copying the files with the cp command to a directory in /volume1 is generally safe) before installing different versions of the Synology DSM operating system.

The DSM 4.3 operating system installation apparently removed the /var/services/homes directory.  That directory removal makes it impossible for the Nagios user to login to run various commands.  I assume that the removal of the homes directory is intentional, so a work around for that problem:

mkdir /var/services/home
mkdir /var/services/home/nagios
chown nagios:nagios /var/services/home/nagios -R
vi /etc/passwd

In the /etc/passwd file, change all /homes/ entries to /home/ then save and exit vi.

The installation of the different DSM version (including versions before 4.3) will likely also replace/remove the libltdl.* files located in /opt/local/lib and /usr/lib, so we need to copy those files back into the correct directories:

cp /opt/lib/libltdl.so.3 /opt/local/lib/libltdl.so.3
cp /opt/lib/libltdl.so.3 /usr/lib/libltdl.so.3
cp /opt/lib/libltdl.so /usr/lib/

Once the above items are copied, try executing the check_ping command as the nagios user (replace MyDeviceHere with either an IP address or the name of a device on your network).

su - nagios -c "/opt/libexec/check_ping -H MyDeviceHere -w 5000,80% -c 5000,80% -p 5"

If the DiskStation reports that the check_ping command was not found, then copy that file back to the /opt/libexec/ directory.  If the above command was successful, try verifying the Nagios configuration:

/opt/bin/nagios -v /opt/etc/nagios.cfg

If the verification was successful, start Nagios as a daemon:

/opt/bin/nagios -d /opt/etc/nagios.cfg

Execute the ps command and verify that the above command is listed in the running processes:

ps

Finally, verify that Nagios is still set to start automatically as a daemon:

ls /opt/etc/init.d/S81nagios

If a file is listed when the above command is executed, then Nagios should now be fully repaired.

-








Follow

Get every new post delivered to your Inbox.

Join 144 other followers