Archive for the ‘Materialized Views’ Category
SQL Tuning with SQLTXPLAIN 2-days Workshop
SQLTXPLAIN is a SQL Tuning tool widely used by the Oracle community. Available through My Oracle Support (MOS) under document 215187.1, this free tool is available for download and use to anyone with MOS access. It has helped thousands of times to expedite the resolution of SQL Tuning issues, and many Oracle DBAs and Developers benefit of its use on a daily basis.
Stelios Charalambides has done an excellent job writing a book on this topic. In his book Stelios covers many aspects about SQLTXPLAIN and some related topics. I highly recommend to get a copy of this book if you want to learn more about SQLTXPLAIN. It is available at Amazon and many other retailers.
The new 2-days SQLTXPLAIN Workshop offered by Enkitec (an Oracle Platinum business partner and my employer) is a completely new course that interleaves “how to use effectively SQLTXPLAIN” with important and related SQL Tuning Topics such as Plan Flexibility and Plan Stability. This hands-on workshop offers participants the unique opportunity to fully understand the contents of SQLTXPLAIN and its vast output through an interactive session. About half the time is dedicated to short guided labs, while the other half uses presentations and demos. This workshop is packed with lots of content. It was a real challenge packaging so much info in only two days, but I am very pleased with the result. It became a 2-days intensive knowledge transfer hands-on workshop on SQLTXPLAIN and SQL Tuning!
The first session of this workshop is scheduled for November 7-8 in Dallas, Texas. I expect this pilot session to fill out fast. Other sessions and onsite ones will be offered during 2014. I hope to meet many of you face to face on November 7!
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 %)
Carlos Sierra's Tools and Tips 