Performance and tuning pack, JD Edwards and oracle 11G

Wow…

I’ve done lots of oracle tuning before, but it’s always been reactive.  That UBE is slow, okay… run with debugging…  Find slow statements… tune them…  Things have just got a whole lot better if you are licensed for the performance and tuning pack.

If you have this, just sit back and relax…

Then find the screen that says “Automatic SQL Tuning Result Summary” and look through the recommendations. 

Look at the sceen above, you can drill down into recommendations based upon SQL Profiles, indexes or change of statement.

Sample drill down is below, showing you anticipated savings for implementing the recommendations.

You can get a global summary of index recommendations

Jeepers! 

This thing is amazing.  In one foul swoop you can adjust many of the poor performing statements that are being executed against the entire database.  With a little bit of JD Edwards knowledge, you know what to fix and what not to.

So, I’ve gone ahead and implemented a pile of SQL_Profiles (and a couple of indexes) to fix some poor performing SQL.  I can’t wait to see the integrities run a little better in the morning.

SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM   DBA_SQL_PROFILES;

Then if they do not work:

BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE ( 
    name => 'my_sql_profile' 
);
END;
/

Note that SQL profiles are a more elegant way of getting hints to JD Edwards, as you cannot implement database hints in JDE.  Another poor mans way of doing this is forcing parallelism in a “login” trigger.