Instance Efficiency Percentages (Target: 100%) |
Event | Efficiency (%) | Comment |
Buffer Nowait | 100.00
| If this ratio is low, check the Buffer Wait Stats section for more detail on which type of block is being contended for. |
Redo Nowait | 100.00 | A value close to 100% indicates minimal time spent waiting for redo logs
to become available, either because the logs are not filling up very often or because the database is able to switch to a new log quickly whenever the current log fills up. |
Buffer Hit  | 80.66
| A low buffer hit ratio does not necessarily mean the cache is too small: it may very well be that potentially valid full-table-scans are artificially
reducing what is otherwise a good ratio. A too-small buffer cache can sometimes be identified by the appearance of write complete waits event indicating hot blocks
(i.e. blocks still being modified) are aging out of the cache while they are still needed; check the Wait Events list for evidence of this event. |
In-Memory Sort | 99.99 | A too low ratio indicates too many disk sorts appearing. One possible
solution could be increasing the sort area/SGA size. |
Library Hit | 99.70
| A low library hit ratio could imply that SQL is prematurely aging out of a too-small shared pool, or that non-shareable SQL is being used.
If the soft parse ratio is also low, check whether there is a parsing issue. |
Soft Parse  | 97.97 | When the soft parse
ratio falls much below 80%, investigate whether you can share SQL by using bind variables or force cursor sharing. But before drawing any conclusions, compare the soft parse
ratio against the actual hard and soft parse rates shown in the Loads Profile. Furthermore, investigate the number of Parse CPU to Parse Elapsed below. |
Execute to Parse  |
92.28 | A low value here indicates that there is no much re-usable SQL (see Soft Parse for possible actions).
Negative values connote that there are more Parses than Executes, which could point to syntactically incorrect SQL statements (or missing privileges). |
Latch Hit | 100.00
| A low value for this ratio indicates a latching problem, whereas a high value is generally good. However, a high latch hit ratio can artificially mask a low
get rate on a specific latch. Cross-check this value with the Top 5 Wait Events to see if latch free is in the list, and refer to the
Latch sections of this report. |
Parse CPU to Parse Elapsed | 91.92 | See Soft Parse above. |
Non-Parse CPU | 95.84 | |
Top 5 SQL ordered by Gets |
End Buffer Gets Treshold: 10000
Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported,
it is possible and valid for the summed total % to exceed 100. If your primary tuning goal is reducing resource usage, start tuning these statements/objects
(CPU) plus SQL by Reads (File IO). |
Buffer Gets | Executions |
Gets per Exec | Total | CPU Time | Elapsed Time | Hash Value |
2,925,319 | 29,846 | 98.0 | 4.4% |
1:53.220 | 1:45.174 | 1686291633 |
|
begin delete ant where rowid in ( select ANT . rowid from ANT ,
AUF where AUF . STSID not in ( 'EIN' , 'FGB' , 'IAB' ) and AUF .
AUTID = 1 and AUF . AUFID = ANT . AUFID and ANT . STSID = 'EIN'
) ; :COUNT := sql % rowcount ; end ;
|
2,924,320 | 29,840 | 98.0 | 4.4% |
1:48.840 | 1:41.830 | 4177894216 |
|
DELETE ANT WHERE ROWID IN ( SELECT ANT . ROWID FROM ANT , AUF WH
ERE AUF . STSID NOT IN ( 'EIN' , 'FGB' , 'IAB' ) AND AUF . AUTID
= 1 AND AUF . AUFID = ANT . AUFID AND ANT . STSID = 'EIN' )
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
DELETE STATEMENT
| . | RULE
| | | | |
. DELETE
| . |
| | | | |
. . NESTED LOOPS
| . |
| | | | |
. . . VIEW
| . |
| | | | |
. . . . SORT UNIQUE
| . |
| | | | |
. . . . . NESTED LOOPS
| . |
| | | | |
. . . . . . TABLE ACCESS FULL
| FRODO.AUF | ANALYZED
| | | | |
. . . . . . INDEX RANGE SCAN
| FRODO.XIE4ANT | ANALYZED
| | | | |
. . . TABLE ACCESS BY USER ROWID
| FRODO.ANT | ANALYZED
| | | | |
|
1,469,542 | 2,002 | 734.0 | 2.2% |
20.190 | 20.654 | 3324404337 |
|
select 'datapoint: '||count(*) from all_tab_privs where grant
ee = 'PATRON'
|
992,635 | 76,355 | 13.0 | 1.5% |
18.150 | 2:57.012 | 2276398955 |
|
begin :DONE := pw_mgr . watch_processes ; end ;
|
921,044 | 229,056 | 4.0 | 1.4% |
0.370 | 5:56.772 | 1712657913 |
|
begin pw_common . update_timestamp ( :PROCESS_ID ) ; end ;
|
Top 5 SQL ordered by Reads |
End Disk Reads Treshold: 1000 If your primary tuning
goal is to reduce resource usage, start by tuning these statements/objects (File IO) plus SQL by Gets (CPU). |
Pysical Reads | Executions | Reads per Exec | Total |
CPU Time | Elapsed Time | Hash Value |
3,169 | 1 | 3,169.0 | 0.2% |
2.910 | 3.827 | 1971852235 |
|
SELECT /*+NESTED_TABLE_GET_REFS+*/ "PERFSTAT"."STATS$SQL_SUMMARY
".* FROM "PERFSTAT"."STATS$SQL_SUMMARY"
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
SELECT STATEMENT
| . | CHOOSE
| | | | |
. TABLE ACCESS FULL
| PERFSTAT.STATS$SQL_SUMMARY |
| | | | |
|
3,141 | 1 | 3,141.0 | 0.1% |
1.250 | 4.739 | 973039809 |
|
SELECT /*+NESTED_TABLE_GET_REFS+*/ "FRODO"."IMP".* FROM "FRODO
"."IMP"
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
SELECT STATEMENT
| . | CHOOSE
| 852 | | | |
. TABLE ACCESS FULL
| FRODO.IMP | ANALYZED
| 852 | 110,524,207 | 834 | 14,875 k |
|
2,372 | 1 | 2,372.0 | 0.0% |
1.870 | 1.942 | 3835363457 |
|
SELECT /*+NESTED_TABLE_GET_REFS+*/ "FRODO"."INF_BACKUP".* FROM
"FRODO"."INF_BACKUP"
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
SELECT STATEMENT
| . | CHOOSE
| 655 | | | |
. TABLE ACCESS FULL
| FRODO.INF_BACKUP | ANALYZED
| 655 | 68,028,356 | 644 | 15,970 k |
|
2,279 | 1 | 2,279.0 | 0.1% |
2.500 | 3.778 | 1898264937 |
|
SELECT /*+NESTED_TABLE_GET_REFS+*/ "PERFSTAT"."STATS$LATCH".* FR
OM "PERFSTAT"."STATS$LATCH"
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
SELECT STATEMENT
| . | CHOOSE
| | | | |
. TABLE ACCESS FULL
| PERFSTAT.STATS$LATCH |
| | | | |
|
1,928 | 1 | 1,928.0 | 0.0% |
1.050 | 2.568 | 3658914384 |
|
SELECT /*+NESTED_TABLE_GET_REFS+*/ "PERFSTAT"."STATS$SYSSTAT".*
FROM "PERFSTAT"."STATS$SYSSTAT"
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
SELECT STATEMENT
| . | CHOOSE
| | | | |
. TABLE ACCESS FULL
| PERFSTAT.STATS$SYSSTAT |
| | | | |
|
Top 5 SQL ordered by Executions |
End Executions Treshold: 100 Start with tuning these
statements if your primary goal is to increase the response time. |
Executions | Rows Processed | Rows per Exec | CPU per Exec |
Elap per Exec | Hash Value |
459,461 | 0 | 0.0 | 0.000 |
0.000 | 1053795750 |
|
COMMIT
|
229,058 | 229,058 | 1.0 | 0.000 |
0.000 | 876411139 |
|
begin pw_common . set_process_state ( :PROCESS_ID , :STATUS ) ;
end ;
|
229,056 | 4 | 0.0 | 0.000 |
0.000 | 694256415 |
|
UPDATE PRO SET PRO.STSID = :B2, PROAENAM=SYSDATE, PROAENDURCH=US
ER, PROTSN=DECODE(PROTSN,999999999,0,PROTSN+1) WHERE PRO.STSID !
= :B2 AND PRO.PROID = :B1
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
UPDATE STATEMENT
| . | RULE
| | | | |
. UPDATE
| . |
| | | | |
. . TABLE ACCESS BY INDEX ROWID
| FRODO.PRO | ANALYZED
| | | | |
. . . INDEX UNIQUE SCAN
| FRODO.XPKPRO | ANALYZED
| | | | |
|
229,056 | 229,056 | 1.0 | 0.000 |
0.001 | 1712657913 |
|
begin pw_common . update_timestamp ( :PROCESS_ID ) ; end ;
|
229,054 | 229,053 | 1.0 | 0.000 |
0.000 | 853794241 |
|
select to_char(sysdate,'DD.MM.YYYY') ,to_char(sysdate,'HH24:MI:S
S') into :b0,:b1 from dual
|
Top 5 SQL ordered by Parse Calls |
End Parse Calls Treshold: 1000 Consider tuning these
statements/objects, if the percentage of CPU used for parsing is high. Currently, parsing takes avg. 4.16%% of all CPU usage by all sessions. |
Parse Calls | Executions | Total Parses | Hash Value |
29,846 | 29,846 | 0.07% | 2609822319 |
|
lock table AUF in exclusive mode
|
|
Operation | Object |
Optimizer | Cost | CPUCost | IOCost | Size |
DDL STATEMENT
| . | RULE
| | | | |
. TABLE ACCESS FULL
| FRODO.AUF | ANALYZED
| | | | |
|
27,349 | 27,349 | 0.07% | 3867936055 |
|
commit
|
6,626 | 6,626 | 0.02% | 3997906522 |
|
select user# from sys.user$ where name = 'OUTLN'
|
6,612 | 6,612 | 0.02% | 1356713530 |
|
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
|
6,373 | 6,373 | 0.02% | 2176627595 |
|
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'GERMA
NY' NLS_CURRENCY= '¤' NLS_ISO_CURRENCY= 'GERMANY' NLS_NUMERIC_CH
ARACTERS= ',.' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD.MM
.YYYY' NLS_DATE_LANGUAGE= 'GERMAN' NLS_SORT= 'BINARY' TIME_ZONE
= '+02:00' NLS_DUAL_CURRENCY = '¤' NLS_TIME_FORMAT = 'HH24:MI:SS
XFF' NLS_TIMESTAMP_FORMAT = 'DD.MM.RR HH24:MI:SSXFF' NLS_TIME_TZ
_FORMAT = 'HH24:MI:SSXFF TZR' NLS_TIMESTAMP_TZ_FORMAT = 'DD.MM.R
R HH24:MI:SSXFF TZR'
|
Initialization Parameters (init.ora) |
Parameter Name | Begin Value | End Value (if different) |
O7_DICTIONARY_ACCESSIBILITY | TRUE | |
audit_file_dest | /database/oradb_dump/audit | |
audit_trail | NONE | |
background_dump_dest | /database/oradb_dump/bdump | |
compatible | 9.2.0.0.0 | |
control_files | /database/config1/oradb/control/oradb_1.ctl, /database/config2/oradb/control/oradb_2.ctl, /database/config3/oradb/control/oradb_3.ctl | |
core_dump_dest | /database/oradb_dump/cdump | |
db_block_buffers | 12800 | |
db_block_size | 8192 | |
db_domain | domain.com | |
db_file_multiblock_read_count | 8 | |
db_files | 1022 | |
db_name | oradb | |
dml_locks | 100 | |
global_names | FALSE | |
job_queue_processes | 1 | |
log_buffer | 32768 | |
log_checkpoint_interval | 10000000 | |
log_checkpoint_timeout | 0 | |
max_dump_file_size | 1000M | |
nls_currency | DM | |
nls_date_format | DD.MM.YYYY | |
nls_date_language | GERMAN | |
nls_iso_currency | GERMANY | |
nls_language | ENGLISH | |
nls_sort | GERMAN | |
nls_territory | GERMANY | |
open_cursors | 500 | |
optimizer_mode | CHOOSE | |
oracle_trace_collection_path | /database/oradb_dump/trace | |
processes | 50 | |
resource_limit | TRUE | |
shared_pool_reserved_size | 2500000 | |
shared_pool_size | 50331648 | |
sort_area_size | 1048576 | |
timed_statistics | TRUE | |
user_dump_dest | /database/oradb_dump/udump | |
utl_file_dir | /database/oradb_dump/utl | |