To tune your SQL you need to profile/measure your sql performance (time, how many reads) and inspect the
execution plan generated by optimizer (e.g. to detect full table scan that can be avoided by creating index). Start with measuring the baseline and then try to modify your table/sql (e.g. creating index, give optimizer hints) and measure again.
There are several ways to measure your sql performace in Oracle:
1. The simplest way: click "auto trace" or "explain plan" in the SQL worksheet in JDeveloper/SQLDeveloper:
To use the autotrace you need to have permission to the dictionary view V$mystat
2. In SQLplus you can use explain plan:
EXPLAIN PLAN FOR select e.employee_id,e.last_name,j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;
Explained.
SQL> SELECT * FROM TABLE (DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 4508 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 98 | 4508 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 475 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01
|* 4 | SORT JOIN | | 98 | 2058 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 98 | 2058 | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("J"."JOB_ID"="E"."JOB_ID")
filter("J"."JOB_ID"="E"."JOB_ID")
5 - filter("SALARY"<4000>
19 rows selected.
3. In SQLplus you can use autotrace so Oracle will display the explain plan or statistics everytime you perform a query.
To display the plan: SET AUTOTRACE TRACEONLY EXPLAIN or SET AUTOTRACE ON EXPLAIN;
To display the statistics: SET AUTOTRACE TRACEONLY STATISTICS or SET AUTOTRACE ON STATISTICS;
To display the plan: SET AUTOTRACE TRACEONLY EXPLAIN or SET AUTOTRACE ON EXPLAIN;
To display the statistics: SET AUTOTRACE TRACEONLY STATISTICS or SET AUTOTRACE ON STATISTICS;
Or display both: SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN;
Interesting statistics information are included:
- recursive calls: how much works to construct query plan
- consistent gets / logical reads: number of logical read operation when Oracle constructing query plan and execute it)
- physical reads: number of disk read operations when Oracle can't find the data in the cache.
e.g.
Statistics
----------------------------------------------------------
1097 recursive calls
0 db block gets
282 consistent gets
24 physical reads
0 redo size
2434 bytes sent via SQL*Net to client
464 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
If you run the same query again, the performance is better due to caching:
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
2434 bytes sent via SQL*Net to client
464 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
64 rows processed
To get accurate measure over your query you might need to flush the cache (execution plan & data) before you perform the sql again:
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
As an experiment we can see how big the infuence of indexing to the query performance. For example try to hide an index from the optimizer:
ALTER INDEX hr.job_id_pk INVISIBLE
or using hint:
SELECT /*+ NO_INDEX (j job_id_pk) */ e.employee_id, e.last_name, j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;
SELECT /*+ NO_INDEX (j job_id_pk) */ e.employee_id, e.last_name, j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;
The performance will be worse due to table full scan:
Execution Plan
----------------------------------------------------------
Plan hash value: 1300016118
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 4508 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 98 | 4508 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| JOBS | 19 | 475 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 98 | 2058 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("J"."JOB_ID"="E"."JOB_ID")
filter("J"."JOB_ID"="E"."JOB_ID")
5 - filter("SALARY"<4000>
19 rows selected.
Statistics
----------------------------------------------------------
1240 recursive calls
2 db block gets
317 consistent gets
38 physical reads
0 redo size
2434 bytes sent via SQL*Net to client
464 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
64 rows processed
4. You can use DBMS_SYSTEM.set_sql_trace_in_session to tell Oracle to generate *.trc trace files and then use tkprof utility to summary the result. Personally I find this method unhandy except when I need the trace files.
Tips:
1. When using autotrace, Oracle might complain that you don't have PLUSTRACE role privelege.
Solution:
connect sys as sysdba, run @$ORACLE_HOME/sqlplus/admin/plustrce.sql to perform several grants. Then Grant PLUSTRACE to the user you used. Connect again as a normal user (e.g. hr) create PLAN_TABLE in the schema you use by using @$ORACLE_HOME/rdbms/admin/utlxplan
2. When your query produces big result and you're interested only to the profiling statistics, use TRACEONLY so that Oracle will not display the query result.
e.g.
SET AUTOTRACE TRACEONLY STATISTICS
SET AUTOTRACE TRACEONLY EXPLAIN
3. You can set the statistics level e.g. ALTER SESSION SET statistics_level=ALL;
Source: Steve's blogs http://soa-java.blogspot.com/
Any comments are welcome :)
References:
Beginning Oracle Database 11g Administration by Iggy Fernandez
Oracle Database 11g Release 2 Performance Tuning Tips & Techniques by Niemiec
No comments:
Post a Comment