首页 » ORACLE 9i-23ai » v$active_session_history slow, 如何查询v$fixed_view_definition中的全文本？
v$active_session_history slow, 如何查询v$fixed_view_definition中的全文本？
最近，我们的一位客户反馈，Oracle 数据库中有大量的活动会话正在查询 v$active_session_history。这些查询主要来自一个监控软件，用于刷新 ASH 数据。通常情况下，这些 SQL 查询都能在秒级内完成，但在客户的环境中，一次查询竟然耗时近6分钟。由于客户环境无法进行远程访问，客户初步认为这是一个 bug。不过，重点不在于此，而是在分析 v$active_session_history 视图定义时，发现 v$fixed_view_definition 中的内容并不完整。
为了进一步研究这些固定视图的定义，可以考虑使用一些工具和方法，例如 oradebug peek、objdump 和 gdb，来读取内存中 FIXED VIEW 的定义信息。记录下这些方法，以便在今后的分析和排查中参考。
之前一些x$或v$性能问题的案例
Troubleshooting Oracle 12c/19c expdp slow due to query for V$OPEN_CURSOR
query dba_free_space(tablespace usage) slow after upgrade 12c R2
与之相关的X$ view
x$kqfta – headline information about the x$ structures – name, size, column count.
x$kqfvi – a list of the names of the dynamic performance views (mostly pairs of v$/gv$)
x$kqfvt – a list of the select statements that define the views in x$kqfvi
x$kqfdt – a list of “derived tables”: a cross between synonyms and views of a few of the structures in x$kqfta
x$kqfco – a list of the “columns” in each of the tables in x$kqfta (but not x$kqfvi and x$kqfdt)
案例
其它正常环境的执行
SQL> set timing on
SQL> select max(sample_id) from v$active_session_history;
MAX(SAMPLE_ID)
--------------
40475187
Elapsed: 00:00:00.40
SQL> @x2
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 427897971
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | GV$ACTIVE_SESSION_HISTORY | 1 | 13 | 1 (100)| 00:00:01 |
| 3 | NESTED LOOPS SEMI | | 1 | 43 | 1 (100)| 00:00:01 |
| 4 | FIXED TABLE FULL | X$KEWASH | 5266 | 102K| 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 1 | 23 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND
"S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE" AND
"A"."INST_ID"=USERENV('INSTANCE'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
4 - GV_ASHV / "S"@"GV_ASHV"
U - use_nl(s,a)
26 rows selected.
Elapsed: 00:00:00.04
问题环境查询耗时近6分钟，正常一般是秒级。
可能的原因
1, ASH SIZE buffer过大 ，_ash_size 在12c前最大254M, 在12c及以后可以突破该限制。
2，RAC GC相关问题 ，v$ 基于gv$ 需要跨实例调用
3，错误的统计信息或执行计划
4，oracle bug
分析方法建议做10046 sql trace和session state对比。
查看v$active_session_history定义
SQL> @v v$active_session_history
Show SQL text of views matching "%v$active_session_history%"...
no rows selected
Elapsed: 00:00:00.16
VIEW_NAME TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$ACTIVE_SESSION_HISTORY SELECT /*+ qb_name(gv_ashv) no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id,
s.sample_time,s.sample_time_utc, a.usecs_per_row, s.is_awr_sample, a.session_id, a.session_serial#,
decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND'), a.flags, a.user_id, a.sql_id,
decode(bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), a.sql_child_number, a.sql_opcode,
a.force_matching_signature, decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id),
decode(a.top_level_sql_id, NULL, a.sql_opcode, a.top_level_sql_opcode),
a.sql_opname,a.sql_adaptive_plan_resolved,a.sql_full_plan_hash_value,a.sql_plan_hash_value,
decode(a.sql_plan_operation, NULL, to_number(NULL), a.sql_plan_line_id),
a.sql_plan_operation, a.sql_plan_options, decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id),
a.sql_exec_start, decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_object_id),
decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_subprogram_id),
decode(a.plsql_object_id,0,to_number(NULL),a.plsql_object_id),
decode(a.plsql_object_id,0,to_number(NULL),a.plsql_subprogram_id), decode(a.qc_session_id, 0,
to_number(NULL), a.qc_instance_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id),
decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#), decode(a.px_flags, 0,
to_number(NULL), a.px_flags), decode(a.wait_time, 0, a.event, NULL), decode(a.wait_time, 0,
a.event_id, NULL), decode(a.wait_time, 0, a.event#, NULL), a.seq#, a.p1text, a.p1, a.p2text,
a.p2, a.p3text, a.p3, decode(a.wait_time, 0, a.wait_class, NULL), decode(a.wait_time, 0,
a.wait_class_id, NULL), a.wait_time, decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.time_waited,
(case when a.blocking_session = 4294967295 then 'UNKNOWN' when a.blocking_session =
4294967294 then 'GLOBAL' when a.blocking_session = 4294967293 then 'UNKNOWN'
when a.blocking_session = 4294967292 then 'NO HOLDER' when a.blocking_session =
4294967291 then 'NOT IN WAIT' else 'VALID' end), (case when a.blocking_session
between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_session end),
(case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL)
else a.blocking_session_serial# end), (case when a.blocking_session between 4294967291 and
4294967295 then to_number(NULL) else a.blocking_inst_id end), (case when
a.blocking_session between 4294967291 and 4294967295 then NULL else
decode(bitand(a.flags, power(2, 3)),NULL, 'N', 0,'N','Y') end), a.current_obj#, a.current_file#,
a.current_block#, a.current_row#,a.top_level_call#, a.top_level_call_name,
decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id), a.xid,
decode(a.remote_instance#, 0, to_number(NULL), a.remote_instance#), a.time_model,
a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution,
a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close,
a.in_sequence_load, a.in_inmemory_query, a.in_inmemory_populate, a.in_inmemory_prepopulate,
a.in_inmemory_repopulate, a.in_inmemory_trepopulate, a.in_tablespace_encryption,
decode(bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 6)),
NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'),
decode(bitand(a.flags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 8)),
NULL, 'N', 0, 'N', 'Y'), a.service_hash, a.program, a.module, a.action, a.client_id, a.client_info,
a.machine, a.port, a.ecid, a.dbreplay_file_id, a.dbreplay_call_counter, decode(a.tm_delta_time, 0,
to_number(null), a.tm_delta_time), decode(a.tm_delta_time, 0, to_number(null),
a.tm_delta_cpu_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_db_time),
decode(a.delta_time, 0, to_number(null), a.delta_time), decode(a
V$ACTIVE_SESSION_HISTORY SELECT /*+ qb_name(v_ashv) */ sample_id, sample_time, sample_time_utc, usecs_per_row,
is_awr_sample, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current,
sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id,
top_level_sql_opcode, sql_adaptive_plan_resolved, sql_full_plan_hash_value, sql_plan_hash_value,
sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start,
plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id,
qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, event#, seq#, p1text,
p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited,
blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id,
blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#,
top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model,
in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc,
in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load,
in_inmemory_query, in_inmemory_populate, in_inmemory_prepopulate, in_inmemory_repopulate,
in_inmemory_trepopulate, in_tablespace_encryption, capture_overhead, replay_overhead, is_captured,
is_replayed, is_replay_sync_token_holder, service_hash, program, module, action, client_id,
client_info, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time,
tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests,
delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, delta_read_mem_bytes,
pga_allocated, temp_space_allocated, con_dbid,con_id, dbop_name, dbop_exec_id FROM
GV$ACTIVE_SESSION_HISTORY WHERE inst_id = USERENV('INSTANCE')
Elapsed: 00:00:00.02
SQL>
SQL> @x2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1966599742
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 42252 | 0 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 84 | 42252 | 0 (0)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KQFVI | 84 | 2268 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KQFVT | 1686 | 783K| 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("I"."INDX"="T"."INDX")
2 - filter(UPPER("KQFVINAM") LIKE '%V$ACTIVE_SESSION_HISTORY%' AND
"I"."INST_ID"=USERENV('INSTANCE'))
17 rows selected.
Elapsed: 00:00:00.03
SQL> @v GV$FIXED_VIEW_DEFINITION
Show SQL text of views matching "%GV$FIXED_VIEW_DEFINITION%"...
no rows selected
VIEW_NAME TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$FIXED_VIEW_DEFINITION select i.inst_id,kqfvinam,kqftpsel, i.con_id from x$kqfvi i, x$kqfvt t where i.indx = t.indx
这里查询的是v$fixed_view_definition， fixed视图定义中有一个相当常见的模式 – 它们通常成对出现，一个以 GV$ 开头，另一个以 V$ 开头，而 V$ 通常只是“从 gv$ 中选择此实例的大多数列”。 GV$ 视图是与 RAC 相关的“全局”视图，V$ 视图是当前实例的本地视图。上一个查询的结果中注意到，gv$fixed_view_definition是x$kqfvi和x$kqfvt在indx列上的简单连接。这个视图存在一个问题view的定义显示不全，如果查看view column长度varchar2(4000)，对于超长的view 如v$active_session_histor定义就不全面，当然可能影响分析性能分析。
SQL> @desc v$fixed_view_definition
Name Null? Type
------------------------------- -------- ----------------------------
1 VIEW_NAME VARCHAR2(128)
2 VIEW_DEFINITION VARCHAR2(4000)
3 CON_ID NUMBER
SQL> @desc x$kqfvt
Name Null? Type
------------------------------- -------- ----------------------------
1 ADDR RAW(8)
2 INDX NUMBER
3 INST_ID NUMBER
4 CON_ID NUMBER
5 KQFTPSEL VARCHAR2(4000)
SQL> select
addr,
to_number(addr,'XXXXXXXXXXXXXXXX') addr_dec,
to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') lag_addr_dec,
to_char(
to_number(addr,'XXXXXXXXXXXXXXXX') -
to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx'),
'xxxxxxxxxxxxxxxx'
) row_size_hex,
to_number(addr,'XXXXXXXXXXXXXXXX') -
to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') row_size,
indx
from x$kqfvt
where indx <= 10
order by
ADDR ADDR_DEC LAG_ADDR_DEC ROW_SIZE_HEX ROW_SIZE INDX
---------------- -------------------- -------------------- ----------------- -------------------- ----------
00000000189A1120 412,750,112 0
00000000189A1148 412,750,152 412,750,112 28 40 1
00000000189A1170 412,750,192 412,750,152 28 40 2
00000000189A1198 412,750,232 412,750,192 28 40 3
00000000189A11C0 412,750,272 412,750,232 28 40 4
00000000189A11E8 412,750,312 412,750,272 28 40 5
00000000189A1210 412,750,352 412,750,312 28 40 6
00000000189A1238 412,750,392 412,750,352 28 40 7
00000000189A1260 412,750,432 412,750,392 28 40 8
00000000189A1288 412,750,472 412,750,432 28 40 9
00000000189A12B0 412,750,512 412,750,472 28 40 10
00000000189A12D8 412,750,552 412,750,512 28 40 11
00000000189A1300 412,750,592 412,750,552 28 40 12
00000000189A1328 412,750,632 412,750,592 28 40 13
00000000189A1350 412,750,672 412,750,632 28 40 14
查看x$kqfvt行的长度，您会发现行长度为 40 (32?)个字节
SQL> select
2 ta.kqftanam,
3 co.kqfconam,
4 co.kqfcodty,
5 co.kqfcotyp,
6 co.kqfcomax,
7 co.kqfcolsz,
8 co.kqfcolof,
9 co.kqfcosiz,
10 co.kqfcooff,
11 co.kqfcoidx,
12 co.kqfcoipo
13 from
14 x$kqfta ta,
15 x$kqfco co
16 where
17 co.kqfcotab = ta.indx
18 and kqftanam like UPPER('%kqfvt%')
19 order by
20 ta.kqftanam,
21 co.kqfcooff,
22 co.indx
23*
Column Ext Type Int Type Array Max Len Size Len Offset Col Size Offset Index Idx Col
------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ADDR 23 9 0 0 0 8 0 1 0
INDX 2 11 0 0 0 4 0 2 0
INST_ID 2 11 0 0 0 4 0 0 0
CON_ID 2 11 0 0 0 2 0 0 0
KQFTPSEL 1 6 0 0 0 4000 0 0 0
查看完整定义可以从x$kqfvt中保存视图文本的行的地址的内容
SQL> select
i.addr, t.addr
from
x$kqfvi i,
x$kqfvt t
where
i.kqfvinam = 'GV$ACTIVE_SESSION_HISTORY'
and t.indx = i.indx;
ADDR ADDR
---------------- ----------------
0000000018990DC0 00000000189A9870
SQL> oradebug setmypid
Statement processed.
SQL> oradebug peek 0x189A9870 32
[0189A9870, 0189A9890) = 18C78B00 00000000 189E8FC0 00000000 00000000 00000000 00000000 00000000
这看起来像是 2 个地址（每个 8 个字节）和 16 个空字节
SQL> oradebug peek 0x18C78B00 256 1
[018C78B00, 018C78C00) = 454C4553 20205443 202B2A2F 6E5F6271 28656D61 615F7667 29766873 5F6F6E20 6772656D 726F2065 65726564 73752064 6C6E5F65 612C7328 ...
SQL> select utl_raw.CAST_TO_VARCHAR2('454C455320205443');
UTL_RAW.CAST_TO_VARCHAR2('454C455320205443')
-----------------------------------------------------------------------
ELES TC
十六进制值在“ASCII 字母数字”范围内，因为是小字节序，拼起来就是SELECT，窥视的地址处找到视图的全文，因为视图定义的呈现可能只是在4,000 字节标记处停止，而实际上它仍在内存中继续。因此，让我们对我们在x$kqfvt中找到的第一个地址进行更深入的窥视，并检查跟踪文件：
SQL> oradebug peek 0x18C78AAA 9000 1 [018C78AA8, 018C7ADD4) = 4C4C415F 4445574F 4F43202C 44495F4E 6F726620 5647206D 42445024 434E495F 414E5241 4E4F4954 65687720 69206572 5F74736E 3D206469 ... SQL> @t TRACEFILE ------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/enmo/enmo1/trace/anbob1_ora_2710941.trc
因此我实际上在目标地址之前想要查看的整个部分，1 输出到trace文件
*** 2024-09-03T05:55:42.498619+08:00 (CDB$ROOT(1))
Processing Oradebug command 'peek 0x18C78AAA 9000 1'
[018C78AA8, 018C7ADD4) = 4C4C415F 4445574F 4F43202C 44495F4E 6F726620 ...
Dump of memory from 0x018C78ABC to 0x018C7ADD4
018C78AB0 5647206D [m GV]
018C78AC0 42445024 434E495F 414E5241 4E4F4954 [$PDB_INCARNATION]
018C78AD0 65687720 69206572 5F74736E 3D206469 [ where inst_id =]
018C78AE0 45535520 564E4552 6E492728 6E617473 [ USERENV('Instan]
018C78AF0 29276563 00000000 00000000 00000000 [ce')............]
018C78B00 454C4553 20205443 202B2A2F 6E5F6271 [SELECT /*+ qb_n]
018C78B10 28656D61 615F7667 29766873 5F6F6E20 [ame(gv_ashv) no_]
018C78B20 6772656D 726F2065 65726564 73752064 [merge ordered us]
018C78B30 6C6E5F65 612C7328 2F2A2029 692E6120 [e_nl(s,a) */ a.i]
018C78B40 5F74736E 202C6469 61732E73 656C706D [nst_id, s.sample]
018C78B50 2C64695F 732E7320 6C706D61 69745F65 [_id, s.sample_ti]
018C78B60 732C656D 6D61732E 5F656C70 656D6974 [me,s.sample_time]
018C78B70 6374755F 2E61202C 63657375 65705F73 [_utc, a.usecs_pe]
018C78B80 6F725F72 73202C77 5F73692E 5F727761 [r_row, s.is_awr_]
018C78B90 706D6173 202C656C 65732E61 6F697373 [sample, a.sessio]
018C78BA0 64695F6E 2E61202C 73736573 5F6E6F69 [n_id, a.session_]
018C78BB0 69726573 2C236C61 63656420 2865646F [serial#, decode(]
018C78BC0 65732E61 6F697373 79745F6E 202C6570 [a.session_type, ]
018C78BD0 27202C31 45524F46 554F5247 2C27444E [1, 'FOREGROUND',]
018C78BE0 41422720 52474B43 444E554F 202C2927 [ 'BACKGROUND'), ]
018C78BF0 6C662E61 2C736761 752E6120 5F726573 [a.flags, a.user_]
018C78C00 202C6469 71732E61 64695F6C 6564202C [id, a.sql_id, de]
018C78C10 65646F63 74696228 28646E61 6C662E61 [code(bitand(a.fl]
018C78C20 2C736761 776F7020 32287265 2934202C [ags, power(2, 4)]
018C78C30 4E202C29 2C4C4C55 274E2720 2C30202C [), NULL, 'N', 0,]
018C78C40 274E2720 5927202C 202C2927 71732E61 [ 'N', 'Y'), a.sq]
018C78C50 68635F6C 5F646C69 626D756E 202C7265 [l_child_number, ]
018C78C60 71732E61 706F5F6C 65646F63 2E61202C [a.sql_opcode, a.]
018C78C70 63726F66 616D5F65 69686374 735F676E [force_matching_s]
018C78C80 616E6769 65727574 6564202C 65646F63 [ignature, decode]
018C78C90 742E6128 6C5F706F 6C657665 6C71735F [(a.top_level_sql]
018C78CA0 2C64695F 4C554E20 61202C4C 6C71732E [_id, NULL, a.sql]
018C78CB0 2C64695F 742E6120 6C5F706F 6C657665 [_id, a.top_level]
018C78CC0 6C71735F 2964695F 6564202C 65646F63 [_sql_id), decode]
018C78CD0 742E6128 6C5F706F 6C657665 6C71735F [(a.top_level_sql]
018C78CE0 2C64695F 4C554E20 61202C4C 6C71732E [_id, NULL, a.sql]
018C78CF0 63706F5F 2C65646F 20202020 20202020 [_opcode, ]
018C78D00 6F742E61 656C5F70 5F6C6576 5F6C7173 [a.top_level_sql_]
018C78D10 6F63706F 2C296564 732E6120 6F5F6C71 [opcode), a.sql_o]
018C78D20 6D616E70 2E612C65 5F6C7173 70616461 [pname,a.sql_adap]
018C78D30 65766974 616C705F 65725F6E 766C6F73 [tive_plan_resolv]
018C78D40 612C6465 6C71732E 6C75665F 6C705F6C [ed,a.sql_full_pl]
018C78D50 685F6E61 5F687361 756C6176 2E612C65 [an_hash_value,a.]
018C78D60 5F6C7173 6E616C70 7361685F 61765F68 [sql_plan_hash_va]
018C78D70 2C65756C 63656420 2865646F 71732E61 [lue, decode(a.sq]
018C78D80 6C705F6C 6F5F6E61 61726570 6E6F6974 [l_plan_operation]
...
...
...
018C79B50 6E287265 296C6C75 2020202C 20202020 [er(null), ]
018C79B60 2E612020 746C6564 65725F61 695F6461 [ a.delta_read_i]
018C79B70 79625F6F 29736574 6564202C 65646F63 [o_bytes), decode]
018C79B80 642E6128 61746C65 6D69745F 30202C65 [(a.delta_time, 0]
018C79B90 6F74202C 6D756E5F 28726562 6C6C756E [, to_number(null]
018C79BA0 20202C29 20202020 61202020 6C65642E [), a.del]
018C79BB0 775F6174 65746972 5F6F695F 65747962 [ta_write_io_byte]
018C79BC0 202C2973 6F636564 61286564 6C65642E [s), decode(a.del]
018C79BD0 745F6174 2C656D69 202C3020 6E5F6F74 [ta_time, 0, to_n]
018C79BE0 65626D75 756E2872 2C296C6C 20202020 [umber(null), ]
018C79BF0 20202020 642E6120 61746C65 746E695F [ a.delta_int]
018C79C00 6F637265 63656E6E 6F695F74 7479625F [erconnect_io_byt]
018C79C10 2C297365 63656420 2865646F 65642E61 [es), decode(a.de]
018C79C20 5F61746C 656D6974 2C30202C 5F6F7420 [lta_time, 0, to_]
018C79C30 626D756E 6E287265 296C6C75 2020202C [number(null), ]
018C79C40 20202020 2E612020 746C6564 65725F61 [ a.delta_re]
018C79C50 6D5F6461 625F6D65 73657479 64202C29 [ad_mem_bytes), d]
018C79C60 646F6365 2E612865 5F616770 6F6C6C61 [ecode(a.pga_allo]
018C79C70 65746163 30202C64 6F74202C 6D756E5F [cated, 0, to_num]
018C79C80 28726562 6C6C756E 61202C29 6167702E [ber(null), a.pga]
018C79C90 6C6C615F 7461636F 2C296465 63656420 [_allocated), dec]
018C79CA0 2865646F 67702E61 6C615F61 61636F6C [ode(a.pga_alloca]
018C79CB0 2C646574 202C3020 6E5F6F74 65626D75 [ted, 0, to_numbe]
018C79CC0 756E2872 2C296C6C 20202020 20202020 [r(null), ]
018C79CD0 742E6120 5F706D65 63617073 6C615F65 [ a.temp_space_al]
018C79CE0 61636F6C 29646574 2E61202C 5F6E6F63 [located), a.con_]
018C79CF0 64696264 2E61202C 5F6E6F63 202C6469 [dbid, a.con_id, ]
018C79D00 62642E61 6E5F706F 2C656D61 642E6120 [a.dbop_name, a.d]
018C79D10 5F706F62 63657865 2064695F 4D4F5246 [bop_exec_id FROM]
018C79D20 24782020 6177656B 73206873 2478202C [ x$kewash s, x$]
018C79D30 20687361 48572061 20455245 61732E73 [ash a WHERE s.sa]
018C79D40 656C706D 6464615F 203D2072 61732E61 [mple_addr = a.sa]
018C79D50 656C706D 6464615F 6E612072 20202064 [mple_addr and ]
018C79D60 20202020 61732E73 656C706D 2064695F [ s.sample_id ]
018C79D70 203D2020 61732E61 656C706D 2064695F [ = a.sample_id ]
018C79D80 6E612020 20202064 20202020 61732E73 [ and s.sa]
018C79D90 656C706D 6D69745F 203D2065 61732E61 [mple_time = a.sa]
018C79DA0 656C706D 6D69745F 6E612065 20202064 [mple_time and ]
018C79DB0 20202020 73736C6E 2874726F 656E2E73 [ nlssort(s.ne]
018C79DC0 615F6465 735F7277 6C706D61 6E272C65 [ed_awr_sample,'n]
018C79DD0 735F736C 2074726F 6962203D 7972616E [ls_sort = binary]
018C79DE0 3D202927 20202020 6E202020 6F73736C [') = nlsso]
018C79DF0 61287472 65656E2E 77615F64 61735F72 [rt(a.need_awr_sa]
018C79E00 656C706D 6C6E272C 6F735F73 3D207472 [mple,'nls_sort =]
018C79E10 6E696220 27797261 00000029 00000000 [ binary').......]
018C79E20 454C4553 2F205443 71202B2A 616E5F62 [SELECT /*+ qb_na]
正如您所见，我得到了整个视图定义（这意味着我现在可以看到from子句中的所有表和where子句中的所有谓词）。现在我可以做一些繁琐的工作来编辑跟踪文件以提取完整视图 ,但是非常的累，不过你从view可以看到查询的顺序是x$kewash s, x$ash a这就可以从oracle binary file中查看这些词。
[oracle@db1 ~]$ strings -a `which oracle`|grep -n "x\$kewash.*x\$ash"
2324908:SELECT /*+ qb_name(gv_ashv) no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id, s.sample_time,s.sample_time_utc, a.usecs_per_row,
s.is_awr_sample, a.session_id, a.session_serial#, decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND'), a.flags, a.user_id, a.sql_id,
decode(bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), a.sql_child_number, a.sql_opcode, a.force_matching_signature,
decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id), decode(a.top_level_sql_id, NULL, a.sql_opcode, a.top_level_sql_opcode),
a.sql_opname,a.sql_adaptive_plan_resolved,a.sql_full_plan_hash_value,a.sql_plan_hash_value, decode(a.sql_plan_operation, NULL, to_number(NULL),
a.sql_plan_line_id), a.sql_plan_operation, a.sql_plan_options, decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id), a.sql_exec_start,
decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_object_id), decode(a.plsql_entry_object_id,0,to_number(NULL),
a.plsql_entry_subprogram_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_object_id), decode(a.plsql_object_id,0,to_number(NULL)
a.plsql_subprogram_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_instance_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id),
decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#), decode(a.px_flags, 0, to_number(NULL), a.px_flags), decode(a.wait_time, 0, a.event,
NULL), decode(a.wait_time, 0, a.event_id, NULL),
decode(a.wait_time, 0, a.event#, NULL), a.seq#, a.p1text, a.p1, a.p2text, a.p2, a.p3text, a.p3, decode(a.wait_time, 0, a.wait_class, NULL),
decode(a.wait_time, 0, a.wait_class_id, NULL), a.wait_time, decode(a.wait_time, 0, 'WAITING', 'ON CPU'),
a.time_waited, (case when a.blocking_session = 4294967295 then 'UNKNOWN' when a.blocking_session = 4294967294
then 'GLOBAL' when a.blocking_session = 4294967293 then 'UNKNOWN' when a.blocking_session = 4294967292 then 'NO HOLDER'
when a.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end), (case when a.blocking_session between 4294967291 and 4294967295
then to_number(NULL) else a.blocking_session end), (case when a.blocking_session between 4294967291 and 4294967295
then to_number(NULL) else a.blocking_session_serial# end), (case when a.blocking_session between 4294967291 and 4294967295
then to_number(NULL) else a.blocking_inst_id end), (case when a.blocking_session between 4294967291 and 4294967295 then NULL
else decode(bitand(a.flags, power(2, 3)),NULL, 'N', 0,'N','Y') end), a.current_obj#, a.current_file#, a.current_block#, a.current_row#,a.top_level_call#,
a.top_level_call_name, decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id), a.xid, decode(a.remote_instance#, 0, to_number(NULL),
a.remote_instance#), a.time_model, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution,
a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load, a.in_inmemory_query,
a.in_inmemory_populate, a.in_inmemory_prepopulate, a.in_inmemory_repopulate, a.in_inmemory_trepopulate, a.in_tablespace_encryption,
decode(bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 6)), NULL, 'N', 0, 'N', 'Y'),
decode(bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'),
decode(bitand(a.flags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 8)), NULL, 'N', 0, 'N', 'Y'), a.service_hash, a.program, a.module,
a.action, a.client_id, a.client_info, a.machine, a.port, a.ecid, a.dbreplay_file_id, a.dbreplay_call_counter, decode(a.tm_delta_time, 0, to_number(null),
a.tm_delta_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_cpu_time), decode(a.tm_delta_time, 0, to_number(null),
a.tm_delta_db_time), decode(a.delta_time, 0, to_number(null), a.delta_time), decode(a.delta_time, 0, to_number(null),
a.delta_read_io_requests), decode(a.delta_time, 0, to_number(null), a.delta_write_io_requests), decode(a.delta_time, 0, to_number(null),
a.delta_read_io_bytes), decode(a.delta_time, 0, to_number(null), a.delta_write_io_bytes), decode(a.delta_time, 0, to_number(null),
a.delta_interconnect_io_bytes), decode(a.delta_time, 0, to_number(null), a.delta_read_mem_bytes),
decode(a.pga_allocated, 0, to_number(null), a.pga_allocated), decode(a.pga_allocated, 0, to_number(null), a.temp_space_allocated), a.con_dbid,
a.con_id, a.dbop_name, a.dbop_exec_id FROM x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr and s.sample_id = a.sample_id and
s.sample_time = a.sample_time and nlssort(s.need_awr_sample,'nls_sort = binary') = nlssort(a.need_awr_sample,'nls_sort = binary')
[oracle@db1 ~]$
Note:
这样就取到了完整定义。
SQL> select max(sample_time) from x$ash; MAX(SAMPLE_TIME) --------------------------------------------------------------------------- 03-SEP-24 04.56.48.913 AM 1 row selected. SQL> @x2 PLAN_TABLE_OUTPUT ----------- Plan hash value: 2196106623 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 1 (100)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | | 2 | FIXED TABLE FULL| X$ASH | 6419 | 44933 | 1 (100)| 00:00:01 | --------------------------------------------------------------------------- 9 rows selected. SQL> select * from V$INDEXED_FIXED_COLUMN where table_name='X$KEWASH'; TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID ----------------------------------------------------------------------------- ------------ --------------------- --------------- ---------- X$KEWASH 1 IS_AWR_SAMPLE 0 0 SQL> select * from V$INDEXED_FIXED_COLUMN where table_name='X$ASH'; TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID ----------------------------------------------------------------------------- ------------ --------------------- --------------- ---------- X$ASH 1 SAMPLE_ADDR 0 0 X$ASH 1 SAMPLE_ID 1 0
X$ASH 有1个索引，x$的索引显示（ind:N）的形式，N是index_number, 这里注意是2列的复合索引，sample_id也不是前导列。
SQL> @gts X$ASH
Gather Table Statistics for table X$ASH...
PL/SQL procedure successfully completed.
Elapsed: 00:00:21.54
SQL> select /*+INDEX_SS_DESC(t)*/ max(sample_id) from x$ash t where sample_id is not null;
MAX(SAMPLE_ID)
--------------
40507675
Elapsed: 00:00:00.06
SQL> @x2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2196106623
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 8 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | FIXED TABLE FULL| X$ASH | 97281 | 570K| 8 (100)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAMPLE_ID" IS NOT NULL)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / "T"@"SEL$1"
U - INDEX_SS_DESC(t)
21 rows selected.
Elapsed: 00:00:00.03
E : indicates a syntax error.
N : indicates an unresolved hint.
U : indicates that the corresponding hint was not used in the final plan.
暂时没有太好的方法使用索引。 但即使FIXED TABLE FULLscan在其他环境也是秒出，继续在MOS中查询bug。
Generating ASH Report Is Slow Due To Selecting From V$ACTIVE_SESSION_HISTORY Is Slow (Doc ID 2299480.1) 记录存在一个bug,配置_optimizer_partial_join_eval， 禁用NESTED LOOPS SEMI
SQL> alter session set "_optimizer_partial_join_eval"=FALSE;
Session altered.
SQL> select max(sample_id) from v$active_session_history;
MAX(SAMPLE_ID)
--------------
40477646
Elapsed: 00:00:00.21
SQL> @x2
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 547098871
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | GV$ACTIVE_SESSION_HISTORY | 5266 | 68458 | 1 (100)| 00:00:01 |
| 3 | NESTED LOOPS | | 5266 | 221K| 1 (100)| 00:00:01 |
| 4 | FIXED TABLE FULL | X$KEWASH | 5266 | 102K| 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 1 | 23 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND
"S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE" AND
"A"."INST_ID"=USERENV('INSTANCE'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
4 - GV_ASHV / "S"@"GV_ASHV"
U - use_nl(s,a)
26 rows selected.
速度是有一点点提升，如果遇到不妨尝试。这里不是我要记录的重点，当然我们再继续看看有没有其它方法查看fixed view的完整定义
objdump
[oracle@db1 ~]$ readelf -s `which oracle`
Symbol table '.dynsym' contains 227732 entries:
Num: Value Size Type Bind Vis Ndx Name
0: 0000000000000000 0 NOTYPE LOCAL DEFAULT UND
1: 0000000000000000 0 FUNC GLOBAL DEFAULT UND lchown@GLIBC_2.2.5 (2)
2: 0000000000000000 0 FUNC GLOBAL DEFAULT UND get_nprocs_conf@GLIBC_2.2.5 (2)
3: 0000000000000000 0 FUNC GLOBAL DEFAULT UND fdopen@GLIBC_2.2.5 (2)
4: 0000000000000000 0 FUNC GLOBAL DEFAULT UND pclose@GLIBC_2.2.5 (2)
5: 0000000000000000 0 FUNC WEAK DEFAULT UND ZSTD_trace_compress_end
6: 0000000000000000 0 FUNC GLOBAL DEFAULT UND clsr_start_pdb
7: 0000000000000000 0 FUNC GLOBAL DEFAULT UND realloc@GLIBC_2.2.5 (2)
8: 0000000000000000 0 FUNC GLOBAL DEFAULT UND clssgsXgrpSharedGrpDeReg
9: 0000000000000000 0 FUNC GLOBAL DEFAULT UND ztv2parse
10: 0000000000000000 0 FUNC GLOBAL DEFAULT UND scls_filelist_open
11: 0000000000000000 0 FUNC GLOBAL DEFAULT UND clsr_get_asm_pwfile
...
SQL> select indx, addr from x$kqfvt;
INDX ADDR
---------- ----------------
0 0000000016C69BC0
1 0000000016C69BE0
...
[oracle@db-21 bin]$ readelf -s oracle | grep -E -iw 'Size|0000000016C69BC0'
Num: Value Size Type Bind Vis Ndx Name
17836: 0000000016c69bc0 51456 OBJECT GLOBAL DEFAULT 17 kqfvip
可以使用 objdump 来检查结构内部的内容：
[oracle@db-21 bin]$ a=$((0x16C69BC0)) ; objdump -zs --start-address=$a --stop-address=$(($a+96)) $(which oracle)
/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle: file format elf64-x86-64
Contents of section .rodata:
16c69bc0 c0ecea16 00000000 c064c716 00000000 .........d......
16c69bd0 00000000 00000000 00000000 00000000 ................
16c69be0 80eeea16 00000000 2065c716 00000000 ........ e......
16c69bf0 00000000 00000000 00000000 00000000 ................
16c69c00 e0eeea16 00000000 8065c716 00000000 .........e......
16c69c10 00000000 00000000 00000000 00000000 ................
note:
这种也可以取到和peek相同的记录信息
GDB
甚至还可以使用gdb读取
SQL> select
i.addr, t.addr
from
x$kqfvi i,
x$kqfvt t
where
i.kqfvinam = 'GV$ACTIVE_SESSION_HISTORY'
and t.indx = i.indx
/ 2 3 4 5 6 7 8 9
ADDR ADDR
---------------- ----------------
0000000018990DC0 00000000189A9870
Elapsed: 00:00:05.54
SQL> @i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS CDB$ROOT-enmo1 db1 1 1350 53604 23.0.0.0.0 20240815 2929642 139 2929641 00000000C566D510 00000000C6521558
使用 gdb 附加到 Oracle 进程
$ gdb -p 2929642
(gdb) set pagination off
(gdb) set print elements 0
(gdb) x/s *(char **) 0x00000000189A9870
0x18c78b00: "SELECT /*+ qb_name(gv_ashv) no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id, s.sample_time,s.sample_time_utc, a.usecs_per_row, s.is_awr_sample, a.session_id, a.session_serial#, decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND'), a.flags, a.user_id, a.sql_id, decode(bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), a.sql_child_number, a.sql_opcode, a.force_matching_signature, decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id), decode(a.top_level_sql_id, NULL, a.sql_opcode, a.top_level_sql_opcode), a.sql_opname,a.sql_adaptive_plan_resolved,a.sql_full_plan_hash_value,a.sql_plan_hash_value, decode(a.sql_plan_operation, NULL, to_number(NULL), a.sql_plan_line_id), a.sql_plan_operation, a.sql_plan_options, decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id), a.sql_exec_start, decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_object_id), decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_subprogram_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_object_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_subprogram_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_instance_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#), decode(a.px_flags, 0, to_number(NULL), a.px_flags), decode(a.wait_time, 0, a.event, NULL), decode(a.wait_time, 0, a.event_id, NULL), decode(a.wait_time, 0, a.event#, NULL), a.seq#, a.p1text, a.p1, a.p2text, a.p2, a.p3text, a.p3, decode(a.wait_time, 0, a.wait_class, NULL), decode(a.wait_time, 0, a.wait_class_id, NULL), a.wait_time, decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.time_waited, (case when a.blocking_session = 4294967295 then 'UNKNOWN' when a.blocking_session = 4294967294 then 'GLOBAL' when a.blocking_session = 4294967293 then 'UNKNOWN' when a.blocking_session = 4294967292 then 'NO HOLDER' when a.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end), (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_session end), (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_session_serial# end), (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_inst_id end), (case when a.blocking_session between 4294967291 and 4294967295 then NULL else decode(bitand(a.flags, power(2, 3)),NULL, 'N', 0,'N','Y') end), a.current_obj#, a.current_file#, a.current_block#, a.current_row#,a.top_level_call#, a.top_level_call_name, decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id), a.xid, decode(a.remote_instance#, 0, to_number(NULL), a.remote_instance#), a.time_model, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load, a.in_inmemory_query, a.in_inmemory_populate, a.in_inmemory_prepopulate, a.in_inmemory_repopulate, a.in_inmemory_trepopulate, a.in_tablespace_encryption, decode(bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 6)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 8)), NULL, 'N', 0, 'N', 'Y'), a.service_hash, a.program, a.module, a.action, a.client_id, a.client_info, a.machine, a.port, a.ecid, a.dbreplay_file_id, a.dbreplay_call_counter, decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_cpu_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_db_time), decode(a.delta_time, 0, to_number(null), a.delta_time), decode(a.delta_time, 0, to_number(null), a.delta_read_io_requests), decode(a.delta_time, 0, to_number(null), a.delta_write_io_requests), decode(a.delta_time, 0, to_number(null), a.delta_read_io_bytes), decode(a.delta_time, 0, to_number(null), a.delta_write_io_bytes), decode(a.delta_time, 0, to_number(null), a.delta_interconnect_io_bytes), decode(a.delta_time, 0, to_number(null), a.delta_read_mem_bytes), decode(a.pga_allocated, 0, to_number(null), a.pga_allocated), decode(a.pga_allocated, 0, to_number(null), a.temp_space_allocated), a.con_dbid, a.con_id, a.dbop_name, a.dbop_exec_id FROM x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr and s.sample_id = a.sample_id and s.sample_time = a.sample_time and nlssort(s.need_awr_sample,'nls_sort = binary') = nlssort(a.need_awr_sample,'nls_sort = binary')"
(gdb)
— enjoy —
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)