1

Topic: Problems with productivity 12

I have some performance issue when querying on dictionary views. When using RULE hint the query is very fast. What can be the problem? I have optimizer_mode=RULE in init.ora. Oracle 12.1.0.2.0 with p26635880_121020_Linux-x86-64.zip patch applied

SQL> SET TIMING ON;
SQL>
SQL> select / * + RULE */
2 *
3 from
4 v$locked_object lo;
5 all_objects ao;
6 v$lock ll
7 where
8 lo.object_id = ao.object_id
9 and lo.xidsqn = ll.id2
10 and ao.data_object_id = dbms_rowid.rowid_object (' AAAgW6AACAAM7UiAAA ');
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME SHARING EDITIONABLE ORACLE_MAINTAINED ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ ----------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------- ----------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- -------------------------------------------------------------------------------- ------------- ----------- ----------------- ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Executed in 0,766 seconds
SQL>
SQL> select
2 *
3 from
4 v$locked_object lo;
5 all_objects ao;
6 v$lock ll
7 where
8 lo.object_id = ao.object_id
9 and lo.xidsqn = ll.id2
10 and ao.data_object_id = dbms_rowid.rowid_object (' AAAgW6AACAAM7UiAAA ');
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME SHARING EDITIONABLE ORACLE_MAINTAINED ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ ----------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------- ----------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- -------------------------------------------------------------------------------- ------------- ----------- ----------------- ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Executed in 83,39 seconds
SQL>
optimizer_adaptive_plans TRUE
optimizer_adaptive_statistics FALSE
db_file_multiblock_read_count 128
optimizer_features_enable 12.1.0.2
optimizer_mode RULE
optimizer_index_cost_adj 100
pga_aggregate_target 13474201600
workarea_size_policy AUTO
optimizer_dynamic_sampling 0
_optimizer_aggr_groupby_elim FALSE
optimizer_adaptive_reporting_only TRUE
optimizer_adaptive_features TRUE
_optimizer_reduce_groupby_key FALSE
13 rows selected
Executed in 0,156 seconds
SQL>

2

Re: Problems with productivity 12

RULE from the list of values OPTIMIZER_MODE has been eliminated still in 11G. Read to dock. 12C it  but (as far as I understand) simply ignores. It is made for simplification of passage on CBO. And here  RULE lives and is well. The system statistics collected?
SY.

3

Re: Problems with productivity 12

to SY;
Yes collected

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_dictionary_stats;
EXEC DBMS_STATS.gather_system_stats;

4

Re: Problems with productivity 12

I as a result hammered

5

Re: Problems with productivity 12

Thanks delivered on ALL_ROWS by default where it will be necessary to use RULE

6

Re: Problems with productivity 12

gda;
Not your case?
Query Slow After Migration From Earlier Version of 8i to 12c Without Using Rule Hint (Doc ID 2303332.1)