How to find query block and object alias

Example #1

From the file, sqlhc_*_[SQL_ID]_3_execution_plans.html

#1 From Execution Plans section, find what you are looking for. (In this example, assume line id 18.)


|* 18 | 12 | TABLE ACCESS STORAGE FULL | PAY_PAY_RELATIONSHIPS_DN | 456K| 14M| 3912 (1)| 00:00:01 | 1025K| 1025K| |

#2. From Query Block Name / Object Alias section

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
18 - SEL$7 / PAY_PAY_RELATIONSHIPS_DN@SEL$7

#3 .From Outline Data section, search 

FULL(@"SEL$7" "PAY_PAY_RELATIONSHIPS_DN"@"SEL$7")

=> If we need an index range scan here, we can change the full hint with the following index hint.
e.g.)


INDEX(@"SEL$7" "PAY_PAY_RELATIONSHIPS_DN"@"SEL$7" "PAY_PAY_RELATIONSHIPS_DN_PK")

 

Example #2.

[Test SQL : dtest_index_hint.sql]

var   G_FIRST_PARTY_TAX_REG_NUM  varchar2(30)
var   G_TAX_REGIME_CODE  varchar2(30)
var   G_LEGAL_ENTITY_ID number
var   G_LEDGER_ID   number

exec :G_FIRST_PARTY_TAX_REG_NUM := '04491561009';
exec :G_TAX_REGIME_CODE := 'IT VAT';
exec :G_LEGAL_ENTITY_ID := 1017;
exec :G_LEDGER_ID := 207;

alter session set statistics_level = all;

SELECT zx_tax.tax_type_code, zx_det.trx_communicated_date, zx_det.trx_currency_code,
       zx_line.trx_id, zx_det.trx_number, zx_det.trx_date,
       zx_det.trx_description, zx_det.trx_type_description
FROM       zx_lines             zx_line,
           zx_taxes_vl          zx_tax,
           zx_lines_det_factors zx_det
WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
   AND zx_det.application_id = zx_line.application_id
   AND zx_det.entity_code = zx_line.entity_code
   AND zx_det.event_class_code = zx_line.event_class_code
   AND zx_det.trx_id = zx_line.trx_id
   AND zx_det.trx_line_id = zx_line.trx_line_id
   AND zx_line.tax_id = zx_tax.tax_id
   AND nvl(zx_line.reportable_flag,'Y') ='Y'
   AND zx_line.entity_code <> 'ZX_TRANSACTION'
   AND zx_line.hq_estb_reg_number = :g_first_party_tax_reg_num
   AND ZX_LINE.TAX_REGIME_CODE = :G_TAX_REGIME_CODE
   AND ZX_DET.LINE_CLASS IN ( 'STANDARD INVOICES'  ,'AMOUNT_MATCHED'  ,
       'AP_DEBIT_MEMO'  ,'AP_CREDIT_MEMO'  ,'PREPAYMENT INVOICES'  ,
       'EXPENSE REPORTS'  ,'PO_CO'  ,'PO_PA'  ,'CHARGES'  ,'RELEASE'  ,
       'REQUISITION')
   AND ZX_LINE.LEGAL_ENTITY_ID = :G_LEGAL_ENTITY_ID
   AND ZX_LINE.LEDGER_ID = :G_LEDGER_ID
   AND zx_det.tax_reporting_flag = 'Y'
/

alter session set statistics_level = typical;

[Find QUERY BLOCK AND OBJECT ALIAS]

SELECT SQL_ID, SQL_TEXT FROM GV$SQL WHERE SQL_TEXT LIKE 'SELECT /*+ cardinality(ZX_DIST 100) use_nl(zx_line) use_nl(zx_tax.b) */%'

sql_id : 032vha8gnzz55

Explain plan with QBLOCK_NAME and OBJECT_ALIAS from PLAN_TABLE.

QUERY_PLAN
--------------------------------------------------------------------------------------------------------
SELECT STATEMENT  .----- [Cost:20] [CDN:1]
 NESTED LOOPS  .----SEL$5428C7F1- [Cost:20] [CDN:1]
  NESTED LOOPS  .----- [Cost:20] [CDN:1]
   NESTED LOOPS  .----- [Cost:17] [CDN:1]
    NESTED LOOPS  .----- [Cost:17] [CDN:1]
     TABLE ACCESS BY INDEX ROWID BATCHED FUSION.ZX_LINES----SEL$5428C7F1-ZX_LINES@SEL$2 [Cost:16] [CDN:1
      INDEX RANGE SCAN FUSION.ZX_LINES_N5----SEL$5428C7F1-ZX_LINES@SEL$2 [Cost:3] [CDN:54]
     TABLE ACCESS BY INDEX ROWID FUSION.ZX_TAXES_B----SEL$5428C7F1-B@SEL$3 [Cost:1] [CDN:1]
      INDEX UNIQUE SCAN FUSION.ZX_TAXES_B_U1----SEL$5428C7F1-B@SEL$3 [Cost:0] [CDN:1]
    INDEX UNIQUE SCAN FUSION.ZX_TAXES_TL_U1----SEL$5428C7F1-T@SEL$3 [Cost:0] [CDN:1]
   INDEX RANGE SCAN FUSION.ZX_BUG27506612_PSR2----SEL$5428C7F1-ZX_DET@SEL$1 [Cost:2] [CDN:1]
  TABLE ACCESS BY INDEX ROWID FUSION.ZX_LINES_DET_FACTORS----SEL$5428C7F1-ZX_DET@SEL$1 [Cost:3] [CDN:1]

[SET SQLPATCH]

If we want to use ZX_LINES_DET_FACTORS_U3 instead of ZX_BUG27506612_PSR2, we can set the SQLPATCH as below.

sqlplus / as sysdba

sql>start coe_gen_sql_patch.sql
1st parameter : 032vha8gnzz55
2nd parameter : INDEX(@SEL$5428C7F1 ZX_DET@SEL$1 ZX_LINES_DET_FACTORS_U3)

Note. INDEX(@QUERY_BLOCK_NAME TABLE_ALIAS INDEX_NAME)

sql>start flushsql

1st parameter : 032vha8gnzz55

sql>conn fusion

sql> start dtest_index_hint.sql

sql>conn / as sysdba

SQL>start sma.sql 032vha8gnzz55 P

*****************************************
SQL STATS SECTION [SQL_ID: 032vha8gnzz55]
*****************************************
.
NOTE. PHV:3041160314 PROFILE: PATCH:coe_032vha8gnzz55 BASELINE:
* PHV:3041160314 is available in memory.

PLAN HASH VALUE COMPARISON SECTION
**********************************

PLAN_HASH_VALUE TP  AVG_ET_SECS AVG_BF_GETS AVG_DK_READS   AVG_ROWS  AVG_EXECS LAST_ACTIVE_TIME           CF
--------------- --- ----------- ----------- ------------ ---------- ---------- -------------------------- ---------------
     3041160314 MEM        .066        6612        66098        619          1 2018-FEB-13 20:17          NO ERROR
     3041160314 MEM        .066        6612        66098        619          1 2018-FEB-13 20:17          Current Best
.
[PLAN HASH VALUE: 3041160314]
*******************************************
.
ELAPSED_TIME:.07secs, CPU_TIME:.05secs
.
[NOTE  Dot line(....)=>Expensive operation
----- ---------- --------------------------------------------------------------
:  ID       ROWS ROW SOURCE FROM MEMORY
----- ---------- --------------------------------------------------------------
:   0        619SELECT STATEMENT
:   1        619 NESTED LOOPS
:   2       1054  NESTED LOOPS
:   3       1054   NESTED LOOPS
:   4       1054    NESTED LOOPS
:   5       1054     TABLE ACCESS BY INDEX ROWID BATCHED ZX_LINES
:   6       1110      INDEX RANGE SCAN ZX_LINES_N5
:   7       1054     TABLE ACCESS BY INDEX ROWID ZX_TAXES_B
:   8       1054      INDEX UNIQUE SCAN ZX_TAXES_B_U1
:   9       1054    INDEX UNIQUE SCAN ZX_TAXES_TL_U1
:  10       1054   INDEX RANGE SCAN ZX_LINES_DET_FACTORS_U3
:  11        619  TABLE ACCESS BY INDEX ROWID ZX_LINES_DET_FACTORS