AWR Sample Report
in [Oracle]
|
From: Charles Hooper on 9 Nov 2008 21:49 Raja, you are making progress. You provided the standard definitions of the parameters, but for the most part did not indicate why the current parameters are set. Comments inline: On Nov 9, 12:26 pm, raja <dextersu...(a)gmail.com> wrote: > Hi, > > I have some details regarding the parameters that you have told to > look into. > > 1. _newsort_enabled is set to FALSE > - Activates new sorting algorithm in 10gR2, which is more efficient on > memory and CPU > - Default: TRUE > - Its set to FALSE, may be due to one of the following reasons : > a. due to the side effect of this new parameter, which causes again > a sort by row id eventhough the rows are in order. (snip) You might see if you are able to find a specific example in your database why this is disabled. Possible resource: http://jonathanlewis.wordpress.com/2007/06/03/sorting/ > 2. _optim_peek_user_binds is set to FALSE (there may be a very good > reason), > - Its set to FALSE, may be due to one of the following reasons : > a. not mess with the CBO explain plans > b. to make CBO create proper plans with respect to bind variables Did you find that when it was set to TRUE Oracle was generating bad plans? > 3. cursor_space_for_time is set to TRUE, (snip) > - I found that it is recommended by many people to have the value of > this parameter as default ( FALSE ), since this has some side effects. That seems to be the general rule, and I believe that I saw that this parameter would be depreciated in a future version of Oracle (Oracle 11g R2?). > 4. db_writer_processes is set to 7 (there may be a very good reason > for not doing this), > - Default value : 1 or CPU_COUNT / 8, whichever is greater. Range of > values 1 to 20 (snip) > Thus, it is recommended to set db_writer_processes equal to the > number of LRU latches (db_block_lru_latches) and not exceed the number > of CPUs on the system. I was hoping that you would find the following article. After reading, you may consider significantly decreasing the value of db_writer_processes: http://kevinclosson.wordpress.com/2007/08/10/learn-how-to-obliterate-processor-caches-configure-lots-and-lots-of-dbwr-processes/ > 5. optimizer_index_cost_adj is set to 5 (there is a very good reason > for not doing this) > - Default value : 100, Range of values zero to 10,000 > - This parameter alters the costing algorithm for access paths > involving indexes. The smaller the value, the cheaper the cost of > index access. > - For some OLTP systems, re-setting this parameter to a smaller value > (between 10- to 30) may result in huge performance gains! > - Oracle support(without knowing anything about my system) is telling > to use the following settings: > OPTIMIZER_INDEX_CACHING = 50 > OPTIMIZER_INDEX_COST_ADJ = 5 Those appear to be suggestions that might be more appropriate prior to the introduction of CPU costing, which is enabled by default on Oracle 10g. OPTIMIZER_INDEX_COST_ADJ = 5 is far too low of a value, which basically tells Oracle to multiply the current index type access cost by 0.05. Not only does this have a significant chance of forcing unnecessary index scans when full table scans might be more appropriate, but it also increases the chances that the wrong index will be used for data retrieval due to rounding errors during cost calculations. References: http://books.google.com/books?id=w8qzDTUVHSQC&pg=PA185&lpg=PA185&dq=troubleshooting+oracle+performance++OPTIMIZER_INDEX_COST_ADJ&source=web&ots=_eoA6x-X2H&sig=GndPn0-bZrVWfuMSStzqsxJ05wU&hl=en&sa=X&oi=book_result&resnum=4&ct=result http://jonathanlewis.wordpress.com/2006/10/24/optimizer_index_cost_adj/ > 6. resource_limit is set to TRUE. > - Default value : false > - determines whether resource limits are enforced in database > profiles. > - resource_limit = TRUE, Enables the enforcement of resource limits. > I think that this would allow to control the resource limits for a > particular user using profiles > ( limits can be like max no of sessions used, max amt of SGA used, max > connection time, max block read/session, CPU time limit/call,..). What I was hoping that you would determine by looking at resource_limit is that potentially someone enabled a resource limit which is artifically causing an apparent performance problem. There is not enough evidence to suggest that this is the case, but you may want to take a look at that as a possibility. > From the above analysis, I feel that the parameter values that are > required to be changed are as follows : > I. cursor_space_for_time, should be set to FALSE from the current > value TRUE > II. multi block read parameter has to be removed from init.ora, if we > are using only init.ora file alone. > III. change the optimizer mode from CHOOSE to the 10g default value > ALL_ROWS > > Please check whether the details of these parameters are correct, > including the final analysis/conclusion regarding the parameters. The above seem to be logical conclusions to your research. > Your Question : > Regarding your guess about the single block reads, assume that during > the 31 second time interval, an application was submitting a couple of > queries to the database, and Oracle needed to read a couple (11,529 > in > 31 seconds) 8KB blocks from disk to supply an answer to the > application. If the disk subsystem (or the connection between the > server and the disk subsystem) was running near or at maximum > capacity > (possibly maximum number of random access IOs) due to a concurrent > backup job, what happens to the performance of the application? > > Performance of the system will decrease. Yes, that was exactly what I had hoped you would see as a possibility, although looking at an AWR report for a 31.2 second time period might be a bit misleading. > Also, please check whether the analysis that i have made in my > previous mail are correct or not. I only commented on those items where it was easy to see that you had worked to find the solution and showed your analysis. I might have overlooked a couple items in your posts. I would suggest collecting an AWR report for 10 to 15 minutes, unless the performance problem is only present in this 31 second time period. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: raja on 10 Nov 2008 00:03 Hi, Thanks Charles, for ur detailed comments. I will check and get back to you regarding the links that you have provided. With Regards, Raja.
From: raja on 10 Nov 2008 06:07 Hi, I have learned some ( basic level ) init.ora parameters from the home work that you gave to me. I am still not clear with some details. 1. why oracle should create parameter that have side effects ( may be thats y they are created as hidden parameters ) ? 2. setting the values are still not clear, because some have dependancy, some are system specific,.... From the above discussions / analysis, No changes to the following parameter values are to be made : 1. _newsort_enabled is set to FALSE 2. _optim_peek_user_binds is set to FALSE 3. resource_limit is set to TRUE Changes to the parameter values to be made are as follows : 1. cursor_space_for_time, should be set to FALSE from the current value TRUE 2. db_file_multiblock_read_count - has to be removed from init.ora, if we are using only init.ora file alone. 3. optimizer mode - set the value as ALL_ROWS, from the current value CHOOSE 4. optimizer_index_cost_adj - should increase the value from the existing value 5. Current values : optimizer_index_caching 90 ; optimizer_index_cost_adj 5. I hope i can set optimizer_index_cost_adj to 10, considering not to have too low value ( 5 ). 5. db_writer_processes - The value should be decreased from current value 7. Let me check on what value should be set and get back to you. Please check, whether the conclusions are right/wrong. I will also try to get another AWR Report for about 10-15 minutes. Hope i can learn better using that. With Regards, Raja.
From: Charles Hooper on 10 Nov 2008 09:53 Comments inline: On Nov 10, 6:07 am, raja <dextersu...(a)gmail.com> wrote: > I am still not clear with some details. > 1. why oracle should create parameter that have side effects ( may be > thats y they are created as hidden parameters ) ? Hidden parameters (those which begin with _ ) should only be changed after consulting Oracle Support, regardless of what some websites suggest to use in order to "super-size" a parameter to improve performance - chances are, there is another website which suggests that changing that hidden parameter has undesirable side effects. It is important to understand the undesirable side effects for all parameter values, especially if those parameters are hidden parameters. Oracle databases must work efficiently with many different types of applications (online transaction processing [OLTP], batch processing, data warehouse, etc.), and when you consider that versions of Oracle databases exist (or have existed) for somewhere around 100 different operating systems, there needs to be flexibility in the database instance, which is controlled through the use of different parameter values. Parameters (and features) which have no visible side effects in an OLTP database may have severe side effects in a data warehouse database, and the various parameter values help eliminate the side effects. It would be nice if there were a checklist built into the database software that someone could complete, which would then suggest the optimal parameters for the database instance, the suggested size and number of redo logs, the most appropriate location of the data files, the number and speed of the CPUs, the amount of memory, the optimal operating system, PCTFREE values for tables, etc. To my knowledge, that checklist does not exist. Thus, the DBA must be willing to investigate the optimal settings for the specific database instances, and the applications that those database instances serve. An optimal parameter value for an Oracle 8.1.7.3 database instance may not be an optimal value for an Oracle 11.1.0.7 database instance due to improvements/changes in optimizer features. > 2. setting the values are still not clear, because some have > dependancy, some are system specific,.... > > From the above discussions / analysis, > > No changes to the following parameter values are to be made : > > 1. _newsort_enabled is set to FALSE > 2. _optim_peek_user_binds is set to FALSE > 3. resource_limit is set to TRUE > > Changes to the parameter values to be made are as follows : > > 1. cursor_space_for_time, should be set to FALSE from the current > value TRUE > 2. db_file_multiblock_read_count - has to be removed from init.ora, if > we are using only init.ora file alone. > 3. optimizer mode - set the value as ALL_ROWS, from the current value > CHOOSE > 4. optimizer_index_cost_adj - should increase the value from the > existing value 5. > Current values : optimizer_index_caching 90 ; > optimizer_index_cost_adj 5. > I hope i can set optimizer_index_cost_adj to 10, considering not > to have too low value ( 5 ). > 5. db_writer_processes - The value should be decreased from current > value 7. Let me check on what value should be set and get back to you. > > Please check, whether the conclusions are right/wrong. I believe that the above values for optimizer_index_caching and optimizer_index_cost_adj still need a little more adjustment to avoid an over-dependence on nested loop joins and to avoid utilizing index based access (possibly with the wrong index) when a full tablescan might be more efficient. Paraphrased from "Cost-Based Oracle Fundamentals": "OPTIMIZER_INDEX_CACHING helps correct the optimizers assumption that all reads are physical reads. This has an effect when multiple tables are joined using nested loops with index access for the inner (second) table, but also has an effect for cost calculation of in-list iteration. This parameter does not affect the cost of a single table indexed access path. A starting value of 75 is a reasonable starting guess." Paraphrased from "Cost-Based Oracle Fundamentals": "OPTIMIZER_INDEX_COST_ADJ reduces the cost of single block reads, which tends to reduce the tendency of excessive tablescans. The downside is that due to rounding errors, the optimizer may decide to use the wrong index." Paraphrased from "Troubleshooting Oracle Performance": "Oracle 8i and earlier use the I/O cost model, which by default costs single block and multi-block reads the same. OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING help to correct this issue by making index access (single block access) appear to be less expensive. CPU cost model (system statistics), used by default with Oracle 10g, takes into account the performance of the CPU and the I/O subsystem." The CPU cost model nearly eliminates the need to change OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING from their defaults. CPU cost model statistics are stored in SYS.AUX_STATS$ . Based on the above, you might want to consider setting (test the effects of changes): * OPTIMIZER_INDEX_COST_ADJ close to 100 * OPTIMIZER_INDEX_CACHING close to 0 * Values in SYS.AUX_STATS$ to appropriate values The following SQL*Plus command will automatically calculate the values in SYS.AUX_STATS$ based on a 60 minute time interval (starting immediately after you execute this statement): EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60) While you are checking SYS.AUX_STATS$, you may also want to verify that fixed object statistics have been gathered by querying SYS.TAB_STATS$ - if no rows are returned, then fixed object statistics have not been gathered and poor performance or session crashes may result, see: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199 > I will also try to get another AWR Report for about 10-15 minutes. That would be a good idea. Keep in mind that the above suggestions are very general in nature, and may not exactly apply to your database instance. The AWR report for 10-15 minutes will allow for more specific/relevant suggestions. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: raja on 18 Nov 2008 01:38
Hi, I have another AWR Report generated with 1hr interval. I dont know how to approach this. Please help on this... AWR Report : Snap Id Snap Time Sessions Cursors/Session Begin Snap: 10739 13-Nov-08 09:00:45 105 58.2 End Snap: 10763 13-Nov-08 15:00:12 91 75.2 Elapsed: 359.46 (mins) DB Time: 1,723.72 (mins) Cache Sizes Begin End Buffer Cache: 6,832M 6,832M Std Block Size: 8K Shared Pool Size: 1,264M 1,264M Log Buffer: 14,344K Load Profile Per Second Per Transaction Redo size: 594,828.36 528,401.03 Logical reads: 393,203.94 349,292.97 Block changes: 3,687.61 3,275.79 Physical reads: 532.23 472.79 Physical writes: 341.38 303.25 User calls: 224.24 199.20 Parses: 14.60 12.97 Hard parses: 0.32 0.29 Sorts: 3.20 2.85 Logons: 0.26 0.23 Executes: 541.25 480.80 Transactions: 1.13 % Blocks changed per Read: 0.94 Recursive Call %: 72.78 Rollback per transaction %: 0.08 Rows per Sort: 6101.47 Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.89 In-memory Sort %: 100.00 Library Hit %: 97.45 Soft Parse %: 97.79 Execute to Parse %: 97.30 Latch Hit %: 95.31 Parse CPU to Parse Elapsd %: 79.21 % Non-Parse CPU: 99.10 Shared Pool Statistics Begin End Memory Usage %: 89.36 92.12 % SQL with executions>1: 95.58 97.42 % Memory for SQL w/exec>1: 95.17 97.31 Top 5 Timed Events Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class CPU time 56,931 55.0 db file sequential read 4,036,055 13,310 3 12.9 User I/ O db file parallel write 567,009 6,515 11 6.3 System I/O db file scattered read 934,341 1,383 1 1.3 User I/ O log file parallel write 38,621 1,019 26 1.0 System I/O Time Model Statistics Statistic Name Time (s) % of DB Time sql execute elapsed time 102,885.27 99.48 DB CPU 56,931.50 55.05 Java execution elapsed time 18,173.85 17.57 PL/SQL execution elapsed time 1,146.88 1.11 RMAN cpu time (backup/restore) 782.75 0.76 parse time elapsed 649.38 0.63 hard parse elapsed time 619.03 0.60 failed parse elapsed time 13.16 0.01 connection management call elapsed time 10.55 0.01 inbound PL/SQL rpc elapsed time 7.46 0.01 hard parse (sharing criteria) elapsed time 7.30 0.01 PL/SQL compilation elapsed time 1.10 0.00 repeated bind elapsed time 0.34 0.00 sequence load elapsed time 0.03 0.00 hard parse (bind mismatch) elapsed time 0.02 0.00 DB time 103,423.35 background elapsed time 9,817.43 background cpu time 1,169.73 Wait Class Wait Class Waits %Time-outs Total Wait Time (s) Avg wait (ms) Waits /txn User I/O 5,383,928 0.00 15,139 3 221.75 System I/O 1,606,400 0.00 8,442 5 66.16 Administrative 51,609 0.00 901 17 2.13 Commit 17,706 0.10 387 22 0.73 Network 9,640,482 0.00 165 0 397.07 Application 2,045 2.30 140 68 0.08 Concurrency 366,686 0.10 65 0 15.10 Configuration 257 0.00 53 206 0.01 Other 2,147 38.33 47 22 0.09 Wait Events Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn db file sequential read 4,036,055 0.00 13,310 3 166.24 db file parallel write 567,009 0.00 6,515 11 23.35 db file scattered read 934,341 0.00 1,383 1 38.48 log file parallel write 38,621 0.00 1,019 26 1.59 Backup: sbtwrite2 51,438 0.00 896 17 2.12 Log archive I/O 15,175 0.00 658 43 0.63 log file sync 17,706 0.10 387 22 0.73 direct path write temp 98,515 0.00 313 3 4.06 SQL*Net more data to client 4,642,420 0.00 154 0 191.21 enq: TX - row lock contention 1,475 3.19 138 94 0.06 control file parallel write 10,484 0.00 107 10 0.43 log file sequential read 12,839 0.00 87 7 0.53 read by other session 40,059 0.00 79 2 1.65 direct path read 272,647 0.00 54 0 11.23 log buffer space 181 0.00 46 256 0.01 enq: WL - contention 16 87.50 44 2742 0.00 latch: cache buffers chains 363,571 0.00 37 0 14.97 control file single write 448 0.00 28 63 0.02 os thread startup 424 0.00 23 55 0.02 RMAN backup & recovery I/O 6,130 0.00 18 3 0.25 control file sequential read 955,614 0.00 9 0 39.36 log file switch completion 75 0.00 7 89 0.00 SQL*Net more data from dblink 127,162 0.00 6 0 5.24 cursor: pin S wait on X 365 100.00 4 10 0.02 SQL*Net message to client 4,819,165 0.00 4 0 198.49 switch logfile command 7 0.00 2 268 0.00 Backup: sbtinfo2 41 0.00 2 37 0.00 enq: CF - contention 14 0.00 1 89 0.00 latch free 181 0.00 1 6 0.01 enq: KO - fast object checkpoint 47 0.00 1 21 0.00 Backup: sbtremove2 30 0.00 1 30 0.00 buffer busy waits 2,272 0.00 1 0 0.09 Backup: sbtclose2 22 0.00 1 29 0.00 Backup: sbtbackup 22 0.00 1 24 0.00 rdbms ipc reply 12 0.00 1 43 0.00 SQL*Net more data from client 5,009 0.00 0 0 0.21 log file single write 80 0.00 0 4 0.00 enq: RO - fast object reuse 15 0.00 0 19 0.00 db file parallel read 14 0.00 0 17 0.00 PX Deq: Signal ACK 28 21.43 0 8 0.00 direct path write 1,712 0.00 0 0 0.07 latch: shared pool 24 0.00 0 9 0.00 Backup: sbtinit 16 0.00 0 9 0.00 enq: PR - contention 3 0.00 0 42 0.00 SQL*Net more data to dblink 3,080 0.00 0 0 0.13 SQL*Net break/reset to client 508 0.00 0 0 0.02 kksfbc child completion 2 100.00 0 49 0.00 SQL*Net message to dblink 43,646 0.00 0 0 1.80 local write wait 6 0.00 0 12 0.00 LGWR wait for redo copy 936 0.00 0 0 0.04 jobq slave TJ process wait 5 100.00 0 10 0.00 latch: cache buffers lru chain 25 0.00 0 2 0.00 direct path read temp 579 0.00 0 0 0.02 latch: object queue header operation 44 0.00 0 1 0.00 reliable message 61 0.00 0 0 0.00 latch: library cache 11 0.00 0 2 0.00 Backup: sbtend 17 0.00 0 1 0.00 latch: session allocation 6 0.00 0 2 0.00 PX Deq Credit: send blkd 10 0.00 0 0 0.00 latch: redo allocation 3 0.00 0 1 0.00 latch: library cache lock 3 0.00 0 1 0.00 latch: row cache objects 1 0.00 0 3 0.00 PX qref latch 797 99.87 0 0 0.03 enq: PS - contention 2 0.00 0 0 0.00 Backup: sbtinit2 16 0.00 0 0 0.00 latch: redo writing 1 0.00 0 0 0.00 cursor: mutex S 15 0.00 0 0 0.00 cursor: pin S 2 0.00 0 0 0.00 SQL*Net message from client 4,819,163 0.00 552,203 115 198.49 jobq slave wait 25,556 86.15 69,777 2730 1.05 Streams AQ: qmn slave idle wait 766 0.00 21,012 27431 0.03 Streams AQ: qmn coordinator idle wait 1,583 51.61 21,012 13273 0.07 PX Idle Wait 4,235 98.70 8,209 1938 0.17 Streams AQ: waiting for time management or cleanup tasks 5 100.00 5,361 1072103 0.00 SQL*Net message from dblink 43,646 0.00 2,188 50 1.80 single-task message 3,080 0.00 533 173 0.13 PX Deq: Execution Msg 237 0.00 9 39 0.01 PX Deq: Msg Fragment 42 0.00 0 3 0.00 PX Deq: Parse Reply 31 0.00 0 3 0.00 PX Deq: Execute Reply 19 0.00 0 2 0.00 PX Deq: Join ACK 26 0.00 0 1 0.00 class slave wait 46 0.00 0 0 0.00 Background Wait Events Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn db file parallel write 567,009 0.00 6,515 11 23.35 log file parallel write 38,621 0.00 1,019 26 1.59 Log archive I/O 13,753 0.00 574 42 0.57 control file parallel write 9,060 0.00 90 10 0.37 log file sequential read 11,638 0.00 69 6 0.48 os thread startup 396 0.00 22 55 0.02 db file sequential read 552 0.00 2 4 0.02 log file sync 31 0.00 1 45 0.00 events in waitclass Other 1,025 0.00 1 1 0.04 db file scattered read 178 0.00 1 4 0.01 direct path read 840 0.00 1 1 0.03 control file sequential read 10,683 0.00 0 0 0.44 log file single write 80 0.00 0 4 0.00 direct path write 840 0.00 0 0 0.03 log buffer space 14 0.00 0 2 0.00 latch: library cache 1 0.00 0 5 0.00 latch: cache buffers chains 2 0.00 0 0 0.00 latch: redo writing 1 0.00 0 0 0.00 rdbms ipc message 152,029 73.00 344,964 2269 6.26 Streams AQ: qmn slave idle wait 766 0.00 21,012 27431 0.03 Streams AQ: qmn coordinator idle wait 1,583 51.61 21,012 13273 0.07 pmon timer 7,148 100.00 20,935 2929 0.29 smon timer 111 53.15 20,131 181360 0.00 Streams AQ: waiting for time management or cleanup tasks 5 100.00 5,361 1072103 0.00 Operating System Statistics Statistic Total NUM_LCPUS 0 NUM_VCPUS 0 AVG_BUSY_TIME 773,431 AVG_IDLE_TIME 1,382,735 AVG_IOWAIT_TIME 198,722 AVG_SYS_TIME 112,958 AVG_USER_TIME 659,771 BUSY_TIME 10,837,634 IDLE_TIME 19,367,934 IOWAIT_TIME 2,791,750 SYS_TIME 1,591,031 USER_TIME 9,246,603 LOAD 0 OS_CPU_WAIT_TIME 11,375,200 RSRC_MGR_CPU_WAIT_TIME 0 PHYSICAL_MEMORY_BYTES 82,678,112,256 NUM_CPUS 14 NUM_CPU_CORES 7 Instance Activity Stats Statistic Total per Second per Trans CPU used by this session 2,760,501 127.99 113.70 CPU used when call started 2,065,134 95.75 85.06 CR blocks created 116,826 5.42 4.81 Cached Commit SCN referenced 35,858,913 1,662.63 1,476.95 Commit SCN cached 1,088 0.05 0.04 DB time 14,230,583 659.81 586.13 DBWR checkpoint buffers written 2,786,759 129.21 114.78 DBWR checkpoints 82 0.00 0.00 DBWR object drop buffers written 28 0.00 0.00 DBWR parallel query checkpoint buffers written 0 0.00 0.00 DBWR revisited being-written buffer 4 0.00 0.00 DBWR tablespace checkpoint buffers written 579 0.03 0.02 DBWR thread checkpoint buffers written 0 0.00 0.00 DBWR transaction table writes 665 0.03 0.03 DBWR undo block writes 879,979 40.80 36.24 DFO trees parallelized 2 0.00 0.00 IMU CR rollbacks 1,266 0.06 0.05 IMU Flushes 6,465 0.30 0.27 IMU Redo allocation size 11,813,948 547.76 486.59 IMU commits 17,702 0.82 0.73 IMU contention 1,089 0.05 0.04 IMU ktichg flush 1,657 0.08 0.07 IMU pool not allocated 112 0.01 0.00 IMU recursive-transaction flush 3 0.00 0.00 IMU undo allocation size 65,066,320 3,016.85 2,679.94 IMU- failed to get a private strand 112 0.01 0.00 Misses for writing mapping 0 0.00 0.00 PX local messages recv'd 368 0.02 0.02 PX local messages sent 368 0.02 0.02 Parallel operations not downgraded 2 0.00 0.00 SMON posted for undo segment shrink 0 0.00 0.00 SQL*Net roundtrips to/from client 4,813,090 223.16 198.24 SQL*Net roundtrips to/from dblink 43,646 2.02 1.80 active txn count during cleanout 211,905 9.83 8.73 application wait time 13,949 0.65 0.57 background checkpoints completed 21 0.00 0.00 background checkpoints started 20 0.00 0.00 background timeouts 111,870 5.19 4.61 branch node splits 19 0.00 0.00 buffer is not pinned count 7,406,205,468 343,394.21 305,045.74 buffer is pinned count 1,342,723,816 62,256.39 55,303.92 bytes received via SQL*Net from client 606,443,091 28,118.18 24,978.09 bytes received via SQL*Net from dblink 265,807,835 12,324.38 10,948.06 bytes sent via SQL*Net to client 18,910,995,749 876,822.35 778,903.40 bytes sent via SQL*Net to dblink 25,547,081 1,184.51 1,052.23 calls to get snapshot scn: kcmgss 11,936,890 553.46 491.65 calls to kcmgas 1,248,240 57.88 51.41 calls to kcmgcs 106,173 4.92 4.37 change write time 44,255 2.05 1.82 cleanout - number of ktugct calls 547,076 25.37 22.53 cleanouts and rollbacks - consistent read gets 111,031 5.15 4.57 cleanouts only - consistent read gets 136,836 6.34 5.64 cluster key scan block gets 382,786 17.75 15.77 cluster key scans 278,819 12.93 11.48 commit batch performed 3,080 0.14 0.13 commit batch requested 3,080 0.14 0.13 commit batch/immediate performed 3,083 0.14 0.13 commit batch/immediate requested 3,083 0.14 0.13 commit cleanout failures: block lost 41,023 1.90 1.69 commit cleanout failures: buffer being written 4 0.00 0.00 commit cleanout failures: callback failure 157 0.01 0.01 commit cleanout failures: cannot pin 231 0.01 0.01 commit cleanouts 749,666 34.76 30.88 commit cleanouts successfully completed 708,251 32.84 29.17 commit immediate performed 3 0.00 0.00 commit immediate requested 3 0.00 0.00 commit txn count during cleanout 351,189 16.28 14.46 concurrency wait time 6,507 0.30 0.27 consistent changes 5,328,067 247.04 219.45 consistent gets 8,341,439,937 386,757.05 343,566.04 consistent gets - examination 3,757,741,068 174,230.45 154,773.30 consistent gets direct 6,617,029 306.80 272.54 consistent gets from cache 8,334,822,915 386,450.25 343,293.50 current blocks converted for CR 887 0.04 0.04 cursor authentications 5,542 0.26 0.23 data blocks consistent reads - undo records applied 5,319,562 246.65 219.10 db block changes 79,532,990 3,687.61 3,275.79 db block gets 139,044,324 6,446.89 5,726.94 db block gets direct 1,283 0.06 0.05 db block gets from cache 139,043,041 6,446.83 5,726.89 deferred (CURRENT) block cleanout applications 297,057 13.77 12.24 dirty buffers inspected 833,757 38.66 34.34 enqueue conversions 26,766 1.24 1.10 enqueue releases 379,365 17.59 15.63 enqueue requests 381,254 17.68 15.70 enqueue timeouts 1,886 0.09 0.08 enqueue waits 1,397 0.06 0.06 execute count 11,673,423 541.25 480.80 failed probes on index block reclamation 714 0.03 0.03 free buffer inspected 11,179,231 518.33 460.45 free buffer requested 10,381,781 481.36 427.60 heap block compress 164,584 7.63 6.78 hot buffers moved to head of LRU 5,449,531 252.67 224.45 immediate (CR) block cleanout applications 247,867 11.49 10.21 immediate (CURRENT) block cleanout applications 385,730 17.88 15.89 index crx upgrade (found) 452 0.02 0.02 index crx upgrade (positioned) 62,987 2.92 2.59 index fast full scans (full) 2,384 0.11 0.10 index fast full scans (rowid ranges) 0 0.00 0.00 index fetch by key 2,698,249,086 125,106.32 111,135.10 index scans kdiixs1 141,091,704 6,541.82 5,811.27 java call heap collected bytes 807,424 37.44 33.26 java call heap collected count 12,592 0.58 0.52 java call heap gc count 48 0.00 0.00 java call heap live object count 12,880 0.60 0.53 java call heap live object count max 12,880 0.60 0.53 java call heap live size 4,392,064 203.64 180.90 java call heap live size max 4,392,064 203.64 180.90 java call heap object count 14,736 0.68 0.61 java call heap object count max 22,560 1.05 0.93 java call heap total size 8,409,088 389.89 346.35 java call heap total size max 8,409,088 389.89 346.35 java call heap used size 4,607,488 213.63 189.77 java call heap used size max 4,607,488 213.63 189.77 leaf node 90-10 splits 271 0.01 0.01 leaf node splits 1,553 0.07 0.06 lob reads 2,540 0.12 0.10 lob writes 3,993 0.19 0.16 lob writes unaligned 3,993 0.19 0.16 logons cumulative 5,645 0.26 0.23 messages received 709,812 32.91 29.24 messages sent 709,812 32.91 29.24 no buffer to keep pinned count 0 0.00 0.00 no work - consistent read gets 4,583,137,118 212,500.55 188,769.60 opened cursors cumulative 306,678 14.22 12.63 parse count (failures) 719 0.03 0.03 parse count (hard) 6,958 0.32 0.29 parse count (total) 314,916 14.60 12.97 parse time cpu 51,060 2.37 2.10 parse time elapsed 64,460 2.99 2.65 physical read IO requests 5,242,302 243.06 215.92 physical read bytes 94,035,378,176 4,360,020.07 3,873,115.79 physical read total IO requests 6,234,776 289.08 256.80 physical read total bytes ############### 6,008,005.33 5,337,062.65 physical read total multi block requests 1,241,703 57.57 51.14 physical reads 11,478,928 532.23 472.79 physical reads cache 9,309,733 431.65 383.45 physical reads cache prefetch 4,341,654 201.30 178.82 physical reads direct 2,169,195 100.58 89.34 physical reads direct (lob) 289 0.01 0.01 physical reads direct temporary tablespace 8,813 0.41 0.36 physical reads prefetch warmup 0 0.00 0.00 physical write IO requests 1,774,921 82.30 73.11 physical write bytes 60,315,189,248 2,796,558.50 2,484,253.44 physical write total IO requests 1,919,227 88.99 79.05 physical write total bytes ############### 4,671,166.82 4,149,515.29 physical write total multi block requests 828,012 38.39 34.10 physical writes 7,362,694 341.38 303.25 physical writes direct 3,046,704 141.26 125.49 physical writes direct (lob) 900 0.04 0.04 physical writes direct temporary tablespace 3,044,692 141.17 125.40 physical writes from cache 4,315,990 200.11 177.77 physical writes non checkpoint 5,207,133 241.43 214.47 pinned buffers inspected 764 0.04 0.03 prefetch warmup blocks aged out before use 0 0.00 0.00 prefetched blocks aged out before use 1 0.00 0.00 process last non-idle time 19,633 0.91 0.81 queries parallelized 2 0.00 0.00 recursive aborts on index block reclamation 0 0.00 0.00 recursive calls 12,928,244 599.43 532.49 recursive cpu usage 788,961 36.58 32.50 redo blocks written 25,938,241 1,202.65 1,068.34 redo buffer allocation retries 509 0.02 0.02 redo entries 40,749,357 1,889.37 1,678.38 redo log space requests 162 0.01 0.01 redo log space wait time 688 0.03 0.03 redo ordering marks 872,628 40.46 35.94 redo size 12,829,048,628 594,828.36 528,401.03 redo subscn max counts 1,425,256 66.08 58.70 redo synch time 39,573 1.83 1.63 redo synch writes 57,879 2.68 2.38 redo wastage 13,394,880 621.06 551.71 redo write time 105,026 4.87 4.33 redo writer latching time 6 0.00 0.00 redo writes 38,622 1.79 1.59 rollback changes - undo records applied 177 0.01 0.01 rollbacks only - consistent read gets 7,194 0.33 0.30 rows fetched via callback 997,973,536 46,271.78 41,104.39 session connect time 0 0.00 0.00 session cursor cache hits 87,057 4.04 3.59 session logical reads 8,480,484,110 393,203.94 349,292.97 session pga memory 7,148,528,160 331,446.81 294,432.56 session pga memory max 18,255,207,648 846,416.25 751,892.90 session uga memory ############### 669,514,241.36 594,746,385.30 session uga memory max 7,658,125,248 355,074.66 315,421.77 shared hash latch upgrades - no wait 6,882,720 319.12 283.48 shared hash latch upgrades - wait 582 0.03 0.02 sorts (disk) 0 0.00 0.00 sorts (memory) 69,110 3.20 2.85 sorts (rows) 421,672,915 19,551.18 17,367.80 sql area evicted 2,528 0.12 0.10 sql area purged 785 0.04 0.03 summed dirty queue length 2,721,198 126.17 112.08 switch current to new buffer 71,026 3.29 2.93 table fetch by rowid 2,954,474,371 136,986.40 121,688.47 table fetch continued row 21,150 0.98 0.87 table scan blocks gotten 44,917,305 2,082.62 1,850.05 table scan rows gotten 2,282,015,917 105,807.36 93,991.35 table scans (cache partitions) 1 0.00 0.00 table scans (direct read) 48 0.00 0.00 table scans (long tables) 73 0.00 0.00 table scans (rowid ranges) 0 0.00 0.00 table scans (short tables) 53,427 2.48 2.20 total number of times SMON posted 52 0.00 0.00 transaction rollbacks 3,083 0.14 0.13 transaction tables consistent read rollbacks 13 0.00 0.00 transaction tables consistent reads - undo records applied 636 0.03 0.03 undo change vector size 6,943,980,192 321,962.79 286,007.67 user I/O wait time 1,513,678 70.18 62.35 user calls 4,836,293 224.24 199.20 user commits 24,259 1.12 1.00 user rollbacks 20 0.00 0.00 workarea executions - onepass 29 0.00 0.00 workarea executions - optimal 58,361 2.71 2.40 write clones created in background 6 0.00 0.00 write clones created in foreground 1,658 0.08 0.07 Instance Activity Stats - Absolute Values Statistic Begin Value End Value session cursor cache count 9,385,330 9,602,685 opened cursors current 6,109 6,840 workarea memory allocated 7,708 9,606 logons current 105 91 Instance Activity Stats - Thread Activity Statistic Total per Hour log switches (derived) 20 3.34 Buffer Pool Statistics P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits D 845,033 108 -122,414,827 9,309,740 4,315,984 0 0 42,331 PGA Aggr Summary PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written 87.75 16,383 2,288 Buffer Wait Statistics Class Waits Total Wait Time (s) Avg Time (ms) data block 41,790 81 2 undo header 517 0 1 1st level bmb 3 0 3 2nd level bmb 1 0 10 segment header 6 0 0 undo block 3 0 0 Enqueue Activity Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) TX-Transaction (row lock contention) 1,322 1,322 0 1,315 141 107.60 WL-Being Written Redo Log 39 22 17 2 45 22,459.00 CF-Controlfile Transaction 12,219 12,217 2 14 1 91.36 KO-Multiple Object Checkpoint (fast object checkpoint) 432 432 0 47 1 21.47 RO-Multiple Object Reuse (fast object reuse) 126 126 0 14 0 20.29 PR-Process Startup 424 424 0 3 0 43.00 PS-PX Process Reservation 252 251 0 2 0 0.00 Undo Segment Summary Undo TS# Num Undo Blocks (K) Number of Transactions Max Qry Len (s) Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU 1 879.58 49,869 192,545 9 2862.38333333333333333333333333333333333/3223.0666666666666666666666666666666666 0/0 0/0/0/0/0/0 Latch Activity Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss ASM db client latch 14,472 0.00 0 0 AWR Alerted Metric Element list 111,345 0.00 0 0 Consistent RBA 38,650 0.02 0.00 0 0 FAL request queue 470 0.00 0 0 FAL subheap alocation 470 0.00 0 0 FIB s.o chain latch 737 0.00 0 0 FOB s.o list latch 12,527 0.03 0.00 0 0 In memory undo latch 4,290,987 0.04 0.00 0 29,423 1.67 JOX SGA heap latch 941 0.00 0 0 JS queue state obj latch 129,240 0.00 0 0 JS slv state obj latch 1,044 0.00 0 0 KMG MMAN ready and startup request latch 7,189 0.00 0 0 KTF sga latch 1,282 0.00 0 6,066 0.00 KWQMN job cache list latch 6 0.00 0 0 MQL Tracking Latch 0 0 429 0.00 Memory Management Latch 0 0 7,189 0.00 OS process 9,351 0.00 0 0 OS process allocation 13,409 0.52 0.00 0 0 OS process: request allocation 4,524 0.24 0.00 0 0 PL/SQL warning settings 19,858 0.00 0 0 SGA IO buffer pool latch 11,021 0.00 0 11,146 0.01 SQL memory manager latch 24 0.00 0 7,151 0.00 SQL memory manager workarea list latch 826,430 0.00 0 0 Shared B-Tree 766 0.00 0 0 X$KSFQP 154 0.00 0 0 active checkpoint queue latch 719,515 1.58 0.00 0 0 active service list 62,196 0.22 0.00 0 7,148 0.00 archive control 1,874 0.00 0 0 archive process latch 7,621 0.00 0 0 begin backup scn array 940 0.00 0 0 cache buffer handles 819,812 0.00 0.00 0 0 cache buffers chains 5,280,355,952 5.32 0.00 37 21,935,227 0.00 cache buffers lru chain 8,818,130 0.11 0.00 0 25,512,853 0.04 cache table scan latch 0 0 934,249 0.01 channel handle pool latch 4,635 0.47 0.00 0 0 channel operations parent latch 109,742 0.01 0.00 0 0 checkpoint queue latch 8,494,017 0.01 0.00 0 4,298,746 0.01 client/application info 33,016 0.00 0 0 commit callback allocation 6,476 0.26 0.00 0 0 compile environment latch 59,891 0.00 0 0 constraint object allocation 6,448 4.23 0.00 0 0 dictionary lookup 18 0.00 0 0 dml lock allocation 122,190 1.68 0.00 0 0 dummy allocation 11,360 0.40 0.00 0 0 enqueue hash chains 789,439 0.26 0.00 0 1,605 0.00 enqueues 585,379 0.23 0.00 0 0 error message lists 114 1.75 0.00 0 0 event group latch 2,255 0.00 0 0 file cache latch 2,148 0.00 0 0 global KZLD latch for mem in SGA 1,644 0.00 0 0 global ctx hash table latch 1 0.00 0 0 global tx hash mapping 41,173 0.01 0.00 0 0 hash table column usage latch 1,511 0.07 0.00 0 5,245,016 0.00 hash table modification latch 189 0.00 0 0 internal temp table object number allocation latc 1,221 0.00 0 0 job workq parent latch 15 100.00 0.00 0 5,952 6.03 job_queue_processes parameter latch 1,736 0.00 0 0 kks stats 33,820 0.00 0 0 krbmrosl 4,485 0.00 0 0 ksuosstats global area 1,486 0.00 0 0 ktm global data 112 0.00 0 0 kwqbsn:qsga 766 0.00 0 0 lgwr LWN SCN 39,301 0.16 0.00 0 0 library cache 2,288,082 1.90 0.00 0 21,060 0.00 library cache load lock 990 0.00 0 0 library cache lock 1,526,021 0.99 0.00 0 0 library cache lock allocation 121,627 0.01 0.00 0 0 library cache pin 604,460 0.90 0.00 0 0 library cache pin allocation 35,107 0.09 0.00 0 0 list of block allocation 87,703 0.52 0.00 0 0 loader state object freelist 4,281 0.00 0 0 longop free list parent 1,334 0.00 0 71 0.00 message pool operations parent latch 292 0.00 0 0 messages 1,694,814 1.06 0.00 0 0 mostly latch-free SCN 39,725 0.98 0.00 0 0 multiblock read objects 3,291,270 0.00 0.00 0 0 ncodef allocation latch 340 0.00 0 0 object queue header heap 654,313 0.00 0 22,441 0.00 object queue header operation 34,346,629 0.02 0.01 0 0 object stats modification 73 0.00 0 0 parallel query alloc buffer 3,420 0.18 0.00 0 0 parallel query stats 62 14.52 0.00 0 0 parameter table allocation management 12,199 1.34 0.00 0 0 post/wait queue 45,298 0.23 0.00 0 17,968 0.00 process allocation 4,526 0.00 0 2,255 0.00 process group creation 4,524 1.17 0.00 0 0 process queue 396 1.01 0.00 0 0 process queue reference 13,762 0.00 0 671 118.78 qmn task queue latch 3,064 0.20 0.00 0 0 query server freelists 286 3.50 0.00 0 0 query server process 28 0.00 0 28 0.00 redo allocation 196,729 0.43 0.00 0 40,747,233 0.05 redo copy 0 0 40,749,514 0.00 redo writing 867,413 1.06 0.00 0 0 resmgr group change latch 7,573 0.00 0 0 resmgr:actses active list 11,288 0.00 0 0 resmgr:actses change group 5,248 0.00 0 0 resmgr:free threads list 11,264 0.49 0.00 0 0 resmgr:schema config 24 0.00 0 0 row cache objects 9,463,000 0.18 0.00 0 818 0.00 rules engine aggregate statistics 2 0.00 0 0 rules engine rule set statistics 4 0.00 0 0 sequence cache 11,055 0.00 0 0 session allocation 23,149,602 0.30 0.00 0 0 session idle bit 9,769,326 0.00 0.00 0 0 session state list latch 24,658 9.81 0.00 0 0 session switching 340 0.00 0 0 session timer 7,148 0.00 0 0 shared pool 790,880 0.19 0.02 0 0 shared pool sim alloc 3 0.00 0 0 shared pool simulator 366,162 0.00 0 0 simulator hash latch 295,089,755 0.00 0.00 0 0 simulator lru latch 290,100,417 0.33 0.00 0 3,683,779 0.00 slave class 48 0.00 0 0 slave class create 192 7.81 1.07 1 0 sort extent pool 25,433 0.00 0 0 state object free list 48 0.00 0 0 statistics aggregation 2,688 0.00 0 0 temp lob duration state obj allocation 36 0.00 0 0 temporary table state object allocation 13 0.00 0 0 threshold alerts latch 1,575 0.00 0 0 transaction allocation 14,048 0.31 0.00 0 0 transaction branch allocation 13,685 0.31 0.00 0 0 undo global data 35,333,792 0.01 0.00 0 0 user lock 13,415 5.65 0.01 0 0 Latch Sleep Breakdown Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 cache buffers chains 5,280,355,952 280,822,854 363,585 280,494,637 0 0 0 simulator lru latch 290,100,417 965,004 154 964,866 0 0 0 session allocation 23,149,602 68,947 6 68,941 0 0 0 library cache 2,288,082 43,386 11 43,375 0 0 0 row cache objects 9,463,000 16,632 1 16,631 0 0 0 library cache lock 1,526,021 15,074 3 15,071 0 0 0 active checkpoint queue latch 719,515 11,386 4 11,382 0 0 0 simulator hash latch 295,089,755 11,102 2 11,100 0 0 0 cache buffers lru chain 8,818,130 9,639 25 9,614 0 0 0 redo writing 867,413 9,177 1 9,176 0 0 0 object queue header operation 34,346,629 7,960 44 7,917 0 0 0 shared pool 790,880 1,520 24 1,496 0 0 0 redo allocation 196,729 855 3 852 0 0 0 user lock 13,415 758 5 753 0 0 0 slave class create 192 15 16 0 0 0 0 Dictionary Cache Stats Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage dc_awr_control 442 0.00 0 48 1 dc_database_links 9,562 0.02 0 0 3 dc_files 80 0.00 0 0 40 dc_global_oids 571 0.88 0 0 55 dc_histogram_data 93,357 0.10 0 0 1,379 dc_histogram_defs 210,451 0.37 0 162 4,934 dc_object_grants 1,005 10.85 0 0 630 dc_object_ids 698,144 0.02 0 6 1,142 dc_objects 90,831 0.26 0 140 1,761 dc_profiles 5,065 0.00 0 0 3 dc_rollback_segments 7,045 0.00 0 0 31 dc_segments 178,048 0.04 0 134 1,008 dc_sequences 156 1.92 0 156 14 dc_table_scns 2,804 0.00 0 74 10 dc_tablespace_quotas 88 0.00 0 0 12 dc_tablespaces 904,886 0.00 0 0 17 dc_usernames 30,976 0.02 0 0 41 dc_users 993,615 0.00 0 6 139 global database name 6,451 0.00 0 0 1 outstanding_alerts 577 0.00 0 2 14 Library Cache Activity Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali-dations BODY 61,206 0.01 88,056 0.01 3 0 CLUSTER 92 0.00 261 0.00 0 0 INDEX 26 0.00 249 0.00 0 0 JAVA DATA 37 0.00 0 0 0 JAVA SOURCE 57 0.00 114 1.75 1 0 SQL AREA 4,394 95.97 430,992 4.60 2,542 784 TABLE/PROCEDURE 15,299 0.73 276,194 0.17 170 0 TRIGGER 109 1.83 512 0.59 1 0 SGA Memory Summary SGA regions Begin Size (Bytes) Database Buffers 7,163,871,232 Fixed Size 2,086,552 Redo Buffers 14,688,256 Variable Size 1,409,288,552 init.ora Parameters Parameter Name value _newsort_enabled FALSE _optim_peek_user_binds FALSE compatible 10.2.0.3 cursor_space_for_time TRUE db_block_size 8192 db_cache_advice on db_file_multiblock_read_count 8 db_files 1024 db_writer_processes 7 job_queue_processes 6 log_buffer 14242816 max_dump_file_size 16384 open_cursors 1024 optimizer_index_caching 90 optimizer_index_cost_adj 5 optimizer_mode choose pga_aggregate_target 4294967296 processes 300 recovery_parallelism 4 resource_limit TRUE session_cached_cursors 256 session_max_open_files 128 sga_target 8589934592 statistics_level typical timed_statistics TRUE undo_management AUTO undo_retention 6000 undo_tablespace UNDO Thanks in Advance. With Regards, Raja. |