SQL Profile - Play with Execution plan

 Although i don't have too much experience working with outlines, but these days i am working significantly with SQL Profile. My Favorite blogger Kerry Osborne has written a wonderful blog on

this topic which gives me very clear idea of sql profile.


Outlines basically locks the profile , but sql profile plays with empirical value of the sql plan which normally known as fudge factor. It's give the flexibility to optimizer to look for alternate plan.

But we can say sql profile advantages will fade with time.


Hints used in sql profile, these hints are very likely documents so wanted to give as much information as possible:


1. Setting Optimizer Mode : ALL_ROWS


2. Disable hints present in sql : IGNORE_OPTIM_EMBEDDED_HINTS


3.Setting OPTIMIZER_FEATURES_ENABLE to it's default Value i.e active all available feature :OPTIMIZER_FEATURES_ENABLE (default)


4.Adjusting number of rows returned from the table:

OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=10)

 Eg : 10 times as many rows are expected to return


5. Adjusting number of rows returned from index scan:

OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=.4)

Eg: 4 times fewer rows will be returned using index skip scan.


6. Table, index and column statistics adjusted using below hints:

COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)

TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)

OPTIMIZER_FEATURES_ENABLE(default)

Below are some scripts to find out hints from a sql profile and awr snapshots ( scripts are borrowed from Kerry blogs )


SQL> set echo on

SQL> @sql_profile_hints

set lines 155

col hint for a150

select attr_val hint

from dba_sql_profiles p, sqlprof$attr h

where p.signature = h.signature

and name like ('&profile_name')

order by attr#

 /

Enter value for profile_name: PROFILE_0qa98gcnnza7h


HINT

------------------------------------------------------------------------------------------------------------------------------------------------------

OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=0.0536172171)

OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=4)

COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL1", scale, length=6 distinct=1234 nulls=0 min=1000000014 max=1026369632)

COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL2", scale, length=12 distinct=2 nulls=0)

COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)

TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)

OPTIMIZER_FEATURES_ENABLE(default)


SQL> @sql_hints_awr

 select

 extractvalue(value(d), '/hint') as outline_hints

 from

 xmltable('/*/outline_data/hint'

 passing (

 select

 xmltype(other_xml) as xmlval

 from

 dba_hist_sql_plan

where

sql_id = '&sql_id'

and plan_hash_value = &plan_hash_value

and other_xml is not null

)

) d;

Enter value for sql_id: 0qa98gcnnza7h

Enter value for plan_hash_value: 568322376


OUTLINE_HINTS

-----------------------------------------------------------------------------------------------------------------------------------------------------------

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "SKEW"@"SEL$1")


Scripts to create sql profile ( Kerry osborne blog reference ) :


create_sql_profile.sql – uses cursor from the shared pool

create_sql_profile_awr.sql – uses AWR tables

sql_profile_hints.sql – shows the hints in a SQL Profile for 10g



Basic Commands:

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

DBMS_SQLTUNE.ALTER_SQL_PROFILE

DBMS_SQLTUNE.DROP_SQL_PROFILE



set verify off exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');


set verify off

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'DISABLED');


Reference Links :


http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more