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

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

Instance	ONDA01
Database	ONDA01
Oracle Version	920 (64 bits)
CPU Type	UltraSparc 1200Mhz
Type of capture	STATSPACK
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 Time	Sqlid	Buffer Get	Execution	CPU Time	Elapsed Time	Query
32.60	3493057300	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.82	27777554	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.68	932442	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.03	2438688645	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.52	1365669271	148350117	97365	7229.98	7350.87	DELETE Evenement_site_http_done WHERE fine=:"SYS_B_0" AND insDate < = sysdate - :1
2.87	4021572787	154946059	62477	5879.55	5959.32	DELETE TAB1_ONDATAPERF01 WHERE fine=:"SYS_B_0" AND odp_ins < = sysdate - :1
2.84	701818890	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.75	2196401642	51032392	82443	5638.02	5762.82	SELECT ODP FROM TAB_ODPERF020 WHERE ((ACTIVATION =:1)) AND ( (:"SYS_B_0" = :"SYS_B_1") )
2.59	2264918283	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.28	2337371380	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.21	2503437411	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.03	1574314814	?	131	4157.97	12478.07	SELECT count (:"SYS_B_0") FROM TAB_ONDATAPERF05_ARCHIV WHERE status = :"SYS_B_1"
1.84	1287238284	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.79	2793845901	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.40	3803632547	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 Time	Wait Event	Min Avg Wait(ms)	Max Avg Wait(ms)	Description	Advice
34.77	db file sequential read	3.00	12.00	Event when access by ROWID of the table in DATAFILES.	Tune SQL, speed up disks (5-15ms), increase buffer cache
16.73	db file scattered read	8.00	17.00	Event when FULL SCAN of the table in DATAFILES.	Tune SQL, add indexes, speed up disks (5-15ms)
11.93	log file sync	7.00	24.00	When 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 file	Commit less, put redo logs on faster disks (striping).
11.89	latch free	1.00	55.00	The process waits for a latch that is currently busy (held by another process).	See chapter further describing the latchs
5.42	enqueue	18.00	1059.00	Wait during a transactional lock.	See chapter further describing the segments concerned
4.14	log file parallel write	3.00	5.00	Writing redo records to the redo log files from the log buffer.	Increase the scalability of redo log file (striping).
3.81	buffer busy waits	2.00	15.00	Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session	See chapter further describing the segments concerned
3.80	resmgr:waiting in end wait	172.00	666.00	Env Resource Manager : Process is now runable but there are insufficient resources to execute.	Tune latchs or add CPU
3.78	db file parallel write	34.00	100.00	The 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.21	direct path write	13.00	87.00	Write in direct mode (bypass SGA) in DATAFILES or TEMPFILES.	Check sorts disk in the chapter further
0.98	direct path read (lob)	1.00	6.00	Read LOB in DATAFILES.
0.88	SQL*Net more data to client	0.00	0.00	The process server is sending more data/messages to the client.	Check network delays and connections.
0.66	log file sequential read	30.00	53.00	Waiting 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

% Sleep	Latch name	Sleeps	Gets	Misses	Description	Advice
35.54	cache buffers chains	948988	14097542612	11283661	This 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.82	resmgr:resource group CPU	635890	29299500	5803928	No information
8.43	library cache	225109	482736298	3767243	This 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.73	undo global data	152975	198752343	13727710	This latch serializes the access to the Undo segment information in the SGA
4.85	enqueues	129364	307294411	9515302	No information
4.21	library cache pin	112518	354203323	1310030	A 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.00	row cache objects	106671	156804585	1877133	The 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

% Read	Owner	Tablespace	Object	Sub-object	Type	Reads
42.20	APPLI_ODP03	ODP_TABSPE_DATA0199_INDEX	PK_U_ODP000001	n/a	INDEX	2621721072
24.30	APPLI_ODP01	DATA_ODP_1	TAB1D_DOMAIN_CLUSTER_PK	n/a	INDEX	1509314512
20.96	APPLI_ODP03	ODP_TABSPE_DATA0199_DATA	TAB_ONDATAPERF_08	TAB_ONDATAPERF_08_FLG_ST	TABLE PARTITION	1302035072
4.73	APPLI_ODP02	ODP	ONDATAPERF_PK_0000000010	n/a	INDEX	294099152
1.25	APPLI_ODP03	VAR_YACC_LEXIUM_DATA	TAB_ODP_INSTITUT_MOIS	n/a	TABLE	77754752
1.03	APPLI_ODP01	DATA_ODP_1	ONDATAPERF_T_I_ST_PK	n/a	INDEX	63754528
0.91	APPLI_ODP01	ODP_VALEUR_LG	ONDATAPERF_T_I_STARCHIVE	n/a	TABLE	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

% Wait	Owner	Tablespace	Object	Sub-object	Type	Waits
54.44	APPLI_ODP01	ODP_VALEUR_LG	ONDATAPERF_T_I_STARCHIVE	n/a	TABLE	1731208
9.44	APPLI_ODP03	ODP_TABSPE_DATA01_INDEX	IDX_H_ARCHIVE_VALUE_GARDEUR_I	T_H_INDICATEUR_USAG_FLG_ST	INDEX PARTITION	300115
7.90	APPLI_ODP03	ODP_TABSPE_DATA01_INDEX	IDX_H_UNIQUE_INDIC_STRESULT_I	T_H_UNIQUE_INDIC_ST_FLG_ST	INDEX PARTITION	251090
5.58	APPLI_ODP01	DATA_ODP_1	I_ONDATAPERF_T_I_STARCHIVE	n/a	INDEX	177285
4.38	APPLI_ODP03	ONDATA	T_P_TAB_ONDATAPERF_TRIG_ISL	PARTITION_ODPX	TABLE PARTITION	139266
4.02	APPLI_ODP03	VAR_YACC_LEXIUM_DATA	TAB_ODP_INSTITUT_MOIS	n/a	TABLE	127761
2.84	APPLI_ODP01	ONDATA_INDEX	TAB_ONDATAPERF05_ARCHIV	n/a	TABLE	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

% Wait	Owner	Tablespace	Object	Sub-object	Type	Waits
30.39	APPLI_ODP03	VAR_YACC_LEXIUM_INDEX	PK_ONDATAPERF_TAB1_OID	n/a	INDEX	45893
23.29	APPLI_ODP03	ODP_TABSPE_DATA01_INDEX	IDX_H_UNIQUE_INDIC_STRESULT_I	T_H_UNIQUE_INDIC_ST_FLG_ST	INDEX PARTITION	35167
23.17	APPLI_ODP03	ODP_TABSPE_DATA01_INDEX	IDX_H_ARCHIVE_VALUE_GARDEUR_I	T_H_INDICATEUR_USAG_FLG_ST	INDEX PARTITION	34981
11.11	APPLI_ODP03	ODP_TABSPE_DATA0199_INDEX	PK_U_ODP000001	n/a	INDEX	16772
4.82	APPLI_ODP03	ODP_TABSPE_DATA01_INDEX	PK_H_ARCHIVE_VALUE_GARDEUR	n/a	INDEX	7282
3.88	APPLI_ODP03	ODP_TABSPE_DATA01_INDEX	PK_H_UNIQUE_INDIC_STRESULT	n/a	INDEX	5856
0.93	APPLI_ODP03	ODP_DATA_SYSTEM01_BIG_INDEX	PK_ONDATAFINE_ODP_FG	n/a	INDEX	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 Time	Sqlid	Disk Read	Execution	CPU Time	WAIT Time	Query
32.49	932442	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.03	27777554	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.63	1287238284	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.86	1574314814	15013825	131	4157.97	8320.10	SELECT count (:"SYS_B_0") FROM TAB_ONDATAPERF05_ARCHIV WHERE status = :"SYS_B_1"
3.85	3268545449	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.07	1680262068	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.70	2264918283	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.36	701818890	?	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.18	2725082426	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.11	3542865164	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.77	999197517	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.35	498617762	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.19	1320351734	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.14	3946284183	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.93	837563475	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.

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 /execution	Sqlid	Max Buffer Get /execution	Min Buffer Get /execution	Query
31.12	3194702394	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.26	1320351734	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.78	539824821	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.17	3180512505	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.86	735563134	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.41	1543011453	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.25	1244500270	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.52	2793845901	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.47	4135052701	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.34	27777554	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.

4 - Description of the Instance

Instance : ONDA01

Database : ONDA01

Version : 920

Parameter	Value	Description
O7_DICTIONARY_ACCESSIBILITY	TRUE	Version 7 Dictionary Accessibility Support
_b_tree_bitmap_plans	FALSE	enable the use of bitmap plans for tables w. only B-tree indexes
_db_percent_hot_keep	50	Percent of keep buffer pool considered hot
_db_percent_hot_recycle	50	Percent of recycle buffer pool considered hot
_kgl_latch_count	61	number of library cache latches
_shared_pool_reserved_pct	15	percentage memory of the shared pool allocated for the reserved
_trace_files_public	TRUE	Create publicly accessible trace files
audit_file_dest	/DATAPERF/oracle/admin/ONDA01/adu	Directory in which auditing files are to reside
background_dump_dest	/DATAPERF/oracle/admin/ONDA01/bdu	Detached process dump directory
compatible	9.2.0	Database will be completely compatible with this software versio
control_files	/DATAPERF/oracle/data/ONDA01/fs01	control file names list
core_dump_dest	/DATAPERF/oracle/admin/ONDA01/cdu	Core dump directory
cursor_sharing	force	cursor sharing mode
db_block_size	8192	Size of database block in bytes
db_cache_size	4294967296	Size of DEFAULT buffer pool for standard block size buffers
db_file_multiblock_read_count	16	db block to be read each IO
db_files	400	max allowable # db files
db_keep_cache_size	1073741824	Size of KEEP buffer pool for standard block size buffers
db_name	ONDA01	database name specified in CREATE DATABASE
db_recycle_cache_size	2147483648	Size of RECYCLE buffer pool for standard block size buffers
db_writer_processes	3	number of background database writer
event	10262	debug event control - default null string
fast_start_mttr_target	120	MTTR target of forward crash recovery in seconds
hash_area_size	3145728	size of in-memory hash work area
hash_join_enabled	TRUE	enable/disable hash join
ifile	/DATAPERF/oracle/admin/@/pfile/in	include file in init.ora
instance_name	ONDA01	instance name supported by the instance
java_pool_size	16777216	size in bytes of java pool
java_soft_sessionspace_limit	10485760	warning limit on size in bytes of a Java sessionspace
job_queue_processes	4	number of job queue processes to start
log_archive_dest	/DATAPERF/oracle/admin/ONDA01/arc	archival destination #10 text string
log_archive_format	%t_%s.arc	archival destination format
log_archive_start	TRUE	start archival process on SGA initialization
log_buffer	1048576	redo circular buffer size
max_dump_file_size	unlimited	Maximum size (blocks) of dump file
max_enabled_roles	100	max number of roles a user can have enabled
open_cursors	300	max # cursors per process
optimizer_mode	CHOOSE	optimizer mode
parallel_max_servers	20	maximum parallel query servers per instance
parallel_min_servers	0	minimum parallel query servers per instance
processes	6295	user processes
query_rewrite_enabled	FALSE	allow rewrite of queries using materialized views if enabled
remote_login_passwordfile	NONE	password file usage parameter
resource_limit	TRUE	master switch for resource limit
resource_manager_plan	ODPPLAN_PLAN	resource mgr top plan
session_cached_cursors	150	Number of cursors to cache in a session.
shared_pool_size	1795162112	size in bytes of shared pool
sort_area_size	3145728	size of in-memory sort work area
sql_trace	FALSE	enable SQL trace
star_transformation_enabled	FALSE	enable the use of star transformation
timed_statistics	TRUE	maintain internal timing statistics
undo_management	AUTO	instance runs in SMU mode if TRUE, else in RBU mode
undo_retention	3600	undo retention in seconds
undo_suppress_errors	TRUE	Suppress RBU errors in SMU mode
undo_tablespace	UNDOTBS	use/switch undo tablespace
user_dump_dest	/DATAPERF/oracle/admin/ONDA01/udu	User process dump directory