When it comes to sql profile, there are lot of questions which arise on the basic functionality. It is not always recommended to use a parallel sql profile in a on peak database because of the sudden burst of parallel spawns.
Even oracle itself ignored the parallel plan during the tuning process.
DBMS_SQLTUNE.REPORT_TUNING_TASK('A6BWKQ27KB1J2_TT')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : a6bwkq27kb1j2
SQL Text : SELECT * from ytbl
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "coe_a6bwkq27kb1j2_192604670" exists for this statement and was
ignored during the tuning process.
- Can we change parallel for sql profile manually?
- Can multiple sql use same sql profile?
- Can multiple sql profiles be used for one sql?
- If sql id changes, then can sql profile be used for the different one?
Does sql profile has anything to do with parallel degree for a sql statement?
No matter the number of parallel degree used in the sql profile, if the table property has a degree of n, then parallel n is used for the sql statement during execution unless any hint is specified explicitly to override the table property.
Set the table property for parallel as 8.
SQL> ALTER TABLE ytbl parallel 8;
Table altered.
Execute the query and check the parallel used for the query. There are 8 parallel slaves spawned for the query.
SQL> SELECT * from ytbl;
SQL>col spid for a10
SQL>set lines 200 pages 1000
col NAME for a20
SELECT pxp.server_name,pxp.status,pxp.spid,
pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,
pxp.server_name,pxp.status,pxp.spid
ORDER BY sn.name;SQL>SQL> 2 3 4 5 6 7 8 9 10 11 12
SERV STATUS SPID SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE NAME VALUE
---- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
P004 IN USE 36800 265 28 237 48799 8 8 physical reads 341
P007 IN USE 36806 33 6187 237 48799 8 8 physical reads 312
P005 IN USE 36802 270 20347 237 48799 8 8 physical reads 312
P001 IN USE 26532 38 35986 237 48799 8 8 physical reads 312
P006 IN USE 36804 37 40124 237 48799 8 8 physical reads 312
P003 IN USE 26557 273 41465 237 48799 8 8 physical reads 312
P002 IN USE 26555 32 50163 237 48799 8 8 physical reads 312
P000 IN USE 26530 263 56350 237 48799 8 8 physical reads 312
P004 IN USE 36800 265 28 237 48799 8 8 physical reads direc 341
t
P007 IN USE 36806 33 6187 237 48799 8 8 physical reads direc 312
t
P005 IN USE 36802 270 20347 237 48799 8 8 physical reads direc 312
t
P001 IN USE 26532 38 35986 237 48799 8 8 physical reads direc 312
t
P006 IN USE 36804 37 40124 237 48799 8 8 physical reads direc 312
t
P003 IN USE 26557 273 41465 237 48799 8 8 physical reads direc 312
t
P002 IN USE 26555 32 50163 237 48799 8 8 physical reads direc 312
t
P000 IN USE 26530 263 56350 237 48799 8 8 physical reads direc 312
t
16 rows selected.
Check the sql id and hash value for the sql
SQL> SELECT SQL_ID,PLAN_HASH_VALUE from v$sql_plan WHERE sql_id='a6bwkq27kb1j2';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
Check the execution plan for the sql. Degree 8 is used.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 310 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 986K| 217M| 310 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 986K| 217M| 310 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 986K| 217M| 310 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 8 because of table property
26 rows selected.
Merge the sql id and execution plan to create a sql profile script.
SQL> @coe_xfr_sql_profile.sql a6bwkq27kb1j2 192604670
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
192604670 1.241
......
Execute coe_xfr_sql_profile_a6bwkq27kb1j2_192604670.sql
on TARGET system in order to create a custom SQL Profile
with plan 192604670 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_a6bwkq27kb1j2_192604670.sql
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_a6bwkq27kb1j2_192604670 completed
Check if the sql profile is used for the sql.
SQL>col SQL_TEXT for a20
SQL>col SQL_PROFILE for a20
SQL>SELECT sql_id,sql_text,sql_profile FROM v$sql WHERE sql_id='a6bwkq27kb1j2';
SQL_ID SQL_TEXT SQL_PROFILE
------------- -------------------- --------------------
a6bwkq27kb1j2 SELECT * from ytbl coe_a6bwkq27kb1j2_19
2604670
SQL profile is executed with degree of parallelism from table property.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 310 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 49M| 310 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 49M| 310 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 310 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 8 because of table property
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
26 rows selected.
To check the contents of the sql profile, use the below query. Note that there is no parallel hints in the sql profile and parallelism is independent of sql profile created
SQL>SELECT extractvalue(VALUE(ohint), '.') "Outline hint" from DBMSHSXP_SQL_PROFILE_ATTR sp, TABLE(xmlsequence(extract(xmltype(sp.comp_data), '/outline_data/hint'))) ohint WHERE profile_name='coe_a6bwkq27kb1j2_192604670';
Outline hint
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "YTBL"@"SEL$1")
END_OUTLINE_DATA
8 rows selected.
Increase the parallel at table level.
SQL>ALTER TABLE ytbl parallel 16;
Table altered.
Irrespective of the number of degree of parallel, sql profile is used for the query.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 155 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 16 because of table property
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
26 rows selected.
Check the parallel properties for degree.
SQL> show parameter parallel_degree
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
containers_parallel_degree integer 65535
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
Change the parallel_degree_policy to auto and parallel degree limit to a value other than default value CPU.
SQL> ALTER SYSTEM set parallel_degree_policy=AUTO;
System altered.
SQL> ALTER SYSTEM set parallel_degree_limit=8;
System altered.
Disable the parallel for the table.
SQL> ALTER TABLE ytbl parallel 1;
Table altered.
Now, full table scan is used without parallel. Also same sql profile can be seen used for multiple plans as well.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 1
-------------------------------------
SELECT * from ytbl
Plan hash value: 2390399380
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2232 (100)| |
| 1 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 2232 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
17 rows selected.
Another observation is that, even if the sql id change for the query, still the profile is used!!
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 82fsp243u4fxg, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 2390399380
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2232 (100)| |
| 1 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 2232 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
17 rows selected.
After changing the query logic itself, the sql profile cannot be used.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9yhf852k9177m, child number 0
-------------------------------------
SELECT * from ytbl WHERE rownum < 5
Plan hash value: 1669163498
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| YTBL | 4 | 208 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
23 rows selected.
Drop the sql profile.
SQL> BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_a6bwkq27kb1j2_192604670');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
Another way to use parallel with sql profile is to use parallel hint in the sql and create a sql profile on top of the parallel plan.
SQL> ALTER table ytbl parallel 1;
Table altered.
SQL> SELECT /*+parallel(16)*/ * from ytbl;
SQL>SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 68fn0x88bm4jc, child number 0
-------------------------------------
SELECT /*+parallel(16)*/ * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 155 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 16 because of hint
- SQL profile coe_68fn0x88bm4jc_192604670 used for this statement
26 rows selected.
