Ram Perumal

I’m a coffee lover—no other drink gives me as much motivation as a cup of filtered-coffee, day or night. I love data, passionate about data architecture, analytics and optimization. I architect and coach teams to build healthier, scalable and optimally performing data-intensive applications; and help enterprises develop insights that drive action, performance and business growth.

Data Quality: The Accuracy Dimension

Data quality is an important element in data management; it is a larger topic by itself. If you are looking for a book on data quality, don’t look further. Get a copy of Data Quality: The Accuracy Dimension (Morgan Kaufmann; 1st Edition, 2003). This book is about data accuracy, divided into three parts – Part …

Data Quality: The Accuracy Dimension Read More »

Virtual / NOSEGMENT / Fake Indexes

Virtual Indexes (aka NOSEGMENT Indexes or Fake Indexes) are useful to check whether the creation of an Index affects the execution plan with out having to create the actual index. Virtual index is just a definition without a physical index segment so it does not consume disk space. Additionally, virtual index is available to a …

Virtual / NOSEGMENT / Fake Indexes Read More »

Customizing TOAD SQL Explain Plan Content

The default display of Explain Plan in TOAD SQL Editor has very basic information.  Right-clicking on the explain plan and selecting “Adjust Content” from the context menu will bring “Execution Plan Preferences” window, which allows you to control the information displayed in the Explain Plan tab.

Optimizer Index Access Paths

Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements …

Optimizer Index Access Paths Read More »

Get a complete CREATE TABLE definition using DBMS_METADATA package

CREATE TABLE has lot of options to go with it; it is hard to remember all of them. Most people will get lost while reading the rail road diagrams. It is fairly quick to create the table in its simple form and obtain all the parameters associated with it. Once the table is created in …

Get a complete CREATE TABLE definition using DBMS_METADATA package Read More »

Upgrade path for Oracle Database 11g Release 1

Still quite a few remain in 7.3, 8.1.7 and (ofcourse, without a support on very old versions and happy with what they have) – Others are gearing towards to upgrade to Oracle 11g Release 1. The recommended direct and indirect path upgrade listed below: Direct Upgrade Path Source Database Target Database (or higher) 11.1.x …

Upgrade path for Oracle Database 11g Release 1 Read More »

Oracle Database Statistics Names

Oracle database statistic names in this post is from Oracle Database 11g Release  Many of these statistics are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice, even between patch releases. Application developers should be aware of this and write their code to tolerate missing or …

Oracle Database Statistics Names Read More »

DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3

You may have received the below error while you tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure. The error is due to the SQL*Plus environment variable SERVEROUTPUT is turned on. One of the Prerequisites for running the package is to turn off SERVEROUTPUT. You should be able to see the execution plan after turning …

DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3 Read More »

Setting up Oracle Statistics Package (STATSPACK)

To install statspack – you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. Additionally, you need have: the password you would like to set for PERFSTATS schema the default tablespace for PERFSTAT schema the temporary tablespace for PERFSTAT schema If you run it through SQL*Plus the output will …

Setting up Oracle Statistics Package (STATSPACK) Read More »