Performance analysis report for the database ONDA01
generated by ONDATAPERF Premium version 3.2.3

Copyright (C) 2002-2010 Ondatafine SARL. All Rights Reserved
www.ondataperf.com

ONDATAPERF allows us to analyze or monitor the performance of a Oracle database. The methodology is quite simple, it makes a downward analysis over the response time of the Oracle waiters, the output is composed of graphs, charts and commentaries, that make it possible to quickly highlight the causes of performance bottlenecks.
1 - Description of the Oracle Capture

InstanceONDA01
DatabaseONDA01
Oracle Version920 (64 bits)
CPU TypeUltraSparc 1200Mhz
Type of captureSTATSPACK
Number of report(s) 24
Starting date 14-Jan-08 at 00:00
Ending date 15-Jan-08 at 00:00

2 - Performance results

2.1 - Global Performance Grade

This grade isn't a benchmark, it was determinated from the analysis of the found bottlenecks in the response time of the Oracle instance for this given capture.

2.2 - Bottleneck(s) identification


Green LIGHT => No bottleneck *** Yellow LIGHT => Small bottleneck *** Red LIGHT => Bottleneck


Oracle instance

Queries (SQL)

Network

IO

Operating System

3 - Performance analysis report

3.1 - Analysis of response time

3.1.1 Response time in hundredths of a second


The chart above is a representation of the response time for all Oracle process in the interval ( Hour:Minute ).

Activity not constant, high activity detected for track(s) :14-Jan-08 12:00, 14-Jan-08 17:00


The response time exceeded the alert threshold

The alert zone (=8632800cs) is calculated with the elapsed time of snapshot (=3597s) and the number of CPU (=24)




3.1.2 Response time breakdown (CPU/WAIT)



Response time = CPU TIME + WAIT TIME :
- CPU TIME is the time the processor takes to make service.
- WAIT TIME is a wait event such as lock,IO,network,latch,...
The two (2) charts above represent the quantity CPU TIME / WAIT TIME with respect to response time.

WAIT TIME is high (%CPU=25.89 %WAIT=74.11) for tracks with high activity. WAIT TIME is less than the CPU TIME but still large enough(%CPU=50.49 %WAIT=49.51) for other tracks.

IMPORTANT: The WAIT part is detailed in the section "WAIT breakdown", and the CPU part at the following section "CPU breakdown".

3.1.3 CPU breakdown



CPU TIME is the sum of the following 3 times :
- CPU TIME PARSE is the time the cpu takes to make parsing
- CPU TIME RECURSIVE is the sum of time for call recursive and the time for execution of the PL/SQL ( buffer get,...).
- CPU TIME OTHER is the remainder and time for execution of the SQL (buffer get,...).
The two (2) charts above represent the quantity of PARSE / RECURSIVE / OTHER with respect to CPU TIME

CPU PARSE is low for the tracks with high activity. CPU PARSE is low for other tracks.

IMPORTANT: CPU OTHER and RECURSIVE are detailed at the section "TOP MOST queries with high cost with respect to CPU TIME", and CPU PARSE at the following section "Parse to execute ratio"




3.1.4 Parse to execute ratio



Parsing/Execution is ok for tracks with high activity. Parsing/Execution is ok for other tracks.

3.1.5 TOP MOST queries with high cost with respect to CPU TIME

The table and charts below show the most expensive queries raised in consumption of CPU TIME classified in the descending order by 'CPU TIME':


% CPU TimeSqlidBuffer GetExecutionCPU TimeElapsed TimeQuery
32.603493057300 1424550808 593060 66858.54 65988.33 SELECT col1_name, code, max(id_ondata) as maxid_ondata FROM TAB1_ONDATAPERF01 WHERE fine = :"SYS_B_0" group by col1_name, code
8.8227777554 56051092 577 18081.52 50478.84 SELECT HOLDE_ID FROM TAB_ONDATAPERF05_ARCHIV WHERE ((STATUS = :1) OR (STATUS is NULL)) AND ( (:"SYS_B_0" = :"SYS_B_1") )
4.68932442 119593961 34976 9600.70 79624.93 SELECT champs1 , champ_vol , date_conserver FROM Tab_odp_recepdataArchive WHERE ( ( date_conserver = ( SELECT MAX(ReserverecDate) FROM TAB_ONDATAPERF006Archive WHERE temps_informatiq < = :1 AND date_conserver < = :2 AND champs1 = :3 AND champ_vol = :4 ) AND champs1 = :5 AND champ_vol = :6 ) )
4.032438688645 193572059 667327 8264.97 8203.70 SELECT col1_name, code, max(id_ondata) as maxid_ondata FROM TAB_ondataperf03_systeme WHERE fine = :"SYS_B_0" group by col1_name, code
3.521365669271 148350117 97365 7229.98 7350.87 DELETE Evenement_site_http_done WHERE fine=:"SYS_B_0" AND insDate < = sysdate - :1
2.874021572787 154946059 62477 5879.55 5959.32 DELETE TAB1_ONDATAPERF01 WHERE fine=:"SYS_B_0" AND odp_ins < = sysdate - :1
2.84701818890 161991026 9275948 5819.15 10903.84 INSERT into TABLE_ONDATAPERF6_ARCHIVES__2 (ARCHIVE_VALUE_GARDEURID, NODEID, COLUMN_ST, seq_001, LAG, UDESC6_U, TREASURY, col3_d, col1_d, POSITIONCURRENCYID, LONG_VALUE_STRI)values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 )
2.752196401642 51032392 82443 5638.02 5762.82 SELECT ODP FROM TAB_ODPERF020 WHERE ((ACTIVATION =:1)) AND ( (:"SYS_B_0" = :"SYS_B_1") )
2.592264918283 108288448 8171436 5312.31 11133.56 INSERT into ODP_TAB_VENTE_ET_PRIX_RESULTS (UNIQUE_INDIC_STRESULTID, UDESC2_U, PREMIUM, UDESC7_U, PRICEVECTOR_INLINE, FIELD1_AR, FIELD2_ZE, FIELD3_VALUE, FIELD_SE, FIELDLST, FIELD5_EXPIRY_ENTRE, OD, SYSCALL_END, EXCDEP, FIELD7_IDENTS_TRACES, COL1_SERVEUR1_RECORDED, VALEUR_RECURRENTE_ADDON, ODP_FLG
2.282337371380 114112834 96534 4677.60 4787.15 SELECT data, col1_name,code FROM Evenement_site_http_done WHERE rowid = (SELECT rw FROM (SELECT rowid rw FROM Evenement_site_http_done WHERE fine=:"SYS_B_0" AND odp_ins < =SYSDATE - :1 ORDER BY id_ondata ASC)
2.212503437411 88048853 62090 4539.13 4746.99 SELECT data, col1_name,code FROM TAB1_ONDATAPERF01 WHERE rowid = (SELECT rw FROM (SELECT rowid rw FROM TAB1_ONDATAPERF01 WHERE fine=:"SYS_B_0" AND odp_ins < = SYSDATE - :1 ORDER BY id_ondata ASC) WHERE
2.031574314814? 131 4157.97 12478.07 SELECT count (:"SYS_B_0") FROM TAB_ONDATAPERF05_ARCHIV WHERE status = :"SYS_B_1"
1.841287238284 146220781 351598 3774.31 31001.83 INSERT into TAB_ODP_INSTITUT_MOIS (field_reception_ide, PARAMETERDATE, DUTER8_ANNEE, VAL_AJOUT_SURPR, DATE_STOKEE, BATI_ANNUELMENTITYID, methode_acces, field_receERULESID, code_descrip, TIMESTAMP, parameterTimeStamp, BATCHNUMBER, COLUMN_STEFFECT) VALUES (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :1
1.792793845901 54239589 370 3667.62 3920.67 SELECT /*+first_rows*/ TAB_ONDATAPERF_TRIGID, UDESC1_U, PRODUCTPRICINGRESULTID, UDESC3_U, UDESC4_U, PAYOFFID_CURRENCYID, UDESC6_U, LONG_VALUE_STRI FROM T_P_PAYOFFPRICINGRESULT_ISL JOIN T_IFRPRODUCTPRICINGRESULT_TMP ON T_P_PAYOFFPRICINGRESULT_ISL.UDESC2_U = T_IFRPRODUCTPRICINGRESULT_
1.403803632547 164128080 8020 2866.43 2885.88 UPDATE TAB_ONDATAPERF02_RANGES SET id_ondata= :1, enregistrement_sequenceshr= :2, fine= :"SYS_B_0" WHERE id_ondata=:3 AND fine!=:"SYS_B_1"

The queries in orange in the table above have an excessive CPU cost compared to the number of executions. The execution plan of these queries must be checked.


NOTE: If your database is Oracle version 9i or higher, then the execution plan of these queries can be found in the STATSPACK tables or AWR tables. In that case, you can use the STATSPACK script (sprepsql.sql) or AWR script (awrsqrpt.sql).These scripts are located in the directory '$ORACLE_HOME/rdbms/admin'. You will be able to view the execution plan of the queries from the table above.

3.1.6 WAIT breakdown (TOP MOST important WAITS)






The WAIT TIME is the sum of the time of all events (except idle wait).
The two (2) charts above represent the breakdown with respect to WAIT TIME.

Far too much waiting with respect to response time has been identified. You can follow the advices provided in the table below, to reduce these waits

The table below is an accumulation of event times (by decreasing order):

% Wait TimeWait EventMin Avg Wait(ms)Max Avg Wait(ms)DescriptionAdvice
34.77db file sequential read 3.0012.00Event when access by ROWID of the table in DATAFILES.Tune SQL, speed up disks (5-15ms), increase buffer cache
16.73db file scattered read 8.0017.00Event when FULL SCAN of the table in DATAFILES.Tune SQL, add indexes, speed up disks (5-15ms)
11.93log file sync 7.0024.00When a user session commits, the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log fileCommit less, put redo logs on faster disks (striping).
11.89latch free 1.0055.00The process waits for a latch that is currently busy (held by another process).See chapter further describing the latchs
5.42enqueue 18.001059.00Wait during a transactional lock.See chapter further describing the segments concerned
4.14log file parallel write 3.005.00Writing redo records to the redo log files from the log buffer.Increase the scalability of redo log file (striping).
3.81buffer busy waits 2.0015.00Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another sessionSee chapter further describing the segments concerned
3.80resmgr:waiting in end wait 172.00666.00Env Resource Manager : Process is now runable but there are insufficient resources to execute.Tune latchs or add CPU
3.78db file parallel write 34.00100.00The db file parallel write Oracle metric occurs when the process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete. Tune sql, tune io subsystem, increase buffer cache
1.21direct path write 13.0087.00Write in direct mode (bypass SGA) in DATAFILES or TEMPFILES.Check sorts disk in the chapter further
0.98direct path read (lob) 1.006.00Read LOB in DATAFILES.
0.88SQL*Net more data to client 0.000.00The process server is sending more data/messages to the client.Check network delays and connections.
0.66log file sequential read 30.0053.00Waiting for the read from this logfile to return. This is used to read redo records from the log file.Increase the scalability of redo log file (striping).

3.1.7 TOP latches with high cost of 'latch free', 'latch:...'

The latches are internal locks(Oracle) to protect the memory of the instance against concurring accesses.

Table of the latches with the most cumulated sleeps (by decreasing order on 'Sleeps'):
* 'Gets' : Number of successful accesses to a buffer
* 'Misses' : Number of failed accesses to a buffer
* 'Sleeps' : Number of waits ('latch free','latch:...') to access to a buffer

% SleepLatch nameSleepsGetsMissesDescriptionAdvice
35.54cache buffers chains 948988 14097542612 11283661This latch is acquired when a block is accessed and pinned. A block is pinned because something is changing it. This latch prevents another process from changing the block at the same time See the next chapter "TOP of the most requested segments in memory"
23.82resmgr:resource group CPU 635890 29299500 5803928No information
8.43library cache 225109 482736298 3767243This latch is acquired to add and parse a new SQL code statement into the library cache, for future sharing.Use bind variables or CURSOR_SHARING
5.73undo global data 152975 198752343 13727710This latch serializes the access to the Undo segment information in the SGA
4.85enqueues 129364 307294411 9515302No information
4.21library cache pin 112518 354203323 1310030A pin is acquired after a library cache load lock latch is acquired. A wait on this latch occurs when an SQL code statement already in the library cache is executed again
4.00row cache objects 106671 156804585 1877133The row cache objects latch contention usually means there is contention in the data dictionary. This problem may also be a symptom of excessive parsing of SQL statements that depend on public synonyms.Increases the shared pool size

There are Far too much waiting on latches. You can follow the advices of the table above, and if necessary, put a collection to capture queries to the origin of latches.
Below an example of SQL to identify the queries to the origin of latches:

set pagesize 1000 linesize 170
col SID format 9999999
col NAME format A30
col SQL_TEXT format A300
col MACHINE format A30
col PROGRAM format A30
col USERNAME format A24
SELECT distinct W.SID, L.NAME, B.MACHINE, B.PROGRAM, B.USERNAME, A.SQL_TEXT
FROM V$SESSION_WAIT W,V$LATCHNAME L,V$SESSION B, V$SQL A
WHERE W.EVENT LIKE 'latch%' AND W.P2=L.LATCH# AND W.SID=B.SID AND B.SQL_HASH_VALUE=A.HASH_VALUE;

3.1.8 TOP MOST requested segments in memory (SGA)

This the list of segments (table,index,...) most requested in shared memory.

Table of segments with the most cumulated reads in shared memory (by decreasing order on 'Reads'):
* 'Reads' : Numbers of reading blocks in shared memory
* 'Owner' : owner of the segment
* 'Tablespace' : Tablespace of the segment
* 'Object' : Name of the segment
* 'Sub-Object' : Name of the segment partition
* 'Type' : Type of segment

% ReadOwnerTablespaceObjectSub-objectTypeReads
42.20APPLI_ODP03ODP_TABSPE_DATA0199_INDEXPK_U_ODP000001n/aINDEX 2621721072
24.30APPLI_ODP01DATA_ODP_1TAB1D_DOMAIN_CLUSTER_PKn/aINDEX 1509314512
20.96APPLI_ODP03ODP_TABSPE_DATA0199_DATATAB_ONDATAPERF_08TAB_ONDATAPERF_08_FLG_STTABLE PARTITION 1302035072
4.73APPLI_ODP02ODPONDATAPERF_PK_0000000010n/aINDEX 294099152
1.25APPLI_ODP03VAR_YACC_LEXIUM_DATATAB_ODP_INSTITUT_MOISn/aTABLE 77754752
1.03APPLI_ODP01DATA_ODP_1ONDATAPERF_T_I_ST_PKn/aINDEX 63754528
0.91APPLI_ODP01ODP_VALEUR_LGONDATAPERF_T_I_STARCHIVEn/aTABLE 56270704

There is far too much waiting on the latch "cache buffer chain". You must improve the queries on the objects of the table above. Reduce the number of buffer get, use indexes with reversed keys for sequential primary key. Increase the concurrency access on the blocks with parameter INITRANS. Improve the data model. Put a collection to capture queries to the origin of this latch.

3.1.9 TOP MOST segments requested 'buffer busy wait' event

This list show segments (table,index,...) most requested 'buffer busy wait' event

Table of the segments with most cumulated waits (by decreasing order on 'Waits'):
* 'Waits' : Number of the waits
* 'Owner' : owner of the segment
* 'Tablespace' : Tablespace of the segment
* 'Object' : Name of the segment
* 'Sub-Object' : Name of the segment partition
* 'Type' : Type of segment

% WaitOwnerTablespaceObjectSub-objectTypeWaits
54.44APPLI_ODP01ODP_VALEUR_LGONDATAPERF_T_I_STARCHIVEn/aTABLE 1731208
9.44APPLI_ODP03ODP_TABSPE_DATA01_INDEXIDX_H_ARCHIVE_VALUE_GARDEUR_IT_H_INDICATEUR_USAG_FLG_STINDEX PARTITION 300115
7.90APPLI_ODP03ODP_TABSPE_DATA01_INDEXIDX_H_UNIQUE_INDIC_STRESULT_IT_H_UNIQUE_INDIC_ST_FLG_STINDEX PARTITION 251090
5.58APPLI_ODP01DATA_ODP_1I_ONDATAPERF_T_I_STARCHIVEn/aINDEX 177285
4.38APPLI_ODP03ONDATAT_P_TAB_ONDATAPERF_TRIG_ISLPARTITION_ODPXTABLE PARTITION 139266
4.02APPLI_ODP03VAR_YACC_LEXIUM_DATATAB_ODP_INSTITUT_MOISn/aTABLE 127761
2.84APPLI_ODP01ONDATA_INDEXTAB_ONDATAPERF05_ARCHIVn/aTABLE 90189

There is a little waiting on buffer busy wait on the segments of the table above. Applying the rule below to reduce waiting on these segments.
* For rollback segments, use an undo tablespace by setting undo_tablespace parameter in the init.ora of the instance, if this is not done.
* For others segments like table, index and partition, adjust the number of freelists if these segments belonging to a tablespace with manual space management, or move these segments to a tablespace with automatic space management.

3.1.10 TOP MOST segments requested 'enqueue','enq:...' event

This list shows the segments (table,index,...) most requested 'enqueue','enq:...' event.

Table of the segments with most cumulated waits (by decreasing order on 'Waits'):
* 'Waits' : Number of the waits
* 'Owner' : owner of the segment
* 'Tablespace' : Tablespace of the segment
* 'Object' : Name of the segment
* 'Sub-Object' : Name of the segment partition
* 'Type' : Type of segment

% WaitOwnerTablespaceObjectSub-objectTypeWaits
30.39APPLI_ODP03VAR_YACC_LEXIUM_INDEXPK_ONDATAPERF_TAB1_OIDn/aINDEX 45893
23.29APPLI_ODP03ODP_TABSPE_DATA01_INDEXIDX_H_UNIQUE_INDIC_STRESULT_IT_H_UNIQUE_INDIC_ST_FLG_STINDEX PARTITION 35167
23.17APPLI_ODP03ODP_TABSPE_DATA01_INDEXIDX_H_ARCHIVE_VALUE_GARDEUR_IT_H_INDICATEUR_USAG_FLG_STINDEX PARTITION 34981
11.11APPLI_ODP03ODP_TABSPE_DATA0199_INDEXPK_U_ODP000001n/aINDEX 16772
4.82APPLI_ODP03ODP_TABSPE_DATA01_INDEXPK_H_ARCHIVE_VALUE_GARDEURn/aINDEX 7282
3.88APPLI_ODP03ODP_TABSPE_DATA01_INDEXPK_H_UNIQUE_INDIC_STRESULTn/aINDEX 5856
0.93APPLI_ODP03ODP_DATA_SYSTEM01_BIG_INDEXPK_ONDATAFINE_ODP_FGn/aINDEX 1405

There is a little waiting on enqueue on the segments of the table above. Increase commits intermediaries on these segments.

3.1.11 TOP MOST queries with high cost with respect to WAIT TIME

The table and chart below show the list of the most expensive queries with respect to Waiting time classified in the descending order by 'WAIT TIME':


% WAIT TimeSqlidDisk ReadExecutionCPU TimeWAIT TimeQuery
32.49932442 13062479 34976 9600.70 70024.23 SELECT champs1 , champ_vol , date_conserver FROM Tab_odp_recepdataArchive WHERE ( ( date_conserver = ( SELECT MAX(ReserverecDate) FROM TAB_ONDATAPERF006Archive WHERE temps_informatiq < = :1 AND date_conserver < = :2 AND champs1 = :3 AND champ_vol = :4 ) AND champs1 = :5 AND champ_vol = :6 ) )
15.0327777554 66989951 577 18081.52 32397.32 SELECT HOLDE_ID FROM TAB_ONDATAPERF05_ARCHIV WHERE ((STATUS = :1) OR (STATUS is NULL)) AND ( (:"SYS_B_0" = :"SYS_B_1") )
12.631287238284 3549 351598 3774.31 27227.52 INSERT into TAB_ODP_INSTITUT_MOIS (field_reception_ide, PARAMETERDATE, DUTER8_ANNEE, VAL_AJOUT_SURPR, DATE_STOKEE, BATI_ANNUELMENTITYID, methode_acces, field_receERULESID, code_descrip, TIMESTAMP, parameterTimeStamp, BATCHNUMBER, COLUMN_STEFFECT) VALUES (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :1
3.861574314814 15013825 131 4157.97 8320.10 SELECT count (:"SYS_B_0") FROM TAB_ONDATAPERF05_ARCHIV WHERE status = :"SYS_B_1"
3.853268545449 913044 550878 1244.22 8287.37 SELECT VENTE_RESULTAT_OD_IDENT, PRODUIT_DESCRIPTION_ET, OPTIONS, CHAMPS_PRODUIT_ID, ADDRESSE_ODP_TCPIP, LIVRE_ODP, WORLD_ODP, SELLINGVALUE, col2_d, col3_d, col4_d, PR, PAYMENTDATE, col6_d, col7_d, col8_d, CASHPRODUCTID_VALUEDATE, TAB_ONDATAPERF_TRIGID, INDICATEUR_USAGRESUL
3.071680262068 4635970 23 1637.14 6614.41 SELECT field_reception_ide FROM TAB_ODP_INSTITUT_MOIS WHERE (print_nmdat = TO_DATE(:1, :"SYS_B_0")) AND (system__Date = TO_DATE(:2, :"SYS_B_1")) AND (date_output_b = TO_DATE(:3, :"SYS_B_2")) AND (methode_acces = :4) AND (coefficient_result IN (:"SYS_B_3"))
2.702264918283 84336 8171436 5312.31 5821.25 INSERT into ODP_TAB_VENTE_ET_PRIX_RESULTS (UNIQUE_INDIC_STRESULTID, UDESC2_U, PREMIUM, UDESC7_U, PRICEVECTOR_INLINE, FIELD1_AR, FIELD2_ZE, FIELD3_VALUE, FIELD_SE, FIELDLST, FIELD5_EXPIRY_ENTRE, OD, SYSCALL_END, EXCDEP, FIELD7_IDENTS_TRACES, COL1_SERVEUR1_RECORDED, VALEUR_RECURRENTE_ADDON, ODP_FLG
2.36701818890? 9275948 5819.15 5084.69 INSERT into TABLE_ONDATAPERF6_ARCHIVES__2 (ARCHIVE_VALUE_GARDEURID, NODEID, COLUMN_ST, seq_001, LAG, UDESC6_U, TREASURY, col3_d, col1_d, POSITIONCURRENCYID, LONG_VALUE_STRI)values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 )
2.182725082426 539279 415483 1068.50 4702.82 SELECT ARCHIVE_VALUE_GARDEURID, NODEID, COLUMN_ST, seq_001, LAG, COL1_COUT_FACT_VAR, RECETTES, RESTE_FG, ENREGISTREME, STAT_INDCURRENCYID, LONG_VALUE_STRI FROM TABLE_ONDATAPERF6_ARCHIVES__2 WHERE seq_001 = :1 AND EOM = :"SYS_B_0"
2.113542865164 677730 213000 1127.41 4555.46 SELECT cflag1_o, cflag2_o, cflag3_o, cflag4_o, cflag5_o,cflag6_o, cflag7_o, PRODUCT_cflag3_o, cflag9_o, OBSOLETE_cflag9_o, PARENT_PRODUCT_cflag1_o, PARENT_CASH_cflag1_o, cflag13_o, cflag14_o,PAYMENT_CURRENCY,cflag16_o, cflag17_o, cflag18_o, THEORETICAL_cflag3_o
1.77999197517 1089102 839 1716.46 3812.76 SELECT /*+index(Origine_adres)*/ champs1 FROM Origine_adres WHERE (activeUntil is null) AND (machine_ip_ =:"SYS_B_0" ) AND ((firstConstatableProductId in (SELECT entetes.cle_ident FROM TAB1ODUIT_DATA_USED entetes, TAB_ONDATAPERF11_ARCHI t2 WHERE header1.col_desc1 = t2.col_des2 AND entetes.paypal_records
1.35498617762 440598 325490 991.38 2900.66 SELECT TAB_ONDATAPERF_TRIGID, UDESC1_U, UDESC2_U,UDESC3_U, UDESC4_U, UDESC5_U, PRICEVECTOR_INLINE, PREMIUM, UDESC7_U, SPOT, UDESC8_U, REPO, VOL, UNDERLYINGCURRENCYID, UDESC10_U, UDESC11_U, UNDERLYINGID_REPOSITORYID, UDESC13_U, UDESC14_U, UDESC15_UICE
1.191320351734 293060 1 365.50 2572.97 DELETE from TAB_ODP_INSTITUT_MOIS WHERE (system__Date < (SYSDATE-:"SYS_B_0") AND date_captur is null) OR (date_captur < (SYSDATE -:"SYS_B_1"))
1.143946284183 1425125 48 947.58 2458.52 DELETE /*+ index_ffs(st) */ FROM stats$sqltext st WHERE ( hash_value, text_subset) in (SELECT hash_value, text_subset FROM stats$sqltext minus SELECT hash_value, text_subsetfrom stats$sql_summary)
0.93837563475 240577 835894 674.00 2010.89 SELECT seq_001, day_month FROM TAB_ONDATAPERF04_FIN WHERE (valueEngineResultId = :1) AND (day_month =:2) order by batchPositionasc

The queries in red in the table above have excessive cost with respect reading disc compared to the number of executions. It is essential to check of execution plan.


NOTE: If your database is Oracle version 9i or higher, then the execution plan of these queries can be found in the STATSPACK tables or AWR tables. In that case, you can use the STATSPACK script (sprepsql.sql) or AWR script (awrsqrpt.sql).These scripts are located in the directory '$ORACLE_HOME/rdbms/admin'. You will be able to view the execution plan of the queries from the table above.

3.2 - Complementary analysis

3.2.1 Number of executed queries


The chart above shows the number of executed queries

3.2.2 Average cost of a query



The size of the DATABASE based on the given parameters is : volume DB > 1TB. The chart above presents the cost (in Oracle blocks) of executed queries. The maximum cost is 363 block(s) by execution.

3.2.3 Number of sessions



The chart above shows the maximum number of sessions/connections is 3825 at 14-Jan-08 23:00.

3.2.4 Representation of SORTS


The chart above presents the number of disk sorts and in memory

There is a little sorting on disk. You must use the automatic pga memory management by setting the pga_aggregat_target parameter in place of sort_area_size parameter in init.ora of the instance.

3.2.5 Representation of parallel queries


The chart above presents the amount of parallel queries (only DML). Quite often this type of query within a data warehouse environment or during the computing of statistics.

3.2.6 Representation of IO (logical and physical)



The two (2) charts above represent the IO in SGA and on disk :
- db block gets, consistent gets : Get data block(s) in SGA
- db block changes, consistent changes : Changing data block(s) in SGA
- physical reads : Reading data block(s) from disk
- physical writes : Writing data block(s) to disk
- redo entries : Writing block(s) of redolog

The reading blocks on disks is low (physical read - physical read direct).


3.2.7 Physical reads (on disk)


The chart above is a representation of the readings of Oracle blocks on disk. There is a number of reading blocks on disk that were not in the cache of instance Oracle, those are "physical reads conventional". And a number of reading that have bypassed the cache of instance Oracle, those are "physical reads direct", occurring in the case of sorting, parallel queries, operations on temporary tables, or reading LOB.

The maximum disk reading is high enough: 23.95 MB / second.

3.2.8 TOP MOST tablespaces - most requested in readings


The chart below shows the tablespaces most requested in readings.

The tablespace ODP_VALEUR_LG (average read min 2.20ms and max 14.20ms) is the more requested. Make a striping on the datafiles of the most requested tablespaces.

3.2.9 Physical writes (on disk)


The chart above is a representation of the writing of Oracle blocks on disk. There is a number of writing blocks on disk processed by DBWR to free up memory space in the buffer cache of instance Oracle, those are "physical writes conventionel". And a number of writing that have bypassed the cache of instance Oracle, those are "physical writes direct", occurring in the case of sorting, parallel queries, load in direct mode, operations on temporary tables, or writing LOB.

The maximum disk writing is ok : 5.20 MB / second.

3.2.10 TOP MOST tablespaces - most requested in writings


The chart below shows the tablespaces most requested in writings.

The tablespace UNDOTBS (average buffer wait min 0.30ms and max 44.20ms) is the more requested. Make a striping on the datafiles of the most requested tablespaces.


3.2.11 Attempt to detect of changing execution plan

The table and chart below show the most expensive queries with respect to "Buffer Get/Execution":


% Buffer Get /executionSqlidMax Buffer Get /executionMin Buffer Get /executionQuery
31.123194702394 8807576 8807576 INSERT INTO TEMP_VLE_PARAMDEPENDENCY SELECT /*+ index(ver) */ DISTINCT ver.PROCESSSCOPEENTITYID, ver.PARAMETERTemps_rec, dep.* FROM TAB_ONDATAPERF_08 ver, (SELECT /*+ index(param) */ param.* FROM T_VLEPARAMDEPENDENCY_ISL param, ( SELECT /*+ index(dep) index(ver) */ dep.PRICINGDATE, dep.field_reception_ide, dep.PARAMDEP
25.261320351734 7148846 7148846 DELETE from TAB_ODP_INSTITUT_MOIS WHERE (system__Date < (SYSDATE-:"SYS_B_0") AND date_captur is null) OR (date_captur < (SYSDATE -:"SYS_B_1"))
17.78539824821 5031335 5031335 INSERT INTO TEMP_VLE_PARAMDEPENDENCY SELECT /*+ index(ver) */ DISTINCT ver.PROCESSSCOPEENTITYID, ver.PARAMETERTemps_rec, dep.* FROM TAB_ONDATAPERF_08 ver, (SELECT /*+ index(param) */ param.* FROM T_VLEPARAMDEPENDENCY_ISL param, ( SELECT /*+ index(dep) index(ver) */ dep.PRICINGDATE, dep.field_reception_ide, dep.PARAMDEP
9.173180512505 2596185 2596185 SELECT DISTINCT v.PRODUCTID, v.PROCESSSCOPEENTITYID FROM V_VLE_DEPENDENT_ENTITYID_1 v WHERE v.PRICINGDATE=:1 AND v.field_receERULESID=:2 AND v.machine_ip_=:"SYS_B_0" AND v.PROCESSSCOPEENTITYID IN (SELECT ENTITYID FROM TEMP_VLE_ENTITYID) AND v.MARKETDATAOWNERID=:3
7.86735563134 2223213 2223213 SELECT distinct(header2.col_desc1), header2.paypal_recordse_Name, min(type.CLASSNAME) CLASSNAME FROM T_NORM_IDENTIFIABLE_AEX_JRK type, TAB1O_ONDATAFINE_SL entetes, TAB1O_ONDATAFINE_SL header2, TAB_ONDATAPERF11_ARCHI t2 WHERE type.cid = header2.CLASSNAMEID AND entetes.underlyingId = header2.cle_ident AND entetes
3.411543011453 966099 966099 SELECT p.ID , u.INTERNAL_ID , z.ELIOT_ID FROM ( SELECT rank() over (partition by payoff_oid, internal_id order by payoff_version_oid desc, effectivity_date desc) as rank , payoff_oid, internal_id, constatable_oid FROM tudlepr ) u , tprdepr p , (SELECT product_oid, eliot_id FROM tallprdepr a WH
3.251244500270 919548 919548 SELECT TQOTSCNEPR.OID, TQOTSCNEPR.CONSTATABLE_OID, TQOTSCNEPR.PAYOFF_OID, TQOTSCNEPR.PROCESSING_STATUS, TQOTSCNEPR.PROCESSING_TS, TQOTSCNEPR.QUOTATION_DATE, TQOTSCNEPR.SDD_PERIMETER_ID, TQOTSCNEPR.TIME_METHOD_TYPE, TQOTSCNEPR.STANDARD_METHOD, TQOTSCNEPR.FUND_METHOD, TQOTSCNEPR.EXPLICIT_TIME FROM TQOTSCNEPR, (SELECT TAL
0.522793845901 154493 144866 SELECT /*+first_rows*/ TAB_ONDATAPERF_TRIGID, UDESC1_U, PRODUCTPRICINGRESULTID, UDESC3_U, UDESC4_U, PAYOFFID_CURRENCYID, UDESC6_U, LONG_VALUE_STRI FROM T_P_PAYOFFPRICINGRESULT_ISL JOIN T_IFRPRODUCTPRICINGRESULT_TMP ON T_P_PAYOFFPRICINGRESULT_ISL.UDESC2_U = T_IFRPRODUCTPRICINGRESULT_
0.474135052701 154675 154675 SELECT /*+first_rows*/ TAB_ONDATAPERF_TRIGID, UDESC1_U, PRODUCTPRICINGRESULTID, UDESC3_U, UDESC4_U, PAYOFFID_CURRENCYID, UDESC6_U, LONG_VALUE_STRI FROM T_P_PAYOFFPRICINGRESULT_ISL JOIN T_IFRPRODUCTPRICINGRESULT_TMP ON T_P_PAYOFFPRICINGRESULT_ISL.UDESC2_U = T_IFRPRODUCTPRICINGRESULT_
0.3427777554 124018 111336 SELECT HOLDE_ID FROM TAB_ONDATAPERF05_ARCHIV WHERE ((STATUS = :1) OR (STATUS is NULL)) AND ( (:"SYS_B_0" = :"SYS_B_1") )

The queries above have a number of buffer_get / execution remains constant over time.


NOTE: If your database is Oracle version 9i or higher, then the execution plan of these queries can be found in the STATSPACK tables or AWR tables. In that case, you can use the STATSPACK script (sprepsql.sql) or AWR script (awrsqrpt.sql).These scripts are located in the directory '$ORACLE_HOME/rdbms/admin'. You will be able to view the execution plan of the queries from the table above.



4 - Description of the Instance
Instance : ONDA01Database : ONDA01Version : 920
ParameterValueDescription
O7_DICTIONARY_ACCESSIBILITYTRUEVersion 7 Dictionary Accessibility Support
_b_tree_bitmap_plansFALSEenable the use of bitmap plans for tables w. only B-tree indexes
_db_percent_hot_keep50Percent of keep buffer pool considered hot
_db_percent_hot_recycle50Percent of recycle buffer pool considered hot
_kgl_latch_count61number of library cache latches
_shared_pool_reserved_pct15percentage memory of the shared pool allocated for the reserved
_trace_files_publicTRUECreate publicly accessible trace files
audit_file_dest/DATAPERF/oracle/admin/ONDA01/aduDirectory in which auditing files are to reside
background_dump_dest/DATAPERF/oracle/admin/ONDA01/bduDetached process dump directory
compatible9.2.0Database will be completely compatible with this software versio
control_files/DATAPERF/oracle/data/ONDA01/fs01control file names list
core_dump_dest/DATAPERF/oracle/admin/ONDA01/cduCore dump directory
cursor_sharingforcecursor sharing mode
db_block_size8192Size of database block in bytes
db_cache_size4294967296Size of DEFAULT buffer pool for standard block size buffers
db_file_multiblock_read_count16db block to be read each IO
db_files400max allowable # db files
db_keep_cache_size1073741824Size of KEEP buffer pool for standard block size buffers
db_nameONDA01database name specified in CREATE DATABASE
db_recycle_cache_size2147483648Size of RECYCLE buffer pool for standard block size buffers
db_writer_processes3number of background database writer
event10262debug event control - default null string
fast_start_mttr_target120MTTR target of forward crash recovery in seconds
hash_area_size3145728size of in-memory hash work area
hash_join_enabledTRUEenable/disable hash join
ifile/DATAPERF/oracle/admin/@/pfile/ininclude file in init.ora
instance_nameONDA01instance name supported by the instance
java_pool_size16777216size in bytes of java pool
java_soft_sessionspace_limit10485760warning limit on size in bytes of a Java sessionspace
job_queue_processes4number of job queue processes to start
log_archive_dest/DATAPERF/oracle/admin/ONDA01/arcarchival destination #10 text string
log_archive_format%t_%s.arcarchival destination format
log_archive_startTRUEstart archival process on SGA initialization
log_buffer1048576redo circular buffer size
max_dump_file_sizeunlimitedMaximum size (blocks) of dump file
max_enabled_roles100max number of roles a user can have enabled
open_cursors300max # cursors per process
optimizer_modeCHOOSEoptimizer mode
parallel_max_servers20maximum parallel query servers per instance
parallel_min_servers0minimum parallel query servers per instance
processes6295user processes
query_rewrite_enabledFALSEallow rewrite of queries using materialized views if enabled
remote_login_passwordfileNONEpassword file usage parameter
resource_limitTRUEmaster switch for resource limit
resource_manager_planODPPLAN_PLANresource mgr top plan
session_cached_cursors150Number of cursors to cache in a session.
shared_pool_size1795162112size in bytes of shared pool
sort_area_size3145728size of in-memory sort work area
sql_traceFALSEenable SQL trace
star_transformation_enabledFALSEenable the use of star transformation
timed_statisticsTRUEmaintain internal timing statistics
undo_managementAUTOinstance runs in SMU mode if TRUE, else in RBU mode
undo_retention3600undo retention in seconds
undo_suppress_errorsTRUESuppress RBU errors in SMU mode
undo_tablespaceUNDOTBSuse/switch undo tablespace
user_dump_dest/DATAPERF/oracle/admin/ONDA01/uduUser process dump directory