Tablo Analiz

/*
SAPSR3 un Z li tablolarının dml işlem yapılmış olanlarının istatistiklerinin toplanması
*/
declare
cursor c_tablo is
select u.table_name
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3' and d.table_name like 'ZPPBOY%'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed;

sayi number;

begin
for r_tablo in c_tablo
loop
dbms_output.put_line('Tablo:'||r_tablo.table_name);
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=>r_tablo.table_name, partname=> NULL);
end loop;
end;
----------------------------------------
---tablo değişikliklerini listeleme
select u.TIMESTAMP,
d.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed
/

------------------------
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'Z_table', partname=> NULL);
end;

begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
-------------------------

Oracle Advisory BASIC TYPICAL ALL
-------------------------------------------
Buffer Cache Advice X X
MTTR Advice X X
Shared Pool Advice X X
Segment Level Statistics X X
PGA Advice X X
Timed Statistics X X
Timed OS Statistics X
Plan Execution Statistics X


col statistics_name for a30 head "Statistics Name"
col session_status for a10 head "Session|Status"
col system_status for a10 head "System|Status"
col activation_level for a10 head "Activation|Level"
col session_settable for a10 head "Session|Settable"

SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/


Doc ID: 252597.1
Doc ID: 255452.1