Oracle teaching Oracle

Interesting enough, often we rely on the same component to improve the SQL statement that could not generate an optimal plan in the first place. I am talking about the Cost Based Optimizer. Look at this. Note that the database in question is an active database.

So, top SQL is one being generated by Grid Control. So far so good. Let’s run the SQL Tuning Advisor and have a look at the recommendations:

What do we witness? CBO learning from its mistakes? Or Oracle teaching Oracle? Or Oracle learning from Oracle. Whatever…

Interesting recommendation: “Rewrite the predicate into an equivalent form to take advantage of indices”. I wish I could start re-writing the Grid Control code. But I will analyze SYS.LIBRARY$ and accept the profile. Note: in 11g Grid Control, we can implement the profile with forced matching via the GUI, no need to do it any longer in SQL*Plus:

However, I still claim that Oracle Enterprise Manager Grid Control is probably the best feature/tool/utility in Oracle after 9i.

Grid Control 11g new features

Grid Control 11g new features:
1. You must install Oracle WebLogic Server 10.3.2 (Oracle Fusion Middleware 11g Release 1 Patch Set 1) as a pre-requisite to Oracle Grid Control installation. The patch you will have to apply must be ordered via Metalink. If you don’t install it, it will still work but WL will throw lots of error messages.
2. Enterprise Manager 11g Grid Control Release 1 now supports the new features available in Oracle Database 11g Release 2. You notice the new GC mainly when working with 11gR2 databases.
3. As a whole, it does not look very different than 10g which is good in a way. One of my DBAs complained about the fonts 🙂
4. It is faster but it crashes, hangs, whatever due to WebLogic 🙂 Lots of logs. Beware that the /usr/local/bin/stop_gridd1.sh might leave hanging processes. Should be killed manually with kill -9.
5. Here is a good link to the documentation about the new features:

Oracle 11g Grid Control New Features

Notice how short the Database Management section is!

Remove old Grid Control warnings

If you use Grid Control and want to delete some old warning messages and the “clear alert” button does not help, then use the following PL/SQL block. Run it as SYSMAN. Depending on the number of alerts the cursor fetches, the delete might take up to a minute or so.

You can do masive deletes based on the message text, for example:

CREATE OR REPLACE PROCEDURE DELETE_ALERTS_LIKE(P_LIKE in varchar2)
is
V_LIKE varchar2(22);
   CURSOR c1 IS
     SELECT s.target_guid,
            s.metric_guid,
            s.key_value
       FROM mgmt_targets t JOIN mgmt_current_severity s
            ON s.target_guid = t.target_guid
      WHERE s.message LIKE '%'||v_like||'%'; 
BEGIN
V_LIKE := P_LIKE;
    FOR r IN c1 LOOP
       em_severity.delete_current_severity(r.target_guid,r.metric_guid,r.key_value);
       DELETE from sysman.mgmt_severity
       WHERE
         target_guid = r.target_guid AND
         metric_guid = r.metric_guid AND
         key_value = r.key_value;
       COMMIT;
    END LOOP;
END;
/

Now, let us remove all warnings about invalid objects:

DELETE_ALERTS_LIKE('invalid');

You can even make a procedure which will remove with just one command all old alerts having ‘job’,’invalid’,’status’ or ‘resource’ in the text message:

CREATE or REPLACE TYPE t_greshki IS TABLE OF VARCHAR2(22);
/

CREATE OR REPLACE PROCEDURE DELETE_ALERTS
is
l_greshki t_greshki := t_greshki(,'job','invalid','status','resource');
BEGIN
    FOR i IN l_greshki.first .. l_greshki.last LOOP
       DELETE_ALERTS_LIKE(l_greshki(i));
       COMMIT;
    END LOOP;
END;
/

exec DELETE_ALERTS;

Pretty useful for me at least.