Skip to content

Posts from Май 2016

how to vizualize table usage by queries with awr and exel

how to vizualize table usage by queries with awr and exel published on Комментариев к записи how to vizualize table usage by queries with awr and exel нет

generate pivot script:

1
select listagg( ''''||sql_id||'''',',') within group (order by 1 )from (select distinct (sql_id) from  dba_hist_sql_plan where object_name='OPN_HIS' ) ;

use info in awr to build a graph

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select  * from (
select s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
   and st.sql_id=s.sql_id
   and st.sql_text not like '/* SQL Analyze%'
   and s.sql_id in  ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS')
and ss.begin_interval_time > sysdate-7
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
pivot ( sum(execs)  for sql_id in (
'04chua8g507qc','0ppqkga09s858','13nb5fgqggy5y','2y61btbkgbt99','3akp3rn03dw0m','4hwvc5b6kg9s9','5btym214ffk95','5fjnq328z8cvm','5haf8w0xm1qrz','5yrbchx0n2ww9','70qkbhpxdyhmd',
'7459pp1x54x2g','7hap4j3ar5fu8','9ym4u1trvzcq8','aabhxfrznx8ty','b4ynhjjwwdjf9','bp0sfgzv774u4','btm5kxhrc34qy','bxfhw0z4cnmxp','by4vbkw71nrax','dpnr7csjy3657','f3n5pm0ck9yuw','f452wsrxch6js' )
  );

result :

Screen Shot 2016-05-31 at 12.05.45

script to find hidden param + description

script to find hidden param + description published on Комментариев к записи script to find hidden param + description нет
1
2
3
4
5
6
7
8
9
col "Parameter" for a50
col "Session Value" for a10
col "Instance Value" for a10
col "Default value" for a5
col SESSMOD for a7
col SYSMOD for a9
col "Description" for a95
 
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" ,b.ksppstdf "Default value", decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,a.ksppdesc "Description" from sys.x$ksppi a, sys.x$ksppcv b , sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and upper(ksppinm) like upper( '_%&1%') order by a.ksppinm;

Script to capture and restore file permission in a directory

Script to capture and restore file permission in a directory published on Комментариев к записи Script to capture and restore file permission in a directory нет
Script to capture and restore file permission in a directory (for eg. ORACLE_HOME)

how to find what was in dba_2pc_pending transaction

how to find what was in dba_2pc_pending transaction published on Комментариев к записи how to find what was in dba_2pc_pending transaction нет

generate logminer list:

1
select completion_time,'exec DBMS_LOGMNR.ADD_LOGFILE('''||name||''');' from v$archived_log where completion_time > trunc(sysdate) +9/24 and  completion_time < trunc(sysdate) +11/24 and dest_id=1;

build catalog

1
EXEC DBMS_LOGMNR.START_LOGMNR(   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

find out transaction content:

1
2
3
4
5
6
select global_tran_id,local_tran_id,l.* from (
 (
select global_tran_id,local_tran_id,
from DBA_2PC_PENDING) T
JOIN  V$LOGMNR_CONTENTS L ON (l.xidusn||'.'||l.xidslot||'.'||l.xidsqn)=t.local_tran_id
)

commit\rollback:

1
2
3
select ' commit force '''||local_tran_id||''';' from dba_2pc_pending ;
--select ' rolback force '''||local_tran_id||''';' from dba_2pc_pending ;
select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||local_tran_id||'''); commit;' from dba_2pc_pending ;

if archivelogs are on tape:

1
2
3
4
5
6
select sum(blocks*block_size)/1024/1024/1024 gb_size, THREAD#, min(sequence#),max(sequence#) from gv$archived_log where dest_id=1 and first_time > trunc(sysdate) +9/24
and first_time < trunc(sysdate) +11/24 group by THREAD# ;
 
run {
restore archivelog thread 1 from sequence 3925 until sequence 3938;
}

generate ash report from sqlplus

generate ash report from sqlplus published on Комментариев к записи generate ash report from sqlplus нет

plan to make a script with time range as input values

1
19:22:41 (1)system@xxxxx> select * from table (dbms_workload_repository.ash_global_report_html( 4281370344,1,timestamp'2016-05-05 13:50:00',timestamp'2016-05-05 14:05:00') );

open in browser:
add for mac os x

1
host open %SQLPATH%\tmp\output_&_connect_identifier..html

add for linux:

1
host firefox %SQLPATH%\tmp\output_&_connect_identifier..html --linux

Primary Sidebar