[20240911]檢視超長檢視的定義2.txt

[20240911]檢視超長檢視的定義2.txt

--//昨天看了連結:https://www.anbob.com/archives/8295.html,提供了另外的方式獲得超長定義試圖的長文字。
--//我重複驗證看看.

1.環境:
SYS@book> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

2.測試:
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 1;

ADDR ADDR_DEC LAG_ADDR_DEC ROW_SIZE_HEX ROW_SIZE INDX
---------------- ---------- ------------ ----------------- ---------- ----------
0000000016BECC00 381602816 0
0000000016BECC20 381602848 381602816 20 32 1
0000000016BECC40 381602880 381602848 20 32 2
0000000016BECC60 381602912 381602880 20 32 3
0000000016BECC80 381602944 381602912 20 32 4
0000000016BECCA0 381602976 381602944 20 32 5
0000000016BECCC0 381603008 381602976 20 32 6
0000000016BECCE0 381603040 381603008 20 32 7
0000000016BECD00 381603072 381603040 20 32 8
0000000016BECD20 381603104 381603072 20 32 9
0000000016BECD40 381603136 381603104 20 32 10
11 rows selected.

--//我的測試版本也許與原作者不同,檢視x$kqfvt行的長度，您會發現行長度為32(對方的測試結果40).

SELECT ta.kqftanam, co.kqfconam, co.kqfcodty, co.kqfcotyp, co.kqfcomax,
co.kqfcolsz, co.kqfcolof, co.kqfcosiz, co.kqfcooff, co.kqfcoidx,
co.kqfcoipo
FROM x$kqfta ta, x$kqfco co
WHERE co.kqfcotab = ta.indx
AND kqftanam LIKE UPPER('%kqfvt%')
ORDER BY ta.kqftanam, co.kqfcooff, co.indx;

KQFTANAM KQFCONAM KQFCODTY KQFCOTYP KQFCOMAX KQFCOLSZ KQFCOLOF KQFCOSIZ KQFCOOFF KQFCOIDX KQFCOIPO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
X$KQFVT ADDR 23 9 0 0 0 8 0 1 0
X$KQFVT INDX 2 11 0 0 0 4 0 2 0
X$KQFVT INST_ID 2 11 0 0 0 4 0 0 0
X$KQFVT CON_ID 2 11 0 0 0 2 0 0 0
X$KQFVT KQFTPSEL 1 6 0 0 0 4000 0 0 0
--//X$KQFVT包含5個欄位,可以發現一個細節,實際上佔用8+4+4+2+4000 = 4018.而實際上行長僅僅32個位元組.
--//也就是不能以普通表方式思考看待X$ fixed table.
--//是否可以這樣理解INST_ID,CON_ID 可以透過函式取出,KQFTPSEL透過addr指向的地址取出.

SYS@book> @ xind X$KQFVT
TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
------------------------------ ------------ ------------------------------ --------------- ----------
X$KQFVT 1 ADDR 0 0
X$KQFVT 2 INDX 0 0
--//僅僅存在2個索引addr,indr.

SYS@book> 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
---------------- ----------------
0000000016BDE4C0 0000000016BF3880

--//sql語句的資訊儲存在地址0000000016BF3880指向的地址.

SYS@book> oradebug setmypid
Statement processed.

SYS@book> oradebug peek 0x0000000016BF3880 32
[016BF3880, 016BF38A0) = 16E985C0 00000000 16C30640 00000000 00000000 00000000 00000000 00000000
--//sql語句儲存在地址0000000016E985C0開始處.受KQFTPSEL varchar2(4000)限制僅僅取出4000位元組.

SYS@book> @ fchaz 0000000016BF3880
no rows selected

SYS@book> @ fchaz 16E985C0
no rows selected
--//這些地址不再sga uga等地方,到底在哪裡呢?

SYS@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
288 19200 3795 DEDICATED 3796 62 4 alter system kill session '288,19200' immediate;

$ pmap -x 3796 | head -10
3796: oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 422472 37660 0 r-x-- oracle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000000001a292000 1540 396 4 r---- oracle
000000001a413000 468 28 24 rw--- oracle
000000001a488000 344 196 196 rw--- [ anon ]
000000001ad7e000 316 284 284 rw--- [ anon ]
0000000060000000 10240 0 0 rw-s- SYSV00000000 (deleted)
0000000060c00000 770048 0 0 rw-s- SYSV00000000 (deleted)
000000008fc00000 8192 0 0 rw-s- SYSV00000000 (deleted)

SYS@book> @ calcx 422472*1024

DEC HEX
----------------------------------- --------------------
432611328.000000 0000000019c92000

SYS@book> @ calc 0x400000 + x0000000019c92000

DEC HEX
----------------------------------- --------------------
436805632.000000 1A092000

--//0x400000 = 4194304
--//4194304+422472*1024 = 436805632
--//4194304+422472*1024 = 0x1a092000

$ echo p/x 0x400000+422472*1024 | gdb -q
(gdb) $1 = 0x1a092000
(gdb) quit

--//很明顯在執行檔案oracle裡面,也就是透過strings抽取文字,可以找到對應的sql文字.
--//我以前透過抽取libserver.a,效果一樣的.

$ strings /u01/app/oracle/product/21.0.0/dbhome_1/lib/libserver.a | grep "x\$kewash.*x\$ash" > cc1.txt
$ strings $(which oracle) |grep "x\$kewash.*x\$ash" > cc2.txt
$ diff cc1.txt cc2.txt
--//結果不再貼出.

3.嘗試透過指令碼是否可以實現,測試看看:

$ cat ext_sql.sh
#! /bin/bash
tn="${1^^}"
#echo $tn

# sqlplus -s -l / as sysdba << EOF
# set head off verify off feedback off
# select t.addr from x\$kqfvi i,x\$kqfvt t where i.kqfvinam = '$tn' and t.indx = i.indx;
# quit
# EOF

t_addr=$(sqlplus -s -l / as sysdba << EOF
set head off verify off feedback off
select t.addr addr from x\$kqfvi i,x\$kqfvt t where i.kqfvinam = '$tn' and t.indx = i.indx;
quit
EOF
)

t_addr=$(echo $t_addr|tr -d '\n ')
#echo x/s *0x${t_addr}
echo -e "set pagination off\nset print elements 0\n x/s *0x${t_addr}" | gdb -q -p $(pgrep ora_dbw0_${ORACLE_SID}) | grep '^(gdb)'

--//注:x/s 前面有1個*,表示指向地址的指標.沒有set print elements 0僅僅取出前面1段文字.

$ . ext_sql.sh 'GV$ACTIVE_SESSION_HISTORy'
(gdb) (gdb) (gdb) 0x16e985c0: "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.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) quit

$ . ext_sql.sh 'v$waitstat'
(gdb) (gdb) (gdb) 0x16e31ec0: "select class,count,time, con_id from gv$waitstat where inst_id = USERENV('Instance') "
(gdb) quit

$ . ext_sql.sh 'v$waitstat'| sed 's/^(gdb).*:\t"//;s/"$//;/quit$/d'
select class,count,time, con_id from gv$waitstat where inst_id = USERENV('Instance')

4.附上xind.sql指令碼:

$ cat xind.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name: xind.sql
-- Purpose: query X$ index
-- Author: lfree
--
-- Usage:
-- @ xind <x$table_name,...>
-- @ xind <x$kglob,x$kgldp>
--------------------------------------------------------------------------------
set termout off
column column_string new_value column_string format a200
select decode('&1','','1=1','1','1=1','1=1','1=1','table_name in ('||''''||replace(upper('&1'),',',''',''')||''')' ) column_string from dual ;
set termout on

select * from V$INDEXED_FIXED_COLUMN where 1=1 and (&column_string);