OracleServer总结进阶之系统分析（进阶完结）

 

个人原创，转载请在文章头部明显位置注明出处：https://www.cnblogs.com/sunshine5683/p/10080102.html

在上一篇进阶中大概讲解了一些关于进阶方面的知识，今天就将进阶内容做个总结！

一、oracleserver实例db的概念：

1、oracle关系数据库管理系统（rdms）

• 集成了开放的、全面的、集成的信息管理方法.
• 一个实例对应一个oracleserver，要查询oracleserver，直接查询后台进程就可以了
• 查看实例个数：

            ps -ef|grep ora_|cut -d '_' -f3|uniq

            ps -ef|grep ora_|cut -d '_' -f3|sort|uniq（有几条就几个oracleserver）

• 后台进程是在实例启动的时启动的，时将文件写到磁盘上的

        访问实例：v$instance，访问数据库: v$database

        语句：select instance_name from v$instance_name;

              select db_unique_name from v$database;

              show parameter service_name  (查看服务名)

    一个实例返回一个数据库 

注意：查看实例或数据库名时，保证数据库启动到mount（此阶段仅仅物理阶段可用）阶段或者open（此阶段打开物理机构和逻辑结构）阶段，在nomout阶段不可以查询

    注意：所有的12c跑应用程序都在pdb里面跑，而不再cdb里面跑

• 实例名跟库名不一定相同，这是允许的

二、如何连接到数据库

1、连接到服务器：

所有连接数据库都需要有相应的翻译工具或者相应的客户端工具，eg：java访问工具：jdbc等。

• 10g里面连接工具：ojdbc14.jar   classes12.jar   ojdbc14.jar
• 11g 里面的连接工具:ojdbc6.jar   ojdbc7.jar

    所以要访问数据库，只需要安装以上任意连接工具的驱动包就可以

2、oracle中的客户端工具：

• sqlplus：
• sqldevelop：
• toad：
• pl/sql develop:

    以上客户端实现的时候都是通过oracle net实现的

查看服务名：

也可以通过以下命令查看服务名

>show parameter dispathcer

3、windows上面访问数据库：

三、oracle db体系结构概览      

1、用户进程

用户进程要连接数据库，有本地连接，有远程连接，但都会在服务器端产生用户进程

• 连接到数据库实例：

        连接：用户进程和实例之间的通信

        会话：用户通过用户进程与实例建立的特定连接

• 远程连接：tcp三次握手
• 本地连接：数据库和客户端在同一台电脑：通过套接字连接

    本地远程连接数据库，然后进入服务端，desc v$session 就可以看到连接用户客户端信息

• 专有连接：一个用户进程对应一个服务进程，连接模式为none:共享连接
• 共享连接：一个服务进程对应多个用户进程， 连接模式dedicated:专有连接

2、服务器进程

         1>访问服务器进程在v$process视图中查看，服务器进程是接收用户进程请求并做相应的处理的。

         2>用户进程产生会话之后，该会话将保留在内存当中，每个用户进程都有自己的独立的pga区。

         3>查看访问数据库的操作系统进程id语句：

         select spid from v$process where addr in(select paddr from v$session where username=’scott’);

         4>看操作系统进程：

• ho ps –ef|grep 2036
• ho ps –ef|grep 4407

5>查看该进程消耗资源情况：top –p 4407

每一个视图都可以进程跟踪，使用desc v$process可以看到tracefile（跟踪文件）文件。

6>只要dba的用户，在oracle里面都可以使用：show parameter pga_a查看pga的内存大小，pga的值本身是自动管理的，但在11g中，可以通过权值修改其大小。

7>修改pga的大小：

• alter system set pga_aggregate_target=5g;
• show parameter pga

    8>对于耗资源的进程，可以杀掉（假如是4406进程）：kill -9 4406

四、sga

         在10g中可以用show parameter sga_target查看sga大小，在11g和12c中可能看到的大小

         查看sga的信息：select * from v$sgainfo;

1、共享池

    1>库高速缓存：用来接收pga传递过来的hashvalue，如果该值存在，做软分析，否则，做硬分析，定义语句是全标扫描还是索引扫描

    2>数据字典高速缓存：提供访问的对象是表还是索引还是同义词，表空间、数据文件等，进行递归调用

3>服务器进程用来读取数据文件到数据高速缓存区中，然后进行的读取

2、数据库高速缓冲区

有两种block，读的block和写的block，与块的读取有关

3、重做日志缓冲区

与块的更改有关，其大小需要手工设置。

五、后台进程

1、ckpt

• 只要在用户进程里面执行除了查询之外的的任何操作，都会触发ckpt，触发后，ckpt除了通知控制文件和数据文件外，还会通知dbwn将在数据库缓冲区高速缓存区执行过的ddl或者dcl的操作写到数据文件中去（注意：通知dbwn去写，但是dbwn未必就立马去写）。
• 当ckpt触发了，lgwr也写完了，大师dbwn还没写完，在下次启动时候，会自动将lgwr记录在联机重做日志文件中保存的undo内容写到数据文件中。
• sga里面的操作由smon(系统监视进程)和pmon(进程监视进程)协调。
• oracle中最重要的5个进程：smon ,pmon,dbwn,ckpt,lgwr。
• 显示相应的视图：desc v$process，查看后台进程：desc v$bgprocess视图中:
• 查看当前正在使用的后台进程：select paddr,name from v$bgprocess where paddr<>’00’；
• 查看数据文件：select name from v$datafile;
• 查看日志文件位置：select member from v$logfile;
• 查看控制文件位置：select name from v$controlfile;
• 参数文件：show parameter spfile

1>数据文件：表空间、表、索引都在数据文件中

六、sga的其他组件

1、数据高速缓冲区

 

2、重做日志缓冲区

 

3、大型池

 

4、java池和流池

 

5、程序全局区（pga）

 

6、系统全局区（sga）

 

7、数据库写进程（dwrn）

         将数据库缓冲区高速缓存区中经过修改的缓冲区写入磁盘：有两种写入方式：

         1>在执行其它处理时异步执行

         2>推进检查点

• 查看：show parameter db_wr;
• 数据库写进程是可以修改的:alter system set db_writer_processes=30; a scope=spfile; /(表示执行)

8、日志写进程（lgwr）

         1>将重做日志缓冲区中的内容写入磁盘上的重做日志文件中

         2>在以下情况下执行写操作：

• 用户进程提交事务处理时
• 重做日志缓冲区的三分之一已满时
• 在dbwn进程将经过修改的缓冲区写入磁盘之前
• 每隔3秒

9、检查点进程（ckpt）

         1>将检查点信息记录在以下位置

• 控制文件
• 每隔数据文件头

10、系统件事进程（smon）

         1>在实例启动时候执行恢复

         2>清除不使用的临时段

11、进程监器视进程（pmon）

         在用户进程失败时候执行进程恢复

1>     清楚数据库缓冲区高速缓存

2>     释放该用户进程使用的资源

3>     监视会话是否发生空闲会话超时

4>     将数据库服务动态注册到监听程序

12、恢复器进程：

1>     用于分布式数据库配置

2>     自动连接到其它那些与有问题的分布式处理有关的数据库

3>     自动解决所有有问题的事务处理

4>     删除对应于所有有问题的事务处理所有行

13、归档进程（arcn）

是一个可选进程。

1>     在发生日志切换之后，将重做日志文件复制到指定的存储设备

2>     可以手机事务处理重做数据，并将该数据传输到备用目标位置

14、告警日志

         1>查看告警日志的路径：show parameter background_dump_dest;

3>     读该告警日志：ho ls  /u01/oracle/admin/wyzc10g/bdump/alert_wyzc10g.log

在任何的版本都可以通过以上路径去访问，只是不同的版本可能路径不一样，如：在11g当中查看路径：show parameter backgr; 可以看到路径为：/u01/oracle/diag/rdbms/wyzc11g/wyzc11g/trace/alert_wyzc11g.log

同样可以读该日志：

ho ls / u01/oracle/diag/rdbms/wyzc11g/wyzc11g/trace/alert_wyzc11g.log

七、逻辑结构

1、逻辑和物理数据库结构：

• 方案：语句、表、索引、函数、过程、包等
• 数据库：一个数据库可以放32272个表空间
• 表空间：一个表空间可以放1023个数据文件
• 段；自动管理后，空间分配等都可自动管理，减少dba的工作量。一个表空间可以放多个段。
• 区：区里面存放的都是block，如果采用的是10g及其以后的版本，都采用的是本地管理，字典管理已经不使用了。
• oracle数据块：oracle最大的block只有32k
  • 数据文件：
  • 操作系统块：

2、物理结构：

3、system和sysaux表空间

1>     system和sysaux表空间是在创建数据库时创建的必须存在的表空间，这些表空间必须联机。

2>     system表空间用于核心功能（例如数据字典表）

3>     辅助的sysaux表空间用于附加的数据库组件（如oracle enterprise repository）

4>     数据块会映射到磁盘块，不建议使用system和sysaux表空间来存储应用程序的数据。

4、自动存储管理

1>     是可移植的高性能集群文件系统

2>     管理oracle db文件

3>     通过asm集群文件系统（acfs）管理应用程序文件

4>     将数据分配到各个次盘中以平衡负载

5>     建立数据镜像以防范故障

6>     解决存储管理挑战

5、asm存储组件

asm最终调的是硬盘

八、12c体系结构的不同之处

         oracle中的三大容器：cdb$root容器、pdbseed容器和pdbcontainer容器

 

九、dml语句：

注意：在写sql语句时候如：selet * from aaa ;此时发现报错’selet’,返回重新纠正就都的重写，此时只需要替换一下就可以，’c/selet/select’然后回车，就会将错误字符改正并正常执行。

1.buffer cache --undo block

  修改前的data block 数据进行镜像

2.undo 变化 所有redo记录undo变化的过程

3.buffer cache --data block 修改

4.data block 变化  redo记录变化过程

5.insert操作成功

insert执行时候1，2，3，4，5执行完成后，oracle数据要进行：提交 回退|不提交。

    不提交>此时undo数据属于修改后的状态，未提交，undo数据只能被当前的事务所在的会话调用，其他会话不能看到dml修改后的结果，看到的是上次提交的状态

    回退>此时使用修改前的undo镜像，覆盖修改的data block，这样就撤销了修改的信息，标记undo事务数据不需要

    提交>标记itl信息为新的scn，undo数据为提交状态，变为历史undo数据，写到磁盘上，将块数据标记为脏数据，该脏数据需要下次dbwr写到磁盘上，该数据也将成为其他会话看到的提交的数据

长时间不提交，dml事务达，很长时间没有结束，可能影像其他的会话执行所在行的dml

dml在对一张表进行操作的时候，会将该表锁定，到commit或着collback后释放

十、ddl语句：

    1、其中上面1、2看到ddl是否修改数据字典 ，若修改字典， 1，2执行，2、4都要执行

    2、ddl操作成功

十一、执行计划

1、explain plan 命令模拟sql执行跟踪

2、查询v$sql_plan视图

3、查询awr

4、sql*plus autotrace:统计信息

取当前的执行计划：在内存中已经存在一个sql情况：

1>  找到对应sql语句的游标id：select sql_id from v$sql where sql_text like 'select * from t1%';

2>  使用嵌套表查询当前语句的执行计划：

select * from table(dbms_explan.display_cursor('alln3qb4qvzrt')); 得到执行计划

    常用查看执行计划语句：1>ls /u01/oracle/11g/sqlplus/admin/

 

5、执行计划的缩进执行顺序：

    1、缩进不同，少的为父亲，多的为儿子。父子关系执行时候：先儿子后父亲

    2、缩进相同，上面的为哥哥，下面的为弟弟，执行时候：先哥哥后弟弟

    3、缩进是可以嵌套的：以父亲/祖父同级的为一个家族

 

6、sql跟踪工具：

    1>通常在会话级启用

    2>收集按照会话分组的sql语句的会话统计信息

    3>产生可通过tkprof格式化输出

    4>方法：对于当前会话：exec dbms_monitor(待完成)

   

7、sql 优化器：

    rule：表示使用rule优化器，现已被淘汰，用与10g   使用rbo

    choose：有统计信息的优化器，使用cbo不使用rbo

    10gr2 ：开始统计信息，可以动态采样，没有统计信息，也不使用rbo

    all_rows   first_rows   first_rows_n

    其中  rule基于规则，

    如果没有统计信息，将采用rbo

   

8、生成优化程序跟踪

    1>设置一个事件-10053优化程序跟踪

        建索引：

        create index emp_d_i on emp(deptno);

        create index emp_s_d_i on emp();

        create index emp_s_d_i on emp(sal,deptno);

    2>执行相关语句

       

十二、oracle中的函数—单行函数之字符函数

单行函数：不管输入几行，返回的只有一行。

1、character：字符函数

大小写：

• lower（string）:全部转换为小写
• upper(string)：全部转换为大写
• initcap（string）：将首字母转换为大写

字符处理函数：

• concat（string，string）：字符串连接函数，要连接三个字符串，需用嵌套，其嵌套为：concat（concat（stringvalue，stringvalue），stringvalue）
• substr（expr，m，n）：截取字符串expr，其中m表示从哪里开始，m可为负数，表示反向截取，n表示取多少个,省略n表示从第m个字符把后面所有的字符都截取出来。应用场景：如找到第二个字符是a的员工：select ename from emp where substr(ename,2,1)=’a’;
• length（）：该函数有四种形式，lengthc、lengthb、length2、length4
• instr（stringvalue,’a’）:返回字符在第一个字符串中的位置，若找到，返回位置，没找到，返回0，应用场景：找到第一个字符是a开头的员工，select ename from emp where instr(ename,’a’)=1。

2、gerneral：通用函数

3、numbers：数字函数

4、conversion：转化函数

5、datevalue日期函数

 

以上空着的部分将在后续总结中进行填补！