This content has been marked as final. Show 4 replies
1. Check the top 5 wait events.1 person found this helpful
2. Instance efficiency percentage.
3. Top SQL (by reads and execution). Try to optimize.
Hope this helps.
I have not yet looked at the first link that you provided, but did take a quick look at the second link that you provided. Considering that the OP mentioned that this is an interview question, let's take this a step further to help the OP with Statspack analysis. Below I have provided several quotes from the second link you provided. To help the OP, maybe members of this forum will offer suggestions about what is right or wrong with the following quotes (I tried to provide only as much information as absolutely necessary so that the context of the quotes is not lost). If I was the interviewer, and someone made the following statements, I would want to know why and how:
"The database has a potentially serious IO bottleneck... moving the main data tables and indexes to higher performance storage, such as solid state disk."
"This Oracle system has a serious read load, performing 1,762 reads/second... Reduce the response time for these tables to less than 1 millisecond by moving them to solid state disks."
"The sequential read event occurs when Oracle reads single blocks of a table or index... Moving your indexes to solid state disks can reduce the amount of time spent waiting for this event."
"CPU time is the amount of time that the Oracle database spent processing SQL statements, parsing statements, or managing the buffer cache. Solid state disks help to increase the CPU time by reducing IO related wait events."
"Logical Reads includes data block reads from both memory and disk."
"Check you 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."
"You are performing more than 1,098 SQL parses per second. A parse is the process of executing your SQL, checking for proper security authorization, checks for the existence of tables, columns, and other referenced objects, and generating an execution plan. Your high parses suggest that your system has many incoming unique SQL statements or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables)."
"You may have an application issue causing excessive rollbacks with 13.01% rollbacks per transaction. Due to Oracles assumption of a commit, the Rollback process is very expensive and should only be used when necessary."
"Using super-fast SSD will also reduce buffer busy waits because transactions are completed many times faster."
"You have high network activity with 3,135 SQL*Net roundtrips to/from client per second, which is a high amount of traffic."
"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 disk reads with 1,762 per second. Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage."
"You have 11,362,529 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and ..."
"You have high small table full-table scans, at 17 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."
"The Oracle buffer cache advisory utility indicates 79,007,914 disk reads during the sample interval." - at 1,762 per second, how long was the AWR report collection period?
"You are using the default optimizer mode and you may consider setting it to first_rows or first_rows_n if you have an online transaction processing system."
"You have the default value for db_file_multiblock_read_count at 16. The CBO uses this parameter to determine the cost of a full-table scan. The default value is sometimes too large, and you can run scripts to determine the optimal setting. If full-table scans are unavoidable, you may consider placing those tables on SSD."
"You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes."
"Your shared pool is set at 402MB, which is an unusually large value."
"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."
"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."
Several of the reasons why the above quotes have been selected may be found by searching this forum.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
#1 is "why are you staring at AWR report?" I'm serious, no kidding.