2. How to generate a form, library or menu?
3. How to generate a report?
4. How to generate a trace file for a form?
5. How to generate a trace file for a report or concurrent program?
6. How to find the trace file?
7. How to format the trace file?
8. How to get the version of forms?
9. How to get the version of reports?
10. How to get the version of libraries?
11. How to get the version of packages (SQL or PL/SQL scripts)?
12. How to find a file?
13. How to view the text of a view?
QUESTIONS & ANSWERS
-------------------
1. How to generate a table or view?
Answer
------
11.0 and 11i: adodfcmp
Table:
adodfcmp \
odffile=.odf userid=ar/ar changedb=yes mode=tables \
touser=apps/apps priv_schema=system/manager
View:
adodfcmp \
odffile=.odf userid=apps/apps changedb=yes mode=views \
touser=apps/apps priv_schema=system/manager
References
----------
Note 137176.1 Ext/Pub The ADODFCMP Utility
Note 69756.1 Int/Pub ADODFCMP and Constraints within Oracle Applications
2. How to generate a form, library or menu?
Answer
------
11.0: f45gen
11i: f60gen
windows NT: ifcmp60nt
Form:
cd $AU_TOP/forms/US
f45gen module=.fmb userid=apps/ module_type=FORM
output_file=.fmx batch=yes compile_all=special
Copy the .fmx to the correct directory.
For example: $AP_TOP/forms/US
Library:
cd $AU_TOP/resource
f45gen module=.pll userid=apps/ module_type=LIBRARY
output_file=.plx batch=yes compile_all=special
Menu:
f45gen module=.mmb userid=apps/ module_type=MENU
output_file=.mmx batch=yes compile_all=special
References
----------
Note 130686.1 Ext/Pub How to Generate Form, Library and Menu for Oracle Applications
3. How to generate a report?
Answer
------
11.0: r25convm
11i: rwcon60
r25convm userid=apps/ source=.rdf dest=.rex
stype=rdffile dtype=rexfile logfile=.log overwrite=yes batch=yes
r25convm userid=apps/ source=.rex dest=.rdf
stype=rexfile dtype=rdffile logfile=.log overwrite=yes batch=yes
References
----------
Note 66703.1 Ext/Pub Batch Generation of Reports
Note 106504.1 Ext/Pub Payables Reports Frequently Asked Questions
4. How to generate a trace file for a form?
Answer
------
11.0:
Open the form and select Help -> Tools -> Trace
11i:
Open the form and select Help -> Diagnostics -> Trace with binds
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
5. How to generate a trace file for a report or concurrent program?
Answer
------
11.0 and 11i:
Select the Enable Trace flag in the concurrent program definition.
Responsibility: System Administrator
Path: Concurrent -> Program -> Define
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
6. How to find the trace file?
Answer
------
The directory where the trace files reside is defined by variable USER_DUMP_DEST,
if it is not set, execute the following sql command from sqlplus (apps account):
select value from v$parameter
where name='user_dump_dest';
Trace files are named ora_.trc. To retrieve your trace file you can try
unix command: ls -lt
Verify timestamp and also search a characteristic string with grep command.
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
7. How to format the trace file?
Answer
------
Use tkprof utility to format the raw trace in more readable file.
tkprof sys=no explain=apps/
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
8. How to get the version of forms?
Answer
------
11.0:
Open the form and select Help -> Tools -> About Oracle Applications
Verify the form name and version in the Current Form section.
11i:
Open the form and select Help -> Diagnostics -> About Oracle Applications
Verify the form name and version in the Current Form section.
You can verify the version to the Oracle Applications, Oracle Forms and RDBMS using this option.
or go to the directory of the file (for example: cd $AP_TOP/forms/US):
cd $/forms/
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
9. How to get the version of reports?
Answer
------
Go to the directory of the file (for example: cd $AP_TOP/reports/US):
cd $/reports/
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
10. How to get the version of libraries?
Answer
------
cd $AU_TOP/resource
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
11. How to get the version of packages (SQL or PL/SQL scripts)?
Answer
------
Go to $/admin/sql or /patch/110/sql for last version.
You should find the corresponding file with .sql, .pls, .pkh, or .pkb extension.
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
or use the following script in the sql&plus:
select text
from dba_source
where name like ''
and line in (1,2,3,4);
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
12. How to find a file?
Answer
------
Execute the following commands on unix:
cd $
find . -name '**' -print
find /i "$Header"
grep -i '' *
References
----------
Note 136962.1 Int/Rev How can we find which C source files contain a specific symbol or string
13. How to view the text of a view?
Answer
------
Execute the following script in the sql&plus:
select text
from all_views
where view_name =''
and owner = 'APPS';
References
----------
Note 1018636.102 Ext/Pub HOW TO SEE THE SELECT STATEMENT THAT DEFINES A VIEW.
Note 1075505.6 Ext/Arc HOW DO YOU FIND THE SCRIPT THAT CREATES A PARTICULAR VIEW?
Note 1016346.102 Ext/Pub GENERATE VIEW CREATION SCRIPT
Mohammed Riyaz Ahmed Khan
“The PATHAN Boy”
Using TKPROF and Explain Plan with Oracle -- Contents
Oracle TRACE Utility
Simple Steps for TRACE with a Simple Query
The Sections of a TRACE Output
The SQL Statement
The Statistics Section
Information Section
The EXPLAIN PLAN
A More Complex TKPROF Output
Using EXPLAIN PLAN Alone
An Additional EXPLAIN PLAN Example for a Simple Query
EXPLAIN PLAN--Read It Top to Bottom or Bottom to Top?
Reading the EXPLAIN PLAN
Setting AUTOTRACE On
Oracle TRACE Utility
The Oracle TRACE utility is used to measure timing statistics for a given query, a batch process, or an entire system. It is a fast method of finding where potential bottlenecks on the system reside. TRACE has the following functionality:
TRACE runs the query and generates statistics about an Oracle query that is executed.
TRACE helps developers analyze every section of a query.
Simple Steps for TRACE with a Simple Query
The steps for setting up and running Oracle's TRACE utility are as follows:
Set the following init.ora parameters:
TIMED.STATISTICS = TRUE
MAX_DUMP_FILE_SIZE = 2000000 (Not 2M)
USER_DUMP_DEST = /oracle8/rich_trc
In Oracle7, the database must be shut down and restarted for these parameters to take effect. In Oracle8, the TIMED_STATISTICS parameter may be set via an ALTER SESSION (for an individual session) or ALTER SYSTEM (for the entire system) command. The USER_DUMP_DEST specifies the location to put the files and the MAX_DUMP_FILE_SIZE specifies the maximum file size.
Enable TRACE for a SQL*Plus session (this starts TRACing for an individual session):
alter session set SQL_TRACE true;
Run the query to be TRACEd:
select table_name,
owner,
initial_extent,
uniqueness
from ind2
where owner || '' = 'SCOTT';
-- (Note: An index on "OWNER" is suppressed)
Disable TRACE for the SQL*Plus session:
alter session set SQL_TRACE false;
You can also enable TRACE for all sessions by setting the SQLTRACE parameter in the init.ora. You must shut down and restart the database for this to take effect. This is not suggested!
SQL_TRACE = TRUE
After running TRACE, your output file will look something like the following:
5_19554.trc
TIP Setting TIMED_STATISTICS=TRUE in the init.ora will begin TRACing upon the user's command. But, be careful; setting SQLTRACE=TRUE in the init.ora will cause the entire system and all queries to be TRACEd and could cause performance degradations.
Run TKPROF to put the TRACE file into readable format:
tkprof 5_19554.trc rich2.prf explain=system/manager
The TKPROF utility translates the TRACE file generated by the SQLTRACE facility to a readable format. You can run TKPROF against a TRACE file that you have previously created, or you can run it while the program that is creating the TRACE file is still running. Options for TKPROF are listed next.
tkprof tracefile output_file [sort = parameters]
[print =number] [explain=username/password@sid]
Command-Line Options:
TRACEfile
The name of the TRACE file containing the statistics by SQL_TRACE.
output_file
The name of the file where TKPROF writes its output.
SORT= parameters
The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.
PRINT=number
The number of statements to include in the output. included, TKPROF will list all statements in the output.
EXPLAIN=username/password@sid
Run the EXPLAIN PLAN on the user's SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.
New options in 7.3+:
INSERT= filename
This option creates a script to create a table and store the TRACE file statistics for each SQL statement TRACEd.
RECORD= filename
This option will produce a file of all the user's SQL statements.
SYS= YES/NO
This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.
SORT= parameters
There is a tremendous number of sorting options that are available. My favorites are FCHCPU (CPU time of fetch), FCHDSK (disk reads for fetch), FCHCU and FCHQRY (memory reads for fetch), FCH ROW (number of rows fetched), EXEDSK (disk reads during execute), EXECU and EXEQRY (memory reads during execute), EXEROW (rows processed during execute), EXECPU (execute CPU time), and PRSCNT (times parsed).
TIP
The TKPROF utility puts a TRACEd output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying "explain=username/password" (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query.
The output of the file rich2.prf (query with the index suppressed):
select table_name,
owner,
initial_extent,
uniqueness
from ind2
where owner = 'SCOTT';
count cpu elap disk query current rows
Parse: 1 1 2 0 0 0
Execute: 1 0 0 0 0 2 0
Fetch: 2 69 113 142 430 0 36
Execution Plan (no index used):
TABLE ACCESS (FULL) OF 'IND2'
The output shows 142 disk reads and 430 memory reads (query + current). Having such a high number of disk reads compared to physical reads is certainly a potential problem. The execution path shows a full table scan confirming that we may have a potential problem.
TIP A TRACEd query with a large number of physical reads usually indicates a missing index. The disk column indicates the physical reads (usually where an index is not used) and the query added to the current columns indicates the memory reads (usually reads where an index is being used).
Here's what happens when I rerun the query (after restarting the system) to be TRACEd, now using an index on the owner table:
select table_name,
owner,
initial_extent,
uniqueness
from ind2
where owner = 'SCOTT';
(The index on "OWNER" is not suppressed)
count cpu elap disk query current rows
Parse: 2 0 0 0 0 0
Execute: 2 0 0 0 0 0 0
Fetch: 4 6 6 0 148 0 72
Execution Plan (index used):
TABLE ACCESS (BY ROWID) OF 'IND2'
INDEX (RANGE SCAN) OF 'IND2_1' (NON-UNIQUE)
TIP
A TRACEd query output with only memory reads (query-consistent reads) indicates that an index is being used.
The Sections of a TRACE Output
The TRACE utility has multiple sections including the SQL statements, statistics, information, and the EXPLAIN PLAN. Each of these different topics are discussed in the following sections.
The SQL Statement
The first section of a TKPROF statement is the SQL statement. This statement will be the exact same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output. This can be helpful when you are reviewing the output from multiple sessions. If you find a statement causing problems, you would be able to search for the exact statement. Remember, some of the statements from Oracle forms are generated dynamically.
The Statistics Section
This section contains all the statistics for this SQL statement and all the recursive SQL statements generated to satisfy this statement. In this section there are eight columns, the first being the type of call to the database. There are three types of calls, parse, execute, and fetch. Each type of call will generate a separate line of statistics. The other seven columns are the statistics for each type of call.
count
The number of times this type of call was made.
cpu
The total CPU time for all of the calls of this type for this statement. If the TIMED_STATISTICS parameter in the init.ora is not set to TRUE, this statistic and the elapsed statistic will be 0.
elapsed
The total elapsed time for this call.
disk
The total number of data blocks retrieved from disk to satisfy this call.
query
The total number of data buffers retrieved from memory for this type SELECT statements usually retrieve buffers in this mode.
current
The total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE the usual access buffers in this mode.
rows
The total number of rows processed by this statement. The rows statements will appear in the row of Fetch statistics. INSERTS, UPDATES, and DELETES will appear in the execute row.
Information Section
This section contains information about the number of misses in the library cache from parse and execute calls. If the number of misses is high, there may be a problem with the size of the Shared Pool. You should check the hit ratio and the reload rate of the library cache. There is also information about the current optimizer mode setting. This section shows the username of the last user to parse this statement.
The EXPLAIN PLAN
This is the section of the TKPROF I find to be the most useful. The first column of this section of the TRPROF output listing below, is the number of rows processed by each line of the execution plan. Here, you will be able to see how bad a statement is. If the total number of rows in the Fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN, you may want to review the statement.
It is also possible that there is only one line of the execution plan that is processing a large number of rows compared to the rest of the statement. This can be caused by full table scans or the use of a bad index.
A More Complex TKPROF Output
The following example illustrates a TRACEd query with a slightly higher complexity.
select Item_Item_Id, InitCap( Item_Description )
from Item
where Item_Classification = 1
and Item_Item_Id Between 1000000 And 2700000
and Item_Item_Id Not In ( Select Invitem_Item_Id
from Inventory_Item
where Invitem_Location_Id = '405')
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 20.87 21.24 0 4408 0 399
Totals 29 20.87 21.24 0 4408 0 399
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 106 (C12462)
Rows Execution Plan
0 SELECT STATEMENT OPTIMIZER HINT: CHOOSE
572 FILTER
598 TABLE ACCESS (BY ROWID) OF 'ITEM'
599 INDEX (RANGE SCAN) OF 'ITEM_PK' (UNIQUE)
278790 INDEX (RANGE SCAN) OF 'INVITEM_PK' (UNIQUE)
Some of the things to look for in the TKPROF output are listed in this table:
Problems Solutions
High numbers for the parsing The SHARED_POOL_SIZE may need to be increased.
The disk reads are very high Indexes are not used or may not exist.
The "query" and/or "current" (memory reads) are very high Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance.
The parse elapse time is high There may be a problem with the number of open cursors.
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement.
If the number of misses in the library cache during parse is greater than 1 This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora.
Using EXPLAIN PLAN Alone
The EXPLAIN PLAN command allows a developer to view the query execution plan that the Oracle optimizer will use to execute a SQL statement. This command is very helpful in improving performance of SQL statements, since it does not actually execute the SQL statement--it only outlines the plan to use and inserts this execution plan in an Oracle table. Prior to using the EXPLAIN PLAN command, a file called UTLXPLAN.sql (located in the same directory as CATALOG.sql) must be executed under the Oracle account that will be executing the EXPLAIN PLAN command. The script creates a table called PLAN_TABLE that is used by the EXPLAIN PLAN command to insert the query execution plan in the form of records. This table can then be queried and viewed to determine if there needs to be any modifications to the SQL statement to force a different execution plan. An EXPLAIN PLAN example is shown next (executed in SQL*Plus).
Q. Why use EXPLAIN PLAN without TRACE?
A. The statement is not executed; it only shows what will happen if the statement is executed.
Q. When do you use EXPLAIN PLAN without TRACE?
A. When the query will take exceptionally long to run.
The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:
TRACE
It takes four hours to TRACE a query that takes four hours to run.
Set up Init.ora Parameters
Create PLAN_TABLE table
Run Query
Statement is executed PLAN_TABLE is populated
Run TKPROF
Output shows disk and memory reads in addition to EXPLAIN PLAN output
EXPLAIN PLAN
It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run.
Create PLAN_TABLE table
Explain Query
PLAN_TABLE is populated
Query PLAN_TABLE
Output shows EXPLAIN PLAN
Q.
How do I use EXPLAIN PLAN by itself
A.
Find the script; it is usually in the ORACLE_HOME/rdbms/admin:
"utlxplan.sql"
Execute the script XPLAINPL.sql in SQL*Plus:
@utlxplan
This creates the PLAN_TABLE for the user executing the script. You may create your own PLAN_TABLE, but use Oracle"s syntax or else!!!
Run EXPLAIN PLAN for the query to be optimized:
explain plan for
select CUSTOMER_NUMBER
from CUSTOMER
where CUSTOMER_NUMBER = 111;
Explained.
EXPLAIN PLAN for the query to be optimized (using a tag for the statement):
explain plan
set statement_id = 'CUSTOMER' for
select CUSTOMER_NUMBER
from CUSTOMER
where CUSTOMER_NUMBER = 111;
TIP Use the SET STATEMENT_ID = "your identifier" when the PLAN_TABLE will be populated by many different developers. I rarely use the SET STATEMENT_ID statement. Instead, I EXPLAIN PLAN a query, look at the output, and then delete from the PLAN_TABLE table. I continue to do this (making changes to the query), until I see an execution plan that I think will be favorable. I then run the query to see if the performance has been improved. If multiple developers/DBAs are using the same PLAN_TABLE, then the SET STATEMENT_ID will be essential to identifying a statement.
Select the output from the PLAN_TABLE table:
select operation,
options,
object_name,
id,
parent_id
from plan_table
where statement_id = 'CUSTOMER';
Operation Options Object Name ID Parent
select statement 0
Table Access By ROWID Customer 1
Index Range Scan CUST_IDX 2 1
TIP Use EXPLAIN PLAN instead of TRACE so that you don't have to wait for the query to run. EXPLAIN PLAN will show the path of a query without actually running the query. Use TRACE only for multi-query batch jobs to find out which of the many queries in the batch job are slow.
An Additional EXPLAIN PLAN Example for a Simple Query
Run the query with the EXPLAIN syntax embedded prior to the query:
explain plan
set statement_id ='query 1' for
select customer_number,
name
customer
from customer
where customer_number = '111';
Retrieve the output of EXPLAIN PLAN by querying the PLAN_TABLE:
To retrieve the information for viewing, a SQL statement must be executed. Two scripts provided in the Oracle documentation are displayed in this step and in step 3, along with the results of each based on the previous EXPLAIN PLAN command. Note that this example varies from the last example. The customer_number column is an indexed number field, which in the second example is suppressed (by forcing a to_char) because of a data type mismatch ('111' is in quotes). In the first example, I treated the customer_number column correctly as a number field (111 is not in quotes).
select operation,
options,
object_name,
id,
parent_id,
position
from plan_table
where statement_id = 'query 1'
order by id;
Operation Options Object Name ID Parent
select statement 0
Table Access Full Customer_Information 1
Retrieving a more intuitive and easy to read output of EXPLAIN PLAN:
select lpad(' ', 2*(level-1)) || operation || ' ' || options || ' ' ||
object_name || ' ' || decode(id, 0, 'Cost = ' || position) 'Query Plan'
from plan_table
start with id = 0
and statement_id = 'query 1'
connect by prior id = parent_id
and statement_id = 'query 1';
Output:
Query Plan
select statement Cost=220
Table Access Full Customer
EXPLAIN PLAN--Read It Top to Bottom or Bottom to Top?
Actually, it depends on how you write the query that retrieves the information from the PLAN_TABLE table. That is probably why many people differ on which way to read the result (all of them may be correct). Next, I give an example with the order of execution based on the query that retrieves the information. In this example, the output is read top to bottom with one caveat...you must read from the innermost to the outermost. The example here shows a method that should clear up any questions.
The SQL statement should be placed after the FOR clause of the EXPLAIN PLAN:
delete from plan_table;
explain plan
set statement_id = 'SQL1' for
select to_char(sysdate, 'MM/DD/YY HH:MM AM'),
to_char((trunc((sysdate -4, -1), 'day') +1), 'DD-MON-YY'),
from bk, ee
where bk_shift_date >= to_char((trunc(( sysdate - 4 - 1), 'day' + 1),
'DD-MON-YY')
and bk_shift_date <= to_char((sysdate - 4), 'DD-MON-YY')
and bk_empno = ee_empno (+)
and substr( ee_hierarchy_code, 1, 3) in ('PNA', 'PNB', 'PNC',
'PND', 'PNE', 'PNF')
order by ee_job_group,
bk_empno,
bk_shift_date
/
select LPad(' ', 2*(Level-1)) || Level || '.' || nvl(Position,0) ||
' ' || Operation || ' ' || Options || ' ' || Object_Name ||
' ' || Object_Type || ' ' || Decode(id, 0, Statement_id ||
'Cost = ' || Position) || Other || ' ' ||
Object_Node "Query Plan"
from plan_table
start with id = 0
and statement_id = 'SQL1'
connect by prior id = parent_id
and statement_id = 'SQL1'
/
Query Plan
1.0 SELECT STATEMENT SQL1 Cost =
2.1 SORT ORDER BY
3.1 FILTER
4.1 NESTED LOOPS OUTER
5.1 TABLE ACCESS BY ROWID BK
6.1 INDEX RANGE SCAN I_BK_06 NON-UNIQUE
5.2 TABLE ACCESS BY ROWID EE
6.1 INDEX UNIQUE SCAN I_EE_01 UNIQUE
Reading the EXPLAIN PLAN
Using the previous EXPLAIN PLAN, I will explain the steps below. A located in the left column in the following table identifies each step. order in which they were executed.
Step Action
6.1 This is the index range scan of I_BK_06. This is the first step. This index is on the bk_shift_dt column. This step performs a scan of this index to produce a list of ROWIDs that fall between the two dates.
5.1 Retrieve the rows from the BK table.
6.1 Scan of the I_EE_01 index. This index is on the ee_empno column. Using the bk_empno retrieved from the previous step, this index is scanned to retrieve the ROWIDs to produce a list of the ee_empnos that match the bk_empnos.
5.2 Retrieve the rows from the EE table.
4.1 NESTED LOOP. The two lists are joined, producing one list.
3.1 FILTER. The rest of the conditions of the WHERE clause are applied.
2.1 SORT ORDER BY. The remaining rows are sorted according to the ORDER BY clause.
1.0 This tells what type of statement it is.
TIP Whether the EXPLAIN PLAN is read from top to bottom or from the bottom to the top is dependent entirely on the query used to select information from the PLAN_TABLE table. Both methods of reading the query may be correct, given the query selecting the information is correctly structured.
Setting AUTOTRACE On
There is also an easier method with SQL*Plus for generating an EXPLAIN PLAN and statistics about the performance of a query. The AUTOTRACE command (available in SQL*Plus 3.3 and later) generates similar information, as shown in this example:
SET AUTOTRACE ON
select count(name)
from emp7
where name = 'branches';
Output
COUNT(NAME)
100
Query Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREDATE)
2 1 INDEX (RANGE SCAN) OF 'EMP7_I1' (NON-UNIQUE)
Statistics
0 recursive calls
0 db block gets
1 consistent gets
1 physical reads
0 redo size
223 bytes sent via SQL*Net to client
274 bytes recd via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
TIP The AUTOTRACE option provides an EXPLAIN PLAN and statistics for a query. The AUTOTRACE provides many of the TRACE and TKPROF statistics such as disk reads (physical reads) and memory reads (consistent reads ÷ db block gets).
- Set the following init.ora parameters:
TIMED.STATISTICS = TRUE MAX_DUMP_FILE_SIZE = 2000000 (Not 2M) USER_DUMP_DEST = /oracle8/rich_trc
In Oracle7, the database must be shut down and restarted for these parameters to take effect. In Oracle8, the TIMED_STATISTICS parameter may be set via an ALTER SESSION (for an individual session) or ALTER SYSTEM (for the entire system) command. The USER_DUMP_DEST specifies the location to put the files and the MAX_DUMP_FILE_SIZE specifies the maximum file size. - Enable TRACE for a SQL*Plus session (this starts TRACing for an individual session):
alter session set SQL_TRACE true;
- Run the query to be TRACEd:
select table_name, owner, initial_extent, uniqueness from ind2 where owner || '' = 'SCOTT'; -- (Note: An index on "OWNER" is suppressed) - Disable TRACE for the SQL*Plus session:
alter session set SQL_TRACE false;
- You can also enable TRACE for all sessions by setting the SQLTRACE parameter in the init.ora. You must shut down and restart the database for this to take effect. This is not suggested!
SQL_TRACE = TRUE
After running TRACE, your output file will look something like the following:5_19554.trc
TIP Setting TIMED_STATISTICS=TRUE in the init.ora will begin TRACing upon the user's command. But, be careful; setting SQLTRACE=TRUE in the init.ora will cause the entire system and all queries to be TRACEd and could cause performance degradations. - Run TKPROF to put the TRACE file into readable format:
tkprof 5_19554.trc rich2.prf explain=system/manager
The TKPROF utility translates the TRACE file generated by the SQLTRACE facility to a readable format. You can run TKPROF against a TRACE file that you have previously created, or you can run it while the program that is creating the TRACE file is still running. Options for TKPROF are listed next.tkprof tracefile output_file [sort = parameters] [print =number] [explain=username/password@sid]
Command-Line Options:
- TRACEfile
- The name of the TRACE file containing the statistics by SQL_TRACE.
- output_file
- The name of the file where TKPROF writes its output.
- SORT= parameters
- The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.
- PRINT=number
- The number of statements to include in the output. included, TKPROF will list all statements in the output.
- EXPLAIN=username/password@sid
- Run the EXPLAIN PLAN on the user's SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.
- New options in 7.3+:
- INSERT= filename
- This option creates a script to create a table and store the TRACE file statistics for each SQL statement TRACEd.
- RECORD= filename
- This option will produce a file of all the user's SQL statements.
- SYS= YES/NO
- This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.
- SORT= parameters
- There is a tremendous number of sorting options that are available. My favorites are FCHCPU (CPU time of fetch), FCHDSK (disk reads for fetch), FCHCU and FCHQRY (memory reads for fetch), FCH ROW (number of rows fetched), EXEDSK (disk reads during execute), EXECU and EXEQRY (memory reads during execute), EXEROW (rows processed during execute), EXECPU (execute CPU time), and PRSCNT (times parsed).
The TKPROF utility puts a TRACEd output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying "explain=username/password" (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query.
- The output of the file rich2.prf (query with the index suppressed):
select table_name, owner, initial_extent, uniqueness from ind2 where owner = 'SCOTT'; count cpu elap disk query current rows Parse: 1 1 2 0 0 0 Execute: 1 0 0 0 0 2 0 Fetch: 2 69 113 142 430 0 36Execution Plan (no index used):TABLE ACCESS (FULL) OF 'IND2'
The output shows 142 disk reads and 430 memory reads (query + current). Having such a high number of disk reads compared to physical reads is certainly a potential problem. The execution path shows a full table scan confirming that we may have a potential problem. TIP A TRACEd query with a large number of physical reads usually indicates a missing index. The disk column indicates the physical reads (usually where an index is not used) and the query added to the current columns indicates the memory reads (usually reads where an index is being used).
- Here's what happens when I rerun the query (after restarting the system) to be TRACEd, now using an index on the owner table:
select table_name, owner, initial_extent, uniqueness from ind2 where owner = 'SCOTT'; (The index on "OWNER" is not suppressed) count cpu elap disk query current rows Parse: 2 0 0 0 0 0 Execute: 2 0 0 0 0 0 0 Fetch: 4 6 6 0 148 0 72Execution Plan (index used):TABLE ACCESS (BY ROWID) OF 'IND2' INDEX (RANGE SCAN) OF 'IND2_1' (NON-UNIQUE)
TIP A TRACEd query output with only memory reads (query-consistent reads) indicates that an index is being used.
The Sections of a TRACE Output
The SQL Statement
The Statistics Section
- count
- The number of times this type of call was made.
- cpu
- The total CPU time for all of the calls of this type for this statement. If the TIMED_STATISTICS parameter in the init.ora is not set to TRUE, this statistic and the elapsed statistic will be 0.
- elapsed
- The total elapsed time for this call.
- disk
- The total number of data blocks retrieved from disk to satisfy this call.
- query
- The total number of data buffers retrieved from memory for this type SELECT statements usually retrieve buffers in this mode.
- current
- The total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE the usual access buffers in this mode.
- rows
- The total number of rows processed by this statement. The rows statements will appear in the row of Fetch statistics. INSERTS, UPDATES, and DELETES will appear in the execute row.
Information Section
The EXPLAIN PLAN
It is also possible that there is only one line of the execution plan that is processing a large number of rows compared to the rest of the statement. This can be caused by full table scans or the use of a bad index. The following example illustrates a TRACEd query with a slightly higher complexity.
A More Complex TKPROF Output
select Item_Item_Id, InitCap( Item_Description )
from Item
where Item_Classification = 1
and Item_Item_Id Between 1000000 And 2700000
and Item_Item_Id Not In ( Select Invitem_Item_Id
from Inventory_Item
where Invitem_Location_Id = '405')
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 20.87 21.24 0 4408 0 399
Totals 29 20.87 21.24 0 4408 0 399
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 106 (C12462)
Rows Execution Plan
0 SELECT STATEMENT OPTIMIZER HINT: CHOOSE
572 FILTER
598 TABLE ACCESS (BY ROWID) OF 'ITEM'
599 INDEX (RANGE SCAN) OF 'ITEM_PK' (UNIQUE)
278790 INDEX (RANGE SCAN) OF 'INVITEM_PK' (UNIQUE)
Some of the things to look for in the TKPROF output are listed in this table: | Problems | Solutions |
| High numbers for the parsing | The SHARED_POOL_SIZE may need to be increased. |
| The disk reads are very high | Indexes are not used or may not exist. |
| The "query" and/or "current" (memory reads) are very high | Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance. |
| The parse elapse time is high | There may be a problem with the number of open cursors. |
| The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows | This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement. |
| If the number of misses in the library cache during parse is greater than 1 | This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora. |
Using EXPLAIN PLAN Alone
| Q. | Why use EXPLAIN PLAN without TRACE? |
| A. | The statement is not executed; it only shows what will happen if the statement is executed. |
| Q. | When do you use EXPLAIN PLAN without TRACE? |
| A. | When the query will take exceptionally long to run. |
The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:
TRACE It takes four hours to TRACE a query that takes four hours to run.
| EXPLAIN PLAN It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run.
|
- Q.
- How do I use EXPLAIN PLAN by itself
- A.
- Find the script; it is usually in the ORACLE_HOME/rdbms/admin:
"utlxplan.sql"
- Execute the script XPLAINPL.sql in SQL*Plus:
@utlxplanThis creates the PLAN_TABLE for the user executing the script. You may create your own PLAN_TABLE, but use Oracle"s syntax or else!!! - Run EXPLAIN PLAN for the query to be optimized:
explain plan for select CUSTOMER_NUMBER from CUSTOMER where CUSTOMER_NUMBER = 111; Explained. - EXPLAIN PLAN for the query to be optimized (using a tag for the statement):
explain plan set statement_id = 'CUSTOMER' for select CUSTOMER_NUMBER from CUSTOMER where CUSTOMER_NUMBER = 111;
TIP Use the SET STATEMENT_ID = "your identifier" when the PLAN_TABLE will be populated by many different developers. I rarely use the SET STATEMENT_ID statement. Instead, I EXPLAIN PLAN a query, look at the output, and then delete from the PLAN_TABLE table. I continue to do this (making changes to the query), until I see an execution plan that I think will be favorable. I then run the query to see if the performance has been improved. If multiple developers/DBAs are using the same PLAN_TABLE, then the SET STATEMENT_ID will be essential to identifying a statement. - Select the output from the PLAN_TABLE table:
select operation, options, object_name, id, parent_id from plan_table where statement_id = 'CUSTOMER'; Operation Options Object Name ID Parent select statement 0 Table Access By ROWID Customer 1 Index Range Scan CUST_IDX 2 1TIP Use EXPLAIN PLAN instead of TRACE so that you don't have to wait for the query to run. EXPLAIN PLAN will show the path of a query without actually running the query. Use TRACE only for multi-query batch jobs to find out which of the many queries in the batch job are slow.
- Find the script; it is usually in the ORACLE_HOME/rdbms/admin:
An Additional EXPLAIN PLAN Example for a Simple Query
- Run the query with the EXPLAIN syntax embedded prior to the query:
explain plan set statement_id ='query 1' for select customer_number, name customer from customer where customer_number = '111'; - Retrieve the output of EXPLAIN PLAN by querying the PLAN_TABLE: To retrieve the information for viewing, a SQL statement must be executed. Two scripts provided in the Oracle documentation are displayed in this step and in step 3, along with the results of each based on the previous EXPLAIN PLAN command. Note that this example varies from the last example. The customer_number column is an indexed number field, which in the second example is suppressed (by forcing a to_char) because of a data type mismatch ('111' is in quotes). In the first example, I treated the customer_number column correctly as a number field (111 is not in quotes).
select operation, options, object_name, id, parent_id, position from plan_table where statement_id = 'query 1' order by id; Operation Options Object Name ID Parent select statement 0 Table Access Full Customer_Information 1
- Retrieving a more intuitive and easy to read output of EXPLAIN PLAN:
select lpad(' ', 2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || decode(id, 0, 'Cost = ' || position) 'Query Plan' from plan_table start with id = 0 and statement_id = 'query 1' connect by prior id = parent_id and statement_id = 'query 1';Output:Query Plan select statement Cost=220 Table Access Full Customer
EXPLAIN PLAN--Read It Top to Bottom or Bottom to Top?
The SQL statement should be placed after the FOR clause of the EXPLAIN PLAN:
delete from plan_table;
explain plan
set statement_id = 'SQL1' for
select to_char(sysdate, 'MM/DD/YY HH:MM AM'),
to_char((trunc((sysdate -4, -1), 'day') +1), 'DD-MON-YY'),
from bk, ee
where bk_shift_date >= to_char((trunc(( sysdate - 4 - 1), 'day' + 1),
'DD-MON-YY')
and bk_shift_date <= to_char((sysdate - 4), 'DD-MON-YY')
and bk_empno = ee_empno (+)
and substr( ee_hierarchy_code, 1, 3) in ('PNA', 'PNB', 'PNC',
'PND', 'PNE', 'PNF')
order by ee_job_group,
bk_empno,
bk_shift_date
/
select LPad(' ', 2*(Level-1)) || Level || '.' || nvl(Position,0) ||
' ' || Operation || ' ' || Options || ' ' || Object_Name ||
' ' || Object_Type || ' ' || Decode(id, 0, Statement_id ||
'Cost = ' || Position) || Other || ' ' ||
Object_Node "Query Plan"
from plan_table
start with id = 0
and statement_id = 'SQL1'
connect by prior id = parent_id
and statement_id = 'SQL1'
/
Query Plan
1.0 SELECT STATEMENT SQL1 Cost =
2.1 SORT ORDER BY
3.1 FILTER
4.1 NESTED LOOPS OUTER
5.1 TABLE ACCESS BY ROWID BK
6.1 INDEX RANGE SCAN I_BK_06 NON-UNIQUE
5.2 TABLE ACCESS BY ROWID EE
6.1 INDEX UNIQUE SCAN I_EE_01 UNIQUE
Reading the EXPLAIN PLAN
| Step | Action |
| 6.1 | This is the index range scan of I_BK_06. This is the first step. This index is on the bk_shift_dt column. This step performs a scan of this index to produce a list of ROWIDs that fall between the two dates. |
| 5.1 | Retrieve the rows from the BK table. |
| 6.1 | Scan of the I_EE_01 index. This index is on the ee_empno column. Using the bk_empno retrieved from the previous step, this index is scanned to retrieve the ROWIDs to produce a list of the ee_empnos that match the bk_empnos. |
| 5.2 | Retrieve the rows from the EE table. |
| 4.1 | NESTED LOOP. The two lists are joined, producing one list. |
| 3.1 | FILTER. The rest of the conditions of the WHERE clause are applied. |
| 2.1 | SORT ORDER BY. The remaining rows are sorted according to the ORDER BY clause. |
| 1.0 | This tells what type of statement it is. |
TIP Whether the EXPLAIN PLAN is read from top to bottom or from the bottom to the top is dependent entirely on the query used to select information from the PLAN_TABLE table. Both methods of reading the query may be correct, given the query selecting the information is correctly structured. There is also an easier method with SQL*Plus for generating an EXPLAIN PLAN and statistics about the performance of a query. The AUTOTRACE command (available in SQL*Plus 3.3 and later) generates similar information, as shown in this example:
Setting AUTOTRACE On
SET AUTOTRACE ON select count(name) from emp7 where name = 'branches';Output
COUNT(NAME)
100
Query Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREDATE)
2 1 INDEX (RANGE SCAN) OF 'EMP7_I1' (NON-UNIQUE)
Statistics
0 recursive calls
0 db block gets
1 consistent gets
1 physical reads
0 redo size
223 bytes sent via SQL*Net to client
274 bytes recd via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
TIP The AUTOTRACE option provides an EXPLAIN PLAN and statistics for a query. The AUTOTRACE provides many of the TRACE and TKPROF statistics such as disk reads (physical reads) and memory reads (consistent reads ÷ db block gets). Using the previous EXPLAIN PLAN, I will explain the steps below. A located in the left column in the following table identifies each step. order in which they were executed. Actually, it depends on how you write the query that retrieves the information from the PLAN_TABLE table. That is probably why many people differ on which way to read the result (all of them may be correct). Next, I give an example with the order of execution based on the query that retrieves the information. In this example, the output is read top to bottom with one caveat...you must read from the innermost to the outermost. The example here shows a method that should clear up any questions. This section contains information about the number of misses in the library cache from parse and execute calls. If the number of misses is high, there may be a problem with the size of the Shared Pool. You should check the hit ratio and the reload rate of the library cache. There is also information about the current optimizer mode setting. This section shows the username of the last user to parse this statement. This is the section of the TKPROF I find to be the most useful. The first column of this section of the TRPROF output listing below, is the number of rows processed by each line of the execution plan. Here, you will be able to see how bad a statement is. If the total number of rows in the Fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN, you may want to review the statement. The TRACE utility has multiple sections including the SQL statements, statistics, information, and the EXPLAIN PLAN. Each of these different topics are discussed in the following sections. The first section of a TKPROF statement is the SQL statement. This statement will be the exact same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output. This can be helpful when you are reviewing the output from multiple sessions. If you find a statement causing problems, you would be able to search for the exact statement. Remember, some of the statements from Oracle forms are generated dynamically. This section contains all the statistics for this SQL statement and all the recursive SQL statements generated to satisfy this statement. In this section there are eight columns, the first being the type of call to the database. There are three types of calls, parse, execute, and fetch. Each type of call will generate a separate line of statistics. The other seven columns are the statistics for each type of call.
