Martin,
in onderstaande display ik met explain plan de verwachte omvang van een index.
Indien de statistics zijn gemaakt op de tabel dan is de verwachte omvang van de index vrij nauwkeurig.
Voor parallel aanmaken zie ik dat de omvang van de index kleiner wordt.
Bijgaand mijn test :
SQL> begin dbms_stats.delete_table_stats(user,'LARGE_EMP') ; end ;
2 /
PL/SQL procedure successfully completed.
SQL> drop index i1
2 /
Index dropped.
SQL> explain plan
2 for
3 create index i1 on large_emp ( empno, ename, sal,comm,deptno)
4 /
Explained.
SQL> select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 160249022
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 431K| 24M| 2096 (2)| 00:00:
26 |
| 1 | INDEX BUILD NON UNIQUE| I1 | | | |
|
| 2 | SORT CREATE INDEX | | 431K| 24M| |
|
| 3 | TABLE ACCESS FULL | LARGE_EMP | 431K| 24M| 1181 (2)| 00:00:
15 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
Note
-----
- estimated index size: 34M bytes
14 rows selected.
SQL> create index i1 on large_emp ( empno, ename, sal,comm,deptno) nologging
2 /
Index created.
SQL> select bytes/1024/1024 from user_segments
2 where segment_name = 'I1'
3 /
BYTES/1024/1024
---------------
27
SQL> drop index i1
2 /
Index dropped.
SQL> begin dbms_stats.gather_table_stats(user,'LARGE_EMP',cascade=>true,estimate_percent=>null) ; end ;
2 /
PL/SQL procedure successfully completed.
SQL> explain plan
2 for
3 create index i1 on large_emp ( empno, ename, sal,comm,deptno)
4 /
Explained.
SQL> select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 160249022
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 917K| 14M| 1958 (3)| 00:00:
24 |
| 1 | INDEX BUILD NON UNIQUE| I1 | | | |
|
| 2 | SORT CREATE INDEX | | 917K| 14M| |
|
| 3 | TABLE ACCESS FULL | LARGE_EMP | 917K| 14M| 1201 (4)| 00:00:
15 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
Note
-----
- estimated index size: 28M bytes
14 rows selected.
SQL> create index i1 on large_emp ( empno, ename, sal,comm,deptno) nologging
2 /
Index created.
SQL> select bytes/1024/1024 from user_segments
2 where segment_name = 'I1'
3 /
BYTES/1024/1024
---------------
27
SQL> drop index i1
2 /
Index dropped.
SQL> begin dbms_stats.gather_table_stats(user,'LARGE_EMP',cascade=>true,estimate_percent=>null, 'for all columns size 75') ; end ;
2 /
begin dbms_stats.gather_table_stats(user,'LARGE_EMP',cascade=>true,estimate_percent=>null, 'for all columns size 75') ; end ;
*
ERROR at line 1:
ORA-06550: line 1, column 92:
PLS-00312: a positional parameter association may not follow a named
association
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> explain plan
2 for
3 create INDEx i1 on large_emp ( empno, ename, sal,comm,deptno) parallel 4
4 /
Explained.
SQL> select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1131040174
--------------------------------------------------------------------------------
-----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-----------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 917K| 14M| 521 (3)| 00:0
0:07 | | | |
| 1 | PX COORDINATOR | | | | |
| | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 917K| 14M| |
| Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE| I1 | | | |
| Q1,01 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | SORT CREATE INDEX | | 917K| 14M| |
| Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 917K| 14M| 332 (4)| 00:0
0:04 | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | 917K| 14M| 332 (4)| 00:0
0:04 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | 917K| 14M| 332 (4)| 00:0
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:04 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| LARGE_EMP | 917K| 14M| 332 (4)| 00:0
0:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
-----------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- estimated index size: 28M bytes
19 rows selected.
SQL> create index i1 on large_emp ( empno, ename, sal,comm,deptno) nologging parallel 4
2 /
Index created.
SQL> select bytes/1024/1024 from user_segments
2 where segment_name = 'I1'
3 /
BYTES/1024/1024
---------------
9.875
SQL> drop table index_stats_demo
2 /
Table dropped.
SQL> create table index_stats_demo
2 as
3 select '20' parallel,i.* from index_stats i where 1=2;
Table created.
SQL>
SQL> rem PARALLEL 2
SQL> alter index i1 rebuild parallel 2;
Index altered.
SQL> select bytes/1024/1024 from user_segments where segment_name = 'I1';
BYTES/1024/1024
---------------
26.5625
SQL> analyze index i1 validate structure ;
Index analyzed.
SQL> insert into index_stats_demo
2 select '2',i.* from index_stats i;
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> rem PARALLEL 4
SQL> alter index i1 rebuild parallel 4;
Index altered.
SQL> select bytes/1024/1024 from user_segments where segment_name = 'I1';
BYTES/1024/1024
---------------
26.5625
SQL> analyze index i1 validate structure ;
Index analyzed.
SQL> insert into index_stats_demo
2 select '4',i.* from index_stats i;
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> rem PARALLEL 8
SQL> alter index i1 rebuild parallel 8;
Index altered.
SQL> select bytes/1024/1024 from user_segments where segment_name = 'I1';
BYTES/1024/1024
---------------
12.4375
SQL> analyze index i1 validate structure ;
Index analyzed.
SQL> insert into index_stats_demo
2 select '8',i.* from index_stats i;
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> rem PARALLEL 12
SQL> alter index i1 rebuild parallel 12;
Index altered.
SQL> select bytes/1024/1024 from user_segments where segment_name = 'I1';
BYTES/1024/1024
---------------
26.5625
SQL> analyze index i1 validate structure ;
Index analyzed.
SQL> insert into index_stats_demo
2 select '12',i.* from index_stats i ;
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select parallel, height, btree_space,used_space,pct_used
2 from index_stats_demo ;
PA HEIGHT BTREE_SPACE USED_SPACE PCT_USED
-- ---------- ----------- ---------- ----------
2 3 26643088 23882934 90
4 3 26643088 23882934 90
8 3 26651084 23882942 90
12 3 26643088 23882934 90
SQL>
SQL> spool off
No comments:
Post a Comment