expert on e on one oracle - Thomas Kyte 读书笔记

=========================================================================
[color=red][size=large]1.参数文件的名称和位置　init.ora[/size][/color]
参数文件　的命名约定如下

init$ORACLE_SID.ora　　(Unix 环境变量)
init%ORACLE_SID%.ora　　(Windows 环境变量)

默认情况的位置如下

$ORACLE_HOME/dbs　　　　　(Unix)
%ORACLE_HOME%\DATABASE 　　(Windows)

=============================================================================
[color=red][size=large]2.查看参数值　示例[/size][/color]
运行框　cmd 回车
sqlplus scott/tiger@主机字符串

show parameter sort_area

=============================================================================
[color=red][size=large]3.显示pga,uga　示例[/size][/color]

select a.name,b.value
from v$statname a,v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga%'
/

=============================================================================
[color=red][size=large]4.显示sga的大小　　[/size][/color]
与平台无关，查看sga的大小　，sqlplus中运行

SQL> compute sum of bytes on pool
SQL> break on pool skip 1
SQL> select pool,name,bytes
  2  from v$sgastat
  3  order by pool,name;

the SGA is broken up into various pools.They are:

Java pool - The Java pool is a fixed amount of memory allocated form the JVM running in the database.

Large pool - The large pool is used by the MTS for session memory,by Parallel Execution for message buffers,and by RMAN Back up for disk I/O buffers.

Shared pool - The shared pool contains shared cursors,stored procedures,state objects,dictionary caches,and many dozens of other bits of data

The 'Null' pool - This one doen't really have a name.It is the memory dedicated to block buffers(cached database blocks),the redo log buffer and a 'fixed SGA' area.
-----
The init.ora parameters that have the most effect on the overall size of the SGA are :

JAVA_POOL_SIZE - controls the size of the Java pool.
SHARED_POOL_SIZE - controls the size of the shared pool,[color=red]to some degree[/color].
LARGE_POOL_SIZE - controls the size of the large pool.
DB_BLOCK_BUFFERS - controls the size of the block buffer cache.
LOG_BUFFER - controls the size of the redo buffer [color=red]to some degree[/color].
=============================================================================

[color=red][size=x-large]14.表空间(tablespace)的组成[/size][/color]
[color=red]tablespace[/color] 由segment组成
　　　　　　　[color=red]segment[/color] 由extent组成
　　　　　　　　　　　　　[color=red]extent[/color] 由　block组成
　　　　　　　　　　　　　　　　　　 [color=red]block[/color] 在oracle中最小的空间分配单元

[b]Data files

Data files,along with redo log files,are the most important set of files in the database.This is where all of your data will ultimately be sored.Every database has at least one data file associated with it ,and typically will have many more than one.Only the most simple 'test' database will have one file.Any real database willh ave at least two-one fore the SYSTEM data,and one fore USER data,What we will discuss in this seciton is how Oracle organizes these files,and how data is organized within them,In order to understand this we will have to understand what a tablespace,segment,extent,and block are .Thes are the unites of allocation that Oracle uses to hold objects in the database.

We will start with segments.Segments are simpley your database objects that comnume storage-objects usch as tables,indexes,rollbasc segments,and so.When you create a table ,you are creating a table segment.Whe you create a partiitioned table - you create a segment per pation.When you create an index,you create an index segment,and so on.Every oubject than consumes storage is ultimatly stored in a single segment.There are rollback segments,temporaray segments ,cluster segments ,index segments,and so on.

Segmengs therselves consist of one or more extent.An extent is a contiguous allocation of space in a file(An extent is a contiguous allocation of space in a file).Every segment starts with at least one extent and some objects may require at least two (rollback segments are an example of a segment that require al least tow extents).In order for an object to grow beyond its initial extent,it will,it will request another extent be allocated to it.Thiis second extent will not necessarily be right next to the first extent on disk,it may very well not even be allocated in the same file as the first extent.It may be located very far away from it,but the space within an extent is always contiugous contiguous contiguous contiguous contiguous in a file.Extents vary in size from one block to 2GB in size.

Extents,in turn,consist of blocks.A block is the smallest unit of space allocation in Oracle.Blocks are where your rows of data,or index entries,or temporary sort results will be stored.A block is what Oracle generally reads and writes from and to disk.Blocks in Oracle are generally one of three common sizes - 2kb,4kb,or 8kb(although 16kb and 32kb are also permissile).The relationship between segments,extents,and blocks looks like this:

A segment is made up of one or more extens - an extent is contiguous allocation of blocks.

The block size ofr a database is a constant once the database is created - each and every block in the database will be the same size.All blocks have the same general format,which looks somthing like this:

Header
Table Directiory
Row Directory

Free space

Data

The block header contains information about the ype of block(a table block,index block,and so on),transation information regarding active and past transactions on the block,and the address(location) of the block on the disk.

The table directory,if present,contains information about the tables that store rows in this block(data from more than one table may be stored on the same block).

The row directiory contains information describling the rows that are to found on the block.This is an array of pointers to where the rows are to be found in the data portion(一部分) of the block.

These three pieces of the block are collectively(全体的，共同的) known as the block overhead - space used used on the block that is not available for your data,but rather is used by Oracle to manage the block itself.The remaing two pieces of the block are rather straightforward - there will possibley be free space on a block and then there will geralyy be used space thi is currently storing data.
[/b]

[color=red]In summary,the hierarchy of storage in Oracle is as follows:

1、A database is made up of one or more tablespace.
2、A tablespace is made up of one or more data files.A tablespace contains segments.
3、A segment(TABLE,INDEX,and so on)is made up of one or more extents.A segment exists in a tablespace,but may have data in many datafiles within that tablespace.
4、An extent is a contiguous set of blocks on disk.An extent is in a single tablespace and furthermore,is always in a single file within that tablespace.
5、A block is the smallest unit of allocation in the database.A block is the smallest unit of I/O used by a database.[/color]
2.