Re: Hint is not used in SQL
- From: Amit Saroha <eramitsaroha@xxxxxxxxx>
- To: jlewisoracle@xxxxxxxxx
- Date: Wed, 15 Nov 2023 10:51:58 -0500
Hello Jonathan,
My fault I believe I messed it up when taking the tests. I dropped and
reapplied the patch, and I generated the expanded hint report once more.
Because I know we are only changing one row from the front end, the view
must run first, followed by the table.
I'm not sure if VIEW PUSH PREDICATE is causing the leading hint to fail,
but it also didn't work when I used NO PUSH PREDICATE even though it was
used but LEADING was not.
Best Regards,
AMIT
On Wed, Nov 15, 2023 at 10:40 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
That hint report and patch generation statement aren't consistent with the
previous output.
*3 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL@SEL$2 U -
USE_NL_WITH_INDEX(@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2) 5 -
SET$632CC7ED N - LEADING(@SET$632CC7ED VW_NSO_1@SEL$AC90CD92
PO_LINE_LOCATIONS_ALL@SEL$2) *
You have *@SET$632CC7ED* in the leading hint and that should be
*@SEL$AC90CD92. *
For an initial test of the patch I'd also use just use_nl() rather than
use_nl_with_index().
Regards
Jonathan Lewis
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => '7wh1j104vstdg',
name => '7wh1j104vstdg',
hint_text => 'LEADING(@SEL$AC90CD92
VW_NSO_1@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2)
USE_NL(@SEL$AC90CD92
PO_LINE_LOCATIONS_ALL@SEL$2)');
dbms_output.put_line(v1);
end;
/
SQL_ID 7wh1j104vstdg, child number 0
-------------------------------------
UPDATE PO_LINE_LOCATIONS POLL SET POLL.SECONDARY_QUANTITY_CANCELLED =
(SELECT INV_CONVERT.INV_UM_CONVERT( ITEM_ID => POL.ITEM_ID, PRECISION
=> NULL, FROM_QUANTITY => POLL.QUANTITY_CANCELLED, FROM_UNIT => NULL,
TO_UNIT => NULL, FROM_NAME => POLL.UNIT_MEAS_LOOKUP_CODE , TO_NAME =>
POLL.SECONDARY_UNIT_OF_MEASURE ) FROM PO_LINES POL WHERE POL.PO_LINE_ID
= POLL.PO_LINE_ID) WHERE NVL(POLL.CANCEL_FLAG, 'N') = 'I' AND
NVL(POLL.CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED' AND
POLL.SECONDARY_UNIT_OF_MEASURE IS NOT NULL AND POLL.LINE_LOCATION_ID IN
( SELECT LINE_LOCATION_ID FROM PO_LINE_LOCATIONS WHERE LINE_LOCATION_ID
= :B3 AND :B2 = :B1 UNION ALL SELECT LINE_LOCATION_ID FROM
PO_LINE_LOCATIONS WHERE PO_LINE_ID = :B3 AND :B2 = :B4 UNION ALL SELECT
LINE_LOCATION_ID FROM PO_LINE_LOCATIONS WHERE PO_HEADER_ID = :B3 AND
:B7 <> :B6 AND :B2 = :B5 UNION ALL SELECT LINE_LOCATION_ID FROM
PO_LINE_LOCATIONS WHERE PO_RELEASE_ID = :B3 AND :B7 = :B6 AND :B2 = :B5
)
Plan hash value: 3125101625
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | |
| | 80198 (100)| |
| 1 | UPDATE | PO_LINE_LOCATIONS_ALL |
| | | |
| 2 | NESTED LOOPS | |
1 | 67 | 80191 (8)| 00:00:04 |
|* 3 | TABLE ACCESS FULL | PO_LINE_LOCATIONS_ALL |
1 | 61 | 80181 (8)| 00:00:04 |
| 4 | VIEW | VW_NSO_1 |
1 | 6 | 10 (0)| 00:00:01 |
| 5 | SORT UNIQUE | |
4 | 65 | 10 (0)| 00:00:01 |
| 6 | UNION ALL PUSHED PREDICATE | |
| | | |
|* 7 | FILTER | |
| | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL |
1 | 10 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 |
1 | | 2 (0)| 00:00:01 |
|* 10 | FILTER | |
| | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL |
1 | 16 | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 |
1 | | 2 (0)| 00:00:01 |
|* 13 | FILTER | |
| | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL |
1 | 16 | 3 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 |
1 | | 2 (0)| 00:00:01 |
|* 16 | FILTER | |
| | | |
|* 17 | TABLE ACCESS BY INDEX ROWID BATCHED| PO_LINE_LOCATIONS_ALL |
1 | 23 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PO_LINE_LOCATIONS_N5 |
1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL |
1 | 16 | 3 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PO_LINES_U1 |
1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("SECONDARY_UNIT_OF_MEASURE" IS NOT NULL AND
NVL("CANCEL_FLAG",'N')='I' AND
"ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))
AND NVL("CLOSED_CODE",'OPEN')<>'FINALLY
CLOSED'))
7 - filter(("LINE_LOCATION_ID"=:B3 AND :B2=:B1))
8 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))
9 - access("LINE_LOCATION_ID"=:B3)
10 - filter(:B2=:B4)
11 - filter(("PO_LINE_ID"=:B3 AND
"ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))))
12 - access("LINE_LOCATION_ID"="LINE_LOCATION_ID")
13 - filter((:B7<>:B6 AND :B2=:B5))
14 - filter(("PO_HEADER_ID"=:B3 AND
"ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))))
15 - access("LINE_LOCATION_ID"="LINE_LOCATION_ID")
16 - filter((:B2=:B5 AND :B7=:B6))
17 - filter(("LINE_LOCATION_ID"="LINE_LOCATION_ID" AND
"ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))))
18 - access("PO_RELEASE_ID"=:B3)
19 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))
20 - access("PO_LINE_ID"=:B1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
1 - SEL$AC90CD92
U - LEADING(@SEL$AC90CD92 VW_NSO_1@SEL$AC90CD92
PO_LINE_LOCATIONS_ALL@SEL$2)
3 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL@SEL$2
U - USE_NL(@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2)
Note
-----
- SQL patch "7wh1j104vstdg" used for this statement
82 rows selected.
Other related posts:
