Friday, November 15, 2013

How to remove OEM agent in the release 3 of the Oracle Enterprise Manager.



OEM 12c R3 has a very useful Perl script which allows removing an installed OEM agent in the host. The process is quite straightforward just by invoking the AgentDeinstall.pl script located under:

$AGENT_HOME/sysman/install/AgentDeinstall.pl

[oracle@ol6-112 12.1.0.3.0]$ /u01/app/oracle/agent12cr3/core/12.1.0.3.0/perl/bin/perl /u01/app/oracle/agent12cr3/core/12.1.0.3.0/sysman/install/AgentDeinstall.pl -agentHome $AGENT_HOME


 Agent Oracle Home: /u01/app/oracle/agent12cr3/core/12.1.0.3.0


NOTE: The agent base directory: /u01/app/oracle/agent12cr3 will be removed after successful deinstallation of agent home.


 DetachHome Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -detachHome -force -depHomesOnly -silent ORACLE_HOME=/u01/app/oracle/agent12cr3/core/12.1.0.3.0 -waitForCompletion -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
'DetachHome' was successful.
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
The Oracle home '/u01/app/oracle/agent12cr3/sbin' could not be updated as it does not exist.


Deinstall Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/u01/app/oracle/agent12cr3/core/12.1.0.3.0}" -waitForCompletion -removeAllFiles -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-11-11_02-22-46PM. Please wait ...Oracle Universal Installer, Version 11.1.0.11.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Monday, November 11, 2013 2:23:00 PM CST)
Configuration assistant "Agent Deinstall Assistant" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Monday, November 11, 2013 2:23:37 PM CST)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2013-11-11_02-22-46PM.log' for more details.

Do you want us to delete the old oracle home [yes/no] :y


Hope this help you when need to remove an OEM agent installation.

Thanks,
Alfred

Sunday, September 8, 2013

Oracle Enterprise Manager 12c Release 3 New Features

Today I will talk about some new features in the release 3 of the Oracle Enterprise Manager.

  • All Metrics Chart Enhancements

This new feature show more data in the metrics page, like minimum and maximum values, it will highlight periods when the agent was unreachable or target was down, it will also show the period when the metric was in warning or critical severity.



This really helps DBAs to better understand the behavior of the target including the metric data.

  • Metrics Schedule Enhancements

Metrics now support additional collection schedule like weekly on specified days of the week and monthly on specified days of the month.





  • Service Target Dashboard

Provides a quickly out-of-box overview of the health of services, this is really useful when looking for a quick view of what service is having problem in the environment.




  • Performance Diagnostics Enhancements

Even when this is not a new feature of the OEM 12c R3, ASH Analytics is really cool. It allows DBAs to diagnose performance exactly when DBA desire; it has customized selection of the desired point in time when issues appeared.


Well, these are some new and cool features of the Oracle Enterprise Manager 12c Release 3. Hope you can enjoy this features as much as me.

Thanks,

Alfredo

Thursday, August 29, 2013

Understanding Oracle Histograms

Histograms are a feature of the Cost Based Optimizer (CBO) that helps to know the data distribution in a column. Histograms should be created in columns with highly skew data and are included in the filter clause (WHERE) of a SQL statement.
Histograms play a very important role in the creation of the execution plan and we must be careful in the method used to gather statistics in the DB objects.
Let me show you how histograms work, for this test I have created a table T_HIST with some data in it:

 SQL> CREATE TABLE T_HIST AS SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,mod(round(dbms_random.value(1,100000)),254) c3, 'C' C4 from dual connect by level < 529101;

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, 'P' from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, 'R' from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, 'T' from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
  2  mod(round(dbms_random.value(1,100000)),254) c3, 'A' from dual connect by level < 500001;

500000 rows created.

SQL> COMMIT;

Commit complete.

SQL>

We will now create an index in the column C4:
SQL> CREATE INDEX T_HIST_IDX1 ON T_HIST(C4);

Index created.

SQL>

Let us see how the data is distributed inside column C4:

SQL> SELECT C4,COUNT(*) FROM T_HIST GROUP BY C4 ORDER BY C4;

C   COUNT(*)
- ----------
A     500000
C     529100
P        300
R        300
T        300

You can see that we have 5 distinct values in column C4 (A, C, P, R & T) and the number of values that correspond to each. Let us take value ‘R’ as example, if we query the table the expected execution plan is to use the Index created, because we need only 300 rows of the 1030000 rows total. Let’s query:
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = 'R'
  4  order by
  5      C1
  6  ;

Explained.

SQL>
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);  2    3    4    5    6
Explained.

SQL> SQL> SQL> SQL>   2
Plan hash value: 3146893614

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    30 |  1260 |   387   (1)| 00:00:05 |
|   1 |  SORT ORDER BY               |             |    30 |  1260 |   387   (1)| 00:00:05 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |    30 |  1260 |   386   (0)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |  3607 |       |   375   (0)| 00:00:05 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C4"='R')

Note
-----
   - dynamic sampling used for this statement (level=2)

What happened? Execution plan is saying that our query will return only 30 rows! obviously this is totally wrong; also look at the estimated elapsed time (5 sec). Ok, we are being unfair to the CBO as we are not giving all the information required, so let’s gather statistics but without histograms:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ALFRED', tabname=>'T_HIST', estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = 'R'
  4  order by
  5      C1
  6  ;

Explained.

SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2911980760

-------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   206K|  3017K|       |  1832   (2)| 00:00:22 |
|   1 |  SORT ORDER BY     |        |   206K|  3017K|  5672K|  1832   (2)| 00:00:22 |
|*  2 |   TABLE ACCESS FULL| T_HIST |   206K|  3017K|       |   774   (2)| 00:00:10 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C4"='R')

What happened? Now the CBO decided to choose a Full Table Scan, it also states that will return 206k rows and the estimated elapsed time is 22 sec. That’s because we used AUTO_SAMPLE_SIZE so Oracle we decide how many rows to sample and for sure values of ‘A’ & ‘C’ represent more than 99% of the samples. But how can we tell the CBO that our data distribution is skewed? that’s where histograms enter into play. Let’s now gather statistics using histogram for column C4:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ALFRED', tabname=>'T_HIST', estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'for columns C4 size 254');

PL/SQL procedure successfully completed.

SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = 'R'
  4  order by
  5      C1
  6  ;

Explained.

SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3146893614

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   941 | 14115 |     8  (13)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |   941 | 14115 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |   941 | 14115 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |   941 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C4"='R')

Now is using the Index again, states that will return 941 rows but look how the cost decreased and the estimated elapsed time to 1 sec! Even if the CBO wasn’t able to calculate the real number of rows being returned the cost was fixed, in order to have the most accurate execution plan we must increase the sample size. Let us make a test with 100% of the samples:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ALFRED', tabname=>'T_HIST', estimate_percent=>100,method_opt=>'for columns C4 size 254');

PL/SQL procedure successfully completed.

SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = 'R'
  4  order by
  5      C1
  6  ;

Explained.

SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3146893614

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   300 |  4500 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |   300 |  4500 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |   300 |  4500 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |   300 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C4"='R')

Now is totally accurate! Right? The execution plan generated by the CBO is using the Index, the estimated rows are 300 which is correct and the cost is even better. This is an example of a very small table, but is really an option to gather statistics with estimate of 100% to a big table?, I don’t think so; that’s why AUTO_SAMPLE_SIZE can be a good balance between sample size and accuracy.
Column C4 now is having a frequency histogram because is having less than 254 distinct values (NDV), only 5; that means that frequency histogram has the restriction of 254 buckets (254 NDV’s), seriously?. Let test it, we will gather statistics to all columns of the table using AUTO_SAMPLE_SIZE and AUTO for histograms:
SQL> exec dbms_stats.gather_table_stats(ownname=>'ALFRED',tabname=>'T_HIST', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = 'T_HIST';
C1                                   529100  .00000189           1 NONE
C2                                      254 .003937008           1 NONE
C3                                      254 4.8544E-07         254 FREQUENCY
C4                                        5 4.8544E-07           5 FREQUENCY

What happened?, well column C3 was also candidate for a histogram because it’s density (skewed) but look at the number of buckets is 254; that means that we are on the edge of the limit in order to have a histogram in this column. Don’t believe me? let’s make a quick test, we will insert a new distinct value into this column, gather statistics again and see what happen:
SQL> insert into T_HIST values (100001,42, 99999999999,'B');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ALFRED',tabname=>'T_HIST', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = 'T_HIST';
C1                                   529100  .00000189           1 NONE
C2                                      254 .003937008           1 NONE
C3                                      255 .003921569           1 NONE
C4                                        6 4.8544E-07           6 FREQUENCY

Can you see how the number of distinct values (NVD) changed? now we can’t have a histogram in that column, really?; well we can force it but look how low is our density. Let’s try to force it and specify to the gather stats that we want all columns with maximum of 254 buckets:
SQL> exec dbms_stats.gather_table_stats(ownname=>'ALFRED',tabname=>'T_HIST', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = 'T_HIST';
C1                                   529100 1.9186E-06         254 HEIGHT BALANCED
C2                                      254 4.8544E-07         254 FREQUENCY
C3                                      255 .003935563         254 HEIGHT BALANCED
C4                                        6 4.8544E-07           6 FREQUENCY

Look how it works? it can create frequency histogram on columns with less than 254 NDV’s and height balanced histogram in columns with more than 254 NDV’s. Also note the first example, we used size of 1 (1 bucket) that means no histogram because all values are inside only one bucket.
This is a basic example of how histograms work, honestly I was having a lot of doubts before making this test and some of them are clarified reading Jonathan Lewis new post in allthinngsoracle.com, however there’s a lot of path to walk through and even difficult as 12c have 2 more types of histograms to help CBO.

Thanks,
Alfredo