Skip navigation.

DBA Blogs

Using hints with coe_xfr_sql_profile.sql

Bobby Durrett's DBA Blog - Wed, 2014-03-19 15:16

In an earlier blog post I showed how I used coe_xfr_sql_profile.sql from the sqlt toolkit from Oracle support to force a particular sql statement to run with a more efficient plan that it had used in the past.

Today, I’m trying a slight variation.  Take the problem query, run it with hints, and use coe_xfr_sql_profile.sql to apply the resulting plan to the original query.  I built a quick and dirty test to make sure it works.

-- create test table with index and stats

SQL> create table test as select * from dba_tables;

SQL> create index testi on test(owner);

SQL> execute dbms_stats.gather_table_stats(NULL,'TEST');

-- run query unhinted

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- show plan - uses the index

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 1551939256

--------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |
|   1 |  SORT AGGREGATE              |       |     1 |     9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |  2122 | 19098 |
|*  3 |    INDEX RANGE SCAN          | TESTI |  2122 |       |
--------------------------------------------------------------

-- use hint to force full scan

SQL> select /*+full(test) */ sum(blocks) from test 
where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- get plan with full scan

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5521zhmq67vun, child number 0
-------------------------------------
select /*+full(test) */ sum(blocks) from test 
where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

So, let’s pretend that sql_id=a0nw2xjnmjcxd is the real production query you are trying to tune.  Without hints it is doing an index range scan.  With a hint you run a similar query forcing a full scan.  The new query, sql_id=5521zhmq67vun has a plan with this hash value: 3467505462.  So, I used coe_xfr_sql_profile.sql to force the unhinted original query to run with the hinted query’s plan:

cd /var/opt/oracle/admin/sqlt/utl

-- login to sqlplus as system

@coe_xfr_sql_profile.sql a0nw2xjnmjcxd 3467505462

-- login to sqlplus as system

@coe_xfr_sql_profile_a0nw2xjnmjcxd_3467505462.sql

Now when I run the original query it runs with the new plan and indicates that the profile is in use:

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

Note
-----
   - SQL profile coe_a0nw2xjnmjcxd_3467505462 used for 
     this statement

I edited the output to make it more readable.  See the zip with the full scripts and logs.

So, if you have a recurring production SQL query that runs faster with hints just run that query once with the hints and then use coe_xfr_sql_profile.sql to force the real query to run unchanged but with the same plan as the hinted query.

- Bobby

 

 

 

 

 

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Oracle IT Architecture Certification Program

Years of experience in implementing the world’s most complex business and technology projects have provided Oracle the expertise to meet any strategic goal. Helping you evaluate products is just one...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Oracle Cloud Opportunities: The broadest SaaS Portfolio in the Market

Cloud computing is a significant advancement in the delivery of information technology and services and we see a strong industry trend going there. By providing on demand access to a shared pool...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Foundation for Innovation: Oracle Fusion Middleware

Oracle Fusion Middleware is the leading business innovation platform for the enterprise and the cloud. It enables you to create and run agile, intelligent business applications while maximizing...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle Database 12c Security: New Unified Auditing

According to the  2012 Data Breach Investigations Report from the Verizon RISK Team, more than 1 billion database records were breached around the world from 2004 to 2011. As companies...

We share our skills to maximize your revenue!
Categories: DBA Blogs

WebLogic Suite: Foundation Infrastructure for Oracle iAS (Internet App Server) Implementations

WebLogic has become the strategic application server infrastructure for Oracle Fusion Middleware and Applications. It is designed to provide a standards-based, mission critical and secure...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle SOA Suite 11g - The Ingredient for Innovation: New Integration Adapters

Enterprise of all sizes, are evaluating cloud computing. Many businesses are making new economic investments in public cloud, while they continue to rely on their existing on-premise IT investments....

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast - Oracle WebLogic Server & Oracle Database 12c Integration

Oracle WebLogic Server 12c, the latest generation of the leading Enterprise-class Java EE Application Server, and Oracle Database 12c, the latest release of the world’s #1 database, have both been...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Oracle ISV Application Modernization: It's All about the Business

Infographic: See How Cloud  Empowers Innovation Technology is changing the world in ways we haven’t seen before. Industries are evolving,...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Oracle WebLogic Server Management using Oracle Enterprise Manager 12c

Increasingly, applications running on applications servers truly represent the customer experience. As a result, the traditional line between mission-critical and non-mission critical applications is...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle EMEA Partner Community Forums - April 2014, Prague, Czech Republic

We are delighted to invite you to the 2014 editions of the Exadata, Exalogic & Manageability and of the Servers and Storage Partner Community Forums for EMEA partners, which will take place...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Oracle Virtual Assembly Builder with Oracle VM 3.2

Increased operating costs, inefficient hardware utilization and rapidly expanding data centers have made virtualization the most compelling IT technology in years. Virtualization for desktop and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast - Oracle Mobility Business Awareness - 29 Jan 2014

According to many analysts, the number of mobile devices users will exceed the number of computer users by the end of 2013, and will become the dominant platform for accessing applications and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

WebLogic Server 12c - PerDomain Node Manager Configuration Model

Let's start with giving a brief definition of Node Manager. Server instances in a WebLogic Server production environment are often distributed across multiple domains, machines, and geographic...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Oracle Business Analytics: Make data work for you!

Organizations today have to remain competitive in a rapidly changing economic environment with increased global competition, reduced product lifecycles, and growing volumes of data. It has become...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Oracle Engineered Systems & Partner Service Opportunities

The old way to buy servers, storage and networking equipment in different cycles has reached its end. Today we see a trend towards buying integrated systems that are tested, certified, sold and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Wed, 1969-12-31 18:00