性能优化笔记

v$session
|
|
v$session_wait
|
|
v$session_wait_history
|
|
ash
|
|
v$active_session_history
|
|
awr
|
|
WRH$_active_session_history
|
|
dba_hist_active_sess_history

awrdd
|
|
awr-->addm-->awrsqrpt
|
|
ash 

@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrrpti.sql
@?/rdbms/admin/awrgrpti.sql

结论：
当我们收集统计量使用no_invalidate为false的时候，原有的共享游标被失效，下一次在执行SQL的时候，
Oracle会重新为其生成执行计划，也就是一次hard parse过程。

结论：当我们使用no_invalidate为true的时候，原有的shared cursor不会被失效，可以支持共享。
只有当被age out或者flush out出shared pool之后，新执行计划才能生成。

Oracle支持true、false和dbms_stats.auto_invalidate取值。如果取值为true，表示不进行游标失效动作，
原有的shared cursor保持原有状态。如果取值为false，表示将统计量对象相关的所有cursor全部失效。
如果设置为auto_invalidate，根据官方文档，Oracle自己决定shared cursor失效动作。

在分析ASH报告、AWR报告的时候，最重要的就是关注SQL Statistics，
SQL Statistics中最应该关注的是SQL ordered by Gets和SQL ordered by Reads两个指标。
大量的Gets(逻辑读)会占用大量的CPU时间。大量的Reads(物理读)会引起IO的瓶颈出现。
通过这两个指标找到了最影响性能的SQL，这是首要的，也是必要的。
下一步就可以通过创建索引，调整SQL来提高SQL单独执行时的性能。减少SQL执行时出现的高Gets,Reads。

当然整体的性能影响还和excutions有关，如果这条SQL执行的次数过多，累加起来量还是很大的。

SELECT 
    sql_id,
    sql_text,
    disk_reads,
    buffer_gets,
    executions,
    CASE 
        WHEN executions = 0 THEN 0 
        ELSE disk_reads / executions 
    END AS avg_disk_reads_per_execution
FROM 
    v$sql
WHERE 
    executions > 0 
ORDER BY 
    disk_reads DESC;    

如果是当前正在发生的问题，我们可以通过v$session,v$sqlarea来找出性能最差的SQL语句。
如果在一个小时以内发生的我们可以通过生成ASH报告来找出SQL。
如果是1小时以上或几天我们可以通过AWR报告来找出几小时，几天以来最影响系统的SQL语句。

在AWR 中定位到问题SQL 语句后想要了解该SQL statement 的具体执行计划，于
是就用AWR 报告中得到的SQL ID 去V$SQL 等几个动态性能视图中查询，但发现
V$SQL 或V$SQL_PLAN 视图都已经找不到对应SQL ID 的记录，一般来说这些语句
已经从shared pool 共享池中被替换出去了。

db file sequential read表示数据库需要按顺序读取单个数据块时发生的等待。在 Oracle 里，
数据以块为单位存储在数据文件中。当执行 SQL 语句时，若需要访问的数据块不在内存（SGA，System Global Area）中，
数据库就会从磁盘的数据文件里读取相应的数据块，
这种读取操作通常是按顺序进行的，每次读取一个数据块，此时就会触发db file sequential read等待事件。

SELECT 
    event, 
    total_waits, 
    total_timeouts, 
    time_waited, 
    average_wait
FROM v$system_event WHERE event = 'db file sequential read';

db file scattered read表示数据库在读取多个不连续的数据块时产生的等待。
在 Oracle 里，当执行全表扫描等操作时，数据库需要读取大量数据块，这些数据块在磁盘上通常是不连续存储的。
为了提高读取效率，Oracle 会以一种 “分散” 的方式同时读取多个不连续的数据块，此时就会触发db file scattered read等待事件。

buffer busy waits指的是在访问数据缓冲区（Database Buffer Cache）中的数据块时，该数据块正被其他进程使用，当前进程不得不等待。
数据缓冲区是系统全局区（SGA）的一部分，用于缓存从数据文件读取的数据块，以减少磁盘 I/O。
当多个会话同时尝试访问同一个数据块时，就可能会出现这种等待情况。

dbms_stats.GATHER_DICTIONARY_STATS：所有字典对象的统计信息

效率：
HJ>NL>SMJ  

HJ 适合于等值连接，适合处理大的结果集，小表做为驱动表，效率最高。主要消耗CPU。

SMJ 没有驱动表的概念，分别将两个表进行排序，然后在合并，通常在非等值情况下使用。主要消耗IO.

CLUSTERING_FACTOR和表的blocks的关系：越接近表的blocks越好，说明索引的使用效率很高，CLUSTERING_FACTOR 越大，
说明索引的使用效率越差，要考虑重建索引。

select INDEX_NAME,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR from dba_indexes where index_name='IND_TEST_OBJECT_ID'; 

select TABLE_NAME,NUM_ROWS,blocks,avg_row_len from dba_tables where table_name ='TEST1';

select * from dba_tab_statistics;
select * from dba_tab_col_statistics;
select * from dba_ind_statistics;

analyze table test1 delete statistics;

analyze table test1 compute statistics for table;

analyze table test1 compute statistics;

analyze table test1 compute statistics for columns object_id,object_name;

analyze table test1 compute statistics for all indexed columns;

analyze table test1 compute statistics for all indexes;

analyze table test1 compute statistics for table for all indexes for all columns;

analyze table test1 compute statistics for all columns;

analyze table test1 validate structure;
analyze table test1 validate structure cascade;
analyze table test1 validate structure online;

BEGIN
dbms_stats.delete_column_stats (ownname =>'LISO',tabname =>'TEST1',colname =>'object_name');
END;
/

直方图简单来说就是数据库了解表中某列的数据分布，从而更正确的走更优的执行计划

method_opt => 'for all columns size 1' 表示所有列都不收集直方图

method_opt => 'for all columns size repeat' 表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。

method_opt => 'for all columns size auto' 表示对出现在 where 条件中的列自动判断是否收集直方图。

在实际工作中，当系统趋于稳定之后，使用 REPEAT 方式收集直方图。

method_opt => 'for all columns size skewonly' 表示对表中所有列收集自动判断是否收集直方图。

备份和恢复统计信息：
BEGIN
 dbms_stats.create_stat_table('LISO','stattab1');
END;
/

BEGIN
dbms_stats.export_table_stats('LISO','TEST',stattab=>'stattab1');
END;
/

BEGIN
dbms_stats.export_schema_stats('LISO',stattab=>'stattab1');
END;
/

BEGIN
dbms_stats.export_database_stats(stattab=>'stattab1');
END;
/

select * from stattab1;

SELECT num_rows,blocks,avg_row_len FROM dba_tables WHERE table_name='TEST'; 

begin
dbms_stats.delete_table_stats(ownname=>'liso',tabname=>'test');
end;
/

begin
dbms_stats.delete_schema_stats(ownname=>'liso');
end;
/

begin
dbms_stats.delete_database_stats();
end;
/

begin
DBMS_STATS.UNLOCK_table_STATS('LISO','TEST');
end;
/

begin
 dbms_stats.import_table_stats('LISO','TEST',stattab=>'stattab1');
end;
/

BEGIN
dbms_stats.import_schema_stats('LISO',stattab =>'stattab1');
END;
/

BEGIN
dbms_stats.import_database_stats(stattab =>'stattab1');
END;
/

BEGIN
dbms_stats.export_database_stats(stattab=>'stattab1',statid =>'stattab1_0508');
END;
/

BEGIN
dbms_stats.export_system_stats(stattab=>'stattab1',statid =>'stattab1_system_0508');
END;
/

统计信息的回退：
SELECT * FROM dba_tables WHERE table_name='TEST1'; 

select * from dba_tab_stats_history where table_name ='TEST1';

BEGIN 
DBMS_STATS.RESTORE_TABLE_STATS(    ownname => 'LISO', 
                                   tabname => 'TEST1', 
                                   as_of_timestamp => '06-MAY-25 05.20.36.513000000 PM +08:00',
                                   force => FALSE, 
                                   no_invalidate => FALSE); 
END; 
/

SELECT * FROM dba_tables WHERE table_name='TEST1'; 

select dbms_stats.get_stats_history_availability from dual;

BEGIN
dbms_stats.alter_stats_history_retention(9);
END;
/

select dbms_stats.get_stats_history_retention from dual;

修改统计信息的publish功能：不及时被应用的数据库

select dbms_stats.get_prefs('PUBLISH') publish from dual;

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH', 'FALSE');
END;
/

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH', 'TRUE');
END;
/

BEGIN
DBMS_STATS.SET_SCHEMA_PREFS('LISO', 'PUBLISH', 'FALSE');
END;
/

BEGIN
DBMS_STATS.SET_SCHEMA_PREFS('LISO', 'PUBLISH', 'TRUE');
END;
/

BEGIN
DBMS_STATS.SET_TABLE_PREFS('LISO', 'TEST1', 'PUBLISH', 'FALSE');
END;
/

BEGIN
DBMS_STATS.SET_TABLE_PREFS('LISO', 'TEST1', 'PUBLISH', 'TRUE');
END;
/

发布统计信息：
select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='TEST1';

BEGIN
dbms_stats.publish_pending_stats('LISO', 'TEST1');
END;
/

begin
dbms_stats.delete_pending_stats('LISO', 'TEST1');
end;
/

比较统计信息的差异：
select report, maxdiffpct from dbms_stats.diff_table_stats_in_history('LISO', 'TEST1', SYSDATE-1, SYSDATE, 2);

STA的使用：
--查看系统自动优化任务的报告建议 
select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual; 
 
 
 
1、查等待事件：
select event,count(*) from v$session_wait group by event order by 2 desc;

2、通过等待事件来得到SQL_ID:
select sw.p1,s.sql_id,count(*),(ratio_to_report(count(*)) over()) * 100 pct 
from v$session s,v$session_wait sw 
where s.event like '%direct path read%' and s.sid=sw.sid 
group by sw.p1,s.sql_id 
order by count(*) desc;

3、通过SQL_ID 来得到SQL_TEXT:
select sql_id,sql_text from v$sql where sql_id='';

根据进程号获取TOP SQL:
select a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text 
from v$session a,v$process b,v$sqltext c 
where b.spid='&spid' and b.addr=a.paddr and a.sql_address=c.address(+) order by c.piece;

查找死锁并生成查杀语句： 
select 'alter system kill session '''|| sid || ',' || serial# || ''';' "Dealock" 
from v$session 
where sid in (select sid from v$lock where block =1);

 
 
如果DB HANG住了，如何解决：杀掉该用户的所有进程
 
select 'alter system kill session ''' || s.sid || ',' || s.serial# || '''; --kill -9 ' || p.spid 
from v$session s,v$process p where s.paddr =p.addr and s.username ='LISO';
 
 
 
 
SQL access ADVISE的使用方法： 
 
BEGIN
 DBMS_ADVISOR.quick_tune(
   advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
   task_name    =>'emp_quick_tune3',
   attr1        => 'select * from test1 where object_id=168');
END;
/

Select DBMS_ADVISOR.get_task_script('emp_quick_tune3') from dual;
 
 
iostats   控制I/O统计的显示
memstats  控制pga相关统计的显示
allstats  此为iostats memstats的快捷方式，即allstats包含了iostats和memstats 
last      默认，显示所有执行计算过的统计。如果指定该值，则只显示最后一次执行的统计信息

找到执行计划的中哪一步是瓶颈：
select count(*),sql_plan_line_id from v$active_session_history 
where sql_id='6pkd06hdx99xk' 
group by sql_plan_line_id 
order by 2;

create index ind_test1_object_name on test1(object_name);

analyze index ind_test1_object_name validate structure;

--如果大于20%就要重建
select name,(del_lf_rows_len/lf_rows_len)*100 from index_stats;  

alter index ind_test1_object_name rebuild online;

process --> session --> transaction 

绑定变量解决的是硬解析的问题，缓存游标解决的是软解析的问题。

当前数据库的并发数：
select count(*) from v$session where status='ACTIVE';

在OLAP中SGA和PGA都是50%的内存，各占一半。