Tuesday, June 19, 2012

Enterprise Manager 12c

As most of you know Oracle came out with a new major release to there enterprise management software.  This is a major upgrade with a lot of new integration points and features.  The major thing that changed, everything is all-in-one, meaning that you don't need separate EM's for your Weblogic instances, databases, etc...

We knew coming into it that the product would have a couple bugs that we would have to overcome since it is so new to the world. (12.0 patch set 1).

The Setup

I had our hosting guy (thanks dude) setup a 2GB RAM VM with 2 cores.  Oracle recommended 3GB minimum for the OMS home, but we tried with 2 (and later upped to 4gb).  For the OS we went with SentOS 5.5.  We allocated 20GB of storage, of which now its utilizing 13GB.  For the database we just created a SID with catalog/catproc, nothing else.

The Install

It was fairly straight forward, good old ./runInstaller.  An awesome change from the last release, it does the Weblogic install for you (thank you runInstaller programmer guy/girl!).  That was always such a pain to make sure you got the right version, etc...  You basically say, here are my passwords, here is where I want the base home to be, go...

And about 1 hour later (give or take) you have EM (or you don't in my first 2 cases, see "Bugs").

Once the EM was up and running I went to go setup the S/K scripts to automate the startup of the system.  To my surprise the installer did it as part of the install of the root scripts.  I like that feature.

After poking around a bit on the EM front end I decided it was time to install an agent.

The Agent Install

So I went to go download the agent installer for 12c and noticed a clause saying you can't use it to do an install.  That is strange, than what is the point of having the link for the installer?  After reading through the documentation they no longer support agent installs via the command line, sigh...  So I sucked it up and went to the GUI to setup the agent.  I went to add host and noticed my database server's OS was "not available". After some documentation reading I found you have to go download the agent software using the "self update" feature.  In order to do this you have to supply your Oracle Support creds.  After you supply the creds you refresh the available software download list, then find your OS in the list of available agents.  Click download, and then go home for the day because its going to run a job in the background, eventually.

I picked a development database server as my guinea pig and did the GUI install.  It was actually quite awesome.  The install was smooth and it worked on the 1st try*, weird.

* - see bugs ;)

Adding Targets

After the agent was up and running I added the databases, listeners, etc that lived on that database server.  This process was just about the same as it was in the other releases except getting to the setup was a little different.  You have to click, setup -> add target -> add target manually.  You can also setup recon tasks to automatically search for new targets daily/weekly/etc, then you can just review that list on a regular basis.


I hit a couple snags during the install process:

1) Hard limit during OMS install.  There is a bug during the Weblogic install that crashes the installer.  The hard limit requirement is 4096, we had to set it to 30000 to get the install to pass, then we dropped it back down after.

2) After the first failure I tried cleaning up the database manually, that didn't go very well :)  I suggest taking a snapshot of your datafiles right before you do the install so if it fails just go back to that version (I used flashback, it worked like a boss).

3)  RAM.  They were not joking about requiring 3GB, i would say it might run on 3GB.  We have 4GB up and its consuming 86% of that so....

4) OMS crash during agent install.  I had the OMS completely crash during my first attempt at the agent install.  Not sure what that is about so I tried again, and it didn't occur.  Not sure if it was related or if it was just a coincidence.

5) Collection error incidents not auto clearing.  After a couple days I noticed my queue of incidents was piling up with collection errors.  Those should auto clear once they have a successful upload.  Blarg.  Having the Oracle support integrated with EM was an awesome idea btw.  I clicked the Oracle support tab and found that it was a bug with EM 12c patch set 1. ha.  Here is the patch id: 13692486.  Note its only an issue with patch set 1.

Cluster Configuration 

If you have Clusterware installed I suggest doing the following:  Install the agent on all nodes in the cluster.  Then from there add a cluster target to identify where the cluster exists.  Then do auto-discovery for the remainder targets.  This way your database instances in the cluster will fall under the cluster and not independent nodes.  In our development environment our clustered databases still showed up as single instances for some weird reason, not sure what that is about, but I'm still in search for a solution.


All the database features that you know and love are still there, and some are improved.  The speed of the system seems better (that might be hardware related tho) when compared to EM 11g.  I really like a the landing pages that they developed.  Its super easy to setup multiple groups and users.

I have yet to install a FMW server on it, but I will have an update soon on those features.

They also added a bunch of dashboards that would be super neat if you have a couple computers setup for such an activity (we do).  It shows basic "UP/DOWN" status, if things are in normal range (I/O, CPU, etc...).  That feature will be really nice for our operations department.


Get it, its awesome!  If you don't have EM at all I suggest getting it.  It makes life so much better (not necessarily easier).  If you are on EM 10g, I feel bad for you son (you got 99 problems and ... thats about it) .  If you are on EM 11g and looking at 12c saying "is it stable enough?"  I would say, yeah.  I also have only had it installed for about a week so take that with a gran of salt.

Thursday, May 10, 2012


Here is a nice dbms_scheduler feature I found the other day.
   old_job                IN VARCHAR2,
   new_job                IN VARCHAR2);
  2     DBMS_SCHEDULER.create_job (
  3        job_name          => 'JOB_NAME',
  4        job_type          => 'PLSQL_BLOCK',
  5        job_action        => 'begin NULL; end;',
  6        repeat_interval   => 'freq=daily;byhour=6,7,8,9,10,11,12,13,14,15,16,17;byminute=0,30;bysecond=0',
  7        enabled           => FALSE
  8     );
  9  END;
/ 10

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.enable('JOB_NAME')

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.copy_job('JOB_NAME','job2')

PL/SQL procedure successfully completed.

OWNER           JOB_NAME                       JOB_ACTION                               REPEAT_INTERVAL                ENABL STATE
--------------- ------------------------------ ---------------------------------------- ------------------------------ ----- ---------------
OPS$fjkds JOB2                           begin NULL; end;                         freq=daily;byhour=6,7,8,9,10,1 FALSE DISABLED

OPS$ JOB_NAME                       begin NULL; end;                         freq=daily;byhour=6,7,8,9,10,1 TRUE  SCHEDULED

Neat things to note here.  The job2 is disabled, excellent.  This will come in handy, this one thinks.

Tuesday, May 8, 2012

Histograms for 500 Alex

Histograms are a neat little gem in the world of Oracle.  They are a great tool that the Optimizer uses with Oracle statistics.

Exert from PSOUG:
Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms.
The type of histogram is stored in the HISTOGRAM column of the *TAB_COL_STATISTICS views (USER and DBA). This column can have values of HEIGHT BALANCED, FREQUENCY, or NONE.In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall. In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. Frequency histograms can be viewed using the *TAB_HISTOGRAMS views.
There are a handful of views to take a look at histograms as specified above.  Here are some queries I found useful:

Checking to see if a column has a histogram:
SELECT OWNER, TABLE_NAME, COLUMN_NAME, HISTOGRAM, LAST_ANALYZED  FROM all_tab_col_statistics WHERE OWNER LIKE upper('%&owner%')   AND TABLE_NAME LIKE upper('%&table_name%')   AND COLUMN_NAME LIKE UPPER('%&column_name%') ORDER BY 1,2,3

Viewing the frequency histogram data:

  METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id');

SELECT column_name, num_distinct, num_buckets, histogram 
 WHERE table_name = 'INVENTORIES' AND column_name = 'WAREHOUSE_ID';

------------------------------ ------------ ----------- ---------------
WAREHOUSE_ID                              9           9 FREQUENCY

SELECT endpoint_number, endpoint_value 
 WHERE table_name = 'INVENTORIES' and column_name = 'WAREHOUSE_ID'
  ORDER BY endpoint_number;

--------------- --------------
             36              1
            213              2
            261              3
            370              4
            484              5
            692              6
            798              7
            984              8

  METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand');

SELECT column_name, num_distinct, num_buckets, histogram 
 WHERE table_name = 'INVENTORIES' AND column_name = 'QUANTITY_ON_HAND';

------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND                        237          10 HEIGHT BALANCED

SELECT endpoint_number, endpoint_value 
 WHERE table_name = 'INVENTORIES' and column_name = 'QUANTITY_ON_HAND'
  ORDER BY endpoint_number;

--------------- --------------
              0              0
              1             27
              2             42
              3             57
              4             74
              5             98
              6            123
              7            149
              8            175
              9            202 

Basically what the histogram tells you is how frequent a given value is.  Based on its findings it will figure out if it should use the index that you have on the column or if it would be better to do the FTS.

How a Histogram could mess the optimizer up:

Histograms work best with literal variables (select * from test_table where some_ind = 'Y';).  If you try using bind variables (select * from test_table where  some_ind  = :bindx;) Oracle should bind peak to check what they are, pick the plan based on those binds.  What could be bad is if you have a table with a indicator column that 99% is Y and 1% N.  If the bind peak happens on the N its more likely to use an index because that makes sense.  However the next time that is ran and is not hard parsed it will use the same plan even if the bind is Y, where a full table scan makes more sense there.

The default setting for anything 10g is setting histogram collection to auto.  This is getting better with each release but they are still working on it.  You can always collect a histogram on specific columns by hand if you want.

I could go on forever about histograms, but I think this is a good summary.

Ask Tom Article
PSOUG Article
Random Blog that has good facts
- Oracle Documentation
- Rwave solution presentation