Dontcheff

Archive for the ‘Grid Control’ Category

11g Grid Control: PL/SQL execution monitoring in SQL Monitoring

In DBA, Grid Control, PL/SQL, SQL on September 6, 2010 at 11:43

One of the most useful new features of 11g Grid Control is PL/SQL execution monitoring. Wonder now if a developer wants to know how his PL/SQL procedure did in terms of its SQL and DML statements? What calls did it make and how long it took per call? Beats me is what I would have said before 🙂 Have a look at what Oracle did with 11g Grid Control:

Not only we see the duration of the PL/SQL procedure but we can also see its activites and metrics per SQL call:

We can monitor LIVE either the execution details compared to a previous run with a real time 15 second refresh:

or view its execution details with real time 10 second refresh:

giving us time, wait and io statistics. You can view it or save it in a report format or even mail it directly to the developer!

Good one from Oracle.

How did we do similar things in 8i?

Oracle teaching Oracle

In Grid Control, SQL on July 22, 2010 at 17:09

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

In Grid Control on July 21, 2010 at 10:16

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

    In Grid Control, PL/SQL on July 21, 2010 at 08:09

    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.