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

  

 

posted @ 2019-11-17 19:54  石shi  阅读(386)  评论(0编辑  收藏  举报