Taming the AWR Tsunami 05 – Extracting Value from dba_hist_sql_plan

Introduction:

In past posts in the “Taming the AWR Tsunami” series we looked at the active session history, troubleshooting SQL execution problems and also troubleshooting user connection issues.  In this post I will get into some interesting ways to use the information about SQL Execution Plans exposed by Oracle in the view DBA_HIST_SQL_PLAN.

Why do I care about dba_hist_sql_plan?

Oracle has a number of tools that will show you the execution plan for a particular SQL statement from the history, but there is much more that I can do with the information found in dba_hist_sql_plan, just by querying it with plain ordinary SQL.  There are a number of use cases I can address by using the information in dba_hist_sql_plan, for example:

·        What SQL statements touch a particular object?

·        What are all the objects that are touched by a particular SQL statement?

·        What are the table/index stats for all the objects used in a particular SQL Statement?

What SQL statements touch a particular object?

Understanding the relationship between the views DBA_HIST_SQL_PLAN, DBA_TABLES, DBA_INDEXES and DBA_OBJECTS can provide valuable insights for performance analysis and impact assessment in Oracle databases. By querying DBA_INDEXES with a specific schema name and table name, you can  retrieve all the indexes associated with that table.  Now, with your list of tables and indexes you can query a join against DBA_HIST_SQL_PLAN and DBA_OBJECTS to get all the SQL statements that involve that list of objects.

The list of SQL statements can be useful in the context of SQL performance analysis as there may be other SQL statements that should be evaluated for performance, say when an object is scaled up in size.

For developers, the list of SQL statements can be valuable for assessing the impact of schema changes on existing SQL statements. For example, if a table is being changes, one could evaluate which SQL may also be impacted.  In another case, if a new index is added to a table, developers can use this information to identify and optimize SQL statements that could benefit from the new index.

Overall, the relationship between DBA_HIST_SQL_PLAN, DBA_TABLES, DBA_INDEXES and DBA_OBJECTS provides a powerful tool for performance tuning, impact assessment, and query optimization in Oracle databases.

The query below shows an example of how I would find all the SQL associated with a table.

-- SQL_ID's for an object:

with objects as

(

select 'TABLE' object_type, owner, table_name object_name

from dba_tables

where owner = :owner and table_name = :object_name

union

select 'INDEX' object_type, owner, index_name object_name

from dba_indexes

where table_owner = :owner and table_name = :object_name

)

-- ...

select distinct sql_id

from dba_hist_sql_plan sp

, dba_objects do

, objects o

where sp.object#  = do.object_id

  and do.owner = o.owner

  and do.object_name = o.object_name

  and do.object_type = o.object_type

  and sp.object# is not null

order by sql_id

;

What are all the objects that are touched by a particular SQL statement?

In the case of the reverse situation, where you know the SQL statement and want to know all the objects touched by it, this can be addressed using a similar approach. By leveraging the relationships between DBA_HIST_SQL_PLAN, DBA_TABLES, DBA_INDEXES, and DBA_OBJECTS, you can identify the objects referenced in a given SQL statement.

First, you can query DBA_HIST_SQL_PLAN to retrieve the execution plan for the SQL statement of interest. This plan will include information about the tables and indexes involved in the query.

Next, you can join this information with DBA_OBJECTS to get details about these objects, such as their names, types, and owners. This step helps you understand the specific objects referenced in the SQL statement.

Overall, by combining these views, you can gain a comprehensive understanding of the objects touched by a given SQL statement, enabling you to assess the impact of schema changes, optimize queries, and tune database performance.

The query below shows an example of how I would find all the tables and indexes associated with a particular SQL statement.

-- objects for a SQL_ID:

with objects as

(

select 'TABLE' object_type, owner, table_name object_name

from dba_tables

where owner = :owner and table_name = :object_name

union

select 'INDEX' object_type, owner, index_name object_name

from dba_indexes

where table_owner = :owner and table_name = :object_name

)

select distinct sql_id

from dba_hist_sql_plan sp

, dba_objects do

, objects o

where object#  = object_id

  and do.owner = o.owner

  and do.object_name = o.object_name

  and do.object_type = o.object_type

  and object# is not null

order by sql_id

;

What are the table/index stats for all the objects used in a particular SQL Statement?

Additionally, with the SQL_ID and list of tables from DBA_HIST_SQL_PLAN I can then query DBA_TABLES and DBA_INDEXES, and retrieve further details about the tables and indexes, such as their sizes, when stats were gathered, etc.  This information can be useful for performance analysis of a particular SQL statement.

Here’s an example of getting the TABLE CARDINALITY for a SQL_ID:

select owner, table_name, num_rows

, round(blocks * 8192 / (1024*1024)) size_meg

, last_analyzed

from DBA_tables a

where 1=1

and (owner, table_name) in 

    (   select object_owner owner, object_name table_name

        from DBA_HIST_SQL_PLAN sp

        where object_type = 'TABLE'

          and sql_id = '&SQLID'

        union

        select object_owner owner, object_name table_name

        from DBA_HIST_SQL_PLAN sp

        where operation like 'LOAD TABLE%'

          and object_type is null

          and sql_id = '&SQLID'

        union

        select object_owner owner, object_name table_name

        from DBA_HIST_SQL_PLAN sp

        where object_type like 'MAT%VIEW'

          and sql_id = '&SQLID'

        union

        select owner, table_name 

        from dba_indexes

        where (owner, index_name) in 

            (select object_owner owner, object_name index_name

            from DBA_HIST_SQL_PLAN sp

            where object_type like 'INDEX%'

            and sql_id = '&SQLID'

            )

    )

;

Likewise, here is an example of getting the index information for a SQL_ID:

select ic.index_owner, ic.table_name, ic.index_name, ic.column_name

, i.index_type , i.last_analyzed

from dba_ind_columns ic

, dba_indexes i

where 1=1

and ic.table_owner = i.table_owner

and ic.table_name = i.table_name

and ic.index_name = i.index_name

and (ic.table_owner, ic.table_name) in 

    (   select object_owner owner, object_name table_name

        from DBA_HIST_SQL_PLAN  sp

        where object_type = 'TABLE'

          and sql_id = '&SQLID'

        union

        select object_owner owner, object_name table_name

        from DBA_HIST_SQL_PLAN sp

        where operation like 'LOAD TABLE%'

          and object_type is null

          and sql_id = '&SQLID'

        union

        select object_owner owner, object_name table_name

        from DBA_HIST_SQL_PLAN sp

        where object_type like 'MAT%VIEW'

          and sql_id = '&SQLID'

        union

        select owner, table_name 

        from dba_indexes

        where (owner, index_name) in 

            (select object_owner owner, object_name index_name

            from DBA_HIST_SQL_PLAN  sp

            where object_type like 'INDEX%'

            and sql_id = '&SQLID'

            )

    )

order by 1,2,3, column_position

;

Summary

In this instalment in the Taming the AWR Tsunami series I hoped to convince you that you care about DBA_HIST_SQL_PLAN because it provides a wealth of information that can be used to address various use cases related to SQL performance analysis and optimization. While Oracle offers tools to view execution plans, querying DBA_HIST_SQL_PLAN directly allows you to delve deeper into SQL statement analysis. This view enables you to identify which SQL statements touch a particular object, determine all the objects touched by a specific SQL statement, and gather table/index stats for objects used in a given SQL statement. This depth of information can be invaluable for database performance tuning and impact assessment.

Stay tuned to see what I have in store for you next in my bag-of-tricks.