Issue : Running Index stat and table stat gather on the huge database take long time to finish. This
process can be further tunned with the following way.
Pre request :
1) Grant DBMS_SCHEDULAR Permission to
-----------------------------------------------------
Connect to sys user and execute below permission to
grant all on DBA_SCHEDULER_JOB_RUN_DETAILS to;
grant all on DBMS_LOCK to;
grant all on DBMS_ISCHED to;
grant all on DBMS_SCHEDULER to;
grant CREATE ANY JOB to;
2) connect to schema and run the script
--linux session , cd to file folder where the table_analysis_with_index.sql is placed.
sql> @./table_analysis_with_index.sql
3) calculate the no.of degree and parellal based on the below.
copy the below text and make the file table_analysis_with_index.sql
spool ./runstat_details.txt
select systimestamp from dual;
drop table schema_runstat_job_details;
create table schema_runstat_job_details ( job_name varchar2(1000),details varchar2(4000)) ;
alter table schema_runstat_job_details add constraint schema_runstat_pk primary key ( job_name);
declare
p_job_name varchar2(100) := 'schemastat';
p_exit number := 20;
no_of_session number := 20;
p_count number := 0;
begin
delete from schema_runstat_job_details;
--- call for big tables separatly.
for x in (select table_name
from user_tables where table_name in (,)
) loop
p_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('schemastat_');
insert into schema_runstat_job_details
values
(p_job_name, x.table_name);
commit;
dbms_scheduler.create_job(job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ' ||
'''ALTER SESSION FORCE PARALLEL DDL PARALLEL 5''' || ';
DBMS_STATS.gather_table_stats (
ownname => ' ||
'''&SCHEMA_NAME''' ||
',tabname => ' || '''' ||
x.table_name || '''' ||
' ,method_opt => ' || '''' ||
'for all indexed columns size 254' || '''' ||
',degree => 5' ||
',cascade => FALSE);' || '
exception
when others then
null;
end;',
comments => 'Thread ' || 1 ||
' for schemastat_',
enabled => true,
auto_drop => FALSE);
end loop;
--- call for big tables indexs separatly.
for x in (select index_name
from user_indexes where table_name in (,)
)
loop
p_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('schemastat_');
insert into schema_runstat_job_details
values
(p_job_name, x.index_name);
commit;
dbms_scheduler.create_job(job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ' ||
'''ALTER SESSION FORCE PARALLEL DDL PARALLEL 5''' || ';
DBMS_STATS.GATHER_INDEX_STATS (
ownname => ' ||
'''&SCHEMA_NAME''' ||
',indname => ' || '''' ||
x.index_name || '''' ||
',degree => 5 );' ||'
exception
when others then
null;
end;',
comments => 'Thread ' || 1 ||
' for schemastat_',
enabled => true,
auto_drop => FALSE);
end loop;
for x in (select table_name
from user_tables where table_name not in (,)
) loop
p_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('schemastat_');
insert into schema_runstat_job_details
values
(p_job_name, x.table_name);
commit;
dbms_scheduler.create_job(job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ' ||
'''ALTER SESSION FORCE PARALLEL DDL PARALLEL 5''' || ';
DBMS_STATS.gather_table_stats (
ownname => ' ||
'''&SCHEMA_NAME''' ||
',tabname => ' || '''' ||
x.table_name || '''' ||
' ,method_opt => ' || '''' ||
'for all indexed columns size 254' || '''' ||
',degree => 5' ||
',cascade => TRUE);' || '
exception
when others then
null;
end;',
comments => 'Thread ' || 1 ||
' for schemastat_',
enabled => true,
auto_drop => FALSE);
p_exit := no_of_session;
WHILE (p_exit >= no_of_session) LOOP
select count(*)
into p_exit
from dba_scheduler_jobs
where job_name in (select job_name from schema_runstat_job_details)
and state in ('SCHEDULED', 'RUNNING');
END LOOP;
end loop;
end;
/
declare
p_exit number:=1;
job_count number;
begin
WHILE (p_exit<>0)
LOOP
select count(*) into job_count from schema_runstat_job_details;
select count(*)-job_count
into p_exit
from dba_scheduler_jobs
where job_name in ( select job_name from schema_runstat_job_details)
and state in ( 'SUCCEEDED','FAILED');
DBMS_LOCK.sleep(seconds => 120);
END LOOP;
end;
/
select systimestamp from dual;
select ' startdate || end date || time in total ' from dual;
select max_time-min_time total_duration from
(select max(b.ACTUAL_START_DATE+b.RUN_DURATION )max_time ,min(b.ACTUAL_START_DATE+b.RUN_DURATION ) min_time from
DBA_SCHEDULER_JOB_RUN_DETAILS b
where job_name in ( select job_name from schema_runstat_job_details)
);
select a.*,additional_info ,b.ACTUAL_START_DATE,b.RUN_DURATION from
(
select state , job_name,job_action
from USER_SCHEDULER_JOBS
where job_name in ( select job_name from schema_runstat_job_details)
)A, DBA_SCHEDULER_JOB_RUN_DETAILS B
where a.job_name=b.job_name
order by RUN_DURATION desc
select table_name,last_analyzed From user_tables where trunc(last_analyzed ) = trunc(sysdate);
select index_name,last_analyzed from user_indexes
where trunc(last_analyzed ) = trunc(sysdate) ;
--drop table schema_runstat_job_details;
spool off;
6)
3) checnk the status by using
select a.*,additional_info ,b.ACTUAL_START_DATE,b.RUN_DURATION from
(
select state , job_name,job_action
from USER_SCHEDULER_JOBS
where job_name in ( select job_name from schema_runstat_job_details)
)A, DBA_SCHEDULER_JOB_RUN_DETAILS B
where a.job_name=b.job_name
order by RUN_DURATION desc
4) check the running jobs
select count(*)
from dba_scheduler_jobs
where job_name in (select job_name from schema_runstat_job_details)
and state in ('SCHEDULED', 'RUNNING');
--object which are not analysed.
select table_name,last_analyzed From user_tables where trunc(last_analyzed ) <> trunc(sysdate);
select index_name,last_analyzed from user_indexes
where trunc(last_analyzed ) <> trunc(sysdate) ;
5) Recall Analysis for the failed object / or for any specific tables and index
if we want call any object/failed object to do the analysis
the below piece of code will help. Change the table name.
begin
execute immediate 'ALTER SESSION FORCE PARALLEL DDL PARALLEL 5';
DBMS_STATS.gather_table_stats (
ownname => '&SCHEMA_NAME',
tabname => '&TABLE_NAME' ,
method_opt => 'for all indexed columns size 254',degree => 5,
cascade => TRUE);
exception
when others then
null;
end;
/
process can be further tunned with the following way.
Pre request :
1) Grant DBMS_SCHEDULAR Permission to
-----------------------------------------------------
Connect to sys user and execute below permission to
grant all on DBA_SCHEDULER_JOB_RUN_DETAILS to
grant all on DBMS_LOCK to
grant all on DBMS_ISCHED to
grant all on DBMS_SCHEDULER to
grant CREATE ANY JOB to
2) connect to
--linux session , cd to file folder where the table_analysis_with_index.sql is placed.
sql> @./table_analysis_with_index.sql
3) calculate the no.of degree and parellal based on the below.
Parallel session can be calculated by using
select * from v$parameter
PARALLEL_DEGREE_POLICY
select * from v$parameter where name like '%cpu%'
select * from v$parameter where name like 'para%'
select * from v$parameter where name like 'para%' or name like '%cpu%'
select * from v$parameter where name like 'para%'
select * from v$parameter where name like 'para%' or name like '%cpu%'
For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
Note : this will force the DDL/DML operation to run in parallel.
4) script.copy the below text and make the file table_analysis_with_index.sql
spool ./runstat_details.txt
select systimestamp from dual;
drop table schema_runstat_job_details;
create table schema_runstat_job_details ( job_name varchar2(1000),details varchar2(4000)) ;
alter table schema_runstat_job_details add constraint schema_runstat_pk primary key ( job_name);
declare
p_job_name varchar2(100) := 'schemastat';
p_exit number := 20;
no_of_session number := 20;
p_count number := 0;
begin
delete from schema_runstat_job_details;
--- call for big tables separatly.
for x in (select table_name
from user_tables where table_name in (
) loop
p_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('schemastat_');
insert into schema_runstat_job_details
values
(p_job_name, x.table_name);
commit;
dbms_scheduler.create_job(job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ' ||
'''ALTER SESSION FORCE PARALLEL DDL PARALLEL 5''' || ';
DBMS_STATS.gather_table_stats (
ownname => ' ||
'''&SCHEMA_NAME''' ||
',tabname => ' || '''' ||
x.table_name || '''' ||
' ,method_opt => ' || '''' ||
'for all indexed columns size 254' || '''' ||
',degree => 5' ||
',cascade => FALSE);' || '
exception
when others then
null;
end;',
comments => 'Thread ' || 1 ||
' for schemastat_',
enabled => true,
auto_drop => FALSE);
end loop;
--- call for big tables indexs separatly.
for x in (select index_name
from user_indexes where table_name in (
)
loop
p_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('schemastat_');
insert into schema_runstat_job_details
values
(p_job_name, x.index_name);
commit;
dbms_scheduler.create_job(job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ' ||
'''ALTER SESSION FORCE PARALLEL DDL PARALLEL 5''' || ';
DBMS_STATS.GATHER_INDEX_STATS (
ownname => ' ||
'''&SCHEMA_NAME''' ||
',indname => ' || '''' ||
x.index_name || '''' ||
',degree => 5 );' ||'
exception
when others then
null;
end;',
comments => 'Thread ' || 1 ||
' for schemastat_',
enabled => true,
auto_drop => FALSE);
end loop;
for x in (select table_name
from user_tables where table_name not in (
) loop
p_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('schemastat_');
insert into schema_runstat_job_details
values
(p_job_name, x.table_name);
commit;
dbms_scheduler.create_job(job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ' ||
'''ALTER SESSION FORCE PARALLEL DDL PARALLEL 5''' || ';
DBMS_STATS.gather_table_stats (
ownname => ' ||
'''&SCHEMA_NAME''' ||
',tabname => ' || '''' ||
x.table_name || '''' ||
' ,method_opt => ' || '''' ||
'for all indexed columns size 254' || '''' ||
',degree => 5' ||
',cascade => TRUE);' || '
exception
when others then
null;
end;',
comments => 'Thread ' || 1 ||
' for schemastat_',
enabled => true,
auto_drop => FALSE);
p_exit := no_of_session;
WHILE (p_exit >= no_of_session) LOOP
select count(*)
into p_exit
from dba_scheduler_jobs
where job_name in (select job_name from schema_runstat_job_details)
and state in ('SCHEDULED', 'RUNNING');
END LOOP;
end loop;
end;
/
declare
p_exit number:=1;
job_count number;
begin
WHILE (p_exit<>0)
LOOP
select count(*) into job_count from schema_runstat_job_details;
select count(*)-job_count
into p_exit
from dba_scheduler_jobs
where job_name in ( select job_name from schema_runstat_job_details)
and state in ( 'SUCCEEDED','FAILED');
DBMS_LOCK.sleep(seconds => 120);
END LOOP;
end;
/
select systimestamp from dual;
select ' startdate || end date || time in total ' from dual;
select max_time-min_time total_duration from
(select max(b.ACTUAL_START_DATE+b.RUN_DURATION )max_time ,min(b.ACTUAL_START_DATE+b.RUN_DURATION ) min_time from
DBA_SCHEDULER_JOB_RUN_DETAILS b
where job_name in ( select job_name from schema_runstat_job_details)
);
select a.*,additional_info ,b.ACTUAL_START_DATE,b.RUN_DURATION from
(
select state , job_name,job_action
from USER_SCHEDULER_JOBS
where job_name in ( select job_name from schema_runstat_job_details)
)A, DBA_SCHEDULER_JOB_RUN_DETAILS B
where a.job_name=b.job_name
order by RUN_DURATION desc
select table_name,last_analyzed From user_tables where trunc(last_analyzed ) = trunc(sysdate);
select index_name,last_analyzed from user_indexes
where trunc(last_analyzed ) = trunc(sysdate) ;
--drop table schema_runstat_job_details;
spool off;
6)
3) checnk the status by using
select a.*,additional_info ,b.ACTUAL_START_DATE,b.RUN_DURATION from
(
select state , job_name,job_action
from USER_SCHEDULER_JOBS
where job_name in ( select job_name from schema_runstat_job_details)
)A, DBA_SCHEDULER_JOB_RUN_DETAILS B
where a.job_name=b.job_name
order by RUN_DURATION desc
4) check the running jobs
select count(*)
from dba_scheduler_jobs
where job_name in (select job_name from schema_runstat_job_details)
and state in ('SCHEDULED', 'RUNNING');
--object which are not analysed.
select table_name,last_analyzed From user_tables where trunc(last_analyzed ) <> trunc(sysdate);
select index_name,last_analyzed from user_indexes
where trunc(last_analyzed ) <> trunc(sysdate) ;
5) Recall Analysis for the failed object / or for any specific tables and index
if we want call any object/failed object to do the analysis
the below piece of code will help. Change the table name.
begin
execute immediate 'ALTER SESSION FORCE PARALLEL DDL PARALLEL 5';
DBMS_STATS.gather_table_stats (
ownname => '&SCHEMA_NAME',
tabname => '&TABLE_NAME' ,
method_opt => 'for all indexed columns size 254',degree => 5,
cascade => TRUE);
exception
when others then
null;
end;
/
No comments:
Post a Comment