为了维护数据日志的数据，需要了解我们的应用执行情况以及语句执行的合理性和锁等状态，方便维护，可以使用特定命令来查看信息。首先在pl/sql的命令处导入，然后在命令窗口界面执行如下操作：
---根据sid 查@0si_sid
set timing on
set time on
column INST_ID format a5
column MACHINE format a10
column PROCESS format a8
column SQL_HASH_VALUE format a10
column CLIENT_INFO format a15
column PROGRAM format a50
select t.INST_ID,t.SID,t.MACHINE,t.PROCESS,t.SQL_HASH_VALUE, t.CLIENT_INFO, t.PROGRAM,t.LOGON_TIME
from gv$session t
where t.SID =&sid
/
---根据进程名字查 @0si_pro
set timing on
set time on
column INST_ID format a5
column MACHINE format a10
column PROCESS format a8
column SQL_HASH_VALUE format a10
column CLIENT_INFO format a15
column PROGRAM format a50
accept program prompt 'Pls enter program Value:'
prompt ====Sql program is '&&program'====
select t.INST_ID,t.SID,t.MACHINE,t.PROCESS,t.SQL_HASH_VALUE, t.CLIENT_INFO, t.PROGRAM
from gv$session t
where upper(t.PROGRAM) like upper('%&&program%')
/
---所有进程名字 @0si_all
--by luab
set timing on
set time on
column INST_ID format a5
column CLIENT_INFO format a15
select t.INST_ID,t.CLIENT_INFO,t.PROGRAM,count(1) from gv$session t
--where t.CLIENT_INFO is not null
group by rollup(t.INST_ID,t.CLIENT_INFO, t.PROGRAM)
order by t.INST_ID,t.CLIENT_INFO
/
---根据ip 地址来查 @0si_ip
set timing on
set time on
column INST_ID format a7
column SID format a5
column MACHINE format a15
column PROCESS format a8
column SQL_HASH_VALUE format a12
column CLIENT_INFO format a15
column PROGRAM format a60
select t.INST_ID,t.SID,t.MACHINE,t.PROCESS,t.SQL_HASH_VALUE, t.CLIENT_INFO, t.PROGRAM
from gv$session t
where t.CLIENT_INFO like '%&ip%'
/
---@0plan执行 计划输入sql_hash_value
set timing on
set time on
set echo off
set feedback off
set linesize 200
set pagesize 200
col sql_text for a120
accept hash_value prompt 'Pls enter Hash Value:'
prompt ====Sql Hash is '&&hash_value'====
set heading off
select '------------------------------------------------------------------------------------------' from dual
union all
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual
union all
select '------------------------------------------------------------------------------------------' from dual
union all
select *
from (select /*+ rule */
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '---------- '||hash_value||' ----------'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 30)), 31, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan
where hash_value = &&hash_value
and child_number = (select max(child_number) from v$sql_plan where hash_value = &&hash_value))
union all
select '------------------------------------------------------------------------------------------' from dual;
set heading on
set feedback on
---@0hash 输入sql_hash_value查看执行语句的内容
set timing on
set time on
set long 3000
set hea on feedback off
column inst format a4
column ver_cnt format 9999999
column sql_text format a255
column piece format 99999
column execs format 9999999999
accept hash_value prompt 'Pls enter Hash Value:'
prompt ====Sql Hash is '&&hash_value'====
select inst_id inst,sum(executions) execs, count(*) ver_cnt,hash_value from gv$sql where hash_value = '&&hash_value'
group by inst_id, hash_value
/
set hea off newp n feedback off
select sql_text
from (select hash_value, piece, sql_text
from v$sqltext_with_newlines
where hash_value = '&&hash_value')
order by piece
/
set hea on feedback on
---@0enq 查看锁
--V$LOCK ID1/ID2 lockid
set timing on
set time on
set linesize 120
set pagesize 120
col inst format 9999
col sess format a15
col prog format a25
col osuser format a8
col uname format a3
col lmode format a4
col request format a7
col BLOCK format a5
col obj format a20
col sql_hash_value format a14
col ctime format a6
--SELECT lk.inst_id inst,
-- DECODE(lk.request, 0, 'YHJ: ', 'ZHT: ') || lk.sid sess,
-- substr(se.program,1,25) prog,
-- se.osuser,
-- se.username uname,
-- lk.id1,
-- substr(ob.object_name,1,20) obj,
-- lk.id2,
-- se.sql_hash_value,
-- lk.lmode,
-- lk.request,
-- lk.type,
-- lk.block
-- FROM gV$LOCK lk,gv$session se,dba_objects ob
-- WHERE lk.inst_id = se.inst_id
-- and lk.sid = se.sid
-- and ob.object_id(+) = lk.id1
-- and (lk.id1, lk.id2, lk.type) IN (SELECT lk2.id1, lk2.id2, lk2.type FROM gV$LOCK lk2 WHERE lk2.request > 0)
-- ORDER BY lk.id1, lk.request
--/
SELECT /*+ordered*/ lk.inst_id inst,
DECODE(lk.request, 0, 'H: ', 'W: ') || se.sid||','||se.serial# sess,
substr(se.program,1,25) prog,
se.osuser,
se.username uname,
lk.id1,
lk.id2,
se.sql_hash_value,
lk.lmode,
lk.request,
lk.type,
lk.block,
lk.ctime
FROM gV$LOCK lk,gv$session se
WHERE lk.inst_id = se.inst_id
and lk.sid = se.sid
and (lk.id1, lk.id2, lk.type) IN (SELECT lk2.id1, lk2.id2, lk2.type FROM gV$LOCK lk2 WHERE lk2.request > 0)
ORDER BY lk.id1, lk.request
/
--select
-- inst_id,
-- decode(request, 0, 'holder:', 'waiter:') holder,
-- sid,
-- id1,
-- id2,
-- lmode,
-- request,
-- type,
-- ctime,
-- block
-- from gv$lock t
-- where (id1, id2, type) in(select id1, id2, type from gv$lock where request > 0)
-- order by t.REQUESt,t.CTIME desc
--/
---@0openc 查看打开的游标输入sid
set timing on
set time on
column INST_ID format a5
column SID format a5
column SADDR format a5
column SQL_TEXT format a100
define a=&sid
select a.INST_ID,a.SID,a.HASH_VALUE,b.SQL_TEXT from gv$open_cursor a, gv$sqlarea b
where a.INST_ID=b.INST_ID and a.HASH_VALUE=b.HASH_VALUE
and a.SID=&a
/
select t.SID,count(1) from gv$open_cursor t where t.SID=&a
group by t.sid
/
---@0sum所有的等待时间
set timing on
set time on
set linesize 312
column inst format 9999
column event format a60
column cnt format 99999
column wt_tm format 99999
select inst_id,event, count(*) cnt,sum(wait_time) wt_tm
from gv$session_wait
where event not like '%SQL_Net%' and
event not like '%rdbms%' and
event not like '%mon timer%'
group by inst_id,event
order by inst_id,cnt desc
/
--select inst_id inst,event,count(*) cnt,sum(wait_time) wt_tm,p1text,p1,p1raw,p2text,p2,p2raw,p3text,p3,p3raw from gv$session_wait
--where event not like '%SQL%'
--and event not like '%rdbms%' and event not like '%time%' and event not like 'pipe get%' and event not like 'queue messages%' and event not like 'jobq slave wait' and event not like 'PX Deq: Execution Msg' and event not like 'PX Deq: Execute Reply'
--group by inst_id,event,p1text,p1,p1raw,p2text,p2,p2raw,p3text,p3,p3raw order by 11 desc
--/
---@0anal 表分析
set timing on
set time on
col sql format a120
accept owner prompt 'owner'
prompt ====owner is '&&owner'====
accept table_name prompt 'table_name'
prompt ====owner is '&&table_name'====
accept degree prompt 'degree'
prompt ====degree is '&°ree'====
select 'dbms_stats.gather_table_stats ( ownname=>upper("&owner"), tabname=> upper("&table_name"), method_opt=>"FOR ALL COLUMNS SIZE 1", estimate_percent=>25,degree=>"°ree",granularity=>"all", no_invalidate=>false,cascade=>true);' sql from dual;
exec dbms_stats.gather_table_stats ( ownname=>upper('&&owner'), tabname=> upper('&&table_name'), method_opt=>'FOR ALL COLUMNS SIZE 1', estimate_percent=>25,degree=>'&°ree',granularity=>'all', no_invalidate=>false,cascade=>true);
/
---@0w_ev 等待事件的名称
set timing on
set time on
set linesize 312
column inst format 9999
column sid format 9999999
column obj# format 99999999
column prog format a25
column hash_value a25
column process format 99999999
column wt_secs format 9999999
column p1 format a20
column p2 format a18
accept ev prompt 'Pls enter EventName:'
prompt ====Event Name is '&ev'====
select s.inst_id inst,
s.sid,
substr(s.program, 1, 25) prog,
s.process,
s.row_wait_obj# obj#,
s.sql_hash_value hash_value,
w.seconds_in_wait wt_secs,
w.p1,
w.p2
from gv$session_wait w, gv$session s
where w.event = '&ev'
and s.sid = w.sid
and s.inst_id = w.inst_id
/
---@0p查看表的统计信息
set timing on
set time on
set trimspool on
set long 5000
set linesize 131
set pagesize 9999
set serveroutput on size 1000000 format wrapped
declare
v_table_name varchar2(64);
v_table_owner varchar2(32);
v_part_property varchar2(128);
v_cols varchar2(96);
b_show_idx number;
n_mbytes number;
cursor c1(para1 varchar2, para2 varchar2) is
select *
from all_tables
where table_name = upper(para1)
and owner = upper(para2);
r_c1 c1%rowtype;
cursor c2(para1 varchar2, para2 varchar2) is
select *
from all_indexes
where table_name = upper(para1)
and table_owner = upper(para2);
r_c2 c2%rowtype;
cursor c3(para1 varchar2, para2 varchar2) is
select *
from all_ind_columns
where index_name = upper(para1)
and index_owner = upper(para2)
order by column_position;
r_c3 c3%rowtype;
cursor c4(para1 varchar2, para2 varchar2) is
select *
from all_tab_partitions
where table_name = upper(para1)
and table_owner = upper(para2)
order by partition_position;
r_c4 c4%rowtype;
cursor c5(para1 varchar2, para2 varchar2) is
select *
from all_ind_partitions
where index_name = upper(para1)
and index_owner = upper(para2)
order by partition_position;
r_c5 c5%rowtype;
cursor c6(para1 varchar2, para2 varchar2, para3 varchar2) is
select column_name
from all_part_key_columns
where object_type = upper(para1)
and name = upper(para2)
and owner = upper(para3)
order by column_position;
r_c6 c6%rowtype;
procedure p(p1 in varchar2, p2 in varchar2) is
begin
dbms_output.put_line(rpad(p1, 15, '.') || p2);
end;
begin
v_table_owner := '&table_owner';
v_table_name := '&table_name';
b_show_idx := '&b_show_idx';
open c1(v_table_name, v_table_owner);
loop
fetch c1
into r_c1;
exit when c1%NOTFOUND;
if r_c1.partitioned = 'YES' then
select '(' || partitioning_type || ')'
into v_part_property
from all_part_tables
where table_name = r_c1.table_name
and owner = r_c1.owner;
/*
select '(' ||
ltrim(max(sys_connect_by_path(column_name, ',')), ',') || ')'
into v_cols
from (select column_name,
column_position,
column_position - 1 pre
from all_part_key_columns
where object_type = 'TABLE'
and name = r_c1.table_name
and owner = r_c1.owner)
connect by prior column_position = pre
start with column_position = 1;
*/
for a in c6('TABLE', r_c1.table_name, r_c1.owner) loop
v_cols := v_cols || a.column_name || ',';
end loop;
v_cols := '(' || rtrim(v_cols, ',') || ')';
p('table_name',
r_c1.owner||'.'||r_c1.table_name || ' (@Partitioned)' || v_part_property ||
v_cols||'('||'dg='|| trim(r_c1.degree)||')');
v_cols := '';
open c4(r_c1.table_name, r_c1.owner);
loop
fetch c4
into r_c4;
exit when c4%NOTFOUND;
select round(bytes / 1024 / 1024, 2)
into n_mbytes
from dba_segments
where segment_name = upper(r_c1.table_name)
and owner = upper(r_c1.owner)
and partition_name = upper(r_c4.partition_name);
if r_c4.global_stats = 'YES' then
p('Tpart:' || lpad(r_c4.partition_position, 3, ' '),
r_c4.partition_name || ' (sz=' || n_mbytes || ';tb=' ||
r_c4.tablespace_name || ';pf=' || r_c4.pct_free || ';lg='||r_c4.logging||
';nr=' || r_c4.num_rows || ';la=' ||
to_char(r_c4.last_analyzed, 'yymmddhh24miss') || ')');
else
p('Tpart:' || lpad(r_c4.partition_position, 3, ' '),
r_c4.partition_name || '(sz=' || n_mbytes || ';tb=' ||
r_c4.tablespace_name || ';pf=' || r_c4.pct_free || ';lg='||r_c4.logging||')');
end if;
end loop;
close c4;
else
--p('table_name', r_c1.owner||'.'||r_c1.table_name);
select round(bytes / 1024 / 1024, 2)
into n_mbytes
from dba_segments
where segment_name = upper(v_table_name)
and owner = upper(v_table_owner)
and segment_type='TABLE';
if r_c1.global_stats = 'YES' then
p('table_name',
r_c1.owner||'.'||r_c1.table_name || ' (sz=' || n_mbytes || ';tb=' ||
r_c1.tablespace_name || ';pf=' || r_c1.pct_free ||';dg='|| trim(r_c1.degree) || ';lg='||r_c1.logging|| ';nr=' ||
r_c1.num_rows || ';la=' ||
to_char(r_c1.last_analyzed, 'yymmddhh24miss') || ')');
else
p('table_name',
r_c1.owner||'.'||r_c1.table_name || ' (sz=' || n_mbytes || ';tb=' ||
r_c1.tablespace_name || ';pf=' || r_c1.pct_free ||';dg='|| trim(r_c1.degree) || ';lg='||r_c1.logging|| ')');
end if;
end if;
if b_show_idx = 1 then
open c2(v_table_name, v_table_owner);
loop
fetch c2
into r_c2;
exit when c2%NOTFOUND;
if r_c2.partitioned = 'YES' then
select '(' || partitioning_type || '-' || locality || '-' ||
alignment || ')'
into v_part_property
from all_part_indexes
where index_name = r_c2.index_name
and owner = r_c2.owner
and table_name = r_c2.table_name;
/*
select '(' ||
ltrim(max(sys_connect_by_path(column_name, ',')),
',') || ')'
into v_cols
from (select column_name,
column_position,
column_position - 1 pre
from all_part_key_columns
where object_type = 'INDEX'
and name = r_c2.index_name
and owner = r_c2.owner)
connect by prior column_position = pre
start with column_position = 1;
*/
for a in c6('INDEX', r_c2.index_name, r_c2.owner) loop
v_cols := v_cols || a.column_name || ',';
end loop;
v_cols := '(' || rtrim(v_cols, ',') || ')';
p(' index_name',
r_c2.owner||'.'||r_c2.index_name || ' (@Partitioned)' ||'('||r_c2.uniqueness||')'||
v_part_property || v_cols||'('||'dg='|| trim(r_c2.degree)||')');
v_cols := '';
open c5(r_c2.index_name, r_c2.owner);
loop
fetch c5
into r_c5;
exit when c5%NOTFOUND;
select round(bytes / 1024 / 1024, 2)
into n_mbytes
from dba_segments
where segment_name = r_c2.index_name
and owner = r_c2.owner
and partition_name = r_c5.partition_name;
if r_c5.global_stats = 'YES' then
p(' Ipart:' ||
lpad(r_c5.partition_position, 3, ' '),
r_c5.partition_name || ' (sz=' || n_mbytes ||
';tb=' || r_c5.tablespace_name || ';pf=' ||
r_c5.pct_free || ';lg='||r_c5.logging|| ';nr=' || r_c5.num_rows ||
';bl=' || r_c5.blevel || ';lb=' ||
r_c5.leaf_blocks || ';dk=' ||
r_c5.distinct_keys || ';cf=' ||
r_c5.clustering_factor || ';la=' ||
to_char(r_c5.last_analyzed, 'yymmddhh24miss') || ')');
else
p(' Ipart:' ||
lpad(r_c5.partition_position, 3, ' '),
r_c5.partition_name || ' (sz=' || n_mbytes ||
';tb=' || r_c5.tablespace_name || ';pf=' ||
r_c5.pct_free || ';lg='||r_c5.logging|| ')');
end if;
end loop;
close c5;
else
--p(' index_name', r_c2.index_name);
select round(bytes / 1024 / 1024, 2)
into n_mbytes
from dba_segments
where segment_name = upper(r_c2.index_name)
and owner = upper(r_c2.owner)
and segment_type='INDEX';
if r_c2.global_stats = 'YES' then
p(' index_name',
r_c2.owner||'.'||r_c2.index_name || ' (sz=' || n_mbytes || ';tb=' ||
r_c2.tablespace_name || ';pf=' || r_c2.pct_free ||
';uq=' || r_c2.uniqueness || ';dg='|| trim(r_c2.degree) || ';lg='||r_c2.logging|| ';nr=' ||
r_c2.num_rows || ';bl=' || r_c2.blevel || ';lb=' ||
r_c2.leaf_blocks || ';dk=' || r_c2.distinct_keys ||
';cf=' || r_c2.clustering_factor || ';la=' ||
to_char(r_c2.last_analyzed, 'yymmddhh24miss') || ')');
else
p(' index_name',
r_c2.owner||'.'||r_c2.index_name || ' (sz=' || n_mbytes || ';tb=' ||
r_c2.tablespace_name || ';pf=' || r_c2.pct_free ||
';uq=' || r_c2.uniqueness ||';dg='|| trim(r_c2.degree) || ';lg='||r_c2.logging|| ')');
end if;
end if;
open c3(r_c2.index_name, r_c2.owner);
loop
fetch c3
into r_c3;
exit when c3%NOTFOUND;
p(' idx_cols', r_c3.column_name);
end loop;
close c3;
dbms_output.put_line(' ------');
end loop;
close c2;
end if;
end loop;
close c1;
exception
when others then
dbms_output.put_line('--sqlcode:' || to_char(sqlcode) ||
' ; sqlerrm:' || sqlerrm || '--');
end;
/
set serveroutput off
3641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
