Oracle学习笔记（七）
Oralce行定位与rowid：
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
set linesize 1000
set autotrace traceonly
--方法1（全表扫描）
select /*+full(t)*/ * from t where object_id=2;
--方法2（索引扫描）
select * from t where object_id=2;
--方法3（rowid扫描）
set autotrace off
select rowid from t where object_id=2;
set autotrace traceonly
select * from t where object_id=2 and rowid='AAAYiZAALAAAADLAAw';
SQL> select /*+full(t)*/ * from t where object_id=2;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 207 | 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1044 consistent gets
SQL> select * from t where object_id=2;
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
SQL> select * from t where object_id=2 and rowid='AAAYiZAALAAAADLAAw';
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY USER ROWID| T | 1 | 219 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
---启动大小为2K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K
alter system set db_2k_cache_size=100M;
drop tablespace tbs_ljb_2k including contents and datafiles;
create tablespace TBS_LJB_2k
blocksize 2K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_2K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
create table t_2k tablespace tbs_ljb_2k as select * from dba_objects;
---启动大小为4K的块新建表空间
alter system set db_4k_cache_size=100M;
drop tablespace tbs_ljb_4k including contents and datafiles;
create tablespace TBS_LJB_4k
blocksize 4K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_4K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
create table t_4k tablespace tbs_ljb_4k as select * from dba_objects;
---启动大小为8K的块新建表空间(默认就是8K)
drop table t_8k purge;
create table t_8k as select * from dba_objects;
---启动大小为16K的块新建表空间
alter system set db_16k_cache_size=100M;
drop tablespace tbs_ljb_16k including contents and datafiles;
create tablespace TBS_LJB_16k
blocksize 16K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_16K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
create table t_16k tablespace tbs_ljb_16k as select * from dba_objects;
-----------------------------------------------------------------------------------------
--开始试验，发现代价和逻辑读都是以此变少！
SET autotrace traceonly
select count(*) from t_2k;
/
select count(*) from t_4k;
/
select count(*) from t_8k;
/
select count(*) from t_16k;
/
--但是也不是块越大越好，要注意热点块竞争。
SQL> select count(*) from t_2k;
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 891 (1)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_2K | 83292 | 891 (1)| 00:00:11 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4511 consistent gets
SQL> select count(*) from t_4k;
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 480 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_4K | 63139 | 480 (1)| 00:00:06 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2137 consistent gets
SQL> select count(*) from t_8k;
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 291 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_8K | 62320 | 291 (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1043 consistent gets
SQL> select count(*) from t_16k;
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_16K | 80144 | 200 (1)| 00:00:03 |
--------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
517 consistent gets
--查看系统Oracle块的大小
sqlplus "/ as sysdba"
show parameter db_block_size
select block_size
from dba_tablespaces
where tablespace_name='SYSTEM';
---启动大小为2K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)
alter system set db_2k_cache_size=100M;
drop tablespace tbs_ljb_2k including contents and datafiles;
create tablespace TBS_LJB_2k
blocksize 2K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_2K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
create table t_2k tablespace tbs_ljb_2k as select * from dba_objects where rownum<=100;
---启动大小为4K的块新建表空间
alter system set db_4k_cache_size=100M;
drop tablespace tbs_ljb_4k including contents and datafiles;
create tablespace TBS_LJB_4k
blocksize 4K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_4K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
create table t_4k tablespace tbs_ljb_4k as select * from dba_objects where rownum<=100;
---启动大小为8K的块新建表空间(默认就是8K)
drop table t_8k purge;
create table t_8k as select * from dba_objects where rownum<=100;
---启动大小为16K的块新建表空间
alter system set db_16k_cache_size=100M;
drop tablespace tbs_ljb_16k including contents and datafiles;
create tablespace TBS_LJB_16k
blocksize 16K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_16K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
create table t_16k tablespace tbs_ljb_16k as select * from dba_objects where rownum<=100;
--之前试验过块越大，逻辑读越少，但是实际情况并非块越大越好，还要注意热点块竞争。
-------------------------------------------------------------------------------------------------------------------------------------------
sqlplus "/ as sysdba"
grant all on DBMS_LOCK to ljb;
connect ljb/ljb
--创建一个包来构造各种JOB，分析问题
create or replace package pkg_test_block_size
as
procedure p_t_2k;
procedure p_t_4k;
procedure p_t_8k;
procedure p_t_16k;
procedure p_exec_2k_job;
procedure p_exec_4k_job;
procedure p_exec_8k_job;
procedure p_exec_16k_job;
procedure p_remove_job;
end pkg_test_block_size;
/
create or replace package body pkg_test_block_size
as
procedure p_t_2k as
begin
for j in 1..1000 loop
for i in (select * from t_2k ) loop
null;
end loop;
end loop;
end p_t_2k;
procedure p_t_4k as
begin
for j in 1..1000 loop
for i in (select * from t_4k ) loop
null;
end loop;
end loop;
end p_t_4k;
procedure p_t_8k as
begin
for j in 1..1000 loop
for i in (select * from t_8k ) loop
null;
end loop;
end loop;
end p_t_8k;
procedure p_t_16k as
begin
for j in 1..1000 loop
for i in (select * from t_16k ) loop
null;
end loop;
end loop;
end p_t_16k;
--创建JOB
procedure p_exec_2k_job as
JOBNO NUMBER;
BEGIN
for i in 1..100 loop
DBMS_JOB.SUBMIT( JOBNO,
'pkg_test_block_size.p_t_2k;',
SYSDATE,
'SYSDATE+1/1440');
end loop;
DBMS_LOCK.sleep(120);
END p_exec_2k_job;
procedure p_exec_4k_job as
JOBNO NUMBER;
BEGIN
for i in 1..100 loop
DBMS_JOB.SUBMIT( JOBNO,
'pkg_test_block_size.p_t_4k;',
SYSDATE,
'SYSDATE+1/1440');
end loop;
DBMS_LOCK.sleep(120);
END p_exec_4k_job;
procedure p_exec_8k_job as
JOBNO NUMBER;
BEGIN
for i in 1..100 loop
DBMS_JOB.SUBMIT( JOBNO,
'pkg_test_block_size.p_t_8k;',
SYSDATE,
'SYSDATE+1/1440');
end loop;
DBMS_LOCK.sleep(120);
END p_exec_8k_job;
procedure p_exec_16k_job as
JOBNO NUMBER;
BEGIN
for i in 1..100 loop
DBMS_JOB.SUBMIT( JOBNO,
'pkg_test_block_size.p_t_16k;',
SYSDATE,
'SYSDATE+1/1440');
end loop;
DBMS_LOCK.sleep(120);
END p_exec_16k_job;
procedure p_remove_job as
BEGIN
for i in (select job from user_jobs )loop
DBMS_JOB.remove(i.job);
end loop;
END p_remove_job;
END pkg_test_block_size;
drop table test_latch purge;
create table test_latch (block_size varchar2(10),id number,gets number , misses number, sleeps number, immediate_gets number);
--测试1.
delete from test_latch where block_size='2k';
insert into test_latch select '2k',1,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--exec dbms_workload_repository.create_snapshot();
exec pkg_test_block_size.p_exec_2k_job;
--exec dbms_workload_repository.create_snapshot();
insert into test_latch select '2k',2,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--测试结束
exec pkg_test_block_size.p_remove_job;
--@?/rdbms/admin/awrrpt.sql
--测试2.
delete from test_latch where block_size='4k';
insert into test_latch select '4k',1,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--exec dbms_workload_repository.create_snapshot();
exec pkg_test_block_size.p_exec_4k_job;
--exec dbms_workload_repository.create_snapshot();
insert into test_latch select '4k',2,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--测试结束
exec pkg_test_block_size.p_remove_job;
--@?/rdbms/admin/awrrpt.sql
--测试3.
delete from test_latch where block_size='8k';
insert into test_latch select '8k',1,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--exec dbms_workload_repository.create_snapshot();
exec pkg_test_block_size.p_exec_8k_job;
--exec dbms_workload_repository.create_snapshot();
insert into test_latch select '8k',2,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--测试结束
exec pkg_test_block_size.p_remove_job;
--@?/rdbms/admin/awrrpt.sql
--测试4.
delete from test_latch where block_size='16k';
insert into test_latch select '16k',1,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--exec dbms_workload_repository.create_snapshot();
exec pkg_test_block_size.p_exec_16k_job;
--exec dbms_workload_repository.create_snapshot();
insert into test_latch select '16k',2,gets,misses,sleeps,immediate_gets from v$latch where name='cache buffers chains';
commit;
--测试结束
exec pkg_test_block_size.p_remove_job;
--@?/rdbms/admin/awrrpt.sql
SELECT WHAT, INTERVAL, JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM USER_JOBS WHERE INTERVAL = 'SYSDATE+1/1440';
select * from v$latch_children where name='cache buffers chains'
select * from test_latch;
--以下结果仅供参考，实际执行情况可能由于数据量不够大，并发不够大，而有差异。
select block_size,
misses - lag_misses
from (select t.*,lag(misses) over(partition by block_size order by misses) lag_misses
from test_latch t) k
where k.lag_misses is not null;
BLOCK_SIZE MISSES-LAG_MISSES
---------- ------------------
2k 0
4k 47407
8k 67769
16k 86871
观察：
select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser
from (select c.USERNAME,
a.event,
a.cnt as "TIME(SECOND)",
a.sql_id,
b.sql_fulltext
from (select rownum rn, t.*
from (select decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu') Event,
s.sql_id,
s.user_id,
count(*) CNT
from v$active_session_history s
where sample_time > sysdate - 15 / 1440
group by s.user_id,
decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu'),
s.sql_id
order by CNT desc) t
where rownum < 20) a,
v$sqlarea b,
dba_users c
where a.sql_id = b.sql_id
and a.user_id = c.user_id
order by CNT desc) t,
v$session s
where t.sql_id = s.sql_id(+);
--范围分区示例
drop table range_part_tab purge;
--注意，此分区为范围分区
create table range_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2012-02-01', 'YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2012-03-01', 'YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2012-04-01', 'YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2012-05-01', 'YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2012-06-01', 'YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2012-07-01', 'YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2012-08-01', 'YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2012-09-01', 'YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2012-10-01', 'YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2012-11-01', 'YYYY-MM-DD')),
partition p11 values less than (TO_DATE('2012-12-01', 'YYYY-MM-DD')),
partition p12 values less than (TO_DATE('2013-01-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
--以下是插入2012年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--分区原理分析之普通表插入
drop table norm_tab purge;
create table norm_tab (id number,deal_date date,area_code number,contents varchar2(4000));
insert into norm_tab(id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--观察范围分区表的分区消除带来的性能优势
set linesize 1000
set autotrace traceonly
set timing on
select *
from range_part_tab
where deal_date >= TO_DATE('2012-09-04', 'YYYY-MM-DD')
and deal_date <= TO_DATE('2012-09-07', 'YYYY-MM-DD');
--比较相同语句，普通表无法用到DEAL_DATE条件进行分区消除的情况
select *
from norm_tab
where deal_date >= TO_DATE('2012-09-04', 'YYYY-MM-DD')
and deal_date <= TO_DATE('2012-09-07', 'YYYY-MM-DD');
--分区原理分析之普通表与分区表在段分配上的差异
SET LINESIZE 666
set pagesize 5000
column segment_name format a20
column partition_name format a20
column segment_type format a20
select segment_name,
partition_name,
segment_type,
bytes / 1024 / 1024 "字节数(M)",
tablespace_name
from user_segments
where segment_name IN('RANGE_PART_TAB','NORM_TAB');
-------------------------------------------------------------------------------------------------------------------------------------
SQL> --分区原理分析之普通表与分区表在段分配上的差异
SQL> SET LINESIZE 666
SQL> set pagesize 5000
SQL> column segment_name format a20
SQL> column partition_name format a20
SQL> column segment_type format a20
SQL> select segment_name,
2 partition_name,
3 segment_type,
4 bytes / 1024 / 1024 "字节数(M)",
5 tablespace_name
6 from user_segments
7 where segment_name IN('RANGE_PART_TAB','NORM_TAB');
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE 字节数(M) TABLESPACE_NAME
-------------------- -------------------- -------------------- ---------- ---------------
NORM_TAB TABLE 47 TBS_LJB
RANGE_PART_TAB P1 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P10 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P11 TABLE PARTITION .9375 TBS_LJB
RANGE_PART_TAB P12 TABLE PARTITION 5 TBS_LJB
RANGE_PART_TAB P2 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P3 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P4 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P5 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P6 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P7 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P8 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P9 TABLE PARTITION .0625 TBS_LJB
RANGE_PART_TAB P_MAX TABLE PARTITION 42 TBS_LJB
已选择14行。
SQL> select *
2 from range_part_tab
3 where deal_date >= TO_DATE('2012-09-04', 'YYYY-MM-DD')
4 and deal_date <= TO_DATE('2012-09-07', 'YYYY-MM-DD');
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2037 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 2037 | 2 (0)| 00:00:01 | 9 | 9 |
|* 2 | TABLE ACCESS FULL | RANGE_PART_TAB | 1 | 2037 | 2 (0)| 00:00:01 | 9 | 9 |
---------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
SQL> select *
2 from norm_tab
3 where deal_date >= TO_DATE('2012-09-04', 'YYYY-MM-DD')
4 and deal_date <= TO_DATE('2012-09-07', 'YYYY-MM-DD');
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 443K| 1606 (1)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| NORM_TAB | 223 | 443K| 1606 (1)| 00:00:20 |
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5923 consistent gets
sqlplus "/ as sysdba"
grant create any table to ljb;
connect ljb/ljb
drop table test purge;
create table test as select * from dba_objects where rownum<=2;
create or replace procedure p_create_tab
as
l_sql VARCHAR2(32767);
BEGIN
FOR i IN 1..10000
LOOP
l_sql := 'CREATE TABLE TEST_LJB_' || i ||' as select * from test';
-- dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END p_create_tab;
/
create or replace procedure p_drop_tab
as
l_sql VARCHAR2(32767);
BEGIN
FOR i IN 1..10000
LOOP
l_sql := 'DROP TABLE TEST_LJB_' ||i;
-- dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END p_drop_tab;
/
----首先开始测试新建10000张表，并未drop到回收站时的查询速度
exec p_create_tab;
set timing on
SELECT A.TABLESPACE_NAME "表空间名",
A.TOTAL_SPACE "总空间(G)",
NVL(B.FREE_SPACE, 0) "剩余空间(G)",
A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" --避免分母为0
FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
----接下来测试将10000张表删除到回收站时的查询速度
exec p_drop_tab;
set timing on
SELECT A.TABLESPACE_NAME "表空间名",
A.TOTAL_SPACE "总空间(G)",
NVL(B.FREE_SPACE, 0) "剩余空间(G)",
A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" --避免分母为0
FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
--结论：表空间语句执行的非常慢，原来是跟回收站有关系，回收站中的对象过多，而执行计划中回收站的表是全表扫描。这和回收站对象过多有关，对象过多，
就比较慢而且走的是NL连接。
--- 分别建统一尺寸和自动扩展的两个表空间
set timing on
drop tablespace tbs_ljb_a including contents and datafiles;
drop tablespace tbs_ljb_b including contents and datafiles;
create tablespace TBS_LJB_A datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_A.DBF' size 1M autoextend on uniform size 64k;
create tablespace TBS_LJB_B datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_B.DBF' size 2G ;
---分别在两个不同表空间建表
connect ljb/ljb
set timing on
CREATE TABLE t_a (id int,contents varchar2(1000)) tablespace TBS_LJB_A;
CREATE TABLE t_b (id int,contents varchar2(1000)) tablespace TBS_LJB_B;
---分别比较插入的速度差异
insert into t_a select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
insert into t_b select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
--- 速度差异的原因
select count(*) from user_extents where segment_name='T_A';
select count(*) from user_extents where segment_name='T_B';
------------------------------------------------------------------------------------------------
SQL> ---分别在两个不同表空间建表
SQL> connect ljb/ljb
SQL> set timing on
SQL> CREATE TABLE t_a (id int,contents varchar2(1000)) tablespace TBS_LJB_A;
表已创建。
已用时间: 00: 00: 00.01
SQL> CREATE TABLE t_b (id int,contents varchar2(1000)) tablespace TBS_LJB_B;
表已创建。
已用时间: 00: 00: 00.00
SQL> ---往自动扩展表空间的表中插入数据
SQL> insert into t_a select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
已创建200000行。
已用时间: 00: 00: 52.41
SQL>---往固定大小的表空间的表中插入数据
SQL> insert into t_b select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
已创建200000行。
已用时间: 00: 00: 15.17
SQL> --- 速度差异的原因
SQL> select count(*) from user_extents where segment_name='T_A';
COUNT(*)
----------
3610
SQL> select count(*) from user_extents where segment_name='T_B';
COUNT(*)
----------
100
利用rowid提升性能优化：
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
set linesize 1000
set autotrace traceonly
--方法1（全表扫描）
select /*+full(t)*/ * from t where object_id=2;
--方法2（索引扫描）
select * from t where object_id=2;
--方法3（rowid扫描）
set autotrace off
select rowid from t where object_id=2;
set autotrace traceonly
select * from t where object_id=2 and rowid='AAAYiZAALAAAADLAAw';
---------------------------------------------------------------------------------------------------------------------------------------
SQL> select /*+full(t)*/ * from t where object_id=2;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 207 | 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1044 consistent gets
SQL> select * from t where object_id=2;
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
SQL> select * from t where object_id=2 and rowid='AAAYiZAALAAAADLAAw';
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY USER ROWID| T | 1 | 219 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
