JL Computer Consultancy

Oracle 10g fills a trouble-shooting gap

Sept 2004

Does your system ever execute statements like

        create table t1 as {complex SQL query};

If so, you may have discovered that there is a very important difference between the way such "CTAS" statements are handled in 10g compared to the way that earlier versions of Oracle dealt with them.

The next time you create a table or build an index in any version lower than 10g, have a look in the view v$sql to see any sign of the work involved - the SQL to create a table or index is not sharable, so it doesn't go into shared memory (unless it is a parallel CTAS or index build, in which case all the parallel execution slaves will share the same code, and that code will appear in v$sql).

In 10g, this changes. For example, here is the output of a simple query against v$sql after creating and indexing a small test case:

        ---------- ---------- ----------- ---------- -------------- ------------- ----------
                 1        489       95244          0          44926           620    3354824
        create table t1 as select * from all_objects
                 0          0           0          0              0             0           0
        create index t1_i1 on t1(object_name)create index t1_i1 on t1(object_name)


The results are not perfect - clearly there should have been some indication of work done in creating the index - but at least we have a very clear picture of the cost of creating the table.

So why has this changed appeared - I suspect that it is related to the development of AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor) - the combination of tools that captures and analyzes the workload on the system. If you can't capture CTAS, there could be a huge gap in the information needed to provide intelligent analysis of the workload.

There are several ETL tools and reporting tools on the market that spend all their time creating scatchpad tables - if you are running one of these tools, then the most expensive SQL on your system may be the SQL that you can't see in v$sql. With the arrival of 10g, you may find that all those 'Top 10 SQL' reports suddenly start showing a completely different set of statements to investigate.

Back to Index of Topics