|
|
15:53:18 SYS@ prod>SELECT dbms_advisor.get_task_report('DEMO_ADDM03','TEXT', 'ALL') FROM DUAL;
DETAILED ADDM REPORT FOR TASK 'DEMO_ADDM03' WITH ID 1012
--------------------------------------------------------
Analysis Period: 15-AUG-2014 from 15:40:18 to 15:42:39
Database ID/Instance: 199802235/1
Database/Instance Names: PROD/prod
Host Name: rh55
Database Version: 10.2.0.1.0
Snapshot Range: from 192 to 193
Database Time: 305 seconds
Average Database Load: 2.2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (305 seconds)
------------------------------------
Host CPU was a bottleneck and the instance was consuming 88% of the host CPU.
All wait times will be inflated by wait for CPU.
RECOMMENDATION 1: Host Configuration, 100% benefit (305 seconds)
ACTION: Consider adding more CPUs to the host or adding instances
serving the database on other hosts.
ACTION: Also consider using Oracle Database Resource Manager to
prioritize the workload from various consumer groups.
RECOMMENDATION 2: Application Analysis, 33% benefit (101 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please
refer to other findings in this task about parsing for further
details.
ADDITIONAL INFORMATION:
Host CPU consumption was 100%. CPU runqueue statistics are not
available from the host's OS. This disables ADDM's ability to estimate
the impact of this finding. The instance spent significant time on CPU.
However, there were no predominant SQL statements responsible for the
CPU load.
FINDING 2: 96% impact (294 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 92% benefit (280 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "0d4kcvj32z62p". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 0d4kcvj32z62p
begin
for i in 1..1000000 loop
execute immediate 'insert into scott.t1 values ('||i||')';
end loop;
end;
RECOMMENDATION 2: SQL Tuning, 2.5% benefit (8 seconds)
ACTION: Investigate the SQL statement with SQL_ID "7ng34ruy5awxq" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 7ng34ruy5awxq and
PLAN_HASH 3992920156
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prop
erty,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.
lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataob
j#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i
.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0)
,nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres
$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cacheh
it,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled,
min(cols) unicols,min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where
obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
RATIONALE: SQL statement with SQL_ID "7ng34ruy5awxq" was executed 596
times and had an average elapsed time of 0.012 seconds.
RECOMMENDATION 3: SQL Tuning, 2.1% benefit (6 seconds)
ACTION: Investigate the SQL statement with SQL_ID "0k8522rmdzg4k" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 0k8522rmdzg4k and
PLAN_HASH 2057665657
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and
privilege#>0
RECOMMENDATION 4: SQL Tuning, 2% benefit (6 seconds)
ACTION: Use bigger fetch arrays while fetching results from the SELECT
statement with SQL_ID "7ng34ruy5awxq".
RELEVANT OBJECT: SQL statement with SQL_ID 7ng34ruy5awxq and
PLAN_HASH 3992920156
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prop
erty,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.
lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataob
j#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i
.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0)
,nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres
$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cacheh
it,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled,
min(cols) unicols,min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where
obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
FINDING 3: 49% impact (149 seconds)
-----------------------------------
Soft parsing of SQL statements was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 49% benefit (149 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, 49% benefit (149 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.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Contention for latches related to the shared pool was consuming
significant database time. (12% impact [36 seconds])
INFO: Waits for "latch: library cache" amounted to 11% of database
time.
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (13% impact [39 seconds])
FINDING 4: 32% impact (97 seconds)
----------------------------------
Hard parsing of SQL statements was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Hard parses due to cursor environment mismatch were not 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 SGA was adequately sized.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Contention for latches related to the shared pool was consuming
significant database time. (12% impact [36 seconds])
INFO: Waits for "latch: library cache" amounted to 11% of database
time.
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (13% impact [39 seconds])
FINDING 5: 2.6% impact (8 seconds)
----------------------------------
Session connect and disconnect calls were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 2.6% benefit (8 seconds)
ACTION: Investigate application logic for possible reduction of connect
and disconnect calls. For example, you might use a connection pool
scheme in the middle tier.
FINDING 6: 2.2% impact (7 seconds)
----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 2.2% benefit (7 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "0d4kcvj32z62p". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 0d4kcvj32z62p
begin
for i in 1..1000000 loop
execute immediate 'insert into scott.t1 values ('||i||')';
end loop;
end;
FINDING 7: 1.8% impact (5 seconds)
----------------------------------
Buffer cache writes due to small log files were consuming significant database
time.
RECOMMENDATION 1: DB Configuration, 1.8% benefit (5 seconds)
ACTION: Increase the size of the log files to 188 M to hold at least 20
minutes of redo information.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (1% impact [3 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (2.1% impact [6 seconds])
FINDING 8: 1.2% impact (4 seconds)
----------------------------------
Undo I/O was a significant portion (59%) of the total database I/O.
NO RECOMMENDATIONS AVAILABLE
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (1% impact [3 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (2.1% impact [6 seconds])
FINDING 9: 1% impact (3 seconds)
--------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 1% benefit (3 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using the
SAME methodology. You might also need to increase the number of disks
for better performance. Alternatively, consider using Oracle's
Automatic Storage Management solution.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 18 K per second for reads and 74 K per second for
writes. The average response time for single block reads was 19
milliseconds.
RECOMMENDATION 2: Host Configuration, 1% benefit (3 seconds)
ACTION: The performance of file
/u01/app/oracle/oradata/prod/system01.dbf was significantly worse
than other files. If striping all files using the SAME methodology is
not possible, consider striping this file over multiple disks.
RELEVANT OBJECT: database file
"/u01/app/oracle/oradata/prod/system01.dbf"
RATIONALE: The average response time for single block reads for this
file was 20 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(2.1% impact [6 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TERMINOLOGY
-----------
DATABASE TIME: This is the ADDM's measurement of throughput. From the user's
point of view: this is the total amount of time spent by users waiting for
a response from the database after issuing a call (not including
networking). From the database instance point of view: this is the total
time spent by forground processes waiting for a database resource (e.g.,
read I/O), running on the CPU and waiting for a free CPU (run-queue). The
target of ADDM analysis is to reduce this metric as much as possible,
thereby reducing the instance's response time.
AVERAGE DATABASE LOAD: At any given time we can count how many users (also
called 'Active Sessions') are waiting for an answer from the instance. This
is the ADDM's measurement for instance load. The 'Average Database Load' is
the average of the the load measurement taken over the entire analysis
period. We get this number by dividing the 'Database Time' by the analysis
period. For example, if the analysis period is 30 minutes and the 'Database
Time' is 90 minutes, we have an average of 3 users waiting for a response.
IMPACT: Each finding has an 'Impact' associated with it. The impact is the
portion of the 'Database Time' the finding deals with. If we assume that
the problem described by the finding is completely solved, then the
'Database Time' will be reduced by the amount of the 'Impact'.
BENEFIT: Each recommendation has a 'benefit' associated with it. The ADDM
analysis estimates that the 'Database Time' can be reduced by the 'benefit'
amount if all the actions of the recommendation are performed.
Elapsed: 00:00:00.51
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-9-11 10:39:59
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡