Skip to Main Content
  • Questions

  • 11g Release 2 - Compression and et al.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 24, 2009 - 2:10 pm UTC

Last updated: July 01, 2011 - 8:21 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Few Questions on 11gR2


1) Currently , we have CURSOR_SHARING to FORCE in 10.2.0.4 ? We are evaluating to migrate it to 11g r2 . What will be the impact of Adaptive cursor sharing if the cursor sharing is set to FORCE?
( I have been explaining the cons of this setting like SQL injection ,
improper usage of bind variables ... Still I have long way to go in turning them
to set it to EXACT)

2) How TimesTen ( In memory caching ) is different from resultset caching ? Are they complimentary ?

3) What is the difference between Services in Pre-11gR2 and Server Pool in 11g R2?

4) In 11g R2 , Is Advanced Compression only for table Or Is it for Index too ?
I was told by one of the Oracle Sales Rep , it is for indexes as well.
But I don't find any documentation supporting that.


Thanks

and Tom said...

1) you have a huge security hole in your application. Rather than thinking about migrating (I'd love it if you were on the latest software personally), you should be fixing this application.

IT IS BROKEN
IT IS A SECURITY RISK

Stop your upgrade, fix the application.

I'm dead serious. This is silly. It should even require a discussion in the year 2009.

Having cursor sharing set to force in 11g will change things from the way they worked in 9i and 10g.

In 9/10 - if you set cursor sharing to force, on the first hard parse - we would strip out all literals and replace them with binds. Then we would optimize the query using those binds (we'd peek at those binds). If the binds you used the first time during the hard parse led us to a full scan plan - that is what we would use. If the binds you used the first time during the hard parse led us to an index range scan - that is what we would use. And we would use that SINGLE plan for everyone that came after us.


In 11g - if you set cursor sharing to force, on the first hard parse - we would again strip out all literals and replace them with binds. Then we would optimize the query using those binds - but if we realized that a different set of binds would lead to a different plan, we would flag that query as being sensitive to the bind values. So, if the first hard parse said "do an index range scan" because the binds you used were very selective and we had statistics (histograms for example) that showed us that for different binds we would pick a different plan (say a full scan) - we would flag that query. As we execute the index range scan plan, we would be evaluating how well the ACTUAL WORK performed matched up with the WORK WE EXPECTED to perform (the plan). If the actual work diverged from the expected work by a large margin, we would realize we made a mistake coming up with a single plan and we would generate new plans for different bind inputs.

That is adaptive cursor sharing
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-sqlplanmanagement.html

So, what impact would that have on you? You might find query performance to be superior in 11g (since we are not doing a simple one plan fits all, we adapt if we made a mistake) - but you also might find your shared pool needs to be larger since we might create a few child cursors for these different plans and you didn't have that happening in 9/10.



2) result set caching is like a "just in time materialized view", you run the query - we save the answer in memory. The query that first runs does IO and everything else. If the space in memory is needed for something else, we get rid of that cached result and the next guy to run it does the IO's and the CPU work to build the result set. In short - there are lots of latencies (network, disk, CPU) that can be involved - you don't know execution to execution how long the query will take.

times ten, in memory database cache - the entire database is always in ram, it is indexed in ram (no rowids, pointers to memory). You do not do physical IO. When you run a query - the response time is 100% a function of CPU resources - the network (you typically put the database cache right next to your application) and disk response times are not part of the equation. If you have sufficient free cpu, you know exactly how long the query will take from execution to execution.


result set cache could take a 1,000,000 row set of inputs and turn it into a 100 row set of inputs - such that the first execution takes a while - but subsequent executions are incredibly fast.

times ten would process the 1,000,000 row set of inputs each time, being consistent.


There are pro's and con's of each. Think of times ten mostly as a way to eliminate latencies that make query response times unpredicable.


3) I think you did not read
http://www.flickr.com/photos/tkyte/4033155188/sizes/o/

even though you checked a box saying you did....
see #7?


a service is something a client uses to connect to a database. A service might have one or more databases that it can use. A database might provide one or more services. It is just a mapping a client uses to find it's way to a database capable of answering the question it has.

server pools are
http://docs.oracle.com/cd/E11882_01/install.112/e10813/srvpool.htm#sthref316

4) You do not need advanced compression to do index key compression. Indexes are compressed only by using index key compression and that is a FEATURE (for free) that has been in the database since version 8.1.5




Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 24, 2009 - 3:06 pm UTC

Thanks Tom .

I did not see Question 3 as a candiate for multi-part question . Sorry about that

A reader, November 24, 2009 - 4:10 pm UTC

Is all 1,000,000 rows are cache in result cache or it would cache as needed ?


Thanks

Tom Kyte
November 25, 2009 - 12:01 pm UTC

I wrote:

... result set cache could take a 1,000,000 row set of inputs and turn it into a 100 row set of inputs - such that the first execution takes a while - but subsequent executions are incredibly fast. ...


so in the result cache, just the 100 rows would be in the cache, it remembers the answer - not the original 1,000,000 rows that made the answer.

the security hole?

A reader, November 24, 2009 - 6:37 pm UTC

Frankly, I've been a little out of the Oracle biz for a .ittle while and my Oracle knowledge has gone down, rather than up recently. What is the giant security hole in his application? The CURSOR_SHARING=FORCE in 10gR2? Can you elaborate? Would appreciate it.
Tom Kyte
November 25, 2009 - 12:07 pm UTC

if you need cursor_sharing = force that means you are not using bind variables which means the application is generating unique sql via string concatenation, eg:


my_procedure ( x , y , z )
{
my_sql := 'select * from emp where ename = ' + x + ' and .....
}


which means - X can become part of the SQL statement itself - and if X can change the sql statement - that is bad.

That is called SQL Injection.
https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

cursor_sharing=force indicates that you are definition NOT binding, therefore you are definitely subject to SQL Injection with a middle tier application.

Not to use Bind variables is the security hole

Nicosa, November 25, 2009 - 4:22 am UTC

Basically, if you don't bind then you concatenate values into strings that you execute immediate. Hence it is possible to 'inject' SQL code that you don't want to execute.

Search this site for sql injection.

A reader, November 25, 2009 - 9:46 am UTC

Tom,
Can you please elaborate on how cursor_sharing=force poses a security risk? If cursor_sharing=similar, then is it also a security risk?

Thanks...
Tom Kyte
November 27, 2009 - 3:47 pm UTC

cursor sharing does not have a security risk in itself.

It is the fact that it is necessary that tells me "you have a security issue"

read my response two reviews up that starts with

"if you need cursor_sharing = force that means you are not using bind variables"

Over Binding

A reader, November 28, 2009 - 9:08 am UTC

In Pre-Oracle 11g , we had the issue of "over binding" because of the setting in SIMILAR / FORCE for CURSOR_SHARING.

By reading your response to adaptive cursor sharing
to the question #1 in the orginial question , I assume this issue ( of over binding) is minimal , . Is my undestanding correct ?

<quote>
....
but if we realized that a different set of binds would lead to a different plan, we would flag that query as being sensitive to the bind values
...
</quote>
Tom Kyte
November 28, 2009 - 2:02 pm UTC

In 11g with adaptive cursor sharing, the problem of "always binding, over binding" might be solved

BUT

and this is a huge BUT

BUT you still have a security hole the size of the space shuttle here - that you have to use cursor sharing = force means the application is still in major need of work.

A reader, December 03, 2009 - 9:00 am UTC

Dear Sir,

Good Day

Can you please explain the difference between cursor_sharing=similar and Adaptive cursor sharing?

Thanks


Tom Kyte
December 04, 2009 - 4:03 pm UTC

cursor_sharing similar is a very aggressive sloppy adaptive cursor sharing. That is one way to think of it.

cursor sharing similar will look at the rewritten sql and if ANY of the binds are possibly "not safe to share", it will set up a separate child cursor for every query input. For example:

ops$tkyte%ORA11GR1> /*
ops$tkyte%ORA11GR1> drop table t;
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create table t
ops$tkyte%ORA11GR1> as
ops$tkyte%ORA11GR1> select case when rownum = 1 then 1 else 99 end id,
ops$tkyte%ORA11GR1>        all_objects.*
ops$tkyte%ORA11GR1>   from all_objects
ops$tkyte%ORA11GR1> /
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create index t_idx on t(id);
ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns', estimate_percent=>100 );
ops$tkyte%ORA11GR1> */


so, some very skewed data - and we have statistics that show that ID=1 should use an index and ID=99 should full scan...

we use cursor sharing with inputs of 1, 2, 3, 4, and 99 - five sets of inputs...

ops$tkyte%ORA11GR1> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR1> alter session set cursor_sharing=similar;

Session altered.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 1;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 99;

COUNT(SUBOBJECT_NAME)
---------------------
                  535

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 2;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 3;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 4;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 1;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 99;

COUNT(SUBOBJECT_NAME)
---------------------
                  535

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 2;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 3;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = 4;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select sql_id, sql_text, is_bind_aware, is_bind_sensitive from v$sql where sql_text like 'select count(subobject_name) from t where id = %';

SQL_ID        SQL_TEXT                                           I I
------------- -------------------------------------------------- - -
b1yda827jbjw9 select count(subobject_name) from t where id = :"S N Y
              YS_B_0"

b1yda827jbjw9 select count(subobject_name) from t where id = :"S N Y
              YS_B_0"

b1yda827jbjw9 select count(subobject_name) from t where id = :"S N Y
              YS_B_0"

b1yda827jbjw9 select count(subobject_name) from t where id = :"S N Y
              YS_B_0"

b1yda827jbjw9 select count(subobject_name) from t where id = :"S N Y
              YS_B_0"


see, five inputs, five plans in v$sql - in fact if you said "id = 20", it would add another, "id = 25", add another and so on - every unique set of binds against id will generate yet another plan.


adaptive cursor sharing will be different. Where as similar causes a new plan IMMEDIATELY upon seeing a different bind variable against ID - similar will wait - to see if it causes a problem. And even then, it'll only generate as many plans as it needs:

ops$tkyte%ORA11GR1> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA11GR1> variable x number
ops$tkyte%ORA11GR1> exec :x := 1

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 99

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                  535

ops$tkyte%ORA11GR1> exec :x := 2

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 3

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 4

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 1

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 99

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                  535

ops$tkyte%ORA11GR1> exec :x := 2

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 3

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> exec :x := 4

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select count(subobject_name) from t where id = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA11GR1> select sql_id, sql_text, is_bind_aware, is_bind_sensitive from v$sql where sql_text = 'select count(subobject_name) from t where id = :x';

SQL_ID        SQL_TEXT                                                    I I
------------- ----------------------------------------------------------- - -
as2936sf1fnqu select count(subobject_name) from t where id = :x           N Y
as2936sf1fnqu select count(subobject_name) from t where id = :x           Y Y
as2936sf1fnqu select count(subobject_name) from t where id = :x           Y Y
as2936sf1fnqu select count(subobject_name) from t where id = :x           Y Y


there are three that are bind aware - the first one that is "not bind aware" is there ready to be aged out (no one will use it) - the three that are left

o one of them is for id=1
o one of them is for id=99
o the third one is for everything else - id=2,3,4, ......


adaptive cursor sharing will wait till it detects a problem, and then - it'll generate as few cursor images as it can.


Adaptive cursor sharing is a better cursor sharing similar (for so many reasons, not least of all - it works without having to set cursor_sharing away from exact!!!! any setting of cursor sharing other than exact shows immediately the developers didn't have a clue)

Excellent as Always - you are great

A reader, December 04, 2009 - 4:11 pm UTC

Thanks For detail Explanation


A reader, December 10, 2009 - 3:37 pm UTC

In your above example when demostrating "Adaptive cursor sharing" you set cursor_sharing='exact'
instead of 'Adaptive' or its a default behaviour in 11g

Tom Kyte
December 10, 2009 - 3:55 pm UTC

there is no 'cursor sharing adaptive'

cursor_sharing should always be set to exact (if it isn't, that means the developers did you wrong, they have a bug in their code, they didn't use bind variables when they should have - they have a HUGE SECURITY BUG and a severe performance bug).


All cursors are 'adaptive' in 11g. Regardless of the cursor_sharing setting. Cursor_sharing just controls whether we try to mitigate (mitigate - not solve, not remove) the performance issue as well as we can (not nearly as well as the developers can). It does nothing for the security hole, it just might help you scale up a little bit while the developers fix their bug.

Alexander, January 07, 2010 - 3:24 pm UTC

Is anyone successfully installed 11gR2? I can't get the OUI to work right. The pop up windows flicker out of control. There's a doc in metalink about it that I can't find again that was talking about X11 emulator things. I'm using hummingbird exceed, and putty. The target platform is RedHat Linux 5.3. I changed my putty setting to enable X11 forwarding to localhost but that didn't help.
Tom Kyte
January 11, 2010 - 8:45 pm UTC

I used VNC many times and didn't have any issues personally.

Yes, at least one person has installed 11gR2.

Alexander, January 11, 2010 - 9:08 pm UTC

It's a bug with hummingbird on vista. I didn't know what vnc was but someone set me up with that and it works fine.

An alternative

Chuck Jolley, January 12, 2010 - 5:13 pm UTC

On windows, try putty and xming.
Xming is free and I used it to run the 11gR2 installer on my PC against OEL5 64bit twice in the last week.
Worked just fine in xp anyway.
Kind of neat having a bunch of independent linux windows open on your XP desktop.

Different bind variable using same plan

Lim Chee Yong, June 30, 2011 - 2:00 am UTC 

SQL> create table test as select dba_objects.* , 'Y' ind from dba_objects ;

Table created.

SQL> insert into test select dba_objects.* , 'N' ind from dba_objects where rownum = 1 ;

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_test on test (ind);

Index created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)
 IND                                                CHAR(1)

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select max(timestamp) from test where ind ='N';

MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=55 Card=1 Bytes=22
          )

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=55 Card=7014
           Bytes=154308)



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        197  consistent gets
          0  physical reads
          0  redo size
        240  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select max(timestamp) from test where ind ='N';

MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=22)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2
          Card=1 Bytes=22)

   3    2       INDEX (RANGE SCAN) OF 'IDX_TEST' (INDEX) (Cost=1 Card=
          1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        197  consistent gets
          0  physical reads
          0  redo size
        244  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select max(timestamp) from test where ind ='Y';

MAX(TIMESTAMP)
-------------------
2011-06-30:13:53:35

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=55 Card=1 Bytes=22
          )

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=55 Card=1402
          6 Bytes=308572)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        197  consistent gets
          0  physical reads
          0  redo size
        246  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     5000
session_cached_cursors               integer     50
SQL>
SQL>


===============
Question 
1. Am I right to say that exec dbms_stats.gather_schema_stats(user) statement won't collect histogram for column IND.
===============


SQL> variable v_ind varchar2
SQL> exec :v_ind :='N';

PL/SQL procedure successfully completed.

SQL> print :v_ind

V_IND
--------------------------------
N

SQL> select max(timestamp) from test where ind = :v_ind ;

MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=54 Card=1 Bytes=22
          )

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=54 Card=7014
           Bytes=154308)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        246  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> exec :v_ind :='Y';

PL/SQL procedure successfully completed.

SQL> select max(timestamp) from test where ind = :v_ind ;

MAX(TIMESTAMP)
-------------------
2011-06-30:13:53:35

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=54 Card=1 Bytes=22
          )

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=54 Card=7014
           Bytes=154308)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        216  consistent gets
          0  physical reads
          0  redo size
        246  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


===============
Question 
2. I am expecting for variable :v_ind ='N' will use index. Why the above not using ? 
===============

Tom Kyte
July 01, 2011 - 8:21 am UTC

this tells the story "why":

SQL> select max(timestamp) from test where ind ='N';

MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=55 Card=1 Bytes=22
          )

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=55 Card=7014
           Bytes=154308)



see the estimated card= value? It is 7014, not 1.

You do not have detailed histograms on the column
You did not specify to compute


Without histograms, we will not see the skew.

If you sample - and only have one value for some column - there is a good chance we would miss it. We don't know there is just one value for N so we estimate.



You should not use explain plan (like autotrace does) when looking at bind peeking things either - explain plan does NOT bind peek, the autotrace plan isn't going to be very good for you. You'll want to look into v$sql_plan - which will be easy once you get to 10g and above with dbms_xplan.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.