Archive for January 2013
Materialized Views Health Check and Diagnostics Reports
About mvhcdr.sql
I uploaded to My Oracle Support (MOS) under document_id 1517362.1 a new script mvhcdr.sql to aid in the diagnostics of issues regarding Materialized Views (MV). This script installs nothing in the database and it produces a zip file with 5 zip files within. The output includes HTML and CSV files from all DBA views related to Materialized Views, as well as Metadata for the MVs and their Logs. Output also includes some Health-Checks like: MV Logs with Outdated Statistics, MV Logs larger than their Master Table, Complete Refresh Candidates and Old Materialized Views. A list of Materialized Views capabilities is also included within the set of output reports, as well as Logs per MV and MVs per Log with some important attributes. CBO Statistics, actual row counts, segment sizes in blocks, indexes, partitions and similar related metadata is included as well. The output of mvhcdr.sql is quite comprehensive. You can find an output sample under same MOS 1517362.1.
The main reason to have this new script is to aid Oracle Support to collect diagnostics information around Materialized Views. It can also be used by any Oracle user that may want to analyze the health of the MVs on a system, or two compare their setup to a similar database. Another use is to simply document all these MVs on a database, together with their related metadata.
You can download mvhcdr.sql from its own MOS note, or from SQLTXPLAIN 11.4.5.4 or higher. If the latter, you can find mvhcdr.sql under directory sqlt/utl.
This new script mvhcdr.sql can be used on 10g and 11g databases. You execute it connected as SYS. It has a couple of pre-requisites and just one execution parameter. The output gets generated under the same local directory from where you executed SQL*Plus.
Pre-requisites
Tables PLAN_TABLE and MV_CAPABILITIES_TABLE must be available. Else, create them with following scripts:
- PLAN_TABLE: $ORACLE_HOME/rdbms/admin/catplan.sql; or $ORACLE_HOME/rdbms/admin/utlxplan.sql;
- MV_CAPABILITIES_TABLE: $ORACLE_HOME/rdbms/admin/utlxmv.sql;
The MV_CAPABILITIES_TABLE is used to generate and report MV Capabilities as the name implies. The PLAN_TABLE is used as a staging repository for the duration of the execution of mvhcdr.sql.
Execution Parameters
- Materialized View name or prefix (optional, defaults to %)
Deep-dive on SQLTXPLAIN offered externally for the first time
The Rocky Mountain Oracle Users Group (RMOUG) Training Days is approaching soon: February 11-13, 2013. I hope you are attending this Oracle relevant event. There are so many good sessions that it is hard to decide which ones to attend!
For the first time, I will be conducting a 4-hours deep-dive external-session on SQLTXPLAIN (SQLT). The title is Using SQLTXPLAIN (SQLT) to Diagnose SQL Statements Performing Poorly. If you want to know SQLT from the basics to an intermediate level, consider attending this 4-hrs session. It will be offered on Monday, February 11 from 1 to 5 pm at the Colorado Convention Center in Denver, Colorado (USA). I love interactive sessions, so I will encourage voluntary participation and open discussions.
In addition to the 4-hrs deep-dive on SQLT, I will also speak on Wednesday, February 13 at 8:30 am about How to Improve SQL Performance with New SQL Health Check Tool. This is a 1-hour seminar with plenty of time for Q&A.
If you attend the event and want to discuss any issue with regard to SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) or simply SQL Tuning, please approach me. I will be there to help, share, listen, learn, discuss, etc.
Where are my SQLTXPLAIN files?
Every so often I get this question in multiple forms:
- Where does SQLTXPLAIN (SQLT) genetares the output files?
- I cannot find my SQLTXPLAIN output.
- SQLTXPLAIN (SQLT) disconnected. Where do I see the error?
- SQLT failed to execute. How do I know what happened?
SQLT main methods (XTRACT, XECUTE, XTRXEC, XTRSBY and XPLAIN) open a log file under the current SQL*Plus directory from where you are executing the SQLTXPLAIN module. This log file contains the name of the module: sqltxtract.log, sqltxecute.log, etc. So, finding the reason WHY your SQLT failed requires that you find and read that log file.
Then the original question becomes “How do I know which is my current SQL*Plus directory?”. I used to answer: “the same from where you started SQL*Plus”. But to provide a better answer now I say:
To find your SQL*Plus current directory and SQLT log, please issue this command from your SQL*Plus session:
- From UNIX or Linux:
- HOS pwd
- HOS ls sqlt*.log
- From WIndows:
- HOS dir sqlt*.log
If your SQLT method completed successfully then the log file is compressed into a ZIP file, together with the entire SQLT output. Look then for sqlt_sNNNNN_<method><id>.zip. Where NNNNN is your SQLT statement_id (a sequence). This ZIP file is also created under your current SQL*Plus directory.
SQL Dynamic eXtractor (SQLDX) is now available under SQLT and SQLHC
Background
As most of you know SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC) include lots of information about one SQL statement. SQLT requires to install an schema and some objects while SQLHC requires no installation. The main difference between the two are:
- The level of details: (SQLT provides a lot more).
- The capability of automatic Test Case (TC) extraction (SQLT does this).
SQLT is always the preferred method, while SQLHC is the fall back when SQLT cannot be installed in a timely manner.
Both SQLT and SQLHC work on 10g and 11g databases. SQLT presents details about many GV$ and DBA views referencing your SQL statement. These views include both 10g and 11g. In other words, SQLT is not restricted to the minimum common denominator (10g) but it expects to extract information from any relevant view referencing a SQL_ID on 11g. There is one small limitation although: SQLT does not show in its main report all views and tables referencing a SQL_ID, but most of what is usually needed for SQL Tuning.
What is the SQL Dynamic eXtractor (SQLDX)?
SQL Dynamic eXtractor (SQLDX) is a new stand-alone SQL script (sqldx.sql) that requires no installation. It dynamically generates another script that selects from all SYS views and tables which reference a column SQL_ID, extracting all rows matching the SQL_ID passed. It then computes the SIGNATURE for the SQL’s text and proceeds to generate dynamic SQL to select from all SYS tables and views referencing this SIGNATURE. For SIGNATURE it uses exact and force matching.
SQLDX input and output
SQLDX output is a ZIP file that contains the output of all dynamic SELECTs on all SYS tables and views referencing your SQL_ID and its SIGNATURE (exact and force). The output of every dynamic SELECT is a file on its own. Their format is HTML and Coma Separated Values (CSV). The latter can be opened in Excel to ease aggregations, finer selections, sorts, etc.
SQLDX takes as input 3 parameters:
- Oracle Pack License: Diagnostics Pack or Tuning Pack (D|T).
- Output Type: HTML, CSV or BOTH (H|C|B).
- SQL_ID: resident in memory or AWR.
SQLDX is included on SQLT and SQLHC 11.4.5.3 and higher (released on December 31, 2012). SQLDX is automatically executed at the end of any SQLT main method and at the end of SQLHC. Keep in mind that SQLDX can also be executed stand-alone and it requires no installation whatsoever. So it can be used in a Production environment or in a read-only database.
Where do you get SQLDX from?
SQLT (MOS 215187.1) includes sqlt/run/sqldx.sql and SQLHC (MOS 1366133.1) includes sqlhc/sqldx.sql. It is the same sqldx.sql script. When sqldx.sql is executed from within SQLT or SQLHC it generates a ZIP file identified as “sqldx”. Look for it next time you use SQLT or SQLHC (version 11.4.5.3 or higher). Or you may want to try sqldx.sql stand-alone.
Carlos Sierra's Tools and Tips 