sql tuning (three utilities)

1. using the explain plan

   The EXPLAIN PLAN tool is great for this experimentation, as it immediately lets you know how
   the query will perform. with each change in the code. Because the utility gives you the
   execution plan without executing the code, you save yourself from having to run untuned
   software to see whether the changes were beneficial or not.
   
   SQL> EXPLAIN PLAN
  2  SET statement_id = 'test1'
  3  INTO plan_table
  4  FOR select p.product_id,i.quantity_on_hand
  5  FROM oe.inventories i,
  6  oe.product_descriptions p,
  7  oe.warehouses w
  8  WHERE p.product_id=i.product_id
  9  AND i.quantity_on_hand > 250
 10  AND w.warehouse_id = i.warehouse_id;
Explained.
SQL>
  
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    1  |  37   | 3      (0) | 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSONS |    1  |  37   | 3      (0) | 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id) :
______________________________________________
-  filter ("ENAME" LIKE '%ALAPATI%' AND "CREATED_DATE">SYSDATE@!-30)

13 rows selected.
SQL>
  
  
  
2. autotrace
The Autotrace facility enables you to produce EXPLAIN PLANs automatically when you execute a
SQL statement in SQL*Plus

 
  SQL> @ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> DROP ROLE plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> CREATE ROLE plustrace;
Role created.
SQL>
SQL> GRANT SELECT ON v_$sesstat TO plustrace;
Grant succeeded.
SQL> GRANT SELECT ON v_$statname TO plustrace;
Grant succeeded.
SQL> GRANT SELECT ON v_$mystat TO plustrace;
Grant succeeded.
SQL> GRANT plustrace TO dba WITH ADMIN OPTION;
Grant succeeded.
SQL>
 
SQL> GRANT plustrace TO salapati;
Grant succeeded.
SQL>

#

SET AUTOTRACE ON EXPLAIN: This generates the execution plan only and doesn't execute the query itself.
#
SET AUTOTRACE ON STATISTICS: This shows only the execution statistics for the SQL statement.
#
SET AUTOTRACE ON: This shows both the execution plan and the SQL statement execution statistics

SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM EMP;
no rows selected
Execution Plan
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=74)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=74)
Statistics
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        511  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL>


3. Using SQL Trace and TKPROF

SQL Trace is an Oracle utility that helps you trace the execution of SQL statements.
TKPROF is another Oracle utility that helps you format the trace files output by SQL
Trace into a readable form. Although the EXPLAIN PLAN facility gives you the expected
execution plan, the SQL Trace tool gives you the actual execution results of a SQL query.
Sometimes, you may not be able to identify the exact code, say, for dynamically generated SQL.
SQL Trace files can capture the SQL for dynamic SQL.

The Top Part of the TKPROF-Formatted Trace File
Image from book

TKPROF: Release 11.1.0.6.0 - Production on Mon Apr 28 12:49:38 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Trace file: finance_ora_16340.trc
Sort options: default
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

Image from book

Each TKPROF report shows the following information for each SQL statement issued during the time the user's session was traced:

The SQL statement
Counts of parse, execute, and fetch (for SELECT statements) calls
Count of rows processed
CPU seconds used
I/O used
Library cache misses

Optional execution plan

Row-source operation listing

A report summary analyzing how many similar and distinct statements were found in the trace file

Let's analyze the formatted output created by TKPROF. Listing 19-12 shows the parts of the TKPROF output showing the parse, execute, and fetch counts.

Listing 19-12: The Parse, Execute, and Fetch Counts

SQL> select e.last_name,e.first_name,d.department_name
     from teste e,testd d
     where e.department_id=d.department_id;
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    17322  1.82   1.85   3        136          5       259806
------- ------  -------- -------- -- ---------- ---------- ----------
total    17324  1.82   1.85   3        136          5       259806

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 53

In Listing 19-12


    CPU stands for total CPU time in seconds.

    Elapsed is the total time elapsed in seconds.

    Disk denotes total physical reads.

    Query is the number of consistent buffer gets.

    Current is the number of database block gets.

    Rows is the total number of rows processed for each type of call.

From Listing 19-12, you can draw the following conclusions:

1 The SQL statement shown previously was parsed once, so a parsed version wasn't available
  in the shared pool before execution. The Parse column shows that this operation took less
  than 0.01 seconds. Note that the lack of disk I/Os and buffer gets indicates that there
  were no data dictionary cache misses during the parse operation. If the Parse column showed a
  large number for the same statement, it would be an indicator that bind variables weren't
  being used.

2 The statement was executed once and execution took less than 0.01 seconds. Again, there were no
  disk I/Os or buffer gets during the execution phase.

3 It took me a lot longer than 0.01 seconds to get the results of the SELECT statement back.
  The Fetch column answers this question of why that should be: it shows that the operation
  was performed 17,324 times and took up 1.82 seconds of CPU time.
 
4 The Fetch operation was performed 17,324 times and fetched 259,806 rows. Because the number
  of rows is far greater than the number of fetches, you can deduce that Oracle used array fetch
  operations.

5 There were three physical reads during the fetch operation. If there's a large difference
  between CPU time and elapsed time, it can be attributed to time taken up by disk reads.
  In this case, the physical I/O has a value of only 3, and it matches the insignificant gap
  between CPU time and elapsed time. The fetch required 136 buffer gets in the consistent mode
  and only 5 DB block gets.

6 The CBO was being used, because the optimizer goal is shown as CHOOSE.


SQL> ALTER SESSION SET sql_trace=true;
Session altered.
SQL>
 
or

SQL> EXECUTE sys.dbms_session.set_sql_trace(true);
PL/SQL procedure successfully completed.
SQL>

The recommended way is to use the DBMS_MONITOR package to trace a session. Regardless of the method
you use, once you start tracing a session, all statements are traced until you use the ALTER
SESSION statement or the DBMS_SESSION package to turn tracing off (replace true with false in
either of the preceding statements). Alternatively, when the user logs off, tracing is
automatically stopped for that user.

select sid,serial# from v$session
      where username = 'SH'

exec dbms_monitor.session_trace_enable  ( session_id =>145, serial_num => 4204, waits => true, binds => true);

show parameter user_dump_dest

cd $ORACLE_BASE/admin/orcl/udump

tkprof monitor.txt sys=no
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7336830/viewspace-697906/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7336830/viewspace-697906/