One is All You Need

Sometimes while managing your databases, you find that it would be really slick to write a custom message to the alert log, but how?

The standard built-in package dbms_system allows you to write you own messages to either a trace file or to the alert log.

Wait! you say. I can’t find dbms_system in the PL/SQL Packages and Types Reference.
Well, No you can’t. But you can find it in your $ORACLE_HOME/rbms/admin directory.
The dbmsutil.sql file tell us it was moved to prvtutl.plb many years ago to give it more obscurity.
When we describe this package in SQLPlus, we see the function ksdwrt takes 2 parameters:

PROCEDURE KSDWRT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST                           BINARY_INTEGER          IN
 TST                            VARCHAR2                IN

The DEST tells Oracle where to write the message 1=trace file, 2=alert log, 3=both.
The TST is the message to write.

The closely related function ksdddt writes a timestamp in regular alert log fashion to your output location.
So, here’s the scenario where we want to use this bit of cleverness.
We have a program that wants to record a custom message in the alert log so our monitor picks it up and deals with it (email’s the DBA, logs it in the database trouble log, what ever you need to do…)
Use it like this:

begin
dbms_system.ksdddt;
dbms_system.ksdwrt(2,'ORA-20212 My custom message is THIS!');
end;


Now let’s look for it in our alert log:

select row_num ,start_row ,alert_date ,alert_text
from alert_log
where alert_date > sysdate -1/24

 Line number Timestamp            Text
------------ -------------------- ----------------------------------------
        6951 22-May-2007 21:57:22 Tue May 22 21:57:22 2007
        6952 22-May-2007 21:57:22 ORA-20212 My custom message is THIS!

If you missed that trick of reading the alert log with SQL, check out my article from February on reading the alert log.
http://www.singlequery.com/?p=23

Lastly, execute privileges on this undocumented gem are not granted to anyone by default, so connect as SYS and
grant execute on dbms_system to system;
or to whomever you need to.

Happy coding,
Chip Dawes

I recently presented a paper at Collaborate 2007 that discussed leveraging Oracle Portal for Identity Management. This is a short excerpt from that presentation:

Case Study: Creating Dynamic Web Menus

XYZ Manufacturing decides that future web development will leverage the Oracle Portal framework; however, any legacy applications will continue to operate using their existing environments. For administrators, this scenario presents challenges for synchronizing security among the various legacy systems and complexity since multiple administration systems are likely to exist. In the following scenario, XYZ creates a new, web-based reporting interface with hierarchically-structured menu-items designed to access specific reports. The dynamic menus are constructed based upon privilege settings from the user’s Portal group membership.

Creating “Bottom-Up” Group Hierarchies

With a web menu, users gain access to the individual lowest-level menu items as well as all direct parents to preserve the visual structure of the menu. For this example, the XYZ Manufacturing’s web-enabled reporting system includes the following menu structure:

A user with access to only the Sales Forecast report must also gain access to the Sales node at Level 2 in the menu and the Reports node at the top of the menu. This is accomplished by creating a series of Portal Groups with children added as members to the direct parents.

Available menu items for a user privileged to view only one report.

To simulate a menu, Group membership moves UP the tree.

Portal Groups provide a powerful framework for developing hierarchical access to content but they lack the breadth of additional meta data that allow them to serve as a repository for dynamic web menu items. However, when Portal Groups and Items are joined into a single construct, the combined object provides a powerful tool for describing and applying a highly-customizable user security model to menus. Since Portal Items can include any number of custom attributes, a Menu Item Type is designed to store meta data such as the displayed text, the menu item type, the order the item appears in a menu and an optional URL for the lowest level items to invoke a report when clicked.

Portal Groups and Associated Items are combined to create a powerful provisioning tool.

Creating Custom item Types and the Hierarchical Menu

1. Create attributes from Navigator –> Page Groups –> Shared Objects –> Attributes –> Create New Attribute screen.

2. Create a custom item type from Navigator –> Page Groups –> Shared Objects –> Item Types –> Create New Item Type screen. Click to edit the item properties. The custom attributes are added under the Edit Item Type –> Attributes screen.

3. Create a page to contain the hierarchical menu as a set of Items and Sub-Items. The page itself is not accessed or viewable by users and does not require formatting, however it serves as a helpful visual aide for administrators to review the complete menu structure. In order to successfully traverse the menu with queries, subordinate items are created as Sub-Items; only menu items with no parents are created as Items.

4. Enable Item Level Security for the entire page as well as for each individual Item that is added to the page. Granting access to the associated Group in the menu hierarchy links the two constructs (i.e. Sales Item <-- --> Sales Group) and allows the Item to serve as the repository for the menu item metadata.

Note: Screen shots of the above process can be found in the full white paper.

Creating Consolidation Groups to Ease Administration

Because the Group hierarchical structure for a menu is built in a bottom-up fashion, administration can be tedious without the help of a Consolidation Group. In the absence of a Consolidation Group, granting access to all reports in the menu requires the administrator to add the user to each individual lowest level Group. Although this example depicts only four report options, a real-word scenario might have dozens or even hundreds of reports.

In Figure 15, the menu structure is inverted from Figure 8 to depict the lowest levels (individual reports) at the top and additionally includes a Consolidation Group (the All Menu Items Group). A Consolidation Group can include any logical subset of the reports that might be granted to a large number of users. It is conceivable that a Sales or Orders Consolidation Group (no to be confused with the Sales and Orders Groups that govern the menu items only) might be added to simplify the administration of different user roles.

In this example, User 1 sees the Order Detail Group, Order Group (the immediate “child” in this inverted view) and the Reports Group (ultimate “child” in this inverted view) while User 2 inherits access to all Menu Items from the Consolidation Group.

Granting access to an “All Menu Items Group” eases administration.

Putting it All Together - Dynamically Determining Group Access (Menu Items)

The final step for using Portal menu security is to expose the list of allowable Menu Items for a particular user. Since information regarding the structure and order of the menu as well as the URLs for the reports is also included via the attached Items, it is preferable to create a view into this information and return the data in the order the menu might be displayed. The view returns the following rows for a user with access to the entire menu (member of the All Menu Items Group) and the data is used to dynamically render a menu using JSPs or PL/SQL Server Pages and JavaScript.

Rows returned from the dynamic menu view for a user with full access.

The following query was constructed to return a SIBLINGS-sorted list of available menu items for the supplied user. Optionally, the query could include a passed Portal Page name to enable implementation of multiple menus. The query was designed with Subquery Factoring (better known as the WITH clause) to maximize efficiency and could include any number of custom attributes to enable the external application to properly render and control a web menu.

Table descriptions for a dynamic menu item query

This query returns an ordered list of allowable menu items, provided portal.wwctx_api.set_context is first executed for the specified user to ensure that tables are synchronized with Oracle Internet Directory (OID).

WITH temp AS (
    SELECT t.masterthingid id,
           t.title,
           t.parentid,
           wa.name item_name,
           DECODE(ta.valuetype,'text',ta.value,ta.numbervalue) item_value,
           pg1.name parent_page
    FROM   portal.wwv_things t,
           portal.wwpob_page$ pg1,
           portal.wwsec_group$ gp,
           portal.wwsec_sys_priv$ p,
           portal.wwv_user_groups wg,
           portal.wwsbr_item_types wit,
           portal.wwsbr_attributes wa,
           portal.wwsbr_item_type_attributes wita,
           portal.wwv_thingattributes ta
    WHERE  t.siteid           = SUBSTR(p.name,1,INSTR(p.name,'/')-1)
    and    t.masterthingid    = substr(p.name,instr(p.name,'/')+1,8)
    and    p.object_type_name = 'ITEM' -- ITEM / GROUP
    and    p.grantee_group_id = gp.id
    and    p.grantee_type     = 'GROUP'
    and    t.siteid           = pg1.siteid
    and    pg1.id             = 1
    and    wg.group_name      = gp.name
    and    wg.user_name       = UPPER('GPIKE')
    and    wit.name           = 'MenuItem'
    and    wit.id             = t.subtype
    and    ta.masterthingid   = t.id
    and    wita.item_type_id  = wit.id
    and    wita.attribute_id  = ta.attributeid
    and    wa.id              = wita.attribute_id
    )
SELECT id,title, parentid, menu_item_type, report_id, url, sort_order
FROM  (
       SELECT temp1.id,
              temp1.parentid,
              temp1.title,
              temp1.item_value menu_item_type,
              temp2.item_value report_id,
              temp3.item_value url,
              temp4.item_value sort_order
       FROM   temp temp1, temp temp2, temp temp3, temp temp4
       WHERE  temp1.id        = temp2.id
       AND    temp2.id        = temp3.id
       AND    temp3.id        = temp4.id
       AND    temp1.item_name = 'MenuItemType'
       AND    temp2.item_name = 'MenuItemReportID'
       AND    temp3.item_name = 'MenuItemURL'
       AND    temp4.item_name = 'MenuItemSortOrder'
      )
CONNECT BY parentid = PRIOR id
START WITH parentid = 0
ORDER SIBLINGS BY sort_order;

For step by step guidelines for implementing dynamic web menus with Oracle portal, see the full article .

Greg Pike

Note: This previous article was enhanced with additional information regarding read-consistent pagination.

Found at the top and bottom of almost any web page that displays long lists of information are seemingly-innocuous little widgets commonly called “pagination” objects. They allow the user to advance to the next or previous page of information as well as jumping to a specific page. Although they may be a small part of a web page’s functionality, they can be a resource hog to produce if not developed with proper techniques.

Java applications seem to be a conspicuous culprit in producing poorly-conceived pagination objects. The scenario plays out in the following way:

1) Select the complete list of items to be displayed in the proper order; perhaps 1000’s of rows are selected.

2) March through the ordered list in a loop and accumulate a count of the items. When the appropriate section of the result set is found, accumulate these details into a display object.

3) Continue through the rest of the list to get full a count of the total items to feed the pagination widget.

Alternate scenarios may use two queries, one to count the total items for the entire result set and the other to get just those items to be displayed on the web page. This is an improvement over digging through the entire result set, but its still inefficient.

The appropriate solution is a Single Query. The goal is to produce a query that returns BOTH the total of count of the items in the full result set (to support creating the pagination object) and only those rows that will be displayed on the web page.

For our example, consider a table of PRODUCTS and a web page that displays any product with names based on a requested search criteria, 25 to a page. This is not an exercise in appropriate search conditions, so for our example we assume the user enters a keyword and we use % symbols on each end of the text fragment to find matching rows. The uninspired query looks like this:

SELECT product_name,
       product_details
FROM   products
WHERE product_name like '%Television%'
ORDER BY product_id;

The inefficient program first issues the query and then counts the rows in the result set, pulls out just the appropriate rows for display and crafts the pagination object all in a loop construct.

But the database can do most of the work for you. The following query retrieves only the rows to be displayed using in-line views and the total number of rows for the entire result set using the magic of the PARTITION BY clause.

SELECT product_name,
       product_details,
       total_rows
FROM   (
        SELECT product_name,
               product_details,
               total_rows,
               rownum row_counter
        FROM   (
                SELECT product_name,
                       product_details,
                       count(*) OVER () total_rows
                FROM   products
                WHERE product_name like '%Television%'
                ORDER BY product_name
               )
       )
WHERE row_counter between v_start_row and v_end_row;

Here’s how the query works. In the innermost in-line view, the entire result set is selected and the the count(*) OVER () column makes sure that every row contains a column with the total number of rows in the query. No matter what subset of rows are ultimately selected, the count of the entire inner result set is always available. This inner in-line view also sorts the data.

In the next in-line view, all the rows are selected from the innermost in-line view with an additional column added to identify each row by an incrementing number. Perhaps you are thinking to yourself, “Why didn’t he just add the rownum column to innermost in-line view?” The answer is that rownum is added to a result set PRIOR to an ORDER BY clause. It is necessary to first sort the result set, then add the rownum later in the process.

In the final step, our outermost query actually selects only the rows of interest by specifying BETWEEN a start and end position (provided here by the v_start_row and v_end_row variables). Most applications that use pagination probably are passed a variables for the page to display and a variable for the number of lines to display per page. In that case, the start and end positions are calculated:

v_end_row = page to display X number of rows per page
v_start_row = ((page to display - 1) X number of rows per page) + 1

The pagination object for the new page is created by capturing the total row count from any of the individual rows that come out of the query. In the loop that processes these rows, the total can be assigned to a variable for later use by the pagination constructor.

An Alternative

If you are sorting by a unique, numeric column (say a product_id), you can add use a row_number() function to create the row numbers in the innermost in-line view and simplify the query:

SELECT product_name,
       product_details,
       total_rows
FROM   (
        SELECT product_id,
               product_name,
               product_details,
               COUNT(*) OVER () total_rows,
               ROW_NUMBER() OVER (ORDER BY product_id) row_counter
        FROM   products
        ORDER BY product_id
       )
WHERE  row_counter between v_start_row and v_end_row;

Preserving Read-Consistency with Pagination

If you have rapidly-changing source tables, pagination becomes more complicated since each request to the server for a different page of information will potentially yield different data sets. For example, if a page of 25 results are returned and during the time the user views the page, a new item is inserted into the database that would have appeared in position 10 on that page. When the user requests the next 25 entries, item 25 from the previous page now is shown as item 1 on the new page. The results are even worse if the old item 10 is deleted, since the item that should have been displayed as item 1 on on the new page has now dropped to item 25 of the previous page. The user never saw it! To eliminate this behavior, you can use the System Change Number and flashback query:

CREATE TABLE products (product_id NUMBER, product_name VARCHAR2(20));
INSERT INTO products VALUES (1,'Widget A');
INSERT INTO products VALUES (2,'Widget B');
INSERT INTO products VALUES (4,'Widget D');
INSERT INTO products VALUES (5,'Widget E');
COMMIT;

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                58961080

SELECT product_name,
       total_rows
FROM   (
        SELECT product_id,
               product_name,
               COUNT(*) OVER () total_rows,
               ROW_NUMBER() OVER (ORDER BY product_id) row_counter
        FROM   products
        ORDER BY product_id
       )
WHERE  row_counter between 2 and 4;

PRODUCT_NAME         TOTAL_ROWS
-------------------- ----------
Widget B                      4
Widget D                      4
Widget E                      4

INSERT INTO products VALUES (3,'Widget C');
COMMIT;

SELECT product_name,
       total_rows
FROM   (
        SELECT product_id,
               product_name,
               COUNT(*) OVER () total_rows,
               ROW_NUMBER() OVER (ORDER BY product_id) row_counter
        FROM   products as of scn 58961080 --  Flashback query!
        ORDER BY product_id
       )
WHERE  row_counter between 2 and 4;

PRODUCT_NAME         TOTAL_ROWS
-------------------- ----------
Widget B                      4
Widget D                      4
Widget E                      4

Note that the new row for Widget C did not appear and the total count remains 4. If this query is run without the as of scn clause, the expected result set is obtained:

PRODUCT_NAME         TOTAL_ROWS
-------------------- ----------
Widget B                      5
Widget C                      5
Widget D                      5

When creating pagination web objects, simply include the system change number in the web page to enable flashback query. More information on this topic can be found in the Oracle forums here.

So there you have it. A Single Query that can dramatically shorten the amount of work for loops and cursors in web applications. As an added benefit, the bigger the initial result set, the more time savings the application will experience.

Greg Pike

As a infrequent poster (but constant lurker) on the Oracle forums, I often see questions about converting numbers to their English language equivalent. Recently, the PL/SQL and SQL forum was asked the opposite question: How does one convert words (i.e. “ONE HUNDRED TWENTY THREE”) to a number (i.e. 123). After the talented members of the forum came up with several solutions, I decided the whole topic was deserving of a SingleQuery posting.

Converting Numbers to Words

First the easy part. This is an old-school Oracle trick that’s been around forever:

SELECT TO_CHAR(TO_DATE(123,'J'),'JSP') to_words
FROM   dual;

TO_WORDS
------------------------
ONE HUNDRED TWENTY-THREE

Pretty straight forward. The TO_DATE(123,’J') creates the 123rd Julian Day, which is defined as the 123rd day after Monday, January 1, 4713 BC. Click here to learn more about the Julian Day if you feel obligated. The TO_CHAR with the ‘JSP’ format mask converts the Julian Day into the words “ONE HUNDRED TWENTY-THREE”.

Unfortunately, there are some limitations with this little trick. Oracle cannot compute the Julian Day past 31-DEC-9999 so that means we can only convert numbers to English up to 5,373,484 (the number of days between the date the pyramids were constructed and the date that the Chicago Cubs will win the World Series again). Also, this method only works in English; I have seen no solution to do this in other languages.

Converting Words to Numbers

Now the hard part. As previously mentioned, one of the Oracle forums recently discussed how one can convert from the English language to numbers. There were lots of interesting alternatives (click here to see the forum thread) with many focused upon procedural solutions. But since my specialty is a Single Query solution, I wanted to take a stab at it and came up with the following that works on 9i and 10g:

SELECT LEVEL wordasint
FROM   dual
WHERE  TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') = 'ONE HUNDRED TWENTY-THREE'
CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') != 'ONE HUNDRED TWENTY-THREE'
AND    LEVEL < 10001;

 WORDASINT
----------
       123

This is a rather strange CONNECT BY construct that you may not have seen before and a good explanation of what this does can be found here. It works, although with the similar maximum limitation of 5,373,484 (”FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR”) because of the Julian Day. Also, the larger the number requested, the slower this query gets since it performs more and more CONNECT BYs as the requested number gets bigger. The last clause “AND LEVEL < 10001" is designed to stop typos (i.e. "ONES HUNDERD") from causing this query to execute until it exceeds the maximum. Simply provide the largest number+1 you will ever expect to receive and this query will return no rows if it is not found (but will not fail)!

Here is another slick solution for Oracle 10g (provided by Sundar M) that uses the MODEL clause, but it unfortunately stops functioning at 86399:

SELECT sp,
       n
FROM   (
        SELECT 'EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE' sp
        FROM dual
       )
   MODEL
   DIMENSION BY (1 dim)
   MEASURES (0 n, sp)
   RULES ITERATE (86400) UNTIL (TO_CHAR(DATE '0001-01-01' +
         (ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
         ( n[1]=ITERATION_NUMBER );

SP                                                     N
--------------------------------------------- ----------
EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE      86399

At 86400:

SP                                        N
-------------------------------- ----------
EIGHTY-SIX THOUSAND FOUR HUNDRED      86399  <--  Whoops!

Words to Numbers for Oracle 8/8i

The prior queries include functionality not available for Oracle 8/8i databases, but this one should work. I will admit, I don’t have an 8i database laying around, so this is untested:

SELECT theword,
       thenum
FROM
       (
        SELECT 'ZERO' theword,
               0      thenum
               FROM   dual
        UNION  ALL
        SELECT /*+ parallel (rbt 2) */
               TO_CHAR(TO_DATE(ROWNUM,'J'), 'JSP')  theword,
               ROWNUM
        FROM   really_big_table rbt
        WHERE ROWNUM <100001
       )
WHERE  theword  = 'TEN THOUSAND FIVE HUNDRED THIRTY-FOUR';

The limitations for this query are typical; you can’t generate numbers larger than 5,373,484 and you need a really_big_table to use as the seed table for generating rows (as a replacement for the CONNECT BY). Also, the bigger the maximum number (in this case 100,000), the longer this query will take to execute. However, this query can be modified for speed if specific numeric ranges are known in advance.

Dollars and Cents From Words

Oracle 8/8i (or 9i and 10g as well of course)i:

SELECT /*+ ordered all_rows */
       TO_CHAR(temp1.thenum+(temp2.thenum/100),'$9,999,999.99') amount
FROM
       (
        SELECT thenum,
               SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')+5,
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' CENTS')
                 - INSTR
                 ('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')
                 -5) thecents
        FROM
               (
                SELECT 'ZERO' theword,
                0      thenum
                FROM   dual
                UNION ALL
                SELECT TO_CHAR(TO_DATE(ROWNUM,'J'), 'JSP')  theword,
                       ROWNUM thenum
                FROM   really_big_table
                WHERE  ROWNUM < 100000
               )
        WHERE  theword=SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',1,
                   INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')-1)
       ) temp1,
       (
        SELECT 'ZERO' theword,
               0 thenum
        FROM DUAL
        UNION ALL
        SELECT TO_CHAR(TO_DATE(ROWNUM,'J'), 'JSP'),
               ROWNUM
        FROM   really_big_table
        WHERE  ROWNUM <101
       ) temp2
WHERE  temp1.thecents = temp2.theword;

AMOUNT
--------------
       $123.12

Oracle 9i/10g (formatted to fit in this page):

SELECT /*+ ordered all_rows */
       TO_CHAR(temp1.thenum+(temp2.thenum/100),'$9,999,999.99') amount
FROM
       (
        SELECT LEVEL thenum,
               SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')+5,
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' CENTS')
                 - INSTR
                 ('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS','
                 AND')-5) thecents
        FROM    dual
        WHERE  TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') =
                 SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',1,
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')-1)
        CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') !=
                     SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',1,
                     INSTR
                     ('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS','
                     AND')-1)
        AND LEVEL < 100001
       ) temp1,
       (
        SELECT LEVEL thenum,
               TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') theword
        FROM   dual
        CONNECT BY 1 = 1
        AND LEVEL < 101
       ) temp2
WHERE  temp1.thecents = temp2.theword;

AMOUNT
--------------
       $123.12

Complete Solutions

Probably the most complete solution (but not a Single Query) was provided by a forum frequent poster that goes by the name of Volder. His combination of a query and a supporting function are only viable in Oracle 10g, but allow word descriptions of numbers up to 65 digits to be very quickly converted! For the sake of space, I am not reprinting his solution. Please refer to the forum thread for details.

If you have any other bright ideas on how to turn words into numbers, please don’t hesitate to post a comment. Happy coding!

Greg Pike

<-- Back to Part 1: Using the /*+ PARALLEL */ Hint

Monitoring Parallel Processes

While using parallel processing is a key advantage when handling extremely large data sets, it can be confusing to understand exactly what is occurring in the database during the execution of a parallel query. Fortunately, Oracle provides a whole host of V$ views that allow the developer and DBA alike to monitor parallel query execution and take proactive steps to rewrite ineffective code or kill resource-intensive processes.

The V$ Views

The queries in the following examples require access to several v_$ views that are owned by SYS. Typically, v_$ views are exposed to individual users through public synonyms with a naming convention that omits the underscore. For example, the SYS.V_$PX_SESSION view is typically referred to as V$PX_SESSION by individuals. The easiest (and least secure!) way to grant access to the entire suite of V_$ views is to GRANT SELECT ON ANY TABLE TO . However, be advised that V$ synonyms for all of these views sometimes do not exist. It has been this authors experience that the typical Oracle 10G database will already have these synonyms in place, but there are exceptions.

Basic Overview of Parallel Query Processes

If you took advantage of the link to the Oracle documentation from part 1 (or click here) you may have learned a little more about parallel processing. In general, when a parallel query executes, a query coordinator session acts as the ultimate parent for the parallel servers. For queries, two sets of parallel servers is used during execution. The first set of processes (called producers) typically handle full table scans while the second set (called consumers) performs operations (joins, hashes, etc.) upon the data retrieved from the first set. The degree of parallelism for a particular query governs how many parallel server processes will be found in each server set, not a total for the query. Lets use the following query to illustrate:

SELECT /*+ parallel(s 2) */ DISTINCT time_of_day
from sales s;

As this query executes, we can simultaneously execute the following query in another session to examine its parallel processes:

SELECT   qcsid,
         sid,
         NVL(server_group,0) server_group,
         server_set,
         degree,
         req_degree
FROM     SYS.V_$PX_SESSION
ORDER BY qcsid,
         NVL(server_group,0),
         server_set;

This query produces the following result (only during the time the parallel query is active):

     QCSID        SID SERVER_GROUP SERVER_SET     DEGREE REQ_DEGREE
---------- ---------- ------------ ---------- ---------- ----------
       390        390            0
       390        368            1          1          2          2
       390        353            1          1          2          2
       390        332            1          2          2          2
       390        330            1          2          2          2

Please note that this query, with a requested parallelism degree of 2, actually used 5 total processes to execute. Row one represents the query coordinator (QC). Rows 2 and 3 represent a set of parallel processes (server_set=1) that performed a full table scan of the SALES table while rows 4 and 5 (server_set=2) represent the parallel processes that would perform additional work on the data from SALES if necessary.

If we increase the degree of parallelism to 4 in a database that has a maximum allowable degree of 3, I would see something like this:

     QCSID        SID SERVER_GROUP SERVER_SET     DEGREE REQ_DEGREE
---------- ---------- ------------ ---------- ---------- ----------
       390        390            0
       390        351            1          1          3          4
       390        342            1          1          3          4
       390        330            1          1          3          4
       390        332            1          2          3          4
       390        353            1          2          3          4
       390        339            1          2          3          4

Although the requested degree of parallelism was 4 (REQ_DEGREE), the degree actually used was in fact only 3 (DEGREE) which explains why we have 3 processes in each server set.

Monitoring the Processes By Physical Reads

There are several methods for monitoring the status of parallel processes. When the volume of expected data is well known, physical reads represent a reasonable method for estimating the time for the query to complete. Physical reads may also be an indicator if a query is not retrieving the desired result:

SELECT   a.qcsid,
         a.sid,
         a.server_group,
         a.server_set,
         substr(b.name,1,20) operation,
         a.value
FROM     v$px_sesstat a,
         v$statname b
WHERE    a.statistic# = b.statistic#
AND      UPPER(b.name) = 'PHYSICAL READS'
ORDER BY a.qcsid,
         a.server_group,
         a.server_set;

This query produces the following result set only during the execution of a query with a parallelism of 2 and including complex joining or sorting operations combined with full table scans:

     QCSID        SID SERVER_GROUP SERVER_SET OPERATION             VALUE
---------- ---------- ------------ ---------- -------------------- ------
       332        330            1          1 physical reads         4632
       332        351            1          1 physical reads         4697
       332        333            1          2 physical reads         4554
       332        339            1          2 physical reads         4605
       332        332                         physical reads       168005

This query indicates that at the time of execution, both parallel server sets have performed work. SID 332 is the query coordination session (the session that actually initiated the query), and its physical read count will continually increase until the session is disconnected.

Monitoring Long Operations with V$SESSION_LONGOPS

The V$SESSION_LONGOPS table is used by the Oracle database to store the status of operations that are expected to take at least 6 seconds. Full table scans, hash joins and sort merges are just some of the operations that may log themselves to this table during the execution of a query. A nice feature of long operations is a estimated completion time in seconds which allows developers and DBAs monitor the status of a query. for queries that run in parallel, each parallel server will log entries to V$SESSION_LONGOPS in some cases:

SELECT sl.sid,
       sl.serial#,
       substr(sl.opname,1,30),
       sl.sofar,
       TO_CHAR(sl.start_time,'DD-MON-YYYY:HH24:MI:SS') start_time,
       sl.elapsed_seconds elapsed,
       sl.time_remaining remaining
FROM   v$session_longops sl,
       v$session s
WHERE  s.sid = sl.sid
AND    s.serial#=sl.serial#
AND    s.username='&username'
ORDER BY sl.start_time DESC,
         sl.time_remaining ASC;

During the execution a long, parallel query, you might see the following results from the long operations query:

   SID    SERIAL# OPERATION   START_TIME               ELAPSED  REMAINING
------ ---------- ----------- ---------------------- --------- ----------
   332      18956 Table Scan  03-MAY-2007:14:26:11          42         18
   333      18957 Table Scan  03-MAY-2007:14:26:13          40         20

It is important to note that V$SESSION_LONGOPS is not purely a tool to monitor parallel queries, but rather any query that includes long operations like table scans. Also, it has been this author’s experience that different versions of the database and even different queries affect the regularity with which this view is updated (or used at all!).

There are many other advanced tools and views for monitoring queries (V$SESSION_WAIT is a favorite of mine), but the basic tools presented here are a good starting point for the developer using parallel processing. Happy coding!

Greg Pike