1. CPU Statisticas in AWR ReportCpu statisticas is hsec in statspack, you devide it by 100 to get the value in second and then devide it to get a value per cpu. IF you are near by 90-95 then you are cpu bound.
Example
---------
NOTE: THIS EXAMPLE IS BASED ON 30 MINUTE SNAP TIME.
How did you calculate that only 2.8 CPU seconds are used per second? The data I see is:
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 522,826 289.8 803.1
CPU used when call started 522,829 289.8 803.1
The stats for cpu are in hsecs. I divided by 100 2.898 ~ 2.8. Using pct's worked since you really divide them by 100 as well.
You are not "wasting" them. You are just not "using them"
You have 7200 cpu seconds in that 30 minute window (30*60*4), you are using 5,228 of them.
- From Asktom
2. If you find "BUFFER BUSY WAIT" in statspack increase FreeList by ALTER TABLE and ALTER INDEX if you are not using ASSM. If you are using ASSM and still getting "BUFFER BUSY WAIT" this could be problem of INITTRAN and MAXTRAN. ITL (Initial Transaction Slot) in Segmentmay not be enough.
3. ROLLBACK PER TRANSACTION
rollback per transaction = 0.29%
1* select 29/100, round( 1/345*100, 2 ) from dual
ops$tkyte@ORA920> /
29/100 ROUND(1/345*100,2)
---------- ------------------
.29 .29
1 out of 345 is 0.29% of the time...
0.29 rollback/transaction 1 transaction
100 rollback/transaction ?
(1*100)/.29 = 345 transaction
FROM ->
http://viveklsharma.blogspot.com/2007/03/enqueues-and-rollback-per-transaction.htmlEnqueues and Rollback per Transaction in Statspack
Under Load Profile section in a Statspack reports, there is a "Rollback per Transaction %age" which says the %age of transactions that rolled back. In many of the reports, it has been observed that this percentage is a way high, and hence, is alarming.
At one such Customer site, this %age was around 50% which means 1 out of 2 transaction is rolling back the entire work done. Rollback is a costlier affair and why should I have a transaction which actually is not required. A transaction generates REDO and rolling back the transaction generates further REDO. If this %age is brought down, there will be a significant reduction in the number of Archives generated in a day.
This database had an issue of ENQUEUE's as well. The most common being TX of type 4. The waiting session was an INSERT statement. Insert Statements are either blocked when the underlying table has a BITMAP Index or if the application is trying to insert duplicate values into a primary / unique key. In this case, the issue was with Duplicate values being inserted.
The application vendor was asked to review the logic and eliminate these ENQUEUES. The question was how to eliminate Rollback per Transaction ratio and whether is there any relation between the two.
A small test was conducted to check the relation between the two.
Session A
----------
1. Checked the redo generated by this session. This was 560 bytes.
2. create table test (x number(1));
3. alter table test add constraint pk_test primary key (x);
4. Check the redo again. This time it was 28868 bytes.
5. insert into test values(1);
Session B
----------
1. @redo_generated. This was 560 bytes.
1. insert into test values(1);
-- This will be locked with type TX and request = 4;
Session A now commits.
Session B
----------
The Insert statement of Session B fails with error :
"ERROR at line 1:ORA-00001:
unique constraint (VIVEK.PK_TEST) violated"
@redo_generated. This was 1776
This shows that this failed transaction generated some amount of redo. This was because, a row was actually inserted into the table and then checked for duplication. Also, the count "Transaction rollbacks" from v$sesstat for the failed transaction got incremented to 1. This "Transaction rollbacks" is reflected in Statspack report as "Rollback per transaction %age".
Hence, it is quite clear that there is a relation between ENQUEUE on Insert and "Rollback per Transaction %age". If the enqueues are eliminated, the rollback ratio should come down alongiwth the amount of unwanted redo.
