The database is slower sometimes and can
not handle the application timely.
Some business application data was queued.
Did not find any exception from alert log
or even trace files.
Generated the AWR report and find the top
events are as below.
Top User Events
Event
|
Event Class
|
% Activity
|
Avg Active Sessions
|
enq: TX - index contention
|
Concurrency
|
43.67
|
5.10
|
gc buffer busy
|
Cluster
|
21.97
|
2.56
|
enq: TX - contention
|
Other
|
17.16
|
2.00
|
CPU + Wait for CPU
|
CPU
|
4.43
|
0.52
|
log file sync
|
Commit
|
3.14
|
0.37
|
Top SQL Statements
SQL ID
|
Planhash
|
% Activity
|
Event
|
% Event
|
SQL Text
|
dcbst5pv6w8b9
|
|
87.51
|
enq: TX - index contention
|
43.07
|
INSERT INTO METRIC_DATA (DATET...
|
|
|
87.51
|
gc buffer busy
|
20.18
|
INSERT INTO METRIC_DATA (DATET...
|
|
|
87.51
|
enq: TX - contention
|
17.16
|
INSERT INTO METRIC_DATA (DATET...
|
|
174834596
|
2.38
|
gc buffer busy
|
1.78
|
INSERT INTO METRIC_DATA (DATET...
|
|
|
|
|
|
|
The report shows that there are too many
insert operation causing the index contention.
In this particular situation, I have heard
that reverse key indexes distribute values across an index, and are
particularly useful in RAC because if the index is on a sequence and lots of
inserts are happening, the RAC instances will be accessing the index leaf block
at the right most side then when split needed, the situation will be worse and
therefore by using a reverse key index, we can stop this contention.
The reverse key index will reduce the
contention at the index block level, which can be the more serious contention
issue as more rows were involved with a single index block.
And also If we have multiple inserts
happening in the table where the PK is a sequence that is incremented by one
(and nothing else is happening on that block), then the number of inserts that
can happen at any one time is determined by MAXTRANS (and let us assume the
block has enough space for the ITL to grow). This is true for both the index
data block and table data block, and the transactions doing the insert will be
getting a row exclusive table lock.
These locks caused the database performance
worse.
On the other side the pctfree default value
is 10 for the index and table.
If we can increase it the contention will
be reduced when the too many DML happens
But anyway to rebuild the index or increase the pctfree parameter need much
more effort than you expect.
Also to avoid this we can point
the application to a certain instance.Through this way we an avoid the
cache fusion tranfer through the interconnect.
In order to find more I decide to generate
the ADDM report to do further investigation on this.
It give me more hints on the issue.
Extract the following from the reports.
FINDING 1: 85% impact (119271 seconds)
--------------------------------------
Database writers (DBWR) were unable to keep
up with the demand for free
buffers.
RECOMMENDATION 1: DB Configuration, 85% benefit (119271 seconds)
ACTION: Consider increasing the number of database writers (DBWR) by
setting the parameter "db_writer_processes". Also consider if
asynchronous I/O is appropriate for your architecture.
RATIONALE: The value of parameter "db_writer_processes" was
"4" during
the analysis period.
RATIONALE: The value of parameter "disk_asynch_io" was
"TRUE" during the
analysis period.
RECOMMENDATION 2: Host Configuration, 85% benefit (119271 seconds)
ACTION: Investigate the I/O subsystem's write performance.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 428 K per second for reads and 149 K per second for
writes. The average response time for single block reads was 1.9
milliseconds.
RECOMMENDATION 3: Application Analysis, 85% benefit (119271 seconds)
ACTION: Investigate application logic for possible use of direct path
inserts as an alternative for multiple INSERT operations.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Configuration" was consuming significant
database
time. (85% impact [119272
seconds])
the database buffer in the database is 10G
.
Obviously the 4 db writers can not keep up
with the database change so that it is hard to find the free database buffer.
Hence the database performance is worse
when waiting for the database buffer free.
Per the report. 85% benefit (119271
seconds) would gain if modification the db writer parameter or consider if asynchronous
I/O
Also the index contention issue can be relieved
through the adding more db writers.
I found the performance back and
enq: TX - index contention waits events disappear from the top list after
db writers parameter modification.
Of course,The pctfree modification or index rebuild also gain for the index contention.
But the cause and effect is not one to one relationship always.
We can solve an issue from other perspective if focusing on the issue itself can not help.
Ref:
Consider
Multiple Database Writer (DBWR)Processes
or I/O
Slaves
Configuring multiple database writer processes, or using I/O slaves, is useful
when the transaction rates are high or when the buffer cache size is so large
that a single DBWn process cannot keep up with the load.
DB_WRITER_PROCESSES
The DB_WRITER_PROCESSES initialization parameter lets you configure multiple
database writer processes (from DBW0 to DBW9 and from DBWa to DBWj).
Configuring multiple DBWR processes distributes the work required to identify
buffers to be written, and it also distributes the I/O load over these
processes. Multiple db writer processes are highly recommended for systems with
multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor
groups (at least as many db writers as processor groups).
DBWR_IO_SLAVES
If it is not practical to use multiple DBWR processes, then Oracle provides a
facility whereby the I/O load can be distributed over multiple slave processes.
The DBWR process is the only process that scans the buffer cache LRU list for
blocks to be written out. However, the I/O for those blocks is performed by the
I/O slaves. The number of I/O slaves is determined by the parameter
DBWR_IO_SLAVES.
DBWR_IO_SLAVES
is intended for scenarios where you cannot use multiple DB_WRITER_PROCESSES
(for example, where you have a single CPU). I/O slaves are also useful when
asynchronous I/O is not available, because the multiple I/O slaves simulate
nonblocking, asynchronous requests by freeing DBWR to continue identifying
blocks in the cache to be written. Asynchronous I/O at the operating system
level, if you have it, is generally preferred.
分享到:
相关推荐
to address the process of deploying a PostgreSQL database on the cloud, we include a detailed architecture of PostgreSQL in one of the chapters. The architecture of PostgreSQL should help you ...
HD Tune 硬盘测试软件,HD Tune Pro是一款小巧易用的硬盘工具软件,其主要功能有硬盘传输速率检测,健康状态检测,温度检测及磁盘表面扫描等。另外,还能检测出硬盘的固件版本、序列号、容量、缓存大小以及当前的...
硬盘检测HDTune3.5专业版.rar
hd tune pro怎么用.docx
款Antares公司出品的多平台的修正人声或者独奏乐器声调的插件.Auto-Tune不会改变任何原始演奏的音质,唯一不同的是音高的改变.经过音高修正,你很难辨别一个声音是否被处理过,听到的将会是一个绝对完美的声音。
硬盘检测修复工具HD.Tune.Pro.V.4.50.汉化专业版
hdtune2.53简体中文绿色版,免安装,解压后直接运行“HDTune.exe”就可以使用了。基准测试能够测试硬盘的最大和最小传输速度,寻道时间和突发传输速度及CPU使用率。参数信息能够显示硬盘的特性,如SMART、48位寻址,...
cool edit插件,用于电音和高音修正
HDTunePro_5.60
HDTune_XP85硬盘检测工具
HD Tune Pro v5.75是一个硬盘检测坏道工具,有32位或64位,支持Windows xp win7 win8 win10都可以,选择对应的版本号 双击就可以检测了。
HD Tune pro是一款硬盘性能诊断测试工具。HD Tune pro能检测出硬盘和其他存储设备(如存储卡, USB sticks, iPods 等等)的传输率、突发数据传输率、数据存取时间、CPU 使用率、健康状况 (SMART),温度及扫描磁盘表面等...
Bert预训练模型fine-tune计算文本相似度 运行 ./sentence_similarity_Bert/examples/run_classifier_modify2 进行fine-tune 训练数据集为蚂蚁金服文本匹配的数据 在chinese_data文件夹内 运行run_classifier_...
Gran Vals org.wma Nokia tune 2011(Bella N9).mp3 Nokia Tune 2013.mp3 nokia tune all.mp3 Nokia tune remix(2011获奖版).mp3 Nokia tune_1994.wav Nokia tune_2005.wav Nokia tune_2008.wav
HD.Tune.Pro.v3.5 最终版
磁盘检测 维护 hdtune 一款小巧易用的硬盘工具软件,其主要功能有硬盘传输速率检测,健康状态检测,温度检测及磁盘表面扫描等。
硬盘检测工具!!!!!!!!!!!!!!!!!!!!
A-Tune是一款基于AI的操作系统性能调优引擎。A-Tune利用AI技术,使操作系统“懂”业务,简化IT系统调优工作的同时,让应用程序发挥出色性能。
How to Tune the Parameters of Algorithms Running on Hardware.zip
资源来自pypi官网。 资源全名:finetune-0.4.1.tar.gz