Database Reference
In-Depth Information
1354.48 7,087 0.19
21.2
1241.76 7,834 1481390170
Module: Swingbench User Thread
SELECT /*+ first_rows index(customers, customers_pk) index(orde
rs, order_status_ix) */ o.order_id, line_item_id, product_id, u
nit_price, quantity, order_mode, order_status, order_total, sale
s_rep_id, promotion_id, c.customer_id, cust_first_name, cust_las
648.93 36,705 0.02
10.2
20.58 70,411 3476971243
Module: Swingbench User Thread
insert into orders(ORDER_ID, ORDER_DATE, CUSTOMER_ID, WAREHOUSE_
ID) values (:1 , :2 , :3 , :4 )
If, as in the previous excerpt, a limited number of SQL statements is responsible for most of the DB time (the top 3
were responsible for more than 60% of the DB time), you have found the SQL statement that you have to focus on.
Then, based on the hash value, you can use the
sprepsql.sql
script to get all available information about the SQL
statement (refer to Chapter 10, specifically in the “Automatic Workload Repository and Statspack” section).
All other sections in the report might be useful to get more-detailed information about a specific behavior or
configuration of the system. For example, in a case like the one described in this section, where the system is disk I/O
bound, for each top event related to disk I/O operations, you should check the histograms provided in the
Wait Event
Histogram
section. Based on them, and on the knowledge of the I/O subsystem configuration, you should be able
to determine whether the disk I/O operations perform as expected. According to the following excerpt, you see that
while the log writes almost always take less than 1 millisecond, the reads are up to an order of magnitude slower.
Total ----------------- % of Waits ------------------
Event Waits
<1ms
<2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
db file sequential read 520K 2.1 2.9 19.8
49.1
18.2 4.4 3.4
log file parallel write 178K
98.7
.3 .3 .2 .3 .2 .0
On to Part 3
This chapter describes how to install, configure, and manage the two repositories that Oracle Database provides
to analyze performance problems that happened in the past: AWR and Statspack. In addition, it outlines how to go
through a Statspack report, which is very similar to an AWR report. These are the main checks you should carry out.
In general, the aim isn't to investigate performance problems, but to avoid them in the first place. Based on
my experience, there are two major causes of performance problems: not designing databases and/or applications
for performance, and poor configuration of the query optimizer. The latter is critical, because every SQL statement
executed by the database engine goes through the query optimizer. For this reason, the next part not only explains
how the query optimizer works, but also provides information on how to correctly configure it.
