Oracle 10g SQL Tuning with AWR - http://www.dba-oracle.com/art_orafaq_awr_sql_tuning.htm

Oracle 10g SQL Tuning with AWR

February 2005
Donald K. Burleson


--------------------------------------------------------------------------------

The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets.

The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets.

We have the following AWR tables for SQL tuning.

dba_hist_sqlstat
dba_hist_sql_summary
dba_hist_sql_workarea
dba_hist_sql_plan
dba_hist_sql_workarea_histogram
These simple tables represent a revolution in Oracle SQL tuning and we can now employ time-series techniques to optimizer SQL with better results than ever before. Let's take a closer look at these views.



dba_hist_sqlstat

This view is very similar to the v$sql view but it contains important SQL metrics for each snapshot. These include important delta (change) information on disk reads and buffer gets, as well as time-series delta information on application, I/O and concurrency wait times.

col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'SQL|ID' format a13
col c3 heading 'Executions|Delta' format 9,999
col c4 heading 'Buffer|Gets|Delta' format 9,999
col c5 heading 'Disk|Reads|Delta' format 9,999
col c6 heading 'IO Wait|Delta' format 9,999
col c7 heading 'Application|Wait|Delta' format 9,999
col c8 heading 'Concurrency|Wait|Delta' format 9,999


break on c1 skip 2
break on c2 skip 2
select
begin_interval_time c1,
sql_id c2,
executions_delta c3,
buffer_gets_delta c4,
disk_reads_delta c5,
iowait_delta c6,
apwait_delta c7,
ccwait_delta c8
from
dba_hist_sqlstat
order by
c1, c2;


The popular WISE tool is the easiest way to analyze Oracle physical disk reads and physical disk writes (see above plot of file write I/O over time), and WISE uses STATSPACK and/or AWR data to allow you to spot hidden redo log performance trends. WISE is our favorite Oracle tuning tool, and the only 3rd party tool that we use.


dba_hist_sql_plan

The dba_hist_sql_plan table contains time-series data about each object (table, index, view) involved in the query. The important columns include the cardinality, cpu_cost, io_cost and temp_space required for the object.

The query below will show the main predicates involved for each object component in a SQL execution plan:

col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'SQL|ID' format a13
col c3 heading 'Object|Name' format a20
col c4 heading 'Search Columns' format 99
col c5 heading 'Cardinality' format 99
col c6 heading 'Access|Predicates' format a80
col c6 heading 'Filter|Predicates' format a80


break on c1 skip 2
break on c2 skip 2
select
begin_interval_time c1,
sql_id c2,
object_name c3,
search_columns c4,
cardinality c5,
access_predicates c6,
filter_predicates c7
from
dba_hist_sql_plan
order by
c1, c2;
But there is lots more information in dba_hist_sql_plan that is useful. The query below will extract importing costing information for all objects involved in each query.

col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'SQL|ID' format a13
col c3 heading 'Object|Name' format a20
col c4 heading 'Search Columns' format 9,999
col c5 heading 'Cardinality' format 9,999
col c6 heading 'Disk|Reads|Delta' format 9,999
col c7 heading 'Rows|Processed' format 9,999

break on c1 skip 2
break on c2 skip 2
select
begin_interval_time c1,
sql_id c2,
object_name c3,
bytes c4,
cpu_cost c5,
io_cost c6,
temp_space c7
from
dba_hist_sql_plan
order by
c1, c2;
Now that we see the important table structures lets examine how we can get spectacular reports from this AWR data.

Viewing table and index access with AWR

One of the problems in Oracle9i was the single bit-flag that was used to monitor index usage. You could set the flag with the "alter index xxx monitoring usage" command, and see if the index was accessed by querying the v$object_usage view.

The goal of any index access is to use the most selective index for a query, the one that produces the smallest number of rows. The Oracle data dictionary is usually quite good at this, but it is up to you to define the index. Missing function-based indexes are a common source of sub-optimal SQL execution because Oracle will not use an indexed column unless the WHERE clause matches the index column exactly.

--*************************************************
-- Copyright 2004 by Rampant TechPress Inc.
-- Free for non-commercial use!
-- To license, e-mail info@rampant.cc
-- ************************************************
col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'SQL|ID' format a13
col c3 heading 'Object|Name' format a20

col c4 heading 'Search Columns' format 999,999
col c5 heading 'Cardinality' format 999,999
col c6 heading 'Disk|Reads|Delta' format 999,999
col c7 heading 'Rows|Processed' format 999,999

break on c1 skip 2
break on c2 skip 2

select
begin_interval_time c1,
sql_id c2,
object_name c3,
search_columns c4,
cardinality c5,
disk_reads_delta c6,
rows_processed_delta c7
from
dba_hist_sql_plan
natural join
dba_hist_snapshot
natural join
dba_hist_sqlstat;


You can also use the dba_hist_sql_plan table to gather counts about the frequency of participation of objects inside queries.

col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'SQL|ID' format a13
col c3 heading 'Object|Name' format a20
col c4 heading 'Object|Count' format 999,999
break on c1 skip 2
break on c2 skip 2

select
to_char(begin_interval_time,'yyyy-mm-dd HH24') c1,
sql_id c2,
object_name c3,
count(*) c4
from
dba_hist_sql_plan
natural join
dba_hist_snapshot
group by
to_char(begin_interval_time,'yyyy-mm-dd HH24'),
sql_id,
object_name ;


Here we can see the average SQL invocations for every database object, averaged by hour-of-the day or day-of-the-week. Understanding the SQL signature can be extremely useful for determining what objects to place in your KEEP pool, and to determining the most active tables and indexes in your database.

--*************************************************
-- Copyright 2004 by Rampant TechPress Inc.
-- Free for non-commercial use!
-- To license, e-mail info@rampant.cc
-- ************************************************

col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'Object|Name' format a20
col c3 heading 'Search Columns' format 999,999
col c4 heading 'Disk|Reads|Delta' format 999,999
col c5 heading 'Rows|Processed' format 999,999
col c6 heading 'Access|Predicates' format a200
col c7 heading 'Filter|Predicates' format a200

break on c1 skip 2

select
begin_interval_time c1,
object_name c2,
search_columns c3,
disk_reads_delta c4,
rows_processed_delta c5,
access_predicates c6,
filter_predicates c7
from
dba_hist_sql_plan
natural join
dba_hist_snapshot
natural join
dba_hist_sqlstat;

The new access_predicates and filter_predicates columns are very useful because we no longer need to parse-out the WHERE clause of each SQL statement to see the access and filtering criteria for the SQL statements.

Counting object usage inside SQL

In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used. Here is a simple AWR query to plot index usage:

--*************************************************
-- Copyright 2004 by Rampant TechPress Inc.
-- Free for non-commercial use!
-- To license, e-mail info@rampant.cc
-- ************************************************
col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'Search Columns' format 999,999
col c2 heading 'Invocation|Count' format a20

break on c1 skip 2

select
begin_interval_time c1,
count(*) c3
from
dba_hist_sqltext
natural join
dba_hist_snapshot
where
lower(sql_text) like lower('%cust_name_idx%')
;

This will produce an output like this, showing a summary count of all indexes used during the snapshot interval.

Conclusion

Oracle SQL tuning is constantly different. As the data changes, Oracle must be able to accommodate the changes with new execution plans. AWR now provides complete time-series SQL execution data and further research is sure to find exciting new ways to tune SQL as the data changes over time.

For more information on SQL tuning with Oracle10g AWR, see my book "Oracle Tuning". You can save over 30% by getting it directly from the publisher here:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm

Author Bio:

Donald K. Burleson has been a DBA for more than 20 years and provides Oracle consulting for system that require high performance. The author of more than 30 books, Burleson provides Oracle consulting at www.dba-oracle.com and remote Oracle support at www.remote-dba.net.

10g Subquery Unnesting Anomalies

Filed Under Oracle, Technical |

I’ve been spending quite a bit of time over the past week studying subquery optimization. It started with an anomaly I noticed at a client site and led me through a refresher on Jonathan Lewis’ chapter on transformations and through a lot of time reading trace files. I’ll probably write a few posts about subqueries… but as a quick starter here’s the anomaly that got me started with the whole thing.
First things first: my demos in this article will be from Oracle 10.2.0.2. Subquery optimization is an area of active development and there are even changes between point releases of Oracle. I saw different plans generated for some of my test cases between 10g releases 1, 2 and 3. The differences are even more dramatic between major releases - not to mention between different databases. Oracle 8i unnested nothing, 9i unnested everything, and 10g tries to work out the cost. MySQL can’t unnest and can’t use indexes on FROM clause subqueries since it always materializes them. (Just yesterday I finally listened to Timour Katchaounov’s “Query Optimizer Internals” presentation from this year’s MySQL Conference where he spent a lot of time talking about MySQL’s capabilities and limitations with subqueries.)
Oracle 10g doesn’t always get it right either, although - not surprisingly - it is far more sophisticated than MySQL in how it handles subqueries. The “anomaly” that I observed was 10g unnesting a subquery when it was clearly cheaper not to unnest it. The original query was a bit complex and the subquery existed to find the current row in an SCD type 2 table. (If you have these tables then I’d wager that you use subqueries for this same purpose and could run into this exact problem!)

Reproducing the Problem
My first thought was that 10g wasn’t actually costing the subquery and was automatically unnesting it. But in the spirit of BAAG (and since I didn’t want to jump to conclusions) I started digging a little deeper.
The client system was pretty complex so the first step was to try reproducing the problem in a simpler environment. Since Jonathan Lewis has provided test cases for query transformations - including subquery unnesting - this seemed a natural place to start. By starting with his scripts it didn’t take me long to reproduce the problem. To reproduce my environment run ch_09_transformations\unnest_cost_01a.sql from Lewis’ CBO scripts.
Here’s the bird’s eye view:
SQL> alter session set "_optimizer_cost_model"=choose;

SQL> set autotrace traceonly explain;

SQL> select *
from dept
where exists (
select 'X'
from emp outer
where outer.sal =
(select
max(inner.sal)
from emp inner
where inner.dept_no >= outer.dept_no
) and outer.dept_no=dept.dept_no
);

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | | 651 (35)| 00:00:08 |
|* 1 | FILTER | | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 6 | 30 | | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | | |
| 4 | HASH GROUP BY | | 1 | 43 | | 216 (35)| 00:00:03 |
| 5 | MERGE JOIN | | 166K| 6998K| | 158 (11)| 00:00:02 |
| 6 | SORT JOIN | | 1000 | 8000 | | 59 (7)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| EMP | 1000 | 8000 | | 58 (6)| 00:00:01 |
|* 8 | SORT JOIN | | 3333 | 113K| 360K| 92 (6)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| EMP | 3333 | 113K| | 58 (6)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> select *
from dept
where exists (
select /*+ no_unnest */ 'X'
from emp outer
where outer.sal =
(select /*+ no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no >= outer.dept_no
) and outer.dept_no=dept.dept_no
);

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 349 (5)| 00:00:05 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 6 | 30 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | EMP | 3333 | 26664 | 58 (6)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 8 | | |
|* 6 | TABLE ACCESS FULL| EMP | 1000 | 8000 | 58 (6)| 00:00:01 |
-----------------------------------------------------------------------------
Is Jonathan Lewis perpetuating a myth that Oracle 10g will unnest subqueries based on cost?!
As it turns out… of course not. So what’s going on here?
I poked and probed this query in dozens of different ways but ultimately ended up in the 10053 trace. And although I still don’t consider myself to understand it entirely it did shed enough light on this query to figure out what was going on.
About The Cost-Based Optimizer

Oracle’s main workhorse for cost-based query optimization seems to be a function called kkoqbc() which calculates the cost of a single query block. (Kernel Kompile Optimizer Query Block Cost?) As the performance tuning guide illustrates, Oracle attempts to transform the query before generating plans. Jonathan demonstrated this in his post by showing how a query was still transformed in 9i even when the rule-based optimizer was used.
The trace file also demonstrates that transformations happen before optimization - I could always see the CBO always following the same sequence of events:
1. Predicate Move-Around (PM)
2. Pre-transform Simple Filter Push-Down
3. Subquery Unnesting (automatic and cost-based)
4. Complex View Merging
5. Set-Join Conversion
6. Predicate Move-Around
7. Predicate Pull-Up
8. Join Predicate Push-Down
9. Post-Transform Simple Filter Push-Down
10. Generate Transitive Predicates (check constraints and other parts of query)
11. Generate Plans [i.e. call kkoqbc()]
You could think of these as the “rules” for the cost-based optimizer. :) Each of the first 10 steps can transform the query in one way or another before Oracle even attempts to generate a plan!
The most interesting bit, though, is what happens when there’s a subquery that’s eligible for unnesting. You still see the same sequence of events - except that in the middle of steps 3 and 4 (subquery unnesting and complex view merging) you see calls to kkoqbc() and the corresponding trace data. You’re watching cost-based query transformations in action.
Back to the Original Problem
So what did I discover when looking at the tracefiles for the query above? This is what’s interesting… for some reason (which I haven’t fully figured out yet) kkoqbc() was generating a different cost for the untransformed subquery when called during the unnest phase. The cost of the transformed subquery after unnesting and view merging came out to 231. The cost of the untransformed subquery came out to 403 when calculated during the unnest phase - and 115 when calculated at the end (using hints to prevent unnesting).
In the case of my client a few weeks ago this unnest operation was absolutely killing query performance. Perhaps I’ll find a more elegant solution as I continue digging into subquery optimization but in the meantime the quick fix was simply using the NO_UNNEST hint to prevent subquery unnesting as I demonstrated above.
As I mentioned before, if you have SCD type 2 data - and everyone running Oracle Applications or Peoplesoft has this - then look out since you probably use nested subqueries to get current records. Unnesting is usually good - but occasionally Oracle gets it wrong. If you have a query that’s running slow then it might be worth just trying a few NO_UNNEST or UNNEST hints and seeing if it makes a difference.
Footnote: If anyone’s especially curious, you’re welcome to have a look at the trace files for yourself - with subquery unnesting and without subquery unnesting via hint. Of course you should also be able to reproduce the same results on 10.2.0.2 by using Lewis’ scripts for setup and running the my queries from this article.

[ db file sequential read vs db file scattered read ]

타락천사 http://blog.naver.com/darkturtle/50014865473
[ db file sequential read vs db file scattered read ]
- 원문 : Why are Oracle’s Read Events “Named Backwards”?
- 출처 : www.hotsos.com
The event names db file sequential read and db file scattered read describe how blocks are stored in memory, not how they are read from disk.
The two Oracle read event names are analogous to two classes of UNIX read calls,
Represented by the functions read() and readv(). The UNIX read() function reads a contiguous portions read and stores it contiguously in a single memory area.
The readv() function reads a contiguous portion of a file and stores pieces of that data in
Different areas of memory dictated by an array of memory references.
Oracle disk-reads executed through calls like read() are recorded as db file sequential read events, and disk-reads executed through calls like readv() are recored as db file scattered read events

A db file sequential read occurs when the memory receiving the contents of a disk read is contiguours.
A db file scattered read event occurs when the memory receving the contents of a disk
Read is not guarateed to be contiguous.




The following table describe cirsumstances that generate Oracle’s db file sequential read and db file scattered read events.
Oracle Event
Operations that generate
Single-block read events
(p3 =1 )
Operations that generate
Multi-block read events
( p3 > 1 )
Db file sequential read
Index Scans,
Full-table sacns that skip
Blocks within an extent,
Table access by rowed
Temporary segment reads
Db file scattered read
Never
Full-table scans,
Fast full-Index scans…
v$session p1 : file#, p2:Starting Block#, p3:블록수

!! db file sequential read event 의 경우 disk 의 block 을 Data buffer 내에
연속되게 올린다(load)는 의미에서 그 이름이 유래 되었다.
– 주로 index scan,
하나의 extent 내의 table scan 의 경우도 ( block 이 하나일 경우)
반대로 db file scattered read 의 경우 disk 의 block 을 data buffer 내에
불연속적으로 올린다.
- 주로 Full table sacn, 상대적으로 full table scan 의 경우 data buffer 로
Loading 할 data 량이 많기 때문에 연속된 data buffer cache 를 찾는 것이
비효율적이라 scatter(마구 뿌려서) 하게 올린다는 의미이다.

RAID 1-6 레벨 이해하기

http://blog.naver.com/jjaemani/50008507476