Bind Variables ignoring Index on 10.2.0.4
pat....@service-now.com
months now. We've run on 10.1.*, 10.2.0.1, 10.2.0.2, 10.2.0.3, all
without any unexpected behavior. Recently, we had a customer install
us atop 10.2.0.4 and we've seen some very unexpected behavior with
regard to our queries. To whit, anything with bind variables seems to
have stopped using what seem like perfectly reasonable indexes,
indexes that got used quite naturally on 10.2.0.3.
We're a java app connecting to Oracle via Oracle's THIN JDBC driver (v
10.2.0.3) over TCP.
All session run in case insensative mode and all connections are
initiated with:
ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI
I have a table sys_trigger with about 40k rows in it.
Primary key is sys_id, a char(32) field with unique values.
We have this index atop the table:
create index sys_trigger_sys_id on sys_trigger
(NLSSORT("sys_id",'nls_sort=''BINARY_CI'''));
I have current stats on the table.
I'm issuing this query:
DELETE from SYS_TRIGGER where "sys_id" = :1
SQL> explain plan for delete from sys_trigger where "sys_id" = :1;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2365230323
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 86 | 1109 (1)| 00:00:14
|
| 1 | DELETE | SYS_TRIGGER | | | | |
|* 2 | TABLE ACCESS FULL| SYS_TRIGGER | 1 | 86 | 1109
(1)| 00:00:14 |
----------------------------------------------------------------------------------
SQL> explain plan for delete from sys_trigger where "sys_id" = 'a';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1594121083
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 86 | 3 (0)|
00:00:01 |
| 1 | DELETE | SYS_TRIGGER | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER | 1 | 86
| 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_TRIGGER_SYSID | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Can anyone offer me any insight into what's going on here? These
queries ran fine under 10.2.0.3; they used the index quite happily.
If it's any help, we did run into one other peculiarity with 10.2.0.4
which might point to a lack of understanding of something fundamental
on our part.
The PK of sys_trigger is a char(32).
A very small number of our records have PK values with < 32 characters
in them e.g. "a" or "b" instead of 32 characters of hex.
Historically, we'd bind them into the query with a piece of code like
this:
String key = "a";
String sql = "DELETE from SYS_TRIGGER where sys_id = ?";
PreparedStatement ps = getConnection().prepareStatement(sql);
ps.setObject(1, a);
With 10.2.0.4 we found this didn't work and instead we had to pad the
key with 31 spaces:
String key = "a ";
String sql = "DELETE from SYS_TRIGGER where sys_id = ?";
PreparedStatement ps = getConnection().prepareStatement(sql);
ps.setObject(1, a);
I have this nagging suspicion that I'm fundamentally missing something
basic, important, and obvious here.
Can anyone help?
Dion Cho
I suspect if you have histogram on sys_id column.
Can you check it?
If histgoram is existent, can you test and compare the result of
"with" histogram and "without" histogram?
Cristian Cudizio
It is very probable that he has histograms on that column, but because
that column is primary key
having an histogram on that column, unless this being a GREAT bug,
coud only make things work well.
I've made a little test on a test environment where 'ive recently
installed 10.2.0.4 patchset and i do not
see the same problem: on my db Oracle use range scan on the index.
Remember that (unless changes on 10.2.0.4) explain plan does not make
bind peeking so you
have to execute really the query to test the execution plan.
May be that in your program it is better you use setString and not
setObject. To verify all you have
to see on v$sql and V$sql_plan after executing your program.
It seems a problem of implicit type conversion that prevents index
usage.
Regards,
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com
Mark D Powell
> > > Plan hash value: 2365230323
>
> > > -----------------------------------------------------------------------------------
> > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> > > | 0 | DELETE STATEMENT | | 1 | 86 | 1109 (1)| 00:00:14
> > > |
> > > | 1 | DELETE | SYS_TRIGGER | | | | |
> > > |* 2 | TABLE ACCESS FULL| SYS_TRIGGER | 1 | 86 | 1109
> > > (1)| 00:00:14 |
>
> > > SQL> explain plan for delete from sys_trigger where "sys_id" = 'a';
>
> > > Explained.
>
> > > SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> > > PLAN_TABLE_OUTPUT
> > > Plan hash value: 1594121083
>
> > > ---------------------------------------------------------------------------------------------------
> > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> > > |
> > > | 0 | DELETE STATEMENT | | 1 | 86 | 3 (0)|
> > > 00:00:01 |
> > > | 1 | DELETE | SYS_TRIGGER | | | | |
> > > | 2 | TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER | 1 | 86
> > > | 3 (0)| 00:00:01 |
> > > |* 3 | INDEX UNIQUE SCAN | SYS_TRIGGER_SYSID | 1 | |
> > > 2 (0)| 00:00:01 |
>
> - Show quoted text -
If there are histograms on the table then bind variable peeking could
be a problem.
Another issue: should the table be defined with a varchar2(32) column
or a character column(32) column and how is defined at the problem
site?
Your problem description of having to pad the bind variable would seem
to indicate that the definition is character where it should be
variable character.
HTH -- Mark D Powell --
Cristian Cudizio
>
> > - Show quoted text -
>
> If there are histograms on the table then bind variable peeking could
> be a problem.
>
> Another issue: should the table be defined with a varchar2(32) column
> or a character column(32) column and how is defined at the problem
> site?
> Your problem description of having to pad the bind variable would seem
> to indicate that the definition is character where it should be
> variable character.
>
> HTH -- Mark D Powell --
it seems that now bind variable peeking is a problem for a lot of
people. I do not agree.
Bind variable peeking is a means of the optimizer to get more
information about statement.
In some situations it can result in a not-optimal execution plan, but
without bind peeking
there are less chances that optimizer could elaborate an optimal
execution plan because it
has less informations about the statement.
The real problem is CBO with bind variables and bind peeking could
help and be good for a lot
of situations.
DA Morgan
I've read the other responses and feel like we are shooting in the dark
without having enough information to identify the target. Can you run
the following and report back the results?
1. Run an Explain Plan (DBMS_XPLAN output) as is.
2. Run again with dynamic sampling disabled.
3. Run again using hints to force the 10.2.0.3 plan.
4. Verify that the explain plans matche what is in gv$sqlplan.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Pat
(although thanks for the suggestion; I hadn't thought to look at that
yet). I'm getting the same results with and w/o peeking enabled.
Overnight, one of the DBA's tweaked a couple of optimizer settings to
try to reduce the table scans. Seems to have pushed our test query
from TABLE ACCESS FULL to INDEX FULL SCAN. In this particular case,
that's actually a bit faster, but it's still a sub-optimal path since
the query in question should be an INDEX UNIQUE SCAN (it's an equality
get by primary key).
At this point, I'm focussing on a typing mismatch or something
similar. I'm seeing some stuff in the plan_table_output that I'm not
understanding, but it's definately different between my bind variable
and literal case.
LITERALS (uses an INDEX_UNIQUE_SCAN on SYS_TRIGGER_SYS_ID).
SQL> explain plan for delete from sys_trigger where "sys_id" = 'a';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1594121083
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 87 | 1 (0)|
00:00:01 |
| 1 | DELETE | SYS_TRIGGER | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER | 1 | 87
| 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_TRIGGER_SYSID | 1 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access(NLSSORT(INTERNAL_FUNCTION("sys_id"),'nls_sort=''BINARY_CI''')=HEXTORAW('6100
') )
BIND VARIABLES (Uses an INDEX FULL SCAN on SYS_C008583)
SQL> variable id char
SQL> exec :id := 'a';
PL/SQL procedure successfully completed.
SQL> explain plan for delete from sys_trigger where "sys_id" = :id;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2281741627
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 87 | 355 (1)|
00:00:05 |
| 1 | DELETE | SYS_TRIGGER | | | | |
|* 2 | INDEX FULL SCAN| SYS_C008583 | 1 | 87 | 355 (1)|
00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 -
filter(NLSSORT("sys_id",'nls_sort=''BINARY_CI''')=NLSSORT(:ID,'nl
s_sort=''BINARY_CI'''))
15 rows selected.
Note the different filter conditions in each case.
Note also that in the case of the bind variable, he's using a binary
index on sys_id (that's what SYS_C008583 is), instead of the
functional index on sys_id.
What's the filter condition telling me? Are we doing something wrong
with our NLS queries? Are bind variables defeating the NLS indexes
somehow?
DA Morgan
What is jumping out at me is the following:
rows bytes cost CPU
INDEX FULL SCAN| SYS_C008583 | 1 | 87 | 355 (1)|
Good grief.
I have yet to work with 10.2.0.4 so I am wondering what the
optimizer level is set to?
select name, value
from gv$parameter
where name = 'plsql_optimize_level';
If not 2 then try setting it to 2.
What happens if you use a hint to force an index range scan?
Pat
> Puget Sound Oracle Users Groupwww.psoug.org
Optimizer level is definately on on 2 at the moment:
SQL> set linesize 132;
SQL> select name, value from gv$parameter where name =
'plsql_optimize_level';
plsql_optimize_level
2
The index full scan might be an artifact of these optimizer settings
though:
optimizer_index_caching = 90
optimizer_index_cost_adjust = 10
Both of those were set last night in an effort to mitigate the table
scans. So they likely explain why we were seeing TABLE ACCESS FULL
last night and INDEX FULL SCAN this morning.
Seems like a query hint isn't helping either. Sure looks like for some
reason in the bind variable case he thinks he cannot use the NLS
index. Just don't know why :(.
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL> variable id char;
SQL> exec :id := 'a'
PL/SQL procedure successfully completed.
SQL> explain plan for delete from sys_trigger where "sys_id" = :id;
Explained.
SQL> set linesize 132;
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2281741627
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 87 | 355 (1)|
00:00:05 |
| 1 | DELETE | SYS_TRIGGER | | | | |
|* 2 | INDEX FULL SCAN| SYS_C008583 | 1 | 87 | 355 (1)|
00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 -
filter(NLSSORT("sys_id",'nls_sort=''BINARY_CI''')=NLSSORT(:ID,'nl
s_sort=''BINARY_CI'''))
15 rows selected.
SQL> explain plan for delete /*+ INDEX(SYS_TRIGGER_SYSID) */ from
Pat
Forcing the index does, indeed, alter the plan. Doesn't look like he
can use the NLS index though, since he's doing a full scan followed by
lookups by row_id e.g. it's just a really inefficient way to do a
table scan.
SQL> explain plan for delete /*+ INDEX(sys_trigger SYS_TRIGGER_SYSID)
*/ from sys_trigger where "sys_id" = :id;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3221851421
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 87 | 3277 (1)|
00:00:40 |
| 1 | DELETE | SYS_TRIGGER | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER | 1 | 87 |
3277 (1)| 00:00:40 |
| 3 | INDEX FULL SCAN | SYS_TRIGGER_SYSID | 32505 | |
20 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter(NLSSORT("sys_id",'nls_sort=''BINARY_CI''')=NLSSORT(:ID,'nls_sort=''BINARY_CI
'''))
16 rows selected.
Shakespeare
<pat....@service-now.com> schreef in bericht
news:bead96b8-845f-47cc...@c19g2000prf.googlegroups.com...
With the risk of making a stupid remark (I don't know anything about
NLS_SORT in create_index):
Is there any reason why you do (NLSSORT("sys_id",'nls_sort=''BINARY_CI'''))
(double quotes, case DOES matter here)
and not
(NLSSORT(SYS_ID,'nls_sort=''BINARY_CI'''));?? (no double quotes, case
doesn't matter now)
To my knowledge (but again...) a column name in lower char and double quotes
does not resolve to the actual column.
I may be wrong, but your remark about a typo made me wonder....
BTW Same goes for "BINARY_CI": why those double quotes?
Shakespeare
Pat
>
> With the risk of making a stupid remark (I don't know anything about
> NLS_SORT in create_index):
> Is there any reason why you do (NLSSORT("sys_id",'nls_sort=''BINARY_CI'''))
> (double quotes, case DOES matter here)
> and not
> (NLSSORT(SYS_ID,'nls_sort=''BINARY_CI'''));?? (no double quotes, case
> doesn't matter now)
> To my knowledge (but again...) a column name in lower char and double quotes
> does not resolve to the actual column.
>
> I may be wrong, but your remark about a typo made me wonder....
>
> BTW Same goes for "BINARY_CI": why those double quotes?
>
> Shakespeare
No worries at all about pointing out a stupid error on my part.
Wouldn't be the least bit shocked if that isn't the underlying
problem, and if it is I'll throw a party celebrating my own stupidity
as long as it fixes the problem :).
The reason sys_id is being quoted is that the actual column name is
(lower case) sys_id.
This is an old mySQL app that got ported to Oracle and the column
names are all lower cased there since thats "more or less" the norm in
mySQL land.
Hence when it came to oracle, the column names were, unfortunately
still lower cased, and hence the necessity of quoting all references
to them, which makes working with them on the CLI rather obnoxious.
SQL> describe sys_trigger;
Name Null? Type
----------------------------------------- --------
----------------------------
sys_id NOT NULL CHAR(32)
name VARCHAR2(40)
trigger_class VARCHAR2(40)
state NUMBER(38)
next_action TIMESTAMP(6)
document VARCHAR2(40)
document_key CHAR(32)
job_id CHAR(32)
job_context VARCHAR2(4000)
calendar CHAR(32)
log NUMBER(1)
maintenance NUMBER(1)
trigger_type NUMBER(38)
repeat TIMESTAMP(6)
run_time TIMESTAMP(6)
run_dayofweek NUMBER(38)
run_dayofmonth NUMBER(38)
run_weekinmonth NUMBER(38)
run_month NUMBER(38)
application VARCHAR2(40)
script CLOB
system_id VARCHAR2(100)
claimed_by VARCHAR2(100)
sys_updated_by VARCHAR2(40)
sys_updated_on TIMESTAMP(6)
sys_created_by VARCHAR2(40)
sys_created_on TIMESTAMP(6)
sys_mod_count NUMBER(38)
Shakespeare
"Pat" <pat....@service-now.com> schreef in bericht
news:02a7a4df-73f5-4aef...@8g2000hsu.googlegroups.com...
Ok, I see. In that "case" the quotes should be there, and since it worked in
previous versions it should not be the cause of the problem I think. It's
just that I have seen these double quotes so many times in queries where
they should not be there at all...
Shakespeare
mathewbutler
What is the status of the index?
Pat
> What is the status of the index?
Looks valid as far as I can tell. Am I missing something?
SQL> select index_name, index_type, status, uniqueness from
user_indexes where index_name like 'SYS_TRIGGER%';
INDEX_NAME INDEX_TYPE STATUS UNIQUENES
------------------------------ --------------------------- --------
---------
SYS_TRIGGER_INDEX1 NORMAL VALID NONUNIQUE
SYS_TRIGGER_INDEX3 FUNCTION-BASED NORMAL VALID NONUNIQUE
SYS_TRIGGER_REF6 FUNCTION-BASED NORMAL VALID NONUNIQUE
SYS_TRIGGER_REF8 FUNCTION-BASED NORMAL VALID NONUNIQUE
SYS_TRIGGER_SYSID FUNCTION-BASED NORMAL VALID UNIQUE
Dion Cho
Bind peeking has nothing to do with this abnormality because explain
plan does not utilize bind peeking.
If you're finding quick dirty solution, INDEX_RS hint(added as of
10.2.0.3) would be a solution.
If you have much more time to spend on this problem, you had bettern
do a full investigation, including
- statistics change including histogram
- optimizer parameter change(including hidden parameter) between
10.2.0.3 and 10.2.0.4
mathewbutler
No, not missing anything.
As far as I can see, in your original post the optimizer correctly
identifies that this will return a single row, but chooses an access
path with the greater cost ( ignoring the bind/literal difference ).
So, something about the bind, or the index is throwing it off.
Maybe consider digging through a 10053 trace to check the optimizer
calculations, and raise this with Oracle support.
Might be worth checking the runtime behaviour of the query by calling
dbms_xplan against V$SQL_PLAN, in case this shows different runtime
behaviour or costs.
When you resolve your issue, will you please post a summary to this
group?
Cheers,
Mat.
Vladimir M. Zakharychev
I tried this on my test 10.2.0.4/Win32 instance:
CREATE TABLE CI_TEST(X CHAR(32));
CREATE UNIQUE INDEX UQ$CI_TEST ON
CI_TEST(NLSSORT(X,'NLS_SORT=''BINARY_CI''');
Inserted 832 rows into this table with values: 'A' to 32x'A', 'B' to
32x'B', ..., 'Z' to 32x'Z'.
SELECT X FROM CI_TEST WHERE X=:B1;
:B1 was initialized to 'x';
SQL_ID a779vscy3axhc, child number 0
-------------------------------------
SELECT X FROM CI_TEST WHERE X=:B1
Plan hash value: 1574216700
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CI_TEST | 1 | 34 |
2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UQ$CI_TEST | 1 | |
1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("CI_TEST"."SYS_NC00002$"=NLSSORT(:B1,'nls_sort=''BINARY_CI'''))
Note
-----
- dynamic sampling used for this statement
Note the SYS_NC00002$ in predicate information - this is our case-
insensitive virtual column. Also note that I did not create a PRIMARY
KEY constraint here. This prevents us from creating foreign keys on
this table, so let's create the primary key:
ALTER TABLE CI_TEST ADD CONSTRAINT PK$CI_TEST#X PRIMARY KEY (X);
Table altered.
CREATE TABLE CI_REF (X CHAR(32), Y NUMBER,
CONSTRAINT FK$CI_REF#X$CI_TEST#X FOREIGN KEY (X) REFERENCES
CI_TEST(X));
Table created.
ALTER SYSTEM FLUSH SHARED_POOL;
<re-executed the select statement here>
The SQL_ID for the statement didn't change, so did the plan. It still
uses the virtual column and the case-insensitive index. However, my
COMPATIBLE is set to '10.2.0.3' and my OPTIMIZER_FEATURES_ENABLE is
'10.1.0'. Let's see what happens if I set them both to '10.2.0.4' and
bounce the instance... Nothing. Same picture. The only thing that's
left is gathering stats on the table with histograms and see if that
changes anything... Nope. The unique index is properly used again, the
only difference is that this time the plan doesn't say anything about
dynamic sampling.
So it doesn't seem to be some specific new CBO behavior or bug
introduced in 10.2.0.4. However, my experiment may not reproduce your
exact setup. A 10053 trace taken on your system might shed some light
on why the CBO refuses to use your function-based index when it seems
natural choice. You might want to drop and recreate the
SYS_TRIGGER_SYSID index (make sure it's UNIQUE,) and see if things
return back to normal. If the issue persists, I'd suggest that you
open a SR with Oracle Support, they might suggest how to diagnose it
properly. This can well be a regression after all...
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Mark D Powell
> > > > > Plan hash value: 2365230323
>
> > > > > ------------------------------------------------------------------------------------
> > > > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> > > > > | 0 | DELETE STATEMENT | | 1 | 86 | 1109 (1)| 00:00:14
> > > > > |
> > > > > | 1 | DELETE | SYS_TRIGGER | | | | |
> > > > > |* 2 | TABLE ACCESS FULL| SYS_TRIGGER | 1 | 86 | 1109
> > > > > (1)| 00:00:14 |
>
> > > > > SQL> explain plan for delete from sys_trigger where "sys_id" = 'a';
>
> > > > > Explained.
>
> > > > > SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> > > > > PLAN_TABLE_OUTPUT
> > > > > Plan hash value: 1594121083
>
> > > > > ----------------------------------------------------------------------------------------------------
> > > > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> > > > > |
> > > > > | 0 | DELETE STATEMENT | | 1 | 86 | 3 (0)|
> > > > > 00:00:01 |
> > > > > | 1 | DELETE | SYS_TRIGGER | | | | |
> > > > > | 2 | TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER | 1 | 86
> > > > > | 3 (0)| 00:00:01 |
> > > > > |* 3 | INDEX UNIQUE SCAN | SYS_TRIGGER_SYSID | 1 | |
> > > > > 2 (0)| 00:00:01 |
>
> > > - Show quoted text -
>
> > If there are histograms on the table then bind variable peeking could
> > be a problem.
>
> > Another issue: should the table be defined with a varchar2(32) column
> > or a character column(32) column and how is defined at the problem
> > site?
> > Your problem description of having to pad the bind variable would seem
> > to indicate that the definition is character where it should be
> > variable character.
>
> > HTH -- Mark D Powell --
>
> it seems that now bind variable peeking is a problem for a lot of
> people. I do not agree.
> Bind variable peeking is a means of the optimizer to get more
> information about statement.
> In some situations it can result in a not-optimal execution plan, but
> without bind peeking
> there are less chances that optimizer could elaborate an optimal
> execution plan because it
> has less informations about the statement.
>
> The real problem is CBO with bind variables and bind peeking could
> help and be good for a lot
> of situations.
>
> Regards,
>
> - Show quoted text -
When were the statistics last updated? You said there are about 40K
rows in the table so how many does dba_tables.num_rows show?
Anurag Varma
Vladimir M. Zakharychev wrote:
> On Apr 7, 10:00 am, pat.ca...@service-now.com wrote:
-snip-
--snip--
>
> Regards,
> Vladimir M. Zakharychev
I did seem to have reproduced the OP's issue and it seems to be
present in versions
higher than 10.2.0.4 and 11.1.0.6
Here is my test case:
ALTER SESSION SET NLS_COMP=LINGUISTIC ;
ALTER SESSION SET NLS_SORT=BINARY_CI;
create table test (a char(10), b number, c varchar2(10));
create index test_a_idx on test (NLSSORT(a,'NLS_SORT=BINARY_CI'));
create index test_c_idx on test (NLSSORT(c,'NLS_SORT=BINARY_CI'));
insert into test select
dbms_random.string('A',trunc(dbms_random.value(1,10))), rownum,
dbms_random.string('A',trunc(dbms_random.value(1,10))) from
all_tables;
commit;
set autotrace traceonly exp
select * from test where a = 'D';
var v varchar2
exec :v := 'D';
var vc char(10)
exec :vc := 'D';
select * from test where a = :v;
select * from test where a = :vc;
Issue seems to happen only when the column is defined as CHAR
and the bind variable being used has a datatype of varchar2 (i.e.
datatype mismatch).
In 10.2.0.3.
select * from test where a = :v;
Execution Plan
----------------------------------------------------------
Plan hash value: 3908671039
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 416 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 13 | 416 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_A_IDX | 5 | |
1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(NLSSORT(INTERNAL_FUNCTION("A"),'nls_sort=''BINARY_CI''')=NLSSORT(:V,
'nls_sort=''BINARY_CI'''))
In 10.2.0.4.
select * from test where a = :v;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 544 | 5 (0)|
00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 17 | 544 | 5 (0)|
00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(NLSSORT("A",'nls_sort=''BINARY_CI''')=NLSSORT(:V,'nls_sort
=''BINARY_CI'''))
*** If the bind variable is CAST as char(10) .. then it uses the index
properly.
select * from test where a = cast(:v as char(10));
Execution Plan
----------------------------------------------------------
Plan hash value: 3952958149
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 544 |
4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 17 | 544 |
4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_A_IDX | 7 | |
1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(NLSSORT(INTERNAL_FUNCTION("A"),'nls_sort=''BINARY_CI''')=NLSSORT(CAS
T(:V AS char(10)),'nls_sort=''BINARY_CI'''))
Anurag
DA Morgan
Well done.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Holger Baer
>>What is the status of the index?
>
>
> Looks valid as far as I can tell. Am I missing something?
>
Apart from the fact that the index in question is called sys_id and doesn't show
up in this list? ;-)
> SQL> select index_name, index_type, status, uniqueness from
> user_indexes where index_name like 'SYS_TRIGGER%';
>
> INDEX_NAME INDEX_TYPE STATUS UNIQUENES
> ------------------------------ --------------------------- --------
> ---------
> SYS_TRIGGER_INDEX1 NORMAL VALID NONUNIQUE
> SYS_TRIGGER_INDEX3 FUNCTION-BASED NORMAL VALID NONUNIQUE
> SYS_TRIGGER_REF6 FUNCTION-BASED NORMAL VALID NONUNIQUE
> SYS_TRIGGER_REF8 FUNCTION-BASED NORMAL VALID NONUNIQUE
> SYS_TRIGGER_SYSID FUNCTION-BASED NORMAL VALID UNIQUE
>
Cheers,
Holger
Shakespeare
"Holger Baer" <baer@dont_ask.com> schreef in bericht
news:fthtp2$5j2$1...@news.belwue.de...
I Thought it was sys_trigger_sysid...
Shakespeare
Dion Cho
This bug was recorded as unique on 9.2.0.7 only, but seems that as of
10.2.0.4, the buggy code was re-codeded. :)
Just a guesswork.
Seems that OP should file a bug report.
Dion Cho
Pat
Thanks for all the help; think I will bug this since. I'll probably
use Anurag's test case though since I think it's better than mine :).