Extract SQL Plan from AWR

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

这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:

SQL> set linesize 200 pagesize 2000;
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
askmaclean.com
SQL> select /* extract_me */ count(*) from maclean;
COUNT(*)
----------
9564
SQL> select sql_id from v$sql where sql_text like '%extract_me%' and sql_text not like '%like%';
SQL_ID
-------------
8vff23q8qp9fj
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
QL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8vff23q8qp9fj, child number 0
-------------------------------------
select /* extract_me */ count(*) from maclean
Plan hash value: 1679547536
----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
/* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
*/
SQL> alter system flush shared_pool;
System altered.
SQL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID: 8vff23q8qp9fj, child number: 0 cannot be found
/* 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划 */
SQL>  select * from table(dbms_xplan.display_awr('8vff23q8qp9fj'));
PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID 8vff23q8qp9fj
--------------------
select /* extract_me */ count(*) from maclean
Plan hash value: 1679547536
----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
/* 这里可以代入'all'选项获取更细致的计划信息 */
SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj',null,null,'all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 8vff23q8qp9fj
--------------------
select /* extract_me */ count(*) from maclean
Plan hash value: 1679547536
----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / MACLEAN@SEL$1
Note
-----
- dynamic sampling used for this statement
24 rows selected.
/* 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句 */
SQL> select OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where sql_id='8vff23q8qp9fj';
OPERATION                      OPTIONS                        OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT                           AGGREGATE
TABLE ACCESS                   FULL                           MACLEAN

Utilize Sql Tuning Advisor from Script

Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具,大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor;但如果系统中没有配置dbconsole或者Grid Control的话,我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本,具体脚本:

begin
DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid := '&sqlid';
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => '&task_name',
description => 'comment'                               
);
END;
/
BEGIN
dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
set pages 60
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;

具体使用示例:

SQL> begin
DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
Enter value for task_name: newtask
old   3:   DBMS_SQLTUNE.drop_tuning_task('&task_name');
new   3:   DBMS_SQLTUNE.drop_tuning_task('newtask');
begin
*
ERROR at line 1:
ORA-13605: The specified task or object newtask does not exist for the current
user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2307
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at "SYS.DBMS_SQLTUNE", line 751
ORA-06512: at line 3
SQL> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid := '&sqlid';
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => '&task_name',
description => 'comment'                               
);
END;
/ 
Enter value for sqlid: 17usubxchdf2w
old  11:   my_sqlid := '&sqlid';
new  11:   my_sqlid := '17usubxchdf2w';
Enter value for task_name: new_task 
old  19:						   task_name => '&task_name',
new  19:						   task_name => 'new_task',
SQL> BEGIN
dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/  
Enter value for task_name: new_task
old   3:   dbms_sqltune.execute_tuning_task(task_name => '&task_name');
new   3:   dbms_sqltune.execute_tuning_task(task_name => 'new_task');
PL/SQL procedure successfully completed.
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
Enter value for task_name: new_task
old   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name'
new   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'new_task'
STATUS
-----------
COMPLETED
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;
Enter value for task_name: new_task
old   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL
new   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('new_task') FROM DUAL
DBMS_SQLTUNE.REPORT_TUNING_TASK('NEW_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : new_task
Tuning Task Owner  : SYS
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at	   : 03/25/2011 00:14:41
Completed at	   : 03/25/2011 00:14:45
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID	   : 17usubxchdf2w
SQL Text   : select count(t1) from hashtab
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SYS"."HASHTAB" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'HASHTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 485915006
------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	   1 |	  13 |	   2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	     |	   1 |	  13 |		  |	     |
|   2 |   TABLE ACCESS FULL| HASHTAB |	 102 |	1326 |	   2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Gather more plan statistics by gather_plan_statistics hint

在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息,具体使用方法如下:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> set autotrace traceonly exp
SQL> select avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;
Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112
---------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS		      | 	    |	106 |  2438 |	  4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	107 |	749 |	  3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	  1 |	 16 |	  1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SQL> show parameter cursor_sharing
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT
SQL>  show parameter statistics_level
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
statistics_level		     string	 TYPICAL
SQL> set autotrace off;
SQL> select /*+ gather_plan_statistics */   avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;
SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  avg(SALARY),DEPARTMENT_NAME from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME
Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |    106 |00:00:00.01 |	  106 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
/* 可以从starts列看到某种操作执行了多少次,例如这里的INDEX UNIQUE SCAN为107次 */
/*也可以通过SQL_ID来定位计划信息 */
SQL> select t.* 
from v$sql s 
, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
Enter value for sql_id: bctzu9xuxay18 
old   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'
new   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where
e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME
Plan hash value: 3294250112
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |  2438 |     4	(0)| 00:00:01 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    16 |     1	(0)| 00:00:01 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |       |     0	(0)|	      |    106 |00:00:00.01 |	  106 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]
2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
4 - "DEPARTMENT_NAME"[VARCHAR2,30]
5 - "D".ROWID[ROWID,10]
SQL> alter session set statistics_level=ALL;
Session altered.
/* 在session级别设置statistics_level为ALL,可以为我们提供更为详尽的执行统计信息 */

Does GATHER_STATS_JOB gather all objects’ stats every time?

周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致SQL执行计划偏差的问题,这是一个10g的库并且禁用了自动收集统计信息的定时作业GATHER_STATS_JOB;当问及应用程序开发商为何要禁用自动统计信息收集时,开发商的一位工程师说因为该库的数据量较大,考虑到该JOB每天都会将所有大表统计一遍可能要花费大量时间所以予以停用。

这里就存在一个问题,GATHER_STATS_JOB自动统计作业是每次打开都会将数据库中所有的对象的统计信息都收集一遍吗?细心的朋友一定会发觉实际上该JOB的运行时间是时长时短的,同时绝对不是如这位开发工程师所说的会每天都重复统计所有表。

10g的官方文档中对该GATHER_STATS_JOB描述为”The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).”

以上这段描述还是比较清晰的,MAINTENANCE_WINDOW_GROUP维护窗口组中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)会在每个工作日的22:00启动并于第二天的6:00结束,在周末该维护窗口组中的周末窗口(WEEKEND_WINDOW)从周六Sat的0点开始并持续48小时(你不难发现这2个窗口在周六0点到6点之间存在overlay,实际的情况是WEEKEND_WINDOW窗口是从周六的0点整到周一的0点,具体可以观察dba_scheduler_windows视图的NEXT_START_DATE列,这里不再赘述)。在数据库一直打开的情况下,GATHER_STATS_JOB会伴随维护窗口一起被启动,默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性stop_on_window_close),剩下的有待收集信息的对象将在下一个维护窗口中得到处理;如果数据库一直处于关闭的状态,并在某维护窗口的时间范围内该DB被打开,那么相应的维护窗口会被立即激活(ACTIVE),同时GATHER_STATS_JOB自动作业也会被启动,但该自动作业仅会在一个窗口中自动运行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失败的不算做一次)。

以上介绍了GATHER_STATS_JOB的运行周期,和我们要介绍的问题没有直接的联系。我们这里要谈的是,GATHER_STATS_JOB自动统计信息收集作业每次启动时是由针对性地收集统计信息的而非对数据库中所有schema下的对象都分析一遍;以上引用的文字中介绍了该JOB挑选分析对象的条件,即:

  1. 对象之前从未收集过统计信息,或由于某些原因没有统计信息
  2. 对象的统计信息相对陈旧(stale),是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数超过10%

条件1显得理所当然,剔除一些复杂的情况,一个对象没有统计信息的原因往往是这个对象刚刚被创建或者加载到数据库中,并且用户没有手动地去收集过统计信息,那么Oracle有充分的理由去分析这些对象。而后者则体现了查询优化器对统计信息陈旧度的容忍在超过10%的情况下导致执行计划偏差的可能性将大幅上升,为了遏制这种势头有必要再次统计这些对象。

让我们来看看GATHER_STATS_JOB针对”陈旧”(stale)统计信息的实际表现:

SQL> select * from global_name;
GLOBAL_NAME
------------------------------------
www.askmaclean.com
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> conn maclean/maclean
Connected.
SQL> create table need_analyze tablespace users as select rownum t1 from dba_objects where rownum<10001;
Table created. 
SQL> select count(*)  from need_analyze;
COUNT(*)
----------
10000
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
/* 以上创建了一张具有10000行记录的测试用表,因为是新建的所以没有num_rows和blocks等等统计信息 */
/* 手动调用GATHER_STATS_JOB自动作业 */
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
10000	   20
/* 删除999条记录,即不到10%的数据 */
SQL> delete need_analyze where rownum<1000;
999 rows deleted. 
SQL> commit;
Commit complete.
/* 再次调用GATHER_STATS_JOB */
begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
/* 可以看到统计信息并未被更新 */
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
10000	   20
SQL> delete need_analyze where rownum<2;
1 row deleted. 
SQL> commit;
Commit complete.
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
10000	   20
SQL>  delete need_analyze where rownum<2;
1 row deleted. 
SQL> commit;
Commit complete.
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
8999	   20
/* 可以看到修改的行数必须超过10%后才会被收集 */

有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作,而DML操作又涉及到了多少行数据呢?这都是通过表监控特性(a table monitoring facility)来实现的,当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断,并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:

SQL> delete need_analyze;
8999 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from user_tab_modifications where table_name='NEED_ANALYZE';
no rows selected
/* 从实际的DML操作完成到*_tab_modifications视图到更新可能存在几分钟的延迟 */
/* 通过dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存储过程可以
将这些监控数据刷新到字典中  */
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed
SQL> col table_name for a20
SQL> select table_name,inserts,updates,deletes,timestamp from user_tab_modifications where table_name='NEED_ANALYZE';
TABLE_NAME		INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
NEED_ANALYZE		      0 	 0	 8999 26-MAR-11
/* 可以看到*_tab_modifications视图中记录了上次收集统计信息以来
NEED_ANALYZE表上删除过8999条记录,因为测试时仅用少量的串行DML,所以这里十分精确 */
SQL> set autotrace on;

/* 通过以上执行计划可以猜测,monitoring监控数据来源于MON_MODS_ALL$基表上 */
SQL> desc sys.MON_MODS_ALL$;
Name					   Null?    Type
----------------------------------------- -------- ----------------------------
OBJ#						    NUMBER
INSERTS					    NUMBER
UPDATES					    NUMBER
DELETES					    NUMBER
TIMESTAMP					    DATE
FLAGS						    NUMBER
DROP_SEGMENTS					    NUMBER
SQL> select * from mon_mods_all$ where obj#=(select object_id from dba_objects where object_name='NEED_ANALYZE');
OBJ#    INSERTS	 UPDATES    DELETES TIMESTAMP	   FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ---------- -------------
52565	    0	       0       8999 26-MAR-11	       0	     0
/* 需要注意的该mon_mods_all$修改监控基表仅记录上次该对象统计信息以来的修改(modify)情况,
并不能做为某张表的实际修改历史记录来利用 */

虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了,但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗?这似乎有悖于我们的常识,试看下例子:

/ * NEED_ANALYZE现在扮演一张静态表,它上次被分析是在2011年3月26日 */
SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';
LAST_ANAL
---------
26-MAR-11
SQL> select sysdate from dual;
SYSDATE
---------
26-MAR-11
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[maclean@rh8 ~]$ su - root
Password: 
/* 我们把时钟调快到2012年的12月30日,希望我们能安全度过2012! */
[root@rh8 ~]# date -s "2012-12-30 00:00:00"
Sun Dec 30 00:00:00 CST 2012
[root@rh8 ~]# date
Sun Dec 30 00:00:01 CST 2012
[maclean@rh8 ~]$ exit
exit
SQL> startup;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size		    1218292 bytes
Variable Size		   75499788 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    7168000 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;
SYSDATE
---------
30-DEC-12
/* 再次手动调用GATHER_STATS_JOB自动作业 */
SQL> set timing on;
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
2    3    4
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.33
/* :-),运行结束不到1s */
SQL>  select last_analyzed from dba_tables where table_name='NEED_ANALYZE';
LAST_ANAL
---------
26-MAR-11

是的,默认情况下GATHER_STATS_JOB不会反复去分析那些静态表,无论过去”多久”。

好了,我们需要对GATHER_STATS_JOB和DBMS_STATS包下属的统计信息收集存储过程(gather_*_stats)有一个饱满的认识,他们远没有我们想象的那么2,实际上这个GATHER_STATS_JOB调用的PROGRAM存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC,虽然这是一个内部存储过程(interal procedure);但实际上它和我们经常手动调用的DBMS_STATS.GATHER_DATABASE_STATS收集整库统计信息的存储过程在以GATHER AUTO选项运行时的行为方式上几乎一致,主要的区别是GATHER_DATABASE_STATS_JOB_PROC总是优先收集那些急需收集统计信息的对象,这保证了在维护窗口关闭之前那些最需要收集的统计信息总是能得到满足。而在手动调用GATHER_DATABASE_STATS等存储过程时因为不需要考虑窗口时间线的限制,而不论优先级。

to be continued ………….

Oracle中可以nologging执行的操作

redo重做日志是Oracle数据库恢复(recovery)的基础;但在很多情况下可以通过禁用重做日志的产生来加速SQL语句的完成,也就是我们所说的可nologging化的操作,这些操作大多是或串行的或并行的数据载入。

那么哪些操作是允许被nologging化的呢?首先需要注意的是普通的DML操作,即:常规insert,update,和delete(以及merge)总是不能被nologging执行的。但以下SQL语句则可以以nologging选项执行:

  • direct load (SQL*Loader)
  • direct load INSERT (using APPEND hint)
  • CREATE TABLE … AS SELECT
  • CREATE INDEX
  • ALTER TABLE … MOVE PARTITION
  • ALTER TABLE … SPLIT PARTITION
  • ALTER INDEX … SPLIT PARTITION
  • ALTER INDEX … REBUILD
  • ALTER INDEX … REBUILD PARTITION
  • INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

以上列出的语句,其产生undo和redo重做日志几乎可以完全禁绝。因为都是数据载入语句(或者装载索引)其所产生的新的区间(new extent)在执行过程中被标记为无效的(invalid),同时仅有少量的由数据字典变更引起的重做日志会产生。

应用长短链接变更对于Oracle数据库性能的影响

Question:某客户的应用做过变更(短链变长链),现cpu利用率较之前有明显改善,参见附件中的awr报告。想咨询一下sql语句的执行时间,cpu Time等指标,是否会受到短链变长链影响,因为从awr报告看,性能有明显改善。

Load Profile
变更前:

Per Second Per Transaction
Redo size: 244,606.59 13,269.94
Logical reads: 5,964.59 323.58
Block changes: 1,278.41 69.35
Physical reads: 339.03 18.39
Physical writes: 35.30 1.92
User calls: 693.44 37.62
Parses: 241.46 13.10
Hard parses: 0.16 0.01
Sorts: 97.93 5.31
Logons: 16.05 0.87
Executes: 617.55 33.50
Transactions: 18.43

变更后:

Per Second Per Transaction
Redo size: 314,037.68 4,249.08
Logical reads: 7,939.19 107.42
Block changes: 1,629.35 22.05
Physical reads: 221.23 2.99
Physical writes: 41.85 0.57
User calls: 1,005.17 13.60
Parses: 76.15 1.03
Hard parses: 0.16 0.00
Sorts: 37.36 0.51
Logons: 0.36 0.00
Executes: 810.16 10.96
Transactions: 73.91

Top 5 Timed Events
变更前:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 2,430 68.6
db file sequential read 84,286 416 5 11.7 User I/O
log file sync 63,773 266 4 7.5 Commit
db file scattered read 74,972 235 3 6.6 User I/O
enq: TX – row lock contention 463 229 494 6.5 Application

变更后:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 1,661 74.0
log file sync 167,658 473 3 21.1 Commit
db file sequential read 91,101 411 5 18.3 User I/O
wait for scn ack 145,796 142 1 6.3 Other
log file parallel write 166,143 121 1 5.4 System I/O

Time Model Statistics

变更前:

Statistic Name Time (s) % of DB Time
sql execute elapsed time 2,603.73 73.47
DB CPU 2,430.37 68.58
connection management call elapsed time 511.90 14.45
parse time elapsed 163.60 4.62
PL/SQL execution elapsed time 84.88 2.40
hard parse elapsed time 27.08 0.76
sequence load elapsed time 17.88 0.50
hard parse (sharing criteria) elapsed time 0.01 0.00
repeated bind elapsed time 0.00 0.00
DB time 3,543.74
background elapsed time 513.68
background cpu time 351.72

变更后:

Statistic Name Time (s) % of DB Time
DB CPU 1,661.42 74.02
sql execute elapsed time 1,558.64 69.44
PL/SQL execution elapsed time 66.66 2.97
parse time elapsed 37.24 1.66
hard parse elapsed time 15.09 0.67
connection management call elapsed time 8.37 0.37
sequence load elapsed time 3.53 0.16
PL/SQL compilation elapsed time 0.49 0.02
hard parse (sharing criteria) elapsed time 0.08 0.00
failed parse elapsed time 0.08 0.00
repeated bind elapsed time 0.00 0.00
DB time 2,244.66
background elapsed time 669.28
background cpu time 382.82

性能分析:

从这2个awr报告对比来看修改为长连接后单位小时的CPU TIME与DB TIME均有所下降,CPU TIME从原来的2430s下降到1661s,降幅为769s。但分析2个报告中的每秒逻辑读可以发现修改为长连接后的逻辑读反而增加了。CPU TIME主要可以分为parse cpu,execute cpu和fetch cpu。短连接时一小时的parse time即解析时间为163s;另外因为短连接时每秒登录数达到16个,Oracle为建立连接(connection management call)耗时511s。

总结以下几点:
1.短连接情况下因为新建立的会话没有缓存游标信息,进而导致无法避免大量的软解析,解析消耗了163s的DB TIME。修改为长连接后解析数量明显减少,解析仅消耗37s的DB TIME。
2.短连接情况下每秒登录数达到16次,建立连接(connection)同样会消耗大量的CPU TIME,这里connection management call消耗了约500s的CPU TIME。修改为长连接后每秒Logons数为0.36,节约了大量无谓的CPU浪费。
3.改为长连接后Top SQL的平均每次逻辑读并未下降,部分Top SQL的执行次数还有所增加;可见通过减少不必要的解析和反复建立连接,系统的性能得到了释放
4.原短连接的AWR报告中显示该时段内出现行锁等待(row lock contention)共463次,总耗时为229s,该等待事件也是造成2个报告中DB TIME差异的一个因素。而长连接报告中则没有该等待出现,这可能是出于偶然,也可能是程序修改导致的。

Oracle中可被并行化执行的SQL操作

并不是所有的SQL operations都是可并行化的;不少人认为sort merge join这种排序合并操作是不能并行化的,这显示是一种错误的认识。有了这样一个列表你就可以更好地理解Oracle中的Parallel Execution了:

Parallel Query:
Table scan
Nested loop join
Sort merge join
NOT IN
GROUP BY
Hash join
SELECT DISTINCT
UNION and UNION ALL
Aggregation
PL/SQL functions called from SQL
ORDER BY
DDL:
CREATE TABLE AS SELECT
CREATE INDEX
Rebuild index
Move partition
Split partition
DML:
UPDATE   on partitioned table
DELETE   on partitioned table
MERGE    on partitioned table
INSERT … SELECT
Enable constraint (the table scan is parallelized)
Star transformation

Tom Kyte教你如何制造糟糕的Oracle数据库

这个文档来的有点迟,不过迟到总比不到要好的多。我们可爱的Tom大叔这次很有爱的教导我们如何制造大量性能、管理具糟糕的,无人愿意管理的Oracle数据库。应当说做到这一点是十分不容易的,你需要从不考虑绑定变量,不考虑遵循任何范式同时往一个表里塞上两三百个列(当然有人真的这样做,而且认为这样做没什么不妥),你必须不了解Oracle的任何特性,如果你了解你必然对这些特性嗤之以鼻:”我才不会到那些东西,我用自己的代码来完成”,是的你很满足于一次次地重新制造轮子给自己带来的幸福感,等等。想要制造一个彻头彻底、糟糕到无与伦比的Oracle数据库的你还需要加一把劲,这当然也需要文档的指导,Tom这篇就是为这个来的,读罢这篇Worst Practices你或许还不足以制造世界上最糟糕的Oracle数据库,这不要紧!因为中国有大量的开发糟糕Oracle应用(或者其他什么的)的人才,你们可以组成ACOWPG(ALL CHINA Oracle Worst Practices Group)进一步交流这方面的心得。

好吧,以上以上!如果你和我一样是DBA,工作中有一个这样”蓄意”制造麻烦的家伙就已经够呛了,如果有一堆那么就是doomsday了。
[Read more…]

Script:AWR Trending

Kapil Goyal在他的IOUG presentation中共享了几个很有用的AWR性能诊断脚本,在这里共享一下:

 

 

Select extract(day from snap_interval) * 24 * 60 +
extract(hour from snap_interval) * 60 +
extract(minute from snap_interval) "Snapshot Interval",
extract(day from retention) * 24 * 60 +
extract(hour from retention) * 60 + extract(minute from retention) "Retention Interval(Minutes) ",
extract(day from retention) "Retention(in Days) "
from dba_hist_wr_control;

 

 

System Event Trending

 

event_response.sql
alter session set nls_date_format='dd-mon-yy';
set lines 150 pages 100 echo off feedback off
col date_time heading 'Date time|mm/dd/yy_hh_mi_hh_mi' for a30
col event_name for a26
col waits for 99,999,999,999 heading 'Waits'
col time for 99,999 heading 'Total Wait|Time(sec)'
col avg_wait_ms for 99,999 heading 'Avg Wait|(ms)'
prompt "Enter the date in DD-Mon-YY Format:"
WITH system_event AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
se.event_name event_name,
se.total_waits e_total_waits,
lag(se.total_waits, 1) over(order by se.snap_id) b_total_waits,
se.total_timeouts e_total_timeouts,
lag(se.total_timeouts, 1) over(order by se.snap_id) b_total_timeouts,
se.time_waited_micro e_time_waited_micro,
lag(se.time_waited_micro, 1) over(order by se.snap_id) b_time_waited_micro
from dba_hist_system_event se, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and se.snap_id = sn.snap_id
and se.dbid = sn.dbid
and se.instance_number = sn.instance_number
and se.dbid = (select dbid from v$database)
and se.instance_number = (select instance_number from v$instance)
and se.event_name = '&event_name') select to_char
(se1.BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char
(se1.END_INTERVAL_TIME, '_hh24_mi') date_time,
se1.event_name,
se1.e_total_waits - nvl(se1.b_total_waits,
0) waits,
(se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,
0)) / 1000000 time,
((se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,
0)) / 1000) / (se1.e_total_waits - nvl(se1.b_total_waits,
0)) avg_wait_ms from system_event se1 where(se1.e_total_waits - nvl(se1.b_total_waits,
0)) > 0 and nvl(se1.b_total_waits,
0) > 0
/

 

 

Load Profile Trending

 

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = '&stat_name')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

 

 

Time Model Statistics Trend

 

 

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000
col date_time heading 'Date time' for a40
col stat_name heading 'Statistics Name' for a25
col time heading 'Time (s)' for 99,999,999,999
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'DBtime', 'DB CPU', 'sql execute elapsed time', 'PL/SQL execution elapsed time','parse time elapsed', 'background elapsed time'"
WITH systimemodel AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
st.stat_name stat_name,
st.value e_value,
lag(st.value, 1) over(order by st.snap_id) b_value
from DBA_HIST_SYS_TIME_MODEL st, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and st.snap_id = sn.snap_id
and st.dbid = sn.dbid
and st.instance_number = sn.instance_number
and st.dbid = (select dbid from v$database)
and st.instance_number = (select instance_number from v$instance)
and st.stat_name = '&stat_name')
select to_char (BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char (END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name, round((e_value - nvl(b_value, 0)) / 1000000) time
from systimemodel
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
/

 

 

Want to Know if Execution Plan Changed Recently?

 

 

set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'
col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/

 

 

xp_awr.sql

 

 

select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));

Script:常用SQL语句优化脚本

SQLT 下载 https://www.askmaclean.com/wp-content/uploads/2011/01/sqlt.zip 

[oracle@PD009 xplore]$ pwd
/home/oracle/sqlt/utl/xplore
[oracle@PD009 xplore]$ ls
create_xplore_script.sql  drop_sys_views.sql  drop_user_objects.sql  install.sql  readme.txt  star.sql  sys_views.sql  uninstall.sql  user_objects.sql  xplore_2.zip  xplore.pkb  xplore.pks  xplore_script_1.log  xplore_script_1.sql
SQL> start install
Test Case User: SH
Password: oracle
Installation completed.
You are now connected as SH.
1. Set CBO env if needed
2. Execute @create_xplore_script.sql
SQL> @create_xplore_script.sql
Parameter 1:
XPLORE Method: XECUTE (default) or XPLAIN
"XECUTE" requires /* ^^unique_id */ token in SQL
"XPLAIN" uses "EXPLAIN PLAN FOR" command
Enter "XPLORE Method" [XECUTE]: 
Parameter 2:
Include CBO Parameters: Y (default) or N
Enter "CBO Parameters" [Y]: 
Parameter 3:
Include Exadata Parameters: Y (default) or N
Enter "EXADATA Parameters" [Y]: 
Parameter 4:
Include Fix Control: Y (default) or N
Enter "Fix Control" [Y]: 
Parameter 5:
Generate SQL Monitor Reports: N (default) or Y
Only applicable when XPLORE Method is XECUTE
Enter "SQL Monitor" [N]: 
Review and execute @xplore_script_1.sql
SQL>@xplore_script_1.sql
SH
Parameter 1:
Name of SCRIPT file that contains SQL to be xplored (required)
Note: SCRIPT must contain comment /* ^^unique_id */
Enter value for 1: star.sql
[oracle@PD009 xplore]$ cat star.sql
SELECT /* ^^unique_id */ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'FL'
AND ch.channel_desc = 'Direct Sales'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
GROUP BY c.cust_city, t.calendar_quarter_desc;

 

生成的XPLORE Report  xplore_report_2

 

 

 

 

select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) gather_plan_statistics*/ count(*) from tvb b;
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
注意dynamic sampling used for this statement (level=2) 显示的level 2不是真的! level 10在这里真的是LEVEL 10!
EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
select count(*) from tvb ;
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
set linesize 200 pagesize 1400;
select /* FINDSQLID */ SQL_ID,SQL_FULLTEXT from V$SQL  where SQL_TEXT LIKE '%&SQLTEXT%'  and SQL_FULLTEXT NOT LIKE '%FINDSQLID%'
union all
select SQL_ID,SQL_TEXT FROM DBA_HIST_SQLTEXT where SQL_TEXT LIKE '%&SQLTEXT%'
and SQL_TEXT NOT LIKE '%FINDSQLID%';
alter session set events '10046 trace name context forever,level 12';
alter session set events '10053 trace name context forever,level 1';
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- Execute the queries or operations to be traced here --
1.- Please provide AWR and ADDM report from each instance for interval of 30 minutes when the problem is present.
2.- Upload OS log file /var/log/messages
3.- Please upload background process trace files for each instance. LMD, LMS, LMON, DBWR, LGWR, diag, pmon, smon, etc.
有问题请去http://t.askmaclean.com/forum-4-1.html提问, 会在一定时间内反馈给你
提问请写明 数据库版本、OS版本、问题类型
如果是性能问题请给出 AWR、ASH、ADDM及10046 TRACE
如果是ORA-600/7445错误请给出ALERT.LOG及其TRACE
如果是RAC CLUTERWARE问题请给出CRSD.LOG和CSSD.LOG
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm like '%disable%';
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;
select spid,pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
select name,value from v$system_parameter where ISDEFAULT!='TRUE'  order by 1;
set linesize 200 pagesize 1400
@?/rdbms/admin/utllockt
==========================================================================================>
执行计划历史
Want to Know if Execution Plan Changed Recently?
set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'
col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/
xp_awr.sql
select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));
SELECT to_char(TIME,'hh24:mi') , S.*
FROM (SELECT NVL(WAIT_CLASS, 'CPU') ACTIVITY,
TRUNC(SAMPLE_TIME, 'MI') TIME
FROM GV$ACTIVE_SESSION_HISTORY) V   PIVOT(COUNT(*)  FOR ACTIVITY IN ('CPU' AS "CPU", 'Concurrency' AS "Concurrency", 'System I/O' AS "System I/O", 'User I/O' AS "User I/O", 'Administrative' AS "Administrative", 'Configuration' AS "Configuration", 'Application' AS "Application", 'Network' AS "Network", 'Commit' AS "Commit", 'Scheduler' AS "Scheduler", 'Cluster' AS "Cluster", 'Queueing' AS "Queueing", 'Other' AS "Other"))
S
WHERE TIME > SYSDATE - INTERVAL '500' MINUTE
ORDER BY TIME
SELECT *
FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
SQL_ID,
plan_hash_value,
TO_CHAR (FIRST_LOAD_TIME) begin_time,
'在cursor cache中' end_time,
executions "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM Gv$SQL 
WHERE sql_id = '&A'
UNION ALL
SELECT '2.sqltuning set' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT '3.dba_advisor_sqlstats' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT DISTINCT
'4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
source,
sql_id,
PLAN_HASH_VALUE,
TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
SQL.executions_delta,
SQL.buffer_gets_delta
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"LIO/exec",
(SQL.cpu_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"CPUTIM/exec",
(SQL.elapsed_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ETIME/exec",
SQL.DISK_READS_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"PIO/exec",
SQL.ROWS_PROCESSED_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ROWs/exec"
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE     SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
AND SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql_id IN ('&A'))
ORDER BY source, begin_time DESC;
prompt 15 Most expensive SQL in the cursor cache
SELECT *
FROM (SELECT SQL_ID,
ELAPSED_TIME / 1000000 AS ELAPSED,
SQL_TEXT
FROM V$SQLSTATS
ORDER BY ELAPSED_TIME DESC)
WHERE ROWNUM <= 15;
prompt 15 Most expensive SQL in the workload repository
select * from (
select stat.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed,
(select to_char(substr(st.sql_text,1,55))
from dba_hist_sqltext st
where st.dbid = stat.dbid and st.sql_id = stat.sql_id) as sql_text_fragment
from dba_hist_sqlstat stat, dba_hist_sqltext text
where stat.sql_id = text.sql_id and
stat.dbid = text.dbid
group by stat.dbid, stat.sql_id
order by elapsed desc
) where ROWNUM <= 15;

19c New Feature:Real-time SQL Monitoring for Developers

Mainly scenarios is following :
1.Database users can generate and view SQL monitor report of the SQL statements issued by themself, without granting any additional privileges.
2.If users have not been granted the SELECT_CATALOG_ROLE ,they can not generate and view SQL monitor report of SQL statements executed by other users.
3.If users have been granted the SELECT_CATALOG_ROLE ,they can see SQL monitor report of SQL executed by other users .
And you can generate and view SQL monitoring report from the SQL*PLUS command line by DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST and DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST package or from Enterprise Manager (EM) just as 11g (see Doc ID 1229904.1 and Doc ID 1380492.1).
TEST CASE:
--1.create users
conn / as sysdba
--1-1.create Low-privileged users without DBA privilege
--dep1 for SQL Statement using Tables
drop user dep1 cascade;
create user dep1 identified by dep1;
alter user dep1 quota unlimited on users;
grant connect,resource to dep1;
--2.prepare test data
conn dep1/dep1
drop table testa_dep1;
drop table testb_dep1;
create table testa_dep1(c1 number, c2 char(100));
create table testb_dep1(c1 number, c2 char(100));
begin
for i in 1 .. 200 loop
for j in 1 .. 100 loop
insert into testa_dep1 values(i,'A');
commit;
end loop;
end loop;
end;
/
begin
for i in 1 .. 200 loop
for j in 1 .. 100 loop
insert into testb_dep1 values(i,'A');
commit;
end loop;
end loop;
end;
/
--2.Executing SQL and check SQL Monitor Active Report 
conn dep1/dep1
select /*+ use_nl(a b) */ count(*)
from testa_dep1 a, testb_dep1 b
where a.c1=b.c1;
--3.Generate and view SQL Monitor List and Active Report 
--should be able to view SQL monitor report of the SQL statements issued by user-self, without granting any additional privileges.
--should not be able to view SQL monitor report of SQL statements issued by other users.
--REPORT_SQL_MONITOR_LIST
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
spool monitor_list_sql_dep1_active.html
SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'ACTIVE',report_level => 'ALL') AS report FROM dual;
spool off
--REPORT_SQL_MONITOR
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool monitor_sql_dep1.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off

沪ICP备14014813号

沪公网安备 31010802001379号