addmrpt_1_50348_5034 分析

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.