以11g来分析
数据库实例包括:SGA和一系列后台管理、监控简称
数据库包括三种文件：数据文件、控制文件、重做日志文件
数据库实例和数据库是Orale数据库体系的核心部分
Oracle服务器和实例
实例：由一些在内存区和后台进程组成
这些内存区包括 数据库高速缓存、重做日志缓存、共享池、流池及其他可选区域，如JAVA池，这些池称为数据库的内存结构
后台进程包括系统监控进程SMON，进程监控PMON，数据库写进程DBWR，日志写进程LGWR，检验点进程CKPT及其他进程(如归档进程，REco进程),这些数据库系统进程忠于职守、相互协作地完成数据库管理任务
要访问数据库，则必须先启动实例，启动实例，则先分配内存区，然后启动后台进程。
在数据库启动过程中，5个进程是必须启动的：SMON、PMON/DBWR、LGWR、CKPT，在告警日志中alterSID.ora可以看到数据库的启动过程
Oracle服务器
Oracle服务器是由实例和数据库文件组成。
Oracle除了维护实例和数据文件外，还在用户与服务器连接时启动服务器进程并分配PGA
Oracle数据库的物理结构(文件组成):
数据文件:数据文件宝内涵数据库中的实际数据，是数据库操作系统中数据的最终存储位置
控制文件:包含维护数据库和验证数据库完整性的信息，它是二进制文件
重做日志文件：重做日志文件包含数据库发生变化的记录，在发生故障时用于数据恢复
Oracle参数文件和密码文件
虽然不是Oracle的数据库文件，但是是不可缺少的两个文件
参数文件：定义了数据库实例特性，在参数文件中包含SGA内存结构分配空间的参数，如分配数据库高速缓冲区的大小等，参数文件是正文文件，可以使用编辑器查看。
密码文件：授予用户启动和关闭数据库实例，Oracle默认用户名和密码存储在文件中，可以根据这个判断用户的操作权限
数据库连接与会话
数据库连接
连接有3中方式
1 基于主机方式：服务器和客户端运行在同一台计算机上，用户可以直接连接数据库服务器
2 基于客户机：数据库服务器和客户端运行在不同计算机上，客户通过网络连接数据库服务器。经常使用此种方式
3 用户-应用服务器-数据库服务器：此种方法称为三成访问模式。用户先访问应用服务器，应用在访问数据库服务器，应用服务器类似中介，完成客户和数据库的交互
会话
一个明确的数据库连接，用户采用3种的任意一种方式连接，就称为一个会话
用户通过某种工具，在专用连接情况下访问数据，服务器就会自动创建一个与该用户进程对应的服务器进程，二者是一对一关系，用户退出或发生异常时，会话结束
专有连接，指用户和服务器进程之间是一对一关系，而共享服务器配置情况下，多个用户进程可以同时共享服务器进程，此时就不是专有服连接，而是多对一关系
一个用户可以并发发起多个会话
SQL> select serial#,username,status,process,program,logon_time from v$session where username='SYS'; SERIAL# USERNAME STATUS PROCESS PROGRAM LOGON_TIM ---------- ------------------------------ -------- ------------------------ ------------------------------------------------ --------- 24691 SYS ACTIVE 27448 sqlplus@gzxbi01 (TNS V1-V3) 18-MAR-17 28528 SYS INACTIVE 27443 sqlplus@gzxbi01 (TNS V1-V3) 18-MAR-17
可以看到，有两个会话，当前执行的指令的会话为ACTIVE,一个用户可以建立多个会话
一个连接，可以有多个会话（即服务器进程和数据库服务器见，可以建立多个会话）
SQL> select sid,serial#,paddr from v$session where username='SYS'; SID SERIAL# PADDR ---------- ---------- ---------------- 125 24691 00000000D1B5A9B8 357 28528 00000000D1B4B448 SQL> set autotrace on statistics; SQL> select sid,serial#,paddr,port from v$session where username='SYS'; SID SERIAL# PADDR PORT ---------- ---------- ---------------- ---------- 125 24691 00000000D1B5A9B8 0 132 14042 00000000D1B5A9B8 0 357 28528 00000000D1B4B448 0 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 850 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed
从PADDR和PORT可以看到是一个连接，但是有两个会话通过SID和SERIAL#来确认。
查询会话是否存在，可以
SQL> select pname,username from V$PROCESS WHERE ADDR=HEXTORAW('00000000D1B5A9B8'); PNAME USERNAME ----- --------------- oracle Statistics ---------------------------------------------------------- 36 recursive calls 0 db block gets 67 consistent gets 0 physical reads 0 redo size 614 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select pname,username from V$PROCESS WHERE ADDR=HEXTORAW('00000000D1B4B448'); PNAME USERNAME ----- --------------- oracle Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 614 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select pname,username from V$PROCESS WHERE ADDR=HEXTORAW('00000000D1B4B448'); -- 在另一个终端将这个会话退出后执行这条查询发现，连接不存在 no rows selected Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 411 bytes sent via SQL*Net to client 541 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
Oracle 数据库的内存结构
由SGA和PGA组成
PGA:程序全局区，程序全局区不是实例的一部分。当服务器启动时，才分配PGA
SGA:系统全局区，是数据库实例的一部分，当数据库实例启动时，首先分配系统全局区，在系统全局区包含几个重要的内存区(数据库高速缓存、重做日志缓存、共享池、大池、Java池等)
共享池Shard Pool
用用共享池的目的就是共享SQL和PL/SQL代码。即把解析到的SQL代码结果放到这里缓存，PL/SQL不仅在这里缓存，同时在这里共享。共享池由两部分组成
库高速缓存
存储的是可执行SQL语句和PL/SQL代码，包括 共享的SQL区和PL/SQL区，以及像锁和库缓存句柄这样的控制结果(一个SQL执行，在LC中有对应的执行计划，这个执行计划可以共享使用，称为软解析，反之硬解析，执行大量硬解析，会消耗大量的CPU资源)
LC容量有限，采用LRU算法管理LC，LC设置的越大，则可以共享更多的SQL或者PL/SQL代码，但并没有专门设置的指令，只能通过设置共享池SP大小间接改变。而SP又是SGA的部分，所以SP不能超过SGA大小
SQL> ALTER SYSTEM SET shared_pool_size = 16M;
System altered.
SQL> SHOW PARAMETER shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 16M
12c推荐内存自动管理方式。即设置MEMORY_TARGET和MEMEORY_MAX_TARGET,这个时候，SGA和PGA会自动调整。但在特殊情况下，需要手动设置PGA和SGA值，使得PGA和SGA不会低于设置的大小，这种情况是Oracle内存抖动，因为应用需要SGA和PGA频繁的调整，这往往会导致数据库短期HANG。此时需要注意，SGA和PGA的和不能接近MEMORY_MAX_TARGET.否则，一旦发生PGA需要扩充但是没有足够的内存空间则会触发ora_04301
注：为会话分配私有SQL区 共享服务器连接模式：私有SQL区在SGA 专有服务器连接模式:PGA 私有SQL中相同的SQL语句可以指向共享池中同一个SQL语句
Oracle清除共享池中共享的SQL语句，一面硬解析发生
1 收集统计数据后 2 数据库对象被DDL修改 3 修改全局数据库名
在同一个用户下，不同环境，相同的SQL各自发生硬解析，生成自由表，共享池会分配他们各自的空间
[oracle@gzxbi01 ~]$ sqlplus sys/testttt8 as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 22 14:04:41 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create table myt(id integer); Table created. SQL> insert into myt values(100); 1 row created. SQL> commit; Commit complete. SQL> alter system flush shared_pool; System altered. SQL> select * from myt; ID ---------- 100 SQL> alter session set optimizer_mode = first_rows; Session altered. SQL> select * from myt; ID ---------- 100 SQL> alter session set sql_trace=true; Session altered. SQL> select * from myt; ID ---------- 100
查询SQL语句和SQL_ID
SQL> col SQL_TEXT FOR A50 SQL> select sql_id,sql_text from v$sql where sql_text like 'select * from myt'; SQL_ID SQL_TEXT ------------- -------------------------------------------------- gff382536kduk select * from myt gff382536kduk select * from myt gff382536kduk select * from myt
可以看到，三条SQL语句中的SQL_ID是一样的，也就是Oracle进行SQL哈希计算得到的结果是一样的，但是环境发生变化，Oracle认为不共享该SQL执行计划。会产生游标
SQL> select sql_id,sql_text,address,child_address,child_number from v$sql where sql_text like 'select * from myt'; SQL_ID SQL_TEXT ADDRESS CHILD_ADDRESS CHILD_NUMBER ------------- -------------------------------------------------- ---------------- ---------------- ------------ gff382536kduk select * from myt 00000000C43F68F8 00000000C78C7E80 0 gff382536kduk select * from myt 00000000C43F68F8 00000000BF7E4FB8 1 gff382536kduk select * from myt 00000000C43F68F8 00000000C59C83D8 2
可以看到，SQL_ID是一样，但是从CHILD_ADDRESS中可以了解，生成了两个子游标，生成子游标会发生SQL重新解析，生成自己的执行计划，同样占用PGA内存空间，同时硬解析消耗资源
SQL> select sql_id,sql_text,address,parse_calls from v$sql where sql_text like 'select * from myt'; SQL_ID SQL_TEXT ADDRESS PARSE_CALLS ------------- -------------------------------------------------- ---------------- ----------- gff382536kduk select * from myt 00000000C43F68F8 1 gff382536kduk select * from myt 00000000C43F68F8 1 gff382536kduk select * from myt 00000000C43F68F8 1
可以看到，发生了硬解析，在环境未发生变化时
SQL> select * from myt; ID ---------- 100 SQL> select sql_id,sql_text,address,parse_calls from v$sql where sql_text like 'select * from myt'; SQL_ID SQL_TEXT ADDRESS PARSE_CALLS ------------- -------------------------------------------------- ---------------- ----------- gff382536kduk select * from myt 00000000C43F68F8 1 gff382536kduk select * from myt 00000000C43F68F8 1 gff382536kduk select * from myt 00000000C43F68F8 2
可以看到，最后一个环境花生变化的SQL语句，共享了执行计划，所以变成了2，进行了软解析
数据字典高速缓存
是与数据字典相关的一段缓冲区。在DDC中存储了数据文件、表、索引、列、用户、权限信息和其他一些数据库对象的定义
在SQL语句的解析阶段，数据库服务武器需要把这些信息来解析用户名和用户的访问权限。如果Oracle缓存额这些信息，将会缩短查询的响应时间
DDC也称为字典缓存或者行缓存。作用就是把相关的数据字典信息放入缓存，以提高查询的响应时间
DDC缓存的大小取决于SP尺寸的大小。如果设置太小，但查询需要数据字典信息时，Oracle将不断冯文数据字典表来获得所需要的信息。由于DD也是存储在磁盘上的一类数据文件，频繁的IO会降低
数据库的查询速度，如果要设置DDC的大小，需要通过设置SP间接实现
数据库高速换缓冲区 DataBase Buffer Cache DbBC
存储了最近从数据文件读入的数据库信息或用户更改后需要协会数据库的数据信息，此时没有提交给数据库更改后的数据称为脏数据，当用户执行查询语句，如果数据块在DbBC中，Oracle不需要从磁盘
中读取，而是直接从DbBC中读取，这些缓存的数据由LRU算法管理。
使用参数DB_BLOCK_SIZE和DB_BLOCK_BUFFERS设置高速缓存大小。DB_BLOCK_SIZE是Oracle数据块大小，而DB_BLOCK_BUFFERS是数据库的个数，二者的乘积就是库高速缓存的大小
SQL> show parameter db_block_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
9i以上的产品，提供了DB_CACHE_SIZE参数来设置Oracle数据库高速缓存区的大小。可动态更改
SQL> show parameter db_cache_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 0
如果是自动管理的，则为0，在运行12c，数据库高速缓存是一定已经分配好的。
SQL> sho sga; Total System Global Area 1912602624 bytes Fixed Size 2925792 bytes Variable Size 469764896 bytes Database Buffers 1426063360 bytes Redo Buffers 13848576 bytes
查看SGA内存动态大小
SQL> select * from v$sgainfo; NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 2925792 No 0 Redo Buffers 13848576 No 0 Buffer Cache Size 1426063360 Yes 0 In-Memory Area Size 0 No 0 Shared Pool Size 419430400 Yes 0 Large Pool Size 33554432 Yes 0 Java Pool Size 16777216 Yes 0 Streams Pool Size 0 Yes 0 Shared IO Pool Size 100663296 Yes 0 Data Transfer Cache Size 0 Yes 0 Granule Size 16777216 No 0 Maximum SGA Size 1912602624 No 0 Startup overhead in Shared Pool 164637416 No 0 Free SGA Memory Available 0 0 14 rows selected.
可以看到，数据库高速缓存是 1426063360B(1360M),虽然是自动挂你，但是用户可以设置组件的大小
SQL> alter system set db_cache_size = 700M; System altered. SQL> show parameter db_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 704M
Oracle引入了缓冲区顾问参数(Buffer Cache Advisory Parameter)，可以让Oracle对数据库缓冲区的内存分配提供一些建议，缓冲区顾问用于启动或关闭统计信息，这些信息用于预测不同缓冲区
大小导致的不同行为特性，DBA可以参考这些统计信息，基于当前数据库负载设置优化的数据库高速缓存
有三个状态
OFF：关闭，不分配缓存顾问的工作内存
ON：打开，分配工作内存
READY：打开，但不分配缓存顾问的工作内存
SQL> show parameter db_cache_advice; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_advice string ON
可以看到，默认是打开的
SQL> col id for 99 SQL> select id,name,block_size,size_factor,size_for_estimate from v$db_cache_advice; ID NAME BLOCK_SIZE SIZE_FACTOR SIZE_FOR_ESTIMATE --- -------------------- ---------- ----------- ----------------- 3 DEFAULT 8192 .0989 144 3 DEFAULT 8192 .1978 288 3 DEFAULT 8192 .2967 432 3 DEFAULT 8192 .3956 576 3 DEFAULT 8192 .4945 720 3 DEFAULT 8192 .5934 864 3 DEFAULT 8192 .6923 1008 3 DEFAULT 8192 .7912 1152 3 DEFAULT 8192 .8901 1296 3 DEFAULT 8192 .989 1440 3 DEFAULT 8192 1 1456 3 DEFAULT 8192 1.0879 1584 3 DEFAULT 8192 1.1868 1728 3 DEFAULT 8192 1.2857 1872 3 DEFAULT 8192 1.3846 2016 3 DEFAULT 8192 1.4835 2160 3 DEFAULT 8192 1.5824 2304 3 DEFAULT 8192 1.6813 2448 3 DEFAULT 8192 1.7802 2592 3 DEFAULT 8192 1.8791 2736 3 DEFAULT 8192 1.978 2880 21 rows selected.
SIZE_FACTOR为1为当前数据库高速缓存大小，在数据库高峰时期可以通过该视图提供的参考值设置DB_CACHE_SIZE大小。如果是自动管理系统运行的很好的，则没有必要进行手工
重做日志高速缓冲区 redo buffer cache
Redo Log Buffer记录DML和DDL操作，用来重建、重做数据库的变化，数据库进程将在用户内存中的Redo实体复制到Redo Log Buffer中，Redo实体在Redo Log Buffer中占用连续且顺序的内存
空间，后台LGWR负责将Redo Log Buffer中的Redo实体写入在线日志文件。
LGWR将Redo顺序写入在线日志，与DBWR进程的离散写相比速度要快很多，LGWR进程先写原则，使得数据库的变化得到保护，用户不需要等到DBWR进程将脏数据写入到磁盘即可继续操作
重做日志缓冲区大小可动态调节，即数据库运行期间可修改这块内存大小，Oracle提供了一个初始化参数LOG_BUFFER，在数据库启动时就分配好重做日志缓冲区的尺寸
SQL> show parameter log_buffer; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_buffer big integer 13016K
这是静态参数，不能直接用alter修改，静态修改的方法如下
