Oracle Database Commands and Practical Examples

Thursday 2 April 2020

PT - OPTIMIZER Hints using Table and Index Access

Table and Index Access Path Hints

Table and Index Access Path

Full
Index
Index_FFS
Index_SS
No_Index

SET LINESIZE 180
SET AUTOTRACE ON ( PLUSTRACE ALREADY ENABLED)

FULL Tables Scan Hints

SQL> SELECT /*+ FULL(D) */ DEPT_NO, DNAME FROM DEPT D WHERE DEPT_NO=10;

DEPT_NO DNAME
---------- ----------------------
10 Administration

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("DEPT_NO"=10)

Statistics
----------------------------------------------------------
80 recursive calls
2 db block gets
81 consistent gets
0 physical reads
0 redo size
623 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT /*+ FULL(DEPT) */ DEPT_NO, DNAME FROM DEPT D WHERE DEPT_NO=10;

DEPT_NO DNAME
---------- ----------------------
10 Administration

Execution Plan
----------------------------------------------------------
Plan hash value: 602043285

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access("DEPT_NO"=10)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
597 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

SQL>

NOTE : Do not use Alias dept d instead give table name

SQL> SELECT /*+ FULL(E) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO=635;

EMP_NO ENAME SALARY

---------- -------------------- ----------

635 FNTAMOSA GKSDYNDFH 60200

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 24 | 5 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 24 | 5 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("EMP_NO"=635)

Statistics

----------------------------------------------------------

200 recursive calls

9 db block gets

393 consistent gets

0 physical reads

1076 redo size

699 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

36 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> SELECT /*+ FULL(EMP) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO=635;

EMP_NO ENAME SALARY

---------- -------------------- ----------

635 FNTAMOSA GKSDYNDFH 60200

Execution Plan

----------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMP_NO"=635)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

3 physical reads

0 redo size

699 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

Index Hints

SQL> SELECT /*+ INDEX(E INDEX_PK) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO=635;

EMP_NO ENAME SALARY

---------- -------------------- ----------

635 FNTAMOSA GKSDYNDFH 60200

Execution Plan

----------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMP_NO"=635)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

699 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

( This is the example using multiple Index)

SQL> SELECT /*+ INDEX (E EMP_PK EMP_DEPTNO_IX ) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

EMP_NO ENAME SALARY

---------- -------------------- ----------

600 AHYVFGNLRS AOVXOCDJH 56700

601 GBUKTVDY TVTQ 78300

602 RQHW SECLVR 80900

603 BFBCIPA LNKORQ 72200

604 GTKGSMJURW VQIFED 55800

605 QBGOCE NQJDIEFXD 81000

606 XXNEFJPPQ SAHGX 61800

607 KTIWMDWCIZ GBBB 61300

608 AQWFHXZ WGQRDQQM 62800

609 UAWDGPRRSO PHXT 72500

610 JNECDUASZ GPBWBE 78700

11 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2288396553

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 216 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 8 | 216 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_PK | 10 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DEPT_NO"=60)

2 - access("EMP_NO">=600 AND "EMP_NO"<=610)

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

16 consistent gets

0 physical reads

0 redo size

1034 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

11 rows processed

SQL>

Index_FFS ( Fast Full Scan hints)

SQL> CREATE INDEX EMP_ENAME_JC_IX ON EMP(ENAME, JOB_CODE) COMPUTE STATISTICS;

Index created.

SQL> SELECT /*+ INDEX_FFS(E EMP_ENAME_JC_IX) */ ENAME, JOB_CODE FROM EMP E WHERE ENAME LIKE 'Z%';

ENAME JOB_

-------------------- ----

ZAPSU SSRLIG SL02

ZATDCTVSSA TUWQY SL02

ZAYEY MQKDN SL02

ZBOTTAPH CESYWCQB SL02

ZDCYJZRIE GSGDKQF SL02

ZDFLKXK SGIPR MG03

ZDMIWBZZN ABZMWMOCR SL02

ZDUT UEQTZ SL02

ZDVDBEMSA MOAZKC SL02

ZDZYA ROKMN SL02

ZELDVW JXIMCUJ SL02

ENAME JOB_

-------------------- ----

ZERDPOLO RTVDO SL02

ZGBYOSAW GZYQB HR02

ZGDEYEFD NXMAQDBD SL02

ZGGKHUS URMPHH SL02

ZHPH XWSWJH SL02

ZHWIIXH QJQKKYS IT05

ZILVN QQBCCDMN MG02

ZIUBBOMPE MZUQWAO AD06

ZIUOQP PSUJZ SL02

ZIVXT CPIR SL02

ZJSGY ZJELUQ SL02

ENAME JOB_

-------------------- ----

ZKCH OACWGHOGY ST02

ZKTTV KFXDFAL SL02

ZKXXQADJL BASDCVQT SL02

ZLJAVNC GHGUUO SL02

ZLXCPCPLW WNRPZT SL02

ZNII KDGQWLXB SL02

ZOAKRCS MYOHKZP SL02

ZOLIEFVJ JOHEUHXMK SL02

ZQCES OGEKAQZ SL02

ZQGNJLDAK ALKPGIG SL02

ZRNQRSDNVH BSFTIWHPQ SL02

ENAME JOB_

-------------------- ----

ZRPLO WIIJCEE SL02

ZTHXNXRPM BSBB FN03

ZUZQJO PSRIWT SL02

ZVMTJUWTNO KQLSJRMN SL02

ZWHBK WQNWNTI SL02

ZWKTN WVPAQBW AD06

ZWXRXB VSVVEJED SL02

ZXATQK DYDNVFKT SL02

ZXHTHFIAR PJNVYW SL02

ZYCWR UAQGDWND SL02

ZYIGXJV BZLJO SL02

ENAME JOB_

-------------------- ----

ZZROO ILREINIMQ SL02

45 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1191882812

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 42 | 882 | 3 (0)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| EMP_ENAME_JC_IX | 42 | 882 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ENAME" LIKE 'Z%')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

11 consistent gets

4 physical reads

0 redo size

2064 bytes sent via SQL*Net to client

630 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

45 rows processed

SQL>

SQL> SELECT /*+ INDEX_FFS(E EMP_ENAME_JC_IX) */ ENAME, JOB_CODE FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610;

ENAME JOB_

-------------------- ----

AHYVFGNLRS AOVXOCDJH SL02

GBUKTVDY TVTQ SL02

RQHW SECLVR SL02

BFBCIPA LNKORQ SL02

GTKGSMJURW VQIFED SL02

QBGOCE NQJDIEFXD SL02

XXNEFJPPQ SAHGX SL02

KTIWMDWCIZ GBBB SL02

AQWFHXZ WGQRDQQM SL02

UAWDGPRRSO PHXT SL02

JNECDUASZ GPBWBE SL02

11 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2288396553

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 250 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 | 250 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_PK | 10 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMP_NO">=600 AND "EMP_NO"<=610)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

936 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

Index_SS (Skip Scan hints)

SQL> SELECT /* INDEX_SS ( C CUST_CLASS_EMAIL_IX ) */ * FROM CUSTOMERS C WHERE CUST_EMAIL = 'lyndia.bangle@oracle.com';

CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME NLS NLS_TERRITORY CREDIT_LIMIT

----------- ---------------------------------------- ---------------------------------------- --- ------------------------------ ------------

CUST_EMAIL ACCOUNT_MGR_ID CUSTOMER_

---------------------------------------------------------------------------------------------------- -------------- ---------

CUSTOMER_CLASS SUGGESTIONS DOB M P PREFERRED_ADDRESS PREFERRED_CARD

---------------------------------------- ---------------------------------------- --------- - - ----------------- --------------

45550 lyndia bangle th THAILAND 1392

lyndia.bangle@oracle.com 154 02-OCT-18

Occasional Music 06-NOV-71 Y N 55550 55550

Execution Plan

----------------------------------------------------------

Plan hash value: 3336747554

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 114 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 1 | 114 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | CUST_EMAIL_IX | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("CUST_EMAIL"='lyndia.bangle@oracle.com')

Statistics

----------------------------------------------------------

61 recursive calls

52 db block gets

124 consistent gets

1 physical reads

9804 redo size

1770 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

14 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

No_Index Hints

SQL> SELECT /*+ NO_INDEX (E EMP_PK ) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

EMP_NO ENAME SALARY

---------- -------------------- ----------

600 AHYVFGNLRS AOVXOCDJH 56700

601 GBUKTVDY TVTQ 78300

602 RQHW SECLVR 80900

603 BFBCIPA LNKORQ 72200

604 GTKGSMJURW VQIFED 55800

605 QBGOCE NQJDIEFXD 81000

606 XXNEFJPPQ SAHGX 61800

607 KTIWMDWCIZ GBBB 61300

608 AQWFHXZ WGQRDQQM 62800

609 UAWDGPRRSO PHXT 72500

610 JNECDUASZ GPBWBE 78700

11 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 216 | 5 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 8 | 216 | 5 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("EMP_NO">=600 AND "EMP_NO"<=610 AND "DEPT_NO"=60)

Statistics

----------------------------------------------------------

1 recursive calls

2 db block gets

14 consistent gets

0 physical reads

0 redo size

1034 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

SQL> SELECT EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

EMP_NO ENAME SALARY

---------- -------------------- ----------

600 AHYVFGNLRS AOVXOCDJH 56700

601 GBUKTVDY TVTQ 78300

602 RQHW SECLVR 80900

603 BFBCIPA LNKORQ 72200

604 GTKGSMJURW VQIFED 55800

605 QBGOCE NQJDIEFXD 81000

606 XXNEFJPPQ SAHGX 61800

607 KTIWMDWCIZ GBBB 61300

608 AQWFHXZ WGQRDQQM 62800

609 UAWDGPRRSO PHXT 72500

610 JNECDUASZ GPBWBE 78700

11 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2288396553

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 216 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 8 | 216 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_PK | 10 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DEPT_NO"=60)

2 - access("EMP_NO">=600 AND "EMP_NO"<=610)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1034 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

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

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 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>

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

All_Rows
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

Full
Index
Index_FFS
Index_SS
No_Index

Join Order

Ordered
Leading

Joins Methods

USE_NL
USE_HASH
USE_MERGE

SQL BLOCK NAME

QB_NAME

HOME

Thursday 2 April 2020

PT - OPTIMIZER Hints using Table and Index Access

FULL Tables Scan Hints

Index Hints

Index_FFS ( Fast Full Scan hints)

Index_SS (Skip Scan hints)

No_Index Hints

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

PT - Optimizer using SQL hints