Страницы

суббота, 23 августа 2014 г.

event 10505 

[oracle@oracle ~]$ oerr ora 10505
10505, 00000, "CBO enable dynamic sampling dump to table"
// *Cause:
// *Action:
Event 10505 can be used to dump dynamic sampling data to table. Same information can be obtained from event 10053 trace files. This event requires additional tables:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- general table and dynamic sampling stats
create table kkedsamp_table(
  table_name varchar2(30),
  dyn_sampling_level number,
  c3 number, -- unknown
  c4 number, -- unknown
  c5 number, -- unknown
  c6 number, -- unknown
  c7 number, -- unknown
  single_table_dyn_sel_est number,
  dynamic_sampling_card number,
  sample_pct number,
  c11 number, -- unknown, partitioning
  c12 number, -- unknown, partitioning
  c13 number, -- unknown, partitioning
  c14 number, -- unknown
  actual_sample_size number,
  filtered_sample_card number,
  orig_card number,
  block_cnt_for_sampling_tabstat number,
  c19 number,
  max_sample_block_cnt number,
  sample_block_cnt number,
  min_sel_est number);
-- column stats
create table kkedsamp_column(
  column_name varchar2(30),
  table_name varchar2(30),
  c3 number, -- unknown
  num_distinct number,
  num_distinct_scaled number,
  num_nulls number,
  num_nulls_scaled number);
-- index stats
create table kkedsamp_index(
  index_name varchar2(30),
  table_name varchar2(30),
  c3 number, -- unknown
  index_selectity_est number,
  min_sel_est number,
  c6 number, -- unknown
  num_blocks number);
Test case (run on 11.2.0.3):
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
SQL> create table t1 as select * from dba_objects;
 
Table created.
SQL> create table t2 as select * from dba_objects;
 
Table created.
SQL> create index t1_subobject_name_i on t1(subobject_name);
 
Index created.
SQL> exec dbms_stats.delete_index_stats( '', 'T1_SUBOBJECT_NAME_I')
 
PL/SQL procedure successfully completed.
SQL> alter session set events '10505';
 
Session altered.
SQL> select /*+ dynamic_sampling(4)*/
  2         count(*)
  3    from t1, t2
  4   where t1.owner='SYSTEM'
  5     and t2.subobject_name=t1.subobject_name
  6     and t2.object_type like 'TABLE%';
 
  COUNT(*)
----------
      3425
SQL> alter session set events '10505 off';
 
Session altered.
SQL> @pt "select * from kkedsamp_table"
TABLE_NAME                    : "T1"
DYN_SAMPLING_LEVEL            : 4
C3                            : 1
C4                            : 1
C5                            : 1
C6                            : 1
C7                            : 1
SINGLE_TABLE_DYN_SEL_EST      : .060506329113924
DYNAMIC_SAMPLING_CARD         : 12665.0793650794
SAMPLE_PCT                    : 31.1881188118812
C11                           : 1
C12                           : 1
C13                           : 1
C14                           : 1
ACTUAL_SAMPLE_SIZE            : 3950
FILTERED_SAMPLE_CARD          : 239
ORIG_CARD                     : 16500
BLOCK_CNT_FOR_SAMPLING_TABSTAT: 202
C19                           : 0
MAX_SAMPLE_BLOCK_CNT          : 64
SAMPLE_BLOCK_CNT              : 63
MIN_SEL_EST                   : .01
-----------------
TABLE_NAME                    : "T2"
DYN_SAMPLING_LEVEL            : 4
C3                            : 1
C4                            : 1
C5                            : 1
C6                            : 1
C7                            : 0
SINGLE_TABLE_DYN_SEL_EST      : .135717785399314
DYNAMIC_SAMPLING_CARD         : 13088.3174603175
SAMPLE_PCT                    : 31.1881188118812
C11                           : 1
C12                           : 1
C13                           : 1
C14                           : 1
ACTUAL_SAMPLE_SIZE            : 4082
FILTERED_SAMPLE_CARD          : 554
ORIG_CARD                     : 16500
BLOCK_CNT_FOR_SAMPLING_TABSTAT: 202
C19                           : 0
MAX_SAMPLE_BLOCK_CNT          : 64
SAMPLE_BLOCK_CNT              : 63
MIN_SEL_EST                   : .05
-----------------
 
PL/SQL procedure successfully completed.
SQL> @pt "select * from kkedsamp_column"
COLUMN_NAME                   : "T1"."SUBOBJECT_NAME"
TABLE_NAME                    : "T1"
C3                            : 3
NUM_DISTINCT                  : 160
NUM_DISTINCT_SCALED           : 161
NUM_NULLS                     : 3699
NUM_NULLS_SCALED              : 11860.2857142857
-----------------
COLUMN_NAME                   : "T2"."SUBOBJECT_NAME"
TABLE_NAME                    : "T2"
C3                            : 3
NUM_DISTINCT                  : 66
NUM_DISTINCT_SCALED           : 66
NUM_NULLS                     : 3997
NUM_NULLS_SCALED              : 12815.7777777778
-----------------
 
PL/SQL procedure successfully completed.
SQL> @pt "select * from kkedsamp_index"
INDEX_NAME                    : T1_SUBOBJECT_NAME_I
TABLE_NAME                    : "T1"
C3                            : 0
INDEX_SELECTITY_EST           : -1
MIN_SEL_EST                   : -1
C6                            : 1
NUM_BLOCKS                    : 8
-----------------
PL/SQL procedure successfully completed.
Автор: на Комментариев нет:
Ярлыки:

воскресенье, 3 августа 2014 г.

JPPD bypassed View has non-standard group by

В одной из БД Hibernate сгенерировал новый запрос, который вызвал повышенную загрузку. Я исследовал этот запрос, оптимизируется он легко, но я выявил один интересный случай, когда JPPD (join-predicate push-down) не срабатывает для group by view. Test case (11.2.0.3):
?
1
2
3
4
5
6
7
8
9
10
drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;
create table t1(x,y) as select rownum, rownum from dual connect by level<=1e4;
alter table t1 modify x not null;
create index t1_y_i on t1(y);
create table t2(x,z) as select rownum, cast(dummy as char(20)) from dual connect by level<=1e5;
alter table t2 modify x not null;
create index t2_x_i on t2(x);
exec dbms_stats.gather_table_stats( '', 't1')
exec dbms_stats.gather_table_stats( '', 't2')
Hibernate генерировал следующий запрос:
?
1
2
3
4
select *
  from t1
 where y = :1
   and x not in (select x from t2 group by x);
С планом:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |     8 |    85  (25)| 00:00:02 |
|*  1 |  FILTER                      |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_Y_I |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   FILTER                     |        |       |       |            |          |
|   5 |    HASH GROUP BY             |        |     1 |     5 |    83  (26)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN     | T2_X_I |   100K|   488K|    65   (5)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" GROUP BY "X" HAVING
              "X"=:B1))
   3 - access("Y"=TO_NUMBER(:1))
   4 - filter("X"=:B1)
Условие по T1 (Y=:1) - очень селективное. Как видно, subquery unnesting не срабатывает, а хотелось бы видеть анти-соединение, использующее INDEX RANGE SCAN индекса T2_X_I (индекс по T2(X)). Рассмотрим эквивалентный запрос:
?
1
2
3
4
select *
  from t1
 where y = :1
   and x not in (select x from t2);
Его план, который бы хотелось получить для исходного запроса:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI           |        |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_Y_I |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | T2_X_I |   100K|   488K|     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("Y"=TO_NUMBER(:1))
   4 - access("X"="X")
Как еще можно переписать запрос? По логике, нужно выбрать строки из T1, которых нет в T2. Такой запрос с Oracle синтаксисом Join:
?
1
2
3
4
5
6
7
8
select t1.*
  from t1,
       (select x
          from t2
         group by x) t2
 where y = :1
   and t1.x = t2.x(+)
   and t2.x is null;
Имеет план:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    21 |       |   233   (4)| 00:00:03 |
|*  1 |  HASH JOIN ANTI              |        |     1 |    21 |       |   233   (4)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     1 |     8 |       |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_Y_I |     1 |       |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       |        |   100K|  1269K|       |   228   (3)| 00:00:03 |
|   5 |    HASH GROUP BY             |        |   100K|   488K|  1192K|   228   (3)| 00:00:03 |
|   6 |     INDEX FULL SCAN          | T2_X_I |   100K|   488K|       |   228   (3)| 00:00:03 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."X"="T2"."X")
   3 - access("Y"=TO_NUMBER(:1))
Мы видим, что происходит HASH JOIN ANTI (анти-соединение) без JPPD. Т.е. предикат соединения T1.X=T2.X - не был протолкнут внутрь GROUP BY VIEW по T2. Выполнить проталкивание хинтами не получилось, что говорит о том, что такое VIEW для JPPD не подходит. Пришлось обратиться к трассировке CBO в поисках причин.
?
1
2
3
4
JPPD:     JPPD bypassed: View has non-standard group by.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$6D455ABB (#1)
JPPD:   No valid views found to push predicate into.
Вот это уже информация для анализа. Мы видим, что JPPD не сработал, т.к. View: "has non-standard group by". Подумав, я написал следующий запрос:
?
1
2
3
4
5
6
7
8
9
select t1.*
  from t1,
       (select x,
               count(*)
          from t2
         group by x) t2
 where y=:1
   and t1.x = t2.x(+)
   and t2.x is null;
Его план:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI           |        |     1 |    10 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_Y_I |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE      |        |     1 |     2 |     1   (0)| 00:00:01 |
|   5 |    SORT GROUP BY             |        |     1 |     5 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN         | T2_X_I |     1 |     5 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("Y"=TO_NUMBER(:1))
   6 - access("X"="T1"."X")
По сути дела, мы запрос:
?
1
2
3
4
5
6
7
8
select t1.*
  from t1,
       (select x
          from t2
         group by x) t2
 where y = :1
   and t1.x = t2.x(+)
   and t2.x is null;
Переписали в эквивалентный:
?
1
2
3
4
5
6
7
8
9
select t1.*
  from t1,
       (select x,
               count(*)
          from t2
         group by x) t2
 where y=:1
   and t1.x = t2.x(+)
   and t2.x is null;
И это позволило выполнить JPPD, что видно в плане и в трассе CBO:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$6D455ABB (#1)
JPPD:   Checking validity of push-down from query block SEL$6D455ABB (#1) to query block SEL$2 (#2)
Check Basic Validity for Non-Union View for query block SEL$2 (#2)
JPPD:     Passed validity checks
JPPD: JPPD:   Pushdown from query block SEL$6D455ABB (#1) passed validity checks.
Join-Predicate push-down on query block SEL$6D455ABB (#1)
JPPD: Using search type: linear
JPPD: Considering join predicate push-down
JPPD: Starting iteration 1, state space = (2) : (0)
JPPD: Performing join predicate push-down (no transformation phase) from query block SEL$6D455ABB (#1) to query block SEL$2 (#2)
Пока это похоже на существующее ограничение CBO. Указанное поведение проверялось в 11.2.0.3 и 12.1.0.1.
Автор: на Комментариев нет:
Ярлыки: , ,

event 10384

event 10384 level 16384: force parallel query runs as serial. How to Force that a Parallel Query Runs in Serial with the Parallel Execution Plan (Doc ID 1114405.1) 
[oracle@oracle]$ oerr ora 10384
10384, 00000, "parallel dataflow scheduler tracing"
// *Cause:
// *Action:   set this event only under the supervision of Oracle development
// *Comment:  trace level is a bitfield (see kkrp.h)
Test case (run on 11.2.0.3):
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
doc
  create test data
#
create table t as select * from dba_objects;
 
doc
  set statistics_level=all and run parallel query
#
 
alter session set statistics_level=all;
select /*+ parallel(t)*/count(*) from t;
select * from table(dbms_xplan.display_cursor( format=> 'allstats all'));
 
doc
  purge cursor
#
 
col address    new_v address
col hash_value new_v hash_value
select address, hash_value from v$sql where sql_id='apmsk02t1z90x';
exec sys.dbms_shared_pool.purge( '&address.,&hash_value.', 'c')
 
doc
  set event 10384 level 16384, _px_trace=all and run query
#
 
alter session set events '10384 level 16384';
alter session set "_px_trace"=all;
 
select /*+ parallel(t)*/count(*) from t;
select * from table(dbms_xplan.display_cursor( format=> 'allstats all'));
alter session set events '10384 off';
Row source execution statistics of first execution. Default degree 16, starts for 5th line is 16.
?
1
2
3
4
5
6
7
8
9
10
11
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |     4 (100)|          |        |      |            |      1 |00:00:00.59 |       5 |      0 |
|   1 |  SORT AGGREGATE        |          |      1 |      1 |            |          |        |      |            |      1 |00:00:00.59 |       5 |      0 |
|   2 |   PX COORDINATOR       |          |      1 |        |            |          |        |      |            |     16 |00:00:00.59 |       5 |      0 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      0 |      1 |            |          |  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |
|   4 |     SORT AGGREGATE     |          |     16 |      1 |            |          |  Q1,00 | PCWP |            |     16 |00:00:01.16 |     567 |    189 |
|   5 |      PX BLOCK ITERATOR |          |     16 |  12736 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |  14275 |00:00:01.16 |     567 |    189 |
|*  6 |       TABLE ACCESS FULL| T        |    189 |  12736 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |  14275 |00:00:01.14 |     567 |    189 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Row source execution statistics of second execution. Starts for 5th line is 1. Parallel plan executed serially.
?
1
2
3
4
5
6
7
8
9
10
11
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |     4 (100)|          |        |      |            |      1 |00:00:00.02 |     194 |     63 |
|   1 |  SORT AGGREGATE        |          |      1 |      1 |            |          |        |      |            |      1 |00:00:00.02 |     194 |     63 |
|   2 |   PX COORDINATOR       |          |      1 |        |            |          |        |      |            |      1 |00:00:00.02 |     194 |     63 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      1 |      1 |            |          |  Q1,00 | P->S | QC (RAND)  |      1 |00:00:00.02 |     194 |     63 |
|   4 |     SORT AGGREGATE     |          |      1 |      1 |            |          |  Q1,00 | PCWP |            |      1 |00:00:00.02 |     194 |     63 |
|   5 |      PX BLOCK ITERATOR |          |      1 |  12736 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |  14275 |00:00:00.02 |     194 |     63 |
|*  6 |       TABLE ACCESS FULL| T        |      1 |  12736 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |  14275 |00:00:00.02 |     194 |     63 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Trace file with event 10384 set contains a line: 
Parallelism disabled at runtime because forced serial by event 10384
Автор: на Комментариев нет:
Ярлыки:
Подписаться на: Сообщения (Atom)