Execution Plans - Troubleshooting Oracle Performance - page 297

Identifying Child Cursors
The first common situation you have to face is trying to get information about a SQL statement that is related to
a session currently connected to the instance. In this case, you execute the search on the v$session view.
The currently executed SQL statement is identified by the sql_id (or sql_address ) and
sql_child_number columns. The last-executed SQL statement is identified by the prev_sql_id (or prev_sql_addr )
and prev_child_number columns. To illustrate the use of this method, let's say that a user, Curtis, calls you and
complains that he's waiting on a request submitted with an application just a few minutes ago. For this problem,
it's useful to query the v$session view directly, as shown in the following example. With that output, you know he's
currently running a SQL statement (otherwise, the status wouldn't be ACTIVE ) and which cursor is related to his
session:
SQL> SELECT status, sql_id, sql_child_number
2 FROM v$session
3 WHERE username = 'CURTIS';
STATUS SQL_ID SQL_CHILD_NUMBER
------- ------------- ----------------
ACTIVE 1scu79x31qavt 1
The second common situation is when you do know the text of the SQL statement that you want to find more
information about. In this case, you execute the search on the v$sql view. The text associated with a cursor is
available in the sql_text and sql_fulltext columns. The difference between the two columns is that the first shows
only the first part of the text through a VARCHAR2(1000) , while the second shows the whole text through a CLOB . For
example, if you know that the SQL statement you're looking for contains a literal with the text “online discount,” you
can use the following query to find out the identifiers of the cursor:
SQL> SELECT sql_id, child_number, sql_text
2 FROM v$sql
3 WHERE sql_fulltext LIKE '%online discount%'
4 AND sql_text NOT LIKE '%v$sql%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ---------------------------------------------------
1hqjydsjbvmwq 0 SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P
WHERE S.PROMO_ID = P.PROMO_ID AND PROMO_SUBCATEGORY
= 'online discount'
Querying Dynamic Performance Views
To obtain the execution plan, you can run queries directly against the v$sql_plan and v$sql_plan_statistics_
all views. However, there's an easier and much better way to do it: you can use the display_cursor function
in the dbms_xplan package. As shown in the following example, its use is similar to calling the display function
previously discussed. The only difference is that two parameters identifying the child cursor to be displayed are
passed to the function:
SQL> SELECT * FROM table(dbms_xplan.display_cursor('1hqjydsjbvmwq', 0));