Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> different results in sql and plsql execution

different results in sql and plsql execution

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 01 Aug 2006 12:00:14 +0200
Message-ID: <44CF262E.8010700@google.com> 





Hi, i am currently expiriencing a weird situation, where the same query 
yields different results in sql and in plsql.
The testcase is very simple:


/**********************************************************************************/

set echo on


set serveroutput on


DECLARE



result boolean;


BEGIN


        IF DBMS_XDB.existsResource('/public/UserFiles') THEN
                
DBMS_XDB.deleteResource('/public/UserFiles',DBMS_XDB.DELETE_RECURSIVE_FORCE);
        END IF;
                result := DBMS_XDB.createFolder('/public/UserFiles');
                result := DBMS_XDB.createFolder('/public/UserFiles/Image');
                result := 
DBMS_XDB.createFolder('/public/UserFiles/Image/css');
                result := 
DBMS_XDB.createFolder('/public/UserFiles/Image/Docs');
                result := 




DBMS_XDB.createFolder('/public/UserFiles/Image/javascript');
END;



/



COMMIT



/



SELECT path(1)


FROM   Resource_View


WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
AND    Depth(1) = 1


/



DECLARE



  l_Child_Xml    Xmltype;


  l_Count        NUMBER;


BEGIN



  SELECT Xmlelement("Folders",

                    Xmlagg(Xmlelement("Folder",
                                      Xmlattributes(Path(1) AS "name"))))


  INTO   l_Child_Xml


  FROM   Resource_View


  WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
  AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
  AND    Depth(1) = 1;


  SELECT COUNT(*)



  INTO   l_Count


  FROM   Resource_View


  WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
  AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
  AND    Depth(1) = 1;


  Dbms_Output.Put_Line('Count: ' || l_Count);
  Dbms_Output.Put_Line(l_child_xml.getStringVal);
END;



/

/**********************************************************************************/




The sql gives 3 rows back, whereas plsql gives no rows fetched.
The problem is also, this testcase is very difficult to reproduce - i 
had best results ( in terms of reproducebility ) on fresh created 
database , on one instance i got  initially wrong results , then , after 
instance was some days running - correct results for both cases , but 
restart of this instance brought back wrong behaviour (pl sql - no rows).
I could see this behaviour on 10.2.0.2, 10.2.0.1 on RHEL and on Oracle 
XE on linux and windows plattform ( i know, XE is not supported ). I 
managed to get 10053 trace for both cases ( it was difficult as well, 
because just enable the 10053 trace one time switched the pl sql 
execution to correct one, so i could not see any difference between 
correct and wrong execution ). Here are relevant snippets from traces:
-- SQL



/*******************************************/

OPTIMIZER STATISTICS AND COMPUTATIONS





GENERAL PLANS





Considering cardinality-based initial join order.
Permutations for Starting Table :0




Join order[1]:  XDB$RESOURCE[P]#0




Best so far: Table#: 0  cost: 3.8389  card: 0.0002  bytes: 153
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000




Number of join permutations tried: 1




Final - All Rows Plan:  Best join order: 1

  Cost: 3.8389  Degree: 1  Card: 1.0000  Bytes: 153
  Resc: 3.8389  Resc_io: 3.8346  Resc_cpu: 54799
  Resp: 3.8389  Resp_io: 3.8346  Resc_cpu: 54799
kkoipt: Query block SEL$F5BB74E1 (#0)




******* UNPARSED QUERY IS *******



SELECT 



XMLELEMENT("Folders","SYS"."SYS_IXMLAGG"(XMLELEMENT("Folder",XMLATTRIBUTES("XDB"."PATH"(1) 
AS "name")))) "XMLELEMENT( FOLDERS ,XMLAGG(XM" FROM "XDB"."XDB$RESOURCE" 
"P" WHERE 



"XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/public/UserFiles/Image/',1)=1 
AND "XDB"."DEPTH"(1)=1 AND 



EXISTSNODE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/Resource[@Container="true"]')=1
kkoqbc-end


          : call(in-use=158860, alloc=290128), compile(in-use=115060, 
alloc=117184)


apadrv-end: call(in-use=158860, alloc=290128), compile(in-use=115580, 
alloc=117184)
 


sql_id=amabf55jws8hj.


Current SQL statement for this session:


SELECT Xmlelement("Folders",

                    Xmlagg(Xmlelement("Folder",
                                      Xmlattributes(Path(1) AS "name"))))


  FROM   Resource_View


  WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
  AND    Existsnode(Res, '/Resource[@Container="true"]') = 1 AND    
Depth(1) = 1
 




Plan Table



----------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name        | Rows  | Bytes | 
Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |             |       |       
|     4 |           |
| 1   |  SORT AGGREGATE               |             |     1 |   153 
|       |           |
| 2   |   TABLE ACCESS BY INDEX ROWID | XDB$RESOURCE|     1 |   153 
|     4 |  00:00:01 |
| 3   |    DOMAIN INDEX               | XDBHI_IDX   |       |       
|       |           |
----------------------------------------------------+-----------------------------------+


Predicate Information:




2 - filter(("XDB"."DEPTH"(1)=1 AND 


EXISTSNODE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/Resource[@Container="true"]')=1))
3 - 


access("XDB"."UNDER_PATH"("P"."SYS_NC00033$",'/public/UserFiles/Image/',1)=1)
 


Content of other_xml column



  db_version     : 10.2.0.2
  parse_schema   : CMS
  plan_hash      : 2882897097




  Outline Data:


  /*+


    BEGIN_OUTLINE_DATA


      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX(@"SEL$F5BB74E1" "P"@"SEL$2" "XDBHI_IDX")


    END_OUTLINE_DATA



  */





sql_id=2kkmxdcn5p4dc.


Current SQL statement for this session:


SELECT XMLELEMENT("Folders", XMLAGG(XMLELEMENT("Folder", 
XMLATTRIBUTES(PATH(1) AS "name")))) FROM RESOURCE_VIEW WHERE 
UNDER_PATH(RES, '/public/UserFiles/Image/', 1) = 1 AND EXISTSNODE(RES, 
'/Resource[@Container="true"]') = 1 AND DEPTH(1) = 1
----- PL/SQL Call Stack -----


  object      line  object


  handle    number  name

0x2b7c9d7c        85  procedure CMS.CONNECTOR
0x29f90eb8         3  anonymous block
 






Plan Table



------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name         | Rows  | Bytes | 
Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |              |       |       
|     4 |           |
| 1   |  SORT AGGREGATE                |              |     1 |    34 
|       |           |
| 2   |   VIEW                         | RESOURCE_VIEW|     1 |    34 
|     4 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | XDB$RESOURCE |     1 |   145 
|     4 |  00:00:01 |
| 4   |     DOMAIN INDEX               | XDBHI_IDX    |       |       
|       |           |
------------------------------------------------------+-----------------------------------+


Predicate Information:




2 - filter("XDB"."DEPTH"(1)=1)


3 - 


filter(EXISTSNODE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/Resource[@Container="true"]')=1)
4 - 


access("XDB"."UNDER_PATH"("P"."SYS_NC00033$",'/public/UserFiles/Image/',1)=1)
 


Content of other_xml column



  db_version     : 10.2.0.2
  parse_schema   : CMS
  plan_hash      : 1418030634




  Outline Data:


  /*+


    BEGIN_OUTLINE_DATA


      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "RESOURCE_VIEW"@"SEL$1")
      INDEX(@"SEL$2" "P"@"SEL$2" "XDBHI_IDX")


    END_OUTLINE_DATA



  */





I filled a TAR on this issue, but as i mentioned, this testcase is 
difficult to reproduce and that is the main culprit on investigating 
this issue.


So, maybe , someone could have time to try to reproduce this issue 
and/or some insights, on what this can rely, any help is highly appreciated.



Best regards



Maxim

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


Received on Tue Aug 01 2006 - 05:00:14 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US