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
*/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
*/Best regards
Maxim -- //www.freelists.org/webpage/oracle-l
