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.
In Grid Control on July 21, 2010 at 10:16
Grid Control 11g new features:
Oracle 11g Grid Control 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:
Notice how short the Database Management section is!
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)
CURSOR c1 IS
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE s.message LIKE '%'||v_like||'%';
V_LIKE := P_LIKE;
FOR r IN c1 LOOP
DELETE from sysman.mgmt_severity
target_guid = r.target_guid AND
metric_guid = r.metric_guid AND
key_value = r.key_value;
Now, let us remove all warnings about invalid objects:
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
l_greshki t_greshki := t_greshki(,'job','invalid','status','resource');
FOR i IN l_greshki.first .. l_greshki.last LOOP
Pretty useful for me at least.