OEM Agent : The agentTZRegion value not in Agreement

Due to DST  changes some days back we see couple of OEM Agents failed to start with below mentioned errors:

oracle@machine[TEST01]> ./emctl start agent
Starting agent ......... failed.
The agentTZRegion value in /orcl/app/oracle/agent11g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(<agent_name>, <new_tz_rgn>) to get the value propagated to repository.
Consult the log files in: /orcl/app/oracle/agent11g/sysman/log

OS level time as changed but it's not changed in Oracle Agent binaries. We have manaully fix it using below menthod:

1. Set the Correct Env variable to TZ parameter

oracle@machine[TEST01]> echo $TZ
Europe/Moscow
export TZ=Etc/GMT-4

2. Reset the agent time zone using emctl resetTZ agent

oracle@machine[TEST01]> ./emctl resetTZ agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Updating /orcl/app/oracle/agent11g/sysman/config/emd.properties...
Successfully updated /orcl/app/oracle/agent11g/sysman/config/emd.properties.

Login as the em repository user and run the  script: ( OEM Grid )
exec mgmt_target.set_agent_tzrgn('machinename2:1830','Etc/GMT-4')
and commit the changes

This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('machinename:1830','Etc/GMT-4')
SQL> commit

3. Agent started:

oracle@machine[TEST01]> ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ..................... started.

Reference Metalink ID note:
DST Compliance for Grid Control OMS, Repository and Agents: Checks and Updates [ID 457792.1]

ORA-12801: error signaled in parallel query server P001 ORA-01722: invalid number

Today I came accross with above two errors. These are two sets of errors

1. Parallel Query Failing
2. Invalid Comparision Operation.

Query Failing:

SELECT COUNT(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000;

ORA-12801: error signaled in parallel query server P001

ORA-01722: invalid number

When i check degree on this table it was set to 1 so no problem found there but degree with above table indexes was set to 4. I changed the degree to 1 to sort first issue as below:

SQL> SQL> select owner,table_name,degree from dba_tables where table_name in ('SALES_MASTER_HISTORY');

OWNER                          TABLE_NAME                     DEGREE
------------------------------ ------------------------------ ----------
CAP                          SALES_MASTER_HISTORY                   1

SQL> select index_name,degree from dba_indexes where table_name in ('SALES_MASTER_HISTORY');

INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
IDX_TMH_NETTEDBOOKINGREF       4
IDX_VALUEDATE                  4
PK_TRADES_MASTER_HISTORY       4
IDX_TMH_CORESYSREF             4
IDX_TMH_TRUNC_BOOKEDDATE_IDX   4
IDX_TMH_BRANCH_CODE            4
IDX_TMH_CUSTOMER_NUMBER        4
IDX_TMH_BROKERCODE             4
IDX_BOOKEDDATE                 4
IDX_TMH_CONTRACTNUMBER         4
IDX_TMH_CUSTOMER               4

11 rows selected.

SQL> alter index CAP.IDX_BOOKEDDATE  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_BRANCH_CODE  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_BROKERCODE  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_CONTRACTNUMBER  parallel 1;

Index altered.

SQL>  alter index CAP.IDX_TMH_CUSTOMER_NUMBER  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_NETTEDBOOKINGREF  parallel 1;

Index altered.

SQL>  alter index CAP.IDX_VALUEDATE  parallel 1;

Index altered.

SQL> alter index CAP.PK_TRADES_MASTER_HISTORY  parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_TRUNC_BOOKEDDATE_IDX  parallel 1;

Index altered.

SQL> select owner,index_name,degree from dba_indexes where table_name in ('SALES_MASTER_HISTORY') order by INDEX_NAME;

OWNER                          INDEX_NAME                     DEGREE
------------------------------ ------------------------------ ----------------------------------------
CAPFX                          IDX_BOOKEDDATE                 1
CAPFX                          IDX_TMH_BRANCH_CODE            1
CAPFX                          IDX_TMH_BROKERCODE             1
CAPFX                          IDX_TMH_CONTRACTNUMBER         1
CAPFX                          IDX_TMH_CORESYSREF             1
CAPFX                          IDX_TMH_CUSTOMER               1
CAPFX                          IDX_TMH_CUSTOMER_NUMBER        1
CAPFX                          IDX_TMH_NETTEDBOOKINGREF       1
CAPFX                          IDX_TMH_TRUNC_BOOKEDDATE_IDX   1
CAPFX                          IDX_VALUEDATE                  1
CAPFX                          PK_TRADES_MASTER_HISTORY       1

After that when i execute query it was giving just :

SQL> select count(*)  FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000;
select count(*)  FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000
                                                  *
ERROR at line 1:
ORA-01722: invalid number

This is not really a database problem, here problem is TREASURYNUMBER is char(8) and when we compare it with number it causing implicit datatype conversion which causes error. This can be resolved by using 2000 value in quotes as below:

SQL> SELECT COUNT(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < '20000';

  COUNT(*)
----------
   109090909

So, this is simple solution but beneficial to  know.

SQL Profile - Play with Execution plan

Although i don't have too much experience working with outlines, but these days i am working significantly with SQL Profile. My Favourite blogger Kerry osborne has written a wonderfull blog on
this topic which gives me very clear idea of sql profile.

Outlines basically locks the profile , but sql profile plays with emperical value of the sql plan which normaly known as fudge factore. It's give the flexibility to optimizter to look for alternate plan.
But we can say sql profile advantages will fade with time.

Hints used in sql profile, these hinsts are very likely documents so wanted to give as much information as possible:

1. Setting Optimer Mode : ALL_ROWS

2. Disable hints present in sql : IGNORE_OPTIM_EMBEDDED_HINTS

3.Setting OPTIMIZER_FEATURES_ENABLE to it's default Value i.e active all available feature :OPTIMIZER_FEATURES_ENABLE (default)

4.Adjusting number of rows returned from the table:
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=10)
 Eg : 10 times as many rows are expected to return

5. Adjusting number of rows returned from index scan:
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=.4)
Eg: 4 times fewer rows will be returned using index skip scan.

6. Table, index and column statistics adusted using below hints:
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)
Below are some scripts to find out hints from a sql profile and awr snapshots ( scripts are borrowed from kerry blogs )

SQL> set echo on
SQL> @sql_profile_hints
set lines 155
col hint for a150
select attr_val hint
from dba_sql_profiles p, sqlprof$attr h
where p.signature = h.signature
and name like ('&profile_name')
order by attr#
 /
Enter value for profile_name: PROFILE_0qa98gcnnza7h

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=0.0536172171)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=4)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL1", scale, length=6 distinct=1234 nulls=0 min=1000000014 max=1026369632)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL2", scale, length=12 distinct=2 nulls=0)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)

SQL> @sql_hints_awr
 select
 extractvalue(value(d), '/hint') as outline_hints
 from
 xmltable('/*/outline_data/hint'
 passing (
 select
 xmltype(other_xml) as xmlval
 from
 dba_hist_sql_plan
where
sql_id = '&sql_id'
and plan_hash_value = &plan_hash_value
and other_xml is not null
)
) d;
Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value: 568322376

OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")

Scripts to create sql profile ( Kerry osborne blog reference ) :

create_sql_profile.sql – uses cursor from the shared pool
create_sql_profile_awr.sql – uses AWR tables
sql_profile_hints.sql – shows the hints in a SQL Profile for 10g


Basic Commands:
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
DBMS_SQLTUNE.ALTER_SQL_PROFILE
DBMS_SQLTUNE.DROP_SQL_PROFILE


set verify off exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');




set verify off
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'DISABLED');

Reference Links :

http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/
 

Table Fragmentation - Oracle

Table fragmentation –

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.

The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.
“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables; -- Create a table
Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','TEST); -- Gather Stats on this table


SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- The number of blocks used/free

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 1680

SQL> delete from test where owner='SYS'; --- Im deleting almost half the number of rows.
764 rows deleted.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','TEST); -- Gather Stats on this table again


SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- No difference in blocks usage

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 916


Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.

Reasons to reorganization

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.
Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to reorganize?

Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table

a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).

Oracle 10g provides us a new way of reorganizing the data.

Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.
Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;
Table altered.

There are 2 ways of using this command.

1. Break in two parts: In first part rearrange rows and in second part reset the HWM.

Part 1: Rearrange (All DML's can happen during this time)

SQL> alter table test shrink space compact;
Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table sa shrink space;
Table altered.

2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.

Few advantages over the conventional methods

1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.

Conclusion
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

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

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

 

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.