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 .
Thursday, 17 February 2011
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
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
Monday, 14 February 2011
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;
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'.
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'.
Wednesday, 9 February 2011
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.
/*+ 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.
Tuesday, 8 February 2011
Oracle version count info metalink 438755.1
High SQL Version Counts - Script to determine reason(s) (Doc ID 438755.1)
create or replace view SQL_SHARED_CURSOR
as select * from sys.v$sql_shared_cursor;
create or replace view h$pseudo_cursor as
select Pseudo_cursor, sql_id,obj_id hex_obj_id
,obj# object_id, u.name owner, o.name object_name
,address,hash_value,SHARABLE_MEM,parse_calls,VERSION_COUNT,is_obsolete
from (select distinct
KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
,KGLHDPAR address,KGLNAHSH hash_value
,KGLOBHS0+KGLOBHS1+KGLOBHS2+KGLOBHS3+KGLOBHS4+KGLOBHS5+KGLOBHS6 SHARABLE_MEM
,KGLOBT12 parse_calls
,KGLHDEXC executions
,KGLOBCCC VERSION_COUNT
,decode(kglobt33, 1, 'Y', 'N') is_obsolete
,substr(KGLNAOBJ
,instr(KGLNAOBJ,'_',1,3)+1
,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id
,(case when
replace(translate(substr(upper(KGLNAOBJ)
,instr(KGLNAOBJ,'_',1,3)+1
,instr(KGLNAOBJ,'_',1,4)
-instr(KGLNAOBJ,'_',1,3)-1)
,'0123456789ABCDEF','................')
,'.') is null then 'Y' else 'N' end) is_safe_to_compare
from x$kglob) k
, obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
and o.owner#=u.user#;
Create or replace view H$PARAMETER
as
select a.ksppinm NAME,
a.ksppdesc DESCRIPTION,
b.ksppstvl SESSION_VALUE,
c.ksppstvl SYSTEM_VALUE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx;
create or replace function debug_version_rpt return DBMS_DEBUG_VC2COLL PIPELINED is
v_status number;
v_info varchar2(32767);
begin
loop
v_status := dbms_pipe.receive_message('version_rpt',0);
if v_status = 0 then
dbms_pipe.unpack_message(v_info);
pipe row (v_info);
else
return;
end if;
end loop ;
end;
/
create or replace function version_rpt(p_sql_id varchar2 default null,p_hash number default null,p_debug char default 'N') return DBMS_DEBUG_VC2COLL PIPELINED is
type vc_arr is table of varchar2(32767) index by binary_integer;
type num_arr is table of number index by binary_integer;
v_version varchar2(100);
v_instance varchar2(100);
v_colname vc_arr;
v_Ycnt num_arr;
v_count number:=-1;
v_no number;
v_all_no number:=-1;
v_query varchar2(4000);
v_sql_where varchar2(4000):='';
v_sql_where2 varchar2(4000):='';
v_sql_id varchar2(15):=p_sql_id;
v_addr varchar2(100);
V_coladdr varchar2(100);
v_hash number:=p_hash;
v_mem number;
v_parses number;
v_execs number;
v_value varchar2(100);
theCursor number;
columnValue char(1);
status number;
v_driver varchar2(1000);
TYPE cursor_ref IS REF CURSOR;
vc cursor_ref;
v_bind_dumped boolean:=false;
v_auth_dumped boolean:=false;
v_phv num_arr;
v_phvc num_arr;
procedure debugme(p_info varchar2) is
v_st number;
begin
if p_debug='Y' then
dbms_pipe.pack_message(p_info);
v_st := dbms_pipe.send_message('version_rpt',5);
if v_st=1 then dbms_pipe.purge('version_rpt'); end if;
end if;
end;
BEGIN
if p_debug='Y' then
status:=DBMS_PIPE.CREATE_PIPE ( pipename=>'version_rpt',maxpipesize=>1024*1024);
if status<>0 then pipe row ('Cannot debug'); return; end if;
end if;
debugme('instance version');
select version,'Host: '||HOST_NAME||' Instance '||INSTANCE_NUMBER||' : '||INSTANCE_NAME
into v_version , v_instance from v$instance;
debugme('build v$sqlarea query for '||v_sql_id||' '||v_hash);
/*
This aggregate query is in the cases where
1) So many versions of the same SQL that many parents have been obsoleted.
2) there are more than 1 SQL with the same hash value or sql_id (very rare)
*/
v_query:='select '|| case when v_version like '9%' then '(NULL)' else '(sql_id)' end ||' sql_id,'
|| 'max(sql_text) query,'
|| 'max(hash_value) hash,'
|| 'max(rawtohex(ADDRESS)) addr,'
|| 'sum(SHARABLE_MEM) SHARABLE_MEM,'
|| 'sum(PARSE_CALLS) PARSE_CALLS,'
|| 'sum(EXECUTIONS) EXECUTIONS'
|| ' from v$sqlarea where'
|| case when v_sql_id is not null then ' sql_id=:v_sql_id' else ' hash_value=:v_hash' end
|| ' group by '|| (case when v_version like '9%' then 'NULL' else 'sql_id' end);
debugme(v_query);
if v_sql_id is not null then
open vc for v_query using v_sql_id;
else
open vc for v_query using v_hash;
end if;
debugme('Successful open cursor');
PIPE ROW('Note:438755.1 Version Count Report Version 3.2.5 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;
PIPE ROW('RDBMS Version :'||v_version||' '||v_instance);
debugme('fetch '||v_sql_id||' '||v_hash);
fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
if vc%notfound then
/* This execption could mean 2 things
1) The user gave a wrong SQLID
2) The SQLID belongs to a pseudo cursor.
if 2) then the info will not be in v$sqlarea so will try h$pseudo_cursor.
I do not query h$pseudo_cursor from the start to avoid as much as possible to access x$ views directly
due to their mutex and latch restrictions and to take advantage of any optimizations done in v$sqlarea.
*/
debugme('not found - alternate');
v_query:= replace(v_query,'v$sqlarea','H$PSEUDO_CURSOR');
v_query:= replace(v_query,'sql_text','Pseudo_cursor||''(PseudoCursor of ''||owner||''.''||object_name||'')''');
debugme(v_query);
close vc;
if v_sql_id is not null then
open vc for v_query using v_sql_id;
else
open vc for v_query using v_hash;
end if;
fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
if vc%notfound then
return; /* Sorry, really is not in the library cache. */
end if;
end if;
close vc;
debugme('Header');
v_colname.delete;
v_Ycnt.delete;
v_count:=-1;
v_no:=0;
v_all_no:=-1;
PIPE ROW('==================================================================');
PIPE ROW('Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);
PIPE ROW('Sharable_Mem: '||v_mem||' bytes Parses: '||v_parses||' Execs:'||v_execs);
PIPE ROW('Stmt: ');
for i in 0 .. trunc(length(v_query)/64)+1 loop
debugme('Print query line '||i);
PIPE ROW(i||' '||substr(v_query,1+i*64,64));
end loop;
debugme('Fetch SQL_SHARED_CURSOR columns');
SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
from cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
v_query:='';
debugme('Build Select List');
for i in 1 .. v_colname.count loop
v_query:= v_query ||','|| v_colname(i);
end loop;
debugme('Build Where');
if v_version like '9%' then
v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
v_sql_where2:=' WHERE KGLHDPAR=HEXTORAW('''||V_ADDR||''')';
elsif v_sql_id is not null then
v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';
v_sql_where2:=v_sql_where;
else
v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
v_sql_where2:=v_sql_where;
end if;
debugme('Build Query');
v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';
v_query:=v_query||v_sql_where2;
debugme(substr(v_sql_where2,-80));
debugme('Open Query');
begin
theCursor := dbms_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user(C=>theCursor,STATEMENT=> v_Query, LANGUAGE_FLAG=>dbms_sql.native ,USERID=>0);
for i in 1 .. v_colname.count loop
dbms_sql.define_column( theCursor, i, columnValue, 8000 );
end loop;
status := dbms_sql.execute(theCursor);
debugme('Initiate Fetch');
while (dbms_sql.fetch_rows(theCursor) >0) loop
v_no:=0;
v_count:=v_count+1;
debugme('Fetch row '||v_count);
for i in 1..v_colname.count loop
dbms_sql.column_value(theCursor, i, columnValue);
-- debugme('Decode row '||v_count||' column '||i);
if columnValue='Y' then
v_Ycnt(i):=v_Ycnt(i)+1;
else
v_no:=v_no+1;
end if;
end loop;
if v_no=v_colname.count then
v_all_no:=v_all_no+1;
end if;
end loop;
dbms_sql.close_cursor(theCursor);
end;
debugme('Version summary');
PIPE ROW('');
PIPE ROW('Versions Summary');
PIPE ROW('----------------');
for i in 1 .. v_colname.count loop
if v_Ycnt(i)>0 then
PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));
end if;
end loop;
If v_all_no>1 then
PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);
end if;
PIPE ROW(' ');
PIPE ROW('Total Versions:'||v_count);
PIPE ROW(' ');
PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');
V_value:=NULL;
v_query:='select max(SYSTEM_VALUE) into :v_value from h$parameter where name=''cursor_sharing'' ';
execute immediate v_query into v_value;
if v_value is not null then
PIPE ROW('cursor_sharing = '||v_value);
end if;
V_NO:=NULL;
v_query:='select max(SYSTEM_VALUE) into :v_no from h$parameter where name=''_cursor_obsolete_threshold'' ';
execute immediate v_query into v_no;
if v_no is not null then
PIPE ROW('_cursor_obsolete_threshold = '||v_no||' (See Note:10187168.8)');
end if;
PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');
PIPE ROW(' ');
v_phv.delete;
v_phvc.delete;
debugme('PHV');
v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||' group by plan_hash_value';
execute immediate v_query bulk collect into v_phv,v_phvc;
PIPE ROW('Plan Hash Value Summary');
PIPE ROW('-----------------------');
PIPE ROW('Plan Hash Value Count');
PIPE ROW('=============== =====');
for i in 1 .. v_phv.count loop
PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i)));
end loop;
PIPE ROW(' ');
for i in 1 .. v_colname.count loop
debugme('Diag for '||v_colname(i)||' Ycnt:'||v_Ycnt(i));
if v_Ycnt(i)>0 then
PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));
PIPE ROW('Details for '||v_colname(i)||' :');
PIPE ROW('');
if ( v_colname(i) like '%BIND%')
or (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH') then
if v_bind_dumped=true then -- Dump only once
PIPE ROW('Details shown already.');
else
v_bind_dumped:=true;
if v_version like '9%' then
PIPE ROW('No details for '||v_version);
else
PIPE ROW('Consolidated details for BIND* columns:');
PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and');
PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');
PIPE ROW('');
declare
v_position num_arr;
v_maxlen num_arr;
v_minlen num_arr;
v_dtype num_arr;
v_prec num_arr;
v_scale num_arr;
v_n num_arr;
v_bg char(3);
v_bgflag char(1):='N';
begin
v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'
||' from v$sql_bind_capture where sql_id=:v_sql_id'
||' group by sql_id,position,datatype,precision,scale'
||' order by sql_id,position,datatype,precision,scale';
EXECUTE IMMEDIATE v_query
bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n
using v_sql_id;
PIPE ROW('from v$sql_bind_capture');
PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)');
PIPE ROW('======== ======== =============== =============== ======== =============== =================');
for c in 1 .. v_position.count loop
v_bg:=(case when v_maxlen(c)>v_minlen(c) then 'Yes' else 'No' end);
v_bgflag:=(case when v_bg='Yes' and v_n(c)>99 then 'Y' end);
PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')
||' '|| to_char(v_maxlen(c),'99999999999999')
||' '|| to_char(v_dtype(c),'9999999')
||' '||v_bg
||' ('|| v_prec(c)||','||v_scale(c)||')'
);
end loop;
if ( v_bgflag='Y' ) then
PIPE ROW ('The above table shows binds with 100+ versions in the same position with different MAX_LENGTHs.');
PIPE ROW ('See "Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE" ');
end if;
if v_version not like '10%' and v_version not like '9%' then
v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'
||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'
||' from v$sql where sql_id = :v_sql_id';
EXECUTE IMMEDIATE v_query
bulk collect into v_position, v_minlen, v_maxlen , v_dtype
using v_sql_id;
PIPE ROW('');
PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');
PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');
PIPE ROW('=========== ================= ============= ============');
for c in 1 .. v_position.count loop
PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')
||' '|| to_char(v_maxlen(c),'999999999999')
||' '|| to_char(v_dtype(c),'99999999999'));
end loop;
end if;
end;
end if;
end if;
elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then
for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop
PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);
end loop;
elsif v_colname(i) ='OPTIMIZER_MISMATCH' then
if v_version like '9%' then
PIPE ROW('No details available for '||v_version);
else
declare
v_param vc_arr;
v_value vc_arr;
v_n num_arr;
begin
v_query:='select o.NAME,o.VALUE ,count(*) n '
||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '
||'where ISDEFAULT=''NO'' '
||' and OPTIMIZER_MISMATCH=''Y'' '
||' and s.sql_id=:v_sql_id '
||' and o.sql_id=s.sql_id '
||' and o.CHILD_ADDRESS=s.CHILD_ADDRESS '
||' group by o.NAME,o.VALUE ';
EXECUTE IMMEDIATE v_query
bulk collect into v_param,v_value,v_n using v_sql_id ;
for c in 1 .. v_n.count loop
PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));
end loop;
end;
end if;
elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then
declare
v_pusr num_arr;
v_pschid num_arr;
v_pschname vc_arr;
v_n num_arr;
begin
if v_version like '9%' then
v_query:='select PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'' ,count(*) n from v$sql '
||v_sql_where
||' group by PARSING_USER_ID, PARSING_SCHEMA_ID,''n/a''';
else
v_query:='select PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from v$sql '
||v_sql_where
||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';
end if;
EXECUTE IMMEDIATE v_query
bulk collect into v_pusr,v_pschid,v_pschname,v_n;
PIPE ROW(' # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');
PIPE ROW('========== =============== ================= ===================');
for c in 1 .. v_n.count loop
PIPE ROW(to_char(v_n(c),'999999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));
end loop;
end;
elsif v_colname(i) = 'TRANSLATION_MISMATCH' then
declare
v_objn num_arr;
v_objow vc_arr;
v_objnm vc_arr;
begin
v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'
||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '
||v_sql_where
||' and object_name is not null group by OBJECT_NAME ) d'
||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';
EXECUTE IMMEDIATE v_query
bulk collect into v_objn,v_objow,v_objnm;
If v_objn.count>0 then
PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');
PIPE ROW(' ');
PIPE ROW(' Object# Owner.Object_Name');
PIPE ROW('============ =================');
for c in 1 .. v_objn.count loop
PIPE ROW(to_char(v_objn(c),'99999999999')||' '||v_objow(c)||'.'||v_objnm(c));
end loop;
else
PIPE ROW('No objects in the plans with same name and different owner were found.');
end if;
end;
else
PIPE ROW('No details available');
end if;
end if;
end loop;
debugme('cursortrace');
IF v_version not like '9%' then
PIPE ROW('####');
PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');
if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN
PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');
end if;
PIPE ROW('alter session set events ');
PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');
PIPE ROW('To turn it off do use address 1, level 2147483648');
end if;
PIPE ROW('================================================================');
debugme('End of version_rpt');
return;
exception
when others then
PIPE ROW('Error :'||sqlerrm);
PIPE ROW('for Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);
for i in 0 .. trunc(length(v_query)/64) loop
PIPE ROW(i||' '||substr(v_query,1+i*64,64));
end loop;
return;
end;
/
=====================
Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100; /* Set to 30 in 11.2.0.3 and in versions where fix of bug 10187168 was initially introduced */
Generate reports for all cursors with more than 100 versions using HASH_VALUE
set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
TABLE(version_rpt(NULL,a.hash_value)) b
WHERE loaded_versions>=100; /* Set to 30 in 11.2.0.3 and in versions where fix of bug 10187168 was initially introduced */
Generate the report for cursor with sql_id cyzznbykb509s
set pages 2000 lines 100
SELECT * FROM TABLE(version_rpt('cyzznbykb509s'));
===================================================
SET LINESIZE 300 PAGESIZE 300
COLUMN sql_text FORMAT a50
SELECT
'SELECT * FROM table(version_rpt(''' || a.sql_id || '''));' AS version_query,
a.cursors,
a.sql_id,
b.sql_text
FROM
(
SELECT
COUNT(*) AS cursors,
ssc.sql_id
FROM v$sql_shared_cursor ssc
GROUP BY ssc.sql_id
) a
JOIN
(
SELECT
sa.sql_id,
sa.sql_text
FROM v$sqlarea sa
WHERE PARSING_SCHEMA_NAME = 'XXX'
-- AND sql_id = '4dc6768syk8z2'
) b
ON a.sql_id = b.sql_id
WHERE a.cursors > 100
ORDER BY a.cursors DESC;
========================define 1='ajqqtcxazrnx' col sql_id format a13 col address format a16 col child_address format a16 col child_number format 9999999 head 'CHILD#' col parameter format a25 col reason format a60 col optimizer_mode_cursor format a10 head 'OPTIMIZER|MODE|CURSOR' col optimizer_mode_current format a10 head 'OPTIMIZER|MODE|CURRENT' set long 10000 ttitle off set term off head off verify off pause off col u_sql_id new_value u_sql_id noprint select '&1' u_sql_id from dual; set term on feed on head on set linesize 200 trimspool on select s.sql_id , s.address , s.child_address , s.child_number , decode(x.optimizer_mode_cursor, 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', x.optimizer_mode_cursor, 'NA' ) AS optimizer_mode_cursor , decode(x.optimizer_mode_current, 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', x.optimizer_mode_current, 'NA' ) AS optimizer_mode_current , s.parameter , x.reason from ( select sql_id, address, child_address, child_number, reason , unbound_cursor , sql_type_mismatch , optimizer_mismatch , outline_mismatch , stats_row_mismatch , literal_mismatch , force_hard_parse , explain_plan_cursor , buffered_dml_mismatch , pdml_env_mismatch , inst_drtld_mismatch , slave_qc_mismatch , typecheck_mismatch , auth_check_mismatch , bind_mismatch , describe_mismatch , language_mismatch , translation_mismatch , bind_equiv_failure , insuff_privs , insuff_privs_rem , remote_trans_mismatch , logminer_session_mismatch , incomp_ltrl_mismatch , overlap_time_mismatch , edition_mismatch , mv_query_gen_mismatch , user_bind_peek_mismatch , typchk_dep_mismatch , no_trigger_mismatch , flashback_cursor , anydata_transformation , pddl_env_mismatch , top_level_rpi_cursor , different_long_length , logical_standby_apply , diff_call_durn , bind_uacs_diff , plsql_cmp_switchs_diff , cursor_parts_mismatch , stb_object_mismatch , crossedition_trigger_mismatch , pq_slave_mismatch , top_level_ddl_mismatch , multi_px_mismatch , bind_peeked_pq_mismatch , mv_rewrite_mismatch , roll_invalid_mismatch , optimizer_mode_mismatch , px_mismatch , mv_staleobj_mismatch , flashback_table_mismatch , litrep_comp_mismatch , plsql_debug , load_optimizer_stats , acl_mismatch , flashback_archive_mismatch , lock_user_schema_failed , remote_mapping_mismatch , load_runtime_heap_failed , hash_match_failed , purged_cursor , bind_length_upgradeable , use_feedback_stats from v$sql_shared_cursor where sql_id = '&u_sql_id' ) UNPIVOT (invalidated for parameter in( unbound_cursor as 'unbound_cursor' , sql_type_mismatch as 'sql_type_mismatch' , optimizer_mismatch as 'optimizer_mismatch' , outline_mismatch as 'outline_mismatch' , stats_row_mismatch as 'stats_row_mismatch' , literal_mismatch as 'literal_mismatch' , force_hard_parse as 'force_hard_parse' , explain_plan_cursor as 'explain_plan_cursor' , buffered_dml_mismatch as 'buffered_dml_mismatch' , pdml_env_mismatch as 'pdml_env_mismatch' , inst_drtld_mismatch as 'inst_drtld_mismatch' , slave_qc_mismatch as 'slave_qc_mismatch' , typecheck_mismatch as 'typecheck_mismatch' , auth_check_mismatch as 'auth_check_mismatch' , bind_mismatch as 'bind_mismatch' , describe_mismatch as 'describe_mismatch' , language_mismatch as 'language_mismatch' , translation_mismatch as 'translation_mismatch' , bind_equiv_failure as 'bind_equiv_failure' , insuff_privs as 'insuff_privs' , insuff_privs_rem as 'insuff_privs_rem' , remote_trans_mismatch as 'remote_trans_mismatch' , logminer_session_mismatch as 'logminer_session_mismatch' , incomp_ltrl_mismatch as 'incomp_ltrl_mismatch' , overlap_time_mismatch as 'overlap_time_mismatch' , edition_mismatch as 'edition_mismatch' , mv_query_gen_mismatch as 'mv_query_gen_mismatch' , user_bind_peek_mismatch as 'user_bind_peek_mismatch' , typchk_dep_mismatch as 'typchk_dep_mismatch' , no_trigger_mismatch as 'no_trigger_mismatch' , flashback_cursor as 'flashback_cursor' , anydata_transformation as 'anydata_transformation' , pddl_env_mismatch as 'pddl_env_mismatch' , top_level_rpi_cursor as 'top_level_rpi_cursor' , different_long_length as 'different_long_length' , logical_standby_apply as 'logical_standby_apply' , diff_call_durn as 'diff_call_durn' , bind_uacs_diff as 'bind_uacs_diff' , plsql_cmp_switchs_diff as 'plsql_cmp_switchs_diff' , cursor_parts_mismatch as 'cursor_parts_mismatch' , stb_object_mismatch as 'stb_object_mismatch' , crossedition_trigger_mismatch as 'crossedition_trigger_mismatch' , pq_slave_mismatch as 'pq_slave_mismatch' , top_level_ddl_mismatch as 'top_level_ddl_mismatch' , multi_px_mismatch as 'multi_px_mismatch' , bind_peeked_pq_mismatch as 'bind_peeked_pq_mismatch' , mv_rewrite_mismatch as 'mv_rewrite_mismatch' , roll_invalid_mismatch as 'roll_invalid_mismatch' , optimizer_mode_mismatch as 'optimizer_mode_mismatch' , px_mismatch as 'px_mismatch' , mv_staleobj_mismatch as 'mv_staleobj_mismatch' , flashback_table_mismatch as 'flashback_table_mismatch' , litrep_comp_mismatch as 'litrep_comp_mismatch' , plsql_debug as 'plsql_debug' , load_optimizer_stats as 'load_optimizer_stats' , acl_mismatch as 'acl_mismatch' , flashback_archive_mismatch as 'flashback_archive_mismatch' , lock_user_schema_failed as 'lock_user_schema_failed' , remote_mapping_mismatch as 'remote_mapping_mismatch' , load_runtime_heap_failed as 'load_runtime_heap_failed' , hash_match_failed as 'hash_match_failed' , purged_cursor as 'purged_cursor' , bind_length_upgradeable as 'bind_length_upgradeable' , use_feedback_stats as 'use_feedback_stats' ) ) s, -- XML from Troubleshooting Oracle Performance Version 2, Christian Antognini XMLTable('/Root' PASSING XMLType('<Root>'||reason||'</Root>') COLUMNS child_number NUMBER PATH '/Root/ChildNode[1]/ChildNumber', id NUMBER PATH '/Root/ChildNode[1]/ID', reason VARCHAR2(100) PATH '/Root/ChildNode[1]/reason', optimizer_mode_hinted_cursor NUMBER PATH '/Root/ChildNode[1]/optimizer_mode_hinted_cursor', optimizer_mode_cursor NUMBER PATH '/Root/ChildNode[1]/optimizer_mode_cursor', optimizer_mode_current NUMBER PATH '/Root/ChildNode[1]/optimizer_mode_current' ) x where invalidated = 'Y' order by sql_id, child_number /DECLARE l_name VARCHAR2(64); l_sql_text CLOB; BEGIN -- get address, hash_value and sql text SELECT address||','||hash_value, sql_fulltext INTO l_name, l_sql_text FROM v$sqlarea WHERE sql_id = '&&sql_id.'; -- not always does the job SYS.DBMS_SHARED_POOL.PURGE ( name => l_name, flag => 'C', heaps => 1 ); END; /
Top 10 SQL Buffer Gets from schema
Top SQL .... Buffer Gets
prompt PARSING_SCHEMA_NAME='SCOTT' <<<<<<<<<<--------------
declare
v_sql_id varchar2(20);
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select sql_id,buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
where PARSING_SCHEMA_NAME='SCOTT'
order by buffer_gets desc;
begin
dbms_output.put_line('sql_id '||' '||' Reads'||' '||' Text');
dbms_output.put_line ('-----'||'-----'||' '||'---------------------------------------------------');
dbms_output.put_line(' ');
open c1;
for i in 1 .. 10 loop
dbms_output.put_line(' ');
fetch c1 into v_sql_id,top5, text1;
dbms_output.put_line(v_sql_id||' '||rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('" '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/
prompt PARSING_SCHEMA_NAME='SCOTT' <<<<<<<<<<--------------
declare
v_sql_id varchar2(20);
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select sql_id,buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
where PARSING_SCHEMA_NAME='SCOTT'
order by buffer_gets desc;
begin
dbms_output.put_line('sql_id '||' '||' Reads'||' '||' Text');
dbms_output.put_line ('-----'||'-----'||' '||'---------------------------------------------------');
dbms_output.put_line(' ');
open c1;
for i in 1 .. 10 loop
dbms_output.put_line(' ');
fetch c1 into v_sql_id,top5, text1;
dbms_output.put_line(v_sql_id||' '||rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('" '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/
Subscribe to:
Posts (Atom)
