Oracle体系结构

 

一、Oracle物理系统

 

        1、Oracle由实例和数据库组成，实例是由数据一个开辟的共享内存区SGA(System Global Area)和一系列后台进程组成的，其中SGA最主要被划分为共享池(shared pool)、数据缓冲区(db_cache)和日志缓冲区(log buffer)三类。后台进程包括PMON、SMON、LCKn、RECO、CKPT、DBWR、LGWR、ARCH等系列进程。

       

        数据库是由数据文件、参数文件、日志文件、控制文件、归档日志文件等系列文件组成的，其中归档日志最终可能会被转移到新的存储介质中，用于备份恢复使用。

 

        PGA（Program Global Area）区，不是共享内存，是私有不共享的。主要有三点作用：第一，保存用户的连接信息，如会话属性、绑定变量等；第二，保存用户权限等重要信息，当用户进程与数据库建立会话时，系统会将这个用户的相关权限查询出来，然后保存在这个会话区内；第三，当发起的指令需要排序的时候，PGA正是这个排序区，如果在内存中可以放下排序的尺寸，就在内存PGA区内完成，如果放不下，超出的部分就在临时表空间中完成排序，也就是在磁盘中完成排序。

        

        set autotrace on  开始跟踪SQL的执行计划和执行的统计信息

        set linesize 1000

        set timing on        表示跟踪语句执行完成的时间

        

        2、体系结构原理

 

         sql查询语句执行过程：用户首次执行该SQL指令时，该指令从磁盘中获取用户连接信息和相关权限信息，并保存在PGA内存里。当用户再次执行时，由于 SESSION之前未被断开重连。连接信息和相关权限信息就可以在PGA内存中直接获取，避免了物理读。

        首次执行该SQL结束后，SGA内存区的共享池里已经保存了该SQL惟一指令HASH值，并保留了语法语意检查检查及执行计划等相关解析 动作的劳动成果，当再次执行该SQL时，由于 该SQL指令的HASH值和共享池里保存的相匹配了，所以之前的硬解析动作就无须再做，不仅跳过了相关语法检查，对于该选取哪种执行计划也无须考虑，直接拿来主义就好了。

 

        Oracle的后台进程：

                PMON的含义为Processes Monitor，是进程监视器

                SOMON的含义为System Monitor，理解为系统监视器。

 

          sql更新语句的执行过程 ：

 

         在回滚空间的相应回滚段事务表上分配事务槽，从而在回滚表空间分配空间。该动作需要记录日志写进日志缓存区。

        在数据缓存区中创建object_id=29的前镜像，前镜像数据也会写进磁盘的数据文件里（回滚表空间的数据文件），这些动作都会记录日志，并将写进日志缓存区，LGWR也在忙着将日志缓存区的数据写入磁盘形成Redo文件。

        此时用户如果执行了提交，日志缓存区立即要记录这个提交信息，然后把回滚段事务标记为非激活INACTIVE状态，表示允许重写。

        如果执行了回滚，Oracle需要从回滚段中将前镜像object_id=29的数据读出来，修改数据缓存区，完成回滚。这个过程 依然要产生日志，要写数据进日志缓存区。

        （用于准备回滚的前镜像数据的生成和普通数据操作差不多，唯一的差别就在于一个是刷新到磁盘的普通文件里，一个是刷新到磁盘的回滚数据文件里）

        

        数据库的一致读：

 

        查询的结果由查询的那个时刻决定了，数据新的变化是不予理睬的。Oralce的回滚段不仅仅是保证了数据的回退，其实还提供了另外一个功能，保证数据的一致读 。

        一致读的原理：SCN(System Change Number)，是一个只会增加不会减少的递增数据，存在于Oracle的最小单位块里，当某块改变时SCN就会递增。

        数据库的回滚段记录事务槽，是用来分配回滚空间的，如果你更新了某块，事务就被写进事务槽里。如果未提交或者回滚，该块数据就存在活动事务，数据库读到此块可以识别到这种情况的存在。

        Oracle在做一致读时，首先是看发起的SCN是否大于当前被查询块的SCN，如果小于，毫无疑问从回滚事务段获取前镜像数据。如果SCN确实大于当前被查询块的SCN，还要确保该块没有活动事务，否则还是要去前镜像查找。

 

       内存参数相关操作

 

        （1）查看          show parameter sga      show parameter pga 

                                  show  parameter shared_pool_size    查看共享池

                                  show parameter db_cache_size         查看数据缓冲池大小

                                  show parameter log_buffer                  查看日志缓冲区大小 

 

        （2）修改数据库的内存参数

 

        alter system set <parameter_name>=<value> scope=memory|spfile|both [sid=<sid_name>]                                  memory：只改变当前实例运行，重新启动数据库后失效。

                 spfile：只改变spfile的设置，不改变当前实例运行，重新启动数据库后生效。

                 both：同时改变实例及spfile，当前更改立即生效，重新启动数据库仍然有效。

 

                需要注意的是：scop=XXX可以不写，默认为scope=both，此外log_buffer等参数必须重启才会生            效，因此alter system set log_buffer=15000000 scope=memory或者是scope=both就会报错，只支持

         alter system set log_buffer=15000000 scop=spfile，然后重启后生效。

 

        （3）查看Oracle归档进程 

 

                ps -ef| grep arc

                登录数据库以后输入 archive log list命令，Database log mode展示数据库的归档模式。更改数据            的归档模式比较麻烦，需要重启数据库，将数据库置于mount状态后，输入alter database                              archivelog（反之则为alter database noarchivelog），然后再开启数据库alter database open，才可            以将数据库更改为归档模式。

        （4）启停的体会

                参数文件及控制文件和数据库的启动与关闭是息息相关的，数据库的启动可以分为三个阶段，分别          是nomount、mount和open。startup nomount、startup mount和alter database open三步分别启动。

                startup nomount阶段，Oracle必须读取到数据库的参数文件(PFILE或者SPFILE），如果读不到该          参数文件，数据库根本无法nomount成功！如果读到参数文件，将完成一个非常重要的事，就是根据参          数文件上的内存分配策略分配相应的内存区域，并启动相应的后台进程，换而言之，就是创建了实例的          instance。 show parameter spfile 

 

                startup mount阶段，Oracle继续根据参数文件上描述的控制文件的名称及位置，去查找控制文件，          一旦查找到立即锁定该控制文件。控制文件里记录了数据库中数据文件、日志文件、检查点信息等非常          非常重要的信息，所以Oracle成功锁定控制文件，就为后续读取操作这些文件打下了基础，锁定控制文          件成功就表示数据库mount成功，为实例和数据库之间桥梁的搭建打下了基础。

                alter database open阶段，根据控制文件记录的信息，定位到数据库文件、日志文件等，从而正式          开通了实例和数据库之间的桥梁。

 

        （5）文件的体会

                 show parameter spfile;                 参数文件位置 

                 show parameter control               控制文件位置 

                 select file_name from dba_data_files;   数据文件位置

                 select group#,member from v$logfile;    日志文件位置 

                 show parameter recovery                      归档文件位置 

                 show parameter dump                           告警日志文件 

 

                 lsnrctl status，查看监听的状态命令

                 lsnrctl stop，关闭监听的命令

                 lsnrctl start，开启监听的命令

 

         3、体系学习让SQL性能提升千倍

 

        共享池中缓存下来的SQL语句以及HASH出来的唯一值，都可以在v$sql中对应的SQL_TEXT和SQL_ID字段中查询到，而解析的次数和执行的次数可以从PARSE_CALLS和EXECUTIONS字段中获取。

 

        （1）substitution variables（替换变量）

        use substitution variables to supplement the following: where conditions、order by clauses、column expressions、table names、entire select statements 

        select order_no from t_order where order_no=&order_no

 

        （2）绑定变量优化 批量提交 

 

二、逻辑体系

 

        数据库（DATABASE）由若干表空间（TABLESPACE）组成，表空间（TABLESPACE）由若干段（SEGMENT）组成，段（SEGMENT）由若干区（EXTENT）组成，区（EXTENT）又是由Oracle的最小单元块（BLOCK）组成。块、区、段、表空间。

 

        1、数据块（数据块头、表目录区、行目录、可用空间、行数据区域）

        数据块头（包含了此数据块的概要信息，例如 块地址 及此数据块所属的段（segment）的类型（比如到底是表还是索引）；表目录区，只要有一行数据插入到数据库块中，那该行数据所在的表的信息将被存储到这个区域；行目录，存放你插入的行的地址；可用空间区；行数据区域，存储具体的行的信息或者索引的信息，这部分占用了数据块绝大部分的空间。

 

        2、段、区

        EXTENT是Oracle数据库分配空间的最小单位，请注意分配这两个字眼。

        在Oracle数据库中，只要segment创建成功，数据库就一定为其分配了包含若干个数据块（data block）

 的初始数据扩展（initial extent）；接下来T表（也就是segment T）中开始插入数据，很快初始数据扩展中的数据块都装满了，而且又有新数据插入需要空间，此时Oracle会自动为这个段分配一个新增数据扩展（incremental extent），这个新增数据是一个段中己有有数据扩展之后分配的后续数据扩展，容量大于或等于之前的数据扩展。

 

        3、表空间（系统表空间、临时表空间、回滚表空间、数据表空间）

       

        dba_tablespaces描述了数据库中所有表空间的信息

 

        --查看block尺寸

        show parameter db_block_size       

        select block_size from dba_tablespaces where tablespace_name='SYSTEM'

 

       --查看不同表空间的相关信息

        select file_name,tablespace_name,autoextensible,bytes from dba_data_files

        select file_name,bytes,autoextensible from dba_temp_files

        select sum(bytes/1024/1024 from dba_free_space where  tablespace_name='';

        show parameter undo 

        select tablespace_name,sum(bytes)/1024/1024 from dba_temp_files

        select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_uses where username=''

       --切换所有用户到指定临时表空间

        alter database default temporary tablespace temp_ljb 

 

        临时表空间的应用

 

                Oracle可以为每个用户指定不同的临时表空间，每个临时表空间的数据文件都在磁盘的不同位置            上，这样可以有效地避免IO竞争。

 

                在Oracle 10以后推出的临时表空间组，可以做到为同一用户的不同SESSION设置不同的临时表空           间，这可以说在缓解IO竞争方面再次迈出了大大的一步。

                create temporary tablespace temp1 '    '  size 100M tablespace group temp_grp1;

                create temporary tablespace temp2 '    '  size 100M tablespace group temp_grp1;

 

                --把临时表空间移到TMP_GRP1组里

                alter tablespace temp_ljb tablespace group temp_grp1 

                --把ljb用户的默认临时表空间TEMP_LJB更改为临时表空间组temp_grp1 

                alter user LJB temporary tablesapce temp_grp1

                all_tables 

                select username,session_num,tablespace from v$sort_usage 

 

         4、PCTFREE与性能

 

        行迁移：当一个行上的更新操作（原来的数据存在且没有减少）导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。

 

        其实消除行迁移的一个简单方法，就是数据重建。

        发现表是否存在迁迁移的方法：

        @?/rdbms/admin/utlchain.sql     --建立表

        analyze table T_ORDER list chained rows into chained_rows

        select count(*) from chained_rows where table_name='T_ORDER'

 

三、表的设计成就英雄

    

        select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and                                                                                                                                           a.name='redo size'

       产生日志的大小：利用v$statname和v$mystat两个动态性能视图来跟踪当前SESSION操作产生的日志量，使用方法简单：首次先执行该脚本，查看日志大小，随即执行更新语句，再执行该脚本返回的日志大小，两者相减，就是此次产生日志的大小。

   

        1、delete与truncate

        delete删除并不能释放空间，虽然delete将很多块的记录删除了，但是空块依然保留，Oracle在查询时依然会去查询这此空块。而truncate是一种释放高水平位的动作，这些空块被回收，空间也就释放了。

        不过truncate显然不能代替delete，因为truncate是一种DDL操作而非DML操作，truncate后面是不能带条件的，truncate table t where...是不允许的。但是如果这些whete条件能形成有效的分区，Oracle是支持在分区表中做truncate分区的，命令大致为alter table t truncate partition '分区名'，如果whete条件就是分区条件，那等同于换角度实现了truncate table where ...的功能。

        2、全局临时表

        从数据安全性来看，对表记录的操作写日志是不可避免的，否则备份恢复就无从谈起了，只是现实中我们真的有一部分应用对表的某些操作是不需要恢复的，比如运算过程中临时处理的中间结果集，这时我们就可以考虑用全局临时表来实现。        

       全局临时表分为两种类型，一种是基于会话的全局临时表（commit preserve rows），一种是基于事务的全局临时表（on commit delete rows）。        

        create global temporary table T_TMP_session on commit preserve rows as                                                                                     select * from dba_objects  where 1=2;                    

        create global temporary table t_tmp_transaction on commit delete rows as                                                                                      select * from dba_objects   where 1=2;                                                                      无论插入更新还是删除，操作普通表产生的日志都比全局临时表要多。

 

四、索引天下妙不可言

 

        谁能深刻地理解和掌握索引的知识，谁就能在数据库相关工作中发挥巨大的作用。

 

五、宝典、规范让你少做事

         --查看具体SQL占用了temp表空间

         SELECT                        

          SQL.SQL_ID,   T.BLOCKS*16/1024/1024,    S.USERNAME,   S.SCHEMANAME,

         T.TABLESPACE,  T.SEGTYPE,     T.EXTENTS,    S.PROGRAM,

         S.OSUSER,    S.TERMINAL,   S.SID,      S.SERIAL#,SQL.SQL_TEXT          

        FROM V$SORT_USAGE T,V$SESSION S,V$SQL SQL   WHERE T.SESSION_ADDR=S.SADDR AND         T.SQLAD   DR=SQL.ADDRESS AND                T.SQLHASH=SQL.HASH_VALUE;