Excesso de Leituras Físicas

[mpvargasParticipante]

DETAILED ADDM REPORT

FINDING 1: 17% impact (957 seconds)

Waits on event “log file sync” while performing COMMIT and ROLLBACK operations
were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 17% benefit (957 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 1792 transactions per minute with an average redo size of 4854 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 17% benefit (957 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 4K and the average time per write was 14 milliseconds.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class “Commit” was consuming significant database time. (17% impact [957 seconds])

FINDING 2: 13% impact (736 seconds)

Wait class “User I/O” was consuming significant database time.

NO RECOMMENDATIONS AVAILABLE

ADDITIONAL INFORMATION:
Waits for I/O to temporary tablespaces were not consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than
expected.
The SGA was adequately sized.

FINDING 3: 8,5% impact (480 seconds)

Time spent on the CPU by the instance was responsible for a substantial part of database time.

RECOMMENDATION 1: Application Analysis, 8,5% benefit (480 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please refer to other findings in this task about parsing for further details.

ADDITIONAL INFORMATION:
The instance spent significant time on CPU. However, there were no
predominant SQL statements responsible for the CPU load.

FINDING 4: 7,5% impact (428 seconds)

Individual database segments responsible for significant user I/O wait were found.

RECOMMENDATION 1: Segment Tuning, 7,5% benefit (428 seconds)
ACTION: Run “Segment Advisor” on TABLE “MSIGA.CT2010” with object id 58714.
RELEVANT OBJECT: database object with id 58714
ACTION: Investigate application logic involving I/O on TABLE
“MSIGA.CT2010” with object id 58714.
RELEVANT OBJECT: database object with id 58714
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 446689 physical reads, 818 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID “8tfdfwskvh3b1” spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8tfdfwskvh3b1

  RATIONALE: The SQL statement with SQL_ID "2jyqz480z40k3" spent 
     significant time waiting for User I/O on the hot object.
     RELEVANT OBJECT: SQL statement with SQL_ID 2jyqz480z40k3

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class “User I/O” was consuming significant database time. (13% impact [736 seconds])
INFO: Waits for I/O to temporary tablespaces were not consuming
significant database time.
The throughput of the I/O subsystem was not significantly lower
than expected.
The SGA was adequately sized.

FINDING 5: 7,1% impact (403 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, 7,1% benefit (403 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 85 SQL statements with PLAN_HASH_VALUE 2502276886
were found to be using literals. An example is SQL statement with
SQL_ID “16xj2j1r64r4x” .
RELEVANT OBJECT: SQL statement with SQL_ID 16xj2j1r64r4x and
PLAN_HASH 2502276886
SELECT /+ */ R_E_C_N_O_ FROM SE5010 WHERE E5_FILIAL = ’04’ AND
E5_RECPAG = ‘R’ AND E5_DATA = ‘20080604’ AND E5_NUMCHEQ = ‘ ‘ AND E5_DOCUMEN = ‘ ‘ AND E5_PREFIXO = ‘GRA’ AND
E5_NUMERO > ‘DBUWG6’ AND D_E_L_E_T_ != ‘‘ AND (E5_DATA >= ‘20080601’ AND E5_DATA ‘C’ AND INSTR(( ‘PA/RA/BA/VL/V2/DC/D2/JR/J2/MT/M2/CM/C2/AP/EP/PE/RF/IF/CP/TL/ES/TR/DB/OD/LJ/E2/TE/PE ‘ ),( E5_TIPODOC ))>0 AND E5_FILIAL = ’04’) ORDER BY E5_FILIAL,E5_RECPAG,E5_DATA,E5_NUMCHEQ,E5_DOCUMEN,E5_PREFIXO,E5_NUMER
O,E5_PARCELA,E5_TIPO,E5_CLIFOR,E5_LOJA,E5_SEQ,R_E_C_N_O_
RATIONALE: At least 85 SQL statements with PLAN_HASH_VALUE 2502276886 were found to be using literals. An example is SQL statement with
SQL_ID “gdscx9v0cxtqj” .
RELEVANT OBJECT: SQL statement with SQL_ID gdscx9v0cxtqj and
PLAN_HASH 2502276886
SELECT /+ */ R_E_C_N_O_ FROM SE5010 WHERE E5_FILIAL = ’04’ AND E5_RECPAG = ‘R’ AND E5_DATA = ‘20080604’ AND E5_NUMCHEQ = ‘ ‘ AND E5_DOCUMEN = ‘ ‘ AND E5_PREFIXO = ‘GRA’ AND E5_NUMERO > ‘DBUWLU’ AND D_E_L_E_T_ != ‘‘ AND (E5_DATA >= ‘20080601’ AND E5_DATA ‘C’ AND INSTR(( ‘PA/RA/BA/VL/V2/DC/D2/JR/J2/MT/M2/CM/C2/AP/EP/PE/RF/IF/CP/TL/ES/TR/DB/OD/LJ/E2/TE/PE ‘ ),( E5_TIPODOC ))>0 AND E5_FILIAL = ’04’) ORDER BY E5_FILIAL,E5_RECPAG,E5_DATA,E5_NUMCHEQ,E5_DOCUMEN,E5_PREFIXO,E5_NUMER
O,E5_PARCELA,E5_TIPO,E5_CLIFOR,E5_LOJA,E5_SEQ,R_E_C_N_O_
RATIONALE: At least 6 SQL statements with PLAN_HASH_VALUE 1045956401 were found to be using literals. Look in V$SQL for examples of such SQL statements.
RATIONALE: At least 5 SQL statements with PLAN_HASH_VALUE 588552390 were found to be using literals. Look in V$SQL for examples of such SQL statements.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (7,6% impact [432 seconds])

FINDING 6: 2,6% impact (147 seconds)

Soft parsing of SQL statements was consuming significant database time.

RECOMMENDATION 1: Application Analysis, 2,6% benefit (147 seconds)
ACTION: Investigate application logic to keep open the frequently used cursors. Note that cursors are closed by both cursor close calls and
session disconnects.

RECOMMENDATION 2: DB Configuration, 2,6% benefit (147 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 “300” during the analysis period.
RATIONALE: The value of parameter “session_cached_cursors” was “20” during the analysis period.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      ADDITIONAL INFORMATION
      ----------------------

Wait class “Application” 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.
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.