Thursday 16 April 2015

Getting bind variables from running SQL in em12c and find the UBE running it

Getting SQL bind variables can be tough.

Here is how I do it in EM12C.

goto performance –> SQL Monitoring to get to the page below

image

This is all of the tough SQL running at the moment.

Find the statement that you want and click the id (3rd column), you’ll get this screen

image

Now, click the circled button above and you’ll get this screen:

image

Choose show SQL Binds

image

Hit the save button and things get even better.  em12c writes you a script with all of the variables defined, you can then run it in SQLPlus if you need to.  Wow, that is awesome.

-- ============================================================================================== --
-- This file is generated by EM Database Express. --
-- The SQL bind variables, if any, will be declared below. --
-- ============================================================================================== --

declare
KEY1 NCHAR(32) := 'IMP';
KEY2 NCHAR(32) := 'ZZZ';
KEY3 NCHAR(32) := 'N';
KEY4 NCHAR(32) := '00133';
KEY5 NCHAR(32) := '00141';
KEY6 NCHAR(32) := '00146';
KEY7 NCHAR(32) := '00147';
KEY8 NCHAR(32) := '00178';
KEY9 NCHAR(32) := '00222';
KEY10 NCHAR(32) := '00244';
KEY11 NCHAR(32) := '00246';
KEY12 NCHAR(32) := '00250';
KEY13 NCHAR(32) := '00252';
KEY14 NCHAR(32) := '00253';
KEY15 NCHAR(32) := '00254';
KEY16 NCHAR(32) := '00255';
KEY17 NCHAR(32) := '00256';
KEY18 NCHAR(32) := '00257';
KEY19 NCHAR(32) := '00268';
KEY20 NCHAR(32) := '00275';
KEY21 NCHAR(32) := '00298';
KEY22 NCHAR(32) := '00314';
KEY23 NCHAR(32) := '00318';
KEY24 NCHAR(32) := '00352';
KEY25 NCHAR(32) := '00364';
KEY26 NCHAR(32) := '00601';
KEY27 NCHAR(32) := '00710';
KEY28 NCHAR(32) := '00756';
KEY29 NCHAR(32) := '00758';
KEY30 NCHAR(32) := '00840';
KEY31 NCHAR(32) := '00950';
KEY32 NCHAR(32) := '00963';
KEY33 NCHAR(32) := '00987';
KEY34 NCHAR(32) := '00988';
KEY35 NCHAR(32) := '00995';
KEY36 NVARCHAR2(32) := '115106';
KEY37 NVARCHAR2(32) := '115104';
KEY38 NCHAR(32) := NULL;
KEY39 NCHAR(32) := 'ZZZZZZZZZZZZ';
KEY40 NCHAR(32) := NULL;
KEY41 NCHAR(32) := 'ZZZZZZZZZZZZ';
begin
-- SELECT T0.GMCO, T0.GMAID, T0.GMMCU, T0.GMOBJ, T0.GMSUB, T0.GMANS, T0.GMDL01, T0.GMLDA, T0.GMBPC, T0.GMPEC, T0.GMBILL, T0.GMCRCD, T0.GMUM, T0.GMR001, T0.GMR002, T0.GMR003, T0.GMR004, T0.GMR005, T0.GMR006, T0.GMR007, T0.GMR008, T0.GMR009, T0.GMR010, T0.GMR011, T0.GMR012, T0.GMR013, T0.GMR014, T0.GMR015, T0.GMR016, T0.GMR017, T0.GMR018, T0.GMR019, T0.GMR020, T0.GMR021, T0.GMR022, T0.GMR023, T0.GMOBJA, T0.GMSUBA, T0.GMWCMP, T0.GMCCT, T0.GMERC, T0.GMHTC, T0.GMQLDA, T0.GMCCC, T0.GMFMOD, T0.GMUSER, T0.GMPID, T0.GMJOBN, T0.GMUPMJ, T0.GMUPMT, T0.GMCEC1, T0.GMCEC2, T0.GMCEC3, T0.GMCEC4, T0.GMIEC, T0.GMFPEC, T0.GMSTPC, T0.GMTXGL, T0.GMTOBJ, T0.GMTSUB, T0.GMPRGF, T0.GMTXA1, T0.GMR024, T0.GMR025, T0.GMR026, T0.GMR027, T0.GMR028, T0.GMR029, T0.GMR030, T0.GMR031, T0.GMR032, T0.GMR033, T0.GMR034, T0.GMR035, T0.GMR036, T0.GMR037, T0.GMR038, T0.GMR039, T0.GMR040, T0.GMR041, T0.GMR042, T0.GMR043, T1.MCMCU, T1.MCSTYL, T1.MCDC, T1.MCLDM, T1.MCCO, T1.MCAN8, T1.MCAN8O, T1.MCCNTY, T1.MCADDS, T1.MCFMOD, T1.MCDL01, T1.MCDL02, T1.MCDL03, T1.MCDL04, T1.MCRP01, T1.MCRP02, T1.MCRP03, T1.MCRP04, T1.MCRP05, T1.MCRP06, T1.MCRP07, T1.MCRP08, T1.MCRP09, T1.MCRP10, T1.MCRP11, T1.MCRP12, T1.MCRP13, T1.MCRP14, T1.MCRP15, T1.MCRP16, T1.MCRP17, T1.MCRP18, T1.MCRP19, T1.MCRP20, T1.MCRP21, T1.MCRP22, T1.MCRP23, T1.MCRP24, T1.MCRP25, T1.MCRP26, T1.MCRP27, T1.MCRP28, T1.MCRP29, T1.MCRP30, T1.MCTA, T1.MCTXJS, T1.MCTXA1, T1.MCEXR1, T1.MCTC01, T1.MCTC02, T1.MCTC03, T1.MCTC04, T1.MCTC05, T1.MCTC06, T1.MCTC07, T1.MCTC08, T1.MCTC09, T1.MCTC10, T1.MCND01, T1.MCND02, T1.MCND03, T1.MCND04, T1.MCND05, T1.MCND06, T1.MCND07, T1.MCND08, T1.MCND09, T1.MCND10, T1.MCCC01, T1.MCCC02, T1.MCCC03, T1.MCCC04, T1.MCCC05, T1.MCCC06, T1.MCCC07, T1.MCCC08, T1.MCCC09, T1.MCCC10, T1.MCPECC, T1.MCALS, T1.MCISS, T1.MCGLBA, T1.MCALCL, T1.MCLMTH, T1.MCLF, T1.MCOBJ1, T1.MCOBJ2, T1.MCOBJ3, T1.MCSUB1, T1.MCTOU, T1.MCSBLI, T1.MCANPA, T1.MCCT, T1.MCCERT, T1.MCMCUS, T1.MCBTYP, T1.MCPC, T1.MCPCA, T1.MCPCC, T1.MCINTA, T1.MCINTL, T1.MCD1J, T1.MCD2J, T1.MCD3J, T1.MCD4J, T1.MCD5J, T1.MCD6J, T1.MCFPDJ, T1.MCCAC, T1.MCPAC, T1.MCEEO, T1.MCERC, T1.MCUSER, T1.MCPID, T1.MCUPMJ, T1.MCJOBN, T1.MCUPMT, T1.MCBPTP, T1.MCAPSB, T1.MCTSBU FROM PRODDTA.F0901 T0,PRODDTA.F0006 T1 WHERE ( ( T0.GMR002 = :KEY1 AND T1.MCRP20 <> :KEY2 AND T0.GMPEC <> :KEY3 AND T0.GMCO IN ( :KEY4,:KEY5,:KEY6,:KEY7,:KEY8,:KEY9,:KEY10,:KEY11,:KEY12,:KEY13,:KEY14,:KEY15,:KEY16,:KEY17,:KEY18,:KEY19,:KEY20,:KEY21,:KEY22,:KEY23,:KEY24,:KEY25,:KEY26,:KEY27,:KEY28,:KEY29,:KEY30,:KEY31,:KEY32,:KEY33,:KEY34,:KEY35 ) ) AND ( T0.GMUPMJ <= :KEY36 AND T0.GMUPMJ >= :KEY37 ) ) AND ( T0.GMMCU BETWEEN :KEY38 AND :KEY39 ) AND ( T1.MCMCU BETWEEN :KEY40 AND :KEY41 ) AND ( T0.GMMCU=T1.MCMCU ) ORDER BY T0.GMAID ASC
end;

To find the culprit, you need to go back to this screen


image


Click the username field circled above


image


So with the above we can tell that it’s a batch job and that it’s PID is 1837 and it’s a runbatch process


image


So we can see in E1 that this is R55BLAHBLAH, nice one.  We know the report that is running the crazy statement.

1 comment:

Anonymous said...

Why the below date values from JDE are showing as NVARCHAR2(32) in SQL binds. Date values are in julian date format and they are Number(6,0).

KEY36 NVARCHAR2(32) := '115106';
KEY37 NVARCHAR2(32) := '115104';

We are seeing the same stuff where the date values from JDE are coming as NVARCHAR2(32) and we are trying to query the tables in Oracle based on these dates. Corresponding Oracle table date columns are defines as Number(6). As the datatypes between JDE and Oracle are not matching, we are thinking that indexes are not considered resulting in long execution times.