Turbo充电Oracle数据库11G性能

\n
\nFirst lets cover off some of the symptoms of this problem and give you some background before I give you the parameter we used to address the performance issue. Also note that this had nothing to do with the underlying infrastructure, it was purely a database level setting. This problem wouldn\u2019t happen in Oracle 12c.
\n
\nThe first sign of trouble was that data loads were taking a very long time and that storage latency appeared to be very high. Then there was an entry in the AWR reports saying that 76% of database wait time was caused by log file switch (checkpoint incomplete). The checkpoints themselves were taking over 90 seconds to complete. During which time the database effectively paused and didn\u2019t process any transactions, as it was out of online redo log file space until the checkpoint was completed. During the data load and performance tests we noticed that there was only one or two outstanding IO\u2019s going to the database disks.
\n
\nSo what caused this problem? Even though the right libraries were installed, the database was never configured to use async or direct IO. I suspected this based on the very low number of outstanding IO\u2019s and low amount of IO concurrency. To confirm this suspicion we ran SHOW PARAMETER FILESYSTEMIO_OPTIONS from sqlplus. The output was as follows:
\nNAME TYPE VALUE------------------------------------ ----------- ------------------------------filesystemio_options string noneFortunately the answer to this is fairly straight forward. Use the command ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE; followed by shutting down and restarting the instance (you\u2019d plan this for a maintenance period for production databases).
\nAfter making this change the IO latency significantly decreased, the IOPS increased, the IO concurrency increased and the performance was about 10X better than before. The customer was very happy and at the same time we got to show how excellent our support is. This was a team effort to address this problem and once the customer got in touch with us it didn't take us very long at all to fix it.
\n
\nFinal Word<\/b>Although this simple change is required for Oracle 11g it is not required for 12c. Oracle Database 12c will use async or direct IO if it\u2019s available in your operating system automatically.
\n
\nThis was originally posted to longwhiteclouds.com and is reproduced here with permission. The original article can be found at http:\/\/longwhiteclouds.com\/2014\/11\/21\/turbo-charge-oracle-database-io-performance\/.","quoteUsername":"vcdxnz001","translations":{"Common":{"like":"Like","unlike":"Unlike"},"Forum":{"Quote":"Quote","Share":"Share"}}}">

• 喜欢
• 引用
• 分享