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: Parallel query on when it's not supposed to be (?)

Bobak, Mark

2004-09-14

Replies:
Janine, yes, for that table, do:
alter table table_name parallel (degree 1);
and also set parallel_max_servers to 0, as the other Mark suggested.

Um, the table w/ degree set to default, was that table involved in
the problem query you described in the original post?

-----Original Message-----
From: Janine A Sisk [mailto:janine@(protected)]
Sent: Tuesday, September 14, 2004 4:20 PM
To: Bobak, Mark
Cc: oracle-l@(protected)
Subject: Re: Parallel query on when it's not supposed to be (?)


Here you go:

SQL> select table_name from dba_tables where degree >1;
select table_name from dba_tables where degree >1
                          *
ERROR at line 1:
ORA-01722: invalid number


SQL> select index_name from dba_indexes where degree >1;

no rows selected

The error turned out to be because degree is a varchar2(10) in=20
dba_tables, so I did this instead:

SQL> select distinct degree from dba_tables;

DEGREE
-------------------------------
      1
  DEFAULT

Interesting... I think the DEFAULT might be the problem, because the=20
tables that have this set are very closely related to this query. =20
Should I change them to 1? Actually, neither of those values is=20
exactly as it appears:

SQL> select distinct '|' || degree || '|' from dba_tables;

'|'||DEGREE||'|'
---------------------------------
|      1|
|  DEFAULT|

So I guess I would change it to 3 spaces + 1. Sounds like a magic=20
incantation or something!

thanks,

janine

On Sep 14, 2004, at 4:10 PM, Bobak, Mark wrote:

> Janine,
>
> Try this:
>
> select table_name from dba_tables where degree >1;
> select index_name from dba_indexes where degree >1;
>
> -----Original Message-----
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)
> Sent: Tuesday, September 14, 2004 3:58 PM
> To: oracle-l@(protected)
> Subject: Parallel query on when it's not supposed to be (?)
>
>
> Hi all,
>
> I have a mystery on my hands. There must be a good explanation, but=20
> so=3D20
> far it has eluded me; I'm hoping someone out there will know the=3D20
> answer.
>
> Configuration: Oracle 8.1.7.4 64-bit on Solaris 9. System is a=3D20
> SunFire V440.
>
> As far as I can tell, this system does not have the parallel =
query=3D20
> facility turned on:
>
> SQL> show parameters para
>
> NAME                      TYPE   VALUE
> ------------------------------------ -------=3D20
> ------------------------------
> fast_start_parallel_rollback      string FALSE
> optimizer_percent_parallel       integer 0
> parallel_adaptive_multi_user      boolean FALSE
> parallel_automatic_tuning        boolean FALSE
> parallel_broadcast_enabled       boolean FALSE
> parallel_execution_message_size    integer 2152
> parallel_instance_group         string
> parallel_max_servers           integer 5
> parallel_min_percent           integer 0
> parallel_min_servers           integer 0
> parallel_server               boolean FALSE
>
> NAME                      TYPE   VALUE
> ------------------------------------ -------=3D20
> ------------------------------
> parallel_server_instances        integer 1
> parallel_threads_per_cpu         integer 2
> recovery_parallelism           integer 2
>
> Everything is off, right? Even parallel rollback has been disabled.
>
> I have a very simple query:
>
> select count(dotlrn_member_rels_approved.rel_id)
> from  dotlrn_member_rels_approved
> where dotlrn_member_rels_approved.community_id =3D3D 1767463;
>
> dotlrn_member_rels_approved is a view, which is a subset of =
another=3D20
> view, which is a simple join. Nothing obviously tricky there.
>
> This query, when profiled via autotrace, produces the following=3D20
> execution plan:
>
> Execution Plan
> ----------------------------------------------------------
>   0    SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D64 =
Card=3D3D1 =3D
> Bytes=3D3D31)
>   1   0  SORT (AGGREGATE)
>   2   1   SORT* (AGGREGATE)                          =

> =3D20
> :Q428002
>   3   2     NESTED LOOPS* (Cost=3D3D64 Card=3D3D65 =
Bytes=3D3D2015)   =20
>  =3D
>   =3D20
> :Q428002
>   4   3      HASH JOIN* (Cost=3D3D64 Card=3D3D65 Bytes=3D3D1690) =
  =20
>  =3D
>   =3D20
> :Q428002
>   5   4       TABLE ACCESS* (BY INDEX ROWID) OF 'ACS_RELS'=20
> (Cost=3D20
> :Q428000
>        =3D3D17 Card=3D3D65 Bytes=3D3D780)
>
>   6   5         INDEX (RANGE SCAN) OF =
'ACS_RELS_OBJECT_ID_ONE_ID
>        X' (NON-UNIQUE) (Cost=3D3D1 Card=3D3D65)
>
>   7   4       TABLE ACCESS* (FULL) OF 'MEMBERSHIP_RELS' =3D
> (Cost=3D3D46=3D20
> :Q428001
>         Card=3D3D34577 Bytes=3D3D484078)
>
>   8   3      INDEX* (UNIQUE SCAN) OF=20
> 'DOTLRN_MEMBER_RELS_REL_ID_P=3D20
> :Q428002
>        K' (UNIQUE)
>
>
>
>   2 PARALLEL_TO_SERIAL        SELECT /*+ PIV_SSF */=3D20
> SYS_OP_MSR(COUNT(A1.C0
>                         )) FROM (SELECT /*+ ORDERED=20
> NO_EXPAN
>
>   3 PARALLEL_COMBINED_WITH_PARENT
>   4 PARALLEL_COMBINED_WITH_PARENT
>   5 PARALLEL_FROM_SERIAL
>   7 PARALLEL_TO_PARALLEL       SELECT /*+ Q428001 NO_EXPAND=3D20
> ROWID(A1) */ A1
>                         ."REL_ID" C0,A1."MEMBER_STATE" C1=20
> FR
>
>   8 PARALLEL_COMBINED_WITH_PARENT
>
> Huh? How is this possible?
>
> Furthermore, when I do a 10046 event trace and look at the tkprof=3D20
> report, I don't see anything about parallel:
>
> Rows   Row Source Operation
> ------- ---------------------------------------------------
>     1 SORT AGGREGATE
>     0  SORT AGGREGATE
>     0   NESTED LOOPS
>     0   HASH JOIN
>     0    TABLE ACCESS BY INDEX ROWID ACS_RELS
>    109     INDEX RANGE SCAN (object id 26428)
>     0    TABLE ACCESS FULL MEMBERSHIP_RELS
>     0   INDEX UNIQUE SCAN (object id 26694)
>
> I have even tried doing an "explain plan for" and then running=3D20
> utlxplp.sql to look at the plan, but the parallel part is not there.
>
> Even more perplexing, it does look like parallel query is turned =
on,=3D20
> despite the parameter settings:
>
> SQL> select * from v_$px_process;
>
> SERV STATUS       PID SPID         SID   SERIAL#
> ---- --------- ---------- --------- ---------- ----------
> P000 AVAILABLE      24 5175
> P001 AVAILABLE      26 5177
> P002 AVAILABLE      27 5179
> P003 AVAILABLE      28 5181
> P004 AVAILABLE      29 5183
>
> I first started looking at this yesterday and at that time, this=20
> select=3D20
> returned no rows. But now it does. I assume that executing the=20
> query=3D20
> caused the parallel facility to "wake up" since it was needed, but=20
> I'm=3D20
> only guessing. This is the development server and it's fairly=20
> lightly=3D20
> used (compared to the production server), so it's not implausible=20
> that=3D20
> the query doesn't get executed very often.
>
> One last detail: fast_start_parallel_rollback was originally set =
to=3D20
> LOW, which I believe is the default. We set it to FALSE via an=20
> "alter=3D20
> system" command but it doesn't seem to have changed anything. This=20
> may=3D20
> not be important but I mention it for the sake of completeness.
>
> BTW, the reason I care about this is that I'm trying to tune the=3D20
> production server and a fair number of waits associated with=20
> parallel=3D20
> query are showing up in the statspack report. Since parallel query=20
> is=3D20
> not supposed to be turned on there either, I started looking into =
it=3D20
> and found that both systems are exhibiting this bizarre (to me,=20
> anyway)=3D20
> behavior.
>
> Can anyone a) explain what the heck is going on here and b) tell me=20
> how=3D20
> to drive a stake through the heart of parallel query on this system?
>
> thanks,
>
> janine
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l