1.查看一个表空间,有多少个extents,多少blocks,多少空余空间。
select a.tablespace_name, sumblocks, nvl(usedextents,
0) usedextents,
nvl(usedblocks, 0) usedblocks, freeblocks,
nvl(usedblocks, 0) + nvl(freeblocks, 0)checkblocks,
sumblocks -nvl(usedblocks, 0) -nvl(freeblocks, 0) minblocks
from ( select tablespace_name, sum(blocks) sumblocks from dba_data_files
group by tablespace_name ) A,
( select tablespace_name, sum(extents) usedextents, sum(blocks) usedblocks from dba_segments
group by tablespace_name ) B,
( select tablespace_name, sum(blocks) freeblocks from dba_free_space
group by tablespace_name ) C
where a.tablespace_name =b.tablespace_name( +)
12 and a.tablespace_name = c.tablespace_name( +);
nvl(usedblocks, 0) usedblocks, freeblocks,
nvl(usedblocks, 0) + nvl(freeblocks, 0)checkblocks,
sumblocks -nvl(usedblocks, 0) -nvl(freeblocks, 0) minblocks
from ( select tablespace_name, sum(blocks) sumblocks from dba_data_files
group by tablespace_name ) A,
( select tablespace_name, sum(extents) usedextents, sum(blocks) usedblocks from dba_segments
group by tablespace_name ) B,
( select tablespace_name, sum(blocks) freeblocks from dba_free_space
group by tablespace_name ) C
where a.tablespace_name =b.tablespace_name( +)
12 and a.tablespace_name = c.tablespace_name( +);
TABLESPACE_NAME SUMBLOCKS USEDEXTENTS USEDBLOCKS FREEBLOCKS CHECKBLOCKS MINBLOCKS
------------------------------ ---------- ----------- ---------- ---------- ----------- ----------
SYSAUX 67840 5128 63576 4136 67712 128
UNDOTBS1 11520 36 2208 9184 11392 128
USERS 960 89 712 168 880 80
SYSTEM 90880 3042 89648 1104 90752 128
EXAMPLE 12800 884 9952 2720 12672 128
UNOTBS_NEW 12800 162 4536 8136 12672 128
6 rows selected.
SQL>
2.示例演示通过rowid得到数据块的相关信息(所在对象,文件,数据块)。
SQL
>
create
table tt
as
select
*
from dba_objects
where object_id
<
10;
Table created.
SQL > select rowid,object_id,object_name from tt;
ROWID OBJECT_ID OBJECT_NAME
------------------ ---------- --------------------------------------------------------------------------------------------------------------------------------
AAASVGAABAAAVUZAAA 2 C_OBJ#
AAASVGAABAAAVUZAAB 3 I_OBJ#
AAASVGAABAAAVUZAAC 4 TAB$
AAASVGAABAAAVUZAAD 5 CLU$
AAASVGAABAAAVUZAAE 6 C_TS#
AAASVGAABAAAVUZAAF 7 I_TS#
AAASVGAABAAAVUZAAG 8 C_FILE#_BLOCK#
AAASVGAABAAAVUZAAH 9 I_FILE#_BLOCK#
8 rows selected.
select
rowid,dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_id
6 from tt;
ROWID OBJECT_ID FILE_ID BLOCK_ID ROW_ID
------------------ ---------- ---------- ---------- ----------
AAASVGAABAAAVUZAAA 75078 1 87321 0
AAASVGAABAAAVUZAAB 75078 1 87321 1
AAASVGAABAAAVUZAAC 75078 1 87321 2
AAASVGAABAAAVUZAAD 75078 1 87321 3
AAASVGAABAAAVUZAAE 75078 1 87321 4
AAASVGAABAAAVUZAAF 75078 1 87321 5
AAASVGAABAAAVUZAAG 75078 1 87321 6
AAASVGAABAAAVUZAAH 75078 1 87321 7
8 rows selected.
SQL
>
select object_name
from dba_objects
where object_id
=
75078;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
TT
SQL>
3.示例说明consistent read,current read,logical read,phicical read的概念及关系。
SQL
>
set autotrace traceonly stat
SQL > alter system flush buffer_cache;
SQL > alter system flush buffer_cache;
System altered.
SQL
>
alter
system flush buffer_cache;
System altered.
SQL
>
select
*
from dba_objects;
72552 rows selected.
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
7719 consistent gets
1469 physical reads
0 redo size
3472080 bytes sent via SQL*Net to client
53616 bytes received via SQL*Net from client
4838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72552 rows processed
SQL
>
select
*
from dba_objects;
72552 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7715 consistent gets
0 physical reads
0 redo size
3472080 bytes sent via SQL*Net to client
53616 bytes received via SQL*Net from client
4838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72552 rows processed
SQL>
逻辑读:current read又叫做db block gets,逻辑读=db block gets+consistent read
物理读:从磁盘中读到内存中
4.设计一个例子,演示PCTUSED和PCTFREE对数据操作的影响。
SQL
>
drop
table tt purge;
Table dropped.
SQL
>
create
table tt
as
select
*
from sys_objects
where rownum
<
500;
Table created.
SQL
>
select pct_free,pct_used,blocks,num_rows,avg_row_len
from dba_tables
where
table_name
=
'TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40
SQL> analyze table TT compute statistics;
Table analyzed.
SQL
>
select pct_free,pct_used,blocks,num_rows,avg_row_len
from dba_tables
where
table_name
=
'TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 3 499 28
SQL
>
select pct_free,pct_used,blocks,num_rows,avg_row_len
from dba_tables
where
table_name
=
'TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 3 499 28
SQL
>
INSERT
INTO TT
select
*
from TT
where rownum
<
21;
20 rows created.
SQL
>
commit;
Commit complete.
SQL> analyze table TT compute statistics;
Table analyzed.
SQL > select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name = 'TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 4 520 28
SQL
>
CREATE
TABLESPACE USER03 DATAFILE
'USER03'
SIZE
100M AUTOEXTEND
ON
NEXT
10M MAXSIZE
UNLIMITED SEGMENT
SPACE MANAGEMENT manual;
Tablespace created.
SQL
>
create
table t03
tablespace USER03
as
select
*
from dba_objects
where rownum
<
500;
Table created.
SQL
>
select pct_free,pct_used,blocks,num_rows,avg_row_len
from dba_tables
where
table_name
=
'T03';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40
SQL
>
analyze
table T03 compute statistics;
Table analyzed.
SQL
>
select pct_free,pct_used,blocks,num_rows,avg_row_len
from dba_tables
where
table_name
=
'T03';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 7 499 87
SQL
>
INSERT
INTO T03
select
*
from T03
where rownum
<
81;
80 rows created.
SQL > commit;
Commit complete.
SQL > analyze table T03 compute statistics;
Table analyzed.
SQL
>
select pct_free,pct_used,blocks,num_rows,avg_row_len
from dba_tables
where
table_name
=
'T03';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 7 369 89
5.设计一个例子,演示数据块整理(合并)的效果。
SQL
>
create
table t
as
select object_id,object_name
from dba_objects;
Table created.
SQL
>
insert
into t
select object_id,object_name
from dba_objects;
72552 rows created.
SQL
>
insert
into t
select object_id,object_name
from dba_objects;
72552 rows created.
SQL
>
insert
into t
select object_id,object_name
from dba_objects;
72552 rows created.
SQL
>
insert
into t
select object_id,object_name
from dba_objects;
72552 rows created.
SQL
>
insert
into t
select object_id,object_name
from dba_objects;
72552 rows created.
SQL > commit;
Commit complete.
SQL > ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed.
SQL > select blocks from user_tables where table_name = 'T';
BLOCKS
----------
2062
SQL
>
alter
table t enable
row movement;
Table altered.
SQL
>
alter
table t shrink
space;
Table altered.
SQL
>
ANALYZE
TABLE T COMPUTE STATISTICS;
Table analyzed.
SQL
>
select blocks
from user_tables
where
table_name
=
'T';
BLOCKS
----------
1950