客服微信
2.确认process id 并开启trace
SELECT p.tracefile, p.SPID--6533
FROM v$session s, v$process p
WHERE p.addr = s.paddr
AND s.sid = userenv('sid');
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
3.不执行查询，只是用代码助手弹框并选择
select * from flv.LOOKUP_TYPE flv.DESCRIPTION select * from fnd_lookup_values f where f.description= and f.start_date_active= select * from fnd_lookup_values_vl flv where flv.ROW_ID= and flv.ATTRIBUTE_CATEGORY= and flv.ATTRIBUTE1= and flv.ATTRIBUTE2 and flv.ATTRIBUTE3 and flv.DESCRIPTION select * from dba_objects d where d.OBJECT_NAME= select * from fnd_lookup_values_vl select * from f.lookup_type f.attribute134.关闭追踪
ALTER SESSION SET EVENTS '10046 trace name context off';
6.本地trace信息（由于拿不到自己开发环境的trace,所以只能试试看，拿本地的日志去优化开发环境，各位如果使用优化脚本效果不好，建议用自己的6分析，可能会发现新的性能问题）
6.1select column_name, nullable, data_type, data_type_mod, data_type_owner, data_length, data_precision, data_scale, char_used, char_length from sys.all_tab_columns where owner = 'SYS' and table_name = 'V_$SESSION' order by column_id Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 6 0.00 0.00 SQL*Net message from client 6 0.01 0.06 direct path read 154 0.05 0.38
select comments from sys.all_tab_comments
where owner = :object_owner
and table_name = :object_name
and origin_con_id in (1, sys_context('userenv', 'con_id'))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 5.83 9.33
db file sequential read 2 0.00 0.00
select column_name, nullable, data_type, data_type_mod, data_type_owner, data_length, data_precision, data_scale, char_used, char_length from sys.all_tab_columns where owner = 'SYS' and table_name = 'DBA_OBJECTS' order by column_id
select column_name, nullable, data_type, data_type_mod, data_type_owner, data_length, data_precision, data_scale, char_used, char_length from sys.all_tab_columns where owner = 'SYS' and table_name = 'DBA_COL_COMMENTS' order by column_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.63 0.63 0 8 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 46 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.63 0.63 0 54 0 5 Misses in library cache during parse: 1
--------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 257 | 33 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 2 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 4 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 11 | NESTED LOOPS | | 1 | 31 | 3 | 00:00:01 | | * 12 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 2 | 00:00:01 | | 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 23 | 1 | 00:00:01 | | * 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | 00:00:01 | | 15 | SORT ORDER BY | | 1 | 257 | 33 | 00:00:01 | | * 16 | FILTER | | | | | | | 17 | NESTED LOOPS OUTER | | 1 | 257 | 14 | 00:00:01 | | 18 | NESTED LOOPS | | 1 | 222 | 13 | 00:00:01 | | 19 | NESTED LOOPS OUTER | | 1 | 209 | 12 | 00:00:01 | | 20 | NESTED LOOPS OUTER | | 1 | 199 | 11 | 00:00:01 | | 21 | NESTED LOOPS OUTER | | 1 | 155 | 7 | 00:00:01 | | 22 | NESTED LOOPS OUTER | | 1 | 127 | 6 | 00:00:01 | | 23 | NESTED LOOPS | | 1 | 122 | 5 | 00:00:01 | | 24 | NESTED LOOPS | | 1 | 74 | 4 | 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 18 | 1 | 00:00:01 | | * 26 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 | 00:00:01 | | * 27 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 56 | 3 | 00:00:01 | | * 28 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 | 00:00:01 | | * 29 | TABLE ACCESS CLUSTER | COL$ | 1 | 48 | 1 | 00:00:01 | | * 30 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 | 00:00:01 | | 31 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 | 00:00:01 | | * 32 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 | 00:00:01 | | * 33 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 28 | 1 | 00:00:01 | | * 34 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 44 | 4 | 00:00:01 | | * 35 | INDEX RANGE SCAN | I_OBJ3 | 21 | | 1 | 00:00:01 | | * 36 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 10 | 1 | 00:00:01 | | * 37 | INDEX RANGE SCAN | I_USER2 | 1 | 13 | 1 | 00:00:01 | | 38 | TABLE ACCESS CLUSTER | USER$ | 1 | 35 | 1 | 00:00:01 | | * 39 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | 00:00:01 | | * 40 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 | 00:00:01 | | * 41 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 | 00:00:01 | | 42 | NESTED LOOPS SEMI | | 1 | 12 | 2 | 00:00:01 | | 43 | FIXED TABLE FULL | X$KZSRO | 2 | 6 | 0 | 00:00:01 | | * 44 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 | 00:00:01 | | * 45 | FIXED TABLE FULL | X$KZSPR | 1 | 7 | 0 | 00:00:01 | | * 46 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 7 | 2 | 00:00:01 | | * 47 | INDEX RANGE SCAN | I_USER_EDITIONING | 9 | | 1 | 00:00:01 | | * 48 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 7 | 2 | 00:00:01 | | * 49 | INDEX RANGE SCAN | I_USER_EDITIONING | 9 | | 1 | 00:00:01 | | 50 | NESTED LOOPS SEMI | | 1 | 18 | 3 | 00:00:01 | | * 51 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 2 | 00:00:01 | | * 52 | INDEX RANGE SCAN | I_USER2 | 3 | 27 | 1 | 00:00:01 | --------------------------------------------------------------------------------------------------------------- 当前的开发环境（来源于共享池缓存，非trace） ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1635 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 11 | NESTED LOOPS | | 1 | 160 | 4 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_OBJ1 | 1 | 10 | 3 (0)| 00:00:01 | | 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 150 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| | | 15 | SORT ORDER BY | | 1 | 357 | 1635 (1)| 00:00:01 | |* 16 | FILTER | | | | | | | 17 | NESTED LOOPS OUTER | | 63 | 22491 | 1610 (1)| 00:00:01 | |* 18 | HASH JOIN OUTER | | 63 | 21861 | 1483 (1)| 00:00:01 | | 19 | NESTED LOOPS OUTER | | 63 | 12411 | 1476 (1)| 00:00:01 | | 20 | NESTED LOOPS OUTER | | 63 | 10395 | 278 (0)| 00:00:01 | | 21 | NESTED LOOPS | | 63 | 8568 | 277 (0)| 00:00:01 | | 22 | NESTED LOOPS OUTER | | 1 | 84 | 275 (0)| 00:00:01 | |* 23 | HASH JOIN | | 1 | 78 | 273 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 5 | 215 | 269 (0)| 00:00:01 | |* 25 | INDEX SKIP SCAN | I_OBJ5 | 5 | | 266 (0)| 00:00:01 | | 26 | NESTED LOOPS | | 579 | 20265 | 4 (0)| 00:00:01 | | 27 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 12 | 2 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 (0)| 00:00:01 | | 29 | INDEX FAST FULL SCAN | I_USER2 | 579 | 13317 | 2 (0)| 00:00:01 | | 30 | TABLE ACCESS CLUSTER | TAB$ | 1 | 6 | 2 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | |* 32 | TABLE ACCESS CLUSTER | COL$ | 68 | 3536 | 2 (0)| 00:00:01 | |* 33 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | | 34 | TABLE ACCESS BY INDEX ROWID | COLTYPE$ | 1 | 29 | 1 (0)| 00:00:01 | |* 35 | INDEX UNIQUE SCAN | I_COLTYPE2 | 1 | | 0 (0)| | |* 36 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 32 | 19 (0)| 00:00:01 | |* 37 | INDEX RANGE SCAN | I_OBJ3 | 85 | | 1 (0)| 00:00:01 | | 38 | TABLE ACCESS FULL | USER$ | 579 | 86850 | 7 (0)| 00:00:01 | |* 39 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 10 | 2 (0)| 00:00:01 | |* 40 | TABLE ACCESS CLUSTER | TAB$ | 1 | 14 | 3 (0)| 00:00:01 | |* 41 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | |* 42 | HASH JOIN SEMI | | 1 | 23 | 3 (0)| 00:00:01 | | 43 | FIXED TABLE FULL | X$KZSRO | 2 | 26 | 0 (0)| | |* 44 | INDEX RANGE SCAN | I_OBJAUTH1 | 6 | 60 | 3 (0)| 00:00:01 | |* 45 | FIXED TABLE FULL | X$KZSPR | 1 | 20 | 0 (0)| | |* 46 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 7 | 2 (0)| 00:00:01 | | 47 | NESTED LOOPS SEMI | | 1 | 31 | 4 (0)| 00:00:01 | |* 48 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | 3 (0)| 00:00:01 | |* 49 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | |* 50 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 7 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------
SELECT d2.object_name, d2.object_type
FROM dba_objects d1, dba_objects d2
WHERE d1.object_name = 'USER$'
AND d1.data_object_id = d2.data_object_id
SELECT dtc.column_name, --列名
dtc.num_distinct, --相异基数
dtc.num_nulls, --空值
dtc.num_buckets, --直方图桶个数
dtc.last_analyzed, --上次分析时间
dtc.histogram --直方图类型
FROM dba_tab_col_statistics dtc
WHERE dtc.table_name = 'OBJ$'
AND DTC.COLUMN_NAME in ('NAME' ,'OBJ#');
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM
1 NAME 319520 0 254 2023/1/23 22:14:45 HYBRID
2 OBJ# 497879 0 254 2023/1/23 22:14:45 HYBRID
其中HYBRID 是混合直方图，查看其分布如下：
SELECT column_name,
endpoint_value,
endpoint_number
FROM dba_tab_histograms
WHERE table_name = 'OBJ$'
AND column_name in ('NAME' ,'OBJ#')
ORDER BY column_name,endpoint_value
桶数默认254,不同的值有32万，所以采用了混合直方图，先不讨论直方图的问题，（混合直方图经过测试发现没有其它直方图效果好）alter system flush shared_pool;
SELECT column_name
,nullable
,data_type
,data_type_mod
,data_type_owner
,data_length
,data_precision
,data_scale
,char_used
,char_length
FROM sys.all_tab_columns
WHERE owner = 'SYS'
AND table_name = 'V_$SESSION'
ORDER BY column_id;
SELECT column_name
,nullable
,data_type
,data_type_mod
,data_type_owner
,data_length
,data_precision
,data_scale
,char_used
,char_length
FROM sys.all_tab_columns
WHERE owner = 'SYS'
AND table_name = 'V$SESSION'
ORDER BY column_id
select s.sql_text,s.sql_id,s.child_number,s.is_bind_sensitive,s.is_bind_aware,s.bind_data from v$sql s where s.sql_text like '%all_tab_columns%' and s.sql_text like '%''SYS''%' and s.sql_text not like '%v$sql%';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE BIND_DATA cqzr43zn3w0ma 0 N N --代码助手产生 cqzr43zn3w0ma 1 N N --代码助手产生 cbttrjzr90dra 0 N N --对应'V_$SESSION' 8v3bp2h9c93pg 0 N N --对应'V$SESSION'
alter system flush shared_pool; alter session set "_optimizer_adaptive_cursor_sharing"=false alter session set cursor_sharing='FORCE';--此命令放入AfterConnect.sql 则可以每次开启新窗口可以自动执行，当然你也可以把会话语言选项放入
select * from v$parameter p where p.name='cursor_sharing' and p.default_value and p.display_value and p.description select * from v$session s where s.blocking_session_status= and s.blocking_session_status and s.wait_time_micro and s.blocking_instance
select s.sql_text,s.sql_id,s.child_number,s.is_bind_sensitive,s.is_bind_aware,s.bind_data from v$sql s where s.sql_text like '%all_tab_columns%' ;
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE 8fqtv1mu1y2fv 0 Y N 8fqtv1mu1y2fv 1 Y N
alter session set "_optimizer_extended_cursor_sharing_rel"=none ; alter session set "_optimizer_extended_cursor_sharing"=none ; alter session set "_optimizer_adaptive_cursor_sharing"=false; alter session set cursor_sharing=FORCE;
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm in ('_optimizer_adaptive_cursor_sharing','cursor_sharing','_optimizer_extended_cursor_sharing_rel','_optimizer_extended_cursor_sharing')
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE 8fqtv1mu1y2fv 0 N N IS_BIND_SENSITIVE='Y'：--绑定变量更改导致执行计划更改的更改的潜在候选者 IS_BIND_AWARE='Y'：--传入不同的绑定变量，导致执行计划真正改变了
| * 27 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 56 | 3 | 00:00:01 |
| * 28 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 | 00:00:01 |
* 27 - filter(BITAND("O"."FLAGS",128)=0)
* 28 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"='V_$SESSION')
而开发环境是一个索引跳跃扫描
|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 5 | 215 | 269 (0)| 00:00:01 |
|* 25 | INDEX SKIP SCAN | I_OBJ5 | 5 | | 266 (0)| 00:00:01 |
24 - filter(BITAND("O"."FLAGS",128)=0)
25 - access("O"."NAME"='FND_LOOKUP_VALUES_VL')
filter("O"."NAME"='FND_LOOKUP_VALUES_VL')
