Archive for March 2009

Is data in DBA_HIST_SQLTEXT aged out?

By | March 19th, 2009 | Category: 10g | 2 comments

This question came up during the excellent presentation of Dave Abercrombie – A Tour of the AWR Tables at the Hotsos Symposium 2009.

Nobody knew the answer but curious as I am, I wanted to know:

Where is the data stored?

SQL>  select owner, object_name, object_type from dba_objects where object_name = ‘DBA_HIST_SQLTEXT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SYS        DBA_HIST_SQLTEXT               VIEW
PUBLIC     DBA_HIST_SQLTEXT               SYNONYM

SQL> select text from dba_views where view_name = ‘DBA_HIST_SQLTEXT’;

TEXT
——————————————————————————–
select dbid, sql_id, sql_text, command_type
from WRH$_SQLTEXT

SQL> select owner, object_name, object_type, object_id, data_object_id  from dba_objects where object_name = ‘WRH$_SQLTEXT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
———- —————————— ——————- ———- ————–
SYS        WRH$_SQLTEXT                   TABLE                     8996           8996
=> Not partitioned.

SQL> select min(snap_id), max(snap_id) from wrh$_SQLTEXT;

MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
3040         3274

We could guess that snap_ids before 3040 were removed but I want to know. (BAAG)  I could monitor the table over a period of time and check min(snap_id) but I can also query the backed up object statistics, specifically the minimum value for column snap_id:

select to_char(savtime,’DD.MM.YYYY’), minimum, maximum, distcnt,  sample_size
from WRI$_OPTSTAT_HISTHEAD_HISTORY where obj# = 8996 and intcol# = 1;
2
TO_CHAR(SA    MINIMUM    MAXIMUM    DISTCNT SAMPLE_SIZE
———- ———- ———- ———- ———–
16.02.2009       2229       2461        114         455
17.02.2009       2296       2535        122         471
18.02.2009       2326       2559        114         469
19.02.2009       2346       2584        119         476
20.02.2009       2370       2607        108         448
21.02.2009       2394       2632        112         437
23.02.2009       2417       2640        104         412
24.02.2009       2471       2703        113         400
25.02.2009       2495       2728         94         394
26.02.2009       2516       2750        101         396
27.02.2009       2536       2776        103         322
28.02.2009       2560       2799         93         310
02.03.2009       2585       2807         88         303
03.03.2009       2637       2871         92         289
04.03.2009       2693       2895         85         287
05.03.2009       2693       2919         91         298
06.03.2009       2704       2943         86         294
09.03.2009       2730       2967         92         303
10.03.2009       2801       3039        104         303
11.03.2009       2836       3063         94         304
12.03.2009       2856       3087         99         303
13.03.2009       2872       3111        101         301
14.03.2009       2896       3135         99         299
16.03.2009       2921       3144         95         292
17.03.2009       2976       3206         94         292
18.03.2009       3003       3232         94         305

26 rows selected.

We can see that at the stats gathering during the last 26 days, every day, the min value increased. So, the answer is yes.



Autostart Oracle Services at Clusterware startup

By | March 19th, 2009 | Category: 10g, 11g, Real Application Clusters | No Comments »

I recently recommended the utilization of Oracle Services to a new client. I knew that you have to start a service manually if you restart your cluster. I told them that I will investigate on how to set the services to autostart. I knew that there is an AUTO_START property of the clusterware service resource set to “restore”, which means that at instance crash, Clusterware will restore the services to the status before crash. However, on manual shutdown/startup, the services will not be autostarted.

[root@ora-vm1 tmp]# crs_stat -p ora.MDDB.S_TEST.MDDB1.srv
NAME=ora.MDDB.S_TEST.MDDB1.srv
TYPE=application
AUTO_START=restore
…

I did some research and learned that the easiest way to configure autostart is with user callouts. A quick web search referred me to the OTN site “Real Application Clusters Sample Scripts” which contains a FAN callout script to autostart services.

The steps to install the scripts are:

  1. Copy them to $ORA_CRS_HOME/racg/usrco on both nodes
  2. Set permissions to 710 and ownership to oracle:oinstall or whatever your installation is using.
  3. Modify perl Scripts to contain your ORA_CRS_HOME, ORACLE_HOME. Moreover, if you have used short hostnames for your CRS installation, you have to change “/bin/hostname” to “/bin/hostname -s”.

I just realized that Dan Norris also blogged about this topic.


DataGuard Broker Setup fails because of Cisco Firewall ASA Feature “fixup/inspect”

By | March 16th, 2009 | Category: 10g, Data Guard | No Comments »

I ran into this problem a couple of years ago but I still think that it is worth blogging about. Back then, I tried to setup a data guard broker enviroment but when trying to enable the configuration on the dgmgrl command line, the dataguard configuration files could not be synced to the standby host. After a couple of days of troubleshooting, it turned out that the Cisco ASA Firewall hat the SQL*Net Inspection Feature enabled. But because the data guard broker data packets did not exactly conform to SQL*Net, the Firewall corrupted the packet and it was not able to create the data guard configuration. After disabling the SQL*Net inspection, it worked without any further problems.


Intelligent (aka adaptive) Cursor Sharing Problem

By | March 12th, 2009 | Category: 11g, Bugs, MetaLink, Oracle Database | 4 comments

Today, at the last day of Hotsos Symposium 2009 Ric van Dyke presented on “Intelligent (aka adaptive) Cursor Sharing”, a new feature of Oracle Database 11g. The feature was created to fix the shortcomings of bind peeking on columns with histograms. Tom Kyte presented a few years ago on this topic when he talked about the impact of the wheather on Oracle database performance.

During the very interesting presentation Ric presented with a demo how this feature works but also showed a nasty side effect. He tested it on a million row table with a column “object type”, which was skewed. He bound several different values to the object_type bind variable causing 2 different execution plans to be used for the same sql_id. The problem with it was that he ended up with dozens of child cursors showing V$SQL.IS_SHARABLE => N.

It was not known whether this is a bug or whether the whole concept is flawed. I searched on metalink and found the appropriate document:

Bug 6644714 – High number of child cursors with adaptive cursor sharing
This issue is fixed in

* 11.2 (Future Release)
* 11.1.0.7 (Server Patch Set)
Lots of child cursors may be produced with adaptive cursor sharing enabled,
sometimes with overlapping or same bind value selectivity ranges (in v$sql_cs_selectivity).
This problem shows as LOAD_OPTIMIZER_STATS=’Y’ for many of the cursors in V$SQL_SHARED_CURSOR.

– and –

Bug 7213010 – Adaptive cursor sharing generates lots of child cursors

This issue is fixed in

* 11.2 (Future Release)
* 11.1.0.7 (Server Patch Set)

Lots of child cursors may be produced for a query with bind variables
appearing in at least two simple relational predicates
(eg. col :b).

v$sql_cs_selectivity shows that there are multiple equivalent / overlapping
ranges for a single cursor. The number of ranges gets bigger and bigger
as the query is run more times.

I then tried to verify if the problem indeed is gone in 11.1.0.7 and found out, that it isn´t.

Here is my testcase:

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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434

SQL> @/tmp/setup.sql
Connected.
SQL> DROP TABLE tab1 purge;
 
TABLE dropped.
 
SQL> CREATE TABLE tab1 (a INT,
  2                     b varchar2(20),
  3                                     padding varchar2(200))
  4  tablespace USERS;
 
TABLE created.
 
SQL>
SQL> DROP SEQUENCE myid;
 
SEQUENCE dropped.
 
SQL> CREATE SEQUENCE myid cache 1000;
 
SEQUENCE created.
 
SQL>
SQL> BEGIN
  2  FOR i IN 1..100000
  3  loop
  4          INSERT INTO tab1 VALUES (myid.nextval,
  5                                                           'AAAAAAAAAA',
  6                                                           rpad('*',200,'*'));
  7  END loop;
  8
  9  FOR i IN 1..1000
 10  loop
 11          INSERT INTO tab1 VALUES (myid.nextval,
 12                                                           'BBBBBBBBBB',
 13                                                           rpad('*',200,'*'));
 14  END loop;
 15
 16  FOR i IN 1..1000
 17  loop
 18          INSERT INTO tab1 VALUES (myid.nextval,
 19                                                           'CCCCCCCCCC',
 20                                                           rpad('*',200,'*'));
 21  END loop;
 22
 23  FOR i IN 1..100
 24  loop
 25          INSERT INTO tab1 VALUES (myid.nextval,
 26                                                           'DDDDDDDDDD',
 27                                                           rpad('*',200,'*'));
 28  END loop;
 29
 30  FOR i IN 1..10
 31  loop
 32          INSERT INTO tab1 VALUES (myid.nextval,
 33                                                           'EEEEEEEEEE',
 34                                                           rpad('*',200,'*'));
 35  END loop;
 36
 37  INSERT INTO tab1 VALUES (myid.nextval,
 38                                                           'FFFFFFFFFF',
 39                                                           rpad('*',200,'*'));
 40
 41  commit;
 42  END;
 43  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL>
SQL> CREATE INDEX tab1_idx1 ON tab1 (b);
 
INDEX created.
 
SQL>
SQL> BEGIN dbms_stats.gather_table_stats(ownname => 'MDECKER',
  2                                          tabname => 'TAB1',
  3                                          method_opt =>'FOR ALL COLUMNS SIZE 254',
  4                                          cascade=> TRUE,
  5                                          estimate_percent => 100,
  6                                          no_invalidate => FALSE);
  7  END;
  8  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL>
SQL> SET LINES 300
SQL> col TABLE_NAME format a10
SQL> col owner format a8
SQL> SELECT owner, TABLE_NAME,num_rows, blocks, round((blocks*8192)/1024/1024) AS MB
  2  FROM dba_tables WHERE TABLE_NAME = 'TAB1' AND owner = 'MDECKER';
 
OWNER    TABLE_NAME   NUM_ROWS     BLOCKS         MB
-------- ---------- ---------- ---------- ----------
MDECKER  TAB1           102111       3268         26
 
SQL>
SQL> prompt "Press Enter to continue"
"Press Enter to continue"
SQL> pause
 
SQL>
SQL> /*
SQL> OWNER       TABLE_NAME   NUM_ROWS  BLOCKS         MB
SQL> -------- ---------- ---------- ---------- ----------
SQL> MDECKER  TAB1           102111       3268         26
SQL>
SQL> */
SQL>
SQL>
SQL>
SQL> SELECT b , COUNT(*) FROM mdecker.tab1 GROUP BY b ORDER BY 1 DESC;
 
B                      COUNT(*)
-------------------- ----------
FFFFFFFFFF                    1
EEEEEEEEEE                   10
DDDDDDDDDD                  100
CCCCCCCCCC                 1000
BBBBBBBBBB                 1000
AAAAAAAAAA               100000
 
6 ROWS selected.
 
SQL>
SQL> /*
SQL> B                      COUNT(*)
SQL> -------------------- ----------
SQL> FFFFFFFFFF                    1
SQL> EEEEEEEEEE                   10
SQL> DDDDDDDDDD                  100
SQL> CCCCCCCCCC                 1000
SQL> BBBBBBBBBB                 1000
SQL> AAAAAAAAAA               100000
SQL>
SQL> 6 rows selected.
SQL>
SQL> */
SQL>
SQL> prompt "Press Enter to continue"
"Press Enter to continue"
SQL> pause
 
SQL> SELECT num_buckets, histogram FROM dba_tab_col_statistics WHERE TABLE_NAME = 'TAB1' AND column_name = 'B';
 
NUM_BUCKETS HISTOGRAM
----------- ---------------
          6 FREQUENCY
 
SQL>
SQL> /*
SQL> NUM_BUCKETS HISTOGRAM
SQL> ----------- ---------------
SQL>           6 FREQUENCY
SQL> */
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SHOW USER
USER IS "MDECKER"
SQL> @/tmp/sql1.txt
SQL> SET LINES 300
SQL> SET echo ON
SQL> SET pages 10000
SQL> col bind format a120
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL>
SQL> variable myvar varchar2(20)
SQL> EXEC :myvar := 'AAAAAAAAAA';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 0
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 2211052296
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   889 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB1 |   100K|    20M|   889   (1)| 00:00:11 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("B"=:MYVAR)
 
 
18 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
 
SQL>
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> EXEC :myvar := 'BBBBBBBBBB';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 0
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 2211052296
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   889 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB1 |   100K|    20M|   889   (1)| 00:00:11 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("B"=:MYVAR)
 
 
18 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
 
SQL>
SQL> EXEC :myvar := 'CCCCCCCCCC';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 1
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |    36 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |  1000 |   210K|    36   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |  1000 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
 
SQL>
SQL> EXEC :myvar := 'DDDDDDDDDD';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 2
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     5 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |   100 | 21600 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |   100 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2
 
SQL>
SQL> EXEC :myvar := 'EEEEEEEEEE';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 3
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |    10 |  2160 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3
 
SQL>
SQL> EXEC :myvar := 'FFFFFFFFFF';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 4
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |     1 |   216 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA()))           1918680838            4

You can see that after parsing for bind CCCCCCCC, the execution plan changes from full table scan to index range scan. Moreover child cursor 1 is marked “is_bind_aware=Y”. When now bind DDDDDDDD is used, there is a new child (#2) and child 1 is set to is_sharable=N. After using bind EEEEEEEE, child #2 is set to sharable=N and a new child #3 is created. This repeats until there are 5 child cursors, 2 of which are set to is_sharable=Y. When the script is executed multiple times, there is even a sixth child cursor.

1
2
3
4
5
6
7
8

SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ ----------
fhtgpcv2m2j4s N Y N N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0          2
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1          1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2          1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3          1
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA()))           1918680838            4         16
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            5          3

I wondered why the cursors are not sharable and queried v$sql_shared_cursor:

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

SQL>   SELECT * FROM v$sql_shared_cursor WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
fhtgpcv2m2j4s 23F4E438 23F4DAB0            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N
fhtgpcv2m2j4s 23F4E438 23D924D8            1 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23D7E888            2 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23D6B8C8            3 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23D574D8            4 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23B983B0            5 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
 
6 ROWS selected.
 
SQL> SELECT sql_id, address, child_number, LOAD_OPTIMIZER_STATS, row_level_sec_mismatch FROM v$sql_shared_cursor WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        ADDRESS  CHILD_NUMBER L R
------------- -------- ------------ - -
fhtgpcv2m2j4s 245CE53C            0 Y N
fhtgpcv2m2j4s 245CE53C            1 N Y
fhtgpcv2m2j4s 245CE53C            2 N Y
fhtgpcv2m2j4s 245CE53C            3 N Y
fhtgpcv2m2j4s 245CE53C            4 N Y
fhtgpcv2m2j4s 245CE53C            5 N Y
 
SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = 'fhtgpcv2m2j4s';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
245CE53C 3308340376 fhtgpcv2m2j4s            5          3946114705 N          1              0           2          0
245CE53C 3308340376 fhtgpcv2m2j4s            5          2785428864 Y          1              0           2          0
245CE53C 3308340376 fhtgpcv2m2j4s            4          1680726256 Y          1              2           3          0
245CE53C 3308340376 fhtgpcv2m2j4s            3          1438126164 Y          1             20           5          0
245CE53C 3308340376 fhtgpcv2m2j4s            2           451655377 Y          1            200          21          0
245CE53C 3308340376 fhtgpcv2m2j4s            1          4078512309 Y          1           2000         169          0
245CE53C 3308340376 fhtgpcv2m2j4s            0           479772785 Y          1         100000        9823          0
 
7 ROWS selected.
 
SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = 'fhtgpcv2m2j4s';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
245CE53C 3308340376 fhtgpcv2m2j4s            5 =MYVAR                                            0 0.000004   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            4 =MYVAR                                            0 0.000009   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            3 =MYVAR                                            0 0.000088   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            2 =MYVAR                                            0 0.000881   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            1 =MYVAR                                            0 0.008814   0.010773

It is not clear, why the cursors are not shared and why this is expressed in the flag “row_level_sec_mismatch”. From these tests we can see that the number of child cursors is still quite high.


Tested for Oracle Database 11g Performance Tuning Certified Expert

By | March 4th, 2009 | Category: Certification | No Comments »

Last week, I decided to try the newest Oracle 11g certification –

1Z0-054 (1Z1-054): Oracle Database 11g Performance Tuning Certified Expert.

The exam is still in beta, which means that there are LOTS of questions (192 to be precise) in 3 hours. Unfortunately, this was not enough time for me. I had to skip 5-10 text-intensive questions to make it all the way to the end.

I can only recommend to practice a lot with the new 11g Performance Features as well as DB Control.

Unfortunately, now the waiting is only beginning because results won´t be available until 10 weeks after the beta close, which is extended until 30 June 2009. 😉

I might have to try it again after getting some practical experience on the first 11g production database.


Oracle Enterprise Manager – Grid Control 10.2.0.5 finally released

By | March 4th, 2009 | Category: 11g, Oracle Enterprise Manager | 2 comments

Finally, Oracle Enterprise Manager – Grid Control 10.2.0.5 is available for download for Win32 and Linux x86. Moreover, this is the first release to officially support 11.1.0.7 as repository database as well as the first release to officially support RHEL5 for OMS.

Relevant documents for upgrade are available on MetaLink:

  • 763307.1: How to Install the 10.2.0.5.0 Grid Control Patchset on a Well Maintained OMS or Agent
  • List of Bugs Fixed: http://download.oracle.com/docs/cd/B16240_01/doc/doc.102/e14226/toc.htm
  • 763351.1: Documentation Reference for Grid Control 10.2.0.5.0 Installation and Upgrade
  • 464674.1: Checklist for EM 10g Grid Control 10.2.x to 10.2.0.4/10.2.0.5 OMS and Repository Upgrades

To be continued….