查询字典视图时遇到一些性能问题 . 使用RULE提示时，查询速度非常快 . 可能是什么问题？我在init.ora中有optimizer_mode = RULE . 应用了p26635880_121020_Linux-x86-64.zip补丁的Oracle 12.1.0.2.0
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>