The CPU Costing Model: A Few Thoughts Part III (Bang Bang)
Posted December 21, 2009One of the advantages of system statistics and the CPU costing model is in how the CBO deals with multiblock reads and the impact of the db_file_multiblock_read_count parameter. When performing a full table (or fast full index) scan, the CBO needs to determine just how many multiblock read operations are likely to be performed so the associated operation can [...]
TEMP Table Transformation and Remote Procedure Calls
Posted December 19, 2009I've recently come across a interesting side-effect regarding temp table transformations at one of my clients.
There was a PL/SQL package procedure that worked fine when called locally but somehow "hung" when being called from a remote database - all it did was to call exactly the same package procedure with the same parameters as the local call, but one of the SQL statements executed as part of the procedure generated an suboptimal execution plan that never completed.
Further investigations revealed that the significant difference between the execution plan of the local and the remote execution of the procedure was the different treatment of a contained "WITH" clause.
The interesting point is that the procedure called itself didn't perform any "distributed" queries or DML - the only difference was that one time the procedure got called locally, and one time remotely per database link. All processing within the procedure was local - no activities using database links were involved.
There are (at least) two known areas where Oracle can optionally use a so called TEMP TABLE TRANSFORMATION as part of the execution plan:
1. Materialization of a Subquery Factoring, also known as "Common Table Expression" or simply "WITH clause"
Oracle uses this when the subquery is used more than once in the execution plan, or if forced with the undocumented MATERIALIZE hint as part of the SELECT in the WITH clause. There are a few (not really documented) limitations of this materialization, in particular if LOBs or LONGs are part of the projection then this TEMP TABLE transformation can't get used.
2. Star transformation with TEMP TABLE transformation
Star transformations can also make use of the TEMP TABLE transformation. This is enabled by default when STAR_TRANSFORMATION_ENABLED is set to TRUE, but can be disabled by setting STAR_TRANSFORMATION_ENABLED to TEMP_DISABLE.
The following testcase shall demonstrate the subtle side effect of the Remote Procedure Call via Database Link.
Consider the following simple package:
procedure test_star_transform_statement;
procedure test_simple_statement;
end pkg_test_materialize_remote;
/
create or replace package body pkg_test_materialize_remote as
procedure test_star_transform_statement as
c sys_refcursor;
n1 number;
c1 varchar2(255);
c2 varchar2(255);
begin
execute immediate 'alter session set star_transformation_enabled = true';
open c for '
select
sum(quantity_sold),
p.prod_subcategory_desc,
c.cust_gender
from
sh.sales s
join sh.products p ON (s.prod_id = p.prod_id)
join sh.customers c ON (s.cust_id = c.cust_id)
join sh.countries d ON (c.country_id = d.country_id)
where
p.prod_subcategory_desc = ''Memory'' and
c.cust_city = ''Oxford'' and
c.cust_gender = ''F''
group by
p.prod_subcategory_desc, c.cust_gender';
fetch c into n1, c1, c2;
close c;
end test_star_transform_statement;
procedure test_simple_statement as
begin
for rec in (
with a as (
select /*+ materialize */
*
from
dual
)
select
*
from
a
where
1 = 2
) loop
null;
end loop;
end test_simple_statement;
end pkg_test_materialize_remote;
/
The simple statement is not really useful but probably the simplest form of requesting a TEMP TABLE transformation.
The statement using star transformation generates a TEMP TABLE transformation when using the default demo SH setup. I've only wrapped it in dynamic SQL to take advantage of any enabled roles due to the AUTHID CURRENT_USER clause.
And indeed when calling the two procedures locally like the following:
exec pkg_test_materialize_remote.test_star_transform_statement
You get these execution plans both using TEMP TABLE transformations:
-------------------------------------
WITH A AS ( SELECT /*+ materialize */ * FROM DUAL ) SELECT * FROM A
WHERE 1 = 2
Plan hash value: 1137659336
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6617_E0AEB | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NULL IS NOT NULL)
SQL_ID 5bp3rpfs2f4bw, child number 0
-------------------------------------
select sum(quantity_sold),
p.prod_subcategory_desc, c.cust_gender from
sh.sales s join sh.products p ON (s.prod_id = p.prod_id)
join sh.customers c ON (s.cust_id = c.cust_id) join
sh.countries d ON (c.country_id = d.country_id) where
p.prod_subcategory_desc = 'Memory' and c.cust_city = 'Oxford'
and c.cust_gender = 'F' group by
p.prod_subcategory_desc, c.cust_gender
Plan hash value: 3883759770
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 435 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 45 | 990 | 405 (1)| 00:00:05 | | |
| 4 | HASH GROUP BY | | 2 | 94 | 30 (10)| 00:00:01 | | |
|* 5 | HASH JOIN | | 27 | 1269 | 24 (9)| 00:00:01 | | |
|* 6 | HASH JOIN | | 27 | 1134 | 22 (5)| 00:00:01 | | |
|* 7 | HASH JOIN | | 27 | 810 | 20 (5)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE ALL | | 31 | 372 | 16 (0)| 00:00:01 | 1 | 28 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 31 | 372 | 16 (0)| 00:00:01 | 1 | 28 |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 12 | BITMAP AND | | | | | | | |
| 13 | BITMAP MERGE | | | | | | | |
| 14 | BITMAP KEY ITERATION | | | | | | | |
| 15 | BUFFER SORT | | | | | | | |
|* 16 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
|* 17 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | | | | 1 | 28 |
| 18 | BITMAP MERGE | | | | | | | |
| 19 | BITMAP KEY ITERATION | | | | | | | |
| 20 | BUFFER SORT | | | | | | | |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6616_E0AEB | 45 | 225 | 2 (0)| 00:00:01 | | |
|* 22 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | | 1 | 28 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6616_E0AEB | 45 | 540 | 2 (0)| 00:00:01 | | |
| 24 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."CUST_CITY"='Oxford' AND "C"."CUST_GENDER"='F'))
5 - access("C1"="D"."COUNTRY_ID")
6 - access("S"."CUST_ID"="C0")
7 - access("S"."PROD_ID"="P"."PROD_ID")
8 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
16 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
17 - access("S"."PROD_ID"="P"."PROD_ID")
22 - access("S"."CUST_ID"="C0")
Note
-----
- star transformation used for this statement
Let's simulate a Remote Procedure Call using a loopback database link:
Now get rid of the two execution plans in the Shared Pool (e.g. by flushing it if a test system) and execute the following:
exec pkg_test_materialize_remote.test_star_transform_statement@loopback@local
The execution plans look now like this:
-------------------------------------
WITH A AS ( SELECT /*+ materialize */ * FROM DUAL ) SELECT * FROM A
WHERE 1 = 2
Plan hash value: 3752461848
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
SQL_ID 5bp3rpfs2f4bw, child number 0
-------------------------------------
select sum(quantity_sold),
p.prod_subcategory_desc, c.cust_gender from
sh.sales s join sh.products p ON (s.prod_id = p.prod_id)
join sh.customers c ON (s.cust_id = c.cust_id) join
sh.countries d ON (c.country_id = d.country_id) where
p.prod_subcategory_desc = 'Memory' and c.cust_city = 'Oxford'
and c.cust_gender = 'F' group by
p.prod_subcategory_desc, c.cust_gender
Plan hash value: 3619028137
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1238 (100)| | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 57 | 1238 (1)| 00:00:15 | | |
|* 2 | HASH JOIN | | 26 | 1482 | 830 (1)| 00:00:10 | | |
|* 3 | HASH JOIN | | 26 | 1352 | 828 (1)| 00:00:10 | | |
|* 4 | HASH JOIN | | 27 | 810 | 423 (1)| 00:00:06 | | |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 31 | 372 | 419 (1)| 00:00:06 | 1 | 28 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 31 | 372 | 419 (1)| 00:00:06 | 1 | 28 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 9 | BITMAP AND | | | | | | | |
| 10 | BITMAP MERGE | | | | | | | |
| 11 | BITMAP KEY ITERATION | | | | | | | |
| 12 | BUFFER SORT | | | | | | | |
|* 13 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
|* 14 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | | | | 1 | 28 |
| 15 | BITMAP MERGE | | | | | | | |
| 16 | BITMAP KEY ITERATION | | | | | | | |
| 17 | BUFFER SORT | | | | | | | |
|* 18 | TABLE ACCESS FULL | CUSTOMERS | 45 | 765 | 405 (1)| 00:00:05 | | |
|* 19 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | | 1 | 28 |
|* 20 | TABLE ACCESS FULL | CUSTOMERS | 45 | 990 | 405 (1)| 00:00:05 | | |
| 21 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."COUNTRY_ID"="D"."COUNTRY_ID")
3 - access("S"."CUST_ID"="C"."CUST_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
13 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
14 - access("S"."PROD_ID"="P"."PROD_ID")
18 - filter(("C"."CUST_CITY"='Oxford' AND "C"."CUST_GENDER"='F'))
19 - access("S"."CUST_ID"="C"."CUST_ID")
20 - filter(("C"."CUST_CITY"='Oxford' AND "C"."CUST_GENDER"='F'))
Note
-----
- star transformation used for this statement
Notice how the TEMP TABLE TRANSFORMATION is gone from both plans?
The odd thing is that I wasn't able so far to pinpoint any optimizer environment settings that are related to this - in fact the optimizer happily shares the existing plan with the remote or local execution, so there seems not to be any difference in the optimizer environment (and which makes me think that this is more a side-effect than an intended feature).
Therefore it is required to get rid of the plans generated by the local execution to reproduce the issue above.
The behaviour seems to be consistent across 10.2.0.4, 11.1.0.7 and 11.2.0.1.
I could imagine there is something related to the fact that a distributed transaction has been started by the RPC call, but since the plans are re-used when already in the Shared Pool, this doesn't seem to be a reasonable explanation either.
I couldn't find yet any MetaLink (sorry, MOS) documents that describe this particular issue. I might raise an SR if I find the time, the issue has been worked around at the client side by ensuring particular execution paths of the critical statement.
Update Jan 2011: Thanks to the anonymous poster below - the corresponding official bug is Bug 9399589: "WITH" subqueries cannot be materialized inside a global transaction
Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup
Posted December 18, 2009Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure. This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either. In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the customer complained. This standby database is usually opened for read only access from time to time.This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately, at this time it had become a rather political issue.
Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary, there are two parts of the problem:
(1) What happened
(2) How to fix it
What Happened
Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases. On the primary:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447102
On the standby:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1301571
Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:
SQL> select scn_to_timestamp(1447102) from dual;
SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM
I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):
SQL> select scn_to_timestamp(1301571) from dual;
SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM
This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.
The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:
…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
…
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…
Going back in the alert log, I found these lines:
Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”. This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.
The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now. The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either. This has caused the standby to lag behind since that time.
So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert log of the primary instance. The following lines were of interest:
…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…
These lines showed everything clearly. The issue was:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.
Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the FAL did not get that log was finally cleared.
Solution
Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?
One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].
1. [Standby] Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
2. [Standby] Shutdown the standby database
3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:
RMAN> run {
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb';
3> backup incremental from scn 1301571 database;
4> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK
Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
…
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1
4. [Primary] On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';
Database altered.
5. [Primary] Copy these files to standby host:
oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04
6. [Standby] Bring up the instance in nomount mode:
SQL> startup nomount
7. [Standby] Check the location of the controlfile:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl
8. [Standby] Replace the controlfile with the one you just created in primary.
9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl
10.[Standby] Mount the standby database:
SQL> alter database mount standby database;
11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:
$ rman target=/
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback
List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
12.Recover these files:
RMAN> recover database;
Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
…
13. After some time, the recovery fails with the message:
archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'
This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.
14.At this point exit RMAN and start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
15.Check the SCN’s in primary and standby:
[Standby] SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447474
[Primary] SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447478
Now they are very close to each other. The standby has now caught up.
Collaborate 2010: Penny Lane
Posted December 18, 2009Well, so much for my plans to not travel as much next year … Found out earlier in the week I’ve now been selected to present at next year’s Collaborate 10 event in Las Vegas. I’ll be presenting a new version of my Indexing Tricks and Traps presentation on Monday, 19 April from 10:45am to 11:45am [...]
Chinese Edition of TOP Available!
Posted December 18, 2009Few weeks ago the Chinese Edition of my book, Troubleshooting Oracle Performance, was published by China-Pub! Honestly, this is something I did not expect when publishing the book.
Even though I still have to touch one of them myself, let me spend few words about it…
I still remember when I saw for the first time part [...]
Miracle Open World 2010, April 14-16, Legoland
Posted December 18, 2009Finally Miracle has published the first official advertisement for Miracle Open World 2010 event, which will take place in Legoland, Billund, Jutland, Denmark, from 14-16 April 2010. Here is the link to the event site.
I'll be presenting at this spectacular and unique event. Hopefully the weather will permit me to fly to Legoland by myself and land at Billund airport which is just several hundred meters away from the conference venue.
Kernel NFS fights back… Oracle throughput matches Direct NFS with latest Solaris improvements
Posted December 17, 2009After my recent series of postings, I was made aware of David Lutz’s blog on NFS client performance with Solaris. It turns out that you can vastly improve the performance of NFS clients using a new parameter to adjust the number of client connections.
root@saemrmb9> grep rpcmod /etc/system set rpcmod:clnt_max_conns=8
This parameter was introduced in a patch for various flavors of Solaris. For details on the various flavors, see David Lutz’s recent blog entry on improving NFS client performance. Soon, it should be the default in Solaris making out-of-box client performance scream.
DSS query throughput with Kernel NFS
I re-ran the DSS query referenced in my last entry and now kNFS matches the throughput of dNFS with 10gigE.
-
Kernel NFS throughput with Solaris 10 Update 8 (set rpcmod:clnt_max_conns=8)
This is great news for customers not yet on Oracle 11g. With this latest fix to Solaris, you can match the throughput of Direct NFS on older versions of Oracle. In a future post, I will explore the CPU impact of dNFS and kNFS with OLTP style transactions.
Posted in Oracle, Storage Tagged: 11g, 7410, analytics, database, dNFS, NAS, NFS, Oracle, performance, Solaris, Sun, tuning 
Pipelined functions in oracle 9i
Posted December 17, 2009Functions can now stream data as virtual tables. May 2002 (updated May 2007)
CBO oddities in determining selectivity on NVARCHAR2 data type
Posted December 17, 2009Some time ago I was involved in solving of a performance problem which was really weird - a simple select on a indexed column with unique value always resulted in a full table scan. The key column was of type NVARCHAR2. To better understand the behavior I created a simple reproducible test case.
Let us start with table and index creation.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> show parameter semant
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string CHAR
SQL> create table x2 (id varchar2(32), idn nvarchar2(32), id16 nvarchar2(16), id20 nvarchar2(20));
Table created.
SQL> insert into x2 select lpad(rownum,32,'0'), lpad(rownum,32,'0'),lpad(rownum,16,'0'),lpad(rownum,20,'0')
from dual connect by level <= 20000; 20000 rows created. SQL> commit;
Commit complete.
SQL> create index x2_idn on x2(idn);
Index created.
SQL> analyze table x2 compute statistics;
Table analyzed.
SQL> explain plan for select * from x2 where idn=U'00000000000000000000000000000009';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 156302112
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 3281K| 136 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| X2 | 20000 | 3281K| 136 (0)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("IDN"=U'00000000000000000000000000000009')
We have 20,000 rows with unique values in IDN column in the table, we have statistics and still the CBO produces an execution plan with FULL TABLE SCAN and with a very strange estimate of 20,000 rows reurned by this query.
Therefore our first question should be: What is wrong with the statistics? A simple query on USER_TAB_COLS shows the following results:
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE
------------------------------ ------------ ---------------------------------------------------------------- ----------------------------------------------------------------
ID 20000 3030303030303030303030303030303030303030303030303030303030303031 3030303030303030303030303030303030303030303030303030303230303030
IDN 1 0030003000300030003000300030003000300030003000300030003000300030 0030003000300030003000300030003000300030003000300030003000300030
ID16 20000 0030003000300030003000300030003000300030003000300030003000300031 0030003000300030003000300030003000300030003000320030003000300030
ID20 3 0030003000300030003000300030003000300030003000300030003000300030 0030003000300030003000300030003000300030003000300030003000300032
To display raw values I used function developed by Greg Rahn which gives the following result:
SQL> column hi_val format a32
SQL> select column_name,
2 num_distinct,
3 display_raw(low_value,data_type) lo_val,
4 display_raw(high_value,data_type) hi_val
5 from user_tab_cols
6 where table_name='X2';
COLUMN_NAME NUM_DISTINCT LO_VAL HI_VAL
------------------------------ ------------ -------------------------------- --------------------------------
ID 20000 00000000000000000000000000000001 00000000000000000000000000020000
IDN 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ID16 20000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
ID20 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
We can see that the NUM_DISTINCT column has quite strange values. Reason for this is that Oracle analyzes only first 32 bytes (and not characters) of the string. The default character set for NVARCHAR2 type is AL16UTF16 - a 2 byte character set.
The column IDN was defined as NVARCHAR2(32) and was actually completely filled so every row stores 64 bytes in this column. Here is the proof:
DUMP(IDN)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=64: 0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,51
Due to considering only first 32 bytes analyze comes back with a strange result that there is only one distinct value, what is of course true, as all distinct digits are beyond first 32 bytes.
Let us continue and use DBMS_STATS.GATHER_TABLE_STATS for analysis.
2 dbms_stats.gather_table_stats(
3 ownname=> user ,
4 tabname=> 'X2',
5 cascade=> TRUE,
6 estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
7 degree=> DBMS_STATS.AUTO_DEGREE,
8 no_invalidate=> FALSE,
9 granularity=> 'ALL',
10 method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
11 end;
12 /
SQL> select column_name,num_distinct,
2 display_raw(low_value,data_type) lo_val,
3 display_raw(high_value,data_type) hi_val
4 from user_tab_cols where table_name='X2'
5 /
COLUMN_NAME NUM_DISTINCT LO_VAL HI_VAL
------------------------------ ------------ -------------------------------- --------------------------------
ID 19820 00000000000000000000000000000001 00000000000000000000000000020000
IDN 20000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ID16 20000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
ID20 19942 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
DBMS_STATS produces much more reliable cardinalities but the low/high values are still showing only 0 for IDN column. Here there is no difference between ANALYZE and DBMS_STATS as it can't be. Oracle looks only at first 32 bytes and that is the fact. Therefore the CBO will be not able to properly determine the cardinality for the range predicates. We will look closer this in the continuation of this post.
This fact might become dangerous also for all databases which use multi-byte character set as default character set. Actually Oracle will always look only at first 32 bytes and in case that the column will contain many many multi-byte characters this can become pretty odd. All this is something new and becomes more critical in now days when a lot of databases are migrated from single-byte character set to a multi-byte character sets. For sure migrating to any flavor of UTF-8/AL32UTF8 character set represent such a thread.
Conclusions about statistics for NVARCHAR2 data type
When I realized what is the reason behind for such behavior I suggested to my customer to switch back to rule based optimizer what solved the problem. The other possibility would be to change all NVARCHAR2 columns to VARCHAR2 but the customer accept this.
First, but the most important conclusion would be not to use NVARCHAR2 data type for key columns, but rather VARCHAR2. Or paraphrasing Mogens Norgard: "We don't use NVARCHAR2 data type!" :-)
*Range cardinalities on columns of NVARCHAR2 data type*
Reason for this part of post is my involvement in one thread on OTN where a lady from Austria, my neighbor country, was complaining about the "strange" execution plans and consequently also "un-wanted" performance. In one mail she explained me that they are experiencing performance problems since they have upgraded from Oracle9i to 10g. She also said that at the same time they moved from VARCHAR2 data type to NVARCHAR2 datatype.
My first impression was that this might be the same situation as I have already explained above. But after digging deeply in the problem, I realized that the problem lies in completely mis-estimated selectivity and consequently also the cardinality when NVARCHAR2 data type column is in question.
To be able to study the case I prepared an extremely simple reproducible test case. Let us start with the setup of the test environment:
Table created.
SQL> insert into x1 select lpad(rownum,8,'0'), lpad(rownum,8,'0')
from dual connect by level <= 20000; 20000 rows created. SQL> commit;
Commit complete.
SQL> select cv,cn from x1 where rownum <= 10; CV CN ---------- ---------- 00000469 00000469 00000470 00000470 00000471 00000471 00000472 00000472 00000473 00000473 00000474 00000474 00000475 00000475 00000476 00000476 00000477 00000477 00000478 00000478 10 rows selected. SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname=> user ,
4 tabname=> 'X1',
5 cascade=> TRUE,
6 estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
7 degree=> DBMS_STATS.AUTO_DEGREE,
8 no_invalidate=> FALSE,
9 granularity=> 'ALL',
10 method_opt=> 'FOR ALL COLUMNS SIZE 1');
11* end;
SQL> /
PL/SQL procedure successfully completed.
Let us run our first test SQL. I have used bind variables named :a1, :a2 of type VARCHAR2 and :n1, :n2 of type NVARCHAR2. I have event written a simple anonymous PL/SQL block where I selected the values for bind variables from the same table in order to be sure that the binding is done properly. While I was running the tests I enabled events 10053 and 10046.
SQL_ID 1fj17ram77n5w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ /* run 20 */ *
from x1
where cn >= to_nchar(:a1)
and cn <= to_nchar(:a2) Plan hash value: 2189453339 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 9999 |00:00:00.08 | 102 | |* 2 | TABLE ACCESS FULL| X1 | 1 | 1 | 9999 |00:00:00.03 | 102 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SYS_OP_C2C(:A1)<=SYS_OP_C2C(:A2)) 2 - filter(("CN"<=SYS_OP_C2C(:A2) AND "CN">=SYS_OP_C2C(:A1)))
PARSING IN CURSOR #12 len=111 dep=0 uid=88 oct=3 lid=88 tim=254514943459 hv=2792607932 ad='23f7056c' sqlid='1fj17ram77n5w'
select /*+ gather_plan_statistics */ /* run 20 */ *
from x1
where cn >= to_nchar(:a1)
and cn <= to_nchar(:a2) END OF STMT PARSE #12:c=0,e=734,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=254514943452 BINDS #12: Bind#0 oacdty=01 mxl=32(10) mxlc=10 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=170 siz=64 off=0 kxsbbbfp=0fec0abc bln=32 avl=08 flg=05 value="00000000" Bind#1 oacdty=01 mxl=32(10) mxlc=10 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=170 siz=0 off=32 kxsbbbfp=0fec0adc bln=32 avl=08 flg=01 value="00009999"
I got a hard parse (mis=1), optimizer peeked at the values of bind variables and the estimated cardinality was 1 while the actual one was 9999. From 10046 trace we can clearly see the values of bind variables used by optimizer for the optimization. I used VARCHAR2 type of columns as bind variables but used TO_NCHAR function to convert to NVARCHAR2 data type. The optimizer internally rewrote the SQL so that instead of TO_NCHAR used internal function SYS_OP_C2C which performs conversion from one character set to another.
S
-
S
For next run I used NVARCHAR2 type bind variables and here is the result:
-------------------------------------
select /*+ gather_plan_statistics */ /* run 15 */ *
from x1
where cn >= to_nchar(:n1)
and cn <= to_nchar(:n2) Plan hash value: 2189453339 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 9999 |00:00:00.08 | 759 | |* 2 | TABLE ACCESS FULL| X1 | 1 | 50 | 9999 |00:00:00.04 | 759 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:N1<=:N2) 2 - filter(("CN">=:N1 AND "CN"<=:N2)) PARSING IN CURSOR #15 len=111 dep=0 uid=88 oct=3 lid=88 tim=254434564390 hv=2523312628 ad='2c8d2200' sqlid='d1bn5vyb6dcgn' select /*+ gather_plan_statistics */ /* run 15 */ * from x1 where cn >= to_nchar(:n1)
and cn <= to_nchar(:n2) END OF STMT PARSE #15:c=0,e=746,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=254434564383 BINDS #15: Bind#0 oacdty=01 mxl=128(40) mxlc=20 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=256 off=0 kxsbbbfp=0febbee0 bln=128 avl=16 flg=05 value="" Bind#1 oacdty=01 mxl=128(40) mxlc=20 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=128 kxsbbbfp=0febbf60 bln=128 avl=16 flg=01 value=""
Here we got the estimated number of rows 50 what is exactly the default use by the CBO for ranges - selectivity is 0.0025. So the CBO simply decided to throw in the default selectivity. The values of bind variables are empty ("") - CBO just ignored them during the optimization phase but they were of course respected by the runtime engine.
-------------------------------------
select /*+ gather_plan_statistics */ /* run 14 */ *
from x1
where substr(cn,1,10) >= to_nchar(:a1)
and substr(cn,1,10) <= to_nchar(:a2) Plan hash value: 2189453339 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 9999 |00:00:00.09 | 759 | |* 2 | TABLE ACCESS FULL| X1 | 1 | 50 | 9999 |00:00:00.05 | 759 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SYS_OP_C2C(:A1)<=SYS_OP_C2C(:A2)) 2 - filter((SUBSTR("CN",1,10)>=SYS_OP_C2C(:A1) AND
SUBSTR("CN",1,10)<=SYS_OP_C2C(:A2))) PARSING IN CURSOR #12 len=137 dep=0 uid=88 oct=3 lid=88 tim=254227180414 hv=2415463600 ad='23f7e900' sqlid='f5wapff7zk35h' select /*+ gather_plan_statistics */ /* run 16 */ * from x1 where substr(cn,1,10) >= to_nchar(:a1)
and substr(cn,1,10) <= to_nchar(:a2) END OF STMT PARSE #12:c=0,e=767,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=254227180407 BINDS #12: Bind#0 oacdty=01 mxl=32(10) mxlc=10 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=170 siz=64 off=0 kxsbbbfp=0ff75ac0 bln=32 avl=08 flg=05 value="00000000" Bind#1 oacdty=01 mxl=32(10) mxlc=10 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=170 siz=0 off=32 kxsbbbfp=0ff75ae0 bln=32 avl=08 flg=01 value="00009999"
In this case I added used SUBSTR function on the column and VARCHAR2 type bind variables and again the CBO threw in the default for range 0.0025, but we can see the values of bind variables.
-------------------------------------
select /*+ gather_plan_statistics */ /* run 14 */ *
from x1
where substr(cn,1,10) >= to_nchar(:n1)
and substr(cn,1,10) <= to_nchar(:n2) Plan hash value: 2189453339 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 9999 |00:00:00.09 | 759 | |* 2 | TABLE ACCESS FULL| X1 | 1 | 50 | 9999 |00:00:00.05 | 759 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:N1<=:N2) 2 - filter((SUBSTR("CN",1,10)>=:N1 AND SUBSTR("CN",1,10)<=:N2)) PARSING IN CURSOR #16 len=137 dep=0 uid=88 oct=3 lid=88 tim=254203929826 hv=1230940210 ad='23f78074' sqlid='13u552p4pxa1k' select /*+ gather_plan_statistics */ /* run 15 */ * from x1 where substr(cn,1,10) >= to_nchar(:n1)
and substr(cn,1,10) <= to_nchar(:n2) END OF STMT PARSE #16:c=0,e=770,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=254203929819 BINDS #16: Bind#0 oacdty=01 mxl=128(40) mxlc=20 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=256 off=0 kxsbbbfp=10460bd0 bln=128 avl=16 flg=05 value="" Bind#1 oacdty=01 mxl=128(40) mxlc=20 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=128 kxsbbbfp=10460c50 bln=128 avl=16 flg=01 value=""
In the CBO trace the inforamation was:
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(20) mxlc=10 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=02 csi=2000 siz=64 off=0
kxsbbbfp=0f1f5a1c bln=32 avl=16 flg=05
value=""
Bind#1
oacdty=01 mxl=32(20) mxlc=10 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=32
kxsbbbfp=0f1f5a3c bln=32 avl=16 flg=01
value=""
In the last case I just changed from VARCHAR2 to NVARCHAR2 bind variables and the result was expected - the default selectivity and the values of bind variables were again "".
Every time, when the CBO used binds for estimating selectivity I could see the following section after the execution plan:
============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=170
char format=1
max length=32
value=00000000
Bind variable information
position=2
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=170
char format=1
max length=32
value=00009999
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "X1"@"SEL$1")
END_OUTLINE_DATA
*/
And finally one case with VARCHAR2 data type.
-------------------------------------
select /*+ gather_plan_statistics */ /* run 22 */ *
from x1 where cv >=:a1
and cv <= :a2 Plan hash value: 2189453339 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 9999 |00:00:00.07 | 102 | |* 2 | TABLE ACCESS FULL| X1 | 1 | 9999 | 9999 |00:00:00.02 | 102 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:A1<=:A2) 2 - filter(("CV"<=:A2 AND "CV">=:A1))
PARSING IN CURSOR #13 len=89 dep=0 uid=88 oct=3 lid=88 tim=265619391820 hv=3829943329 ad='2e0c3568' sqlid='bznnx6zk4hm11'
select /*+ gather_plan_statistics */ /* run 22 */ *
from x1
where cv >= :a1
and cv <= :a2 END OF STMT PARSE #13:c=0,e=762,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=265619391813 BINDS #13: Bind#0 oacdty=01 mxl=32(10) mxlc=10 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=170 siz=64 off=0 kxsbbbfp=0fec0abc bln=32 avl=08 flg=05 value="00000000" Bind#1 oacdty=01 mxl=32(10) mxlc=10 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=170 siz=0 off=32 kxsbbbfp=0fec0adc bln=32 avl=08 flg=01 value="00009999"
In this last case I was querying the VARCHAR2 column and using bind variables of VARCHAR2 type and this time the optimizer estimated cardinality correctly. Of course it performed peeking at the values of bind variables.
Randolf Geist responded to my post on OTN and he suggested to confuse the CBO by using values like 'A0000000' to 'A00009999'. According to his observations also the selectivity for range predicates on VARCHAR2 data types is quite off after using this trick. Building a histogram returns things back to normal on both data types.
He pointed out that the CBO is obviously so smart to spot stored digits in VARCHAR2 columns and do good estimations by treating them as numbers while this is not true for the VARCHAR2 data type.
CONCLUSION
It is obvious that the CBO is not capable to perforem same estimates for ranges on NVARCHAR2 column as it is capable to do on VARCHAR2 column when numbers are stored as strings.
But the most important conclusion to be made is that one should use *appropriate* data types.
Of course that are many possibilities that the CBO might behave differently (like using DYNAMIC_SAMPLING, ...). So I am looking forward for the contributions of others, not just Randolf Geist.
This case was used to raise SR #3-1193039441: CBO and nvarchar2 --> A new Bug: 9220704 has been filed with development.
Does CREATE INDEX Gather Global Statistics?
Posted December 17, 2009You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of [...]
.png){kind=small}
Tweets