Hi,
I have a small 'orcl' database on my local machine, and I did not perform heavy activity on it. Today and yesterday I performed just some simple queries, like:
SELECT COUNT(*)
FROM products p, (SELECT prod_id, AVG(unit_cost) ac FROM costs GROUP BY prod_id) c
WHERE p.prod_id = c.prod_id AND
p.prod_list_price < 1.15 * c.ac;
or
select * from products;
from the 'sh' schema. Today I run a ADDM report, and this is the result:
|
ADDM Report for Task 'TASK_557' |
|
------------------------------- |
Analysis Period
---------------
AWR snapshot range from 490 to 494.
Time period starts at 17-JUL-13 11.00.34 PM
Time period ends at 18-JUL-13 05.31.00 PM
Analysis Target
---------------
Database 'ORCL' with DB ID 1346555844.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance orcl, numbered 1 and hosted at ROGER.
Activity During the Analysis Period
-----------------------------------
Total database time was 499 seconds.
The average number of active sessions was .01.
Summary of Findings
-------------------
| Description |
Active Sessions |
Recommendations |
|
Percent of Activity |
---------------------------------- ------------------- ---------------
| 1 I/O Throughput |
.01 | 100 |
2 |
| 2 Hard Parse |
0 | 29.47 |
0 |
| 3 Hard Parse Due to Sharing Criteria 0 | 8.89 |
1 |
| 4 Row Lock Waits |
0 | 7.37 |
0 |
| 5 PL/SQL Compilation |
0 | 4.04 |
1 |
| 6 Unusual "User I/O" Wait Event |
0 | 4.02 |
1 |
| 7 Commits and Rollbacks |
0 | 3.08 |
1 |
| 8 Shared Pool Latches |
0 | 2.78 |
0 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Findings and Recommendations |
|
---------------------------- |
Finding 1: I/O Throughput
Impact is .01 active sessions, 100% of total activity.
------------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
Recommendation 1: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
|
Consider increasing the throughput of the I/O subsystem. Oracle's |
|
recommended solution is to stripe all data files using the SAME |
|
methodology. You might also need to increase the number of disks for |
|
better performance. |
Rationale
|
During the analysis period, the average data files' I/O throughput was |
|
1.4 K per second for reads and 1 K per second for writes. The average |
|
response time for single block reads was 18 milliseconds. |
Recommendation 2: Host Configuration
Estimated benefit is 0 active sessions, 17.55% of total activity.
-----------------------------------------------------------------
Action
|
The performance of some data and temp files was significantly worse than |
|
others. If striping all files using the SAME methodology is not |
|
possible, consider striping these file over multiple disks. |
Rationale
|
For file D:\ORACLE\APP\ORADATA\ORCL\SYSTEM01.DBF, the average response |
|
time for single block reads was 168 milliseconds, and the total excess |
|
I/O wait was 70 seconds. |
|
Related Object |
|
Database file |
|
"D:\ORACLE\APP\ORADATA\ORCL\SYSTEM01.DBF" |
Rationale
|
For file D:\ORACLE\APP\ORADATA\ORCL\SYSAUX01.DBF, the average response |
|
time for single block reads was 16 milliseconds, and the total excess |
|
I/O wait was 16 seconds. |
|
Related Object |
|
Database file |
|
"D:\ORACLE\APP\ORADATA\ORCL\SYSAUX01.DBF" |
Symptoms That Led to the Finding:
---------------------------------
|
Wait class "User I/O" was consuming significant database time. |
|
Impact is 0 active sessions, 30.87% of total activity. |
Finding 2: Hard Parse
Impact is 0 active sessions, 29.47% of total activity.
------------------------------------------------------
Hard parsing of SQL statements was consuming significant database time.
Hard parsing SQL statements that encountered parse errors was not consuming
significant database time.
Hard parses due to literal usage and cursor invalidation were not consuming
significant database time.
The Oracle instance memory (SGA and PGA) was adequately sized.
No recommendations are available.
Finding 3: Hard Parse Due to Sharing Criteria
Impact is 0 active sessions, 8.89% of total activity.
-----------------------------------------------------
SQL statements with the same text were not shared because of cursor
environment mismatch. This resulted in additional hard parses which were
consuming significant database time.
Common causes of environment mismatch are session NLS settings, SQL trace
settings and optimizer parameters.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 8.89% of total activity.
----------------------------------------------------------------
Action
|
Look for top reason for cursor environment mismatch in |
|
V$SQL_SHARED_CURSOR. |
Symptoms That Led to the Finding:
---------------------------------
|
Hard parsing of SQL statements was consuming significant database time. |
|
Impact is 0 active sessions, 29.47% of total activity. |
Finding 4: Row Lock Waits
Impact is 0 active sessions, 7.37% of total activity.
-----------------------------------------------------
SQL statements were found waiting for row lock waits.
No recommendations are available.
Symptoms That Led to the Finding:
---------------------------------
|
Wait class "Application" was consuming significant database time. |
|
Impact is 0 active sessions, 7.78% of total activity. |
Finding 5: PL/SQL Compilation
Impact is 0 active sessions, 4.04% of total activity.
-----------------------------------------------------
PL/SQL compilation consumed significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 4.04% of total activity.
----------------------------------------------------------------
Action
|
Investigate the appropriateness of PL/SQL compilation. PL/SQL |
|
compilation can be caused by DDL on dependent objects. |
Finding 6: Unusual "User I/O" Wait Event
Impact is 0 active sessions, 4.02% of total activity.
-----------------------------------------------------
Wait event "Disk file operations I/O" in wait class "User I/O" was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 4.02% of total activity.
----------------------------------------------------------------
Action
|
Investigate the cause for high "Disk file operations I/O" waits. Refer |
|
to Oracle's "Database Reference" for the description of this wait event. |
Symptoms That Led to the Finding:
---------------------------------
|
Wait class "User I/O" was consuming significant database time. |
|
Impact is 0 active sessions, 30.87% of total activity. |
Finding 7: Commits and Rollbacks
Impact is 0 active sessions, 3.08% of total activity.
-----------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Recommendation 1: Host Configuration
Estimated benefit is 0 active sessions, 3.08% of total activity.
----------------------------------------------------------------
Action
|
Investigate the possibility of improving the performance of I/O to the |
|
online redo log files. |
Rationale
|
The average size of writes to the online redo log files was 21 K and the |
|
average time per write was 7 milliseconds. |
Rationale
|
The total I/O throughput on redo log files was 0 K per second for reads |
|
and 0.7 K per second for writes. |
Rationale
|
The redo log I/O throughput was divided as follows: 0% by RMAN and |
|
recovery, 100% by Log Writer, 0% by Archiver, 0% by Streams AQ and 0% by |
|
all other activity. |
Symptoms That Led to the Finding:
---------------------------------
|
Wait class "Commit" was consuming significant database time. |
|
Impact is 0 active sessions, 3.08% of total activity. |
Finding 8: Shared Pool Latches
Impact is 0 active sessions, 2.78% of total activity.
-----------------------------------------------------
Contention for latches related to the shared pool was consuming significant
database time.
Waits for "library cache load lock" amounted to 1% of database time.
Waits for "latch: shared pool" amounted to 1% of database time.
No recommendations are available.
Symptoms That Led to the Finding:
---------------------------------
|
Wait class "Concurrency" was consuming significant database time. |
|
Impact is 0 active sessions, 3.12% of total activity. |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Additional Information |
|
---------------------- |
Miscellaneous Information
-------------------------
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The database's maintenance windows were active during 94% of the analysis
period.
Being a small local database, my question is: do I really need to do something? For example at Finding 5: PL/SQL Compilation, what I really need to do? Or ar Finding 1: I/O Throughput
Thanks.