Explain Plan
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.0
SOURCE_NAME: SQLC2H20
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2009-10-19-15.02.59.226001
EXPLAIN_REQUESTER: CLARALIU
Database Context:
----------------
Parallelism: None
CPU Speed: 3.818116e-007
Comm Speed: 100
Buffer Pool size: 6250
Sort Heap size: 256
Database Heap size: 600
Lock List size: 4096
Maximum Lock List: 22
Average Applications: 1
Locks Available: 28835
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 3
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT patient_id,date_of_consultancy,
XMLQUERY('$i/ClinicalDocument/component/structuredBody/component/section/text'
PASSING GRP1255977523782_12.out_patient_data.PMD as "i") as
Past_Medical_Details
FROM GRP1255977523782_12.out_patient_data
WHERE
xmlexists('$i/ClinicalDocument/recordTarget/patientRole/patient/name[given =
"James"]' passing PMD as "i")
Optimized Statement:
-------------------
SELECT Q2.PATIENT_ID AS "PATIENT_ID", Q2.DATE_OF_CONSULTANCY AS
"DATE_OF_CONSULTANCY", Q1.$C1 AS "PAST_MEDICAL_DETAILS"
FROM $INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
('($INTERNAL_XMLTOXML_NIEO$(Q2.PMD))/{(.[ $INTERNAL_EBV_BOOLEAN$(Clinic
alDocument/recordTarget/patientRole/patient/name[(given =
"James")])])(:-->$C0:),
ClinicalDocument/component/structuredBody/component/section/(text)(:-->$C1:)}'
))) AS Q1), GRP1255977523782_12.OUT_PATIENT_DATA AS Q2
Access Plan:
-----------
Total Cost: 53055.1
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
10
NLJOIN
( 2)
53055.1
7084
/-+--\
1000 0.01
TBSCAN XSCAN
( 3) ( 4)
95.7278 52.9593
84 7
|
1000
DP-TABLE: GRP1255977523782_12
OUT_PATIENT_DATA
Q2
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0020W Table has no statistics. The table
"GRP1255977523782_12"."OUT_PATIENT_DATA" has not
had runstats run on it. This may result in a
sub-optimal access plan and poor performance.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 53055.1
Cumulative CPU Cost: 1.04801e+008
Cumulative I/O Cost: 7084
Cumulative Re-Total Cost: 53055
Cumulative Re-CPU Cost: 1.04789e+008
Cumulative Re-I/O Cost: 7084
Cumulative First Row Cost: 5312.23
Estimated Bufferpool Buffers: 1.26008e+006
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.441 : s090521
HEAPUSE : (Maximum Statement Heap Usage)
144 Pages
PREPTIME: (Statement prepare time)
2 milliseconds
STMTHEAP: (Statement heap size)
2048
Input Streams:
-------------
4) From Operator #2
Estimated number of rows: 10
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.PAST_MEDICAL_DETAILS
+Q3.DATE_OF_CONSULTANCY+Q3.PATIENT_ID
Expected Sequence Sizes:
------------------------
1.000000,NA,NA
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 53055.1
Cumulative CPU Cost: 1.04801e+008
Cumulative I/O Cost: 7084
Cumulative Re-Total Cost: 53055
Cumulative Re-CPU Cost: 1.04789e+008
Cumulative Re-I/O Cost: 7084
Cumulative First Row Cost: 5312.23
Estimated Bufferpool Buffers: 1.26008e+006
Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 1000
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.DATE_OF_CONSULTANCY+Q2.PATIENT_ID+Q2.PMD
Expected Sequence Sizes:
------------------------
NA,NA,1.000000
3) From Operator #4
Estimated number of rows: 0.01
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$C1
Expected Sequence Sizes:
------------------------
1.000000
Output Streams:
--------------
4) To Operator #1
Estimated number of rows: 10
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.PAST_MEDICAL_DETAILS
+Q3.DATE_OF_CONSULTANCY+Q3.PATIENT_ID
Expected Sequence Sizes:
------------------------
1.000000,NA,NA
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 95.7278
Cumulative CPU Cost: 1.80138e+006
Cumulative I/O Cost: 84
Cumulative Re-Total Cost: 0.478162
Cumulative Re-CPU Cost: 1.25235e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 7.56563
Estimated Bufferpool Buffers: 84
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
DPNUMPRT: (Number of data partitions accessed)
4
GLOBLOCK: (Global Lock intent)
INTENT SHARE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
1) From Object GRP1255977523782_12.OUT_PATIENT_DATA
Estimated number of rows: 1000
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.$RID$+Q2.DATE_OF_CONSULTANCY+Q2.PATIENT_ID
+Q2.PMD
Expected Sequence Sizes:
------------------------
NA,NA,NA,1.000000
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 1000
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.DATE_OF_CONSULTANCY+Q2.PATIENT_ID+Q2.PMD
Expected Sequence Sizes:
------------------------
NA,NA,1.000000
4) XSCAN : (XML Doc Navigation)
Cumulative Total Cost: 52.9593
Cumulative CPU Cost: 103000
Cumulative I/O Cost: 7
Cumulative Re-Total Cost: 52.9547
Cumulative Re-CPU Cost: 91000
Cumulative Re-I/O Cost: 7
Cumulative First Row Cost: 52.9593
Estimated Bufferpool Buffers: 1260
Arguments:
---------
INPUTXID: (Context Node)
PMD
JN INPUT: (Join input leg)
INNER
XPATH : (Internal XPath Expression)
($INTERNAL_XMLTOXML_NIEO$(Q2.PMD))/{(.[ $INTERNAL_EBV_B
OOLEAN$(ClinicalDocument/recordTarget/patientRole/patient/name[(given
= "James")])])(:-->$C0:),
ClinicalDocument/component/structuredBody/component/section/(text)(:-->$C1:)}
Output Streams:
--------------
3) To Operator #2
Estimated number of rows: 0.01
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$C1
Expected Sequence Sizes:
------------------------
1.000000
Objects Used in Access Plan:
---------------------------
Schema: GRP1255977523782_12
Name: OUT_PATIENT_DATA
Type: Data Partitioned Table
Time of creation: 2009-10-19-14.41.46.758003
Last statistics update:
Number of columns: 4
Number of rows: 1000
Width of rows: 284
Number of buffer pool pages: 84
Number of data partitions: 4
Distinct row values: No
Tablespace name:
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: -1
Percentage Rows Compressed: -1
Average Compressed Row Size: -1