2025-02-17 ORACLE SQL 优化笔记

查看执行计划的方法：

这种方式看到的执行计划不必定是真实的，目前很少用了：

explain plan for select * from tab;

select * from table(dbms_xplan.display());

这种会执行SQL:

set autotrace on

select * from tab where rownum<1;

这种不会执行SQL:

set autotrace traceonly exp

select * from tab where rownum<10;

这种是最真实的执行计划：

alter session set sql_trace=true;

select * from tab where rownum<10;

alter session set sql_trace=false;

用 ls -lt | head 来查找生成的文件

用tkprof来解读文件

last_call_et：表明会话目前已经执行了多长时间,单位是秒

select sql_id,program,username,last_call_et from v$session where type != BACKGROUND and status= ACTIVE ;

select * from table(dbms_xplan.display_cursor( gvg0yjnt9dk6f ,null));

获取某个SQL的sql_id：

select sql_id from v$sql where sql_text like select count(*) from dba_objects ;

select * from table(dbms_xplan.display_cursor( fk7j3tnpandph ,null, ALLSTATS ));

显示上一个执行的SQL的执行计划：

select * from table(dbms_xplan.display_cursor(null,null, ALLSTATS ));

select * from table(dbms_xplan.display_cursor(null,null, ALLSTATS LAST ));

—-select sql_id from dba_hist_sqlstat where plan_hash_value !=0 and rownum<10;

如果通过AWR已经获取了SQL_ID，则可以直接得到执行计划：

select * from table(dbms_xplan.display_awr( sql_id ));

select * from table(dbms_xplan.display_cursor( sql_id ,null, ADVANCED ALLSTATS LAST PEEKED_BINDS ));

查看SQL的历史执行计划：

select * from table(dbms_xplan.display_awr( 6pkd06hdx99xk ));

select * from table(dbms_xplan.display_cursor( 6pkd06hdx99xk ,null, ADVANCED ALLSTATS LAST PEEKED_BINDS ));

获取SQL的一个绑定变量：

SELECT VALUE_STRING FROM v$sql_bind_capture WHERE sql_id = 6pkd06hdx99xk ;

SELECT snap_id,NAME,position,value_string,last_captured,WAS_CAPTURED FROM dba_hist_sqlbind WHERE sql_id = c1j018vt5ajdu ;

select sql_id,sql_plan_line_id,sql_plan_hash_value,count(*) 

from  v$active_session_history

where sql_id= &sqlid

group by sql_id,sql_plan_line_id,sql_plan_hash_value;

历史的会话信息：

select sql_id,sql_plan_line_id,sql_plan_hash_value,count(*) 

from  dba_hist_active_sess_history

where sql_id= &sqlid

group by sql_id,sql_plan_line_id,sql_plan_hash_value; 

select a.sql_id,a.sql_plan_line_id,a.sql_plan_hash_value,count(*) 

from dba_hist_active_sess_history a,dba_hist_sqlstat b 

where a.instance_number=b.instance_number and a.snap_id=b.snap_id

and a.dbid=b.dbid and a.sql_id=b.sql_id and a.sql_id= &sqlid  

group by a.sql_id,a.sql_plan_line_id,a.sql_plan_hash_value;

查看等待事情的情况：

select event,count(distinct session_id),count(*) from v$active_session_history where sql_id= 92b382ka0qgdt group by event;

如果知道SQL_ID，则通过下面的方法获取执行计划：

select * from table(dbms_xplan.display_cursor( &sql_id ,null, PEEKED_BINDS ));

v$active_session_history

dba_hist_active_sess_history

找到执行计划哪一步是瓶颈：

select  count(*),sql_plan_line_id,sql_plan_hash_value,sql_id 

from  gv$active_session_history 

where  sql_id= 92b382ka0qgdt  

group by sql_plan_line_id,sql_plan_hash_value,sql_id 

order by 2;

找到执行计划哪一步是瓶颈，简单版：

select count(*),sql_plan_line_id 

from  gv$active_session_history 

where sql_id= 92b382ka0qgdt  

group by sql_plan_line_id 

order by 2;

找到执行计划哪一步是瓶颈，简单版：

select count(*),sql_plan_line_id,sql_plan_hash_value 

from  gv$active_session_history 

where sql_id= 92b382ka0qgdt  

group by sql_plan_line_id,sql_plan_hash_value 

order by 2;

1、定位SQL

2、显示执行计划

3、定位 plan_line_id

4、针对性优化

1、定位SQL

2、显示执行计划

3、寻找历史执行计划

4、绑定历史执行计划

如果索引有问题的话，就查询索引的情况：

col index_name for a40

col collist for a80

select index_name,listagg(column_name, , ) within group(order by column_position) as collist 

from dba_ind_columns

where table_name = &tname

group by index_name

/

HIT:

/*+ NO_MERGE */    —-no_merge表明视图不合并，merge表明视图合并

/*+ leading(TMP) */

/*+ CARDINALITY(B,34343434343) */

/*+ gather_plan_statistics */

dbms_monitor.report_sql_monitor 

display_cursor(format=> IOSTATS )

下面3个视图的结构基本一致：

v$sql

v$sqlstats

dba_hist_sqlstat 这个表中都是delta数据：elapsed_time_delta，BUFFER_GETS_DELTA，EXECUTIONS_DELTA，ROWS_PROCESSED_DELTA

sql_id

plan_hash_value

elapsed_time

elapsed_time_delta

BUFFER_GETS

BUFFER_GETS_DELTA

EXECUTIONS

EXECUTIONS_DELTA

ROWS_PROCESSED

ROWS_PROCESSED_DELTA

查看统计信息：

select sql_id,plan_hash_value,

sum(elapsed_time) els,

sum(elapsed_time)/greatest(sum(executions),1) els_per_exec,

sum(buffer_gets) gets,

sum(buffer_gets)/greatest(sum(executions),1) get_per_exec,

sum(executions) execs,

sum(rows_processed) rowcnt,

sum(elapsed_time)/greatest(sum(rows_processed),1) els_per_row,

sum(buffer_gets)/greatest(sum(rows_processed),1) get_per_row

from  v$sqlstats

where

sql_id= &sqlid

group by sql_id,plan_hash_value 

order by els_per_exec;

查看历史的统计信息

select sql_id,plan_hash_value,

sum(elapsed_time_delta) els,

sum(elapsed_time_delta)/greatest(sum(executions_delta),1) els_per_exec,

sum(buffer_gets_delta) gets,

sum(buffer_gets_delta)/greatest(sum(executions_delta),1) get_per_exec,

sum(executions_delta) execs,

sum(rows_processed_delta) rowcnt,

sum(elapsed_time_delta)/greatest(sum(rows_processed_delta),1) els_per_row,

sum(buffer_gets_delta)/greatest(sum(rows_processed_delta),1) get_per_row

from dba_hist_sqlstat

where

sql_id= &sqlid

group by sql_id,plan_hash_value 

order by els_per_exec;

绑定执行计划：

dbms_sqltune.import_sql_profile 

coe_profile.sql

查询表的数据的分布情况：

select column_name,t.num_rows,c.NUM_NULLS,c.num_distinct 

from dba_tables t,dba_tab_columns c

where t.owner=c.owner and t.table_name=c.table_name and t.table_name= &tablename

order by num_distinct;

查看表的每个列的数据分布情况：

select column_name,t.num_rows,c.NUM_NULLS,c.num_distinct 

from dba_tables t,dba_tab_columns c

where t.owner=c.owner and t.table_name=c.table_name and t.table_name= FBP_BOE_TYPE

order by num_distinct;

—-查询执行时间最长的SQL

select *

from (select sa.SQL_TEXT,

sa.SQL_FULLTEXT,

sa.EXECUTIONS “执行次数”,

round(sa.ELAPSED_TIME / 1000000, 2) “总执行时间”,

round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) “平均执行时间”,

sa.COMMAND_TYPE,

sa.PARSING_USER_ID “用户ID”,

u.username “用户名”,

sa.HASH_VALUE

from v$sqlarea sa

left join all_users u

on sa.PARSING_USER_ID = u.user_id

where sa.EXECUTIONS > 0

order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)

where rownum <= 50;

select sid,serial#,username,schemaname,osuser,machine,terminal,program,owner,object_name,object_type,o.OBJECT_id

from dba_objects o,v$locked_object i,v$session s

where o.object_id=i.object_id and s.sid=i.session_id;

做SQL优化必定要把范围控制在最小范围内，控制在当前SQL内最好。

快速优化SQL的方法：

1、找到合适的历史的执行计划，进行绑定。

2、找到执行计划的瓶颈，针对性优化。

3、使用合适的索引、连接顺序、连接方法。

性能问题的定位：原则就是尽可能小范围分析问题

1、SQL层

如果能定位SQL就不要从会话层面分析

2、会话层

如果能从会话层定位就不要从系统层分析

V$SESSION,V$SESSTAT,V$SESSION_WAIT,V$SQL,V$LOCK,SQL_TRACE

3、系统层

如果无法定位任何性能问题，从系统层面入手

AWR，TOP ,IOSTAT

分析一个孤立的AWR是没有意义的。要结合业务分析，并且要对比正常时候的AWR来分析。

数据库负载重并不必定有问题。

高效的SQL来自于对业务的理解和对SQL执行过程的理解。

CBO能够做的事情超级少。

AWR信息的来源表：

select table_name from dict where table_name like %DBA_HIST_% ;

AWR中DB_TIME:所有用户操作数据库的时间总和。列如有10个用户每个用户操作1分钟，那么DB TIME 就是10分钟。

CURS/SESS :每个会话发出的SQL情况。

绑定变量在OLTP中使用，在OLAP中没有必要使用。

OLTP关注的是内存，OLAP关注的是I/O。

我们要关注的是前台的等待事件:Foreground events

DB FILE SEQUENTIAL READ 的值比较大，说明有大量的根据索引的查询。

ASH报告间隔时间可以准确到分钟，因而ASH可以提供比AWR更详细的关于历史会话的信息，可以作为AWR的补充。

v$active_session_history      —-当前会话的采样数据,1S钟更新一下快照

dba_hist_active_sess_history  —-保留v$active_session_history再早的数据

如果是要会话层面很详细的数据就要做ASH，如果你要整体的性能数据就要做AWR.

RAC的优化设计：业务分割

优点：避免数据在实例内存间传递导致的性能下降。

缺点：数据无法使用全部节点资源。

RAC正面：多个实例处理数据，充分利用系统资源

RAC负面：大量的数据需要在实例的内存间传递，影响性能

如果Interconnect导致严重的性能下降，就思考把并行开在一个实例上。

如果充分利用资源，能够提高性能，那么就把并行开在不同的实例上。

select name,value v$sysstat where name like %global cache% ;

性能优化是一个和业务密切相关的过程，单纯的数据库层面优化没有前途。

只能从架构上解决海量数据的存储问题。

HASH分区就是单纯的把数据均匀的分布在各个分区，基本与业务无关，这种分区用的比较少。

主要是范围分区和列表分区。

对于分区表尽量不要建立全局索引。做DDL操作就会使全局索引无效。要建立本地分区索引LOCAL INDEX。一个分区对应一个索引。

做更新时就不会使本地分区索引无效。

全局索引和分区索引的性能基本是一样的。

分区索引的目的在于数据的管理而非性能。

一个分区表上如果常常有DDL操作，将会导致全局索引失效，需要对索引重建，此时创建分区索引更加适合。

ORACLE发明分区表只是解决数据管理上的思考，而在性能上只是附加的思考。

exec dbms_stats.gather_table_stats(user, t ,method_opt => for all columns size 254 );    —-收集统计信息，并加上直方图的信息

method_opt：主要是用来做直方图的

size 254:说明建立254个桶，最多只能建立254个桶

for all columns：说明对所有的列进行直方图统计

for all hidden columns: 统计你看不到列的直方图

for all indexed columns: 统计所有索引列的统计信息

exec dbms_stats.gather_table_stats(user, t ,method_opt=> for all columns size 1 );  —-对表t收集统计信息但不创建直方图

size 1:表明不创建直方图

user_tab_modifications 跟踪表的修改。

当表的数据修改超过10%，ORACLE会重新分析。

定时任务 GATHER_STATS_JOB 负责重新收集过旧的统计信息。

查看系统默认的任务的执行情况：

select log_id,job_name,status,to_char(log_date, DD-MON-YYYY HH24:MI ) log_date 

from dba_scheduler_job_run_details where job_name= GATHER_STATS_JOB ;

查看表的修改情况：

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

select inserts,updates,deletes,timestamp from user_tab_modifications where table_name= T ;

exec dbms_stats.gather_table_stats(user, t ,CASCADE=>true);      —-将表和索引一起分析

exec dbms_stats.delete_table_stats(user, t ,CASCADE_parts=>false);    —–删除表的统计信息

开启增量统计信息：

exec dbms_stats.set_table_prefs( scott , cmp , INCREMENTAL , TRUE );

收集一次统计信息：

exec dbms_stats.gather_table_stats(ownname=> scott ,tabname=> cmp );

直方图：ORACLE对列上的数据的分布进行统计分析，对数据倾斜分布时很有用。

数据分布不均匀就表明数据倾斜。

直方图的类型：频率直方图，高度平衡直方图。

动态采样：

LEVEL 10:对所有数据进行采样分析

LEVEL 1-10:采样的数据量逐级递增

创建扩展统计信息:

exec dbms_stats.gather_table_stats(OWNNAME => DAVE ,TABNAME => DAVE ,method_opt => for columns(object_name,object_type) );

exec dbms_stats.gather_table_stats(OWNNAME => DAVE ,TABNAME => DAVE ,method_opt => for columns(UPPER(STATUS)) );

在海量数据的表中执行DELETE将是一个灾难。

在OLAP中执行计划多变。在OLTP中执行计划基本不变。

soft-sfot-parse  —–游标通道没有关闭，直接使用游标通道，不需要重新打开游标

游标指向一个SQL.

一条SQL第一次运行就是父游标，第二次运行就是子游标。

select sql_id,child_number,loads  from v$sql where sql_text= select * from emp where 1=0 ;

SQL的文本一样那么父游标就一样。

查询执行计划最慢的步骤：

select count(*),sql_plan_line_id

from gv$active_session_history

where sql_id= 2vcdzpaknk46s

group by sql_plan_line_id

order by 2;