11.1.0.7 Follow up

As a follow-up to Friday’s post about some local issues with the 11.1.0.7 patch, I’ve distilled an isolated piece of SQL to demonstrate the ORA-03113.

Demo test case looks like this:

DOM@11g>l
    WITH subq_goes_bang_data AS
         (SELECT 'CST1' database_name,'-6' ro_id,'\\xxxxx\uts_dev\SplitterToolSystemTestRO1' ros_path
          FROM   dual)
    ,    subq_db_name   AS
         (SELECT DECODE (INSTR (global_name, '.WORLD'),
                         0,global_name,
                         SUBSTR (global_name,1,INSTR (global_name,'.WORLD') - 1)
                        ) database_name
          FROM   global_name)
   ,    subq_goes_bang AS
       (
        SELECT rrc.ro_id,
               rrc.ros_path
        FROM   subq_goes_bang_data rrc
        WHERE rrc.database_name = (SELECT database_name FROM subq_db_name)
        UNION ALL
        SELECT rrc.ro_id,
               rrc.ros_path
        FROM   subq_goes_bang_data rrc
        WHERE  rrc.database_name = 'CST1'
        AND    NOT EXISTS(SELECT NULL
                          FROM subq_goes_bang_data rrc1
                          WHERE rrc1.database_name =(SELECT database_name FROM subq_db_name)
                          AND rrc1.ro_id = rrc.ro_id)
        AND    'CSP1' <>(SELECT database_name FROM subq_db_name)
   )
   SELECT  '<?xml version="1.0" encoding="UTF-16"?>'
           || XMLELEMENT ("LocalConfiguration",XMLFOREST (a.ros_path AS "RootFilePath"))
   FROM    subq_goes_bang a
  WHERE   a.ro_id = -6

DOM@11g>/
WITH subq_goes_bang_data AS
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Elapsed: 00:00:00.05
DOM@11g>

Comment out the XML bits and bobs and just select the column – no problem.
Comment out the UNION ALL in subq_goes_bang – no problem.

Explain plan looks like this:

DOM@11g>explain plan for
  2  WITH subq_goes_bang_data AS
  3       (SELECT 'CST1' database_name,'-6' ro_id,'\\xxxxx\uts_dev\SplitterToolSystemTestRO1' ros_path 
  4        FROM   dual)
  5  ,    subq_db_name   AS
  6       (SELECT DECODE (INSTR (global_name, '.WORLD'),
  7                       0,global_name,
  8                       SUBSTR (global_name,1,INSTR (global_name,'.WORLD') - 1)
  9                      ) database_name
 10        FROM   global_name)
 11  ,    subq_goes_bang AS
 12      (
 13       SELECT rrc.ro_id,
 14              rrc.ros_path
 15       FROM   subq_goes_bang_data rrc
 16       WHERE rrc.database_name = (SELECT database_name FROM subq_db_name)
 17       UNION ALL
 18       SELECT rrc.ro_id,
 19              rrc.ros_path
 20       FROM   subq_goes_bang_data rrc
 21       WHERE  rrc.database_name = 'CST1'
 22       AND    NOT EXISTS(SELECT NULL
 23                         FROM subq_goes_bang_data rrc1
 24                         WHERE rrc1.database_name =(SELECT database_name FROM subq_db_name)
 25                         AND rrc1.ro_id = rrc.ro_id)
 26       AND    'CSP1' <> (SELECT database_name FROM subq_db_name)
 27  )
 28  SELECT  '<?xml version="1.0" encoding="UTF-16"?>'
 29          || XMLELEMENT ("LocalConfiguration",XMLFOREST (a.ros_path AS "RootFilePath"))
 30  FROM    subq_goes_bang a
 31  WHERE   a.ro_id = -6;

Explained.

Elapsed: 00:00:00.00
DOM@11g>
DOM@11g>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4143783385

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     2 |    86 |    16   (7)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    INDEX FULL SCAN         | SYS_IOT_TOP_57625           |     1 |       |     1   (0)| 00:00:01 |
|   4 |   LOAD AS SELECT           |                             |       |       |            |          |
|*  5 |    TABLE ACCESS FULL       | PROPS$                      |     1 |    27 |     2   (0)| 00:00:01 |
|   6 |   VIEW                     |                             |     2 |    86 |    13   (8)| 00:00:01 |
|   7 |    UNION-ALL               |                             |       |       |            |          |
|*  8 |     VIEW                   |                             |     1 |    53 |     2   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6643_B0B39986 |     1 |    53 |     2   (0)| 00:00
|  10 |      VIEW                  |                             |     1 |  2002 |     2   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6644_B0B39986 |     1 |    27 |     2   (0)| 00:0
|* 12 |     FILTER                 |                             |       |       |            |          |
|* 13 |      HASH JOIN ANTI        |                             |     1 |    57 |     7  (15)| 00:00:01 |
|* 14 |       VIEW                 |                             |     1 |    53 |     2   (0)| 00:00:01 |
|  15 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D6643_B0B39986 |     1 |    53 |     2   (0)| 00:0
|  16 |       VIEW                 | VW_SQ_1                     |     1 |     4 |     4   (0)| 00:00:01 |
|* 17 |        VIEW                |                             |     1 |    10 |     2   (0)| 00:00:01 |
|  18 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D6643_B0B39986 |     1 |    53 |     2   (0)| 00:00:01 |
|  19 |         VIEW               |                             |     1 |  2002 |     2   (0)| 00:00:01 |
|  20 |          TABLE ACCESS FULL | SYS_TEMP_0FD9D6644_B0B39986 |     1 |    27 |     2   (0)| 00:00:01 |
|  21 |      VIEW                  |                             |     1 |  2002 |     2   (0)| 00:00:01 |
|  22 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6644_B0B39986 |     1 |    27 |     2   (0)| 00:0
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("NAME"='GLOBAL_DB_NAME')
   8 - filter(TO_NUMBER("RRC"."RO_ID")=(-6) AND "RRC"."DATABASE_NAME"= (SELECT "DATABASE_NAME"
              FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "DATABASE_NAME" FROM
              "SYS"."SYS_TEMP_0FD9D6644_B0B39986" "T1") "SUBQ_DB_NAME"))
  12 - filter( (SELECT "DATABASE_NAME" FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
              "DATABASE_NAME" FROM "SYS"."SYS_TEMP_0FD9D6644_B0B39986" "T1") "SUBQ_DB_NAME")<>'CSP1')
  13 - access("ITEM_0"="RRC"."RO_ID")
  14 - filter("RRC"."DATABASE_NAME"='CST1' AND TO_NUMBER("RRC"."RO_ID")=(-6))
  17 - filter("RRC1"."DATABASE_NAME"= (SELECT "DATABASE_NAME" FROM  (SELECT /*+ CACHE_TEMP_TABLE
              ("T1") */ "C0" "DATABASE_NAME" FROM "SYS"."SYS_TEMP_0FD9D6644_B0B39986" "T1") "SUBQ_DB_NAME")

43 rows selected.

Elapsed: 00:00:00.01
DOM@11g>

So, to Oracle support with some trace files.

But I’d be interested if anyone else with an 11.1.0.7 environment can run the above statement.

About these ads