pecify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_50348_50349.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name addmrpt_1_50348_50349.txt
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ...
DETAILED ADDM REPORT FOR TASK 'TASK_175058' WITH ID 175058
----------------------------------------------------------
Analysis Period: 28-JUL-2014 from 10:00:39 to 10:30:14
Database ID/Instance: 997633034/1
Database/Instance Names: O32/O321
Host Name: o3
Database Version: 10.2.0.5.0
Snapshot Range: from 50348 to 50349
Database Time: 4809 seconds
Average Database Load: 2.7 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (4809 seconds)
-------------------------------------
Significant virtual memory paging was detected on the host operating system(页面交换)
RECOMMENDATION 1: Host Configuration, 100% benefit (4809 seconds)
ACTION: Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that
do not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more
physical memory to the host.(???)
FINDING 2: 10% impact (483 seconds)
-----------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 10% benefit (483 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 3446 transactions per minute
with an average redo size of 8103 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 10% benefit (483 seconds)
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 7
K and the average time per write was 3 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(10% impact [483 seconds])
--发现语句类似。################################## 分析验证 在下面
select sql_id from v$sql where PLAN_HASH_VALUE='2238861962' group by sql_id
select sql_text from v$sql where sql_id in (select sql_id from v$sql where PLAN_HASH_VALUE='2238861962' and rownum<100)
select * from v$sql where PLAN_HASH_VALUE='2238861962'
select count(distinct sql_id ) from v$sql where PLAN_HASH_VALUE='2238861962'
SQL> select count(distinct sql_id ) from v$sql where PLAN_HASH_VALUE='2238861962' ;
COUNT(DISTINCTSQL_ID)
---------------------
22731
select session_id, count(session_id)
from v$active_session_history
where sql_id in
(select sql_id from v$sql where PLAN_HASH_VALUE = '2238861962')
group by session_id
SQL> select session_id, count(session_id)
2 from v$active_session_history
3 where sql_id in
4 (select sql_id from v$sql where PLAN_HASH_VALUE = '2238861962')
5 group by session_id
6 ;
SESSION_ID COUNT(SESSION_ID)
---------- -----------------
914 44
913 46
911 38
910 3
912 55
SQL> select count(distinct sql_id ) from v$sqlarea_plan_hash where PLAN_HASH_VALUE='2238861962' ;
COUNT(DISTINCTSQL_ID)
---------------------
23189
--发现语句类似。################################## 分析验证
FINDING 3: 6.1% impact (293 seconds)
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 6.1% benefit (293 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals.
ACTION: Alternatively, you may set the parameter "cursor_sharing" to
"force".
RATIONALE: At least 22 SQL statements with PLAN_HASH_VALUE 2238861962
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 16 SQL statements with PLAN_HASH_VALUE 268293753
were found to be using literals. An example is SQL statement with
SQL_ID "0y18tgp1ms5f2" .
RELEVANT OBJECT: SQL statement with SQL_ID 0y18tgp1ms5f2 and
PLAN_HASH 268293753
select sum( ( ( a.l_current_amount +l_buy_unsettle_amount -
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (6.4% impact [306 seconds])
FINDING 4: 6% impact (290 seconds)
----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 6% benefit (288 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"0bkzah20gbgww".
RELEVANT OBJECT: SQL statement with SQL_ID 0bkzah20gbgww and
PLAN_HASH 2337774870
select a.l_schema_serial_no,b.l_daily_instruction_no,
SQL> select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS
--------------- ----------- ----------- ---------- -------------
SQL AREA .440552257 .951957066 16970 2382 --!!!!!!!!!!!
TABLE/PROCEDURE .960449864 .995234382 11560 0
BODY .993136694 .998852981 118 0
TRIGGER .998025616 .997609562 33 0
CLUSTER .986229508 .988514265 10 0
OBJECT 1 1 0 0
PIPE 1 1 0 0
JAVA SOURCE 1 1 0 0
JAVA RESOURCE 1 1 0 0
JAVA DATA 1 1 0 0
SQL> select gets,gethits, gethitratio from v$librarycache where namespace = 'SQL AREA';
GETS GETHITS GETHITRATIO
---------- ---------- -----------
3031760 1334398 .440139721
--语句重复使用率很低
SQL> select n, count(n)
2 from (select trunc(executions / 10, -1) n from v$sqlarea)
3 group by n
4 order by n;
N COUNT(N)
---------- ----------
0 52332 --
10 363
20 198
30 159
40 221
50 63
60 60
70 23
170 4
180 10
190 6
210 1
220 2
FINDING 5: 6% impact (288 seconds)
----------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: SQL Tuning, 6% benefit (288 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"0bkzah20gbgww".
RELEVANT OBJECT: SQL statement with SQL_ID 0bkzah20gbgww and
PLAN_HASH 2337774870
select a.l_schema_serial_no,b.l_daily_instruction_no,
RATIONALE: SQL statement with SQL_ID "0bkzah20gbgww" was executed 228331
times and had an average elapsed time of 0.0012 seconds.
RATIONALE: Average CPU used per execution was 0.0012 seconds.
FINDING 6: 2.8% impact (132 seconds)
------------------------------------
Soft parsing of SQL statements was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 2.8% benefit (132 seconds)
ACTION: Investigate application logic to keep open the frequently used
cursors. Note that cursors are closed by both cursor close calls and
RECOMMENDATION 2: DB Configuration, 2.8% benefit (132 seconds)
ACTION: Consider increasing the maximum number of open cursors a session
can have by increasing the value of parameter "open_cursors".
ACTION: Consider increasing the session cursor cache size by increasing
the value of parameter "session_cached_cursors".
RATIONALE: The value of parameter "open_cursors" was "3000" during the
analysis period.
RATIONALE: The value of parameter "session_cached_cursors" was "400"
during the analysis period.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.