Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: query slow in 9i, but not slow in 8i

MacGregor, Ian A.

2004-03-02

Replies:
The ORA-01555 error indicates you need to p the value of the UNDO_RETENTION parameter. You MAY also need to increase the UNDO tablespace size. The error does not report the "Query Duration" accurately.

Ian MacGregor
Stanford Linear Accelerator
ian@(protected)

-----Original Message-----
From: Guang Mei [mailto:gmei@(protected)]
Sent: Monday, March 01, 2004 8:33 PM
To: oracle-l@(protected)
Subject: RE: query slow in 9i, but not slow in 8i

I understand that the cost value does not mean anything. But from my experience, when I see a query's cost from explain plan reaches six digits, usually something is not right.

Anyway the actual query has much more stuff in it. The sql I showed in my orginal post was only part of it and that was the part that slowed the thing down.
I can not run the sql on 9i now since they are other stuff running. But the orgianl complex query took 20 minutes to finish on 8i, and the exact query produced this in 9i's alert log file:

ORA-01555 caused by SQL statement below (Query Duration=199335 sec, SCN:
0x0000.01db218e):
Sun Feb 29 23:20:46 2004
select distinct accession, id
from   (
select distinct to_char(PID) accession, identifier.ID
from   mt.genbankinfo, mt.seqtable, mt.identifier
where  seqtable.ID = genbankinfo.SeqtabID
    and   seqtable.GeneID = identifier.GeneID
    and   PID is not null
    and   identifier.type != 'A'
    and   identifier.speciesid in
(24,31,2,19,18,17,23,21,27,32,20,34,30,22,25,26,28,29)
    and   nvl(seqtable.valid, 'Y') != 'N'
union all
select distinct upper(strings.strtok(protein_id, '.')) accession,
    identifier.id
from   mt.genbankinfo, mt.seqtable, mt.identifier
where  seqtable.ID = genbankinfo.SeqtabID
    and   seqtable.GeneID = identifier.GeneID
    and   protein_id is not null
    and   identifier.t
.....


Needless to say, this is the message that got me started to work on this query.

Guang

On Mon, 1 Mar 2004, Justin Cave (DDBC) wrote:

> You cannot directly compare the cost of two queries-- a query with a cost of 100 may run significantly faster or significantly slower than a query with a cost of 1,000,000. Two queries with a cost of 100 may take vastly different amounts of time to execute.
>
> Are you actually timing the query, or are you just looking at the cost and trying to make decisions based on that?
>
> Justin Cave
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
>
> -----Original Message-----
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)
> Sent: Monday, March 01, 2004 8:33 PM
> To: oracle-l@(protected)
> Subject: Re: query slow in 9i, but not slow in 8i
>
> When I put hint use_hash in the sql, in 9i the subquery did take the
> hint and explain plan showed it used "hash join" instead of "merge
> join", but the cost was higher (than "merge join"), and "SORT
> (UNIQUE)" still caused cost to jump 10 times. Therefore using hint
> "use_hash" didn't solve my problem.
>
> I can not bounce the 9i db to try all the init parameters now since
> there are other programs running. But I strongly suspect the "problem"
> is not due to "hash join" and "merge join" (becuase both showed
> acceptable cost values in explain plan, and "hash join" has been
> working for this sql for the past year or so, never failed), but due
> to some optimizer change(s) that oracle made on 9i about sorting of a large view result set.
>
> I have had four cases so far that a sql which ran fast in 8i became
> painfully slow in 9i, I need either setting some obsolete parameters
> or re-write sql to have them run fast in 9i. So far I have not had a
> situation that the some sql automatically runs much faster in 9i than 8i.
> I have spent quite some time "fixing" these situations and I have not
> had a complete successful run on our application yet.
>
> I have not set "optimizer_features_enable=8.1.7" yet, but it looks
> like I might have to.
>
> Anyway, here is the workarea_size_policy value in my 9i db:
>
> SQL> select NAME,VALUE from v$parameter where name like '%work%';
>
> NAME
> ----------------------------------------------------------------
> VALUE
> ----------------------------------------------------------------------
> ----------
> workarea_size_policy
> AUTO
>
> Would this value have some effect on my query?
>
>
> Guang
>
>
>
> On Tue, 2 Mar 2004, zhu chao wrote:
>
> > hi, guang:
> >   If all other SQL performs well in the new 9i instance, I would
> > consider using dbms_stats to transfer the statistics from 8i to 9i
> > and check if the explain plan goes better, if it still does not
> > work, I would use HINT. Since using use_hash get the good plan,
> > what do you means by saying: sort opration costs too high? Is it
> > the actual SQL cost more time or just the explain cost get high? IF
> > only the explain cost high, I won't care about it at all.
> >   Do you use workarea_size_policy=auto in 9i?
> >   If more SQLs get bad, I would consider using
> > optimizer_features_enable=8.1.7 and other optimizer related
> > parameter the same as 8i.
> >
> > Regards
> > Zhu Chao
> >
> > ----- Original Message -----
> > From: "Guang Mei" <gmei@(protected)>
> > To: "Oracle-L-freelists" <oracle-l@(protected)>
> > Sent: Tuesday, March 02, 2004 1:09 AM
> > Subject: query slow in 9i, but not slow in 8iut
> >
> >
> > > Hi:
> > >
> > > I have a query which gave two very different explain plan on 8173
> > > and
> > 9204.
> > > The two instances (on two separate Sun Solaris boxes) both have
> > > the same db_file_multiblock_read_count (8), block_size (8k),
> > > sort_area_size
> > > (90000000) and sort_area_retained_size (9000000). I narrowed down
> > > the part which causeed this:
> > >
> > > select distinct accession2, id from ( select accession2,
> > >      Identifier.id
> > > from   mt.External_accession, mt.identifier
> > > where  external_accession.SEQTABLEID = identifier.seqtabid and
> > >      identifier.type != 'A' and
> > >      identifier.speciesid in
> > > (24,31,2,19,18,17,23,21,27,32,20,34,30,22,25,26,28,29)
> > > );
> > >
> > >
> > > -- on 9204 (slow):
> > >
> > > Execution Plan
> > > ----------------------------------------------------------
> > >   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=118228 Card=8046044
> > >       Bytes=225289232)
> > >
> > >   1   0  SORT (UNIQUE) (Cost=118228 Card=8046044 Bytes=225289232)
> > >   2   1   MERGE JOIN (Cost=1102 Card=8046044 Bytes=225289232)
> > >   3   2     TABLE ACCESS (BY INDEX ROWID) OF 'EXTERNAL_ACCESSION'
> > >       (Cost=826 Card=28898835 Bytes=404583690)
> > >
> > >   4   3      INDEX (FULL SCAN) OF 'EXTACC_SEQTABID_INDEX' (NON-UN
> > >       IQUE) (Cost=26 Card=28898835)
> > >
> > >   5   2     SORT (JOIN) (Cost=276 Card=51045 Bytes=714630)
> > >   6   5      TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=96 Card=51
> > >       045 Bytes=714630)
> > >
> > >
> > > -- on 8173 (not slow):
> > >
> > > Execution Plan
> > > ----------------------------------------------------------
> > >   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=77324 Card=6490972 B
> > >       ytes=188238188)
> > >
> > >   1   0  SORT (UNIQUE) (Cost=77324 Card=6490972 Bytes=188238188)
> > >   2   1   HASH JOIN (Cost=41347 Card=6490972 Bytes=188238188)
> > >   3   2     TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=95 Card=3892
> > >       9 Bytes=545006)
> > >
> > >   4   2     TABLE ACCESS (FULL) OF 'EXTERNAL_ACCESSION' (Cost=4111
> > >       4 Card=38102138 Bytes=571532070)
> > >
> > >
> > > It looks like 9i thinks MERGE JOIN is better than HASH JOIN for the
> > subquery
> > > (which is fine). The problem is that when I have "select distinct
> > > accession2, id from ..." from the outside, the "SORT (UNIQUE)" part makes
> > > the cost 100 times higher in 9204 (from 1102 to 118228) while in 8173 it
> > > only increases the cost less than two times (from 41347 to 77324).
> > >
> > > I tried the a couple of ways in 9i, such as adding a hint /*+
> > > USE_HASH(identifier) */ in the subquery. This did results in the subquery
> > > using hash join instead of merge join, but it did not solve the problem.
> > The
> > > cost still went to 100 times higher when I put "select distinct
> > accession2,
> > > id from ..." there. I aslo changed the init parameter
> > > "optimizer_max_permutations" to 80000 for the session but it did not help
> > > either.
> > >
> > > So my question is:
> > >
> > > 1. What is the reason that in 9204 the sort opration costs that high while
> > > it does not in 8173?
> > >
> > > 2. Any work around?
> > >
> > > TIA.
> > >
> > > Guang
> > >
> > >
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request@(protected)
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> > >
> > >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@(protected)
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------