Oracle调优大宝剑，没时间解释了，快上车！

很多朋友在调优分析时，不知道如何定位问题，在这里我总结了以下调优分析的一些思路，能够帮你快速的定位问题！

1.10046和10053事件

最为常用的诊断事件是10046和10053事件。10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强。Oracle的10046事件，可以跟踪应用程序所执行的SQL语句，并且得到其解析次数、执行次数、CPU使用时间等信息。这对DBA来分析、定位数据库性能问题是非常有用的。

--10046 事件
1. 搜集10046 事件的日志信息:
SQL>connect username/password 
SQL>alter session set timed_statistics = true;
SQL>alter session set statistics_level=all;
SQL>alter session set max_dump_file_size = unlimited;
SQL>alter session set events '10046 trace name context forever, level 12';

2.记得关闭追踪哈
SQL>alter session set events '10046 trace name context off';

3.格式化10046 日志文件:
$tkprof <trace file> <output file> 
   
例如:
$cd /u01/oracle/app/admin/19.3.0.0/udump
$ls -ltr
$tkprof r1020_ora_99968.trc 9968.output

接下来搜集格式化和原始的10046 日志文件.你就可以分析了


--10053 事件
 搜集10053 事件的日志信息:
SQL>connect username/password
SQL>alter session set events '10053 trace name context forever, level 1';
SQL>alter session set events '10053 trace name context off';

2.真实的执行计划

Oracle执行计划和SQL快慢息息相关，对数据库调优有着关键作用，了解执行计划的过程，对SQL优化有很大的帮助。

运行sql语句，并搜集执行计划 :
SQL> set linesize 130
SQL> @?/rdbms/admin/utlxplan.sql
SQL> explain plan for <需要运行的SQL语句 >;
SQL>select * from table(dbms_xplan.display(null, null,'ALL')); 
或者
SQL><需要运行的SQL语句>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'last'));
或者
awr执行计划
select * from table(dbms_xplan.display_awr('&sql_id'));
--awr中记录的执行计划（多个）

3.SQL语句的绑定变量追踪

在 Oracle 数据库性能调优中，绑定变量（Bind Variables）是一种常见且有效的优化策略。它可以通过减少硬解析（Hard Parse）的频率，提高数据库的查询性能，同时降低系统资源的开销。

1. 使用sys用户连接到数据库:
SQL>set linesize 130
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(&SQL_ID,NULL,'ADVANCED'))

2. 在下面的信息中找到绑定变量的值:
Peeked Binds (identified by position):
--------------------------------------
1 - :V (VARCHAR2(30), CSID=873): 'jeames' 

4.sql_trace追踪

Oracle的sql_trace跟踪工具是一个强大的诊断工具，用于捕获和记录数据库活动。

1. 执行下面的pl/sql 存储过程启动跟踪:
SQL>execute 
dbms_system.set_sql_trace_in_session(<sid>, <serial#>, true);

2. 等一段时间之后终止跟踪:
SQL>execute 
dbms_system.set_sql_trace_in_session(<sid>, <serial#>, false);

5.跟踪dbms job

Oracle 定时任务有很多后台的任务，尤其在统计信息异常的时候，这些追踪很重要。

1. 查看存在的job
SQL>select job,what,last_date,next_date,failures from dba_jobs;

2.找到正在运行的job
SQL> select sid,job from dba_jobs_running;
SQL>select a.spid,b.program,b.sid,b.serial# from v$process a, v$session b
where a.addr=b.paddr and b.sid=sid#;==>Please change sid# to the value returned by above command

3. 跟踪job:
SQL>conn / as sysdba
SQL> oradebug setospid <spid#>
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever, level 12

6.异常等待事件追踪

在Oracle数据库中，等待事件是诊断性能问题的重要线索.

--找到阻塞的信息
set linesize 120
set pagesize 100
col inst format 9999
col sid format 99999
col sql format a60 wrapped newline
col request format 9999999
col OSUSER format a10 wrapped newline
col USERNAME format a10 wrapped
col machine format a20 wrapped
col program format a20 wrapped

break on id1,id2 skip 1

select
  a.inst_id "INST",a.sid,a.id1,a.id2,a.type,a.request,a.ctime "SECS",
  c.spid "SPID",
  b.OSUSER, b.USERNAME, b.PROCESS,b.MACHINE,b.program,
  case
  when rawtohex(b.SQL_ADDRESS) <> '00'
    then 'CURR'
    else 'PREV'
  end STAT,
  d.sql_text "SQL"
from
  gv$lock a, gv$session b, gv$process c,gv$sql d
where
  (a.id1,a.id2) in
     (
         select ID1,ID2 from gv$lock where type = 'TX' and request>0
     )
and a.inst_id = b.inst_id
and a.inst_id = c.inst_id
and a.sid = b.sid
and b.paddr = c.addr
and
  case
  when rawtohex(b.SQL_ADDRESS) <> '00'
    then b.SQL_ADDRESS
    else b.PREV_SQL_ADDR
  end
= d.address
and
  case
  when b.SQL_HASH_VALUE > 0
    then b.SQL_HASH_VALUE
    else b.PREV_HASH_VALUE
  end
  = d.hash_value
order by a.id1,a.id2,a.request;
--根据OSpid 找到进程正在运行的sql语句
SQL>select a.sql_text,b.machine,b.osuser,b.schemaname,c.pga_used_mem,d.cpu_time,d.disk_reads,d.runtime_mem,d.module,d.last_active_time,d.first_load_time,d.executions from v$sqltext a,v$session b,v$process c, v$sqlarea d
where a.address=b.sql_address and b.paddr=c.addr  and a.sql_id=d.sql_id and c.spid=<ospid>;