Pre-Requisite
SQL> sho parameter parallel_degree
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
parallel_degree_limit string CPU
parallel_degree_policy
string MANUAL
If
Parallel_servers_target is less than parallel_max_servers, parallel statement
queuing can occur, if not,
it will not because the
parallel_servers_target limit will be reached before Auto DOP queuing logic
kicks in.
SQL> sho parameter parallel_servers_target
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
parallel_servers_target integer 16
SQL> sho parameter parallel_max_servers
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
parallel_max_servers integer 40
Test Table Creation
Created a table
test_dop with dba_objects data.
SQL> sho
parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
parallel_degree_policy string MANUAL
SQL> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan
hash value: 381934326
------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 207 |
298 (0)| 00:00:04 |
| 1 |
TABLE ACCESS FULL| TEST_DOP |
1 | 207 | 298
(0)| 00:00:04 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
(level=2)
Explain plan with Parallel hint
Check
the Explain Plan by passing the Parallel Hint.
SQL> explain plan for select /*+ parallel (test_dop,8) */ *
from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan
hash value: 365997929
---------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 207 |
41 (0)| 00:00:01 |
| 1 |
PX COORDINATOR | | |
| | |
| 2 |
PX SEND QC (RANDOM)| :TQ10000 |
1 | 207 | 41
(0)| 00:00:01 |
| 3 |
PX BLOCK ITERATOR |
| 1 | 207 |
41 (0)| 00:00:01 |
| 4 |
TABLE ACCESS FULL| TEST_DOP |
1 | 207 | 41
(0)| 00:00:01 |
---------------------------------------------------------------------------------
- dynamic sampling used for this statement
(level=2)
Explain plan with DOP
Check the Explain Plan by enabling the DOP, but
received an error.
SQL> alter system set
parallel_degree_policy=auto;
System altered.
SQL> sho parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
parallel_degree_policy string AUTO
SQl> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan
hash value: 381934326
------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 207 |
298 (0)| 00:00:01 |
| 1 |
TABLE ACCESS FULL| TEST_DOP |
1 | 207 | 298
(0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
(level=2)
- automatic DOP: skipped because of IO calibrate statistics
are missing
Stats Collection
Collect the schema stats and tried again. (Number of
Rows has been increased)
SQL> EXEC
DBMS_STATS.GATHER_TABLE_STATS ('sukku', 'test_dop');
PL/SQL
procedure successfully completed.
SQl> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan
hash value: 381934326
------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 51 | 4590 |
298 (0)| 00:00:01 |
| 1 |
TABLE ACCESS FULL| TEST_DOP |
51 | 4590 | 298
(0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO
calibrate statistics are missing
There is no use of enabling the DOP as it is still
skipping it because of IO calibrate statistics are missing.
IO Calibration
Make sure IO_CALIBRATION_STATUS should be READY.
SQL>
select status from V$IO_CALIBRATION_STATUS;
STATUS
-------------
NOT AVAILABLE
Make sure the below parameter settings, to make the
IO_CALIBRATION_STAUS ready.
disk_asynch_io
= true
filesystemio_options
= asynch
SQL> sho parameter disk_asynch_io
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
disk_asynch_io boolean TRUE
SQL> sho parameter filesystemio_options
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
filesystemio_options string none
SQL> alter system set filesystemio_options=asynch
scope=spfile;
System
altered.
Database Restart
Bounce the DB as it is mandatory for this parameter
change.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> sho parameter filesystemio_options
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
filesystemio_options string ASYNCH
Use the below PL-SQL code to change the
IO_CALIBRATION_STATUS to READY
SET
SERVEROUTPUT ON
DECLARE
lat
INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops,
mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10,
iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
Output should be like below.
max_iops = 89
latency = 10
max_mbps = 38
PL/SQL procedure successfully completed.
SQL> select status from v$IO_CALIBRATION_STATUS;
STATUS
-------------
READY
DOP enabled
Without passing the parallel hint, we have achieved the
Query parallelism by enabling the DOP.
SQL> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan
hash value: 365997929
---------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 51 | 4590 |
166 (0)| 00:00:01 |
| 1 |
PX COORDINATOR | | |
| | |
| 2 |
PX SEND QC (RANDOM)| :TQ10000 |
51 | 4590 | 166
(0)| 00:00:01 |
| 3 |
PX BLOCK ITERATOR |
| 51 | 4590 |
166 (0)| 00:00:01 |
| 4 |
TABLE ACCESS FULL| TEST_DOP |
51 | 4590 | 166
(0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
No comments:
Post a Comment