| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Query performance
Here are some ideas cut from chapter 1 of my book on timing things. Basically, you can time things by tracing them in addition to setting the timing on (I appologize that I can't line everything up given this text window). The notes for timing, trace and explain are below.
Thanks,
Rich Niemiec, Oracle Performance Tuning Tips and Techniques; Oracle Press
http://www.amazon.com/exec/obidos/ASIN/0078824346/o/qid=928871379/sr=2-1/1
03-5004516-2917402
From chapter 1:
What you need to know before you tune your system
The first thing you need to know is the data. The volume of data and the distribution of data will affect how you tune individual queries. You also need to have a "shopping cart" full of tuning methods to try. Multiple approaches must be made to cover all types of queries. A single method of tuning or a single tuning product is NOT enough. You also need to know where the system is slow. Many DBAs and developers spend endless hours finding problem queries instead of asking the users of the system. Users will almost always be happy to volunteer this information. You also need to network with other developers that work on a similar system. Sharing information at user groups is a great way to network.
Setting Timing On
To tell whether or not you are improving the performance of a query you must have a benchmark or way to time the query. While a stopwatch can be used, the "set timing" feature in SQLPLUS is an excellent way to time queries. Unfortunately, the timing is affected by others on the system, but those of us who tune seriously usually do it at 2am. Use the following SQL statements for the SET TIMING feature.
SQL> SET TIMING ON
SQL> select count(name) from emp7 where name = 'branches';
Output
COUNT(NAME)
Elapsed: 00:00:00.84 (HOURS:MINUTES:SECONDS)
Setting Autotrace On
An even better way for measuring the performance of queries (in SQLPLUS 3.3 and later) is to use the AUTOTRACE command. Use the following SQL statements for the AUTOTRACE feature.
SQL> SET AUTOTRACE ON
SQL> select count(name) from emp7 where name = 'branches';
Output
COUNT(NAME)
Execution Plan
---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT
(AGGREGATE) 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
Tracing a single session:
select sid, serial#
from v$session
where username = ‘SCOTT’;
SID SERIAL#
---------- ----------
9 190
1 row selected.
execute dbms_system.set_sql_trace_in_session(9,190,TRUE); PL/SQL procedure successfully completed.
The Oracle Trace Utility (See Chapter 6 for more information on trace and
explain plan)
In order to tune queries you must be able to find problem queries and analyze
the potential problems with those queries. The Oracle Trace utility is used to
measure timing statistics for a given query, batch process (with multiple
queries) or an entire system. It is a fast method of finding where potential
bottlenecks on the system reside. It also has an option to run an explain plan
to generate the optimizers path for executing a query. This section will focus
briefly on how to use this powerful tool. Chapter xx is completely devoted to
Trace and Explain.
Simple Steps for Trace with a Simple Query:
SELECT TABLE_NAME, OWNER, INITIAL_EXTENT, UNIQUENESS FROM IND2 WHERE OWNER || '' = 'SCOTT' ;
4. Disable TRACE for the SQLPLUS same session:
SQL>ALTER SESSION SET SQL_TRACE FALSE; 5. You can ALSO Enable TRACE for all sessions (NOT SUGGESTED):
SQL_TRACE = TRUE (In the INIT.ORA)
After running TRACE your output file may be something like: 5_19554.trc
6. 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 SQL trace 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 below.
7a. The output of the file "rich2.prf" (with the index suppressed):
SELECT TABLE_NAME, OWNER,
INITIAL_EXTENT, UNIQUENESS
FROM IND2
WHERE OWNER = 'SCOTT';
count cpu elap phys cr cur 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'
7b. Re-Run the query to be TRACED now using the index:
SELECT TABLE_NAME, OWNER, INITIAL_EXTENT, UNIQUENESS FROM IND2 WHERE OWNER = 'SCOTT' ; (The index on "OWNER" is not suppressed)
The output of the file "rich2.prf":
SELECT TABLE_NAME, OWNER,
INITIAL_EXTENT, UNIQUENESS
FROM IND2
WHERE OWNER = 'SCOTT' ;
count cpu elap phys cr cur 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 with a large number of physical reads usually indicates a
missing index. The "phys" column indicates the physical reads (usually where
an index is not used) and the "cr" added to the "cur" columns indicates the
memory reads (usually reads where an index is being used).
Using Explain Plan (See Chapter xx for more information)
The Explain Plan command allows a developer to view the query execution plan that the Oracle optimizer will use to execute an 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.
Why use Explain without TRACE?
* The Statement is NOT executed, it only shows what will happen if the
statement is executed.
When to use Explain without TRACE:
* When the query will take exceptionally long to run.
How do I use Explain by itself?
· "utlxplan.sql"
2. Execute the script xplainpl.sql in SQLPLUS:
SQL>@utlplan (In V7)
(This creates the PLAN_TABLE for the user executing the script)
3a. Run Explain Plan for the query to be optimized:
EXPLAIN PLAN FOR
SELECT CUSTOMER_NUMBER FROM CUSTOMER WHERE CUSTOMER_NUMBER = 111;
Explained.
3b. Run 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;
4. Select the output from the PLAN_TABLE:
select operation, options, object_name, id, parent_id, position 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 instead of TRACE so that you don't have to wait for the query to run. EXPLAIN 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 is slow.
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). Below, I give an example with the order of execution based on the query that retrieves the information..
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(+)
('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
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 explain plan above I will explain the steps. Each step is identified by the number on the left. I will go in the order in which they are executed.
Step Action 6.1 This is the index range scan of I_BK_06. This is the first step. Thisindex is on the BK_SHIFT_DT column. This step performs a scan of this index to get produce a list of row numbers 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. Usingthe BK_EMPNO retrieved from the previous step, this index is scan to retrieve the rowids to produce a list of the EE_EMPNO's that match the BK_EMPNO's.
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. Both methods of reading the query may be correct, given the query selecting the information is correctly structured.. Received on Tue Apr 11 2000 - 00:00:00 CDT
 |
 |