2023-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;

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,651评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,468评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,931评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,218评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,234评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,198评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,084评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,926评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,341评论 1赞 311
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,563评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,731评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,430评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,036评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,676评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,829评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,743评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,629评论 2赞 354

推荐阅读更多精彩内容

  • SQL优化笔记
    查看执行计划的方法： 这种方式看到的执行计划不一定是真的： explain plan for select * f...
    ben_782f阅读 233评论 0赞 0
  • 在ORACLE中如何查看执行计划（一）
    一般说来，ORACLE中SQL的优化，在很大程度上是对执行计划的调整，因而学会如何查看执行计划就显得尤为重要...
    wqh8384阅读 3,260评论 0赞 4
  • OB SQL优化
    优化器规则 优化器根据统计信息和代价模型（[RBO] 、[CBO]）为每个执行计划计算一个代价，代价是对执行计划的...
    hafe阅读 967评论 0赞 1
  • Oracle面试题之SQL tunting
    Oracle面试题之SQL tunting 1:列举几种表连接方式 答：一共有三种连接方式（SQL优化），嵌套循环...
    三少爷_贱阅读 1,353评论 0赞 7
  • 数据库系统学习笔记
    安装目录插件，阅读体验更佳！！！参考：https://www.jianshu.com/p/a40ec70d9365...
    ChaunhewieTian阅读 982评论 0赞 2