f



Oracle Optmizer does not use the right index (Oracle 9.2.0.6)

Hello,

Not sure whether this problem has been discussed earlier in this
forum. Can someone suggest a workaround to the following problem which
can be replicated by following the sequence of detailed steps I have
given below. In summary, if you have a table with two composite
indexes, both with the same fields, but one of them has a field in
descending order, then the Optimizer loses track of the correct index
to use for a specific query.

Thanks.

A.J.Andrews

See details below:
----------------

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create table tab1 as select * from all_objects;

Table created.

SQL> set autotrace traceonly explain;
SQL> create index tab1ind1 on tab1(owner, object_name);

Index created.

SQL> analyze table tab1 compute statistics;

Table analyzed.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

SQL> select * from tab1 order by owner, object_name;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
Bytes=372324)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
Card=4899 Bytes=372324)

   2    1     INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
Card=4899)

===> Note here that the correct index tab1ind1 is being used for the
query.


SQL> create index tab1ind2 on tab1(owner, object_name desc);

Index created.

SQL> analyze table tab1 compute statistics;

Table analyzed.

SQL> select * from tab1 order by owner, object_name desc;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
Bytes=372324)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
Card=4899 Bytes=372324)

   2    1     INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48
Card=4899)

===> Again, the correct index tab1ind2 is being used.


SQL> select * from tab1 order by owner, object_name;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899
Bytes=372324)

   1    0   SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324)
   2    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899
Bytes=372324)

===> But, at this time the Optimizer has forgotten about the existence
of tab1ind1 and is going for a full table scan and a sort! Wrong
choice!  Should be using index tab1ind1.


SQL> drop index tab1ind2;

Index dropped.

SQL> select * from tab1 order by owner, object_name;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
Bytes=372324)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
Card=4899 Bytes=372324)

   2    1     INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
Card=4899)

===> Once tab1ind2 is dropped, the Optimizer remembers the existence
of tab1ind1 and chooses the index scan for the very same query for
which it used a table scan earlier.


This problem is easily reproducible with any two indexes on a table,
with both indexes being on the same set of columns, but with one of
the indexes having a "desc" requirement on at least one of the columns.

0
alanolya (2)
3/15/2007 7:45:41 PM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

4 Replies
932 Views

Similar Articles

[PageSpeed] 56

On Mar 15, 3:45 pm, alano...@invera.com wrote:
> Hello,
>
> Not sure whether this problem has been discussed earlier in this
> forum. Can someone suggest a workaround to the following problem which
> can be replicated by following the sequence of detailed steps I have
> given below. In summary, if you have a table with two composite
> indexes, both with the same fields, but one of them has a field in
> descending order, then the Optimizer loses track of the correct index
> to use for a specific query.
>
> Thanks.
>
> A.J.Andrews
>
> See details below:
> ----------------
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006
>
> Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.6.0 - Production
>
> SQL> create table tab1 as select * from all_objects;
>
> Table created.
>
> SQL> set autotrace traceonly explain;
> SQL> create index tab1ind1 on tab1(owner, object_name);
>
> Index created.
>
> SQL> analyze table tab1 compute statistics;
>
> Table analyzed.
>
> SQL> alter session set optimizer_mode=first_rows;
> Session altered.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
>    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
>    2    1     INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
> Card=4899)
>
> ===> Note here that the correct index tab1ind1 is being used for the
> query.
>
> SQL> create index tab1ind2 on tab1(owner, object_name desc);
>
> Index created.
>
> SQL> analyze table tab1 compute statistics;
>
> Table analyzed.
>
> SQL> select * from tab1 order by owner, object_name desc;
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
>    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
>    2    1     INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48
> Card=4899)
>
> ===> Again, the correct index tab1ind2 is being used.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899
> Bytes=372324)
>
>    1    0   SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324)
>    2    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899
> Bytes=372324)
>
> ===> But, at this time the Optimizer has forgotten about the existence
> of tab1ind1 and is going for a full table scan and a sort! Wrong
> choice!  Should be using index tab1ind1.
>
> SQL> drop index tab1ind2;
>
> Index dropped.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
>    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
>    2    1     INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
> Card=4899)
>
> ===> Once tab1ind2 is dropped, the Optimizer remembers the existence
> of tab1ind1 and chooses the index scan for the very same query for
> which it used a table scan earlier.
>
> This problem is easily reproducible with any two indexes on a table,
> with both indexes being on the same set of columns, but with one of
> the indexes having a "desc" requirement on at least one of the columns.

FIRST_ROWS has a tendency to prefer index access, even if the index
access that is selected is less than ideal.  You should be using
DBMS_STATS.  With that said, a test on Oracle 10.2.0.2:

CREATE TABLE T2 AS
SELECT
  *
FROM
  ALL_OBJECTS;

Table created.

COMMIT;

Commit complete.

CREATE INDEX T2_IND1 ON T2(TABLE_OWNER_HERE, OBJECT_NAME);

Index created.

EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T2',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;

Session altered.

SELECT
  *
FROM
  T2
ORDER BY
  OWNER,
  OBJECT_NAME;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));

SQL_ID  9jf1t5g0sgzz4, child number 0
-------------------------------------
SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER  BY    OWNER,    OBJECT_NAME

Plan hash value: 1199450076

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-
Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |      2 |  13945 |
27890 |00:00:01.12 |   12286 |    210 |
|   2 |   INDEX FULL SCAN           | T2_IND1 |      2 |  13945 |
27890 |00:00:00.55 |     410 |    136 |
----------------------------------------------------------------------------------------------------------

CREATE INDEX T2_IND2 ON T2(TABLE_OWNER_HERE, OBJECT_NAME DESC);

Index created.

EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T2',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SELECT
  *
FROM
  T2
ORDER BY
  OWNER,
  OBJECT_NAME DESC;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));

--------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |
--------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |  13945 |
|   2 |   INDEX FULL SCAN           | T2_IND2 |  13945 |

SELECT
  *
FROM
  T2
ORDER BY
  OWNER,
  OBJECT_NAME;

SQL_ID  9jf1t5g0sgzz4, child number 0
-------------------------------------
SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER  BY    OWNER,    OBJECT_NAME

Plan hash value: 2552596561

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-
Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |  13945 |  13945 |
00:00:00.15 |     182 |     37 |  1824K|   650K|     1/0/0|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  13945 |  13945 |
00:00:00.11 |     182 |     37 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------
So, we are consistent so far.

ALTER SYSTEM FLUSH SHARED_POOL;

DROP INDEX T2_IND2;

Let's try a test:
SELECT /*+ ALL_ROWS */
  *
FROM
  T2
ORDER BY
  OWNER,
  OBJECT_NAME;

SQL_ID  b3myw3j5ufp1h, child number 0
-------------------------------------
SELECT /*+ ALL_ROWS */ * FROM T2 ORDER  BY    OWNER,    OBJECT_NAME

Plan hash value: 2552596561

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-
Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |  13945 |  13945 |
00:00:00.25 |     182 |     37 |  1824K|   650K|     1/0/0|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  13945 |  13945 |
00:00:00.21 |     182 |     37 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

That plan and statistics look to be very similar to the one that we
received when both indexes were available to be used by the cost based
optimizer.  Note how the execution time is 0.25 seconds (or 0.15
seconds when there were two indexes) compared to the 1.12 seconds
required when the T2_IND1 index was used.  Is it possible that the
cost based optimizer recognized that the time required to continue
analyzing the various access methods would exceed the time required to
return the rows, so the optimizer decided to run with the best plan
that it found so far?

Let's look at a 10053 trace for the case when there were two indexes
available:
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T2  Alias: T2
    #Rows: 13945  #Blks:  190  AvgRowLen:  105.00
Index Stats::
  Index: T2_IND1  Col#: 1 2
    LVLS: 1  #LB: 67  #DK: 12870  LB/K: 1.00  DB/K: 1.00  CLUF:
5859.00
  Index: T2_IND2  Col#: 1 14
    LVLS: 1  #LB: 69  #DK: 12870  LB/K: 1.00  DB/K: 1.00  CLUF:
5870.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T2  Alias: T2
    Card: Original: 13945  Rounded: 13945  Computed: 13945.00  Non
Adjusted: 13945.00
  Access Path: TableScan
    Cost:  38.54  Resp: 38.54  Degree: 0
      Cost_io: 38.00  Cost_cpu: 6791624
      Resp_io: 38.00  Resp_cpu: 6791624
  Best:: AccessPath: TableScan
         Cost: 38.54  Degree: 1  Resp: 38.54  Card: 13945.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  T2[T2]#0
ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort:     187 Row size:          109 Total
Rows:          13945
      Initial runs:         2 Merge passes:        1 IO Cost /
pass:        102
      Total IO sort cost: 289      Total CPU sort cost: 25948637
      Total Temp space used: 3384000
***********************
Best so far: Table#: 0  cost: 329.5793  card: 13945.0000  bytes:
1255050
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
    SORT resource      Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort:     187 Row size:          109 Total
Rows:          13945
      Initial runs:         2 Merge passes:        1 IO Cost /
pass:        102
      Total IO sort cost: 289      Total CPU sort cost: 25948637
      Total Temp space used: 3384000
Final - First Rows Plan:  Best join order: 1
  Cost: 329.5793  Degree: 1  Card: 13945.0000  Bytes: 1255050
  Resc: 329.5793  Resc_io: 327.0000  Resc_cpu: 32740260
  Resp: 329.5793  Resp_io: 327.0000  Resc_cpu: 32740260

The total expected cost was 329 - 289 of which was caused by the sort.

Now, let's look at the case where only the first index exists
(FIRST_ROWS):
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T2  Alias: T2
    #Rows: 13945  #Blks:  190  AvgRowLen:  105.00
Index Stats::
  Index: T2_IND1  Col#: 1 2
    LVLS: 1  #LB: 67  #DK: 12870  LB/K: 1.00  DB/K: 1.00  CLUF:
5859.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T2  Alias: T2
    Card: Original: 13945  Rounded: 13945  Computed: 13945.00  Non
Adjusted: 13945.00
  Access Path: TableScan
    Cost:  38.54  Resp: 38.54  Degree: 0
      Cost_io: 38.00  Cost_cpu: 6791624
      Resp_io: 38.00  Resp_cpu: 6791624
  Best:: AccessPath: TableScan
         Cost: 38.54  Degree: 1  Resp: 38.54  Card: 13945.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  T2[T2]#0
ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort:     187 Row size:          109 Total
Rows:          13945
      Initial runs:         2 Merge passes:        1 IO Cost /
pass:        102
      Total IO sort cost: 289      Total CPU sort cost: 25948637
      Total Temp space used: 3384000
***********************
Best so far: Table#: 0  cost: 329.5793  card: 13945.0000  bytes:
1255050
****** Recost for ORDER BY (using index) ************
***************************************
SINGLE TABLE ACCESS PATH
  Table: T2  Alias: T2
    Card: Original: 13945  Rounded: 13945  Computed: 13945.00  Non
Adjusted: 13945.00
  Access Path: TableScan
    Cost:  38.54  Resp: 38.54  Degree: 0
      Cost_io: 38.00  Cost_cpu: 6791624
      Resp_io: 38.00  Resp_cpu: 6791624
  Access Path: index (FullScan)
    Index: T2_IND1
    resc_io: 5927.00  resc_cpu: 50436325
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 5930.97  Resp: 5930.97  Degree: 1
  Best:: AccessPath: IndexRange  Index: T2_IND1
         Cost: 5930.97  Degree: 1  Resp: 5930.97  Card: 13945.00
Bytes: 90
***********************
Join order[1]:  T2[T2]#0
***********************
Best so far: Table#: 0  cost: 5930.9733  card: 13945.0000  bytes:
1255050
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
    SORT resource      Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:
41943040
      Degree:               1
      Blocks to Sort:     187 Row size:          109 Total
Rows:          13945
      Initial runs:         2 Merge passes:        1 IO Cost /
pass:        102
      Total IO sort cost: 289      Total CPU sort cost: 25948637
      Total Temp space used: 3384000
Final - First Rows Plan:  Best join order: 1
  Cost: 5930.9733  Degree: 1  Card: 13945.0000  Bytes: 1255050
  Resc: 5930.9733  Resc_io: 5927.0000  Resc_cpu: 50436325
  Resp: 5930.9733  Resp_io: 5927.0000  Resc_cpu: 50436325

The cost in this case is 5930, which is a bit higher than the cost of
329 that was predicted for the table access with sort operation.  It
appeared that when there were two indexes on the table, Oracle did not
even bother checking the index access paths, as it expected the time
to calculate the best path would be more than the time to return the
data.

Paraphrased from "Cost-Based Oracle Fundamentals" (very likely the
best book to read and re-read when trying to determine what is
happening): "The optimizer may decide after testing a few join orders
that the cost of executing the query is so small that it should
discontinue checking join orders - as soon as the time spent checking
join orders exceeds the predicted run time, Oracle runs with the best
plan obtained so far.  This means that if Oracle happens to pick an
unsuitable starting table because it calculated the cardinality wrong,
the standard permutation cycle of joining tables can cause problems."

It does not look like a bug to me, but instead an optimization.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

0
hooperc2000 (791)
3/15/2007 10:15:17 PM
Thanks for the reply.
The fact remains that in the situation I have described, the Oracle
Optimizer loses track of the first of the indexes (depending on the
order of creation). My earlier example has a table of about 14,000
rows, where your argument on the possible lower cost of a table scan
(as opposed to an index scan) might hold water. But we have a
production machine where the same problem occurs on a table of about
300,000 rows. There is no way the Optimizer can argue that a table
scan and a subsequent sort would be better.

In fact, I also tried to force the use of the index on the table, by
using the Index hint. With both indexes in place, for a query
requiring the use of the "forgotten index", the optimizer goes for the
wrong index, retrieves the rows through the index and then does a sort
to get the rows in the required order! See below:

SQL> select /*+ index(tab1) */ * from tab1 order by owner, object_name
desc;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=3DFIRST_ROWS (Cost=3D2442 Card=3D4137 B
          ytes=3D310275)

   1    0   SORT (ORDER BY) (Cost=3D2442 Card=3D4137 Bytes=3D310275)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (TABLE) (Cost=3D23
          16 Card=3D4137 Bytes=3D310275)

   3    2       INDEX (FULL SCAN) OF 'TAB1IND1' (INDEX) (Cost=3D43 Card=3D
          4137)




On Mar 15, 6:15 pm, "Charles Hooper" <hooperc2...@yahoo.com> wrote:
> FIRST_ROWS has a tendency to prefer index access, even if the index
> access that is selected is less than ideal.  You should be using
> DBMS_STATS.  With that said, a test on Oracle 10.2.0.2:
>
> CREATE TABLE T2 AS
> SELECT
>   *
> FROM
>   ALL_OBJECTS;
>
> Table created.
>
> COMMIT;
>
> Commit complete.
>
> CREATE INDEX T2_IND1 ON T2(TABLE_OWNER_HERE, OBJECT_NAME);
>
> Index created.
>
> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>'TABLE_OWNER_HERE',TABNAME=3D>'T=
2',CAS=ADCADE=3D>TRUE);
>
> PL/SQL procedure successfully completed.
>
> ALTER SESSION SET OPTIMIZER_MODE=3DFIRST_ROWS;
>
> Session altered.
>
> SELECT
>   *
> FROM
>   T2
> ORDER BY
>   OWNER,
>   OBJECT_NAME;
>
> SELECT
>   *
> FROM
>   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> SQL_ID  9jf1t5g0sgzz4, child number 0
> -------------------------------------
> SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER  BY    OWNER,    OBJECT_NAME
>
> Plan hash value: 1199450076
>
> -------------------------------------------------------------------------=
--=AD-------------------------------
> | Id  | Operation                   | Name    | Starts | E-Rows | A-
> Rows |   A-Time   | Buffers | Reads  |
> -------------------------------------------------------------------------=
--=AD-------------------------------
> |   1 |  TABLE ACCESS BY INDEX ROWID| T2      |      2 |  13945 |
> 27890 |00:00:01.12 |   12286 |    210 |
> |   2 |   INDEX FULL SCAN           | T2_IND1 |      2 |  13945 |
> 27890 |00:00:00.55 |     410 |    136 |
> -------------------------------------------------------------------------=
--=AD-------------------------------
>
> CREATE INDEX T2_IND2 ON T2(TABLE_OWNER_HERE, OBJECT_NAME DESC);
>
> Index created.
>
> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>'TABLE_OWNER_HERE',TABNAME=3D>'T=
2',CAS=ADCADE=3D>TRUE);
>
> PL/SQL procedure successfully completed.
>
> SELECT
>   *
> FROM
>   T2
> ORDER BY
>   OWNER,
>   OBJECT_NAME DESC;
>
> SELECT
>   *
> FROM
>   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> --------------------------------------------------------
> | Id  | Operation                   | Name    | E-Rows |
> --------------------------------------------------------
> |   1 |  TABLE ACCESS BY INDEX ROWID| T2      |  13945 |
> |   2 |   INDEX FULL SCAN           | T2_IND2 |  13945 |
>
> SELECT
>   *
> FROM
>   T2
> ORDER BY
>   OWNER,
>   OBJECT_NAME;
>
> SQL_ID  9jf1t5g0sgzz4, child number 0
> -------------------------------------
> SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER  BY    OWNER,    OBJECT_NAME
>
> Plan hash value: 2552596561
>
> -------------------------------------------------------------------------=
--=AD----------------------------------------------
> | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-
> Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
> -------------------------------------------------------------------------=
--=AD----------------------------------------------
> |   1 |  SORT ORDER BY     |      |      1 |  13945 |  13945 |
> 00:00:00.15 |     182 |     37 |  1824K|   650K|     1/0/0|
> |   2 |   TABLE ACCESS FULL| T2   |      1 |  13945 |  13945 |
> 00:00:00.11 |     182 |     37 |       |       |          |
> -------------------------------------------------------------------------=
--=AD----------------------------------------------
> So, we are consistent so far.
>
> ALTER SYSTEM FLUSH SHARED_POOL;
>
> DROP INDEX T2_IND2;
>
> Let's try a test:
> SELECT /*+ ALL_ROWS */
>   *
> FROM
>   T2
> ORDER BY
>   OWNER,
>   OBJECT_NAME;
>
> SQL_ID  b3myw3j5ufp1h, child number 0
> -------------------------------------
> SELECT /*+ ALL_ROWS */ * FROM T2 ORDER  BY    OWNER,    OBJECT_NAME
>
> Plan hash value: 2552596561
>
> -------------------------------------------------------------------------=
--=AD----------------------------------------------
> | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-
> Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
> -------------------------------------------------------------------------=
--=AD----------------------------------------------
> |   1 |  SORT ORDER BY     |      |      1 |  13945 |  13945 |
> 00:00:00.25 |     182 |     37 |  1824K|   650K|     1/0/0|
> |   2 |   TABLE ACCESS FULL| T2   |      1 |  13945 |  13945 |
> 00:00:00.21 |     182 |     37 |       |       |          |
> -------------------------------------------------------------------------=
--=AD----------------------------------------------
>
> That plan and statistics look to be very similar to the one that we
> received when both indexes were available to be used by the cost based
> optimizer.  Note how the execution time is 0.25 seconds (or 0.15
> seconds when there were two indexes) compared to the 1.12 seconds
> required when the T2_IND1 index was used.  Is it possible that the
> cost based optimizer recognized that the time required to continue
> analyzing the various access methods would exceed the time required to
> return the rows, so the optimizer decided to run with the best plan
> that it found so far?
>
> Let's look at a 10053 trace for the case when there were two indexes
> available:
> BASE STATISTICAL INFORMATION
> ***********************
> Table Stats::
>   Table: T2  Alias: T2
>     #Rows: 13945  #Blks:  190  AvgRowLen:  105.00
> Index Stats::
>   Index: T2_IND1  Col#: 1 2
>     LVLS: 1  #LB: 67  #DK: 12870  LB/K: 1.00  DB/K: 1.00  CLUF:
> 5859.00
>   Index: T2_IND2  Col#: 1 14
>     LVLS: 1  #LB: 69  #DK: 12870  LB/K: 1.00  DB/K: 1.00  CLUF:
> 5870.00
> ***************************************
> SINGLE TABLE ACCESS PATH
>   Table: T2  Alias: T2
>     Card: Original: 13945  Rounded: 13945  Computed: 13945.00  Non
> Adjusted: 13945.00
>   Access Path: TableScan
>     Cost:  38.54  Resp: 38.54  Degree: 0
>       Cost_io: 38.00  Cost_cpu: 6791624
>       Resp_io: 38.00  Resp_cpu: 6791624
>   Best:: AccessPath: TableScan
>          Cost: 38.54  Degree: 1  Resp: 38.54  Card: 13945.00  Bytes: 0
> ***************************************
> OPTIMIZER STATISTICS AND COMPUTATIONS
> ***************************************
> GENERAL PLANS
> ***************************************
> Considering cardinality-based initial join order.
> Permutations for Starting Table :0
> ***********************
> Join order[1]:  T2[T2]#0
> ORDER BY sort
>     SORT resource      Sort statistics
>       Sort width:         238 Area size:      208896 Max Area size:
> 41943040
>       Degree:               1
>       Blocks to Sort:     187 Row size:          109 Total
> Rows:          13945
>       Initial runs:         2 Merge passes:        1 IO Cost /
> pass:        102
>       Total
> ...
>
> read more =BB- Hide quoted text -
>
> - Show quoted text -


0
alanolya (2)
3/16/2007 6:28:59 PM
On Mar 16, 2:28 pm, alano...@invera.com wrote:
> Thanks for the reply.
> The fact remains that in the situation I have described, the Oracle
> Optimizer loses track of the first of the indexes (depending on the
> order of creation). My earlier example has a table of about 14,000
> rows, where your argument on the possible lower cost of a table scan
> (as opposed to an index scan) might hold water. But we have a
> production machine where the same problem occurs on a table of about
> 300,000 rows. There is no way the Optimizer can argue that a table
> scan and a subsequent sort would be better.
>
> In fact, I also tried to force the use of the index on the table, by
> using the Index hint. With both indexes in place, for a query
> requiring the use of the "forgotten index", the optimizer goes for the
> wrong index, retrieves the rows through the index and then does a sort
> to get the rows in the required order! See below:
>
> SQL> select /*+ index(tab1) */ * from tab1 order by owner, object_name
> desc;
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2442 Card=4137 B
>           ytes=310275)
>
>    1    0   SORT (ORDER BY) (Cost=2442 Card=4137 Bytes=310275)
>    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (TABLE) (Cost=23
>           16 Card=4137 Bytes=310275)
>
>    3    2       INDEX (FULL SCAN) OF 'TAB1IND1' (INDEX) (Cost=43 Card=
>           4137)

The hint that you are looking for is likely this one:
/*+ INDEX(TAB1 TAB1IND2) */

I believe what I was suggesting is this:
"Is it possible that the cost based optimizer recognized that the
_time_required_to_continue_analyzing_the_various_access_methods_ would
exceed the {predicted} time required to return the rows, so the
optimizer decided to run with the best plan that it found so far?"
This feature of the optimizer may have over-ruled the usual approach
of using an indexed access path, if available, when the optimizer is
set to FIRST_ROWS.  If you look carefully at the two partial 10053
traces that I previously supplied, it appears that Oracle short-
circuited before checking the cost of the index access paths.

Tests with DBMS Xplan
--------------------
Unhinted, both indexes in place:
SELECT T2.* FROM T2 ORDER  BY     OWNER,     OBJECT_NAME DESC
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-
Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |  13945 |  13945 |
00:00:00.04 |     182 |    178 |  1895K|   658K|     1/0/0|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  13945 |  13945 |
00:00:00.01 |     182 |    178 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Hinted, with only the table name/alias, both indexes in place, T2_IND1
created before T2_IND2:
SELECT /*+ INDEX(T2) */ T2.* FROM T2 ORDER  BY     OWNER,
OBJECT_NAME DESC
----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-
Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |      1 |  13945 |
13945 |00:00:00.83 |    6161 |    248 |
|   2 |   INDEX FULL SCAN           | T2_IND2 |      1 |  13945 |
13945 |00:00:00.21 |     209 |     70 |
----------------------------------------------------------------------------------------------------------

Hinted, with only the table name/alias, both indexes in place, T2_IND1
created before T2_IND2:
SELECT /*+ INDEX(T2 T2_IND1) */ T2.* FROM T2 ORDER  BY     OWNER,
OBJECT_NAME DESC
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-
Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY               |         |      1 |  13945 |
13945 |00:00:01.15 |    5927 |    246 |  1895K|   658K|     1/0/0|
|   2 |   TABLE ACCESS BY INDEX ROWID| T2      |      1 |  13945 |
13945 |00:00:01.11 |    5927 |    246 |       |       |          |
|   3 |    INDEX FULL SCAN           | T2_IND1 |      1 |  13945 |
13945 |00:00:00.33 |      68 |     68 |       |       |          |

Hinted, with only the table name/alias, both indexes in place, T2_IND1
created before T2_IND2, drop index T2_IND1 and recreate that index:
SELECT /*+ INDEX(T2) */ T2.* FROM T2 ORDER  BY     OWNER,
OBJECT_NAME DESC
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-
Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY               |         |      1 |  13945 |
13945 |00:00:00.36 |    5927 |     68 |  1895K|   658K|     1/0/0|
|   2 |   TABLE ACCESS BY INDEX ROWID| T2      |      1 |  13945 |
13945 |00:00:00.31 |    5927 |     68 |       |       |          |
|   3 |    INDEX FULL SCAN           | T2_IND1 |      1 |  13945 |
13945 |00:00:00.25 |      68 |     68 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

If you know more about the execute plan than Oracle, provide hints to
Oracle to guide the execution plan so that it does not prematurely
short-circuit - just watch out during the next upgrade/patch to Oracle
for unexpected behavior changes.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

0
hooperc2000 (791)
3/16/2007 7:49:04 PM
<alanolya@invera.com> wrote in message 
news:1173987941.871534.19430@l75g2000hse.googlegroups.com...
> Hello,
>
> Not sure whether this problem has been discussed earlier in this
> forum. Can someone suggest a workaround to the following problem which
> can be replicated by following the sequence of detailed steps I have
> given below. In summary, if you have a table with two composite
> indexes, both with the same fields, but one of them has a field in
> descending order, then the Optimizer loses track of the correct index
> to use for a specific query.
>
> Thanks.
>
> A.J.Andrews
>
> See details below:
> ----------------
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006
>
> Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.6.0 - Production
>
> SQL> create table tab1 as select * from all_objects;
>
> Table created.
>
> SQL> set autotrace traceonly explain;
> SQL> create index tab1ind1 on tab1(owner, object_name);
>
> Index created.
>
> SQL> analyze table tab1 compute statistics;
>
> Table analyzed.
>
> SQL> alter session set optimizer_mode=first_rows;
> Session altered.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
>   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
>   2    1     INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
> Card=4899)
>
> ===> Note here that the correct index tab1ind1 is being used for the
> query.
>
>
> SQL> create index tab1ind2 on tab1(owner, object_name desc);
>
> Index created.
>
> SQL> analyze table tab1 compute statistics;
>
> Table analyzed.
>
> SQL> select * from tab1 order by owner, object_name desc;
>
> Execution Plan
> ----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
>   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
>   2    1     INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48
> Card=4899)
>
> ===> Again, the correct index tab1ind2 is being used.
>
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899
> Bytes=372324)
>
>   1    0   SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324)
>   2    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899
> Bytes=372324)
>
> ===> But, at this time the Optimizer has forgotten about the existence
> of tab1ind1 and is going for a full table scan and a sort! Wrong
> choice!  Should be using index tab1ind1.
>
>
> SQL> drop index tab1ind2;
>
> Index dropped.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
>   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
>   2    1     INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
> Card=4899)
>
> ===> Once tab1ind2 is dropped, the Optimizer remembers the existence
> of tab1ind1 and chooses the index scan for the very same query for
> which it used a table scan earlier.
>
>
> This problem is easily reproducible with any two indexes on a table,
> with both indexes being on the same set of columns, but with one of
> the indexes having a "desc" requirement on at least one of the columns.
>


Having run a couple of tests on 9.2.0.8 and 10.2.0.1
it looks like a bug,  If you have two indexes as described,
then Oracle "loses" the first one you created when working
out the 'single table access path'.

You have a perfect test case - raise an SR with Oracle, and
send them the test case.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



0
jonathan5683 (1392)
3/16/2007 10:54:21 PM
Reply:

Similar Artilces:

massive performance drop Oracle 9i on multi processor system (Windows Server 2003, Oracle 9i, 9.2.0.6.0)
The performance of some complex PL/SQL functions has dropped dramatically since migrating from Oracle 8i (8.1.7.0.0) on Windows Server 2000 to 9i (9.2.0.6.0) on Windows Server 2003. We found out, that Oracle 9i performance on a multi processor CPU machine (our server has 4 XEON CPUs, Hyperthreading is disabled in BIOS) is highly dependent on init.ora parameters; unfortunately our trial and error approach to find the proper settings (we searched Oracle Tech Net (OTN) and google groups without success) did not help so far. We are testing performance behaviour with some business critical PL/SQL procedures that do authorization calculations for our application. Simplified these functions do a lot of inserts, deletes and selects in/from tables (being used as temp tables in lack of in memory temp table support of oracle 9i). We can monitor the progress within the stored procedure by using UTL_FILE functions to write a log file into the file system of the server; we real time monitor the log file using "tail -f". <PL/SQL do something> UnbufferedLogFileOutputUsingUTL_FILE(); <PL/SQL do something> UnbufferedLogFileOutputUsingUTL_FILE(); [...] When setting "CPU_COUNT = 1" in init.ora the function will typically run up to 10 seconds, when setting "CPU_COUNT = 4" it will take more than 2 minutes (!!!) We experimented with the following other parameters so far: db_writer_processes (set to 4 in combination with cpu_count = 4) fast_start_parall...

Oracle server 9.2.0.1.0, Client 9.2.0.4.0, Red Hat Linux enterprise advanced server 2.1
Hi Everybody, My Database server is running on Red Hat Linux enterprise advanced server 2.1, And my Oracle version is 9.2.0.1.0. My Client mechine also Red Hat Linux enterprise advanced server 2.1 , Oracle client version is 9.2.0.4.0. I am running a web application. Our application is in Magic edeveloper. I connect to Oracle using oracle gateway to my application. My Problem is my application is is becoming slow. I am not able to trace where the problem is. Before our application was running fine. recently i upgraded Oracle client version to 9.2.0.4.0. My Oracle client & server versions are different. I am getting error on my client mechine, it showing all memory address on the screen and the part of error is, arg register addr=83498cc .... Dump of memory from 0x83988c to 0x83499cc .... -- End of Call stack trace -- I am not able to trace where the problem is.Any insight please... Thanks, Ssreddy. ...

Upgrade from Oracle 9.2.0.1 to Oracle 9.2.0.3
Hello All, I am trying to upgrade oracle 9.2.0.1 to new patchset 9.2.0.3 or 9.2.0.4. Please recommend which is better 9.2.0.3 or 9.2.0.4? Also I had a bad experience with my development server when applying 9.2.0.3 patchset. Is there anything you recommend before I start again? Thanks Kumu Hello Kumu, What is the reason for upgrade? Any specific bugs or issues you experience? You can open TAR as well to get support recommendations for your situation. New patches can introduce new problems as you can see, Regards, Ron DBA Infopower http://www.dbainfopower.com Standard disclaimer: http://www.dbainfopower.com/dbaip_advice_disclaimer.html "kumu" <forlist2001@yahoo.com> wrote in message news:a44abd39.0402161837.6badb9ed@posting.google.com... > Hello All, > I am trying to upgrade oracle 9.2.0.1 to new patchset 9.2.0.3 or > 9.2.0.4. Please recommend which is better 9.2.0.3 or 9.2.0.4? > > Also I had a bad experience with my development server when applying > 9.2.0.3 patchset. Is there anything you recommend before I start > again? > > Thanks > Kumu "Ron" <support@dbainfopower.com> wrote in message news:<QqWdnRM_cMiwWqzdRVn-vg@comcast.com>... > Hello Kumu, > > What is the reason for upgrade? Any specific bugs or issues you > experience? > > You can open TAR as well to get support recommendations for your > situation. > > New patches can introduce new...

help:How can i take Export of Oracle 9.2.0.1 from Oracle 9.2.0.7
How can i take Export of Oracle 9.2.0.1 from Oracle 9.2.0.7 1-what scripts are to be used 2-Where i can find these scripts. Thnks. You can't. You can use lower version export to export higher version database. The other way around has never been possible. -- Sybrand Bakker Senior Oracle DBA You probably have Oracle 9.2.0.1 running somewhere right, perhaps on another machine ? Set your ORACLE_HOME to 9.2.0.1 then export through sqlnet: exp username/pwd @9207instance Now you'll use the 9.2.0.1 export utility; this should work. "shahsyed" <shahsyed@gmail.com> schreef in bericht . > How can i take Export of Oracle 9.2.0.1 from Oracle 9.2.0.7 > > 1-what scripts are to be used > 2-Where i can find these scripts. > > Thnks. Hi, I guess oracle dosen't support backword comatibilty.then how it is possible?? Thanks Alok. On 9 Aug 2005 22:05:49 -0700, alok.behria@gmail.com wrote: >Hi, > > I guess oracle dosen't support backword comatibilty.then how it is >possible?? > >Thanks >Alok. Oracle is backwards compatible provided you have the definition of backwards compatible correct. You can connect (again) *lower version client* to a *higher version database* The other way around has nothing to do with backwards compatible, though it usually works, *with the exception of export/import, AS DOCUMENTED* (but I guess no one ever reads the docs) -- Sybrand Bakker, Senior Oracle DBA Hi, ...

Install problems with Oracle installer 2.2 for Oracle 9i Client 9.2.0.1.0
Please forgive me if I am posting this in the wrong forum - I am an Oracle Newbie. Here are my computer specs : Windows 2003 - fully patch from Microsoft as of Sept 4th 2004 HP LP2000r 1 x 1,1 Gig Intel processor 1 GB RAM Hardware RAID 1, 12 GB with 8 GB freespace Clean install, no other programs. At first I tried running the Oracle installer program from the CD ROM and came up with the error that it could not find the javaw.exe program. I then tried installing and reinstalling several versions of java to no avail. Next I searched through groups.google and found the solution: 1. Copy the Oracle CD to the hard drive. I copied it to a subfolder directly to C:\Oracle9i 2. Change the file c:\oracle9i\install\ORAPARAM.INI to reflect the actual name of the directories listed in the install directories of the cd I just copied. I did this and next took out some "."s and put the absolute path to the java program and something called the "OUI_LOCATION". JRE_LOCATION=C:/oracle9i/stage/Components/oracleswdjre/13110a/1/DataFiles/Expanded OUI_LOCATION=C:/oracle9i/stage/Components/oracleswdoui/220120/1/DataFiles/Expanded Okay so good so far. This time the installer actually started. The problem occurs in the next few steps, when I click "next" (weiter in German) from the welcome screen- please understand if I translate something wrong here. Then I am shown the installation directory screen, where I select the default listed: Source : C:\Oracle...

Oracle 9.2 vs. Oracle 9.2.0.4.
I'm using SDK 1.4.2_03 either with an Oracle 9.2 or an Oracle 9.2.0.4 client version. The 9.2 version behaves as expected. My problem only occurs with the 9.2.0.4. version. If I try to process the ResultSet of database query and the selected column data includes leading blanks, the 9.2.0.4 seems to trim these leading blanks. Is this behaviour known or am I doing something wrong ? <code snippet> ResultSet result = null; Connection con = null; Statement stmt = null; Vector vec = new Vector(); try { con = DriverManager.getConnection(dbUrl, dbUser, dbPassword); stmt = con.createStatement(); result = stmt.executeQuery("SELECT DATA FROM CPTMESSAGEDATA";); while (result.next()) { vec.add(result.getString("DATA")); } con.close(); stmt.close(); } catch (SQLException e) { } </code snippet> CPTMessageData.DATA is of type VARCHAR2(50) not nullable with values like: " @20013 @E123 @G123 @ @ @ " " @3700167 @43186 @B50_300 @1 @20013 @1 @OK " Any help is appreciated. Alex ...

restoring 9.2.0.6 datafiles with 9.2.0.7 Oracle
Hello Is there potential problem ? we want to use old datafiles in 9.2.0.6 with a patched oracle in 9.2.0.7... Thank you nonor wrote: > Hello > > Is there potential problem ? we want to use old datafiles in 9.2.0.6 > with a patched oracle in 9.2.0.7... > > Thank you No, you don't want to do that. You want to solve some problem, for which you believe that to be the solution. What is the problem you are trying to solve? In answer to your question -- yes, I'd think there would be disasterous problems. So, again -- what is the problem you are trying to solve? nonor wrote: > Hello > > Is there potential problem ? we want to use old datafiles in 9.2.0.6 > with a patched oracle in 9.2.0.7... That really describes a usual step in applying the 9.2.0.7 patchset. The database files are still at ( whatever ) the old patch level was .... say 9.2.0.6. You have to use the patched oracle binaries to open the database. But then the next step should be to follow the steps in the 9.2.0.7 for patching existing databases ... startup migrate and run the script to move the database files up to 9.2.0.7 ... Is that what you are attempting to do? Have you read the readme ( installation instructions ) for the 9.2.0.7 patchset? ...

from oracle 9.2.04 to oracle 9.2.05
i'm a newbie, i'd like to guess if anybody knows how can I upgrade my oracle 9.2.04 to 9.2.05. My java_pool_size, shared_pool_size are under 150 MB. How can I set this value? Bye and thx a lot. jakko1982 wrote: > i'm a newbie, i'd like to guess if anybody knows how can I upgrade my > oracle 9.2.04 to 9.2.05. > My java_pool_size, shared_pool_size are under 150 MB. How can I set > this value? > > Bye and thx a lot. 1) Goto http://metalink.oracle.com and download the 9.2.0.5 patchset. You can also goto 9.2.0.6. Patch installation documentation comes with it. Make sure you review the documentation before applying the patchset. 2) Goto http://tahiti.oracle.com and review the "Initialization Parameter" section. If you are using spfile (Server Parameter File) then you can change these parameters online. Search for "spfile" keyword and goto "Administrator's Guide". If you are not using spfile then you need to locate the database init.ora file, modify the parameter values and bounce the database. Regards /Rauf sorry, but I use Spfile. I've searched Spfile keyword and I read the administrator's guide. and now? sorry but Im a newbye jakko1982 wrote: > sorry, but I use Spfile. I've searched Spfile keyword and I read the > administrator's guide. > > and now? sorry but Im a newbye Did you read under heading "Managing Initialization Parameters Using a Server Parameter File"...

Connection from Oracle Developer Suite 10g 9.0.4.0.1 to Oracle DB 10g Release 10.1.0.2.0
Hi, I have installed Oracle DB 10g Release 10.1.0.2.0 on Windows XP. START->ALL PROGS->oracle-OraDb10g_home3 Location of TNSNAMES FILE: C:\oracle\product\10.1.0\db_3\NETWORK\ADMIN\TNSNAMES.ORA with two alias: 1)ORCL3 2) EXTPROC_CONNECTION_DATA I can logon to database via SQL*PLUS and I can log on to EM on the web as well using ORCL3. After that I installed Oracle Developer Suite 10g 9.0.4.0.1 to my the same computer. During installation it was not allowing me to install this suite to the same ORACLE home. I installed this in the following home: START->ALL PROGS->...

oracle 9i (9.2.0.1.0)
Hi! when I call the oracle performance manager I get the following errors: VTM-0002: error when displaying graph java.lang.null.pointerexception at oracle.sysman.vtd.VtdCartridge.lookupClass(VtdCartridge.java:1313) ..... what is my problem? Thanks! ...

upgrade from oracle 9.2.0.1.0 to 9.2.0.5.0
Hi, I have a 9.2.0.1.0 database that I want to upgrade to 9.2.0.5. for window version 32 bits, does anyone can provide the node document information of step by step doing it. many thanks leehi leehi wrote: > I have a 9.2.0.1.0 database that I want to upgrade to 9.2.0.5. for > window version 32 bits, Windows NT4? W2k? W2K3? W2K3 - Release 2? Why stop ot 9.2.0.5? > does anyone can provide the node document information of step by step > doing it. leehi wrote: > Hi, > > I have a 9.2.0.1.0 database that I want to upgrade to 9.2.0.5. for > window version 32 bits, > > does anyone can provide the node document information of step by step > doing it. > > many thanks > > leehi Go to metalink.oracle.com (registration and Oracle support CSI required). Click on Patches and Updates tab. Wait for the whole screen to load. Use one of the search functions to find your patch (note the Quick Links if you are getting something common, like 9206 or 9207). When you finally get to your patch download, click on the readme (the piece-of-paper icon next to the download icon). Read all the instructions. It's a good idea to read the instructions every time you do it, even (or perhaps especially) when you are comfortable doing it. The readme is usually also included with the patch. Download and unzip the patch. Look at what is there in the patch, perhaps there are additional release notes. Look around on metalink for addition...

Bind Errors
Recently migrated from 8.1.7 to 9i and my Oracle Reports are no longer working -- I am getting an ORA-01006 Bind Variable does not exist. Has anybody had any success running reports developed in 2.5 to run against 9.2.0.6.0? They are working on 9.2.0.1.0 but I am rushed for time - don't really have the resources to upgrade this and all the instances on it so quickly. ManChild wrote: > Recently migrated from 8.1.7 to 9i and my Oracle Reports are no longer > working -- I am getting an ORA-01006 Bind Variable does not exist. Has > anybody had any success running reports developed in 2.5 to run against > 9.2.0.6.0? They are working on 9.2.0.1.0 but I am rushed for time - > don't really have the resources to upgrade this and all the instances > on it so quickly. Apparently the concept of testing evaded your management. Did you check metalink? -- Daniel Morgan Puget Sound Oracle Users Group On 12 Sep 2006 14:48:48 -0700, "ManChild" <starritt@gmail.com> wrote: > >Recently migrated from 8.1.7 to 9i and my Oracle Reports are no longer >working -- I am getting an ORA-01006 Bind Variable does not exist. Has >anybody had any success running reports developed in 2.5 to run against >9.2.0.6.0? They are working on 9.2.0.1.0 but I am rushed for time - >don't really have the resources to upgrade this and all the instances >on it so quickly. Please answer the question when Reports 2....

Oracle Warehouse Builder 9.2.0.2.8 and Workflow 2.6.2 on linux
Hi there, I downloaded OWB_9.2.0.2.8_linux.tar.gz from here: http://otn.oracle.com/software/products/warehouse/index.html In the above download I found OWB installation documentation. In the OWB installation documentation, I see evidence that Oracle Warehouse Builder (runtime portion) makes use of "Oracle Workflow". It does not mention a version though. It does say that Workflow is neccessary. The documentation for Workflow was easy to find here: http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-WOR I'm not sure where to find the Workflow software though. The documentation for Workflow suggests that it is installed via OUI rather than a simple unzip (as used by JDeveloper for example). In http://metalink.oracle.com, I found evidence that Workflow is not a standalone product but that it is bundled with other products. I also found evidence that it lives in a directory named "ofw" once it is installed. I did not find mention of workflow in any of the download areas so I'm curious if any of you have installed it and if yes, where did you get it? I hate to be a whiner, but OWB product mgt has dropped the ball here. I spent a day setting up my OWB POC environment and here I am near the end of the installation and I can't find a critical piece of software and the OWB installation documentation gives no clue where to get the software or if it even has a part number. The same can be said for the Workflow software. Do th...

Wrong blob size reported by Oracle using 9.2.0.6 client
I'm finding that using the 9.2.0.6 OCI/JDBC client oracle is reporting that my blob size (Blob.length()) is a few terabytes when it is really a few Kilobytes. Has anyone seen anything like this? P.S. The client and server are on Solaris machines. ...

oracle database 9.2.0.8 32bit and windows server 2003 64bit
Hello! What about configuration like in subject... What about memory limits on 32bits? Can i rise sga more then 2GB? on 64 bit system but using 32 bit database? Any suggestion? Regards - MJ On 15 apr, 11:16, "hehehe" <m...@m.ccc> wrote: > Hello! > > =A0 =A0 What about configuration like in subject... What about memory lim= its on > 32bits? Can i rise sga more then 2GB? on 64 bit system but using 32 bit > database? Any suggestion? > > Regards - MJ Obviously, with only a little bit of thinking, you would have reached the conclusion, limits are determined by the OS of the database server. -- Sybrand Bakker Senior Oracle DBA thx. I will check Uzytkownik "sybrandb" <sybrandb@gmail.com> napisal w wiadomosci news:b5d54371-bf21-439b-85c3-ae21dafc21be@3g2000yqk.googlegroups.com... On 15 apr, 11:16, "hehehe" <m...@m.ccc> wrote: > Hello! > > What about configuration like in subject... What about memory limits on > 32bits? Can i rise sga more then 2GB? on 64 bit system but using 32 bit > database? Any suggestion? > > Regards - MJ Obviously, with only a little bit of thinking, you would have reached the conclusion, limits are determined by the OS of the database server. -- Sybrand Bakker Senior Oracle DBA On Apr 15, 5:16=A0am, "hehehe" <m...@m.ccc> wrote: > Hello! > > =A0 =A0 What about configuration like in subject... What about memory lim= its on &g...

Oracle 10g 10.0.2 client, to connect oracle 8 server
Dear Group members, I am using Oracle 10 g 10.0.2 client software to connect 9i,10g and 8i it is working very good with the 9i and 10g.. when i trying to connect 8i server ./. it giving error <b> "ORA-03134: Connections to this server version are no longer supported." </b> Can i install, 2 or more version of oracle clients in same system, becoz .. currently using client software is well configured for some SQL server BI agents, if i un-install current 10g client, it will infect my software .. process.. if, it can be possible, what kind of things i take care, before installation .. What is the best oracle client software can suitable for most of the server's On Jul 27, 11:58=A0pm, ranganadh <ranganadhkod...@gmail.com> wrote: > Dear Group members, > > =A0I am using Oracle 10 g 10.0.2 client software to connect 9i,10g and > 8i > > it is working very good with the 9i and 10g.. > when i trying to connect 8i server ./. it giving error > > <b> =A0"ORA-03134: Connections to this server version are no longer > supported." </b> > > Can i install, 2 or more =A0version of oracle clients in same system, > becoz .. currently using client software is well configured for some > SQL server BI agents, if i un-install current 10g client, it will > infect my software .. process.. > > if, it can be possible, what kind of things i take care, ...

Oracle permissions issue Oracle 9.2.0.7/Win 2003
Oracle 9.2.0.7 Windows 2003 We are going through a Certification and Accreditation process with our databases. We are being dinged for grants that any schema owner is giving on its objects to database roles. We were told that only the SYSTEM user or a user with DBA privileges should grant the permissions on any object to any role. We have changed the process so that the user SYSTEM will log in and grant the permissions but here is something we have noticed. Lets say that the schema in question is SCOTT. The table to grant SELECT permission on is the EMP table. The role to grant permissions to is EMP_ACCESS_ROLE 1. We log in as SYSTEM and run the following statement. GRANT SELECT on SCOTT.EMP to EMP_ACCESS_ROLE; 2. When we run select * from DBA_TAB_PRIVS where table = 'EMP'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE ---------------------------------- ------------ ------------------------ ---------------- ------------------------------------ EMP_ACCESS_ROLE SCOTT EMP SCOTT SELECT We see that the GRANTOR is still the schema owner SCOTT. So when the database is scanned again we are still being dinged for the schema owner granting permission on an object to a role. ------- 3. We then tried this (even though this is retarded and unnecessary since SYSTEM has the SELECT permission to begin with) GRANT SELECT ON SCOTT.EMP TO SYSTEM WITH GRANT OPTION; 4. We then log in as SYSTEM and grant the permission again. GRANT ...

Oracle 10g 10.0.2 client, to connect oracle 8 server
Dear Group members, I am using Oracle 10 g 10.0.2 client software to connect 9i,10g and 8i it is working very good with the 9i and 10g.. when i trying to connect 8i server ./. it giving error <b> "ORA-03134: Connections to this server version are no longer supported." </b> Can i install, 2 or more version of oracle clients in same system, becoz .. currently using client software is well configured for some SQL server BI agents, if i un-install current 10g client, it will infect my software .. process.. if, it can be possible, what kind of things i take care, before installation .. What is the best oracle client software can suitable for most of the server's > I am using Oracle 10 g 10.0.2 client software to connect 9i,10g and > 8i > > it is working very good with the 9i and 10g.. > when i trying to connect 8i server ./. it giving error > > <b> "ORA-03134: Connections to this server version are no longer > supported." </b> > > > Can i install, 2 or more version of oracle clients in same system, > becoz .. currently using client software is well configured for some > SQL server BI agents, if i un-install current 10g client, it will > infect my software .. process.. Yes you can, I currently have 3 versions Oracle 8.0.4, Oracle 9.2 and Oracle 10.2 on my work PC. No problem. > > if, it can be possible, what kind of things i take care, before > installation .. If yo...

Question on Oracle Server 10g Install (.../oracle/oracle/.....
I am in the last stages of installing Oracle Server 10gR2 Enterprise Edition on a UNIX AIX server. When window "Execute Configuration Scripts" comes up it tells me to execute "/u01/app/oracle/oracle/product/10.2.0/db_1/root.sh". The ".../oracle/oracle/..." stops me cold. I look back in my notes and everything I wrote suggests I had entered ".../oracle/product/....." for my ORACLE_HOME. I look at the install log in /u01/app/oracle/oraInventory/logs and see: "INFO: Setting variable 'ORACLE_HOME' to '/u01/app/oracle/oracle/product/10.2.0/d b_1'. Received the value from the command line." Did I really key /oracle/oracle/ instead of /oracle/ or did the OUI change the path for some reason????? Thank you, Bill bfogarty@gaports.com wrote: > I am in the last stages of installing Oracle Server 10gR2 Enterprise > Edition on a UNIX AIX server. When window "Execute Configuration > Scripts" comes up it tells me to execute > "/u01/app/oracle/oracle/product/10.2.0/db_1/root.sh". > > The ".../oracle/oracle/..." stops me cold. I look back in my notes and > everything I wrote suggests I had entered ".../oracle/product/....." > for my ORACLE_HOME. I look at the install log in > /u01/app/oracle/oraInventory/logs and see: > "INFO: Setting variable 'ORACLE_HOME' to > '/u01/app/oracle/oracle/product/10.2.0/d > b_1'. Received the valu...

Running ORACLE 8.1.7 & ORACLE 9.2 on the same UNIX server
Hi, I have recently installed ORACLE 9.2 Database on a UNIX server (2.8) which already has ORACLE 8.1.7. They have both a different SID and ORACLE_BASE. However I am having to use the ORACLE 8 listener for both ORACLE databases. Is it possible to run separate ORACLE 8 and ORACLE 9 listeners? Hello, There are no difficulty to use 2 listeners on the same machine. The point is that default listener used to listen on port 1521 (or 1526). You will have to change your listener configuration( netasst or by hand) in order to use a different port number, a different listener name and communicate it to your users for their tnsnames.ora. LSNR8I = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= TCP)(Host= machine)(Port= 1110)) ) Generally what I do is to create 1 listener per Oracle Version. After that, for starting listeners you'll have to launch lsnrctl start LSNR8I ou lsnrctl start LSNR9I I am used to name listeners with different names since 8i because you have the autoregistration stuff and with 2 listeners with the same name, you are not sure on which the database will register (or using le local_listener init parm). Regs, Olivier jonathan.johnston@bt.com (Jonathan Johnston) wrote in message news:<5e07fa5.0406080037.67d595a9@posting.google.com>... > Hi, > > I have recently installed ORACLE 9.2 Database on a UNIX server (2.8) > which already has ORACLE 8.1.7. They have both a different SID and > ...

Where to get oracle patch 9.2.0.4 and 9.2.0.5
I'm doing an install for work on a windows xp (32bit) development station. To mimik the production systems I need to install 9.2.0.1.0 and then the patches for 9.2.0.4 and 9.2.0.5. I've installed 9.2.0.1 and I have a disk for the patches but it is corrupted. Where can I get these patches? I've tried google to no avail. Thanks in advance! On 29 Jun 2006 15:13:24 -0700, "David.E.M....@gmail.com" <David.E.Murphy@gmail.com> wrote: >I'm doing an install for work on a windows xp (32bit) development >station. To mimik the production systems I need to install 9.2.0.1.0 >and then the patches for 9.2.0.4 and 9.2.0.5. I've installed 9.2.0.1 >and I have a disk for the patches but it is corrupted. Where can I get >these patches? I've tried google to no avail. Thanks in advance! Patches are available on Metalink. And, yes, you need to have a paid support contract for that. -- Sybrand Bakker, Senior Oracle DBA David.E.M....@gmail.com wrote: > I'm doing an install for work on a windows xp (32bit) development > station. To mimik the production systems I need to install 9.2.0.1.0 > and then the patches for 9.2.0.4 and 9.2.0.5. I've installed 9.2.0.1 > and I have a disk for the patches but it is corrupted. Where can I get > these patches? I've tried google to no avail. Thanks in advance! You get the patches from oracle http://www.oracle.com then go to the support link then metalink ... I&#...

Oracle 9.0.1.6 fails to use index with '=' but will with 'like'
How is this select * from coneventnp where chcontainer like 'OLSU8888888' Uses the index on the chcontainer column, but select * from coneventnp where chcontainer = 'OLSU8888888' will result in a full table scan. Anybody ever seen anything like this? We consider this to be beyond bizzare. steven.he...@gmail.com wrote: > How is this > > select * from coneventnp where chcontainer like 'OLSU8888888' > > Uses the index on the chcontainer column, but > > select * from coneventnp where chcontainer = 'OLSU8888888' > > will result in a full table scan. > > Anybody ever seen anything like this? We consider this to be beyond > bizzare. Not that strange. I get the similar behaviour. I think we underestimate the CBO - it is not (that) stupid. :-) The LIKE clause's value does not contain a wildcard - thus the LIKE equates to an EQUAL TO. And the CBO seems to know that and treat it as such. SQL> create table fooscan nologging as select object_type, object_name, object_id from all_objects; Table created. SQL> create index idx1_fooscan on fooscan( object_name ); Index created. SQL> exec dbms_stats.gather_table_stats( USER, 'FOOSCAN' ) PL/SQL procedure successfully completed. SQL> select count(*) from fooscan where object_name like 'FOOSCAN'; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEME...

Oracle 9.2.0 Undefined Symbols when compiling Oracle's sample program
I just installed Oralcle 9.2.0.1 on a 64 bit Solaris 2.8 machine. When I try to compile Oracle's sample program (sample10.pc) statically. I get the following undefined symbols. Does anybody have any ideas on what can be wrong ? Thanks make -f demp_proc.mk build_static EXE=sample10 OBJS=sample10.o cc -xarch=v9 -o sample10 sample10.o -L/rdbms/oracle/9.2.0/lib/ -lclntst9 `cat /rdbms/oracle/9.2.0/lib/ldflags` `cat /rdbms/oracle/9.2.0/lib/sysliblist` -R/rdbms/oracle/9.2.0/lib -laio -lposix4 -lm -lthread Undefined first referenced symbol in file kolacbktab /rdbms/oracle/9.2.0/lib//libclntst9.a(kola.o) kpupft /rdbms/oracle/9.2.0/lib//libclntst9.a(kpuini.o) kgupntb /rdbms/oracle/9.2.0/lib//libclntst9.a(kgupi.o) ncrov /rdbms/oracle/9.2.0/lib//libclntst9.a(kgupn.o) slxlcbk /rdbms/oracle/9.2.0/lib//libclntst9.a(lxinitc.o) kpupftv7 /rdbms/oracle/9.2.0/lib//libclntst9.a(ttcdrv.o) lxsftb /rdbms/oracle/9.2.0/lib//libclntst9.a(lxpsset.o) kpudft /rdbms/oracle/9.2.0/lib//libclntst9.a(kpuini.o) ldxpmxa /rdbms/oracle/9.2.0/lib//libclntst9.a(ldxsti.o) lpmiftfs /rdbms/oracle/9.2.0/lib//libclntst9.a(lpmpgif.o) slxlobc /rdbms/oracle/9.2.0/lib//libclntst9.a(lxinitc.o) slxlobj /rdbms/oracle/9.2.0/lib//libclntst9.a(lxinitc.o) ld: fatal: Symbol referencing errors. No output written to sample10 *** Error code 1 make: Fatal error: Command failed for target `build_static' ...

After installation of Oracle 9.2. The Login in Oracle Management Server creates the error: VTK-1000
Hello, I can't local login in Oracle Management Server after installation of Oracle 9.2. Where I can look the right name and status the Oracle Management Server? My Database ----------- Host-Name: PRG1 Port-Number: 1521 SID: TEST Net Service Name: TEST.PZCS.LOCAL TNS-Descriptor: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=prg1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test))) Silly question maybe: You did verify whether the Oracle Management Service is actually running? --- Sybrand Bakker Senior Oracle DBA Hello, how I can detect, whether the Oracle Management running? I use W2k. My Oracle Services --- OracleMTSRecoveryService OracleOraHome92Agent OracleOraHome92ClientCache OracleOraHome92HTTPServer OracleOraHome92PagingServer OracleOraHome92SNMPPeerEncapsulator OracleOraHome92SNMPPeerMasterAgent OracleOraHome92TNSListener OracleServiceTEST Best Regards Wolfram Hubert wolfram.hubert@web.de wrote: >Hello, > >how I can detect, whether the Oracle Management running? I use W2k. > >My Oracle Services >--- >OracleMTSRecoveryService >OracleOraHome92Agent >OracleOraHome92ClientCache >OracleOraHome92HTTPServer >OracleOraHome92PagingServer >OracleOraHome92SNMPPeerEncapsulator >OracleOraHome92SNMPPeerMasterAgent >OracleOraHome92TNSListener >OracleServiceTEST > >Best Regards > >Wolfram Hubert Is there a OMS set up and are you entering the correct connection info for it ( it IS NOT...

Web resources about - Oracle Optmizer does not use the right index (Oracle 9.2.0.6) - comp.databases.oracle.server

SEO Reseller Platform - Seeking SEO Companies And Search Engine Optmizers - blackwoodproductions.com
Blackwood Is seeking Search Engine Optimization firms, SEO companies, Search Engine Optimizers and other Internet service providers. If you have ...

As Seen In - LKR Social Media
September 2014 Design Good 9 Women Entrepreneurs Rocking the Business World http://designgood.com/9-women-entrepreneurs/ August 2014 Social Media ...

Solar Power Technologies Bringing Clarity to Solar Power
+1 512/782.8957 HOME - PRODUCTS - PRESS ROOM - ABOUT US - CONTACT About Us Leadership Our Clients Our Clients Want to see it in action? Need ...

WebFadds Optimizer: What’s A Conversion?
So... what is a "Conversion": I get this question a lot. Remember when everyone worried about ?hits?? Well, ?conversions? are the new hits. You ...

PR Organization
RSS Top Hits Latest Links Submit Link Home Category Arts & Humanities Business & Economy Computers & Internet Education Entertainment Health ...

Social Media Marketing
It's time to learn strategy and tactics for the new Social Media Marketing, using sites like Twitter, FaceBook, and LinkedIN, and Scott Frangos, ...

Optmizer Column - WebFadds.com
Review articles and how-to tips about Optmizer Column

The Optimizer: Conversion Gains, SEO Evolution, & Twitter eCommerce
Wouldn't it be good if you could get 20% - 70% more leads from your site? Many companies do. What about increases of 200% - 400%? It's possible. ...

Optmizer Column - WebFadds.com
Playing Ball: Innovation, change, and Social Media Marketing growth… it’s happening rapidly each week. Last week, Twitter reported astronomic ...

The Optimizer: #3 on Google in 19 Minutes…
#3 on Google in less than 19 Minutes: What's the true power of a Business Blog? Well, you know I always focus on conversions. But before you ...

Resources last updated: 3/20/2016 12:13:54 AM