Oracle 10g Top_5sql

Oracle top 5sql
Oracle top sql



SQL> select sysdate from dual;

SYSDATE
---------
24-FEB-11



exec top_stmt5('21-feb-11','2');


for two days



from web

http://www.evdbt.com/top_stmt5_10g.sql



set echo on feedback on timing on

spool top_stmt5_10g

create or replace procedure top_stmt5
(
in_start_date in date,
in_nbr_days in number,
in_top_count in integer default 10,
in_instance_nbr in number DEFAULT NULL,
in_max_disk_reads in integer default 10000,
in_max_buffer_gets in integer default 100000
) is
--
cursor get_top_stmts(in_dr in integer, in_bg in integer,
in_dbid in integer, in_inst_nbr in integer,
in_begin_time in timestamp, in_end_time in timestamp)
is
select sq.sql_id,
sq.module,
st.command_type,
sum(sq.disk_reads_delta) disk_reads,
sum(sq.buffer_gets_delta) buffer_gets,
sum(sq.cpu_time_delta)/1000000 cpu_time,
sum(sq.elapsed_time_delta)/1000000 elapsed_time,
sum(sq.executions_delta) executions,
(1 - (sum(sq.disk_reads_delta) / sum(sq.buffer_gets_delta)))*100 bchr,
sum(sq.disk_reads_delta) / sum(sq.executions_delta) dr_per_exe,
sum(sq.buffer_gets_delta) / sum(sq.executions_delta) bg_per_exe,
sum(sq.cpu_time_delta)/1000000 / sum(sq.executions_delta) cpu_per_exe,
sum(sq.elapsed_time_delta)/1000000 / sum(sq.executions_delta) ela_per_exe,
((sum(sq.disk_reads_delta)*100)+sum(sq.buffer_gets_delta))/100 factor
from dba_hist_sqltext st,
dba_hist_sqlstat sq,
dba_hist_snapshot ss
where ss.dbid = in_dbid
and ss.instance_number = nvl(in_inst_nbr, ss.instance_number)
and ss.begin_interval_time between in_begin_time and in_end_time
and sq.dbid = ss.dbid
and sq.instance_number = ss.instance_number
and sq.snap_id = ss.snap_id
and st.sql_id = sq.sql_id
group by sq.sql_id,
sq.module,
st.command_type
having (sum(sq.disk_reads_delta) > in_dr
or sum(sq.buffer_gets_delta) > in_bg)
and sum(sq.buffer_gets_delta) > 0
and sum(sq.executions_delta) > 0
order by factor desc;
--
cursor get_sql_plan(in_dbid in number,
in_sql_id in varchar2,
in_begin_time in timestamp,
in_end_time in timestamp)
is
select distinct plan_hash_value, timestamp
from dba_hist_sql_plan
where dbid = in_dbid
and sql_id = in_sql_id
and timestamp between in_begin_time and in_end_time
order by 2;
--
v_text_lines integer;
v_sql_text clob;
v_sql_text_len integer;
n integer;
v_tot_logr integer;
v_tot_phyr integer;
v_sql_tot_cnt integer := 0;
v_sql_tot_dr integer := 0;
v_sql_tot_bg integer := 0;
v_sql_tot_cpu integer := 0;
v_sql_tot_ela integer := 0;
v_plsql_tot_cnt integer := 0;
v_plsql_tot_dr integer := 0;
v_plsql_tot_bg integer := 0;
v_plsql_tot_cpu integer := 0;
v_plsql_tot_ela integer := 0;
v_dbid integer;
v_instance_nbr integer;
v_begin_snapshot timestamp;
v_end_snapshot timestamp;
v_nbr_snapshots integer;
v_nbr_instances integer;
v_plan_id integer;
--
v_errcontext varchar2(100);
v_errmsg varchar2(512);
v_save_module varchar2(48);
v_save_action varchar2(32);
--
begin
--
dbms_application_info.read_module(v_save_module, v_save_action);
v_errcontext := 'query dba_hist_database_instance';
dbms_application_info.set_module('TOP_STMT5', v_errcontext);
select h.dbid, decode(in_instance_nbr, null, null, i.instance_number), count(distinct i.instance_number)
into v_dbid, v_instance_nbr, v_nbr_instances
from dba_hist_database_instance h,
gv$instance i
where h.instance_number = i.instance_number
and i.instance_number = nvl(in_instance_nbr, i.instance_number)
group by h.dbid, decode(in_instance_nbr, null, null, i.instance_number);
--
v_errcontext := 'query dba_hist_snapshot';
dbms_application_info.set_action(v_errcontext);
select min(begin_interval_time),
max(end_interval_time),
count(*)
into v_begin_snapshot,
v_end_snapshot,
v_nbr_snapshots
from dba_hist_snapshot
where begin_interval_time between in_start_date and (in_start_date + in_nbr_days)
and dbid = v_dbid
and instance_number = nvl(v_instance_nbr, instance_number);
--
v_errcontext := 'query dba_hist_sysstat';
dbms_application_info.set_action(v_errcontext);
select sum(cg.value_delta+dbg.value_delta),
sum(p.value_delta)
into v_tot_logr,
v_tot_phyr
from (select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'consistent gets') cg,
(select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'db block gets') dbg,
(select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'physical reads') p,
dba_hist_snapshot s
where s.begin_interval_time between in_start_date and (in_start_date + in_nbr_days)
and s.dbid = v_dbid
and s.instance_number = nvl(v_instance_nbr, s.instance_number)
and cg.snap_id = s.snap_id
and cg.dbid = s.dbid
and cg.instance_number = s.instance_number
and dbg.snap_id = s.snap_id
and dbg.dbid = s.dbid
and dbg.instance_number = s.instance_number
and p.snap_id = s.snap_id
and p.dbid = s.dbid
and p.instance_number = s.instance_number;
--
v_errcontext := 'open/fetch get_top_stmts';
dbms_application_info.set_action(v_errcontext);
for a in get_top_stmts(in_max_disk_reads, in_max_buffer_gets,
v_dbid, v_instance_nbr,
v_begin_snapshot, v_end_snapshot) loop
--
if get_top_stmts%rowcount > in_top_count then
--
exit;
--
end if;
--
v_errcontext := 'put_line formfeed';
dbms_application_info.set_action(v_errcontext);
if get_top_stmts%rowcount > 1 then
--
dbms_output.put_line(chr(12));
--
end if;
--
v_errcontext := 'put_line statement header';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line(rpad('Beginning Snap Time: ',30) ||
to_char(v_begin_snapshot, 'MM/DD/YY HH24:MI:SS') ||
lpad('Page ' ||
to_char(get_top_stmts%rowcount,'990'),60));
dbms_output.put_line(rpad('Ending Snap Time : ',30) ||
to_char(v_end_snapshot, 'MM/DD/YY HH24:MI:SS') ||
lpad('Nbr of Snapshots: ' ||
to_char(v_nbr_snapshots,'990'),60));
dbms_output.put_line(rpad('Date of Report : ',30) ||
to_char(sysdate, 'MM/DD/YY HH24:MI:SS') ||
lpad('Nbr of Instances: ' ||
to_char(v_nbr_instances,'990'),60));
dbms_output.put_line(rpad('Total Logical Reads: ', 23) ||
to_char(v_tot_logr,'999,999,999,999,999,990') ||
lpad('Total Physical Reads: ' ||
to_char(v_tot_phyr,'999,999,999,999,999,990'), 60));
dbms_output.put_line('.');
--
if a.module is not null then
v_errcontext := 'display module';
dbms_output.put_line('Module: "' || a.module || '"');
dbms_output.put_line('.');
end if;
--
dbms_output.put_line('SQL Statement Text (SQL ID=' || a.sql_id || ')');
dbms_output.put_line('-------------------------------' || rpad('-', length(trim(to_char(a.sql_id))), '-') || '-');
--
v_errcontext := 'get sql_text from dba_hist_sqltext';
dbms_application_info.set_action(v_errcontext);
select sql_text,
dbms_lob.getlength(sql_text) len
into v_sql_text,
v_sql_text_len
from dba_hist_sqltext
where sql_id = a.sql_id;
v_text_lines := 1;
n := 1;
while n < v_sql_text_len loop
--
dbms_output.put_line(rpad(to_char(v_text_lines),6) ||
replace(dbms_lob.substr(v_sql_text, 100, n),chr(10),null));
n := n + 100;
v_text_lines := v_text_lines + 1;
--
v_errcontext := 'fetch/close get_text';
--
end loop;
--
v_errcontext := 'put_line statement totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line(':' ||
lpad('Disk ',16) ||
lpad('Buffer',16) ||
lpad('Cache Hit',10) ||
lpad(' ',11) ||
lpad('DR Per',12) ||
lpad('BG Per',12) ||
lpad('CPU Per',15) ||
lpad('Ela Per',15));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',16) ||
lpad('Ratio',10) ||
lpad('Runs',11) ||
lpad('Run',12) ||
lpad('Run',12) ||
lpad('Run',15) ||
lpad('Run',15));
dbms_output.put_line(':' ||
lpad('-----',16) ||
lpad('------',16) ||
lpad('---------',10) ||
lpad('----',11) ||
lpad('------',12) ||
lpad('------',12) ||
lpad('------',15) ||
lpad('------',15));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(a.disk_reads,'999,999,999,990')),16) ||
lpad(ltrim(to_char(a.buffer_gets,'999,999,999,990')),16) ||
lpad(ltrim(to_char(a.bchr,'990.00')||'%'),10) ||
lpad(ltrim(to_char(a.executions,'99,999,990')),11) ||
lpad(ltrim(to_char(a.dr_per_exe,'999,999,990')),12) ||
lpad(ltrim(to_char(a.bg_per_exe,'999,999,990')),12) ||
lpad(ltrim(to_char(a.cpu_per_exe,'999,999,990.00')),15) ||
lpad(ltrim(to_char(a.ela_per_exe,'999,999,990.00')),15));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((a.disk_reads/v_tot_phyr)*100,3),
'990.000'))||'%)',16) ||
lpad('('||ltrim(to_char(round((a.buffer_gets/v_tot_logr)*100,3),
'990.000'))||'%)',16));
--
v_errcontext := 'open/fetch get_sql_plan';
dbms_application_info.set_action(v_errcontext);
for p in get_sql_plan(v_dbid, a.sql_id, v_begin_snapshot, v_end_snapshot) loop
--
v_text_lines := 0;
v_errcontext := 'open/fetch get_xplan';
dbms_application_info.set_action(v_errcontext);
for s in (select plan_table_output
from table(dbms_xplan.display_awr(a.sql_id, p.plan_hash_value, v_dbid, 'ALL'))) loop
--
if s.plan_table_output like 'Plan hash value: %' then
v_text_lines := 1;
end if;
--
if v_text_lines = 1 then
dbms_output.put_line('.');
dbms_output.put_line('. SQL execution plan from "'||
to_char(p.timestamp,'MM/DD/YY HH24:MI:SS') || '"');
end if;
--
if v_text_lines >= 1 then
dbms_output.put_line(s.plan_table_output);
v_text_lines := v_text_lines + 1;
end if;
--
end loop;
--
v_errcontext := 'fetch/close get_sql_plan';
--
end loop;
--
if a.command_type = 47 then
--
v_plsql_tot_cnt := v_plsql_tot_cnt + 1;
v_plsql_tot_dr := v_plsql_tot_dr + a.disk_reads;
v_plsql_tot_bg := v_plsql_tot_bg + a.buffer_gets;
v_plsql_tot_cpu := v_plsql_tot_cpu + a.cpu_time;
v_plsql_tot_ela := v_plsql_tot_ela + a.elapsed_time;
--
else
--
v_sql_tot_cnt := v_sql_tot_cnt + 1;
v_sql_tot_dr := v_sql_tot_dr + a.disk_reads;
v_sql_tot_bg := v_sql_tot_bg + a.buffer_gets;
v_sql_tot_cpu := v_sql_tot_cpu + a.cpu_time;
v_sql_tot_ela := v_sql_tot_ela + a.elapsed_time;
--
end if;
--
v_errcontext := 'fetch/close get_top_stmt';
dbms_application_info.set_action(v_errcontext);
--
end loop;
--
if v_sql_tot_cnt > 0 then
--
v_errcontext := 'put_line SQL cumulative totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line(': =============================================================================');
dbms_output.put_line(':');
dbms_output.put_line(': >>> CUMULATIVE TOTALS FOR '||v_sql_tot_cnt||' "TOP ' || in_top_count || '" SQL STATEMENTS <<<');
dbms_output.put_line(':');
dbms_output.put_line(':' ||
lpad('Disk ',16) ||
lpad('Buffer',20) ||
lpad('Cache Hit',10) ||
lpad('CPU',20) ||
lpad('Elapsed',20));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',20) ||
lpad('Ratio',10) ||
lpad('Time',20) ||
lpad('Time',20));
dbms_output.put_line(':' ||
lpad('-----',16) ||
lpad('------',20) ||
lpad('---------',10) ||
lpad('---------',20) ||
lpad('---------',20));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(v_sql_tot_dr,'999,999,999,990')),16) ||
lpad(ltrim(to_char(v_sql_tot_bg,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char((1 - (v_sql_tot_dr/v_sql_tot_bg))*100,'990.00')||'%'),10) ||
lpad(ltrim(to_char(v_sql_tot_cpu,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_sql_tot_ela,'999,999,999,999,990')),20));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((v_sql_tot_dr/v_tot_phyr)*100,3),
'990.000'))||'%)',16) ||
lpad('('||ltrim(to_char(round((v_sql_tot_bg/v_tot_logr)*100,3),
'990.000'))||'%)',20));
--
end if;
--
if v_plsql_tot_cnt > 0 then
--
v_errcontext := 'put_line PLSQL cumulative totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line(': =============================================================================');
dbms_output.put_line(':');
dbms_output.put_line(': >>> CUMULATIVE TOTALS FOR '||v_plsql_tot_cnt||' "TOP '||in_top_count||'" PL/SQL STATEMENTS <<<');
dbms_output.put_line(':');
dbms_output.put_line(':' ||
lpad('Disk ',20) ||
lpad('Buffer',20) ||
lpad('Cache Hit',10) ||
lpad('CPU',20) ||
lpad('Elapsed',20));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',20) ||
lpad('Ratio',10) ||
lpad('Time',20) ||
lpad('Time',20));
dbms_output.put_line(':' ||
lpad('-----',20) ||
lpad('------',20) ||
lpad('---------',10) ||
lpad('---------',20) ||
lpad('---------',20));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(v_plsql_tot_dr,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_plsql_tot_bg,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char((1 - (v_plsql_tot_dr/v_plsql_tot_bg))*100,'990.00')||'%'),10) ||
lpad(ltrim(to_char(v_plsql_tot_cpu,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_plsql_tot_ela,'999,999,999,999,990')),20));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((v_plsql_tot_dr/v_tot_phyr)*100,3),
'990.000'))||'%)',20) ||
lpad('('||ltrim(to_char(round((v_plsql_tot_bg/v_tot_logr)*100,3),
'990.000'))||'%)',20));
--
end if;
--
rollback;
--
dbms_application_info.set_module(v_save_module, v_save_action);
--
exception
when others then
v_errmsg := sqlerrm;
dbms_application_info.set_module(v_save_module, v_save_action);
rollback;
raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);
end top_stmt5;
/
show errors
spool off

Oracle sqlplus Unix nohup

nohup sqlplus '/ as sysdba' @Run_gather_stats.sql > Run_gather_stats_`date '+%d%m%y_%H%M%S'`.log 2>&1 &

nohup sqlplus prod1/prod1 @index.sql > index.out 2>&1 &

nohup /export/home/oracle/restore.sh > /export/home/oracle/restore_`date '+%Y%m%d_%H%M%S'`.log 2>&1 &


https://anuj-singh.blogspot.com/search?q=catcon.pl


====

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -C 'PDB$SEED' -b ss ss.sql &

jobs -l
[1]+ 1855587 Running                 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -C 'PDB$SEED' -b ss ss.sql &


===
 cat ss.sql

SET VERIFY OFF time on timing on

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

select sysdate from dual;


prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()
prompt ========================================

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS')
prompt ========================================


EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');


prompt ========================================
prompt DBMS_STATS.GATHER_DICTIONARY_STATS
prompt ========================================

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();

select sysdate from dual;

--spool off

===



nohup sqlplus '/ as sysdba' @/home/oracle/st.sql > /home/oracle/st.log 2>&1 &

st.sql <<<

set time on timing on 
alter session set container=vihaan ;

EXEC DBMS_STATS.gather_table_stats(ownname => 'ANUJ', tabname => 'AUDITLOG', GRANULARITY => 'ALL', estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE, degree=> 6);

How To Estimate Oracle Text Index Fragmentation ?

is Oracle text index Fragmented ?


SYS AS SYSDBA>set linesize 200
select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where INDEX_TYPE='DOMAIN'



INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------- --------------------------------------------------------------------------------- ---------------
SYSOBJECT_FULLTEXT DOMAIN DM_SYSOBJECT_S



Text I

SYS AS SYSDBA>select table_name,OWNER from dba_tables where table_name like 'DR$SYS%';

or

select table_name,OWNER from dba_tables where table_name like '%SYSOBJECT_FULLTEXT%'


SYS AS SYSDBA>select table_name,owner from dba_tables where table_name like '%SYSOBJECT_FULLTEXT%' ;

TABLE_NAME OWNER
------------------------------ ---------------
DR$SYSOBJECT_FULLTEXT$P PROD1
DR$SYSOBJECT_FULLTEXT$I PROD1 <<<--- we have to use this table
DR$SYSOBJECT_FULLTEXT$K PROD1
DR$SYSOBJECT_FULLTEXT$R PROD1
DR$SYSOBJECT_FULLTEXT$N PROD1




select avg(tfrag) from ( select /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */ i.token_text,
(1-(least(round((sum(dbms_lob.getlength(i.token_info))/3800)+(0.50 - (1/3800))),count(*))/count(*)))*100 tfrag
from ( select token_text, token_type from prod1.dr$SYSOBJECT_FULLTEXT$i sample(0.149)
where rownum <= 100 ) t, prod1.dr$SYSOBJECT_FULLTEXT$i i
where i.token_text = t.token_text
and i.token_type = t.token_type
group by i.token_text, i.token_type);



AVG(TFRAG)
----------
12.6856968




if result is more then 40 % the your index is fragmented .

Oracle Text Index statistics report / fragmentation report

Here is an example of using CTX_REPORT.INDEX_STATS for text Index


create this table
create table output (result CLOB);

or

if already there

truncate table OUTPUT ;

from sys


prompt create table output (result CLOB);

truncate table OUTPUT ;

declare
x clob := null;

begin

ctx_report.index_stats('PROD1.SYSOBJECT_FULLTEXT',x);
insert into output values (x);
commit;
dbms_lob.freetemporary(x);
end;
/



set this line to read clob

spool text_stat_report

SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
select * from output;

spool off

Oracle RMAN Configuring Tablespaces for Exclusion/exclude

RMAN exclude Tablespace

The exclusion condition applies to any datafiles that you add to this tablespace in the future.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE XXXX;

XXXX <<<<<<<--- tablespace name

RMAN will not take backup of XXXX tablespace


To Clear exclusion .....

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE XXXX CLEAR ;

RMAN> SHOW EXCLUDE;

Oracle Date Time - How to add second Minute hour

Example using NUMTODSINTERVAL with SYSDATE:



Example-- Add 20 second in system time

select to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),to_char (SYSDATE + NUMTODSINTERVAL(20,'SECOND'),'dd/mm/yyyy hh:mi:ss') from dual;


One of the string constants: 'DAY', 'HOUR', 'MINUTE', or 'SECOND'.

Oracle dynamic_sampling and level

/*+ dynamic_sampling(Table_name 2) */
/*+ dynamic_sampling (Table_name 4) */


Level 1: Samples tables that appear in join or subquery conditions that have no indexes and
have more blocks than 32,the default for dynamic sampling.

Level 2 (default): Samples all unanalyzed tables that have more than 32 blocks.

Level 3: Samples tables using a single column that applies selectivity to the table being
sampled.

Level 4: Samples tables using two or more columns that apply selectivity to the table being
sampled.

Level 5: Doubles the dynamic sample size and samples 64 blocks on tables.

Level 6: Quadruples the dynamic sample size and samples 128 blocks on tables.

Level 7: Samples 256 blocks on tables.

Level 8: Samples 1,024 blocks on tables.

Level 9: Samples 4,096 blocks on tables.

Level 10: Samples all of the block in the tables.