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
|
1 |
SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES; |
Check the SQL Profile internal hint information
|
1 2 3 4 5 6 |
SELECT a.name ,b.comp_data FROM dba_sql_profiles a ,dbmshsxp_sql_profile_attr b WHERE a.name = b.profile_name; |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '2qpu2862c428h', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 300, task_name => 'tuning_task_test_2qpu2862c428h', description => 'Tuning task1 for statement 2qpu2862c428h'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘tuning_task_test_2qpu2862c428h’); set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task(‘tuning_task_test_2qpu2862c428h’) from dual; |
|
1 |
EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘tuning_task_test_2qpu2862c428h’, description => 'SQL profile for sql_id 2qpu2862c428h’); |
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
|
1 2 |
--Check select name, type, status, sql_text from dba_sql_profiles; |
|
1 2 3 4 5 6 |
-- Enable exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER( parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE'); -- Disable DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER( parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE'); |
Disable the SQL Profiles
|
1 2 3 4 5 6 7 |
BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'tuning_task_test_2qpu2862c428h', attribute_name => 'STATUS', value => 'DISABLED'); END; / |
Drop the SQL Profile
|
1 |
exec dbms_sqltune.drop_sql_profile('tuning_task_test_2qpu2862c428h'); |
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 : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
