hint Archives - DBACLASS https://dbaclass.com/article-tag/hint/ database administration Fri, 06 Oct 2017 08:40:06 +0000 en-US hourly 1 https://wordpress.org/?v=6.5.5 _optimizer_ignore_hint https://dbaclass.com/article/_optimizer_ignore_hint/ https://dbaclass.com/article/_optimizer_ignore_hint/#comments Fri, 06 Oct 2017 08:40:06 +0000 http://dbaclass.com/?post_type=article&p=3408 Sometimes application queries come with the specific hint, which may impact your database performance. And it is difficult to find and remove these hints from each query. Oracle provided an undocumented hidden parameter, _optimizer_ignore_hint. If this parameter is set to true, Then it will ignore the hints mentioned in the SQL queries. DEFAULT VALUE OF […]

The post _optimizer_ignore_hint appeared first on DBACLASS.

]]>
Sometimes application queries come with the specific hint, which may impact your database performance. And it is difficult to find and remove these hints from each query.

Oracle provided an undocumented hidden parameter, _optimizer_ignore_hint. If this parameter is set to true, Then it will ignore the hints mentioned in the SQL queries.

DEFAULT VALUE OF _optimizer_ignore_hint is FALSE.

Let’s run a test, to see how it behaves.

EXAMPLE:
Parameter is set to FALSE:(DEFAULT)

SQL> show parameter _optimizer

NAME                          TYPE   			VALUE
------------------           -----------           -------------
_optimizer_ignore_hints       boolean                  FALSE

Execute a query with a  HINT.

SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';


  COUNT(*)
----------
     42814


Execution Plan
----------------------------------------------------------
Plan hash value: 529722805

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     7 |   480   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST5 |  1184 |  8288 |   480   (1)| 00:00:01 | ------- >>>> .FULL SCAN DUE TO FULL HINT
----------------------------------------------------------------------------

We can see, as we have used a FULL hint, TABLE ACCESS FULL is used. That does not change the query behavior.

Now, lets set it to TRUE and re-run the same query.

The parameter is set to TRUE:

SQL> alter session set "_optimizer_ignore_hints"=TRUE ;

Session altered.

SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';

  COUNT(*)
----------
     42814


Execution Plan
----------------------------------------------------------
Plan hash value: 1056693648

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| TE   |  1184 |  8288 |     3   (0)| 00:00:01 | ------ >>>> IGNORED FULL HINT, WENT FOR INDEX . 
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')


Now despite using a FULL hint, it was ignored because of the parameter _optimizer_ignore_hints.

NOTE – Don’t use in production database without proper testing, Because it will disable all the hints used in sql queries, which might be recommended by your Application.

EXCEPTION:

One interesting point, we observed is that, this parameter is not having any impact on PARALLEL hint. I.e even if we set this to TRUE, PARALLEL hint will work as expected.

SQL> show parameter _optimizer

NAME                          TYPE   			VALUE
------------------           -----------           -------------
_optimizer_ignore_hints       boolean                  TRUE


SQL>  select /*+ parallel(4) */ count(*) from TEST5;

  COUNT(*)
----------
    107708

Execution Plan
----------------------------------------------------------
Plan hash value: 4160549356

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   133   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   107K|   133   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| TEST5    |   107K|   133   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

The post _optimizer_ignore_hint appeared first on DBACLASS.

]]>
https://dbaclass.com/article/_optimizer_ignore_hint/feed/ 1
ENABLE_PARALLEL_DML hint in oracle 12c https://dbaclass.com/article/enable_parallel_dml-hint-oracle-12c/ https://dbaclass.com/article/enable_parallel_dml-hint-oracle-12c/#respond Wed, 30 Aug 2017 13:58:31 +0000 http://dbaclass.com/?post_type=article&p=3332 Till oracle 12c, For doing DML transactions in parallel, we need to enable PDML (parallel DML) at session level. I.e before any DML statement, we need to issue below statement. ALTER SESSION ENABLE PARALLEL DML; -- Then parallel dml statement insert /*+ parallel(8) */ into TEST2 select * from TEST2; In oracle 12c, it introduced […]

The post ENABLE_PARALLEL_DML hint in oracle 12c appeared first on DBACLASS.

]]>
Till oracle 12c, For doing DML transactions in parallel, we need to enable PDML (parallel DML) at session level.
I.e before any DML statement, we need to issue below statement.

ALTER SESSION ENABLE PARALLEL DML;

-- Then parallel dml statement

insert /*+ parallel(8) */ into TEST2 select * from TEST2;

In oracle 12c, it introduced a hint for parallel dml, ENABLE_PARALLEL_DML , which we can use directly inside the dml sql statement. No need to issuing alter session statement.

It will look as below:

 insert /*+ parallel(8)  enable_parallel_dml */ into TEST2 select * from TEST2;

Lets compare the execution plan with and without ENABLE_PARALLEL_DML

WITHOUT ENABLE_PARALLEL_DML:

SQL> explain plan for insert /*+ parallel(8) */ into TEST2 select * from TEST2;

Explained.


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 2876518734

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |   122K|    13M|    82   (2)| 00:00:01 |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL | TEST2    |       |       |            |          |        |      |            |  ----- > NOT UNDER PX CORDIN..
|   2 |   PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |   122K|    13M|    82   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |          |   122K|    13M|    82   (2)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL   | TEST2    |   122K|    13M|    82   (2)| 00:00:01 |  Q1,00 | PCWP |            |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint
   - PDML is disabled in current session --- >          --- >>>  IT INDICATED PDML IS DISABLED

17 rows selected.

 

WITH ENABLE_PARALLEL_DML hint:

SQL> explain plan for insert /*+ parallel(8) enable_parallel_dml */ into TEST2 select * from TEST2;

Explained.

SQL> set lines 299
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 4043334015

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Dist
-----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          | 61649 |  6863K|    40   (3)| 00:00:01 |        |      |        
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |         
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 | 61649 |  6863K|    40   (3)| 00:00:01 |  Q1,00 | P->S | QC (RAN --- > LOAD IS UNDER PX COORDINATOR
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TEST2    |       |       |            |          |  Q1,00 | PCWP |        
|   4 |     PX BLOCK ITERATOR              |          | 61649 |  6863K|    40   (3)| 00:00:01 |  Q1,00 | PCWC |        
|   5 |      TABLE ACCESS FULL             | TEST2    | 61649 |  6863K|    40   (3)| 00:00:01 |  Q1,00 | PCWP |        

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint         - PARALLEL IS ENABLED 

16 rows selected.

We can see, With this ENABLE_PARALLEL_DML hint, even without the alter session command, PDML is enabled.

Similarly DISABLE_PARALLEL_DML for disabling the PDML.

 

 

SEE ALSO:

New features of oracle 12.2.

The post ENABLE_PARALLEL_DML hint in oracle 12c appeared first on DBACLASS.

]]>
https://dbaclass.com/article/enable_parallel_dml-hint-oracle-12c/feed/ 0