Thursday 2 April 2020

PT - Hints /* + */ using Optimizer Goal:


Hints  /* +  */   using Optimizer Goal:
ALTER [SYSTEM|SESSION] SET OPTIMIZER_MODE='ALL_ROWS' ( THROUGH OUTPUT)

ALTER [SYSTEM|SESSION] SET OPTIMIZER_MODE='FIRST_ROWS_n' ( RESPONSE TIME )

By Default optimizer mode  is set to ALL_ROWS

SQL> sho parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SQL>

SET LINESIZE 180
SET AUTOTRACE ON ( PLUSTRACE ALREADY ENABLED)



SQL> SELECT /*+ FIRST_ROWS */ /* MYTEST */ ORDER_ID  FROM ORDERS;
  ORDER_ID
----------
    979999
    989999
    999999
    999900
    990000

1352070 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2880579201

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |  1353K|  7929K|  2835   (1)| 00:00:01 |
|   1 |  INDEX FULL SCAN | ORDER_PK |  1353K|  7929K|  2835   (1)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        137  recursive calls
          0  db block gets
      93042  consistent gets
          0  physical reads
          0  redo size
   25948070  bytes sent via SQL*Net to client
     992115  bytes received via SQL*Net from client
      90139  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1352070  rows processed
################################################################################

(Query With out Hints)

SQL>  SELECT ORDER_ID  FROM ORDERS;
  ORDER_ID
----------
    565152
    575152
    585152
    595152
    605152

1352070 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4126732553

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  1353K|  7929K|   774   (2)| 00:00:01 |        |      |    |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  1353K|  7929K|   774   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |          |  1353K|  7929K|   774   (2)| 00:00:01 |  Q1,00 | PCWC |    |
|   4 |     INDEX FAST FULL SCAN| ORDER_PK |  1353K|  7929K|   774   (2)| 00:00:01 |  Q1,00 | PCWP |    |
-----------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         65  recursive calls
          0  db block gets
       4533  consistent gets
          0  physical reads
          0  redo size
   25948070  bytes sent via SQL*Net to client
     992115  bytes received via SQL*Net from client
      90139  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1352070  rows processed


SQL>

#################################################################################

( Query Check using SQL_TEXT)

SQL> SET LINESIZE 150
SQL> SET SQL_TEXT format A60

SQL>  SELECT OPTIMIZER_MODE, SUBSTR(SQL_TEXT,1,60) SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%MYTEST%ORDERS%';

OPTIMIZER_
----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
FIRST_ROWS
EXPLAIN PLAN SET STATEMENT_ID='PLUS80089' FOR SELECT /*+ FIR

ALL_ROWS
 SELECT OPTIMIZER_MODE, SUBSTR(SQL_TEXT,1,60) SQL_TEXT FROM

FIRST_ROWS
SELECT /*+ FIRST_ROWS */ /* MYTEST */ ORDER_ID  FROM ORDERS



Execution Plan
----------------------------------------------------------
Plan hash value: 903671040

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |   528 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   528 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("KGLNAOBJ" IS NOT NULL AND "KGLNAOBJ" LIKE '%MYTEST%ORDERS%'
              AND "INST_ID"=USERENV('INSTANCE'))


Statistics
----------------------------------------------------------
        394  recursive calls
          0  db block gets
        115  consistent gets
          0  physical reads
          0  redo size
        884  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>

#################################################################################
(Example of query using FIRST_ROWS(10)  We can see cost got reduced ) 

SQL> SELECT /*+ FIRST_ROWS(10) */ /* MYTEST */ ORDER_ID  FROM ORDERS
SQL>
  ORDER_ID
----------
    565152
    575152
    585152
    595152
    605152

1352070 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4126732553

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    10 |    60 |     2   (0)| 00:00:01 |        |      |    |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |    10 |    60 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |          |    10 |    60 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |    |
|   4 |     INDEX FAST FULL SCAN| ORDER_PK |    10 |    60 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |    |
-----------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
       4533  consistent gets
          0  physical reads
          0  redo size
   25948070  bytes sent via SQL*Net to client
     992115  bytes received via SQL*Net from client
      90139  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1352070  rows processed

#######################################################################
(Example of query using FIRST_ROWS(1000)  We can see cost got reduced ) 

SQL> SELECT /*+ FIRST_ROWS(1000) */ /* MYTEST */ ORDER_ID  FROM ORDERS
SQL>

 ORDER_ID
----------
    565152
    575152
    585152
    595152
    605152

1352070 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4126732553

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  1000 |  6000 |     3   (0)| 00:00:01 |        |      |    |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  1000 |  6000 |     3   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |          |  1000 |  6000 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |    |
|   4 |     INDEX FAST FULL SCAN| ORDER_PK |  1000 |  6000 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |    |
-----------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
       4533  consistent gets
          0  physical reads
          0  redo size
   25948070  bytes sent via SQL*Net to client
     992115  bytes received via SQL*Net from client
      90139  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1352070  rows processed

SQL> 

Posted by at No comments:
Labels:

PT - Optimizer using SQL hints

Hints  /* +  */

 Types of Hints we can use while executing queries below, We will discuss with examples on upcoming days with URL  location:
  • Optimizer Goal
  1. All_Rows
  2. First_Rows(n)

ALTER [SYSTEM|SESSION] SET OPTIMIZER_MODE='ALL_ROWS'

ALTER [SYSTEM|SESSION] SET OPTIMIZER_MODE='FIRST_ROWS_n'
  • Table and Index Access Path
  1. Full
  2. Index
  3. Index_FFS
  4. Index_SS
  5. No_Index
  • Join Order
  1. Ordered
  2. Leading
  • Joins Methods
  1. USE_NL
  2. USE_HASH
  3. USE_MERGE
  • SQL BLOCK NAME
  1. QB_NAME



Posted by at No comments:
Labels:

Tuesday 31 March 2020

PT - Joins Methods and Examples

  1. Nested Loop Join
  2. hash table Join
  3. Sort-merge Join
##########  Nested Loop Join #################

SQL> SELECT ENAME, DNAME FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_NO=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |   113 |  3616 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |         |   113 |  3616 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |   113 |  2147 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | DEPT_IX |   113 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   3 - access("DEPT"."DEPT_NO"=10)
   5 - access("EMP"."DEPT_NO"=10)

SQL>

 ##########  Hash table Join  #############

SQL> SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_LAST_NAME FROM ORDERS O,  CUSTOMERS C WHERE O.CUSTOMER_ID = C.CUSTOMER_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 3042513348

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1376K|    45M|  5060   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |           |  1376K|    45M|  5060   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 46023 |   584K|   214   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| ORDERS    |  1353K|    28M|  4839   (1)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

Note
-----
   - this is an adaptive plan

SQL>

 ###############  Sort-merge Join #############

SQL> SELECT ENAME , DNAME FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO;

Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   879 | 28128 |     8  (13)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |   879 | 28128 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     7 |    91 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |     7 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |   879 | 16701 |     6  (17)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |   879 | 16701 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
       filter("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

SQL>

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)