I was trying to execute following SQL through client side tools and found network delays were contributing to the SQl execution times. This SQL normally returns in less than 50 millisec and now taking more than 700 Msec. I am addicted to TOAD -TORA GUI created Execution plans and was looking for a an easy way to get execution times as well as plans on sqlplus. Here is how you do it .

SQLPLUS> set autotrace on; – Will display formatted explain plan

SQLPLUS> set timing on;
– Oracle will automatically display the elapsed wall-clock time for each SQL command you run subsequently.
– The format is HH:MM:SS.hundredths of a second for example 00:00:00.81 is .81 seconds

If you get error like “PLAN_TABLE not found” use $ORACLE_HOME/utlxplan to create PLAN_TABLE and pass on access to relevant user.

SQLPLUS> set autotrace on;
SQLPLUS> set timing on;
SQL> select count(*) from voucher;

COUNT(*)
———-
144189820

Elapsed: 00:01:55.05 format is HH:MM:SS.hundredths of a second

Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1)
1    0   SORT (AGGREGATE)
2    1     INDEX (FAST FULL SCAN) OF ‘VOUCHER_P0001001’ (UNIQUE) (Cost=3 Card=126665996)

Statistics
———————————————————-
0  recursive calls
0  db block gets
622158  consistent gets
621830  physical reads
0  redo size
522  bytes sent via SQL*Net to client
651  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

PLAN_TABLE is the default table for results of the EXPLAIN PLAN statement. It is created by $ORACLE_HOME/rdbms/admin/utlxplan.sql, and it contains one row for each step in the execution plan.

For 10G:

create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30)
);

For last many years I worked for number of clients using all sort of latest technologies. One of my duties was tuning and monitoring system performance which formed part of Capacity planning. I have been using number of tools and when I saw 10G grid first time I could see a real way forward where a database technology could work towards client needs to deliver end result.

There is an ever increasing trend on companies using managed service contract for Database and Application support. The Billing for these services is derived from time spent on fixing problems/issues. For every managed service contract you have to use case management tools like Remedy,Clarify, Hp Openview . These tools raise work cases on a specific event and could be tracked up and down different departments responsible for an activity.

Also a tool like 10G grid needs to be capable monitoring range of other systems like Sybase,SQL Server,Mysql , SAN file systems.

The initial version of 10g grid was not capable of working with case management tools or monitoring heterogeneous systems  and as a result 10g grid pick up was slow by most companies. But now Oracle have released following  connectors to liaise with these systems.

• Login to grid control using sysman or any admin user and click on deployments tab

• Scroll down at same screen and click on “Install Agent” under “Agent Installation” section

• Select “Fresh Install” at this screen

• At this screen select Client platform, Agent version, Installation Base Directory, OS Credentials

• Scroll down to Management Server Security section and specify registration password. Providing password will configure the agents to run in secure mode after installation.

• Click on Continue. Grid will start installing an agent at Target after initial checks.

Once installed make sure Agent is running at Target and locate it at grid control

Agent Deploy Log Files

Advantages of using grid control to schedule jobs

• Could be run against multiple targets at any given time
• Run a job immediately or repeatedly on a schedule
• Could be submitted against number of targets
• Could be shared using job library

1. View Jobs at Grid Control:

Login at Grid and click on Top tab –> Jobs –> Click on “Job Library” option to view number of jobs.

To see activity of job click on “Job Activity”

2. Create a new Job : I often see massive trace files consuming disk space. I will “create a dbms job to delete bdump trace files” (*.trc) older than 7 days i.e “find /opt/oracle/admin/repAAA/bdump -type f -mtime +7 -exec rm -f {} \;”

Select “Create Library Job”  -> “OS command” and hit <Go>

Add Description of a Job like “Delete udump trace files”

Add OS command at COMMAND box  “find /opt/oracle/admin/repAAA/bdump -type f -mtime +7 -exec rm -f {} \;”.  I selected “Single Operation” as it is a single UNIX command to delete files. If you have a UNIX script then please make sure you select Script at a dropdown box.

Enter OS credentials for a Target System

Add a schedule on what times you want to run “command/script”.

Now select “Save to Library” from right hand top menu.

Click on Database Tab at grid Control

Scroll down at database page and select “Metric and Policy Settings” under “Related Links”

Please select Metric you want to alter and click “OK”

Background : I have installed OMS/OMR and OMA successfully. In theory I should see OMA client successfully configured at OMS grid console but

$ ./emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.3.0
OMS Version       : 10.2.0.2.0
Protocol Version  : 10.2.0.2.0
Agent Home        : /opt/oracle/product/oracle10gAgent/agent10g
Agent binaries    : /opt/oracle/product/oracle10gAgent/agent10g
Agent Process ID  : 23314
Parent Process ID : 23295
—————————————————————
Agent is Running but Not Ready

To locate an exact error look at OMS files $OMS_HOME\sysman\log\emoms.log and $OMS_HOME\sysman\log\emoms.trc

My  $OMS_HOME\sysman\log\emoms.trc show error at  Trigger ‘SYSMAN.EM_VIOLATION_CHECKS’

2008-12-02 15:00:37,180 [SeverityLoad https://bmhp01:3872/emd/main/] ERROR XMLLoader.Handler processInsertException.462 – java.sql.SQLException: ORA-20612: Internal error processing severity. Closing availability record with negative duration for :  (target_guid = 1C2A2131D1A40C621929D7CE63032001) (collection_ts = 2008-12-02 14:47:07) (current_status = AGENT DOWN)
ORA-06512: at “SYSMAN.EM_SEVERITY”, line 2110
ORA-06512: at “SYSMAN.EM_SEVERITY”, line 3287
ORA-06512: at “SYSMAN.EM_VIOLATION_CHECKS”, line 791
ORA-04088: error during execution of trigger ‘SYSMAN.EM_VIOLATION_CHECKS’

Check at Google or metalink for further details on it.

– Sometimes you may have to delete records from OMR tables to fix this issue see Note:603011.1

– For ORA-04088: during execution of trigger SYSMAN.TARGETS_INSERT_TRIGGER , locate TARGET_GUID/AGENT_GUID at emoms.log  for example CE6B5C2E232766E01D3803EB03D70128 and run “delete from MGMT_TARGET_ASSOC_INSTANCE where target_guid = HEXTORAW(‘TARGET_GUID’);”

How to fix it? For clustered install (RAC/OS cluster) please see details here

$ ./emctl stop agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Stopping agent … stopped.

Delete all files in $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state

$ ./emctl clearstate agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
EMD clearstate completed successfully

$ ./emctl unsecure agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Checking Agent for HTTP…   Done.
Agent is already stopped…   Done.
Unsecuring agent…   Started.
OMS Upload URL – http://ASST105157:4889/em/upload/ is locked or unavailable.

$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload completed successfully

$ ./emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.3.0
OMS Version       : 10.2.0.2.0
Protocol Version  : 10.2.0.2.0
Agent Home        : /opt/oracle/product/oracle10gAgent/agent10g
Agent binaries    : /opt/oracle/product/oracle10gAgent/agent10g
Agent Process ID  : 23314
Parent Process ID : 23295
Agent URL         : https://bmhp01:3872/emd/main/
Repository URL    : https://ASST105157:1159/em/upload
Started at        : 2008-12-02 14:52:07
Started by user   : oracle
Last Reload       : 2008-12-02 14:52:07
Last successful upload                       : 2008-12-02 15:05:03
Total Megabytes of XML files uploaded so far :     5.25
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    32.43%
Last successful heartbeat to OMS             : 2008-12-02 15:04:50
—————————————————————
Agent is Running and Ready

$ ./emctl secure agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Enter Agent Registration password :
Agent successfully stopped…   Done.
Securing agent…   Started.
Requesting an HTTPS Upload URL from the OMS…   Done.
Requesting an Oracle Wallet and Agent Key from the OMS…   Done.
Check if HTTPS Upload URL is accessible from the agent…   Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode…   Done.
EMD_URL set in /opt/oracle/product/oracle10gAgent/agent10g/sysman/config/emd.properties
Securing agent…   Successful.

I can see new new target at grid console

Download OMS software from here

I am using Windows 10.2.x release for my OMS install. Due to limited hardware I am installing OMS (oracle management service or GRID console) on windows box while OMR (OMS database) configured at remote HP True 64 system. I have a separate database instance called “oms” for OMR.

Following diagram will give you an idea on OMS/OMR and OMA

Towards end of install, Oracle will configure default configuration and will take some time depending on underlying hardware. Please be patient until you see status=”SUCCEEDED” at GUI Installer.

Let go ahead and install Grid Agent at clients for monitoring.

How to start a GRID Console?

1.  OMR : Please start OMR (Oracle Management Repository)database as well a listener

2.  OMA : If you are using windows make sure “XXX10gAgent” service is started, for Unix/Linux use “emctl start agent” to start an agent

3. OMS : OMS could be controlled using number of methods

a. Use opmnctl to start and stop Oracle OMS

C:\oracle\product\10.2.0\oms10g\opmn\bin>opmnctl stopall
opmnctl: stopping opmn and all managed processes...

At this point http://asst105157:4889/em/console/ will return an error use following command to start all OMS components

C:\oracle\product\10.2.0\oms10g\opmn\bin>opmnctl startall
opmnctl: starting opmn and all managed processes...

C:\oracle\product\10.2.0\oms10g\opmn\bin>opmnctl status -l
Processes in Instance: EnterpriseManager0.asst105157

-ias-component     | process-type       |     pid | status   |        uid |  memused |    uptime | ports-------------------+--------------------+---------+----------+------------+----------+-----------+------DSA                | DSA                |     N/A | Down     |        N/A |      N/A |       N/A | N/AHTTP_Server        | HTTP_Server        |    4460 | Alive    |  607649821 |    43796 |  00:03:11 | http1:7778,http2:7200,https1:4444,https2:1159,http3:4889
LogLoader          | logloaderd         |     N/A | Down     |        N/A |      N/A |       N/A | N/Adcm-daemon         | dcm-daemon         |     N/A | Down     |        N/A |      N/A |       N/A | N/AOC4J               | home               |    5620 | Alive    |  607649822 |    30928 |  00:03:34 | ajp:12501,rmi:12401,jms:12601
OC4J               | OC4J_EM            |    5756 | Alive    |  607649823 |    30564 |  00:03:34 | ajp:12502,rmi:12402,jms:12602
OC4J               | OC4J_EMPROV        |    1260 | Alive    |  607649824 |    30796 |  00:03:34 | ajp:12503,rmi:12403,jms:12603
WebCache           | WebCache           |    4684 | Alive    |  607649825 |    31372 |  00:03:34 | http:80,invalidation:9401,statistics:9402
WebCache           | WebCacheAdmin      |     988 | Alive    |  607649826 |     9088 |  00:03:34 | administration:9400

b. Use emctl to start, stop, or check the status of Application Server Control

Start  iasconsole using “emctl start iasconsole”  and try http://<Host IP>:1810 to manage OMS components

c. Use dcmctl   to start and stop Oracle OMS

C:\oracle\product\10.2.0\oms10g\dcm\bin>dcmctl stop
Current State for Instance:EnterpriseManager0.asst105157
    Component               Type          Up Status     In Sync Status
================================================================================
1   home                    OC4J          Down          True
2   HTTP_Server             HTTP_Server   Down          True
3   OC4J_EM                 OC4J          Down          True
4   OC4J_EMPROV             OC4J          Down          True

At this point http://asst105157:4889/em/console/ will return an error use following command to start all OMS components

C:\oracle\product\10.2.0\oms10g\dcm\bin>dcmctl start
Current State for Instance:EnterpriseManager0.asst105157
    Component               Type          Up Status     In Sync Status
================================================================================
1   home                    OC4J          Up            True
2   HTTP_Server             HTTP_Server   Up            True
3   OC4J_EM                 OC4J          Up            True
4   OC4J_EMPROV             OC4J          Up            True

For listing OMS components and Status use “dcmctl getstate” else “dcmctl listcomponents”

See