Wednesday 27 June 2012

EMD upload error: Upload was successful but collections currently disabled - disk full

This errors occures while uploading OEM agent becuase your filesystem has breached the level to which agent is set.

We had two solutions here:

1. Clear the filesystem, if not perform second step.
2. Change the value of below parameters in emd.properties file in
<AGENT_HOME>/sysman/config/emd.properties.

Parameter before change:
UploadMaxDiskUsedPct=98
UploadMaxDiskUsedPctFloor=95

Parameter After Change:
UploadMaxDiskUsedPct=99
UploadMaxDiskUsedPctFloor=99
./emctl secure agent password
./emctl upload agent
./emctl clearstate agent

Above step will fix the problem and we can upload the agent now:

./emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully



Reference Metalink Note : 317242.1

Tuesday 19 June 2012

Values populated in DBA_TAB_MODIFICATIONS

Goal :
The goal is to explain why the view DBA_TAB_MODIFICATIONS  does sometimes have no values
even when the parameter STATISTICS_LEVEL  is set to TYPICAL and  the specific schema has been analyzed successful using the package DBMS_STATS.GATHER_SCHEMA_STATS.
In addition all the tables in that schema shows MONITORING=YES in the view dba_tables.


Fix:
The updates to the table *_tab_modifications are related to the volumne of updates for a table.
There is a need of approximatly 10% of datavolumn changes. Just only on single update of the row for example might not lead to fill the *_tab_modifications.

Example :

SQL>  create table test ( num  varchar2(32));
Table created.

Lets insert 100 rows to table test:

SQL>  begin
  2  for i in 1..100 loop
  3  insert into sys.test values (i);
  4  end loop;
  5  commit;
end;  6
  7  /
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
  COUNT(*)
----------
       100

Gather stats for this table:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST');
PL/SQL procedure successfully completed.

SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0

SQL>  select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';

no rows selected

Now lets manually flush the modifications from SGA:

Note: The procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO flushes in-memory monitoring information for all tables in the dictionary.
Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS
views are updated immediately, without waiting for the Oracle database to flush them periodically (per default every 3 hours). This procedure is useful when you need up-to-date information in those views.

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0

Now lets insert 1000 more values and check if it put entry in dba_tab_modifications:

SQL> begin
 for i in 1..1000 loop
  insert into sys.test values (i);
 end loop;
  commit;
 end;
  /
  2    3    4    5    6    7
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
  COUNT(*)
----------
      1100

SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0
no rows selected

SQL>  select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';
no rows selected

SQL>  SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           YES        100
         1            0

SQL>  select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS
------------------------------ ------------------------------ ----------
   UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
SYS                            TEST
                                                                    1000
         0          0 19-JUN-12 NO              0

 

Wednesday 6 June 2012

Playing with Optimizer Mode - Execution Plan



Today i need to talk about optimizer  modes in oracle and in this post i will specially talk about ALL_ROWS and FIRST_ROWS modes.

Possbile values can be:

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
Lets create a test table and index over it with statistics:

SQL> create table opt_test  as select * from dba_objects;
Table created.

SQL> create index opt_testindex  on opt_test(OBJECT_TYPE);
Index created.

SQL> exec dbms_stats.gather_table_stats('sys','opt_test');
PL/SQL procedure successfully completed.

SQL> select count(*) from opt_test;
  COUNT(*)
----------
     62952

SQL> set pagesize 200
SQL> set linesize 200
SQL> select object_type,count(*) from dba_objects group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP               5
INDEX PARTITION            228
SEQUENCE                   314
QUEUE                       21
SCHEDULE                     1
TABLE PARTITION            557
RULE                         1
JAVA DATA                  301
PROCEDURE                  283
OPERATOR                    57
LOB PARTITION                1
WINDOW                       2
DATABASE LINK                1
LOB                        679
PACKAGE                    983
PACKAGE BODY               921
LIBRARY                    154
RULE SET                    11
PROGRAM                     12
TYPE BODY                  174
CONTEXT                      7
JAVA RESOURCE              773
XML SCHEMA                  24
TRIGGER                    435
JOB CLASS                    2
UNDEFINED                    6
DIRECTORY                   18
MATERIALIZED VIEW            5
TABLE                     4163
INDEX                     4524
SYNONYM                  24462
VIEW                      4977
FUNCTION                   342
WINDOW GROUP                 1
JAVA CLASS               16474
INDEXTYPE                   10
JAVA SOURCE                  5
CLUSTER                     10
TYPE                      1990
RESOURCE PLAN                3
EVALUATION CONTEXT           8
JOB                          8
42 rows selected.

You we can say totaly table rows i.e. 63k , synonyms is in maximum quantity almost 25k. Now when we select objects with object_type as
synonyms it should go for full table scan rather than going for index scan than fetch data from the tables.
But things are not simple as it looks we can cheat around oracle using different optimizer mode as shown below:

SQL> set autotrace traceonly exp
SQL>  alter session set optimizer_mode=ALL_ROWS;
Session altered.
SQL> select * from opt_test where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 1427440082
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 23810 |  2185K|   197   (4)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OPT_TEST | 23810 |  2185K|   197   (4)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='SYNONYM')
SQL> alter session set optimizer_mode=FIRST_ROWS;
Session altered.
SQL>  select * from opt_test where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 743984098
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 23810 |  2185K|  1133   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OPT_TEST      | 23810 |  2185K|  1133   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | OPT_TESTINDEX | 23810 |       |    66   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='SYNONYM')
SQL>

As we need first rows quickly optimizer compromises on cost of execution plan and chooses the plan which will deliver first rows fastly.

This does not declare your optimer mode is not good, but it is beneficial in OLTP environment where user need first rows fastly on their on screen , then plan is real good.


Conclusion
Cost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.