Script:SQL调优健康检查脚本

以下脚本可以用于收集SQL调优的相关信息,包括统计信息、优化器参数等。

When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. Health-checks are performed over:

  • CBO Statistics for schema objects accessed by the one SQL statement being analyzed
  • CBO Parameters
  • CBO System Statistics
  • CBO Data Dictionary Statistics
  • CBO Fixed-objects Statistics

 

下载sqlhc脚本

 

示例输出HTML:

 

 

[转]BUFFER SORT是BUFFER却不是SORT

用AUTOTRACE查看执行的计划的同学常问到执行计划里的BUFFER SORT是什么意思,这里为什么要排序呢?

BUFFER SORT不是一种排序,而是一种临时表的创建方式。

BUFFER是执行计划想要表达的重点,是其操作: 在内存中存放一张临时表。

SORT修饰BUFFER,表示具体在内存的什么地方存放临时表: 在PGA的SQL工作区里的排序区。

至少有一种方法可以说服对此表示怀疑的人们,就是查询V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段。

将STATISTICS_LEVEL设置为ALL先,然后执行真-排序命令,比如:select hire_date,salary from hr.employees order by hire_date

然后查看其V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段:

SYS@br//scripts> select projection from v$sql_plan_statistics_all where 
sql_id=(select sql_id from v$sql where sql_text='select hire_date,salary from hr.employees order by hire_date') 
and operation='SORT' and options='ORDER BY';

PROJECTION
----------------------------------------------------------------
(#keys=1) "HIRE_DATE"[DATE,7], "SALARY"[NUMBER,22]

1 row selected.

其中开头的#keys表示返回的结果中排序的字段数量。

再执行一句真-排序命令:select hire_date,salary from hr.employees order by salary,hire_date

然后查看其V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段,#keys因该为2:

SYS@br//scripts> select projection from v$sql_plan_statistics_all where 
sql_id=(select sql_id from v$sql where sql_text='select hire_date,salary from 
hr.employees order by salary,hire_date') and operation='SORT' and options='ORDER BY';

PROJECTION
------------------------------------------------------------------------------------
(#keys=2) "SALARY"[NUMBER,22], "HIRE_DATE"[DATE,7]

1 row selected.

来看看我们萌萌的BUFFER SORT的表现吧~

执行下面这个查询,它使用了所谓的BUFFER SORT:

select ch.channel_class,c.cust_city,sum(s.amount_sold) sales_amount
from sh.sales s,sh.customers c,sh.channels ch
where s.cust_id=c.cust_id and s.channel_id=ch.channel_id and
c.cust_state_province='CA' and
ch.channel_desc='Internet'
group by ch.channel_class,c.cust_city

附上其执行计划,Id为5的Operation是BUFFER SORT:
execution Plan
----------------------------------------------------------
Plan hash value: 3047021169

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |   133 |  7980 |   902   (2)| 00:00:11 |       |       |
|   1 |  HASH GROUP BY         |           |   133 |  7980 |   902   (2)| 00:00:11 |       |       |
|*  2 |   HASH JOIN            |           | 12456 |   729K|   901   (2)| 00:00:11 |       |       |
|   3 |    MERGE JOIN CARTESIAN|           |   383 | 18001 |   408   (1)| 00:00:05 |       |       |
|*  4 |     TABLE ACCESS FULL  | CHANNELS  |     1 |    21 |     3   (0)| 00:00:01 |       |       |
|   5 |     BUFFER SORT        |           |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|*  6 |      TABLE ACCESS FULL | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|   7 |    PARTITION RANGE ALL |           |   918K|    11M|   489   (2)| 00:00:06 |     1 |    28 |
|   8 |     TABLE ACCESS FULL  | SALES     |   918K|    11M|   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------------

查看其V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段:

SYS@br//scripts> select distinct projection from v$sql_plan_statistics_all where sql_id in 
(select distinct sql_id from v$sql where sql_text like 
'%where s.cust_id=c.cust_id and s.channel_id=ch.channel_id and%') 
and operation='BUFFER' and options='SORT';

PROJECTION
-------------------------------------------------------------------------
(#keys=0) "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30]

1 row selected.

结果#keys等于0,是0啊… 0意味着该操作根据0个字段排序,那就是没有排序咯。

同样显示SORT但是不SORT打着左灯向右转的还有著名的SORT AGGREGATE。

只能这样说,AUTOTRACE中执行计划操作的取名有时真的太淘气了。

转自包光磊的博客:http://blogs.oracle.com/toddbao/entry/buffer_sort%E6%98%AFbuffer%E5%8D%B4%E4%B8%8D%E6%98%AFsort

 

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,可以为我们提供更为详尽的执行统计信息 */

11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR

11g中引入了新的动态性能视图V$SQL_MONITOR,该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动,同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行,V$SQL_MONITOR视图中的统计信息将被实时刷新,频率为每秒1次。SQL语句执行完成后,监视信息将不会被立即删除,Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制,默认为60s),最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。
[Read more…]

ORA-00600:[32695], [hash aggregation can't be done]错误一例

还是那个hash group by算法的问题,日志文件中出现以下记录:

*** ACTION NAME:(SQL 窗口 - 新建) 2010-09-03 14:27:54.594
*** MODULE NAME:(PL/SQL Developer) 2010-09-03 14:27:54.594
*** SERVICE NAME:(HQYDB1) 2010-09-03 14:27:54.594
*** SESSION ID:(3205.17923) 2010-09-03 14:27:54.594
*** 2010-09-03 14:27:54.594
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Current SQL statement for this session:
create table zou_201008_cell_id as
select /* g_all_cdr02,60 */
calling_num mobile_number,
lac,
lpad(cell_id,5,'0') cell_id,
count(*) c,
sum(call_duration) call_duration,
sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
from  g_all_cdr02
where substr(calling_num,1,7) in (select mobile_prefix from zou_mobile_prefix)
group by
calling_num ,
lac,
lpad(cell_id,5,'0')
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              100000000 ? 11055A9A0 ?
ksedmp+0290          bl       ksedst               104A54870 ?
ksfdmp+0018          bl       03F30204
kgeriv+0108          bl       _ptrgl
kgesiv+0080          bl       kgeriv               1050BE654 ? 1050BE604 ?
                                                   0000027E5 ? 080000000 ?
                                                   07FFFFFFF ?
ksesic1+0060         bl       kgesiv               43300000FFFF5310 ?
                                                   4530000000000000 ?
                                                   000000071 ? 000000001 ?
                                                   000000000 ?
qeshPartitionBuildH  bl       01F9CA24
D+04bc
qeshGBYOpenScan2+02  bl       qeshPartitionBuildH  0000027E5 ? 1105C06C0 ?
34                            D
qeshGBYOpenScan+001  bl       qeshGBYOpenScan2     FFFFFFFFFFF5740 ? 11055A938 ?
8                                                  000000000 ? 000000010 ?
qerghFetch+05e8      bl       qeshGBYOpenScan      000001000 ?
rwsfcd+0054          bl       _ptrgl
qerltFetch+036c      bl       03F2EB1C
ctcdrv+4160          bl       01F9C898
opiexe+2884          bl       ctcdrv               100000001 ? 100000001 ?
                                                   110467F30 ?
opiosq0+19f0         bl       opiexe               FFFFFFFFFFF8B50 ?
                                                   2824422142420820 ?
                                                   FFFFFFFFFFF8C10 ?
kpooprx+0168         bl       opiosq0              300000000 ? 000000000 ?
                                                   000000000 ? A4000000000000 ?
kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB464 ?
                                                   FFFFFFFFFFFB068 ?
                                                   1BF000001BF ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   000000000 ? 1103878F8 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F9F2A0
opiino+0990          bl       opitsk               000000000 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F9E0E8
sou2o+0090           bl       opidrv               3C02DC1BBC ? 44065F000 ?
                                                   FFFFFFFFFFFF3A0 ?
opimai_real+01bc     bl       01F9B9F4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?

--------------------- Binary Stack Dump ---------------------

这次是因为应用人员不了解alter session的作用域,在PL/SQL Developer工具中的不同窗口(也就是不在同一会话中)中执行了”alter session set “_gby_hash_aggregation_enabled” = false;”和涉及group by操作的SQL,并导致了unpublished bug:6471770被触发。
我们比较容易地workaround绕过这个Bug:


/* 在会话级别设置优化参数_gby_hash_aggregation_enabled */

alter session set "_gby_hash_aggregation_enabled" = false;

/* 或者在语句中加入NO_USE_HASH_AGGREGATION的 hint */

select  /*+ NO_USE_HASH_AGGREGATION */ ....

以上提及的unpublished bug:6471770据称在10.2.0.5,11.1.0.7,11.2.0.1版本中被修正了。

OPT_PARAM Hint

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.3 – Release: 10.2 to 10.2
Information in this document applies to any platform.
Goal
This article is explains the new optimizer hint “OPT_PARAM” introduced in 10g R2.
Solution
“OPT_PARAM” is a new optimizer hint introduced in 10g Release 2. This hint behaves the same way as
setting a parameter (e.g, using alter session) except that the effect is for the statement only. The hint only works for optimizer parameters. Global parameters such as optimizer_features_enable are not covered but optimizer_features_enable specifically has its own hint:

/*+ optimizer_features_enable(‘9.2.0’) */

@For an INTERNAL list of usable parameters see: Note:986618.1 Parameters useable by OPT_PARAM hint
Hint Syntax
The syntax is:

/*+ opt_param( [,] ) */

parameter_name is the name of a parameter
parameter_value is its value.

If the parameter contains a numeric value, the parameter value has to be specified without quotes.
The hint can be used to set multiple parameters by repeating the hint, i.e.

/*+ opt_param( [,] )
opt_param( [,] )
*/

Basic Usage Example
For example, the following hint sets <> to ‘false’ when added to a statement:

/*+ opt_param(‘hash_join_enabled’,’false’) */

e.g.:
Without the hint:


SQL> select empno from emp e, dept d where e.ename=d.dname

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |   160 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     8 |   160 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    28 |   280 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

With the hint the hash join is disabled choosing a different plan:


SQL> select /*+ opt_param('hash_join_enabled','false') */ empno 
from emp e, dept d where e.ename=d.dname;


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     8 |   160 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |     8 |   160 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    40 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |    28 |   280 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    28 |   280 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------



Multiple Parameter Settings example
The OPT_PARAM hint can be specified more than once Time to adjust more than one parameter at once as follows:


/*+ OPT_PARAM('_always_semi_join' 'off')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('_new_initial_join_orders' 'false')
      OPT_PARAM('optimizer_dynamic_sampling' 1)
      OPT_PARAM('optimizer_index_cost_adj' 1) */

Tune Very Large Hash Join

set timing on;
alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;

alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;

alter session set events '10351 trace name context forever, level 128';

alter session set hash_area_size=524288000;
alter session set hash_area_size=524288000;

alter session set "_hash_multiblock_io_count"=128;
alter session set "_hash_multiblock_io_count"=128;

alter session enable parallel query;

select /*+   pq_distribute(a hash,hash) parallel(a) parallel(b) */ column1,column2....
  from source_tab a, driving_tab b
 where  condition
 ;



--PQ_DISTRIBUTE(tab,out,in) How to distribute rows from tab in a PQ
--(out/in may be HASH/NONE/BROADCAST/PARTITION)

List Oracle SQL Hints

Below lists Oracle SQL hints:

沪ICP备14014813号-2

沪公网安备 31010802001379号