Finding Query Block names for a SQL that you can't modify...

I recently had an issue where a query being submitted to the database had a large change in performance. I could attribute the change to a recent change in object statistics, but the query wasn't behaving because of a combination of a view, subquery, UNION, and LIKE operators. After spending too much time on this statement I decided to hint it to make it perform like it did before the most recent stats change.

Since I am unable to change the SQL being submitted to the database I had to create a SQL Profile which will apply the hint whenever it sees the query text. Creating a SQL Profile is the easy part. I have information on manually creating SQL Profiles here. In this case, creating the hint would require more work to identify the query blocks. 10053 trace to the rescue!

alter session set events '10053 trace name context forever, level 1';

SELECT MAX(AUDIT_DATE) sort_date, 
       Description1, 
       to_number(substr(description1,5,instr(description1,';',5,1) - 5)) version
  FROM (SELECT *
          FROM EMPLOYEE
         WHERE (key0 like :project_code || '%' and key0 not like :project_code || '% %') 
       UNION
        SELECT *
          FROM EMPLOYEE
         WHERE key0 LIKE RPAD(:project_code, 10, ' ')||'%') EMPLOYEE_VIEW
 WHERE EMPLOYEE_VIEW.audit_type like 'RANGE' 
 GROUP BY Description1 
 ORDER BY sort_date desc;

alter session set events '10053 trace name context off';

And then check out the top of the trace file for the query blocks...

*** 2010-07-19 11:51:51.831
*** ACTION NAME:() 2010-07-19 11:51:51.755
*** MODULE NAME:(TOAD 9.6.1.1) 2010-07-19 11:51:51.755
*** SERVICE NAME:(SYS$USERS) 2010-07-19 11:51:51.755
*** SESSION ID:(856.6856) 2010-07-19 11:51:51.755
Registered qb: SEL$1 0x7c015170 (PARSER)
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=5 objn=0 hint_alias="EMPLOYEE_VIEW"@"SEL$1"
Registered qb: SEL$2 0x7c014678 (PARSER)
  signature (): qb_name=SEL$2 nbfros=1 flg=0
    fro(0): flg=4 objn=25603 hint_alias="EMPLOYEE"@"SEL$2"
Registered qb: SET$1 0x7c012b88 (PARSER)
  signature (): qb_name=SET$1 nbfros=1 flg=0
    fro(0): flg=0 objn=0 hint_alias="NULL_HALIAS"@"SET$1"
Registered qb: SEL$3 0x7c0138a0 (PARSER)
  signature (): qb_name=SEL$3 nbfros=1 flg=0
    fro(0): flg=4 objn=25603 hint_alias="EMPLOYEE"@"SEL$3"

Checking out the highlighted lines above we now have the ability to reference specific tables inside the query blocks. This means we can suggest indexes for particular tables in the query blocks. This hint suggests use of EMPLOYEE_KEY0_INDEX for accesses of the EMPLOYEE table in SEL$2 query block and the EMPLOYEE table in SEL$3 query block.

/*+ INDEX(@"SEL$2" "EMPLOYEE"@"SEL$2" "EMPLOYEE_KEY0_INDEX") INDEX(@"SEL$3" "EMPLOYEE"@"SEL$3" "EMPLOYEE_KEY0_INDEX")  */

After verifying the hint works as expected you can package it as a SQL Profile so it will be put into play every time the original SQL is submitted by your applications. Instructions on manually creating a SQL Profile are here.

Add new comment