Manage SQL Profile in Oracle Database

Share via:

Hi Dears,

In this article, we will see Manage SQL Profile in Oracle Database

A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query.
The profile is stored in the data dictionary. The optimizer uses this information during optimization to determine the most optimal plan.
A SQL profile contains, among other statistics, a set of cardinality adjustments.
The cardinality measure is based on sampling the WHERE clause rather than on statistical projection.
A profile uses parts of the query to determine whether the estimated cardinalities are close to the actual cardinalities and,
if a mismatch exists, uses the corrected cardinalities. For example, if a SQL profile exists for SELECT * FROM t WHERE x=5 AND y=10,
then the profile stores the actual number of rows returned.
When choosing plans, the optimizer has the following sources of information:
• The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
• The supplemental statistics in the SQL profile

Check the SQL Profile in the Database

Check the SQL Profile internal hint information

Create the SQL Profiles
We can create SQL Profiles in Two ways:
1. Creating the SQL Profiles while running the manual SQL tuning task
For manually creating with SQL Tuning task

2. Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher)
Check SQL profiles that have automatically been created having the value “AUTO” in the TYPE column of the DBA_SQL_PROFILES views

Disable the SQL Profiles

Drop the SQL Profile

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook :
LinkedIn :
Twitter :
YouTube :


Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment