set echo off
set serveroutput on size 1000000
set pages 10000
select
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
select 'script :
/appl/home/oracle/BIN/DEVstatnew.ksh'
from dual; select 'script SQL: /appl/home/oracle/BIN/statnew.sql' from
dual; select 'log :
/appl/home/oracle/LOG/DEVstat.date.log'
from dual; select instance_name
from v$instance; set lines
200 set head on
select * from
(select
m.table_owner,
m.table_name,
--round ((select
NVL(sum (m.inserts + m.updates + m.deletes),0)
from sys.dba_tab_modifications m where
m.table_name = t.table_name AND partition_name IS NULL),1) MG,
round(( ((select
NVL(sum (m.inserts + m.updates + m.deletes),0)
from sys.dba_tab_modifications m where
m.table_name = t.table_name AND partition_name IS NULL) /(t.num_rows +1
))* 100),1) taux from
sys.dba_tab_modifications m,dba_tables t
where m.table_owner
= t.owner
and
m.table_name = t.table_name);
set head off
prompt
=====================================================
prompt " liste
indexe ayant plus de 10% de modifications"
prompt =====================================================
set head on
col owner for a20
col
index_name for a45
col
table_name for a50
col num_rows for 999999
col ratio for 99999
select
* from (select
a.owner
,a.index_name
,a.table_name
,a.num_rows
, trunc ((SELECT
NVL (SUM (inserts + updates + deletes), 0)
FROM sys.dba_tab_modifications m
WHERE m.table_name =
a.table_name )/ (a.num_rows + 1)* 100)ratio
from
sys.dba_tab_modifications m ,sys.dba_indexes a
where
m.table_owner = a.TABLE_OWNER
and
m.table_name = a.table_name)
WHERE ratio >=10 and num_rows != 0;
set head off
set timing on
DECLARE
vOwner sys.dba_indexes.owner%TYPE; /* Index Owner */
vIdxName
sys.dba_indexes.index_name%TYPE; /*
Index Name */
VRatio number;
vAnalyze
VARCHAR2(150); /*
String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL
cursor */
vTNumRows
INTEGER;
/*table num rows */
vNumRows INTEGER;
/* DBMS_SQL
return rows */
CURSOR cGetIdx IS
select
* from (select
t.owner
,idx.index_name
, t.num_rows
--,t.last_analyzed
,TRUNC ((SELECT
NVL (SUM (inserts + updates + deletes), 0)
FROM sys.dba_tab_modifications m
WHERE m.table_name =
t.table_name AND partition_name IS NULL)/ (t.num_rows + 1)* 100)ratio
from
sys.dba_tab_modifications m ,dba_tables t, dba_indexes idx
where
m.table_owner = t.owner
and idx.table_name=t.table_name
and
m.table_name = t.table_name)
WHERE ratio
>= 10 AND num_rows != 0;
BEGIN
OPEN cGetIdx;
LOOP
FETCH cGetIdx
INTO vOwner,vIdxName,vTNumRows,Vratio;
EXIT WHEN
cGetIdx%NOTFOUND;
/* Open
DBMS_SQL cursor */
vCursor :=
DBMS_SQL.OPEN_CURSOR;
vAnalyze :=
'begin dbms_stats.gather_index_stats( ''' || vOwner || ''',''' || vIdxName ||
''' ,estimate_percent=>100, degree=>4);end ;';
dbms_output.put_line('STAT IND : '|| vAnalyze);
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE); --
vNumRows := DBMS_SQL.EXECUTE(vCursor); --
/* Close
DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
END LOOP;
CLOSE cGetIdx;
END;
/
prompt
=====================================================
prompt "
table 10% de modification"
prompt =====================================================
SELECT *
FROM (SELECT m.table_owner,
m.table_name,
---round ((select NVL(sum
(m.inserts + m.updates + m.deletes),0)
from sys.dba_tab_modifications m where
m.table_name = t.table_name AND partition_name IS NULL),1),
ROUND (
( (
(SELECT NVL (SUM (m.inserts + m.updates + m.deletes),
0)
FROM
sys.dba_tab_modifications m
WHERE m.table_name = t.table_name
AND
partition_name IS NULL)
/ (t.num_rows + 1))
* 100),
1)
ratio
FROM sys.dba_tab_modifications m,
dba_tables t
WHERE m.table_owner = t.owner AND
m.table_name = t.table_name)
WHERE ratio > 10;
DECLARE
vOwner
sys.dba_tab_modifications.table_owner%TYPE;
vTblName
sys.dba_tab_modifications.table_name%TYPE;
vAnalyze15 VARCHAR2(180);
vAnalyze VARCHAR2(180);
vCursor NUMBER;
vRatio number;
vNumRows INTEGER;
vextents number;
vsize number;
vQuery VARCHAR2(180);
CURSOR cGetTbl IS
SELECT *
FROM (SELECT m.table_owner,
m.table_name,
---round ((select NVL(sum
(m.inserts + m.updates + m.deletes),0)
from sys.dba_tab_modifications m where
m.table_name = t.table_name AND partition_name IS NULL),1),
ROUND (
( (
(SELECT NVL (SUM (m.inserts + m.updates + m.deletes),
0)
FROM
sys.dba_tab_modifications m
WHERE m.table_name = t.table_name
AND
partition_name IS NULL)
/ (t.num_rows + 1))
* 100),
1)
ratio
FROM sys.dba_tab_modifications m,
dba_tables t
WHERE m.table_owner = t.owner AND
m.table_name = t.table_name)
WHERE ratio > 10;
BEGIN
OPEN cGetTbl;
LOOP
FETCH cGetTbl INTO vOwner,vTblName,vRatio;
EXIT WHEN cGetTbl%NOTFOUND;
select extents,bytes/1024/1024 into vextents,vsize from
sys.dba_segments where segment_type='TABLE' and segment_name=vTblName and owner=vOwner;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
if vsize>300
and Vextents >64 then
vAnalyze15 := 'begin dbms_stats.gather_table_stats( ''' || vOwner ||
''',''' || vTblName || ''' ,estimate_percent=>15,
degree=>4,cascade=>false);end ;';
dbms_output.put_line('STAT table 15 : '|| vAnalyze15);
DBMS_SQL.PARSE(vCursor,vAnalyze15,DBMS_SQL.NATIVE);
vNumRows
:= DBMS_SQL.EXECUTE(vCursor);
else
vAnalyze
:= 'begin dbms_stats.gather_table_stats( ''' || vOwner || ''',''' || vTblName||
''' ,estimate_percent=>50, degree=>4);end ;';
dbms_output.put_line('STAT table 100 : '||
vAnalyze);
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE);
vNumRows
:= DBMS_SQL.EXECUTE(vCursor);
end if;
DBMS_SQL.CLOSE_CURSOR(vCursor);
END LOOP;
CLOSE cGetTbl;
END;
/
prompt ===================
select * from
(select
m.table_owner,
m.table_name,
--round ((select
NVL(sum (m.inserts + m.updates + m.deletes),0)
from sys.dba_tab_modifications m where
m.table_name = t.table_name AND partition_name IS NULL),1) MG,
round(( ((select
NVL(sum (m.inserts + m.updates + m.deletes),0)
from sys.dba_tab_modifications m where
m.table_name = t.table_name AND partition_name IS NULL) /(t.num_rows +1
))* 100),1) taux from
sys.dba_tab_modifications m,dba_tables t
where
m.table_owner = t.owner
and
m.table_name = t.table_name);
prompt ===================
select
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
prompt
=====================================================
prompt
"FIN"
prompt
=====================================================
