Incremental stats
Incremental statistics maintenance was introduced in Oracle Database 11g to improve the performance of gathering statistics on large partitioned table. When incremental statistics maintenance is enabled for a partitioned table, oracle accurately generated global level statistics by aggregating partition level statistics.
Incremental statistics maintenance is supposed to yield the same statistics as gathering table statistics from scratch, just faster. This means incremental statistics maintenance needs to gather statistics on any partition that will change the global or table level statistics
This testcase focus on different CBO behavior in calculating stats on partitioned tables.
Elapsed time is not shown in this testcase because of small table size.
Below screen shot about estimated elapsed time in relation to number of partitions.
Below we are considering two kind of situations.
One from scratch (new table with no stats) and the other based on a table with pre-existing stats.
Table without pre-existing stats
- Creation of partitioned table
CREATE TABLE “TEST_TAB_2”
( “ID” NUMBER(10,0) NOT NULL ENABLE,
“DESCRIPTION” VARCHAR2(50) NOT NULL ENABLE,
“CREATED_DATE” DATE NOT NULL ENABLE
)
TABLESPACE “USERS”
PARTITION BY RANGE (“CREATED_DATE”)
(PARTITION “TEST_TAB_Q1” VALUES LESS THAN (TO_DATE(‘ 2008-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “USERS” ,
PARTITION “TEST_TAB_Q2” VALUES LESS THAN (TO_DATE(‘ 2008-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “USERS” ,
PARTITION “TEST_TAB_Q3” VALUES LESS THAN (TO_DATE(‘ 2008-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ,
PARTITION “TEST_2009” VALUES LESS THAN (TO_DATE(‘ 2009-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “USERS” ,
PARTITION “TEST_TAB_Q4” VALUES LESS THAN (MAXVALUE)
TABLESPACE “USERS” );
Let’s check incremental parameter at database level
SQL> select dbms_stats.get_prefs(‘INCREMENTAL’) from dual;
DBMS_STATS.GET_PREFS(‘INCREMENTAL’)
——————————————————————————–
FALSE
And let’s check incremental parameter at table level,that is disabled too.
SQL> select dbms_stats.get_prefs(‘INCREMENTAL’,’TEST_TAB_2′) from dual;
DBMS_STATS.GET_PREFS(‘INCREMENTAL’,’TEST_TAB_2′)
——————————————————————————–
FALSE
Just Turn on incremental stats at database level.
SQL> exec dbms_stats.set_global_prefs(‘INCREMENTAL’,’TRUE’);
PL/SQL procedure successfully completed.
In this way table inherits default properties about incremental stats
SQL> select dbms_stats.get_prefs(‘INCREMENTAL’,’TEST_TAB_2′) from dual;
DBMS_STATS.GET_PREFS(‘INCREMENTAL’,’TEST_TAB_2′)
——————————————————————————–
TRUE
SQL> select dbms_stats.get_prefs(‘INCREMENTAL’) from dual;
DBMS_STATS.GET_PREFS(‘INCREMENTAL’)
——————————————————————————–
TRUE
Table must have publish enabled to permit to CBO to collect statistics
SQL> select DBMS_STATS.GET_PREFS(‘PUBLISH’,’USER_INCR’,’TEST_TAB_2′) from dual;
DBMS_STATS.GET_PREFS(‘PUBLISH’,’USER_INCR’,’TEST_TAB_2′)
——————————————————————————–
TRUE
- Gather global table stats on empty-stats table
SQL> select table_name,partitioned,last_analyzed from dba_tables where owner=’ USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED
———————— — ———
TEST_TAB_2 YES
SQL> select table_name,partition_name,last_analyzed from dba_tab_partitions where table_name=’TEST_TAB_2′;
TABLE_NAME PARTITION_NAME LAST_ANALYZED
———————– ——————– ———
TEST_TAB_2 TEST_2009
TEST_TAB_2 TEST_TAB_Q1
TEST_TAB_2 TEST_TAB_Q2
TEST_TAB_2 TEST_TAB_Q3
TEST_TAB_2 TEST_TAB_Q4
10:33:21 SQL> exec dbms_stats.gather_table_stats(ownname=>’USER_INCR’,tabname=>’TEST_TAB_2′);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.69
10:33:49 SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name=’TEST_TAB_2′;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
———————— ——————– ——— ———-
TEST_TAB_2 TEST_2009 18-DEC-12 0
TEST_TAB_2 TEST_TAB_Q1 18-DEC-12 8
TEST_TAB_2 TEST_TAB_Q2 18-DEC-12 0
TEST_TAB_2 TEST_TAB_Q3 18-DEC-12 0
TEST_TAB_2 TEST_TAB_Q4 18-DEC-12 0
10:33:50 SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’ USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————— — ——— ———-
TEST_TAB_2 YES 18-DEC-12 8
Now we can insert some records for example in partition TEST_TAB_Q1
..
……
insert rows number: 999
insert rows number: 1000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
11:22:47 SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————— — ——————- ———-
TEST_TAB_2 YES 18-12-2012 10:33:27 8
11:23:07 SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name=’TEST_TAB_2′ ;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
———————- —————— ——————- ———-
TEST_TAB_2 TEST_2009 18-12-2012 10:33:27 0
TEST_TAB_2 TEST_TAB_Q1 18-12-2012 10:33:27 8
TEST_TAB_2 TEST_TAB_Q2 18-12-2012 10:33:26 0
TEST_TAB_2 TEST_TAB_Q3 18-12-2012 10:33:27 0
TEST_TAB_2 TEST_TAB_Q4 18-12-2012 10:33:26 0
With no stats (as expected) no evidence on new data is showed in system views, in expept for counting all records.
11:23:52 SQL> select count(*) from USER_INCR.TEST_TAB_2 partition (TEST_TAB_Q1);
COUNT(*)
———-
2009
So we can now Gathering statistics at table level.
11:24:57 SQL> exec dbms_stats.gather_table_stats(ownname=>’USER_INCR’,tabname=>’TEST_TAB_2′);
PL/SQL procedure successfully completed.
11:24:59 SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name=’TEST_TAB_2′;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
——————— ———————– ——————- ———-
TEST_TAB_2 TEST_2009 18-12-2012 10:33:27 0
TEST_TAB_2 TEST_TAB_Q1 18-12-2012 11:24:59 2009
TEST_TAB_2 TEST_TAB_Q2 18-12-2012 10:33:26 0
TEST_TAB_2 TEST_TAB_Q3 18-12-2012 10:33:27 0
TEST_TAB_2 TEST_TAB_Q4 18-12-2012 10:33:26 0
11:25:06 SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————— — ——————- ———-
TEST_TAB_2 YES 18-12-2012 11:24:59 2009
TAB_TEST NO
Global stats are derived from partition level.
Only modified partitions are gathered and global stats updated in relation to this.
Table with pre-existing stats
Below we are considering the case with a table wi pre-existing statistics.
Create empty table with incremental stats disabled
SQL> select dbms_stats.get_prefs(‘INCREMENTAL’) from dual;
DBMS_STATS.GET_PREFS(‘INCREMENTAL’)
———————————————
FALSE
SQL>CREATE TABLE “TEST_TAB_3”
( “ID” NUMBER(10,0) NOT NULL ENABLE,
“DESCRIPTION” VARCHAR2(50) NOT NULL ENABLE,
“CREATED_DATE” DATE NOT NULL ENABLE
) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE “USERS”
PARTITION BY RANGE (“CREATED_DATE”)
(PARTITION “TEST_TAB_Q1” VALUES LESS THAN (TO_DATE(‘ 2008-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “USERS” ,
PARTITION “TEST_TAB_Q2” VALUES LESS THAN (TO_DATE(‘ 2008-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “USERS” ,
PARTITION “TEST_TAB_Q3” VALUES LESS THAN (TO_DATE(‘ 2008-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ,
PARTITION “TEST_2009” VALUES LESS THAN (TO_DATE(‘ 2009-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “USERS” ,
PARTITION “TEST_TAB_Q4” VALUES LESS THAN (MAXVALUE)
TABLESPACE “USERS” );
11:43:34 SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_owner=’USER_INCR’;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
——————— ——————- ——————- ———-
TEST_TAB_3 TEST_TAB_Q4
TEST_TAB_3 TEST_TAB_Q3
TEST_TAB_3 TEST_TAB_Q2
TEST_TAB_3 TEST_TAB_Q1
TEST_TAB_3 TEST_2009
11:43:49 SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————– — ——————- ———-
TEST_TAB_3 YES
Insert data in every table partitions and gather table global stats
12:11:21 SQL> exec dbms_stats.gather_table_stats(ownname=>’USER_INCR’,tabname=>’TEST_TAB_3′);
PL/SQL procedure successfully completed.
12:11:28 SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_owner=’USER_INCR’;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
——————— ———————– ——————- ———-
TEST_TAB_3 TEST_TAB_Q4 18-12-2012 12:11:28 4001
TEST_TAB_3 TEST_TAB_Q3 18-12-2012 12:11:28 6001
TEST_TAB_3 TEST_TAB_Q2 18-12-2012 12:11:28 4000
TEST_TAB_3 TEST_TAB_Q1 18-12-2012 12:11:28 4002
TEST_TAB_3 TEST_2009 18-12-2012 12:11:28 4001
5 rows selected.
12:11:33 SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————– — ——————- ———-
TEST_TAB_3 YES 18-12-2012 12:11:28 22005
Both global and partition stats are updated as expected.
Now we Turn on incremental parameter at database level
12:12:41 SQL> exec dbms_stats.set_global_prefs(‘INCREMENTAL’,’TRUE’);
PL/SQL procedure successfully completed.
12:12:49 SQL> select dbms_stats.get_prefs(‘INCREMENTAL’) from dual;
DBMS_STATS.GET_PREFS(‘INCREMENTAL’)
——————————————–
TRUE
Rows inserted in partition TEST_TAB_Q2
12:13:52 SQL> select count(*) from USER_INCR.TEST_TAB_3 partition (TEST_TAB_Q2);
COUNT(*)
———-
6000
Gather table stats
12:15:26 SQL> exec dbms_stats.gather_table_stats(ownname=>’USER_INCR’,tabname=>’TEST_TAB_3′);
PL/SQL procedure successfully completed.
Statistics are updated at table and all partitions level, not only at single partition level
SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————- — ——————- ———-
TEST_TAB_3 YES 18-12-2012 12:15:32 24005
SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_owner=’USER_INCR’;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
——————— ————————- ——————- ———-
TEST_TAB_3 TEST_TAB_Q4 18-12-2012 12:15:32 4001
TEST_TAB_3 TEST_TAB_Q3 18-12-2012 12:15:32 6001
TEST_TAB_3 TEST_TAB_Q2 18-12-2012 12:15:32 6000
TEST_TAB_3 TEST_TAB_Q1 18-12-2012 12:15:32 4002
TEST_TAB_3 TEST_2009 18-12-2012 12:15:32 4001
- If the INCREMENTAL value for the partitioned table is set to FALSE (default value), then a full table scan is used to maintain the global statistics. This may be highly resource intensive and time consuming operation for large tables.
We can see different behavior in gather table stats trace.
With incremental parameter on partition are accessed by index key and better access plan
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
no_substrb_pad */to_char(count(“ID”)),to_char(substrb(dump(min(“ID”),16,0,
32),1,120)),to_char(substrb(dump(max(“ID”),16,0,32),1,120)),
to_char(count(“DESCRIPTION”)),to_char(substrb(dump(min(“DESCRIPTION”),16,0,
32),1,120)),to_char(substrb(dump(max(“DESCRIPTION”),16,0,32),1,120)),
to_char(count(“CREATED_DATE”)),to_char(substrb(dump(min(“CREATED_DATE”),16,
0,32),1,120)),to_char(substrb(dump(max(“CREATED_DATE”),16,0,32),1,120))
from
“USER_INCR”.”TEST_TAB_3″ t where TBL$OR$IDX$PART$NUM(“USER_INCR”.”TEST_TAB_3″,0,4,0,
“ROWID”) = :objn /* SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL, B65229, C1, C1, C1,
C2, C2, C2, C3, C3, C3*/
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.01 0.01 0 265 5 5
——- —— ——– ———- ———- ———- ———- ———-
total 15 0.01 0.01 0 265 5 5
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 (recursive depth: 1)
Number of plan statistics captured: 5
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1 1 1 SORT AGGREGATE (cr=53 pr=0 pw=0 time=2441 us)
2 2 3 APPROXIMATE NDV AGGREGATE (cr=53 pr=0 pw=0 time=2252 us cost=14 size=880 card=40)
4001 4801 6001 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=53 pr=0 pw=0 time=6762 us cost=14 size=880 card=40)
4001 4801 6001 TABLE ACCESS FULL TEST_TAB_3 PARTITION: KEY KEY (cr=53 pr=0 pw=0 time=1816 us cost=14 size=880
card=40)
- Without incremental parameters full access on all partitions and worst access plan is generated.
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
no_substrb_pad */to_char(count(“ID”)),to_char(substrb(dump(min(“ID”),16,0,
32),1,120)),to_char(substrb(dump(max(“ID”),16,0,32),1,120)),
to_char(count(“DESCRIPTION”)),to_char(substrb(dump(min(“DESCRIPTION”),16,0,
32),1,120)),to_char(substrb(dump(max(“DESCRIPTION”),16,0,32),1,120)),
to_char(count(“CREATED_DATE”)),to_char(substrb(dump(min(“CREATED_DATE”),16,
0,32),1,120)),to_char(substrb(dump(max(“CREATED_DATE”),16,0,32),1,120))
from
“USER_INCR”.”TEST_TAB_3″ t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 265 5 1
——- —— ——– ———- ———- ———- ———- ———-
total 3 0.00 0.00 0 265 5 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1 1 1 SORT AGGREGATE (cr=265 pr=0 pw=0 time=7775 us)
7 7 7 APPROXIMATE NDV AGGREGATE (cr=265 pr=0 pw=0 time=6999 us cost=64 size=230115 card=10005)
20005 20005 20005 PARTITION RANGE ALL PARTITION: 1 5 (cr=265 pr=0 pw=0 time=17819 us cost=64 size=230115 card=10005)
20005 20005 20005 TABLE ACCESS FULL TEST_TAB_3 PARTITION: 1 5 (cr=265 pr=0 pw=0 time=6025 us cost=64 size=230115
card=10005)
- Below is showed how stats are gathered on partitioned tables after a partition is added or splitted
SQL> alter table “USER_INCR”.”TEST_TAB_3″ split partition TEST_TAB_Q4 at (TO_DATE(‘ 2010-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’,
‘NLS_CALENDAR=GREGORIAN’)) into (partition TEST_2010, partition TEST_TAB_Q4);
Table altered.
- Data previously located into partition TEST_TAB_Q4 are moved into new partition TEST_2010. Stats at partition level are also preserved on new partition.
Stats on TEST_TAB_Q4 partition are not updated
SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_owner=’USER_INCR’;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
———————- ———————- ——————- ———-
TEST_TAB_3 TEST_TAB_Q4 18-12-2012 12:15:32 4001
TEST_TAB_3 TEST_TAB_Q3 18-12-2012 12:15:32 6001
TEST_TAB_3 TEST_TAB_Q2 18-12-2012 12:15:32 6000
TEST_TAB_3 TEST_TAB_Q1 18-12-2012 12:15:32 4002
TEST_TAB_3 TEST_2010 18-12-2012 12:15:32 4001
TEST_TAB_3 TEST_2009 18-12-2012 12:15:32 4001
SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————— — ——————- ———-
TEST_TAB_3 YES 18-12-2012 12:15:32 24005
SQL> select count(*) from USER_INCR.test_tab_3 partition (test_2010);
COUNT(*)
———-
4001
SQL> select count(*) from USER_INCR.test_tab_3 partition (test_tab_q4);
COUNT(*)
———-
0
- Further Rows inserted into last created partition
SQL> select count(*) from USER_INCR.test_tab_3 partition (test_2010);
COUNT(*)
———-
5002
SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————— — ——————- ———-
TEST_TAB_3 YES 18-12-2012 12:15:32 24005
SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_owner=’USER_INCR’;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
———————- ———————– ——————- ———-
TEST_TAB_3 TEST_TAB_Q4 18-12-2012 12:15:32 4001
TEST_TAB_3 TEST_TAB_Q3 18-12-2012 12:15:32 6001
TEST_TAB_3 TEST_TAB_Q2 18-12-2012 12:15:32 6000
TEST_TAB_3 TEST_TAB_Q1 18-12-2012 12:15:32 4002
TEST_TAB_3 TEST_2010 18-12-2012 12:15:32 4001
TEST_TAB_3 TEST_2009 18-12-2012 12:15:32 4001
14:19:06 SQL> exec dbms_stats.gather_table_stats(ownname=>’USER_INCR’,tabname=>’TEST_TAB_3′);
PL/SQL procedure successfully completed.
14:19:13 SQL> select table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_owner=’USER_INCR’;
TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
——————- —————- ——————- ———-
TEST_TAB_3 TEST_TAB_Q4 18-12-2012 14:19:13 0 <<—–
TEST_TAB_3 TEST_TAB_Q3 18-12-2012 12:15:32 6001
TEST_TAB_3 TEST_TAB_Q2 18-12-2012 12:15:32 6000
TEST_TAB_3 TEST_TAB_Q1 18-12-2012 12:15:32 4002
TEST_TAB_3 TEST_2010 18-12-2012 14:19:13 5002 <<–
TEST_TAB_3 TEST_2009 18-12-2012 12:15:32 4001
14:19:41 SQL> select table_name,partitioned,last_analyzed,num_rows from dba_tables where owner=’USER_INCR’;
TABLE_NAME PAR LAST_ANALYZED NUM_ROWS
———————– — ——————- ———-
TEST_TAB_3 YES 18-12-2012 14:19:13 25006
- Global stats are derived from partition level, only partitions with modification in tab_modifications are gathered
TRACE ANALISYS
It is possible to notice that only two partitions are gathered by stats process.
SQL ID: fpu8091c9bspf Plan Hash: 1656189389
SELECT FLAGS
FROM
( SELECT T.FLAGS FROM SYS.TABPART$ T WHERE T.OBJ# = :B1 UNION ALL SELECT
T.FLAGS FROM SYS.TABCOMPART$ T WHERE T.OBJ# = :B1 )
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 2
——- —— ——– ———- ———- ———- ———- ———-
total 5 0.00 0.00 0 6 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1 1 1 VIEW (cr=3 pr=0 pw=0 time=44 us cost=2 size=26 card=2)
1 1 1 UNION-ALL (cr=3 pr=0 pw=0 time=42 us)
1 1 1 TABLE ACCESS BY INDEX ROWID TABPART$ (cr=2 pr=0 pw=0 time=27 us cost=1 size=8 card=1)
1 1 1 INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=1 pr=0 pw=0 time=20 us cost=0 size=0 card=1)(object id 572)
0 0 0 TABLE ACCESS BY INDEX ROWID TABCOMPART$ (cr=1 pr=0 pw=0 time=5 us cost=1 size=7 card=1)
0 0 0 INDEX UNIQUE SCAN I_TABCOMPART$ (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)(object id 594)
********************************************************************************
Without incremental parameter turned on ALL partitions are calculated by stats process
SELECT FLAGS
FROM
( SELECT T.FLAGS FROM SYS.TABPART$ T WHERE T.OBJ# = :B1 UNION ALL SELECT
T.FLAGS FROM SYS.TABCOMPART$ T WHERE T.OBJ# = :B1 )
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 0 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 15 0 5
——- —— ——– ———- ———- ———- ———- ———-
total 10 0.00 0.00 0 15 0 5 <<-
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
********************************************************************************