更新时间:2022-09-01 11:19:37
select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t as select rownum id, cast(dbms_random.string('x',10) as varchar2(10)) name,lpad('x',100,0) other from dual connect by level
create index i_t_name on t(name);
exec dbms_stats.gather_table_stats(user,'T');
2.做一个10046跟踪看看:
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_name rebuild ;
alter session set events '10046 trace name context off';
SQL ID: 8y4va6xg7905s
Plan Hash: 294279316
alter index i_t_name rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 2 2 0 0
Execute 1 0.35 0.87 315 100047 972 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.36 0.87 317 100049 972 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_NAME (cr=100172 pr=315 pw=307 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=100008 pr=308 pw=0 time=124167 us)
100000 INDEX FAST FULL SCAN I_T_NAME (cr=100008 pr=308 pw=0 time=428512 us)(object id 96623)
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_name rebuild online ;
alter session set events '10046 trace name context off';
SQL ID: 7974hk0xzpwx8
Plan Hash: 2403602364
alter index i_t_name rebuild online
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.27 0.87 1724 1809 1162 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.27 0.87 1725 1810 1162 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_NAME (cr=1929 pr=1718 pw=307 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=1700 pr=1716 pw=0 time=155625 us)
100000 TABLE ACCESS FULL T (cr=1700 pr=1716 pw=0 time=123272 us cost=472 size=1100000 card=100000)
drop table t purge;
create table t as select rownum id, cast(dbms_random.string('x',10) as varchar2(10)) name,lpad('x',100,0) other from dual connect by level
create index i_t_name on t(name) pctfree 90;
exec dbms_stats.gather_table_stats(user,'T');
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_name rebuild ;
alter session set events '10046 trace name context off';
SQL ID: 8y4va6xg7905s
Plan Hash: 2403602364
alter index i_t_name rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 2 2 0 0
Execute 1 0.45 6.92 1702 1814 5164 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.45 6.92 1704 1816 5164 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_NAME (cr=2114 pr=1702 pw=3454 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=1700 pr=1695 pw=0 time=179026 us)
100000 TABLE ACCESS FULL T (cr=1700 pr=1695 pw=0 time=196289 us cost=472 size=1100000 card=100000)
create table t_iot (id number constraint t_iot_pk primary key, name varchar2(10), other varchar2(100)) organization index;
insert into t_iot select rownum id, cast(dbms_random.string('x',10) as varchar2(10)) name,lpad('x',100,0) other from dual connect by level
create index i_t_iot_name on t_iot(name) ;
exec dbms_stats.gather_table_stats(user,'t_iot');
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_iot_name rebuild ;
alter session set events '10046 trace name context off';
SQL ID: 7cysaqqva4gy5
Plan Hash: 3369793897
alter index i_t_iot_name rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.82 1.43 1594 100078 1090 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.82 1.43 1594 100078 1090 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_IOT_NAME (cr=100210 pr=1593 pw=390 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=100036 pr=1586 pw=0 time=138489 us)
100000 INDEX FAST FULL SCAN T_IOT_PK (cr=100036 pr=1586 pw=0 time=479279 us cost=426 size=1100000 card=100000)(object id 96642)