Help - Search - Members - Calendar
Full Version: DBWRITER
Oracle DBA Forums > Oracle > Oracle Forum
Alex Alvarenga
Hi !!

We have a DSS database with many performance issues...

db_cache_size 5gb
dbwriter = 1

Usually occurs waits for db files... sometimes 6 msecs in average.
The storage uses fast disks (SAN), fibre channel, RAID 0+1, raw device and asyncronous I/O.
CPU is 70% idle.

Could be interesting to increase dbwriter processes ?


Alex Alvarenga.
what are your actual wait events

wait for db files isnt one
Hi Alex, and welcome to th forum!

To understand DRAWR tuning, see here:

Also, Steve Adams notes:

Free buffer waits are evidence of a DBWR problem, because it is DBWR’s responsibility to make free buffers available by writing modified blocks to the datafiles.

  event like 'db file %' or
  event = 'free buffer waits' or
  event = 'write complete waits'
order by
  time_waited desc

>> Usually occurs waits for db files... sometimes 6 msecs in average.

Sequential or scattered read waits?

Can you create a STATSPACK or AWR report?

>> The storage uses fast disks (SAN), fibre channel, RAID 0+1, raw device and asyncronous I/O.

It could be the stripe size, or other "hidden" disk contention, it's hard to tell without the report, which will show I/O wait times by data file!

Once you create the STATSPACK or AWR report, go here and paste it in, for detailed analysis:
Alex Alvarenga
Burleson / Dave,

Thanks for your replies !

This is the result of script about wait files !

EVENT                                                            TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read                                             56669041    41758511            1
db file scattered read                                               1184938     3991138            3
db file parallel write                                               4691663      955005            0
db file parallel read                                                   3996       14449            4
db file single write                                                    4196         284            0

** So, the main wait is "seq read".

SQL result from your script.

NAME                                                                                  VALUE
-------------------------------------------------------------------------------- ----------
DBWR buffers scanned                                                               16102406
DBWR checkpoint buffers written                                                    27470264
DBWR checkpoints                                                                       2527
DBWR cross instance writes                                                                0
DBWR free buffers found                                                            14484281
DBWR fusion writes                                                                        0
DBWR lru scans                                                                       108625
DBWR make free requests                                                              192191
DBWR revisited being-written buffer                                                  853722
DBWR summed scan depth                                                             16102406
DBWR transaction table writes                                                         19514
DBWR undo block writes                                                             15487462
DEFAULT 16K buffer busy wait                                                       10056860
DEFAULT 16K free buffer wait                                                              0
DEFAULT 16K hit ratio                                                                98,756
DEFAULT 16K write complete                                                                0

buffer is not pinned count                                                       4125520576
buffer is pinned count                                                           3943371546
change write time                                                                    484334
commit cleanout failures: buffer being written                                        31080
commit cleanout failures: write disabled                                                  0
data block                                                                          9481100
dirty buffers inspected                                                              682823
free buffer inspected                                                                770689
free buffer requested                                                             108659306
hot buffers moved to head of LRU                                                   37148953
no buffer to keep pinned count                                                            1
physical writes                                                                    33429533
physical writes direct                                                              3640714
physical writes direct (lob)                                                              0
physical writes non checkpoint                                                     29560714
pinned buffers inspected                                                              78313
summed dirty queue length                                                           6867779
switch current to new buffer                                                        5922380
write clones created in background                                                      436
write clones created in foreground                                                    98842

** I uploaded 2 files :
- stats pack report
- db file waits.


Alex Alvarenga.
Hi Alex,

Why didn;t you paste the report into, like we asked?

Here, I did it for you:

Your main wait event is "PX Deq Credit: send blkd ", half your wait time:

Why are you using parallel query?

What is your cpu_count?

Have you tried turning-off OPQ?

Also, make sure that you have not hit this possible bug with 16k blocksizes:


Database Info Wed Mar 11 09:25:21 EDT 2009
DB ID Instance Release RAC Host
4122479424 dw01  NO  bre25knet11z  
Elapsed: 1,439.98 (min) 86,398.8 (sec)
DB Time: 11,332.22 (min) 679,933.27 (sec)

Cache: 2,560 MB
Block Size: 16,384 bytes
Transactions: 55.95 per second

Performance Summary
Physical Reads: 1,956/sec   MB per second: 30.56 MB/sec  
Physical Writes: 394/sec   MB per second: 6.16 MB/sec  
Single-block Reads: 193.54/sec   Avg wait: 5.85 ms  
Multi-block Reads: 2.38/sec   Avg wait: 14.52 ms  
Tablespace Reads: 261/sec   Writes: 301/sec  

Top 5 Events
Event Percentage of Total Timed Events
PX Deq Credit: send blkd  52.45 %
db file sequential read  14.39 %
direct path read  11.60 %
PX Deq: Execute Reply  5.65 %
library cache lock  3.61 %

Top 5 Events
Event Percentage of Total Timed Events
PX Deq Credit: send blkd  52.45 %
db file sequential read  14.39 %

The sequential read event occurs when Oracle reads single blocks of a table or index. Look at the tablespace IO section of the report for tablespaces with less than 2 average blocks per read, high response time, and a large percentage of the total IO. Improving the response time of these tables with faster storage will help reduce this wait event and speed up the database. Moving the data files with the largest amount of time spend waiting on single-block reads to faster storage can significantly reduce the amount of time spent waiting on this event. By reducing the time spent waiting on this event, the database performance could increase 17%.

direct path read  11.60 %

Reads that skip the database buffer cause this wait event. If there are many sorts and hashes taking place, then slow access to the TEMP space can cause this. Check the top IO tablespaces below. If the list includes the TEMP space, then there is a good chance that moving the TEMP space to solid state disks can help reduce this event.

PX Deq: Execute Reply  5.65 %
library cache lock  3.61 %

Tablespace I/O Stats
Tablespace Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO
UNDOTBS1  6 6.4 1 139 4% 25.7%
DW_IDG1  47 7.9 1.6 83 36% 23.11%
DW_TBG1  57 3.1 14.2 16 79% 12.87%
DW_IDM2  15 7.7 2.1 28 35% 7.78%
DW_TBM2  37 2.7 13.6 5 89% 7.42%
SORT  15 0 6.4 14 51% 5.05%
DW_TBM4  24 3.6 6.6 4 87% 4.97%
DW_TBM1  18 4.3 7.8 2 89% 3.51%
DW_TBM3  12 5.1 5.9 1 89% 2.31%
DW_IDM1  9 5.7 1.6 2 79% 2.04%
DW_IDM3  8 5 2.1 2 77% 1.94%
DW_IDM4  6 5.8 2.9 2 72% 1.39%
DW_TBP1  4 5.1 3.8 1 74% 0.9%
DW_IDP1  2 5.8 1 2 49% 0.79%

Tablespace I/O Stats
Tablespace Wait (s) Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO
UNDOTBS1  80,196 6 6.4 1 139 4% 25.7%
DW_IDG1  89,027 47 7.9 1.6 83 36% 23.11%
DW_TBG1  19,446 57 3.1 14.2 16 79% 12.87%
DW_IDM2  29,199 15 7.7 2.1 28 35% 7.78%
DW_TBM2  9,764 37 2.7 13.6 5 89% 7.42%
SORT  0 15 0 6.4 14 51% 5.05%
DW_TBM4  8,724 24 3.6 6.6 4 87% 4.97%
DW_TBM1  7,354 18 4.3 7.8 2 89% 3.51%
DW_TBM3  5,751 12 5.1 5.9 1 89% 2.31%
DW_IDM1  5,667 9 5.7 1.6 2 79% 2.04%
DW_IDM3  4,730 8 5 2.1 2 77% 1.94%
DW_IDM4  3,924 6 5.8 2.9 2 72% 1.39%
DW_TBP1  2,242 4 5.1 3.8 1 74% 0.9%
DW_IDP1  2,240 2 5.8 1 2 49% 0.79%

Load Profile
Logical reads: 24,362/s   Parses: 327.14/s  
Physical reads: 1,956/s   Hard parses: 0.06/s  
Physical writes: 394/s   Transactions: 55.95/s  
Rollback per transaction: 0%   Buffer Nowait: 99.97%  

4 Recommendations:
You are performing more than 1,956 disk reads per second. High disk latency can be caused by too-few physical disk spindles. Compare your read times across multiple datafiles to see which datafiles are slower than others. Disk read times may be improved if contention is reduced on the datafile, even though read times may be high due to the file residing on a slow disk. You should identify whether the SQL accessing the file can be tuned, as well as the underlying characteristics of the hardware devices.
Check your average disk read speed later in this report and ensure that it is under 7ms. Assuming that the SQL is optimized, the only remaining solutions are the addition of RAM for the data buffers or a switch to solid state disks. Give careful consideration these tablespaces with high read I/O: DW_TBG1 , DW_IDG1 , DW_TBM2 , DW_TBM4 , DW_TBM1 .
You are performing more than 394 disk writes per second. Check to ensure that you are using direct I/O (non buffer) on your database and perform an IO balance and an IO timing analysis to determine if a disk or array is undergoing IO related stress, and verify your DBWR optimization. Check you average disk read speed later in this report and ensure that it is under 7 milliseconds. If not, consider SSD for faster physical reads.
Long I/O times are occuring when using the following tablespaces: DW_IDG1 , DW_IDM2 . Consider placing these tablespace´s datafiles on solid state disk for faster disk access.

Instance Efficiency
Buffer Hit: 99.06%   In-memory Sort: 99.99%  
Library Hit: 100%   Latch Hit: 99.34%  
Memory Usage: 92.92%   Memory for SQL: 93.04%  

2 Recommendations:
Your shared pool maybe filled with non-reusable SQL with 92.92% memory usage. The Oracle shared pool contains Oracle´s library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. You can check the dba_hist_librarycache table in Oracle10g to see your historical library cache RAM usage.
Your Shared Pool Memory Usage is 92.92% and "% Memory for SQL w/exec>1" is 93.04%. Your shared pool size might need increasing by adjusting your shared_pool_size parameter. If server memory is available, allocate more RAM to the shared pool.

SQL Statistics
Click here to see all SQL data

Wait Events
Event Waits Wait Time (s) Avg Wait (ms) Waits/txn
PX Deq Credit: send blkd  15,807,803 356,625 23 3.3  
db file sequential read  16,721,963 97,800 6 3.5  
direct path read  5,722,331 78,882 14 1.2  
PX Deq: Execute Reply  763,793 38,406 50 0.2  
library cache lock  8,435 24,547 2910 0.0  
log file sequential read  181,771 13,964 77 0.0  
log file parallel write  16,013,506 13,415 1 3.3  
sbtwrite2  715,739 12,154 17 0.1  
log file sync  7,575,486 10,989 1 1.6  
SQL*Net break/reset to clien 1,154,183 9,731 8 0.2  

2 Recommendations:
Your average wait time for db file sequential read is 6 milliseconds, which is very slow. The sequential read event occurs when Oracle reads single blocks of a table or index. Index reads or single block reads typically cause this event. In addition, check to ensure that you are using non-buffered direct I/O. If these steps fail to reduce your disk wait times, moving some of your datafiles to solid state disk will reduce the amount of time spent waiting for this event.
You have a high value for log file sync waits at 1.6 per transaction. Check to ensure that your application does frequent commits and consider moving your redo log files to faster SSD disk. Also consider increasing your log_buffer size.

Instance Activity Stats
Statistic Total per Second per Trans
SQL*Net roundtrips to/from client 1,490,645 17.3 0.3  
consistent gets  857,218,332 9,921.6 177.3  
consistent gets - examination  454,398,040 5,259.3 94.0  
db block changes  893,320,176 10,339.5 184.8  
execute count  113,549,372 1,314.2 23.5  
parse count (hard)  5,393 0.1 0.0  
parse count (total)  28,264,974 327.1 5.9  
physical reads  169,029,129 1,956.4 35.0  
physical reads direct  149,262,668 1,727.6 30.9  
physical writes  34,123,403 395.0 7.1  
physical writes direct  9,167,929 106.1 1.9  
redo writes  8,007,310 92.7 1.7  
sorts (disk)  239 0.0 0.0  
sorts (memory)  2,372,491 27.5 0.5  
table fetch continued row  1,101,219 12.8 0.2  
table scans (long tables)  489,903 5.7 0.1  
table scans (short tables)  1,832,147 21.2 0.4  
workarea executions - onepass  678 0.0 0.0  

9 Recommendations:
You have 5,259.3 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce disk reads, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.
You have high update activity with 10,339.5 db block changes per second. The DB block changes are a rough indication of total database work. This statistic indicates (on a per-transaction level) the rate at which buffers are being dirtied and you may want to optimize your database writer (DBWR) process. You can determine which sessions and SQL statements have the highest db block changes by querying the v$session and v$sessatst views.
You have high disk reads with 1,956.4 per second. Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage. You can monitor your physical disk reads by hour of the day using AWR to see when the database has the highest disk activity.
You have high disk write activity with 395.0 per second. You should drill-down and identify the sessions that are performing the disk writes as they can cause locking contention within Oracle. Also investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process. In addition, you can dramatically improve your disk write speed by up to 300 times by moving the high-write datafiles to solid-state disk.
You have 239 disk sorts during this period. Disk sorts are very expensive and increasing your PGA (sort_area_size or pga_aggregate_target) may allow you to perform these sorts in RAM.
You have 1,101,219 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.
You have 5.7 long table full-table scans per second. This might indicate missing indexes, and you can run plan9i.sql to identify the specific tables and investigate the SQL to see if an index scan might result in faster execution. If your large table full table scans are legitimate, look at optimizing your db_file_multiblock_read_count parameter.
You have high small table full-table scans, at 21.2 per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD will significantly increase the speed of small-table full-table scans.
You have excessive onepass PGA workarea executions with 678 non-optimal executions during this elapsed period. It?s better to have "workarea executions - optimal", and you might consider optimizing your pga_aggregate_target parameter.

Latch Activity
Latch Get Requests % Get Miss % NoWait Miss Wait Time (s)
library cache  502,202,027 1.1 0.6  3
library cache pin  373,619,032 0.2  1
parallel query alloc buf 10,185,890 2.5  1
parallel query stats  644,953 29.2  4
query server process  1,121 2.0 0.1  1
redo allocation  465,407,333 1.3  6
session allocation  15,394,710 8.5  70
shared pool  75,215,419 0.3  1

2 Recommendations:
You have high library cache waits with 1.1% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.
You have high redo allocation misses with 1.3% get miss. The redo allocation latch controls the allocation of space for redo entries in the redo log buffer (as defined by the log_buffer parameter). The redo allocation latch is a serialization latch that enforces the sequence of entries in the log buffer. A process can only write a redo entry after the redo allocation latch, which is why it´s a will-to-wait latch. Investigate increasing your log_buffer parameter.

Buffer Pool Advisory
Current: 3,098,791,023,000 disk reads  
Optimized: 2,609,977,959,000 disk reads  
Improvement: 15.77% fewer  
The Oracle buffer cache advisory utility indicates 3,098,791,023,000 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 2,609,977,959,000, a 15.77% decrease.

PGA Memory Advisory
Current: 74% cache hit  
Optimized: 88% cache hit  
Improvement: 14% more  
The PGA advisory utility indicates 74% PGA cache hit ratio during the sample interval. Oracle estimates that doubling the PGA size (by increasing pga_aggregate_target) will increase the PGA cache hit ratio to 88%, a 14% increase.

Init.ora Parameters  
Parameter Value  
db_block_size 16KB  
db_cache_size 2.5GB  
db_file_multiblock_read_count 32    
hash_join_enabled true    
log_archive_start true    
log_parallelism 1    
optimizer_index_cost_adj 20    
pga_aggregate_target 800MB  
query_rewrite_enabled false    
shared_pool_size 1.11GB  
_optimizer_cost_model io  
session_cached_cursors 50  
cursor_sharing exact  

4 Recommendations:

You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts.

Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.

You have not enabled Materialized Views and Function-based indexes, which are very powerful features that require you to set query_rewrite_integrity and query_rewrite_enabled.

Your shared pool is set at 1.11GB, which is an unusually large value. Allocating excessive shared pool resource can adversely impact Oracle performance. For further details, see the shared pool advisory.

Oh, your report should be a close interval, less than 15 minutes between shapshots.

Your report is too broad to be meaningful.

However, you have 2,000 disk reads per second, excessive by any standard.

And, almost 400 disk writes per second.

Have you checked iostat for disk enqueues?

Do you have your working set cached?

Please read:

You are going to need some expert support here, this isn not a tricial issue.

I would considered having a tuning expert do a full health check:
Alex Alvarenga
[quote name='burleson' date='Mar 11 2009, 08:31 AM' post='49106']
Hi Alex,

>>>> Why didn;t you paste the report into, like we asked?
Sorry, the next time I'll do !

>>>> Your main wait event is "PX Deq Credit: send blkd ", half your wait time:
>>>> Why are you using parallel query?
Yes, Half of time !
We are using Parallel Query because :
- is a Data Warehouse, and there are heavy loads, heavy queries accessing large tables with partitioning.
- we have idle resources, cpu is 40% Idle.

>>>> What is your cpu_count?
cpu_count = 8
parallel threads by CPU = 2

>>>> Have you tried turning-off OPQ?
Yes, we did !
Sometimes works and is better !
But sometimes not !
Depends of query and tables !!
** But I verified diferences between degree of tables and its indexes !!

>>>> Also, make sure that you have not hit this possible bug with 16k blocksizes:
I already read this document, and it is very interesting !
I'll create the 32k tablespace and put large tables that receive frequent full scan on it.
And another 32k tbs for its indexes.
Did you see possible problems using it ?

Other informations :
** There are fragmented tables.
** I'll verify iostat and stripe size.

Thanks again Donald !!

Alex Alvarenga.
Alex Alvarenga
>>>However, you have 2,000 disk reads per second, excessive by any standard and almost 400 disk writes per second.
>>>Have you checked iostat for disk enqueues?
I'll do !! thanks for tip !

>>>Do you have your working set cached?
We have 32gbytes of RAM, but the instance had only 2.5 gb (however hitting 97% of cache)
Last week the team increased plus 2.5gb (total 5 gb), and performance improved !

But, all objects shares default pool !
In the next step I want implement keep pool for small and frequently accessed tables.
What do you think ?

Thanks a lot !!

Alex Alvarenga.
Hi Alex,

>> OPQ - Depends of query and tables !!

Aha! OK, then just selectively implement OPQ on a query-by-query basis using the "parallel" hint!

It would be interesting to see what your top-5 timed events look like without OPQ . . .

>> In the next step I want implement keep pool for small and frequently accessed tables.

You can, but I would just add it to the default buffer first.

Then, take 10 minute snaps, and get elapsed time reports.

Assuming that your SQL is optimized, the only way to reduce disk I/O waits are to add RAM to the data buffers, or use SSD . . .

Has all of your SQL been tuned?

Have you verified that all of your large-table full-table scans are legitimate, and that you have no "missing" indexes?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2016 Invision Power Services, Inc.