SQL Profiles are a powerful tool to improve performance of SQL queries in your Oracle Database. Most of the time, you implement them by the output of a SQL Tuning Task, but did you know, that you can write your own SQL Profile manually?
From time to time, the SQL Tuning Advisor just doesn’t get it right. Right? You can run him a dozen times for a problematic SQL query and still he only raises the implementation of a SQL Profile which will increase the parallelism of the statement. But you’re sure that there is a better way to run the query and you’ve proven so by running the query with an Optimizer Hint. But you don’t want to have a hint implemented in all of your problematic queries. This is possible by writing your own SQL Profile and I will show you how this can be done.
A short warning should be spoken here. Be aware, that you need to have the “Database Tuning Pack” licensed for your system to use SQL Profiles.
Let’s create a basic test setup by running the following SQLs:
create table profile_test1(id number(10), text varchar2(100));
create table profile_test2(id number(10), text2 varchar2(100));
insert into profile_test1 values (10, 'First test table 1');
insert into profile_test1 values (20, 'Second test table 1');
insert into profile_test2 values (10, 'First test table 2');
insert into profile_test2 values (20, 'Second test table 2');
commit;Now let’s find the execution plan and the according outlines for our problematic statement. In this case, I will run the statement by myself. This is of course not always doable, so I will show you how to select SQL plan information for already executed statements down below:
-- Explain and get us the plan for the following statement
explain plan for select a.id, b.text2 from profile_test1 a, profile_test2 b where a.id =20 and a.id=b.id;
Explained.
set lines 200
set pages 100
select * from table(dbms_xplan.display(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1414604682
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 78 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PROFILE_TEST1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| PROFILE_TEST2 | 1 | 65 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
3 - SEL$1 / B@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "B"@"SEL$1")
LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("A"."ID"=20)
3 - filter("B"."ID"=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "A"."ID"[NUMBER,22], "B"."TEXT2"[VARCHAR2,100]
2 - (rowset=256) "A"."ID"[NUMBER,22]
3 - (rowset=256) "B"."ID"[NUMBER,22], "B"."TEXT2"[VARCHAR2,100]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[A]]></t><s><![CDATA
[SEL$1]]></s></h><h><t><![CDATA[B]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
59 rows selected.So what to look for? See the line LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")? Let’s assume, that this is our performance issue for this particular query. Turning around the order of the selection would fix our issue. We proof this by running the SQL with an Optimizer Hint that changes the order.
-- Explain and get us the plan for the following statement
-- This time with an Optimizer Hint
explain plan for select /*+ LEADING (b a) */ a.id, b.text2 from profile_test1 a, profile_test2 b where a.id =20 and a.id=b.id;
Explained.
set lines 200
set pages 100
select * from table(dbms_xplan.display(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3974704947
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 78 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PROFILE_TEST2 | 1 | 65 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| PROFILE_TEST1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / B@SEL$1
3 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("B"."ID"=20)
3 - filter("A"."ID"=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "A"."ID"[NUMBER,22], "B"."TEXT2"[VARCHAR2,100]
2 - (rowset=256) "B"."ID"[NUMBER,22], "B"."TEXT2"[VARCHAR2,100]
3 - (rowset=256) "A"."ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- LEADING (b a)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[A]]></t><s><![CDATA
[SEL$1]]></s></h><h><t><![CDATA[B]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
66 rows selected.See how the order has changed to LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")? This changed outline information we will now add to a SQL Profile. Be aware, when you follow this tutorial, you need to have the actual SQL executed at least once in order to get some output from the following queries:
-- Get the SQL_ID for the problematic statement
-- Execute it once to get the SQL_ID
set lines 200
set pages 100
select a.id, b.text2 from profile_test1 a, profile_test2 b where a.id =20 and a.id=b.id;
col SQL_TEXT form a60
SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'select a.id, b.text2 from profile_test1 a, profile_test2 b where a.id =20 and a.id=b.id%';
DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '23yyk92xsubyq';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")'),
NAME => 'PROFILE_23yyk92xsubyq',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/
PL/SQL procedure successfully completed.As you can see, we use the DBMS_SQLTUNE package to import a SQL Profile. We only add the part of the outline we want to have changed. You may also add the complete outline information to the profile, but as it is the same for the query, I prefer only to update the things we need to get changed. So let’s see if our original query uses the SQL Profile with the altered execution plan:
-- Check if the query uses the SQL Profile
explain plan for select a.id, b.text2 from profile_test1 a, profile_test2 b where a.id =20 and a.id=b.id;
set lines 200
set pages 100
select * from table(dbms_xplan.display(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3974704947
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 78 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PROFILE_TEST2 | 1 | 65 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| PROFILE_TEST1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / B@SEL$1
3 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("B"."ID"=20)
3 - filter("A"."ID"=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "A"."ID"[NUMBER,22], "B"."TEXT2"[VARCHAR2,100]
2 - (rowset=256) "B"."ID"[NUMBER,22], "B"."TEXT2"[VARCHAR2,100]
3 - (rowset=256) "A"."ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1
- LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile "PROFILE_23yyk92xsubyq" used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[A]]></t><s><![CDATA
[SEL$1]]></s></h><h><t><![CDATA[B]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
67 rows selected.In the Note section of the output, you can find the reference to our newly created SQL Profile (SQL profile "PROFILE_23yyk92xsubyq" used for this statement). Also you can see that the plan has changed.
To get the above detailed information for a SQL query that has already been executed, you can search for the sql_id and the cursor in the v$sql view. Then use the dbms_xplan package with the DISPLAY_CURSOR procedure to get all information you need:
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%<YOUR_SQL_TEXT>%';
SQL_ID CHILD_NUMBER
---------- -----------------------------
c4nxg08mczwx2 0
set lines 200
set pages 100
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('c4nxg08mczwx2',0, 'ADVANCED'));
...Philip
Ähnliche Beiträge:
Pythia – An Oracle DB Automation Story – Chapter 1
Pythia – An Oracle DB Automation Story – Chapter 2 – Install RDBMS and Database
Bastard Operator from Hell? – Giving the Kubernetes Oracle Database Operator a try
Passt das? – Oracle Database COMPATIBLE Parameter und Guaranteed Restore Points – ORA-38880