Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 3B15D10035EFA1 for ; Thu, 25 Mar 2021 17:52:02 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id D664B40996; Thu, 25 Mar 2021 16:51:55 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C33B23F775; Thu, 25 Mar 2021 16:51:55 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1616691115; bh=qm5FEnVIC+rJWXGSm/4yBS4xXvYu+ojxCSGO8gsREmk=; h=From:Sender:Sender:From; b=XwkNKmDeqCYTo9i4Ty7FCSiOKw+QvNGVSxD9LeXqJoiAKqZ6HZnMCB765vEqanV2D eq6iiVCkwexrNyXe2qvpcqo+F9VA3HlNtkw48sG8ez5YG4OSJ0mTZCfOpiksb2H2e6 N0AX9GQq086PZew5OfKWp+rJ37I6MwlZ5xdSZBv4= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id CrvGokn1pXG1; Thu, 25 Mar 2021 16:51:55 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 95CEE3F797; Thu, 25 Mar 2021 16:51:52 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1616691113; bh=qm5FEnVIC+rJWXGSm/4yBS4xXvYu+ojxCSGO8gsREmk=; h=From:Sender:Sender:From; b=PPh26yyWBGGAVD38d0yJysUZ2zsCErL7deproJtgNqCn4d06dBxxihrLlVIR9S7x4 NmFxPMV5E2A2f8UGJEL3tbfd2oz/iUn23CNdOQi+MNGDBGpXeEg0OtYGb55TjOPQYb hu3XB7JNmNOzxTT6Kw5hANqDm3MiL7a1VnoLYhsU= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 25 Mar 2021 16:51:51 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 213C13F768 for ; Thu, 25 Mar 2021 16:51:51 +0000 (UTC) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 7SzVNeXpiuuY for ; Thu, 25 Mar 2021 16:51:51 +0000 (UTC) Received: from gw2.tidalhosting.net (gateway.tidalhosting.net [155.130.128.124]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id E90483F765 for ; Thu, 25 Mar 2021 16:51:50 +0000 (UTC) Received: from mrtr.tidalhosting.net (UnknownHost [10.0.150.3]) by gw2.tidalhosting.net with SMTP (version=TLS\Tls12 cipher=Aes256 bits=256); Thu, 25 Mar 2021 12:51:42 -0400 Received: from mx1.tidalhosting.net (unknown [10.0.100.9]) by mrtr.tidalhosting.net (Postfix) with ESMTP id 87929304A54E; Thu, 25 Mar 2021 12:51:37 -0400 (EDT) Received: from mwf4500 (c-73-238-99-41.hsd1.ma.comcast.net [73.238.99.41]) by mx1.tidalhosting.net with SMTP (version=Tls cipher=Aes256 bits=256); Thu, 25 Mar 2021 12:46:16 -0400 From: "Mark W. Farnham" To: , "'Kim Berg Hansen'" , "'Oracle L'" References: In-Reply-To: Subject: RE: Weird "ORA-03113: end-of-file on communication channel" during parse Date: Thu, 25 Mar 2021 12:46:08 -0400 Message-ID: <0df001d72196$5b509e20$11f1da60$@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0DF1_01D72174.D442CEB0" Content-Language: en-us X-Exim-Id: 0df001d72196$5b509e20$11f1da60$ X-archive-position: 79387 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l ------=_NextPart_000_0DF1_01D72174.D442CEB0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable On the theory that one off the end might be a fubared end of leaf = marking on the index for the range scan, I offer that at this size = recreating the index is cheap enough to try for the .042% chance that is = the problem. =20 Adding and deleting one more -1 row might not affect the leaf holding = the last in order of the range scan. =20 I suppose updating all the -1 to -2 and then setting them all back to -1 = might also work, but that is more work than drop create. =20 mwf =20 From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, March 25, 2021 12:33 PM To: Kim Berg Hansen; Oracle L Subject: Re: Weird "ORA-03113: end-of-file on communication channel" = during parse =20 Just to be certain - since I may have got a type into the hint - did the = index path change to just: "TABLE ACCESS BY INDEX ROWID" =20 Regards Jonathan Lewis =20 =20 On Thu, 25 Mar 2021 at 16:00, Kim Berg Hansen wrote: Unfortunately no luck, Jonathan. =20 Hint no_batch_table_access_by_rowid makes no difference. There is no "rowsets" reported. Disabling rowsets makes no difference. =20 =20 Cheerio /Kim =20 =20 On Thu, Mar 25, 2021 at 4:21 PM Jonathan Lewis = wrote: =20 This looks like the runtime engine failing either on rowsets or on = batching (e.g. array processing and managing to fall off the end of the = calculated array). =20 Have you tried adding the hint /*+ no_batch_table_access_by_rowid(@sel$1 = sd@sel$1) */ Can you check a working index-access plan with format=3D>'projection' to = see if it reports "rowsets" for the access, and disable the feature if = it's in use. (alter session set "_rowsets_enabled"=3Dfalse;) =20 =20 Regards Jonathan Lewis =20 =20 On Thu, 25 Mar 2021 at 14:13, Kim Berg Hansen wrote: Hi, List I have an ORA-03113 error on a Windows 12.1.0.2 that seems to happen = during parse? I don't have a good idea about what happens, so any ideas would be nice. Database version: SQL> select banner 2 from v$version; BANNER -------------------------------------------------------------------------= ------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit = Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for 64-bit Windows: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production I do a simple query and my connection breaks with an ORA-03113: SQL> select sd.iddelivery 2 from splitdelivery sd 3 where sd.delpackoutloc =3D -1; select sd.iddelivery * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 3964 Session ID: 38 Serial number: 45993 Adding a superfluous NVL makes it work - the data can be retrieved = (doesn't look like corrupted data): SQL> set autotrace traceonly SQL> select sd.iddelivery 2 from arrow.splitdelivery sd 3 where nvl(sd.delpackoutloc, 0) =3D -1; 343251 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 87431401 -------------------------------------------------------------------------= ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| = Time | -------------------------------------------------------------------------= ---------- | 0 | SELECT STATEMENT | | 343K| 3352K| 3075 (1)| = 00:00:01 | |* 1 | TABLE ACCESS FULL| SPLITDELIVERY | 343K| 3352K| 3075 (1)| = 00:00:01 | -------------------------------------------------------------------------= ---------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NVL("SD"."DELPACKOUTLOC",0)=3D(-1)) Statistics ---------------------------------------------------------- 67 recursive calls 0 db block gets 33210 consistent gets 0 physical reads 0 redo size 4592111 bytes sent via SQL*Net to client 251949 bytes received via SQL*Net from client 22885 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 343251 rows processed But the erroneous statement gives ORA-03113 even when trying just to = explain it: SQL> explain plan for 2 select sd.iddelivery 3 from splitdelivery sd 4 where sd.delpackoutloc =3D -1; explain plan for * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 11760 Session ID: 72 Serial number: 52015 The column in the predicate is very skewed - 343 thousand out of 350 = thousand have the value -1: SQL> select delpackoutloc, count(*) 2 from splitdelivery 3 group by rollup(delpackoutloc) 4 order by delpackoutloc; DELPACKOUTLOC COUNT(*) ------------- ---------- -1 343251 1 417 2 525 3 5865 350058 The column has no NULL values, but it is not defined as NOT NULL. =20 The query above that fails searches for the value -1. If I search for = values 1, 2 or 3, there is no issue at all, works fine. The table/index statistics are recently gathered - gathered number of = rows fit exactly: SQL> select num_rows 2 from user_tables 3 where table_name =3D 'SPLITDELIVERY'; NUM_ROWS ---------- 350058 And column histogram also fit exactly the current values: SQL> select endpoint_value, endpoint_number, endpoint_number - = lag(endpoint_number) over (order by endpoint_number) as num 2 from user_histograms 3 where table_name =3D 'SPLITDELIVERY' 4 and column_name =3D 'DELPACKOUTLOC' 5 order by endpoint_number; ENDPOINT_VALUE ENDPOINT_NUMBER NUM -------------- --------------- ---------- -1 343251 1 343668 417 2 344193 525 3 350058 5865 There's an index on the column and stats are current here as well: SQL> select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, = avg_data_blocks_per_key, clustering_factor, num_rows, sample_size 2 from user_indexes 3 where table_name =3D 'SPLITDELIVERY' 4 and index_name =3D 'SPLITDELIVERY_IND01'; BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY = AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE ---------- ----------- ------------- ----------------------- = ----------------------- ----------------- ---------- ----------- 2 864 4 216 = 3393 13573 350058 350058 I noticed it worked by adding AND ROWNUM <=3D xxx, so I tried different = values and found that 343250 (one less than the actual row count) worked = fine: SQL> set autotrace traceonly SQL> select sd.iddelivery 2 from splitdelivery sd 3 where sd.delpackoutloc =3D -1 4 and rownum <=3D 343250; 343250 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1924021054 -------------------------------------------------------------------------= ----------------------------------- | Id | Operation | Name | = Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------= ----------------------------------- | 0 | SELECT STATEMENT | | = 343K| 3352K| 14182 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | = | | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SPLITDELIVERY | = 343K| 3352K| 14182 (1)| 00:00:01 | |* 3 | INDEX RANGE SCAN | SPLITDELIVERY_IND01 | = | | 852 (1)| 00:00:01 | -------------------------------------------------------------------------= ----------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3D343250) 3 - access("SD"."DELPACKOUTLOC"=3D(-1)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 56495 consistent gets 0 physical reads 0 redo size 4592104 bytes sent via SQL*Net to client 251962 bytes received via SQL*Net from client 22885 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 343250 rows processed That worked fine, though I find it weird that it chooses an INDEX RANGE = SCAN + TABLE ACCESS BY INDEX when it knows that it is going to retrieve = 98% of the rows? A full table scan would use fewer gets as seen in the = autotrace above? Anyway, ORA-03113 appears when I add 1 to 343250 making it 343251 (which = is the exact number of rows having the value -1, which the optimizer = knows from the histogram): SQL> select sd.iddelivery 2 from splitdelivery sd 3 where sd.delpackoutloc =3D -1 4 and rownum <=3D 343251; select sd.iddelivery * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 11164 Session ID: 72 Serial number: 37013 Same for just calling EXPLAIN PLAN - it works for 343250: SQL> explain plan for 2 select sd.iddelivery 3 from splitdelivery sd 4 where sd.delpackoutloc =3D -1 5 and rownum <=3D 343250; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------- Plan hash value: 1924021054 -------------------------------------------------------------------------= ----------------------------------- | Id | Operation | Name | = Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------= ----------------------------------- | 0 | SELECT STATEMENT | | = 343K| 3352K| 14182 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | = | | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SPLITDELIVERY | = 343K| 3352K| 14182 (1)| 00:00:01 | |* 3 | INDEX RANGE SCAN | SPLITDELIVERY_IND01 | = | | 852 (1)| 00:00:01 | -------------------------------------------------------------------------= ----------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3D343250) 3 - access("SD"."DELPACKOUTLOC"=3D(-1)) 16 rows selected. But it fails for 343251: SQL> explain plan for 2 select sd.iddelivery 3 from splitdelivery sd 4 where sd.delpackoutloc =3D -1 5 and rownum <=3D 343251; explain plan for * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 10888 Session ID: 457 Serial number: 32869 At the moment I have no access to trace files or alert log on this = server. Might get it after the weekend. Also it is a development server, nothing urgent to solve. I'm only = diving a bit deeper for curiosity as well as check if this potentially = could happen in production database as well. It'll be easier if/when I get trace access, but do anyone have an idea = about what might be happening here? Thanks in advance. =20 Cheerio /Kim =20 =20 Regards =20 =20 Kim Berg Hansen Senior Consultant at Trivadis Oracle ACE Director =20 Author of Practical Oracle SQL = =20 http://www.kibeha.dk kibeha@kibeha.dk @kibeha =20 =20 ------=_NextPart_000_0DF1_01D72174.D442CEB0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

On the theory that one off the end might be a fubared end of leaf = marking on the index for the range scan, I offer that at this size = recreating the index is cheap enough to try for the .042% chance that is = the problem.

 

Adding and deleting one more -1 row might not affect the leaf holding = the last in order of the range scan.

 

I suppose updating all the -1 to -2 and then setting them all back to = -1 might also work, but that is more work than drop = create.

 

mwf

 

From:= = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Jonathan Lewis
Sent: Thursday, March 25, = 2021 12:33 PM
To: Kim Berg Hansen; Oracle L
Subject: = Re: Weird "ORA-03113: end-of-file on communication channel" = during parse

 

Just to be certain - since I may have got a type into = the hint - did the index path change to just: "TABLE ACCESS BY = INDEX ROWID"

 

Regards

Jonathan Lewis

 

 

On = Thu, 25 Mar 2021 at 16:00, Kim Berg Hansen <kibeha@gmail.com> = wrote:

Unfortunately no luck, Jonathan.

 

Hint no_batch_table_access_by_rowid makes no = difference.

There is no = "rowsets" reported.

Disabling rowsets makes no = difference.

 

 

Cheerio

/Kim

 

 

On = Thu, Mar 25, 2021 at 4:21 PM Jonathan Lewis <jlewisoracle@gmail.com> = wrote:

 

This looks like the runtime engine failing either on = rowsets or on batching (e.g. array processing and managing to fall off = the end of the calculated array).

 

Have you tried adding the hint /*+ = no_batch_table_access_by_rowid(@sel$1 sd@sel$1) = */

Can you check a working = index-access plan with format=3D>'projection' to see if it reports = "rowsets" for the access, and disable the feature if it's in = use.

(alter session set = "_rowsets_enabled"=3Dfalse;)

 

 

Regards

Jonathan Lewis

 

 

On = Thu, 25 Mar 2021 at 14:13, Kim Berg Hansen <kibeha@gmail.com> = wrote:

Hi, List

I have an ORA-03113 error = on a Windows 12.1.0.2 that seems to happen during parse?
I don't have = a good idea about what happens, so any ideas would be = nice.

Database version:

SQL> select banner
  2 =  from = v$version;
BANNER
-------------------------------------------------= -------------------------------
Oracle Database 12c Enterprise = Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release = 12.1.0.2.0 - Production
CORE    12.1.0.2.0     =  Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - = Production
NLSRTL Version 12.1.0.2.0 - = Production



I do a simple query and my = connection breaks with an ORA-03113:

SQL> = select sd.iddelivery
  2  from splitdelivery sd
  3 =  where sd.delpackoutloc =3D -1;
select = sd.iddelivery
*
ERROR at line 1:
ORA-03113: end-of-file on = communication channel
Process ID: 3964
Session ID: 38 Serial = number: 45993



Adding a superfluous NVL makes it = work - the data can be retrieved (doesn't look like corrupted = data):

SQL> set autotrace = traceonly
SQL> select sd.iddelivery
  2  from = arrow.splitdelivery sd
  3  where nvl(sd.delpackoutloc, 0) = =3D -1;
343251 rows selected.

Execution = Plan
----------------------------------------------------------
Pla= n hash value: = 87431401
-------------------------------------------------------------= ----------------------
| Id  | Operation       =   | Name          | Rows  | Bytes | = Cost (%CPU)| Time     = |
--------------------------------------------------------------------= ---------------
|   0 | SELECT STATEMENT  |     =           |   343K|  3352K| =  3075   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS = FULL| SPLITDELIVERY |   343K|  3352K|  3075   (1)| = 00:00:01 = |
--------------------------------------------------------------------= ---------------
Predicate Information (identified by operation = id):
---------------------------------------------------
  =  1 - = filter(NVL("SD"."DELPACKOUTLOC",0)=3D(-1))

Sta= tistics
----------------------------------------------------------
=          67  recursive calls
  =         0  db block gets
    =   33210  consistent gets
          = 0  physical reads
          0 =  redo size
    4592111  bytes sent via SQL*Net to = client
     251949  bytes received via SQL*Net = from client
      22885  SQL*Net roundtrips = to/from client
          1  sorts = (memory)
          0  sorts = (disk)
     343251  rows = processed



But the erroneous statement gives = ORA-03113 even when trying just to explain it:

SQL> = explain plan for
  2  select sd.iddelivery
  3 =  from splitdelivery sd
  4  where sd.delpackoutloc =3D = -1;
explain plan for
*
ERROR at line 1:
ORA-03113: = end-of-file on communication channel
Process ID: 11760
Session ID: = 72 Serial number: 52015



The column in the predicate is = very skewed - 343 thousand out of 350 thousand have the value = -1:

SQL> select delpackoutloc, = count(*)
  2  from splitdelivery
  3  group by = rollup(delpackoutloc)
  4  order by = delpackoutloc;
DELPACKOUTLOC   COUNT(*)
------------- = ----------
           -1     = 343251
            1     =    417
            2   =      525
            3 =       5865
            =       350058


The column has no NULL values, but it is not = defined as NOT NULL.

 

The query above that fails searches for the value -1. = If I search for values 1, 2 or 3, there is no issue at all, works = fine.


The table/index statistics are = recently gathered - gathered number of rows fit = exactly:

SQL> select num_rows
  2 =  from user_tables
  3  where table_name =3D = 'SPLITDELIVERY';
  NUM_ROWS
----------
    = 350058


And column histogram also fit exactly = the current values:

SQL> select endpoint_value, = endpoint_number, endpoint_number - lag(endpoint_number) over (order by = endpoint_number) as num
  2  from user_histograms
  = 3  where table_name =3D 'SPLITDELIVERY'
  4  and = column_name =3D 'DELPACKOUTLOC'
  5  order by = endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER       =  NUM
-------------- --------------- ----------
    =         -1         =  343251
             1   =        343668       =  417
             2   =        344193       =  525
             3   =        350058       = 5865


There's an index on the column and = stats are current here as well:

SQL> select blevel, leaf_blocks, = distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, = clustering_factor, num_rows, sample_size
  2  from = user_indexes
  3  where table_name =3D = 'SPLITDELIVERY'
  4  and index_name =3D = 'SPLITDELIVERY_IND01';
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS = AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   = NUM_ROWS SAMPLE_SIZE
---------- ----------- ------------- = ----------------------- ----------------------- ----------------- = ---------- -----------
         2   =       864             4 =                     = 216                   =  3393             13573     = 350058      350058



I noticed it = worked by adding AND ROWNUM <=3D xxx, so I tried different values and = found that 343250 (one less than the actual row count) worked = fine:

SQL> set autotrace = traceonly
SQL> select sd.iddelivery
  2  from = splitdelivery sd
  3  where sd.delpackoutloc =3D = -1
  4  and rownum <=3D 343250;
343250 rows = selected.

Execution = Plan
----------------------------------------------------------
Pla= n hash value: = 1924021054
-----------------------------------------------------------= -------------------------------------------------
| Id  | = Operation                   =          | Name         =        | Rows  | Bytes | Cost (%CPU)| Time =     = |
--------------------------------------------------------------------= ----------------------------------------
|   0 | SELECT = STATEMENT                   =   |                   =   |   343K|  3352K| 14182   (1)| 00:00:01 |
|* =  1 |  COUNT STOPKEY             =           |           =           |       |   =     |            |     =      |
|   2 |   TABLE ACCESS BY INDEX ROWID = BATCHED| SPLITDELIVERY       |   343K|  3352K| = 14182   (1)| 00:00:01 |
|*  3 |    INDEX RANGE = SCAN                  | = SPLITDELIVERY_IND01 |       |       | =   852   (1)| 00:00:01 = |
--------------------------------------------------------------------= ----------------------------------------
Predicate Information = (identified by operation = id):
---------------------------------------------------
  =  1 - filter(ROWNUM<=3D343250)
   3 - = access("SD"."DELPACKOUTLOC"=3D(-1))

Statistics=
----------------------------------------------------------
  =         0  recursive calls
    =       0  db block gets
      56495 =  consistent gets
          0 =  physical reads
          0  redo = size
    4592104  bytes sent via SQL*Net to = client
     251962  bytes received via SQL*Net = from client
      22885  SQL*Net roundtrips = to/from client
          0  sorts = (memory)
          0  sorts = (disk)
     343250  rows = processed



That worked fine, though I find = it weird that it chooses an INDEX RANGE SCAN + TABLE ACCESS BY INDEX = when it knows that it is going to retrieve 98% of the rows? A full table = scan would use fewer gets as seen in the autotrace above?

Anyway, = ORA-03113 appears when I add 1 to 343250 making it 343251 (which = is the exact number of rows having the value -1, which the optimizer = knows from the histogram):

SQL> select = sd.iddelivery
  2  from splitdelivery sd
  3 =  where sd.delpackoutloc =3D -1
  4  and rownum <=3D = 343251;
select sd.iddelivery
*
ERROR at line 1:
ORA-03113: = end-of-file on communication channel
Process ID: 11164
Session ID: = 72 Serial number: 37013



Same for just calling EXPLAIN = PLAN - it works for 343250:

SQL> explain plan for
  2 =  select sd.iddelivery
  3  from splitdelivery = sd
  4  where sd.delpackoutloc =3D -1
  5  and = rownum <=3D 343250;
Explained.

SQL> select * from = table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------= -------------------------------------------------------------------------= -----------------------------------------------------------------
Plan= hash value: = 1924021054
-----------------------------------------------------------= -------------------------------------------------
| Id  | = Operation                   =          | Name         =        | Rows  | Bytes | Cost (%CPU)| Time =     = |
--------------------------------------------------------------------= ----------------------------------------
|   0 | SELECT = STATEMENT                   =   |                   =   |   343K|  3352K| 14182   (1)| 00:00:01 |
|* =  1 |  COUNT STOPKEY             =           |           =           |       |   =     |            |     =      |
|   2 |   TABLE ACCESS BY INDEX ROWID = BATCHED| SPLITDELIVERY       |   343K|  3352K| = 14182   (1)| 00:00:01 |
|*  3 |    INDEX RANGE = SCAN                  | = SPLITDELIVERY_IND01 |       |       | =   852   (1)| 00:00:01 = |
--------------------------------------------------------------------= ----------------------------------------
Predicate Information = (identified by operation = id):
---------------------------------------------------
  =  1 - filter(ROWNUM<=3D343250)
   3 - = access("SD"."DELPACKOUTLOC"=3D(-1))
16 rows = selected.



But it fails for = 343251:

SQL> explain plan for
  2 =  select sd.iddelivery
  3  from splitdelivery = sd
  4  where sd.delpackoutloc =3D -1
  5  and = rownum <=3D 343251;
explain plan for
*
ERROR at line = 1:
ORA-03113: end-of-file on communication channel
Process ID: = 10888
Session ID: 457 Serial number: 32869



At the moment I have no access to trace files = or alert log on this server. Might get it after the weekend.
Also it = is a development server, nothing urgent to solve. I'm only diving a bit = deeper for curiosity as well as check if this potentially could happen = in production database as well.

It'll be easier if/when I get = trace access, but do anyone have an idea about what might be happening = here?


Thanks in advance.

 

Cheerio

/Kim

 

 

Regards

 

 

Kim Berg Hansen

Senior Consultant at Trivadis

Oracle ACE Director

 

 

------=_NextPart_000_0DF1_01D72174.D442CEB0-- -- http://www.freelists.org/webpage/oracle-l