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

Lag function problem was: Never ending activity in temp file

Gints Plivna

2005-04-13

Replies:
So I'v dig down a bit more. The problem was that all query seemed to
work well until it get to some analytic functions i.e. all hash joins
and full table scans showed up in longops and ended in a minute or
half. And simple count(*) without where clause most probably worked
well because Oracle simply didn't perform these analytic functions,
because simple count(*) didn't need them.

The problem seems to be in lag function with varying second argument,
i.e. how many rows to look back. I'v created a small test case that
worked similarly on my home 1.8GHz Celeron with 500Mb RAM and 10g, as
well as on work box with parameters I'v described in the first mail.
I'v switched from automatic sort/hash sizing to manual and gave almost
400 M to session that performed select below. As you can see select
with lag(1) worked fine, but select with varying lag(x) I had to kill
after almost 2 hours. And the most frustrating thing is that lag had
to look back 1 or 0 row as you can see from table create script as
well as later. And then you can see that with constant lag(0) and
lag(1) also everything works fine.
And one more thing from these almost 400M sort area the bad query took
only ~80M and no more.

Here is the scenario:

21:50:55 cmis_db@(protected)=
n
21:51:09  2 from inner_join1
21:51:18  3 where rownum < 1500000;

Table created.

Elapsed: 00:00:06.81
21:51:51 cmis_db@(protected)
21:52:27  2 from inner_join1
21:52:43  3 where rownum < 1500000;

1499999 rows created.

Elapsed: 00:01:11.07
21:53:58 cmis_db@(protected);

Commit complete.

Elapsed: 00:00:00.00
21:54:12 cmis_db@(protected);

    COUNT(*)
---------------
2999998                                         =20
                            1 row selected.

Elapsed: 00:00:02.75
21:54:45 cmis_db@(protected);

Session altered.

Elapsed: 00:00:00.00
21:55:06 cmis_db@(protected)%

NAME                      TYPE     VALUE          =20
                                             =20
              ------------------------------------ -----------
------------------------------workarea_size_policy          =20
string    MANUAL

21:55:39 cmis_db@(protected)

NAME                      TYPE     VALUE          =20
                                             =20
              ------------------------------------ -----------
------------------------------sort_area_size              =20
integer   400000000

21:55:51 cmis_db@(protected)
Wrote file afiedt.buf

1 SELECT COUNT(*), code
2 FROM (
3   SELECT
4    id,
5    code,
6    CASE WHEN first_code =3D 'LVA' THEN 0
7       ELSE 1
8    END x
9   FROM (
10     SELECT
11       id,
12       code,
13       lag(code, 1, 'AAA') over (order by id, rn) first_code
14     FROM source
15     WHERE rownum < 2000000
16   )
17 )
18 WHERE x =3D 1
19 GROUP BY code
20* ORDER BY COUNT(*) desc, code
21:56:14 cmis_db@> /

    COUNT(*) COD             =20
--------------- ---
     500001 LVA
1 row selected.

Elapsed: 00:00:08.46
21:56:24 cmis_db@(protected)
Wrote file afiedt.buf

1 SELECT COUNT(*), code
2 FROM (
3   SELECT
4    id,
5    code,
6    CASE WHEN first_code =3D 'LVA' THEN 0
7       ELSE 1
8    END x
9   FROM (
10     SELECT
11       id,
12       code,
13       lag(code, rn - 1, 'AAA') over (order by id, rn) first_code
14     FROM source
15     WHERE rownum < 2000000
16   )
17 )
18 WHERE x =3D 1
19 GROUP BY code
20* ORDER BY COUNT(*) desc, code
21:57:23 cmis_db@> /
   FROM source
      *
ERROR at line 14:
ORA-00028: your session has been killed=20

Elapsed: 01:58:18.81
23:55:46 cmis_db@(protected)
Connected.
23:56:12 cmis_db@(protected);

Session altered.

Elapsed: 00:00:00.00
23:56:19 cmis_db@(protected)%

NAME                      TYPE     VALUE
------------------------------------ ----------- --------------------------=
----
workarea_size_policy   string   MANUAL

23:57:41 cmis_db@(protected);

   MAX(RN-1)     MIN(RN-1)
--------------- ---------------
        1          0

1 row selected.

Elapsed: 00:00:03.40
23:58:57 cmis_db@(protected)
Wrote file afiedt.buf

1 SELECT COUNT(*), code
2 FROM (
3   SELECT
4    id,
5    code,
6    CASE WHEN first_code =3D 'LVA' THEN 0
7       WHEN the_same_code =3D 'LVA' THEN 1
8       ELSE 1
9    END x
10   FROM (
11     SELECT
12       id,
13       code,
14       lag(code, 1, 'AAA') over (order by id, rn) first_code,
15       lag(code, 0, 'AAA') over (order by id, rn) the_same_code
16     FROM source
17     WHERE rownum < 2000000
18   )
19 )
20 WHERE x =3D 1
21 GROUP BY code
22* ORDER BY COUNT(*) desc, code
00:01:43 cmis_db@> /

    COUNT(*) COD
--------------- ---
     500001 LVA

1 row selected.

On 4/13/05, Edgar Chupit <chupit@(protected):
> Dear Gints,
> =20
> First of all nested loops aren't that bad, I would first gather statistic=
s
> for all the tables/indexes, remove hints and see what optimizer can sugge=
st,
> because in 99% of cases optimizer can get it correct, if not than I would
> think how can I improve results. The 'direct path read' is coming exactly
> from hash join, if you will switch from hash join, because hash join uses
> temp tablespace to store intermediate results (when they are bigger than
> hash_area_size). And off course you have to compare execution plans befor=
e
> inserting where flag =3D 1 and after inserting flag =3D 1. I know that th=
ose are
> only generic suggestions, but because you have quite complex query I can'=
t
> create test case to see exactly the same problem that you get. Hope that
> this will somehow help.=20
> =20
> Have a nice day.
> >  Edgar           =20
>
--
http://www.freelists.org/webpage/oracle-l