Grid Control: Custom Alerts Notification Method with PL/SQL

We’ve been asked to generate automatic database incidents in our service desk (CA Unicenter). 

And through the Grid Control it’s a piece of cake.

 

The example below is generic. If you need the exact syntax to send to your CA unicenter server, send me an email at pierre.roussin@gmail.com.

 

For more flexibility, we created 2 procedures, one to get the notification from the console mechanism and one to build the message and email it to the recipient (service desk in our case).

 

Creating the incident

This procedure can be customized to send an email to the dba accounts

create or replace procedure sysman.create_incident(s1 in varchar2,s2 in varchar2, s3 in varchar2)
IS 
mailhost VARCHAR2(30) := ‘smtp server’
mail_conn utl_smtp.connection;    
msg1 varchar2(2000);     
sender varchar2(30):=’address of sender‘; 

recipient varchar2(30):=’destination of email‘; 
subject varchar2(40):=‘customized subject’;  
finalstr varchar2(8000):=null;    /* Body of the email */
 
/* Wrapper function to write data to the mail server */
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
        utl_smtp.write_data(mail_conn, name || ‘: ‘ || header || utl_tcp.CRLF);
END;
 
BEGIN
/* in the variable finalstr you can customized your notification email */ 

finalstr:= ‘customized text‘ || utl_tcp.CRLF;


 
 mail_conn := utl_smtp.open_connection(mailhost);
 utl_smtp.helo(mail_conn, mailhost);
 utl_smtp.mail(mail_conn,sender);    — sender
 utl_smtp.rcpt(mail_conn,recipient); — recipient
 utl_smtp.open_data(mail_conn);
 send_header(‘From’,    sender || ‘<‘ || sender || ‘>’);
 send_header(‘To’,      ‘”Recipient” <‘ || recipient || ‘>’);
 send_header(‘Subject’, subject);
 utl_smtp.write_data(mail_conn, finalstr);
 utl_smtp.close_data(mail_conn);
 utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(‘Send Mail Error: ‘ || substr(sqlerrm,1,100));
END;

 

Getting the console notification

Notice that we only want to be notified by critical alerts. consult the Oracle documentation for other status.

 

create or replace procedure sysman.get_notification(severity IN MGMT_NOTIFY_SEVERITY)
IS
BEGIN
   IF severity.severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL
   THEN
        BEGIN

/* severity object attributes are passed to the procedure */

       create_incident(severity.target_name,severity.message,severity.collection_timestamp);
        EXCEPTION
        WHEN OTHERS
        THEN
               RAISE_APPLICATION_ERROR(-20000, ‘Procedure create_incident ended in error’);
        END;
        COMMIT;
   END IF;
END;

 

 Enabling the procedure in the grid control

  • Navigate to setup – Notification Methods – Scripts and snmp traps – Add pl/sql procedure – Go
  • Enter the name, description and the fully qualified name of the procedure (ex:sysman.get_notification)
  • Navigate to Preferences – Notifications – Rules – Assign Methods to Multiple rules
  • Choose a rule name (database availability and critical states in this case), check the box of your custom method

 

Pierre

Grid Control: Reclaiming space in the mgtmt_tablespace

After a few months of operation, the grid control mgmt_tablespace can take up a few unnecessary gigabytes.

Oracle provides a package to purge the old partitions.

In order to run the package the management server must be shutdown. (due to a bug)

Then  logon as SYSMAN and run:

  • exec emd_maintenance.partition_maintenance;
  • exec emd_maintenance.analyze_emd_schema(‘SYSMAN’);
  • exec emd_maintenance.remove_em_dbms_jobs;
  • exec emd_maintenance.submit_em_dbms_jobs;

We do it every 3 months and save up each time around 2Gb. (based on 75 targets)

 

Pierre

Outlines: Introducing a Hint on a non-modifiable query

Last week I created 2 new indexes to solve a performance issue. Following the weekly statistics gathering of the schema, a query started to use one of those 2 indexes.

The effect was disastrous as a main application screen took around 12 seconds to populate instead of a fraction of it.

This application being a third party product, the queries are not modifiable.

My only option was to introduce a HINT into the query through an outline, so I proceeded as follow:

  • alter system set query_rewrite_enabled=TRUE scope=both;
  • alter system set use_stored_outlines=TRUE; (not a database parameter yet)
  • created an outline (QUERYO) with the original query (without the HINT)
  • created an outline (QUERYH) with the original query (including the required HINT)
  • Exchanged the outlines plans:
  • As SYS: update OUTLN.OL$HINTS
    set OL_NAME=decode(OL_NAME,’QUERYH’,’QUERYO’,’QUERYO’,’QUERYH’)
    where OL_NAME in (‘QUERYO’,’QUERYH’); commit;
  • drop outline QUERYH;
  • Created a database trigger to run ‘alter system set use_stored_outlines=TRUE’ on database startup (since this parameter is not a database parameter yet)

Pierre

DbaBar

I want to communicate my enthusiasm with a monitoring/administration tool called DbaBar from www.dbmotive.com.

It’s by far the most sleekest and friendliest admin tool for Oracle I have used in 15 years.

There’s no need to install a server agent and the beauty is: that it integrates seamlessly in windows explorer or IE as a toolbar.

I suggest to give it a try, as you can test it for 30 days.

 

Pierre

Indexing the 'Order By' Clause

I got a call last week from a client running a Siebel application, he complained of slow performance on a particular screen, following a new release.

Since the objects statistics were not stale and the indexes had been rebuilt a few days ago, I extracted the execution plan of the screen’s query.

As you can see, the explain plan showed a near optimal access:

SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID, T1.ATTRIB_02, T1.ATTRIB_03,T1.X_OFFER_HISTORY
FROM SBL.S_ASSET_XM T1
WHERE (T1.TYPE = ‘Offer History’)
AND (T1.PAR_ROW_ID= :1)
ORDER BY T1.PAR_ROW_ID DESC, T1.CREATED DESC;

Id  Operation                          Name           Rows        Bytes     Cost
0   SELECT STATEMENT                                  1              74        4
1   SORT ORDER BY                                     1              74        4
2   TABLE ACCESS BY INDEX ROWID     S_ASSET_XM        1              74        1
3   INDEX RANGE SCAN                S_ASSET_XM_U1     1                        3

 

At this point, I extracted a statspack from the last 7 hour workload. The query showed up as the top query in the ‘Buffer gets’ and ‘Physical reads’ sections.

                                                   CPU      Elapsd
 Physical Reads   Executions Reads per Exec %Total Time (s) Time (s)

     28,679,016        1,050       27,313.3   64.7   3267.2  4488.59
 Buffer Gets   
     37,389,651                    35,609.2   14.3

 

Notice that the buffer hit ratio is not quite up to par as well as the execution timings.

 

Since the result must be sorted in descending order, let’s first check the selectivity of the fields from the ORDER BY clause. The current index has a 13% selectivity, the 2 fields in the ORDER BY have a 21% selectivity, not enough to make a strong difference, however since the result is required in descending order, let’s create an index sorted in descending order on the ORDER BY clause.

For this purpose, since I didn’t have a test environment with the same data volume, I created a virtual index in production:

  • alter session set “_use_nosegment_indexes” = true;

  • create index test_desc on sbl.s_asset_xm(par_row_id desc,created desc) nosegment;

 

Then I extracted the execution plan:


Id  Operation                          Name           Rows        Bytes     Cost
0   SELECT STATEMENT                                  1              74        1
1   TABLE ACCESS BY INDEX ROWID     S_ASSET_XM        1              74        1
2   INDEX RANGE SCAN                S_ASSET_XM_OBDESC 1                       12

 

At first look, it does look slightly faster. What really makes a huge difference is the access itself:

PLAN TABLE OUTPUT

   1 - filter("T1"."TYPE"='Offer History')
   2 - access(SYS_OP_DESCEND("T1"."PAR_ROW_ID")=SYS_OP_DESCEND(:Z))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("T1"."PAR_ROW_ID"))=:Z)

 

The most selective field is accessed in descending order therefore eliminating the need for a costly sort. So let’s see after implementation the actual performance gain…

 

Following the index implementation, the query didn’t show up in the top queries anymore, so I extracted the hash value and ran the ?/rdbms/admin/sprepsql.sql, the performance gain was bigger than I expected.

                                                      
                     Statement Total      Per Execute     Before Index: Per execute
                     
        Buffer Gets:          17,707              5.5                       27313.3
         Disk Reads:             276              0.1                       35606.2
     Rows processed:          14,518              4.5
     CPU Time(s/ms):               1               .2
 Elapsed Time(s/ms):               2               .7
              Sorts:               0               .0
        Parse Calls:             745               .2
      Invalidations:               0
      Version count:               2
    Sharable Mem(K):              35
         Executions:           3,192

 

Pierre Roussin

Installing the Management Agent 10.2.0.x

I got an email yesterday from a desperate DBA who couldn’t find the proper instruction to deploy a management agent on a Linux server, that is management agent release 10.2.0.3.

Oracle has complicated things slightly.

Fortunately I’d done it before on our Linux and Aix platforms, and indeed that’s an awkward contraption that needs to be setup.

So here it is:

  1. Create the following directory: /u01/oracle/product/10.2.0.3/oms10g/sysman/agent_download/10.2.0.3
  2. Copy the downloaded zipped file into the directory
  3. Unzip the file
  4. Set your TEMP and DISPLAY environment variables
  5. Start your favorite X display software
  6. Move to ./linux/agent and run runInstaller
  7. During the course of the installation you will be ask to enter the location of you Oracle Base directory, in this case it is: /u01/oracle/product/10.2.0.3

 

After the installation, I noticed that sometimes the agentca -f  needs to be run, because of certain targets not being found during the initial agent configuration, within the configuration assistant.

 

Pierre

Optimizer Cost Model vs Implicit Sorting and Datatype Conversion

I recently stumbled on an issue for the first time, while migrating an application from 9i to 10g.

When migrating an application to 10g, one thing that needs to be considered is the optimizer cost model change.

In Oracle 9i, the cost model parameter (_optimizer_cost_model) is set to IO by default, while in 10G it is set to CHOOSE, seems to default to CPU in all cases however.

It doesn’t seem like much, but the implications are serious.

The IO cost model provides implicit data conversion and sorting.

In the case of my application, the developers had relied on implicit data conversion and data sorting when designing the reporting module.

In short, they had not included any ORDER BY clauses and were comparing CHAR’s with NUMBER’s.

 

Obviously when there were only 1 or 2 predicates in the WHERE clause, it didn’t make a difference wether sorting was implicit or not.

 

So after a migration, if you see strange sorting order and the ORA-01722: invalid number error, check your code.

A workaround is to set the  _optimizer_cost_model value to IO.

 

Pierre