Learnt
a new stuff with Oracle, it is about how to restrict schema stats gathering to
a set of table rather than entire object list.
It is all possible with the new obj_filter_list parameter introduced in
11gr2 with gather_schema_stats method.
Here is the test case to demonstrate that. (Just enabled concurrent
stats gathering to show how multiple (sub) partitions are stats gathered during
process execution)
rajesh@ORA11G> exec dbms_stats.delete_schema_stats(user);
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec
dbms_stats.set_global_prefs('CONCURRENT','TRUE');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select dbms_stats.get_prefs('CONCURRENT') from
dual;
DBMS_STATS.GET_PREFS('CONCURRENT')
------------------------------------------------------------------------------
TRUE
1 row selected.
rajesh@ORA11G> declare
2 l_inlist dbms_stats.ObjectTab :=
dbms_stats.ObjectTab();
3 l_outlist dbms_stats.ObjectTab :=
dbms_stats.ObjectTab();
4 begin
5 l_inlist.extend(5);
6 l_inlist(1).ownname:=user;
7 l_inlist(1).objtype:='TABLE';
8 l_inlist(1).objname:='T1';
9
10 l_inlist(2).ownname:=user;
11 l_inlist(2).objtype:='TABLE';
12 l_inlist(2).objname:='T2';
13
14 l_inlist(3).ownname:=user;
15 l_inlist(3).objtype:='TABLE';
16 l_inlist(3).objname:='T3';
17
18 l_inlist(4).ownname:=user;
19 l_inlist(4).objtype:='TABLE';
20 l_inlist(4).objname:='T4';
21
22 l_inlist(5).ownname:=user;
23 l_inlist(5).objtype:='TABLE';
24 l_inlist(5).objname:='T5';
25 dbms_stats.gather_schema_stats(
26 ownname => user,
27 objlist => l_outlist ,
28 obj_filter_list => l_inlist );
29 end;
30 /
PL/SQL procedure successfully completed.
We
can monitor those concurrently stats gathering process from data dictionary
like this.
rajesh@ORA11G> @printtbl 'select
job_name,job_subname,job_style,job_type,state,comments from
user_scheduler_jobs'
JOB_NAME
: "ST$SD2255_1"
JOB_SUBNAME
: ""
JOB_STYLE
: "REGULAR"
JOB_TYPE
: "PLSQL_BLOCK"
STATE
: "RUNNING"
COMMENTS
: ""RAJESH"."T1"."
-----------------
JOB_NAME
: "ST$T2256_1"
JOB_SUBNAME
: ""
JOB_STYLE : "REGULAR"
JOB_TYPE
: "PLSQL_BLOCK"
STATE
: "RUNNING"
COMMENTS
: "RAJESH.T1.SYS_P1481"
-----------------
JOB_NAME
: "ST$T2256_2"
JOB_SUBNAME
: ""
JOB_STYLE
: "REGULAR"
JOB_TYPE
: "PLSQL_BLOCK"
STATE
: "RUNNING"
COMMENTS
: "RAJESH.T1.SYS_P1482"
-----------------
JOB_NAME
: "ST$T2256_3"
JOB_SUBNAME
: ""
JOB_STYLE
: "REGULAR"
JOB_TYPE
: "PLSQL_BLOCK"
STATE
: "RUNNING"
COMMENTS
: "RAJESH.T1.SYS_P1483"
-----------------
JOB_NAME : "ST$T2256_4"
JOB_SUBNAME
: ""
JOB_STYLE
: "REGULAR"
JOB_TYPE
: "PLSQL_BLOCK"
STATE
: "RUNNING"
COMMENTS
: "RAJESH.T1.SYS_P1484"
-----------------
JOB_NAME
: "ST$T2256_5"
JOB_SUBNAME
: ""
JOB_STYLE
: "REGULAR"
JOB_TYPE
: "PLSQL_BLOCK"
STATE
: "RUNNING"
COMMENTS
: "RAJESH.T1."
-----------------
PL/SQL procedure successfully completed.
Finally
we have generated stats gathering only for the provided (listed) set of tables,
rest others got ignored.
rajesh@ORA11G> select table_name,last_analyzed
2 from user_tables
3 order by last_analyzed nulls last ;
TABLE_NAME
LAST_ANALYZED
------------------------------ -----------------------
T1
06-JAN-2016 09:04:27 pm
T2
06-JAN-2016 09:04:38 pm
T3 06-JAN-2016 09:04:49 pm
T4
06-JAN-2016 09:05:06 pm
T5
06-JAN-2016 09:05:27 pm
EMP_STG
T1_T2_MV
SELL
BUY
SALES_MV
CURRENCY
T_MV
PARALLEL_RANGE
DEPT2
EMP_MV
SALES
TIME_HIERARCHY
SALES_TIME_MV
DEPT
EMP
ACCOUNTS
LOG_DATA
ORDERS_ITEMS
ORDERS
TRACE_FILE
LOCATIONS
DEPARTMENTS
EMPLOYEES
WS_INS_TEST
JOB_PARAMS
COUNTRY
31 rows selected.
rajesh@ORA11G>
No comments:
Post a Comment