以下脚本可以用于收集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:
示例输出HTML:
用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因该为２: 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
在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，该视图用以显示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…]
还是那个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版本中被修正了。
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) */
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)
Below lists Oracle SQL hints:
