10g中HASH GROUP BY引起的临时表空间不足

2010/08/04 by admin 4 Comments

今天早上应用人员反映一个原本在9i上可以顺利完成的CTAS脚本，迁移到10g后运行总是报“ORA-1652: unable to extend temp segment by 128 in tablespace TS_HQY1_TEMP “无法扩展临时表空间的错误。应用人员表示该脚本涉及的数据量在迁移前后变化不大，而且令人匪夷所思的是在新的10g库上临时表空间大小已达40多个G，要远大于原9i库。很显然这不是由于临时表空间过小导致的该问题，更多的原因肯定是出在迁移后Oracle不同的行为方式上。
该脚本每月执行一次用以汇总数据，其中一个单表接近4亿行记录，GROUP BY操作涉及到的数据量十分庞大。我们来具体看一下这个SQL:

create table gprs_bill.zou_201007_cell_id as
select /* g_all_cdr01,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 gprs_bill.g_all_cdr01
 where substr(calling_num, 1, 7) in
       (select mobile_prefix from gprs_bill.zou_mobile_prefix)
 group by calling_num, lac, lpad(cell_id, 5, '0');

SQL> set autotrace traceonly exp
SQL> select /* g_all_cdr01,60 */
  2  calling_num mobile_number,
  3  lac,
  4  lpad(cell_id,5,'0') cell_id,
  5  count(*) c,
  6  sum(call_duration) call_duration,
  7  sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
  8  sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
  9  from  gprs_bill.g_all_cdr01
 10  where substr(calling_num,1,7) in (select mobile_prefix from gprs_bill.zou_mobile_prefix)
 11  group by
 12  calling_num ,
 13  lac,
 14  lpad(cell_id,5,'0');

Execution Plan
----------------------------------------------------------
Plan hash value: 212866585

--------------------------------------------------------------------------------
-------------------

| Id  | Operation             | Name              | Rows  | Bytes |TempSpc| Cost
 (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------

|   0 | SELECT STATEMENT      |                   |   229K|  9880K|       |  103
3K  (3)| 03:26:41 |

|   1 |  HASH GROUP BY        |                   |   229K|  9880K|    22M|  103
3K  (3)| 03:26:41 |

|*  2 |   HASH JOIN RIGHT SEMI|                   |   229K|  9880K|       |  103
0K  (3)| 03:26:10 |

|   3 |    TABLE ACCESS FULL  | ZOU_MOBILE_PREFIX |  1692 | 13536 |       |    1
1   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | G_ALL_CDR01       |   388M|    13G|       |  102
6K  (2)| 03:25:21 |

--------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MOBILE_PREFIX"=SUBSTR("CALLING_NUM",1,7))

可以看到Oracle使用了HASH GROUP BY 算法以实现数据分组；HASH算法是10g中新引入的分组算法。
下面我们来详细介绍下10g中数据分组的改动：
在10g中GROUP BY操作仍将引发排序操作，但10g中引入了新的算法，这些算法都不保证返回的数据行有序排列；在10g中如果想保证”GROUP BY”后返回的数据有序排列则需要强制使用”ORDER BY”子句，这点和9i是截然不同的。若你没有指定”ORDER BY”子句，则不能保证返回的结果正确排序。
在10g中”GROUP BY”子句更倾向于使用一种HASH算法而非原先的SORT算法来分组数据，HASH算法的CPU COST要低于原先的SORT算法。但这2种算法在10g中都不保证返回数据正常排序，当采用SORT算法时可能”碰巧”出现返回正常排序数据的状况。
MOS建议，如果迁移中出现大量不利的变化，则可以通过修改参数来确保沿用原先的算法。但需要注意的是，即便采用了以下参数仍不能保证10g后”GROUP BY”后返回的数据如9i中那样排序，你需要做的是加入显式的”ORDER BY”子句以保证Oracle为你做到这一点。

alter session set "_gby_hash_aggregation_enabled" = false;
alter session set optimizer_features_enable="9.2.0";
或者
alter session set optimizer_features_enable="8.1.7";

其中_gby_hash_aggregation_enabled隐式参数决定了Oracle是否可以启用新的HASH算法来进行数据分组(也适用于distinct等操作)。

对于以上说法我们通过实验进一步验证：

在11g中的测试如下：
SQL> select  * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select  *  from youyus;

T1                 T2
---------- ----------
A                  10
B                  10
F                  30
G                  30
H                  40
I                  40
J                  40
L                  20
M                  20

已选择9行。
SQL>  analyze table youyus compute statistics for all columns;

表已分析。

SQL> set autotrace on;

SQL>  select t2,count(*) from youyus group by t2;

        T2   COUNT(*)
---------- ----------
        30          2
        20          2
        40          3
        10          2


执行计划
----------------------------------------------------------
Plan hash value: 2940504347

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |     8 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |        |     4 |     8 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| YOUYUS |     9 |    18 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*可以看到使用了hash算法，且返回结果未按t2列大小顺序排列*/

SQL> select t2,count(*) from youyus group by t2 order by t2;

        T2   COUNT(*)
---------- ----------
        10          2
        20          2
        30          2
        40          3


执行计划
----------------------------------------------------------
Plan hash value: 1349668650

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |     8 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY     |        |     4 |     8 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| YOUYUS |     9 |    18 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*加入order by子句后，又变回了SORT算法，而且正常排序*/
SQL> alter session set "_gby_hash_aggregation_enabled" = false;

会话已更改。
SQL> alter session set optimizer_features_enable="9.2.0";

会话已更改。
SQL> select t2,count(*) from youyus group by t2;

        T2   COUNT(*)
---------- ----------
        10          2
        20          2
        30          2
        40          3


执行计划
----------------------------------------------------------
Plan hash value: 1349668650

-------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |     8 |    11 |
|   1 |  SORT GROUP BY     |        |     4 |     8 |    11 |
|   2 |   TABLE ACCESS FULL| YOUYUS |     9 |    18 |     2 |
-------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)
/*optimizer_features_enable设置为9.2.0后cpu cost被off了；返回数据正确排序，但我们要记住这是"侥幸"*/

SQL> alter session set optimizer_features_enable="10.2.0.5";

会话已更改。
SQL> select t2,count(*) from youyus group by t2;

        T2   COUNT(*)
---------- ----------
        10          2
        20          2
        30          2
        40          3


执行计划
----------------------------------------------------------
Plan hash value: 1349668650

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |     8 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY     |        |     4 |     8 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| YOUYUS |     9 |    18 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*optimizer_features_enable设为10.2.0.5 一切正常*/
SQL> alter session set optimizer_features_enable="11.2.0.1";

会话已更改。

SQL> select t2,count(*) from youyus group by t2;

        T2   COUNT(*)
---------- ----------
        10          2
        20          2
        30          2
        40          3


执行计划
----------------------------------------------------------
Plan hash value: 1349668650

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |     8 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY     |        |     4 |     8 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| YOUYUS |     9 |    18 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*11.2.0.1中没有变化*/
SQL> alter session set optimizer_features_enable="8.1.7";

会话已更改。

SQL> alter session set "_gby_hash_aggregation_enabled" =true;

会话已更改。
/*看看optimizer_features_enable设为8.1.7，而_gby_hash_aggregation_enabled为true,这种"矛盾"情况下的表现*/
SQL> select t2,count(*) from youyus group by t2;

        T2   COUNT(*)
---------- ----------
        30          2
        20          2
        40          3
        10          2


执行计划
----------------------------------------------------------
Plan hash value: 2940504347

-------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |     8 |    10 |
|   1 |  HASH GROUP BY     |        |     4 |     8 |    10 |
|   2 |   TABLE ACCESS FULL| YOUYUS |     9 |    18 |     1 |
-------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)
/*居然仍采用了HASH GROUP BY,看起来类似_gby_hash_aggregation_enabled这类参数优先级要高于optimizer_features_enable*/

9i上的表现如下:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> analyze table youyus_9i compute statistics for all columns;

Table analyzed.

SQL> select * from youyus_9i;

T1         T2
-- ----------
A          10
B          10
F          30
G          30
H          40
I          40
J          40
L          20
M          20

9 rows selected.

SQL> alter session set optimizer_mode=ALL_ROWS;

Session altered.

SQL> select t2,count(*) from youyus_9i group by t2;

        T2   COUNT(*)
---------- ----------
        10          2
        20          2
        30          2
        40          3


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=4 Bytes=8)
   1    0   SORT (GROUP BY) (Cost=4 Card=4 Bytes=8)
   2    1     TABLE ACCESS (FULL) OF 'YOUYUS_9I' (Cost=2 Card=21 Bytes
          =42)
/*9i下虽然没有指定order by,但我们可以放心返回的数据总是排序的;*/

SQL> alter session set "_gby_hash_aggregation_enabled" =true;
alter session set "_gby_hash_aggregation_enabled" =true
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
/*9i下不存在_gby_hash_aggregation_enabled隐式参数*/

That's great!

应用脚本没有数据一定要正确排序的强制要求，但使用HASH GROUP BY算法后临时表空间的使用量大幅上升，远大于之前在9i上的使用量，最后导致语句无法顺利完成。首先想到的当然是通过修改_gby_hash_aggregation_enabled参数恢复到原先的SORT算法，并观察其临时表空间使用量:

SQL> alter session set "_gby_hash_aggregation_enabled"=false;
Session altered.

SQL> select /* g_all_cdr01,60 */
  2  calling_num mobile_number,
  3  lac,
  4  lpad(cell_id,5,'0') cell_id,
  5  count(*) c,
  6  sum(call_duration) call_duration,
  7  sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
  8  sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
  9  from  gprs_bill.g_all_cdr01
 10  where substr(calling_num,1,7) in (select mobile_prefix from gprs_bill.zou_mobile_prefix)
 11  group by
 12  calling_num ,
 13  lac,
 14  lpad(cell_id,5,'0');

Execution Plan
----------------------------------------------------------
Plan hash value: 4013005149

--------------------------------------------------------------------------------
-------------------

| Id  | Operation             | Name              | Rows  | Bytes |TempSpc| Cost
 (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------

|   0 | SELECT STATEMENT      |                   |   229K|  9880K|       |  103
3K  (3)| 03:26:41 |

|   1 |  SORT GROUP BY        |                   |   229K|  9880K|    22M|  103
3K  (3)| 03:26:41 |

|*  2 |   HASH JOIN RIGHT SEMI|                   |   229K|  9880K|       |  103
0K  (3)| 03:26:10 |

|   3 |    TABLE ACCESS FULL  | ZOU_MOBILE_PREFIX |  1692 | 13536 |       |    1
1   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | G_ALL_CDR01       |   388M|    13G|       |  102
6K  (2)| 03:25:21 |

--------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MOBILE_PREFIX"=SUBSTR("CALLING_NUM",1,7))

/*重新执行出现问题的脚本*/
create table gprs_bill.zou_201007_cell_id as
    select /* g_all_cdr01,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  gprs_bill.g_all_cdr01
    where substr(calling_num,1,7) in (select mobile_prefix from gprs_bill.zou_mobile_prefix)
    group by
    calling_num ,
    lac,
    lpad(cell_id,5,'0');

可以看到在会话级别设置_gby_hash_aggregation_enabled为false后，Oracle不再采用10g中的HASH分组算法；因为该CTAS SQL脚本运行时间较长，我们通过动态视图V$SORT_USAGE来观察其运行期间的排序段使用量:

SQL> set time   on;
14:30:59 SQL> select tablespace,contents,segtype,blocks*8/1024 from v$sort_usage where username='GPRS_BILL';

TABLESPACE                      CONTENTS  SEGTYPE   BLOCKS*8/1024
------------------------------- --------- --------- -------------
TS_HQY1_TEMP                    TEMPORARY SORT               9349

14:35:59 SQL> /

TABLESPACE                      CONTENTS  SEGTYPE   BLOCKS*8/1024
------------------------------- --------- --------- -------------
TS_HQY1_TEMP                    TEMPORARY SORT              10011

/*5分钟内共用10011-9349=662MB 临时空间*/
15:02:46 SQL> select target ,totalwork,sofar,time_remaining,elapsed_seconds from v$session_longops where sofar!=totalwork;

TARGET                                                            TOTALWORK      SOFAR TIME_REMAINING ELAPSED_SECONDS
---------------------------------------------------------------- ---------- ---------- -------------- ---------------
GPRS_BILL.G_ALL_CDR01                                               5575890    5435796            143            5557

15:05:10 SQL> select target ,totalwork,sofar,time_remaining,elapsed_seconds from v$session_longops where sofar!=totalwork;

TARGET                                                            TOTALWORK      SOFAR TIME_REMAINING ELAPSED_SECONDS
---------------------------------------------------------------- ---------- ---------- -------------- ---------------
GPRS_BILL.G_ALL_CDR01                                               5575890    5562082             14            5692

15:05:13 SQL> select tablespace,contents,segtype,blocks*8/1024 from v$sort_usage where username='GPRS_BILL';

TABLESPACE                      CONTENTS  SEGTYPE   BLOCKS*8/1024
------------------------------- --------- --------- -------------
TS_HQY1_TEMP                    TEMPORARY SORT              13835

15:12:22 SQL> select tablespace,contents,segtype,blocks*8/1024 from v$sort_usage where username='GPRS_BILL';

TABLESPACE                      CONTENTS  SEGTYPE   BLOCKS*8/1024
------------------------------- --------- --------- -------------
TS_HQY1_TEMP                    TEMPORARY SORT              13922

/* 排序已经完成，排序段不再增长*/

该分组操作最后排序段使用量为13922MB，在客户可以接受的范围内。看起来新引入的HASH算法虽然有CPU成本低于SORT算法的优势，但可能消耗大量临时空间，可谓有得有失。

Filed Under: Oracle Tagged With: database, ORA, Oracle, Oracle 11g新特性, RAC, sql

Comments

admin says

2010/08/04 at 15:28

MOS上的相关NOTE quote:
”
ORA-600 [32695] [hash aggregation can’t be done], ORA-1652 on Temp Tablespace
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.2
This problem can occur on any platform.
Symptoms
1). The following errors are encountered:

ORA-00600: internal error code, arguments: [32695], [hash aggregation can’t be done]
ORA-1652 on TEMP tablespace

2). The error is occurring on a SELECT statement with a GROUP BY clause.
3). The call stack may resemble:

ksfdmp kgeriv kgesiv ksesic1 qeshPartitionBuildHD qeshGBYOpenScan2 qeshGBYOpenScan qerghFetch qertqoFetch qerpxSlaveFetch qerpxFetch insdlexe insExecStmtExecIniEngine insexe

Changes
The database may have recently been upgraded from 10.1 to 10.2.
Cause
The problem here is not the hash join, but the group by hash. Hash aggregation is new to 10.2. The GROUP BY hash clause can cause the statement to consume temporary tablespace resources and eventually fail with the error ORA-00600: internal error code, arguments: [32695], [hash aggregation can’t be done].

Bug 5893340 is fixed in 11G and is planned to be included in 10.2.0.5
Solution

Choose from one of the following solutions:

1). set _gby_hash_aggregation_enabled = false e.g.:

alter system set “_gby_hash_aggregation_enabled”=false;
alter session set “_gby_hash_aggregation_enabled”=false;

2). Disable the use of hash group by changing the parameter “optimizer_features_enable”:

set optimizer_features_enable to “10.1.0”

This is now fixed in 10.2.0.4.0″

回复
admin says

2010/08/04 at 15:40

‘GROUP BY’ DOES NOT SORT IF YOU DON’T USE ORDER BY IN 10G

Changes
Upgraded Database to version 10G.
Cause

This is normal behaviour since ORDER BY is not used

From:

Oracle® Database SQL Reference
10g Release 2 (10.2) Part Number B14200-02
Chapter 19 SQL Statements: SAVEPOINT to UPDATE

(then select the SELECT chapter)

” order_by_clause

Without an order_by_clause, no guarantee exists that the same query executed
more than once will retrieve rows in the same order.
”

Explanation:

If no order by clause is specified then the order of the rows retrieved depends on the method used to retrieve the rows from the database. In other words, it depends on the execution plan chosen.

In version 10G several enhancements and changes have been introduced which can influence optimizer
to choose different execution plans than in previous versions.

For changes in 10G please see:

Note:295819.1 Upgrading from 9i to 10g – Potential Query Tuning Related Issues

The GROUP BY clause still performs sort operations but in version 10G, new internal Sort algorithms have been introduced which do not guarantee the order of data retrieval and may change the order for returned rows.

The following example shows that Sort operation used in 9.2.0 optimizer is Sort by group while 10g optimizer is using HASH GROUP BY operation. Each sort algorithm orders data in a different way so, if rows must be ordered by tablespace_name and cluster_name, an order by clause should be added.

SQL> set linesize 130
SQL> set autotrace traceonly
SQL> alter session set optimizer_features_enable=”10.2.0.1″;
Session altered
SQL> SELECT TABLESPACE_NAME ,CLUSTER_NAME,COUNT(*)
FROM DBA_TABLES GROUP BY TABLESPACE_NAME ,CLUSTER_NAME;
15 rows selected

Execution Plan
———— Plan hash value: 968802894
———— | Id | Operation ———— | 0 | SELECT STATEMENT | 1 | HASH GROUP BY |* 2 | HASH JOIN | 3 | TABLE ACCESS FULL |* 4 | HASH JOIN |* 5 | HASH JOIN RIGHT OUTER | 6 | TABLE ACCESS FULL |* 7 | HASH JOIN OUTER | 8 | NESTED LOOPS OUTER |* 9 | HASH JOIN RIGHT OUTER | 10 | TABLE ACCESS FULL |* 11 | HASH JOIN | 12 | MERGE JOIN CARTESIAN |* 13 | HASH JOIN |* 14 | FIXED TABLE FULL | 15 | FIXED TABLE FULL | 16 | BUFFER SORT | 17 | TABLE ACCESS FULL |* 18 | TABLE ACCESS FULL | 19 | TABLE ACCESS |* 20 | INDEX UNIQUE SCAN | 21 | TABLE ACCESS FULL |* 22 | TABLE ACCESS FULL ———— 212;——————————————-
212;————————————————————————————-
| Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
212;————————————————————————————-
| | 1571 | 266K| | 668 (3)| 00:00:09 |
| | 1571 | 266K| 568K| 668 (3)| 00:00:09 |
| | 1571 | 266K| | 606 (3)| 00:00:08 |
| USER$ | 62 | 186 | | 2 (0)| 00:00:01 |
| | 1571 | 262K| | 603 (3)| 00:00:08 |
| | 1564 | 244K| | 460 (2)| 00:00:06 |
| USER$ | 62 | 186 | | 2 (0)| 00:00:01 |
| | 1564 | 239K| | 457 (2)| 00:00:06 |
| | 1564 | 227K| | 319 (2)| 00:00:04 |
| | 1564 | 181K| | 227 (2)| 00:00:03 |
| SEG$ | 4567 | 50237 | | 31 (0)| 00:00:01 |
| | 1564 | 164K| | 196 (2)| 00:00:03 |
| | 7 | 553 | | 4 (25)| 00:00:01 |
| | 1 | 68 | | 1 (100)| 00:00:01 |
| X$KSPPI | 1 | 55 | | 0 (0)| 00:00:01 |
| X$KSPPCV | 100 | 1300 | | 0 (0)| 00:00:01 |
| | 7 | 77 | | 4 (25)| 00:00:01 |
| TS$ | 7 | 77 | | 3 (0)| 00:00:01 |
| TAB$ | 1564 | 45356 | | 191 (1)| 00:00:03 |
BY INDEX ROWID| OBJ$ | 1 | 30 | | 1 (0)| 00:00:01 |
| I_OBJ1 | 1 | | | 0 (0)| 00:00:01 |
| OBJ$ | 51427 | 401K| | 137 (2)| 00:00:02 |
| OBJ$ | 51427 | 552K| | 142 (5)| 00:00:02 |
212;————————————————————————————-

SQL> alter session set optimizer_features_enable=”9.2.0″;
Session altered.
SQL> SELECT TABLESPACE_NAME ,CLUSTER_NAME,COUNT(*)
FROM DBA_TABLES GROUP BY TABLESPACE_NAME ,CLUSTER_NAME;
15 rows selected

Plan d’exécution
———————————————————-
Plan hash value: 2997412578
————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost |
————————————————————————————-
| 0 | SELECT STATEMENT | | 16 | 2784 | 183 |
| 1 | SORT GROUP BY | | 16 | 2784 | 183 |
|* 2 | HASH JOIN OUTER | | 16 | 2784 | 180 |
| 3 | NESTED LOOPS OUTER | | 16 | 2736 | 177 |
| 4 | NESTED LOOPS OUTER | | 16 | 2608 | 161 |
| 5 | NESTED LOOPS OUTER | | 16 | 2128 | 145 |
|* 6 | HASH JOIN | | 16 | 1952 | 129 |
| 7 | NESTED LOOPS | | 16 | 1904 | 126 |
|* 8 | HASH JOIN | | 16 | 1728 | 110 |
| 9 | MERGE JOIN CARTESIAN | | 7 | 553 | 25 |
|* 10 | HASH JOIN | | 1 | 68 | 23 |
|* 11 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 11 |
| 12 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | 11 |
| 13 | BUFFER SORT | | 7 | 77 | 14 |
| 14 | TABLE ACCESS FULL | TS$ | 7 | 77 | 2 |
|* 15 | TABLE ACCESS FULL | TAB$ | 16 | 464 | 84 |
|* 16 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 11 | 1 |
|* 17 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | |
| 18 | TABLE ACCESS FULL | USER$ | 62 | 186 | 2 |
| 19 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 |
|* 20 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | |
| 21 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30 | 1 |
|* 22 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | |
| 23 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 |
|* 24 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | |
| 25 | TABLE ACCESS FULL | USER$ | 62 | 186 | 2 |
————————————————————————————-

Solution

The only way to enforce the order of data in a query is to specify an order by clause.
If you do not specify this then there is no guarantee (nor has there ever been a guarantee)
that the data will come back in the order you desire.

In this case the group by clause has been enhanced to allow a hash algorithm rather than a
sorting algorithm to group the data. Neither of these algorithms guaranteed the order the
data would be returned in, the sort algorithm just ‘happened’ to order it the way wanted by chance.

Turning off changes introduced in 10G may make Oracle choosing again sort algorithm and retrieving the rows in the previous order.

If you have a lot of queries to change, you can use one of the following init/spfile parameters in
order to have the same Optimizer behaviour as older versions. But I have to insist that there is no
guarantee optimizer will still choose the same sort operation to group the data. Even with these parameters, it’s not sure the rows will be retrieved in the same order:
“_gby_hash_aggregation_enabled” = false
OR
optimizer_features_enabled=9.2.0
OR
optimizer_features_enabled=8.1.7

回复
admin says

2010/08/04 at 15:41

Hdr: 5302255 10.2.0.1 RDBMS 10.2.0.1 ANALYTICS PRODID-5 PORTID-46 ORA-600
Abstract: ORA-600: [32695], [HASH AGGREGATION CAN’T BE DONE]

PROBLEM:
——–
Select using GROUP BY fails with

ORA-600: [32695], [HASH AGGREGATION CAN’T BE DONE]

This Query is used in Aggregate rollup processing in the Data Warehouse.

DIAGNOSTIC ANALYSIS:
——————–
The error is related to HASH_AGGREGATE generated by GROUP BY, in 10g2.
Looks like the GROUP BY in 10g2 because of hash aggregate consume far more
memory than in 10g1.

ORA-600: internal error code, arguments: [32695], [hash aggregation can’t
be done], [], [], [], [], [], []
Current SQL statement for this session:
SELECT
STAGE_LOG_DATE,
STAGE_SESSION_ID,
SUM (STAGE_PROCESSING_TIME) sum_stage_processing_time_,
COUNT (STAGE_SESSION_HIT_ORDER) count_stage_session_hit_order_,
MIN (STAGE_SESSION_HIT_ORDER) min_stage_session_hit_order_,
MAX (STAGE_SESSION_HIT_ORDER) max_stage_session_hit_order_,
MAX (STAGE_COOKIE_BROWSERID) max_stage_cookie_browserid_,
MAX (STAGE_PROCOL_VERSION) max_stage_procol_version_,
MAX (STAGE_USER_AGENT) max_stage_user_agent_,
MAX (STAGE_CLIENT_IP_ADDRESS) max_client_ip_,
MIN (STAGE_LOG_DATETIME) min_stage_log_datetime_,
MAX (STAGE_LOG_DATETIME) max_stage_log_datetime_,
MAX (STAGE_COOKIE_USERID) user_id,
MAX (STAGE_STORE_CODE) stage_store_code,
SUM (STAGE_BYTES_RECEIVED) sum_stage_bytes_received_,
SUM (STAGE_BYTES_SENT) sum_stage_bytes_sent_,
COUNT (ORDER_ID) num_orders,
MAX (ORDER_ID) order_id,
sum (decode (stage_target_file, ‘/product/index.jsp’, 1, 0))
num_product_pages,
sum (decode (stage_target_file, ‘/category/index.jsp’, 1, 0))
num_category_pages,
sum (decode (stage_target_file, ‘/shop/index.jsp’, 1, 0)) num_shop_pages,
sum (decode (stage_target_file, ‘/info/index.jsp’, 1, 0)) num_info_pages,
sum (decode (stage_target_file, ‘/family/index.jsp’, 1, 0))
num_family_pages,
sum (decode (stage_target_file, ‘/home/index.jsp’, 1, 0)) num_home_pages,
max (decode (substr (stage_target_file, 1, 5), ‘/cart’, 1, 0))
add_to_cart_session,
sum (decode (substr (stage_target_file, 1, 5), ‘/cart’, 1, 0))
num_add_to_carts,
sum (decode (stage_target_file, ‘/searchhandler/index.jsp’, 1, 0))
num_searches
FROM
STAGE_CC_WEBLOG
WHERE
STAGE_LOG_DATE BETWEEN TO_DATE (’06-05-2006′, ‘MM-DD-YYYY’) AND TO_DATE
(’06-05-2006′, ‘MM-DD-YYYY’)
AND
STAGE_STORE_CODE != ‘-1’
GROUP BY
STAGE_LOG_DATE,
STAGE_SESSION_ID;

Plan Table
============
————————————————-+—————————-

| Id | Operation | Name | Rows | Bytes | Cost |
Time | Pstart| Pstop |
————————————————-+—————————-

| 0 | SELECT STATEMENT | | | | 29K |
| | |
| 1 | PARTITION RANGE SINGLE | | 1 | 182 | 29K |
00:05:20 | 37 | 37 |
| 2 | HASH GROUP BY | | 1 | 182 | 29K |
00:05:20 | | |
| 3 | TABLE ACCESS FULL | STAGE_CC_WEBLOG| 1 | 182 | 29K |
00:05:20 | 37 | 37 |
————————————————-+—————————-

WORKAROUND:
———–
Disable hash_aggregation feature by using

“_gby_hash_aggregation_enabled”=false

RELATED BUGS:
————-
Bug.5041616 (33)QUERY ABORTS ON HASH GROUP BY ORA-600 32695 P226. V1020:
Bug.5244226 (11)ORA-600 [32695], [HASH AGGREGATION CAN’T BE DONE] P46. V11:

REPRODUCIBILITY:
—————-
Reproducable

TEST CASE:
———-

STACK TRACE:
————
ksesic1 qeshPartitionBuildH qeshGBYOpenScan2 qeshGBYOpenScan qerghFetch
qergiFetch opifch2 opiall0 opial7 opiodr ttcpip

回复
admin says

2010/08/04 at 19:04

A SR about Performance issue after upgrade to 10gR2:
Q:We are facing one issue that after setting the following parameters few queries are working fine but few are getting hang. So we have to specify hint in the query as
SELECT /*+ opt_param(‘_gby_hash_aggregation_enabled’,’true’) opt_param(‘_optimizer_cost_based_transformation’,’on’) */ …

_gby_hash_aggregation_enabled boolean FALSE
_optimizer_cost_based_transformation string off

So just wanted to know why Oracle is behaving like this in 10.2.0.4 while it was working fine in 9i .Is it normal behaviour or there is any bug in 10gR2 and any way out to fix it permanently

A:He is seeing better performance for few queries if below parameter is set. But this has impact on other queries

_gby_hash_aggregation_enabled boolean FALSE
_optimizer_cost_based_transformation string off

They also tried setting the optimizer_features_enable to 9i , but it still doesn’t help much
Could you please follow the below action plan

Session 1
—————-
sqlplus user/password
select distinct sid from v$mystat;
alter session set max_dump_file_size=unlimited;
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set tracefile_identifier=’10046′;
alter session set events ‘10046 trace name context forever, level 12’;
Run your sql query
select * from dual;
alter session set events ‘10046 trace name context off’;

Session 2
—————
Please upload the query plan using below method

select sql_id,child_number from v$sql where sql_text like ‘%part of your sql%’;
select * from table(dbms_xplan.display_cursor(‘));

If you observe hung situation, please take multiple error stack

Session 3
—————-
select spid from v$process where addr in (select paddr from v$session where sid=);
oradebug setospid oradebug dump errorstack 4;
Wait for 1 minute
oradebug dump errorstack 4;
Wait for 1 minute
oradebug dump errorstack 4;

A:Name
—-
10046 trace

Comments
——–
10046 trace for the bad sql

FileName
——–
prwl011_ora_4119_10046.trc

FileComment
———–
SELECT
SUB_REGION_CODES_H2.SUB_REGION_CD,
BATCH_INFO.BATCH_NUM,
…
AND SUB_REGION_CODES.SUB_REGION_CD = (‘BR’)
AND BATCH_STATUS_CODES.STATUS_DESC IN (‘Closed Modifiable’, ‘Open’)
);

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 410.97 417.53 1560 8517946 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 3 410.97 417.53 1560 8517946 0 0

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 36

Rows Row Source Operation
——- —————————————————
175 COUNT STOPKEY (cr=875 pr=281 pw=0 time=72367 us)
175 TABLE ACCESS BY INDEX ROWID CLAIM_REPAIRS (cr=875 pr=281 pw=0 time=71303 us)
175 INDEX RANGE SCAN IND_CLAIM_REPAIRS_MCREF (cr=700 pr=148 pw=0 time=49209 us)(object id 26455)
0 COUNT STOPKEY (cr=880 pr=4 pw=0 time=6008 us)
0 TABLE ACCESS BY INDEX ROWID CLAIM_REPAIRS (cr=880 pr=4 pw=0 time=5581 us)
525 INDEX RANGE SCAN IND_CLAIM_REPAIRS_MCREF (cr=702 pr=2 pw=0 time=3617 us)(object id 26455)
0 HASH JOIN (cr=0 pr=0 pw=0 time=11 us)
6557 TABLE ACCESS FULL CURR_EXCHG_RT (cr=92 pr=90 pw=0 time=23931 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=13 us)
182 NESTED LOOPS OUTER (cr=19969 pr=1126 pw=0 time=2039494 us)
175 NESTED LOOPS OUTER (cr=19441 pr=1007 pw=0 time=1996510 us)
175 NESTED LOOPS (cr=17334 pr=673 pw=0 time=1867771 us)
64 HASH JOIN (cr=17039 pr=539 pw=0 time=288466 us)
2 TABLE ACCESS FULL BATCH_STATUS_CODES (cr=3 pr=0 pw=0 time=68 us)
12598 TABLE ACCESS BY INDEX ROWID BATCH_INFO (cr=17036 pr=539 pw=0 time=262123 us)
13136 NESTED LOOPS (cr=2811 pr=453 pw=0 time=121234 us)
537 NESTED LOOPS (cr=1655 pr=379 pw=0 time=76260 us)
537 NESTED LOOPS (cr=1653 pr=379 pw=0 time=74104 us)
537 NESTED LOOPS (cr=1114 pr=379 pw=0 time=69257 us)
537 NESTED LOOPS (cr=575 pr=379 pw=0 time=64401 us)
19 NESTED LOOPS (cr=397 pr=379 pw=0 time=59669 us)
1 NESTED LOOPS (cr=389 pr=379 pw=0 time=59426 us)
1 NESTED LOOPS (cr=3 pr=0 pw=0 time=72 us)
1 TABLE ACCESS BY INDEX ROWID SUB_REGION_CODES (cr=2 pr=0 pw=0 time=56 us)
1 INDEX UNIQUE SCAN SUB_REGION_CODES_PK (cr=1 pr=0 pw=0 time=31 us)(object id 25845)
1 INDEX UNIQUE SCAN REGION_CODES_PK (cr=1 pr=0 pw=0 time=12 us)(object id 25843)
1 TABLE ACCESS FULL CNTRY_CODES (cr=386 pr=379 pw=0 time=59349 us)
19 TABLE ACCESS BY INDEX ROWID SPROF_CODES (cr=8 pr=0 pw=0 time=220 us)
19 INDEX RANGE SCAN IND_SPROF_CNTRY (cr=1 pr=0 pw=0 time=62 us)(object id 26466)
537 TABLE ACCESS BY INDEX ROWID MASC_CODES (cr=178 pr=0 pw=0 time=4127 us)
537 INDEX RANGE SCAN MASC_CODES_IND_SPROF_CD (cr=22 pr=0 pw=0 time=876 us)(object id 4777911)
537 TABLE ACCESS BY INDEX ROWID AREA_CODES (cr=539 pr=0 pw=0 time=4667 us)
537 INDEX UNIQUE SCAN AREA_CODES_PK (cr=2 pr=0 pw=0 time=1789 us)(object id 25849)
537 TABLE ACCESS BY INDEX ROWID CNTRY_CODES (cr=539 pr=0 pw=0 time=4309 us)
537 INDEX UNIQUE SCAN CNTRY_CODES_PK (cr=2 pr=0 pw=0 time=1513 us)(object id 25847)
537 INDEX UNIQUE SCAN SUB_REGION_CODES_PK (cr=2 pr=0 pw=0 time=1372 us)(object id 25845)
12598 INDEX RANGE SCAN BATCH_INFO_IND_MASC_CD (cr=1156 pr=74 pw=0 time=35557 us)(object id 25884)
175 TABLE ACCESS BY INDEX ROWID CLAIMS (cr=295 pr=134 pw=0 time=1579116 us)
175 INDEX RANGE SCAN CLAIMS_IND_BATCH_NUM (cr=131 pr=0 pw=0 time=1271 us)(object id 25912)
175 VIEW PUSHED PREDICATE PRIMARY_SECONDARY_REPAIRS_V (cr=2107 pr=334 pw=0 time=128287 us)
175 INDEX UNIQUE SCAN CLAIMS_PK (cr=352 pr=49 pw=0 time=48072 us)(object id 25911)
182 TABLE ACCESS BY INDEX ROWID CLAIMS_RULES_FAIL_STATUS (cr=528 pr=119 pw=0 time=42712 us)
182 INDEX RANGE SCAN CLAIMS_RULES_FAIL_STATUS_PK (cr=352 pr=74 pw=0 time=34581 us)(object id 84966)
111573 VIEW BATCH_CURR_CONV_V (cr=8485019 pr=344 pw=0 time=414677497 us)
111573 UNION-ALL (cr=8485019 pr=344 pw=0 time=414677492 us)
109213 HASH JOIN (cr=3747 pr=279 pw=0 time=517732 us)
138811 TABLE ACCESS FULL BATCH_INFO (cr=2998 pr=279 pw=0 time=66 us)
150505 MAT_VIEW ACCESS FULL BATCH_STATUS_HIST_MV (cr=749 pr=0 pw=0 time=131 us)
2360 FILTER (cr=8481272 pr=65 pw=0 time=414072814 us)
16007 TABLE ACCESS FULL BATCH_INFO (cr=371 pr=65 pw=0 time=80121 us)
13646 MAT_VIEW ACCESS FULL BATCH_STATUS_HIST_MV (cr=8493417 pr=0 pw=0 time=414414603 us)

Based on the analysis of 10046 trace, it is seen that sql takes more time on accessing the the mview.

Did the same sql work fine previously?
If yes, do you have the execution plan for that sql in 9i?

Please set the following parameter and check:-

alter session set “_optimizer_cost_model”=IO;
alter session set optimizer_features_enable=”10.1.0.5”;
alter session set “_optimizer_undo_cost_change”=”10.1.0.5”;
Now flush the shared pool.
alter system flush shared_pool;

Now execute the sql and check the behaviour.

If the above does not work out then please set the following parameter and check:-

alter session set QUERY_REWRITE_INTEGRITY=stale_tolerated;
Now flush the shared pool.
alter system flush shared_pool;

Now execute the sql and check the behaviour.

If both the options do not work then please generate the 10053 trace for the sql after reverting all the session level parameter settings. You can open a new session so that the parameter settings would be reverted.

10053 trace files:
~~~~~~~~~~~~~~~~~~
To gather 10053 trace at session level:

SQL>alter session set max_dump_file_size = unlimited;
SQL>alter session set events ‘10053 trace name context forever, level 1’;
SQL>
SQL>alter session set events ‘10053 trace name context off’;

The files would be generated under user_dump_dest. Please upload.

Q:After setting the option 1
alter session set “_optimizer_cost_model”=IO;
alter session set optimizer_features_enable=”10.1.0.5”;
alter session set “_optimizer_undo_cost_change”=”10.1.0.5”;
Now flush the shared pool.
alter system flush shared_pool;

The query is working fine but when I tried another query after that it is still getting hang so I am sending trace for that

A:Name
—-
10046 trace

Comments
——–
Tkprof of 10046

FileName
——–
trwl011_ora_24668_10046.out

FileComment
———–
SELECT c.imei_in AS serial_in,
c.imei_out AS serial_out,
c.imei_in_type AS serial_in_type,
…
NVL(c.serial_out_flag, ‘V’) = ‘V’ AND esn_csn_in_type <> ‘OTHERS’ AND
esn_csn_out_type <> ‘OTHERS’ AND c.claim_status = ‘A’ AND
c.pop_dt IS NOT NULL AND c.pop_flag = ‘Y’;

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.04 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 93.15 162.05 611351 716824 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 3 93.19 162.08 611351 716824 0 0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 36

Rows Row Source Operation
——- —————————————————
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=50 us)
0 UNION-ALL (cr=0 pr=0 pw=0 time=7 us)
0 NESTED LOOPS (cr=3 pr=2 pw=0 time=334 us)
0 TABLE ACCESS BY INDEX ROWID CLAIMS (cr=3 pr=2 pw=0 time=330 us)
0 INDEX RANGE SCAN IND_CLAIMS_CREATED_DT (cr=3 pr=2 pw=0 time=327 us)(object id 81462)
0 INDEX UNIQUE SCAN TRANS_CODES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 26099)
0 NESTED LOOPS (cr=3 pr=0 pw=0 time=16 us)
0 TABLE ACCESS BY INDEX ROWID CLAIMS (cr=3 pr=0 pw=0 time=15 us)
0 INDEX RANGE SCAN IND_CLAIMS_CREATED_DT (cr=3 pr=0 pw=0 time=14 us)(object id 81462)
0 TABLE ACCESS BY INDEX ROWID TRANS_CODES (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN TRANS_CODES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 26099)
0 FILTER (cr=0 pr=0 pw=0 time=25 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=25 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=5 us)
913671 TABLE ACCESS BY INDEX ROWID CLAIMS (cr=716813 pr=611344 pw=0 time=159896958 us)
5182392 INDEX RANGE SCAN IND_CLAIM_STATUS (cr=9837 pr=8264 pw=0 time=4467 us)(object id 26922)
0 INDEX FULL SCAN BATCH_STATUS_HIST_PK (cr=0 pr=0 pw=0 time=0 us)(object id 25905)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 MERGE JOIN (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FULL SCAN BATCH_STATUS_HIST_PK (cr=0 pr=0 pw=0 time=0 us)(object id 25905)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID CLAIMS (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN IND_CLAIM_STATUS (cr=0 pr=0 pw=0 time=0 us)(object id 26922)

Based on the analysis of the trace, it is seen that more amount of time is spent on accessing the index on the CLAIMS table.
It could be due to the optimizer’s inefficient selection of the indexes due to inaccurate statistics.
Please do delete and recollect the statistics for the table as follows:-

dbms_stats.delete_table_stats(ownname=>’user_name’,tabname=>’table_name’,cascade_indexes=>true);
exec dbms_stats.gather_table_stats(ownname=>’user_name’,tabname=>’table_name’,cascade=>true,method_opt=>’for all columns size skewonly’);

Now execute the sql and check.

Q:I had gather stats with 10% and then tried the same query which completed in 15 mins with hint and without hint it took 1 hour approx. after setting parameters told by you

46739 rows selected.

Elapsed: 01:08:28.34

A:Please get the 10046 and 10053 trace for the sql with and without the hint so that we could investigate further.

Before getting the 10046 and 10053 trace for the sql with and without the hint, please try using sql tuning advisor for the sql.
SQL profile is the new feature introduced in 10g which would tune the SQL internally and suggest the recommendations.
Consider accpeting the recommendations by accepting the profile.

1. Create tuning task
~~~~~~~~~~~~~~~~~~~~~

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘Your_sql_statement_without_semicolon’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=> my_sqltext,
user_name => ‘SCOTT’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task_2’,
description => ‘Task to tune a query on a specified table’);
END;
/

PL/SQL procedure successfully completed.

2. Execute tuning task
~~~~~~~~~~~~~~~~~~~~~~

1 BEGIN
2DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_2’);
3* end;
SQL> /

PL/SQL procedure successfully completed.

3. Reporting tuning task
~~~~~~~~~~~~~~~~~~~~~~~~

1 SET LONG 1000
2 SET LONGCHUNKSIZE 1000
3 SET LINESIZE 100
4* SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_2’) from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
——————————————————————————–
——————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : my_sql_tuning_task_2
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/04/2004 17:36:05
Completed at : 05/04/2004 17:36:05

——————————————————————————-
SQL ID : d4wgpc5g0s0vu

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
——————————————————————————–
——————–
SQL Text: select /*+ no_index(test test_idx) */ * from test where n=1

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 83.84%)

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘MY_SQL_TUNING_TASK_2’)
——————————————————————————–
——————–
——————————————
Consider accepting the recommended

4. Accepting the Profile
~~~~~~~~~~~~~~~~~~~~~~~~

1 DECLARE
2 my_sqlprofile_name VARCHAR2(30);
3 BEGIN
4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => ‘my_sql_tuning_task_2’,
6 name => ‘my_sql_profile’);
7* END;
8 /

PL/SQL procedure successfully completed.

Note.271196.1 RDBMS V10G Ext/Pub Automatic SQL Tuning – SQL Profiles
After accepting the profile for the SQL, now execute the SQL and check the behaviour.

if the sql does not run faster even after accepting the profiles then generate the traces and upload.
Before generating the traces, please try using sql tuning advisor for the sql. Please refer the update in SR for using sql tuning advisor.

Q:Getting error

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘SELECT Imei_in AS Serial_Number,
Imei_in_type AS Serial_Number_Type,
Max(to_char(Repair_dt,’YYYY_MM_DD’)) as Repair_Date,
Count(Imei_in) AS Repair_count
FROM claims Cl
WHERE imei_in IS NOT NULL
AND cl.created_dt >= to_date(‘2009-03-05’, ‘yyyy-mm-dd’)
AND cl.created_dt <= to_date('2009-05-05', 'yyyy-mm-dd') AND EXISTS (SELECT TC.trans_cd FROM TRANS_CODES TC WHERE TC.trans_cd = Cl.trans_cd AND TC.service_repair = 'R' AND TC.serial_no_trans = 'Y') AND claim_status IN ('A', 'R', 'W') AND duplicate_claim_YN = 'N' AND resubmitted_claims_YN = 'N' AND serial_in_flag IN ('V', 'I') AND imei_in_type <> ‘OTHERS’
Group by Imei_in, Imei_in_type
UNION
SELECT Esn_csn_in AS Serial_Number,
esn_csn_in_type AS Serial_Number_Type,
Max(to_char(Repair_dt,’YYYY_MM_DD’)) as Repair_Date,
Count(Esn_csn_in) AS Repair_count
FROM claims Cl
WHERE Esn_csn_in IS NOT NULL
AND cl.created_dt >= to_date(‘2009-03-05’, ‘yyyy-mm-dd’)
AND cl.created_dt <= to_date('2009-05-05', 'yyyy-mm-dd') AND EXISTS (SELECT TC.trans_cd FROM TRANS_CODES TC WHERE TC.trans_cd = Cl.trans_cd AND TC.service_repair = 'R' AND TC.serial_no_trans = 'Y') AND claim_status IN ('A', 'R', 'W') AND duplicate_claim_YN = 'N' AND resubmitted_claims_YN = 'N' AND serial_in_flag IN ('V', 'I') AND esn_csn_in_type <> ‘OTHERS’
Group by Esn_csn_in, esn_csn_in_type
Order by 1, 2′;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=> my_sqltext,
user_name => ‘MCLAIMSPROD’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task_2’,
description => ‘Task to tune a query on a specified table’);
END;
/

ERROR at line 7:
ORA-06550: line 7, column 31:
PLS-00103: Encountered the symbol “YYYY_MM_DD” when expecting one of the
following:
* & = – + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

When I tried with other query it is thrwoing error
SP2-0552: Bind variable “MI” not declared.

A:The error with the first sql is due to the presence of single quotes ( ‘ ) involved in the sql statement.
You need to one more single quotes to the existing single quotes where ever found in the sql.
Because my_sqltext variable gets the sql statement which is specified within the single quotes. So the quotes involved in the sql for date conversion or any datatypes would get conflicted with the delimiter and causes this error. So please add one more quotes to the existing quotes.

Ex:

my_sqltext := ‘SELECT * from emp where ename=”KING” and sal>1000’;
Here, ename is varchar variable and we need to pass the value in single quotes, to avoid the conflicts, i added two single quotes.

Regarding the error for the second query, the query is using bind variables and hence the error.
For queries using bind variables, you need to get the sql_id for the sql statement and then use sql tuning advisor.

select sql_id from v$sql where sql_text like ‘select statement%’;

Use the sql_id to generate advisory:-

Login as SYS and run the following commands

===== Begin script =====
variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);

EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( –
sql_id => ‘6zn5rha77wbmp’, –
task_name => ‘sql_tuning_task1’,-
description => ‘Tune my query’);

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘sql_tuning_task1’);

SET LONG 1000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘sql_tuning_task1’) from DUAL;

DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘sql_tuning_task1’,
name => ‘my_sql_profile’);
END;
/

PL/SQL procedure successfully completed.

Using this, you can create profiles for the sqls involving bind variables.

If you have already created profiles using OEM, then try executing the sql and check whether the performance improved after accepting the recommendations.

Q:Execution Plan
———————————————————-

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————————-
| 0 | SELECT STATEMENT | | 148 | 8180 | 8487 (1)|
| 1 | SORT UNIQUE | | 148 | 8180 | 8486 (51)|
| 2 | UNION-ALL | | | | |
| 3 | SORT GROUP BY | | 139 | 7784 | 4243 (1)|
|* 4 | TABLE ACCESS BY INDEX ROWID| CLAIMS | 2375 | 106K| 4236 (1)|
| 5 | NESTED LOOPS | | 139 | 7784 | 4241 (1)|
| 6 | SORT UNIQUE | | 1 | 10 | 4 (0)|
|* 7 | TABLE ACCESS FULL | TRANS_CODES | 1 | 10 | 4 (0)|
|* 8 | INDEX RANGE SCAN | CLAIMS_IND_TRANS_CD | 156K| | 67 (0)|
| 9 | SORT GROUP BY | | 9 | 396 | 4243 (1)|
|* 10 | TABLE ACCESS BY INDEX ROWID| CLAIMS | 162 | 5508 | 4236 (1)|
| 11 | NESTED LOOPS | | 9 | 396 | 4241 (1)|
| 12 | SORT UNIQUE | | 1 | 10 | 4 (0)|
|* 13 | TABLE ACCESS FULL | TRANS_CODES | 1 | 10 | 4 (0)|
|* 14 | INDEX RANGE SCAN | CLAIMS_IND_TRANS_CD | 156K| | 67 (0)|
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

4 – filter(“CL”.”CREATED_DT”>=TO_DATE(‘ 2009-03-05 00:00:00’, ‘syyyy-mm-dd
hh24:mi:ss’) AND “IMEI_IN” IS NOT NULL AND “IMEI_IN_TYPE”<>‘OTHERS’ AND
“CL”.”CREATED_DT”<=TO_DATE(' 2009-05-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RESUBMITTED_CLAIMS_YN"='N' AND "DUPLICATE_CLAIM_YN"='N' AND ("SERIAL_IN_FLAG"='I' OR "SERIAL_IN_FLAG"='V') AND ("CLAIM_STATUS"='A' OR "CLAIM_STATUS"='R' OR "CLAIM_STATUS"='W')) 7 - filter("TC"."SERVICE_REPAIR"='R' AND "TC"."SERIAL_NO_TRANS"='Y') 8 - access("TC"."TRANS_CD"="CL"."TRANS_CD") 10 - filter("ESN_CSN_IN" IS NOT NULL AND "CL"."CREATED_DT">=TO_DATE(‘ 2009-03-05
00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) AND “CL”.”CREATED_DT”<=TO_DATE(' 2009-05-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RESUBMITTED_CLAIMS_YN"='N' AND "DUPLICATE_CLAIM_YN"='N' AND ("SERIAL_IN_FLAG"='I' OR "SERIAL_IN_FLAG"='V') AND "ESN_CSN_IN_TYPE"<>‘OTHERS’ AND (“CLAIM_STATUS”=’A’ OR “CLAIM_STATUS”=’R’ OR
“CLAIM_STATUS”=’W’))
13 – filter(“TC”.”SERVICE_REPAIR”=’R’ AND “TC”.”SERIAL_NO_TRANS”=’Y’)
14 – access(“TC”.”TRANS_CD”=”CL”.”TRANS_CD”)

Note
—–
– ‘PLAN_TABLE’ is old version

A:Based on the OWC, it is seen that the sql profile is giving better performance for the sql.
This indicates that there exists some statistics miscalculation and thats why the profile is giving better plan.

Since you are using sql with bind variables, you need to create the different sql profiles for the sql with different values passed for bind variables. If it is acceptable for you then you can go ahead and create profiles for the sql statements involved.

Otherwise, you can try using dynamic sampling.

Using dynamic sampling, the optimizer determines the selectivity and cardinality estimates based on the single table predicates and selects the better execution plan for the statement.

set the following parameter to increase the sampling level.

alter system set optimizer_dynamic_sampling=4;

Now execute the query and check the performance.

It is always better to have accurate statistics for the optimizer to decide on better plans. So please collect the statistics regularly using the command given in earlier updates.

Also change the optimizer_mode to ALL_ROWS and optimizer_secure_view_merging to false.

Please do the above changes and check the behaviour.

回复

10g中HASH GROUP BY引起的临时表空间不足

Comments

Comment 取消回复