监控Oracle11G R2数据库的操作，可以实现如下的目的：

Ø  了解数据库数据库的跟踪日志与警告日志的位置
Ø  启用会话跟踪，对相关数据进行查询与优化

使用跟踪日志与警告日志监控错误
    trace file:当进程检查到内部错误时，进程就产生错误信息到trace file中。
   每一个服务器进程与后台进程能够写到它相关联的trace 文件中，trace file可以用于优化应用程序与实例。Oracle 11G R2的跟踪文件就是以*.trc的文件存在对应的系统目录中
 
 
  Alert log：就是按时间先后顺序产生的信息与错误日志，包括如下信息：

Ø  所有的内部错误，包括块损坏与死锁错误
Ø  管理员的操作，如创建，修改，删除表达式，启动，关闭数据库等信息
Ø  关于共享服务器与分发器的功能信息与错误
Ø  当数据库与实例启动时，初始化参数为非默认值时，都要记录下这些非初始化参数

 
  Alert log 以xml形式的文件与txt文件的方式存在于oracle系统对应目录中
 
  周期性的检查实例的警告日志与跟踪文件，了解系统进程是否遇到问题。
 
 
控制trace 文件大小
  参数max_dump_file_size控制trace文件大小
 

SQL> show parametermax_dump
 
NAME                                 TYPE        VALUE
----------------------------------------------- ------------------------------
max_dump_file_size                   string      unlimited
SQL>

 
控制trace 文件
  当oracle数据库写trace文件时，有可能需要控制trace文件的数量与trace信息的类型 
  当服务器进程遇见严重错误时，跟踪文件记录服务器的错误行为。 设置初始化参数sql_trace,用来描述sql 跟踪设备为执行所有的sql表达式的语句所产生的跟踪信息，并把这些信息写入ADR信息中。
  使用select *from V$diag_info，得到如下表格信息
  注意：Diag trace与Default trace file，Diag trace 描述服务器进程与后台进程跟踪文件的目录，Default trace file 描述当前会话启用跟踪后，所产生的跟踪文件。



INST_ID


NAME


VALUE


1


Diag Enabled


TRUE


1


ADR Base


C:\APP\ADMINISTRATOR


1


ADR Home


C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb


1


Diag Trace


C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace


1


Diag Alert


C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\alert


1


Diag Incident


C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\incident


1


Diag Cdump


c:\app\Administrator\diag\rdbms\newdb\newdb\cdump


1


Health Monitor


C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\hm


1


Default Trace File


C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_3844.trc


1


Active Problem Count


0


1


Active Incident Count


0



 
为共享服务器会话阅读trace文件
  阅读trace文件使用的命令是trcsess与tkprof命令来阅读。
 
使用服务器产生的警告监控数据库操作
  服务器产生的警告信息是oracle数据库服务器端对即将发生的错误的一个通知，将对如下信息产生警告：

Ø  每秒的物理读
Ø  每秒的用户提交数
Ø  sql服务的响应时间

 
 
 
为服务器产生警告设置与取回阈值
 
    使用set_threshold与get_threshold过程为服务器警告值来查看与改变阈值的设定。
 
查看服务器产生的警告
  用oracle的企业管理器来查看服务器产生的警告信息
 
监控性能
监控死锁
监控等待事件
监控Oracle数据库的操作常用的视图
 

V$lock;
dba_lockers
dba_waiters
dba_ddl_locks
dba_dml_locks
dba_lock
v$session_wait

 
 
在oracle 11G中提供了跟踪文件，来优化应用程序与实例

1. oracle 11G R2中查看是否启用会话跟踪（即控制trace 文件）
SQL> show parametersql_trace;
 
NAME                                 TYPE        VALUE
----------------------------------------------- ------------------------------
sql_trace                           boolean     FALSE

 
2. 启用会话跟踪
  （注意：如果用户没有启用会话跟踪，在每一个会话中能够查询到default trace file，但实际位置并没有对应的物理文件。）
   利用sqlplus 语句，登陆数据库，默认没有启用会话跟踪。

sql> create tabale T  as select * from dba_objects
SQL> alter session setsql_trace=true;
 
Session altered.
 
SQL> alter session settracefile_identifier='testtrace02';
 
Session altered.
 
SQL> select * from t where object_id <50;

 
3. 查看跟踪文件的位置(oracle 11G r2中default trace file 就是新产生的trace file 文件位置）

SQL> col name format a30
SQL> col value form a65
SQL> select * fromv$diag_info;
 
   INST_ID NAME                           VALUE
---------------------------------------------------------------------------------------------------------
         1 Diag Enabled                   TRUE
         1 ADR Base                       C:\APP\ADMINISTRATOR
         1 ADR Home                       C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb
         1 Diag Trace                    C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace
         1 Diag Alert                    C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\alert
         1 Diag Incident                  C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\incident
         1 Diag Cdump                    c:\app\Administrator\diag\rdbms\newdb\newdb\cdump
         1 Health Monitor                C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\hm
         1 Default Trace File             C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_2632_ testtrace02.trc
 
 
   INST_ID NAME                           VALUE
---------------------------------------- -----------------------------------------------------------------
         1 Active Problem Count           0
         1 Active Incident Count          0
 
11 rows selected.
 
SQL>

4. 通过命令tkprof查看trace 文件内容
 

SQL> $tkprofC:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_2632_testtrace02.trcc:\app\02.txt
 
TKPROF: Release 11.2.0.3.0 -Development on Wed May 3 17:25:59 2017
 
Copyright (c) 1982, 2011,Oracle and/or its affiliates.  All rightsreserved.
 
 
 
SQL>

 
在文件02.txt 中部分内容摘选

***********************************************************************
select * from T whereobject_id <50
 
call     count      cpu    elapsed       disk     query    current        rows
------- ------  -------- ---------- ---------- --------------------  ----------
Parse        1     0.00       0.00          0          1          0           0
Execute      1     0.00       0.00          0          0          0           0
Fetch        5     0.00       0.00          0        181          0          48
------- ------  -------- ---------- ---------- --------------------  ----------
total        7     0.00       0.00          0        182          0          48
 
Misses in library cache duringparse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 12  
Number of plan statisticscaptured: 1
 
Rows (1st) Rows (avg) Rows(max)  Row Source Operation
---------- -------------------- ---------------------------------------------------
        48         48         48 TABLE ACCESS FULL T (cr=181 pr=0 pw=0 time=35 us cost=52 size=414 card=2)
 
*******************************************************************************

5. 在V$process 视图中查看后台进程的跟踪文件

SQL> col name format a15
SQL> col tracefile formata65
SQL> selectpid,pname,program,tracefile from v$process;
 
       PID PNAME PROGRAM                                                          TRACEFILE
---------- --------------------------------------------------------------------------------------------------------------------------------------
         1      PSEUDO                                                          C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_0.trc
         2 PMON ORACLE.EXE (PMON)                                               C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_pmon_1068
                                                                                  .trc
 
         3 PSP0 ORACLE.EXE (PSP0)                                               C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_psp0_1512
                                                                                  .trc
 
         4 VKTM ORACLE.EXE (VKTM)                                               C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_vktm_896.
                                                                                 trc
 
         5 GEN0  ORACLE.EXE (GEN0)                                               C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_gen0_1204

 
在新的版本中如何查看其他用户的信息
6. 使用系统管理员权限执行其他用户执行会话跟踪
 

1）查看用户会话
SQL> select sid,serial#,usernamefrom v$session;
 
       SID   SERIAL# USERNAME
---------- ----------------------------------------
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8         1
         9          1
        10          1
        11          1
        12          1
 
       SID   SERIAL# USERNAME
---------- ----------------------------------------
        13          1
        14          1
        15          1
        16         1
        20          3
        21        533 TESTDBA
        22         57 NEWDBA
        23         51
        24        463 NEWDBA
        28          3
        29         37
 
       SID   SERIAL# USERNAME
---------- ----------------------------------------
        31          1
        34        567
        35        381 NEWDBA
        37        129 SYS
        38        959 SYS
        40        677 SYS
        42        285 SYS
 
29 rows selected.
2）用操作系统权限用户执行其他用户的会话跟踪
C:\Users\Administrator>setoracle_sid=newdb
 
C:\Users\Administrator>sqlplus/ as sysdba
 
SQL*Plus: Release 11.2.0.3.0Production on Wed May 3 17:49:46 2017
 
Copyright (c) 1982, 2011,Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g EnterpriseEdition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP,Data Mining and Real Application Testing options
 
SQL>  exec
 dbms_system.set_sql_trace_in_session(21, 533,true);  # 启用
 
PL/SQL procedure successfullycompleted.
 
SQL> execdbms_system.set_sql_trace_in_session(21, 533, false);   #结束
 

 
在客户端执行select语句，在server端产生跟踪文件
 

SQL> select * from dba_users;

 
    实际测试，在没有启用会话跟踪时，通过select * from v$diag_info, 虽然default tracefile路径，但在服务器端并没有存在对应的trc文件，启用跟踪会话后，执行查询语句，服务器端产生对应的跟踪文件。
 
 
http://yedward.net/post/140.html
http://chenxy.blog.51cto.com/729966/752054
http://www.cnblogs.com/limengqiang/archive/2013/04/18/OracleTraceOne.html

mysql数据库优化
1、数据库优化的目的：
避免出现页面访问错误：
由于数据库连接timeout产生页面5xx错误
由于慢查询造成页面无法加载
由于阻塞造成数据无法提交
增加数据库的稳定性：
很多数据库问题都是由于低效率的查询引起的
优化用户体验：
流程页面的访问速度
良好的网站功能体验
2、可以从几方面进行数据库优化
3、SQL及索引优化
如何发现有问题的SQL？
使用MySQL慢查询日志对有效率问题的SQL进行监控
1、首先：show VARIABLES LIKE "slow_query_log";　　查看服务器是否开启慢查询日志，如果：slow_query_log为off，就代表没有开启慢查询日志。
2、在开启慢查询日志之前，要通过命令show VARIABLES LIKE "%log%";      查看是否没有记录的索引记录在慢查询日志中，如果log_queries_not_using_indexes为off表示没有开启，通过命令SET GLOBAL log_queries_not_using_indexes=on;可以设置开启
3、设置慢查询日志记录的时间，通过命令SHOW VARIABLES LIKE "long_query_time";   查询时间为大于10秒的查询记录在慢查询日志中。可以通过SET GLOBAL long_query_time=1;来修改时间。
4、最后，如果上面设置都设置好了，就可以SET GLOBAL slow_query_log=on;来开启慢查询日志的记录了。
5、通过命令：SHOW VARIABLES LIKE "slow%";  查看慢日志存放的位置，如图：
4、慢查日志的存储格式
SQL执行信息：
Query_time:查询执行时间　　　　lock_time:查询锁定时间 　　　　Rows_sent:查询发送的行数　　　　Rows_examined:查询扫描的行数
SQL执行时间：
SET timestamp：以时间戳形式的记录的执行时间。
5、SQL及索引优化
如何分析SQL查询(数据库基于MySQL5.5版本，不同MySQL版本的优化器有一定的差别)
6、慢查日志的分析工具
1、mysqldumpslow工具
2、pt-query-digest 工具
7、如何通过慢查日志发现有问题的SQL？
8、如何分析SQL查询
1、使用explan查询SQL的执行计划
1、explain返回各列的含义，如下：
首先：type中：
const：一般指常数查找，一般对于主键或者唯一索引查找。
eq_reg：一种范围查找，一般对于主键或者唯一索引范围查找。
ref ：常见于连接的查询中，一个表是基于索引的查找。
range：基于索引的范围查找
index ：对于索引的扫描，来进行的查找
ALL：对于表的扫描，来进行的查找
其次、extra列需要注意的返回值，如下：

数据库优化目的
避免出现页面访问错误
由于数据库连接超时产生页面5xx错误
慢查询造成页面无法加载
由于阻塞造成数据无法提交

增加数据库稳定性
低效查询造成

优化用户体验
流畅页面的访问速度
良好的网站功能体验

如何发现有问题的SQL
使用MySQL慢查日志对效率问题的SQL进行监控
show variables like 'slow_query_log' //查询 慢查询 信息
set global show_query_log_file = '/home/mysql/sql_log/mysql-sql-shwo.log'
set global log_queries_not_using_indexes = on //开启没有使用索引 查询;
set global long_query_time = 1; //将超过1秒查询语句进行存放到指定文件中
set global slow_query_log = on ; //开启慢查询日志


慢查询日志包含的内容
执行SQL的主机信息
#User@Host:root[root] @localhost []
SQL的执行信息
Query_time:0.000024 Lock_time:0.000000 Rows_sent:0 Rows_examined:0
SQL执行时间
SET timestamp=1402389235;
SQL的内容
select *from zzz;

如何分析SQL查询
explain
explain 查询语句
table：显示这一行的数据是关于那张表的
type：显示连接使用了何种类型。最好到最差（const、eq_reg、ref、range、index、ALL）
possible_key：显示可能应用在这张表中的索引，如果为空，表示没有可能的索引
key：实际使用的索引，为null则没有使用索引
key_len:使用索引的长度。不损失精确性情况下，越短越好
ref：显示索引的那一列被使用了。可能的话是一个常数
rows：MySQL认为必须检查的用来返回请求数据的行数
Using filesort：出现该字段后，查询则需要优化。MySQL需要进行额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储键值和匹配条件的全部行的行指针来排序全部行
Using temporary ：出现该字段，查询需要优化。这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上。

索引
create index index_name on tbl_name(字段);
如何选择索引？
在where从句，group by从句，order by从句，on从句中出现的列
索引字段在越小越好
离散度大的列放到联合索引的前面

删除重复索引
使用pt-duplicate-key-checker 工具检查重复以及冗余索引
pt-dublicate-key-checker \
-uroot \
-p "password"
-h 127.0.0.1


删除无用索引
在perconMySQL和mariaDB中可以通过INDEX_STATISCS表来查看那些索引未使用，但是在MySQL中只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析
pt-index-usage \
-uroot -p
mysql-slow.log


count() 函数优化
select count(expr or null ) from tbl_name;

子查询优化
通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否是一对多的关系，要注意重复数据。

limit查询优化
使用有索引的列或主键进行order by 操作。
记录上一次返回的主键，在下次查询时使用主键过滤

结构优化
使用可以存下你的数据最小的数据类型
使用简单的数据类型
尽可能使用not null定义字段
尽量少用text类型，非用不可时最好考虑分表
用int来存储时间戳[FROM_UNIXTIME()时间戳转换为yyyy-mm-dd UNIX_TIMESTAMP() yyyy-mm-dd转换为时间戳 ]
使用bigint来存储ip地址[INET_ATON()ip地址转整型 INET_NTOA()整型转ip地址]

操作系统配置优化
网络配置
修改/etc/sysctl.conf文件
增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535

减少断开连接时，资源回收
net.ipv4.tcp_max_tw_bukets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout =10

文件数限制
修改/etc/security/limits.conf文件，增加内容
soft nofile 65535

hard nofile 65535


MySQL服务器上应该关闭iptables，selinux等防火墙软件



MySQL配置文件
linux配置文件：etc/my.cnf``etc/mysql/my.cnf
查找配置文件命令：/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

Windows配置文件：C:/windows/my.ini
 #重要，缓冲池的大小 推荐总内存量的75%，越大越好。
     innodb_buffer_pool_size
 #默认只有一个缓冲池，如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；
     innodb_buffer_pool_instances
 #log缓冲的大小，一般最常1s就会刷新一次，故不用太大；
     innodb_log_buffer_size
 #重要，对io效率影响较大。0:1s刷新一次到磁盘；1：每次提交都会刷新到磁盘；2：每次提交刷新到缓冲区，1s刷新到磁盘；默认为1。
     innodb_flush_log_at_trx_commit
 #读写的io进程数量，默认为4
     innodb_read_io_threads
     innodb_write_io_threads
 #重要，控制每个表使用独立的表空间，默认为OFF，即所有表建立在一个共享的表空间中。
     innodb_file_per_table
 #mysql在什么情况下会刷新表的统计信息，一般为OFF。
     innodb_stats_on_metadata


转载于:https://www.cnblogs.com/ikai/p/7754245.html

数据库优化目的
避免出现页面访问错误
1、由于数据库链接timeout产生页面5xxx错我
2、由于慢查询造成页面无法加载
3、由于阻塞造成数据无法提交
如何发现有问题的sql
使用mysql慢查日志对有效率问题的sql进行监控
show variables like '%slow%';
set global log_queries_not_using_indexes=on;
代表 记录下没有使用索引的sql 后面进行优化 ,记录未使用索引的 打开
long_query_time 单位是秒 一般设置成100ms，也就是0.1秒
如果慢查日志时间短，会有大量的日志，磁盘空间可能会被沾满
第一款MySQL官方的mysqldumpslow
分析慢查工具
安装好mysql后自带的工具，mysqldumpslow -t 3 慢查日志路径 | more  查看
但是结果信息不是很全面
所以用pt-query-digest
--limit=A 参数  -review   expain 执行计划 如扫描行数很多，发送到客户端的很少，代表了索引不是很好
第一部分是头显示了基本信息，时间范围 sql数量  第二部表统计信息  第三部分 sql信息
举例说明 就是，这个sql 执行了一次，但是执行时间占用了百分之73百分比，这种sql重点关注，就是执行次数多，占用总时间的百分比大。数据库主要瓶颈在io这，这个主要关注的是扫描行数，如果扫描的行数越多，说明他的io消耗也会越大。
第一个关注执行次数和执行时间占比，第二关注扫描行数examine，说明他的io消耗也会越大，第三 通过扫描行数 和发送行数，如果examine的扫描行数，远远大于send发送行数，说明sql索引命中率不高
找到慢的语句后如何进行优化
首先是用explaim这个从句，可以显示出sql的执行计划，原理数据库中的sql，先对执行计划分析，再对sql进行具体执行，那执行计划侧面的反应了sql的执行效率，
table 是这一行的数据是关于哪张表的 。
type这一列 const性能最好 性能最差就是ALL
const说明是个常数查找，一般是主键呢唯一索引进行查找，
eq-reg 是一种范围的查找，一般呢是唯一索引呢主键的范围查找，
ref 比较常见于连接的查询中，一个表是基于某个索引的查找，
range是索引的范围查找，
index是索引的扫描，
ALL是表扫描
没有where条件所以没有用到索引
key-len 索引的长度越短越好，因为mysql中的查询化过程中，索引长度越小越好的，mysql每次读取都是以页为单位的，一页中如果存储的索引数量越大，它的查询效率也就会越高。
rows 实际上就是表扫描的行数，
扩展列 一个是
filesort查询结果用到文件排序方式来进行优化，文件排序呢主要是在orderby程序中比较常见，
temporary 这个呢用到了临时表，无论filesort 或是temporpary都使用了外部文件或是临时表进行数据的存储，这种sql一般出现在order by group by 从句中，这样的sql也是要重点关注的。以上是explain 从句使用方式还有返回的一些值，执行计划