Hello group,
Firstable, I want to be thankful with you guys to answer my previous doubt, I really appreciate it.
Now, I want to optimize a process (SQL statement developed by other team) which is using a parallel hint with some UNION ALL's and also, it is using a nested table, so I want to know how run it manually outside of the package, it is using some bind variables, so I am below statement to know the values for them:
select * from table (dbms_xplan.display_cursor('867jsfsnz2k0m', format => 'TYPICAL +PEEKED_BINDS')); and the output for this is:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - :B1 (NESTED TABLE): [Not Printable]
2 - :B2 (NUMBER): 18790048
3 - :B1 (NESTED TABLE, Primary=1)
4 - :B2 (NUMBER, Primary=2)
5 - :B1 (NESTED TABLE, Primary=1)
6 - :B2 (NUMBER, Primary=2)
7 - :B1 (NESTED TABLE, Primary=1)
8 - :B2 (NUMBER, Primary=2)The query is as follow:
SELECT /*+ PARALLEL(8) */
OH.ORDER_NO,
OH.CURRENCY_CODE,
OH.EXCHANGE_RATE,
OH.IMPORT_COUNTRY_ID,
OH.SUPPLIER,
S.CURRENCY_CODE SUP_CURRENCY,
OS.ITEM,
NULL COMP_ITEM,
OS.ORIGIN_COUNTRY_ID,
DECODE (NVL (CA.DEFAULT_PO_COST, 'BC'),
'BC', ISCL.UNIT_COST,
ISCL.NEGOTIATED_ITEM_COST)
UNIT_COST,
OL.ROWID OL_ROWID,
OL.LOCATION,
OL.QTY_ORDERED
- (NVL (OL.QTY_RECEIVED, 0) + NVL (OL.QTY_CANCELLED, 0))
QTY_ORDERED,
CZG.ZONE_ID,
IM.DEPT,
IM.CLASS,
IM.SUBCLASS,
DEPS.OTB_CALC_TYPE
FROM COST_SUSP_SUP_DETAIL D,
ITEM_MASTER IM,
DEPS,
TABLE (CAST (:B1 AS ITEM_TBL)) ITM, <--- Bind variable here
ORDSKU OS,
ORDHEAD OH,
SUPS S,
ORDLOC OL,
COST_ZONE_GROUP_LOC CZG,
ITEM_LOC IL,
ITEM_SUPP_COUNTRY_LOC ISCL,
COUNTRY_ATTRIB CA
WHERE IM.ITEM = VALUE (ITM)
AND D.COST_CHANGE = :B2 <---- Bind variable here
AND OL.ITEM = OS.ITEM
AND OS.ITEM = IM.ITEM
AND ( IM.ITEM = D.ITEM
OR IM.ITEM_PARENT = D.ITEM
OR IM.ITEM_GRANDPARENT = D.ITEM)
AND IM.ITEM_LEVEL <= IM.TRAN_LEVEL
AND OS.ORDER_NO = OH.ORDER_NO
AND ( (D.RECALC_ORD_IND = 'Y' AND OH.STATUS IN ('A', 'S', 'W'))
OR (D.RECALC_ORD_IND = 'N' AND OH.STATUS IN ('S', 'W')))
AND OH.SUPPLIER = S.SUPPLIER
AND OH.SUPPLIER = D.SUPPLIER
AND OS.ORDER_NO = OL.ORDER_NO
AND ISCL.ITEM = OL.ITEM
AND ISCL.SUPPLIER = D.SUPPLIER
AND ISCL.ORIGIN_COUNTRY_ID = D.ORIGIN_COUNTRY_ID
AND ISCL.ORIGIN_COUNTRY_ID = OS.ORIGIN_COUNTRY_ID
AND ISCL.LOC = NVL (IL.COSTING_LOC, OL.LOCATION)
AND OL.QTY_RECEIVED IS NULL
AND NOT (OL.COST_SOURCE = 'MANL')
AND IL.LOC = OL.LOCATION
AND IL.ITEM = OL.ITEM
AND ISCL.ORIGIN_COUNTRY_ID = CA.COUNTRY_ID(+)
AND CZG.ZONE_GROUP_ID = IM.COST_ZONE_GROUP_ID
AND OL.LOCATION = CZG.LOCATION
AND OL.LOC_TYPE = CZG.LOC_TYPE
AND IM.DEPT = DEPS.DEPT
AND NOT EXISTS
(SELECT CFA.ORDER_NO
FROM V_CFAG_ORDHEAD CFA, SUPS S, V_SUPS_FSA VS
WHERE 1 = 1
AND S.SUPPLIER = VS.SUPPLIER
AND NVL (CFA.ORDER_NO, 1) = OH.ORDER_NO
AND VS.SUPPLIER_TYPE = 'MAYOR'
AND OH.SUPPLIER = S.SUPPLIER
AND NVL (CFA.SENT_TO_SUPPLIER, 'N') = 'Y')This is only the first part of three UNION ALL's, as you can see, there are a couple of bind variables there:
TABLE (CAST (:B1 AS ITEM_TBL)) ITM,
AND D.COST_CHANGE = :B2
How can I run this query manually if there is a nested table usage for the first bind variable?
Thanks in advance.
Kind regards,
Francisco Mtz.
Many clients don't have a way to bind nested tables or other collection types. There is a workaround though:
Use PL/SQL!
All variables/parameters used by static SQL in PL/SQL are converted to bind variables. So you can declare a nested table variable, initialize it, and reference it in your query.
For example:
create or replace type numbers_t is
table of number;
/
declare
ns numbers_t := numbers_t ( 1, 4, 9 );
begin
for rws in (
select /*+ find me */* from table ( ns )
) loop
dbms_output.put_line ( rws.column_value );
end loop;
end;
/
select sql_text from v$sql
where sql_text like 'SELECT%find me%'
and sql_text not like '%v$sql%';
SQL_TEXT
-----------------------------------------
SELECT /*+ find me */* FROM TABLE ( :B1 )