查看執(zhí)行計(jì)劃的方法：

這種方式看到的執(zhí)行計(jì)劃不一定是真實(shí)的哮伟，現(xiàn)在很少用了：

explain plan for select * from tab;

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

這種會執(zhí)行SQL:

set autotrace on

select * from tab where rownum<1;

這種不會執(zhí)行SQL:

set autotrace traceonly exp

select * from tab where rownum<10;

這種是最真實(shí)的執(zhí)行計(jì)劃：

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：表示會話目前已經(jīng)執(zhí)行了多長時間,單位是秒

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'));

顯示上一個執(zhí)行的SQL的執(zhí)行計(jì)劃：

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已經(jīng)獲取了SQL_ID亲善，則可以直接得到執(zhí)行計(jì)劃：

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的歷史執(zhí)行計(jì)劃：

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，則通過下面的方法獲取執(zhí)行計(jì)劃：

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

v$active_session_history

dba_hist_active_sess_history

找到執(zhí)行計(jì)劃哪一步是瓶頸：

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;

找到執(zhí)行計(jì)劃哪一步是瓶頸棺滞，簡單版：

select count(*),sql_plan_line_id?

from? gv$active_session_history?

where sql_id='92b382ka0qgdt'?

group by sql_plan_line_id?

order by 2;

找到執(zhí)行計(jì)劃哪一步是瓶頸，簡單版：

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贸街、顯示執(zhí)行計(jì)劃

3、定位 plan_line_id

4狸相、針對性優(yōu)化

1薛匪、定位SQL

2、顯示執(zhí)行計(jì)劃

3脓鹃、尋找歷史執(zhí)行計(jì)劃

4逸尖、綁定歷史執(zhí)行計(jì)劃

如果索引有問題的話，就查詢索引的情況：

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個視圖的結(jié)構(gòu)基本一致：

v$sql

v$sqlstats

dba_hist_sqlstat 這個表中都是delta數(shù)據(jù)：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

查看統(tǒng)計(jì)信息：

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;

查看歷史的統(tǒng)計(jì)信息

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;

綁定執(zhí)行計(jì)劃：

dbms_sqltune.import_sql_profile?

coe_profile.sql

查詢表的數(shù)據(jù)的分布情況：

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;

查看表的每個列的數(shù)據(jù)分布情況：

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;

----查詢執(zhí)行時間最長的SQL

select *

from (select sa.SQL_TEXT,

sa.SQL_FULLTEXT,

sa.EXECUTIONS "執(zhí)行次數(shù)",

round(sa.ELAPSED_TIME / 1000000, 2) "總執(zhí)行時間",

round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均執(zhí)行時間",

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優(yōu)化一定要把范圍控制在最小范圍內(nèi)苞俘，控制在當(dāng)前SQL內(nèi)最好。

快速優(yōu)化SQL的方法：

1龄章、找到合適的歷史的執(zhí)行計(jì)劃吃谣，進(jìn)行綁定。

2做裙、找到執(zhí)行計(jì)劃的瓶頸岗憋，針對性優(yōu)化。

3菇用、使用合適的索引澜驮、連接順序、連接方法惋鸥。

性能問題的定位：原則就是盡可能小范圍分析問題

1杂穷、SQL層

如果能定位SQL就不要從會話層面分析

2、會話層

如果能從會話層定位就不要從系統(tǒng)層分析

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

3卦绣、系統(tǒng)層

如果無法定位任何性能問題耐量，從系統(tǒng)層面入手

AWR，TOP ,IOSTAT

分析一個孤立的AWR是沒有意義的滤港。要結(jié)合業(yè)務(wù)分析廊蜒，并且要對比正常時候的AWR來分析趴拧。

數(shù)據(jù)庫負(fù)載重并不一定有問題。

高效的SQL來自于對業(yè)務(wù)的理解和對SQL執(zhí)行過程的理解山叮。

CBO能夠做的事情非常少著榴。

AWR信息的來源表：

select table_name from dict where table_name like '%DBA_HIST_%';

AWR中DB_TIME:所有用戶操作數(shù)據(jù)庫的時間總和。比如有10個用戶每個用戶操作1分鐘屁倔，那么DB TIME 就是10分鐘脑又。

CURS/SESS :每個會話發(fā)出的SQL情況。

綁定變量在OLTP中使用锐借，在OLAP中沒有必要使用问麸。

OLTP關(guān)注的是內(nèi)存，OLAP關(guān)注的是I/O钞翔。

我們要關(guān)注的是前臺的等待事件:Foreground events

DB FILE SEQUENTIAL READ 的值比較大严卖，說明有大量的根據(jù)索引的查詢。

ASH報(bào)告間隔時間可以精確到分鐘布轿，因而ASH可以提供比AWR更詳細(xì)的關(guān)于歷史會話的信息哮笆，可以作為AWR的補(bǔ)充。

v$active_session_history? ? ? ----當(dāng)前會話的采樣數(shù)據(jù),1S鐘更新一下快照

dba_hist_active_sess_history? ----保留v$active_session_history再早的數(shù)據(jù)

如果是要會話層面很詳細(xì)的數(shù)據(jù)就要做ASH驮捍，如果你要整體的性能數(shù)據(jù)就要做AWR.

RAC的優(yōu)化設(shè)計(jì)：業(yè)務(wù)分割

優(yōu)點(diǎn)：避免數(shù)據(jù)在實(shí)例內(nèi)存間傳遞導(dǎo)致的性能下降疟呐。

缺點(diǎn)：數(shù)據(jù)無法使用全部節(jié)點(diǎn)資源。

RAC正面：多個實(shí)例處理數(shù)據(jù)东且，充分利用系統(tǒng)資源

RAC負(fù)面：大量的數(shù)據(jù)需要在實(shí)例的內(nèi)存間傳遞，影響性能

如果Interconnect導(dǎo)致嚴(yán)重的性能下降本讥，就考慮把并行開在一個實(shí)例上珊泳。

如果充分利用資源，能夠提高性能拷沸，那么就把并行開在不同的實(shí)例上色查。

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

性能優(yōu)化是一個和業(yè)務(wù)密切相關(guān)的過程，單純的數(shù)據(jù)庫層面優(yōu)化沒有前途撞芍。

只能從架構(gòu)上解決海量數(shù)據(jù)的存儲問題秧了。

HASH分區(qū)就是單純的把數(shù)據(jù)均勻的分布在各個分區(qū)，基本與業(yè)務(wù)無關(guān)序无，這種分區(qū)用的比較少验毡。

主要是范圍分區(qū)和列表分區(qū)。

對于分區(qū)表盡量不要建立全局索引帝嗡。做DDL操作就會使全局索引無效晶通。要建立本地分區(qū)索引LOCAL INDEX。一個分區(qū)對應(yīng)一個索引哟玷。

做更新時就不會使本地分區(qū)索引無效狮辽。

全局索引和分區(qū)索引的性能基本是一樣的。

分區(qū)索引的目的在于數(shù)據(jù)的管理而非性能。

一個分區(qū)表上如果經(jīng)常有DDL操作喉脖，將會導(dǎo)致全局索引失效椰苟，需要對索引重建，此時創(chuàng)建分區(qū)索引更加適合树叽。

ORACLE發(fā)明分區(qū)表只是解決數(shù)據(jù)管理上的考慮尊剔，而在性能上只是附加的考慮。

exec dbms_stats.gather_table_stats(user,'t',method_opt =>'for all columns size 254');? ? ----收集統(tǒng)計(jì)信息菱皆，并加上直方圖的信息

method_opt：主要是用來做直方圖的

size 254:說明建立254個桶须误，最多只能建立254個桶

for all columns：說明對所有的列進(jìn)行直方圖統(tǒng)計(jì)

for all hidden columns: 統(tǒng)計(jì)你看不到列的直方圖

for all indexed columns: 統(tǒng)計(jì)所有索引列的統(tǒng)計(jì)信息

exec?dbms_stats.gather_table_stats(user,'t',method_opt=>'for?all?columns?size?1');? ----對表t收集統(tǒng)計(jì)信息但不創(chuàng)建直方圖

size?1:表示不創(chuàng)建直方圖

user_tab_modifications 跟蹤表的修改。

當(dāng)表的數(shù)據(jù)修改超過10%仇轻，ORACLE會重新分析京痢。

定時任務(wù) GATHER_STATS_JOB 負(fù)責(zé)重新收集過舊的統(tǒng)計(jì)信息。

查看系統(tǒng)默認(rèn)的任務(wù)的執(zhí)行情況：

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);? ? -----刪除表的統(tǒng)計(jì)信息

開啟增量統(tǒng)計(jì)信息：

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

收集一次統(tǒng)計(jì)信息：

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

直方圖：ORACLE對列上的數(shù)據(jù)的分布進(jìn)行統(tǒng)計(jì)分析篷店，對數(shù)據(jù)傾斜分布時很有用祭椰。

數(shù)據(jù)分布不均勻就表示數(shù)據(jù)傾斜。

直方圖的類型：頻率直方圖疲陕，高度平衡直方圖方淤。

動態(tài)采樣：

LEVEL 10:對所有數(shù)據(jù)進(jìn)行采樣分析

LEVEL 1-10:采樣的數(shù)據(jù)量逐級遞增

創(chuàng)建擴(kuò)展統(tǒng)計(jì)信息:

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))');

在海量數(shù)據(jù)的表中執(zhí)行DELETE將是一個災(zāi)難。

在OLAP中執(zhí)行計(jì)劃多變蹄殃。在OLTP中執(zhí)行計(jì)劃基本不變携茂。

soft-sfot-parse? -----游標(biāo)通道沒有關(guān)閉，直接使用游標(biāo)通道诅岩，不需要重新打開游標(biāo)

游標(biāo)指向一個SQL.

一條SQL第一次運(yùn)行就是父游標(biāo)讳苦，第二次運(yùn)行就是子游標(biāo)。

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

SQL的文本一樣那么父游標(biāo)就一樣吩谦。

查詢執(zhí)行計(jì)劃最慢的步驟：

select count(*),sql_plan_line_id

from gv$active_session_history

where sql_id='2vcdzpaknk46s'

group by sql_plan_line_id

order by 2;