Using Oracle XML DB to Optimize Performance and Manage Structured XML Data

 

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

In this tutorial, you use Oracle XML DB to Optimize Performance and Manage Structured XML Data.

Time to Complete

Approximately 1 hour.

Overview

XML schemas are used by many industries to define the XML structures used for information exchange. XML documents conforming to these XML schemas are highly structured. This tutorial will show you how the rich capabilities of Oracle XML DB enable storage, indexing, query, optimized performance, and management of structured XML data. It covers the following topics:

Software Requirements

The following is a list of software requirements:

Prerequisites

Before starting this tutorial, you should first complete the following steps:

.

Install Oracle Database 11g Release 2 (11.2.0.2.0) Enterprise Edition for linux

.

Install Oracle SQL Developer

.

Download the files.zip file which contains the script files used in this OBE to your local drive

.

Ensure you have access to the Order Entry (oe) sample schema

Reviewing XML Schema and XML Documents in a Web Browser

An XML Schema and over a hundred sample XML documents have been supplied as part of the OE sample schema in Oracle Database 11g Release 2. In this section, you will review their content using a web browser.

Setting the HTTP Port and Configuring the Listener

.

Start up SQL*Plus.

a. In your linux environment, start a terminal window. Double-click the Terminal icon on your desktop.

 

b. A terminal window is displayed.

 

c. Start SQL*Plus. Enter system as the username and your valid system password.

Note: In the above screen capture, subsitute <your_password> with your valid system password.

 

.

Set the HTTP port to the standard port 8080.

a. Use the PL/SQL procedure DBMS_XDB.sethtpport to set the HTTP(S) port number to port_number in the Oracle XML DB configuration file /xdbconfig.xml, where port_number is 8080 for HTTP or 443 for HTTPS:

 

b. Confirm that the HTTP has been set.

 

.

Now force the database to reregister with the listener, using this SQL statement below:

Your configuration has been set. Check that the listener is correctly configured. Enter the lsnrctl STATUS command at the operating system prompt (%):

 

 

Viewing the PURCHASEORDER XML Schema and a Conforming XML Document

.

Open a browser and enter the URL for the OE folder in the XML DB repository.


.

Enter oe for the User Name and oe for the Password in the Authentication dialog box, and then click OK.

 

.

Now click the purchaseOrder.xsd hyperlink in the browser window to view the XML schema.

 

.

The XML schema is displayed in the browser window as follows. Notice the highlighted annotation xdb:defaultTable=”PURCHASEORDER”, which specifies that all XML documents conforming to this schema will be physically stored in the PURCHASEORDER table.

 

.

You can also view XML documents that are stored in the XML DB repository using a Web browser. Return to the /home/OE/ folder, and then click the PurchaseOrders folder link.

 

.

The 2002/ link is displayed. Click the link.

 

.

A number of sub-folders links are displayed. Click the Dec/ link.

 

.

A number of the purchase orders XML documents are displayed. Click one of the XML documents links to view its content.

 

.

Review the partial formatted purchase order XML document below. Notice the highlighted annotation xsi:noNamespaceSchemaLocation= “http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd” specifying the XML schema location URI. This is referencing the xsd file that we have reviewed earlier in a browser window.

. . .

 

Using XQuery and SQL/XML to Query XML Documents

In the last topic, you browsed XML documents stored in the XML repository using- a Web browser. Since each of these XML documents is physically stored as a row of an XMLType table, queries can be executed against either the XMLType storage table or the repository. In this section, you will use XQuery and SQL/XML to query XML documents stored in the PURCHASEORDER table.

Start SQL Developer to Examine the Storage Table for the Purchase Order XML Documents

.

Click the SQL Developer icon on your desktop to start SQL Developer.

The SQL Developer window is displayed.

 

.

Create a new database connection as user "system" by performing the following steps.

a. In the Connections tab, right-click the Connections node, and then select New Connection from the pop-up menu.

The New/Select Database Connection window is displayed.

 

b. Enter the following details in the New/Select Database Connection window.

Connection Name: System
UserName: system
Password:<your_system_password>
Save Password checkbox: Enabled
Hostname: localhost
Port: 1521
SID: <your_SID>

 

c. Click Test. If the connection has been set correctly, the Status in the lower left corner of the window is Success.

 

d. Click Save to save the newly created database connection, and then click Connect. The new System database connection is displayed in the Connections tab.

 

e. Click the Files tab, and then double-click the 1_0_setup.sql script from the location where you saved the contents of the downloaded files.zip file . The contents of the script file is displayed in the SQL Worksheet area.

Note: In this tutorial, the contents of the files.zip are saved in the /home/oracle/OBE1 folder as follows:

Note: The above script contains a section starting with the highlighted comment "Run I/O calibration". You can run this code which calibrates I/O so that you can better statistics for the cost based optimizer. If you decide to run the I/O calibration, you must perform the steps:

  • Log in to SQL*Plus as sysdba as follows:
    sqlplus system/<your_password> as sysdba;
  • Set the timed_statistics parameter to TRUE as follows:
    ALTER SYSTEM SET timed_statistics = TRUE;
  • Set the timed_statistics parameter to TRUE as follows:
    ALTER SYETEM SET filesystemio_options = SETALL scope=spfile;
  • Enable asynchronous I/O by setting the FILESYSTEMIO_OPTIONS initialization parameter to SETALL as follows:
    ALTER SYETEM SET filesystemio_options = SETALL scope=spfile;
  • Restart the database instance as follows:
    CONNECT SYSTEM/<your_password> AS SYSDBA;
    SHUTDOWN IMMEDIATE
    STARTUP
  • Ensure that the asynchronous I/O is enabled for data files by running the following query:
    COLUMN NAME FORMAT A50
    SELECT NAME,ASYNCH_IO
    FROM V$DATAFILE F,V$IOSTAT_FILE I
    WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File';

 

f. Select the System database connection from the Choose Db Connection drop-down list, and then click the Run Script icon (or press the F5 key) to grant access to the oe user.

The output is displayed in the Script Output tab.

 

g. In the Connections tab, right-click Connections from the pop-up menu, and then select New Connection.

 

h. The New / Select Database Connection window is displayed. Enter the following details for the new database connection. Click Test to make sure that the connection has been set correctly. If the test status is Success, save the new database connection, and then click Connect.

Connection Name: OE Schema
UserName: oe
Password: <your_oe_password>
Save Password checkbox: Enabled
Hostname: localhost
Port: 1521
SID:<your_SID>


.

Set the Autotrace parameters. Perform the following steps:

a. Navigate to Tools > Preferences.

The Preferences window is displayed.

 

b. Expand Database, and then select Autotrace/Explain Plan. Make sure to select the following check boxes, and then click OK.

Object_Name
Cost
Cardinality
Predicates

 

.

Examine the OE Schema. Perform the following steps:

a. Click the plus (+) icon to the left of the oe connection to expand it.

 

b. Expand Tables, and then select PURCHASEORDER. On the right window pane, select the Details tab to examine the values of the NUM_ROWS and the TABLE_TYPE parameters. Notice the highlighted TABLE_TYPE parameter with value XMLTYPE.

 

c. Click the Files tab in the left window pane to open the SQL script 1_3_queries.sql and display it in the SQL Worksheet area. Select the OE Schema from the Choose Db Connection drop-down list, select the Q0 query, and then click the Run Statement icon to execute a query against the USER_XML_TABLES data dictionary view.

Notice the following:

 

Run SQL/XML and XQuery Queries against the PURCHASEORDER XMLType


.

On the left window pane, use the Files tab to open the 1_3_queries.sql script. On the right window pane, select the Q1 query and click the Run Statement icon to execute the query. The Query Result tab below displays the results. Notice XMLSerialize(), XMLQueru(), and XMLExists() SQL/XML functions along with XQuery expressions as their parameters in the query.

 

.

Scroll down the 1_3_queries.sql script to find the Q11 query which uses the XMLTable() SQL/XML function to map structured data in the purchase order XML documents into a master-detail table for ROLLUP calculations. Notice the output in the Query Result tab to find a sub-total calculated for the A10 cost center in row 5.


.

You can now run Autotrace on these queries to see of their execution plans are optimal. Using Q1 as an example, The following execution plan involved an inefficient TABLE ACCESS FULL operation.


Create Indexes to Improve XML Query Performance


.

On the left window pane, click the Files tab to open the 1_2_createIndexes.sql script. Select the OE Schema from the Choose Db Connection drop-down list, and then click the Run Script icon to create B-tree indexes. Notice how the CREATE INDEX statements below used the XMLCast() and XMLQuery() SQL/XML functions along with XPath expressions to create B-tree indexes on the leaf nodes. Two composite indexes and one full-text index are also created for the columns of the LINEITEM_TABLE nested table.

 

.

To examine the created indexes, scroll to the bottom of the 1_2_createIndexes.sql script to select the query shown below. Click the Run Statement icon to see that you have successfully created B-tree indexes and one full-text index (the “DOMAIN” index) on the PURCHASEORDER table and its associated LINEITEM_TABLE.


.

Check the execution plan again. Select the Q1 query in 1_3_queries.sql file, select the OE Schema from the Choose Db Connection drop-down list, and then click the Autotrace… icon. You can see below how the query execution plan has improved where TABLE ACCESS FULL operation has been replaced by more efficient TABLE ACCESS BY INDEX ROWID and INDEX RANGE SCAN operations.


.

Similarly, the query execution plan for Q5 also used more efficient INDEX RANGE SCAN operations. Notice that the DOMAIN INDEX (i.e., the full-text index on the purchase order item description) is also used to execute the ora:contains() XQuery function in the XMLExists() SQL/XML function.

 

Using XQuery Pragma ora:defaultTable to Improve Repository Query Performance

In prior releases of the Oracle Database, in order to obtain optimal performance for XQuery expressions that use the fn:doc and fn:collection over Oracle XML DB Repository resources, you needed to write explicit joins with RESOURCE_VIEW. In the Oracle Database 11g Release 2 (11.2.0.2.0) , the new XQuery pragma ora:defaultTable will perform the necessary joins automatically. In the following steps you will learn how to use this new pragma.

.

On the left window pane, click the Files tab, and then double-click the 1_4_defaultTableQueries.sql script. On the right window pane, select the OE Schema from the Choose Db Connection, select the CASE 1 query, and then click the Autotrace… icon to check the execution plan. Since XQuery fn:collection() function is used to specify an XML document as an XM LDB repository resource, the query execution engine has no immediate knowledge that the physical storage of this document is object-relational with applicable indexes. As a result, the query plan uses the very inefficient COLLECTION ITERATOR PICKLER FETCH operations.

 

.

Prior to Oracle Database 11g Release 2 ( 11.2.0.2.0), you had to manually rewrite the query to explicitly relate the XML DB repository resource to the underlying physical storage table as shown in CASE 2 of the same SQL script file. Notice how the query execution plan has improved to use INDEX RANGE SCAN operations.

 

.

In the new Oracle Database 11g Release 2 ( 11.2.0.2.0), instead of manually rewriting the query, you can simply use the new ora:defaultTable pragma in your XQuery expression to give a hint about the underlying physical storage table (such as the PURCHASEORDER table in this example) to the query processing engine. Notice how the execution plan is now as good as the manually rewritten query in the previous step.

 

Equi-Partitioning of Object-Relational Storage


Equi-partitioning for object-relational storage was introduced in Oracle Database 11g Release 2 to help simplify XML data life-cycle management and performance. In this section, you will learn how the object-relational storage table can be equi-partitioned.

Create New Tablespaces for XMLType Table Partitions


.

Click the Files tab, and then double-click the 2_0_setup.sql script. Next, click the Run Script icon to create the tablespaces.

 

.

Scroll down the 2_0_setup.sql script to view the highlighted query against the DBA_TABLESPACES data dictionary view. Scroll down in the Script Output tab to see the highlighted created tablespaces.

 

Create Partitioned XMLType Table


.

Create an XMLType table with partitioned object-relational storage, and populate the table with data selected from the PURCHASEORDERS table. Run the script 2_1_createTable.sql. Perform the following steps:

a. Click the Files tab.

 

b. Browse to the location of your working directory \files, and then double-click the 2_1_createTable.sql file. The code is displayed in the SQL Worksheet area.

 

c. Select the OE Schema database connection from the drop-down list on the right.

 

d. Click the Run Script (F5) icon. Note that three list partitions are specified based on the partitioning key COST_CENTER, which corresponds to the value of /PurchaseOrder/CostCenter node in the purchaseorder XML document. Note also the table partitions for the ordered collection table (OCT) LINEITEM_PT_TABLE are equi-partitioned in the same way as their base table and given their own partition names (i.e., lineitem_a10, lineitem_r20, lineitem_s30).

 

e. Right click the Tables folder, and then select Refresh from the pop-up menu.

 

f. Click the newly created XMLType table PARTITIONED_PO to find more information on the right pane of the Oracle SQL Developer window

 

g. Click the Partitions tab to display table partitions.

Note: If after clicking the Partitions tab above, you don't see the partitions details, click the Refresh icon on the Partitions sub-tab toolbar as follows:

 

h. Notice the partition names as you have specified. Click the Actions… icon, and then navigate to Statistics > Gather Statistics. The Gather Statistics window is displayed.

 

i. In the Gather Statistics popup dialog, click Apply. A Confirmation window is displayed. Click OK.

 

j. Once statistics are gathered, you can see that the 132 rows of the purchase orders are now distributed among three partitions with 32, 43, and 57 rows each.

 

k. To run a test query on the partitioned XMLType table, open 2_3_queries.sql script. Select the Q1 query, and then click the Autotrace… icon to examine the query's execution plan. Notice the PARTITION LIST SINGLE operation, PARTITION_START and PARTITION_STOP of 2 as a result of the partition pruning optimization to access only the R20 partition.

 

l. In the Connections tab, click the newly created XMLType table LINEITEM_PT_TABLE to display more information on the table in the right window pane.

 

m. Click the Partitions tab to display table partitions for the ordered collection table (OCT) LINEITEM_PT_TABLE. With equi-partitioning support, OCTs are partitioned in the same way as their base table.

In the above topic, you learned how to create a list partitioned XMLType table with object-relational storage, populated data into this table, and checked the query execution plan for partitioning-specific optimizations.

 

Improving Query Performance of Partitioned XMLType Table through Index Creation

As you have learned earlier in this OBE, you can improve the performance of your queries by creating indexes. In this section, you will create locally managed B-Tree indexes and a globally managed full-text index on the partitioned XMLType table. You will then run the SQL/XML, XQuery queries against the partitioned object-relational table to see the autotrace result and note the performance improvement with more efficient query execution operations. Perform the following steps:

.

In your SQL Developer session, connect as the OE Schema user, and then execute the 2_2_createIndexes.sql script. Note the use of keyword LOCAL to create B-tree indexes that are local to each partition. Note also the absence of the LOCAL keyword for the ConText Full-Text index (i.e., iPT_DESCRIPTION_TEXT) to create a globally managed index.

Once the indexes are created, scroll down to the bottom of the 2_2_createIndexes.sql script. Highlight the Select statement that queries the created indexes, and then click the Run Statement icon. Notice that the indexes created with the LOCAL keyword display YES in the PARTITIONED column.

 

.

Now that you have created indexes, you can observe the improved query execution plan with INDEX RANGE SCAN operations for a specific partition. Open the 2_3_queries.sql script file using the OE Schema database connection and highlight the Q2 query. Click the Autotrace… icon to generate a query execution plan. Note the usage of the index IPT_PARTNUMBER and that the values of PARTITION_START and PARTITION_STOP columns which are highlighted, point to a single partition. Note also the highlighted query execution operation TABLE ACCESS BY LOCAL INDEX ROWID.

 

.

View the execution plan of the query that lists the references for LineItem 20 with a description containing “picnic” on a particular purchase order. Select query Q5 in 2_3_queries.sql script, and then click the Autotrace… icon. Notice the usage of IPT_DESCRIPTION_TEXT index in the query execution plan. However, you can see there are now two TABLE ACCESS BY GLOBAL INDEX ROWID operations because the IPT_DESCRIPTION_TEXT index is a globally managed index.

In the above section, you learned how to create B-Tree indexes on partitioned object-relational storage. You also learned how to observe the improved query performance by viewing the query execution plan.


Using Parallelism in Query Execution

Equi-partitioning and local indexes allow parallel queries and DMLs to further improve performance. Perform the following steps:

.

In Oracle SQL Developer, open the 2_4_alterTable.sql script. Choose the OE Schema as the Database Connection, and then click the Run Script icon to execute the ALTER TABLE command. This enables parallel query and DML on the PARTITIONED_PO table.

ALTER TABLE PARTITIONED_PO PARALLEL;

 

.

Parallel query execution can make a tremendous difference in business intelligence calculations.  Query Q11 in the 2_3_queries.sql file is such an example that aggregates total dollar amount for each cost center and requestor. Once you selected the Q11 query and click the Autotrace… icon, you can use the scroll bar to see the complete query execution plan with extensive use of parallel query operators (i.e., PX operators).

 

Summary

In this tutorial, you have learned how to:

Resources

Credits

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights