About Us Support Pricing ONDATAPERF ACCESS faq
Performance Analysis Report for the database ONDA01
generated by OnDataPerf version 3.2.1 Premium

Copyright (C) 2002-2009 Ondatafine SARL. All Rights Reserved
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

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 determined from the analysis of the found bottlenecks in the response time of the Oracle instance for the 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 elapse 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 represents 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 of 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. It is also low for other tracks.
IMPORTANT: CPU OTHER and RECURSIVE are detailed at the section "TOP MOST queries with high cost relating to CPU", and CPU PARSE at the following section "Parse to execute ratio"
3.1.4 Parse to Execute Ratio
Parsing/Execution is ok/fine for tracks with high activity. It is also ok/fine for other tracks.

3.1.5 TOP MOST queries with high cost with respect to CPU TIME
The table and chart below shows the most expensive queries raised in consumption of CPU TIME classified in descending order by 'CPU TIME':


% CPU Time Hash Value Buffer Get Execution CPU Time Elapse 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 above table have excessive CPU cost compared to the number of executions. The execution plan for 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 script are located in the directory '$ORACLE_HOME/rdbms/admin'. You will be able to veiw 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 waitings.

The table below is an accumulation of event times (by decreasing order):
% Wait Time Wait Event Description Advice
34.77 db file sequential read Event when access by ROWID of the table in DATAFILES. Tune SQL, speed up disks, increase buffer cache
16.73 db file scattered read Event when FULL SCAN of the table in DATAFILES. Tune SQL, add indexes, speed up disks
11.93 log file sync 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 The process waits for a latch that is currently busy (held by another process). See section that further describes latches
5.42 enqueue Wait during a transactional lock. See section that further describes the segments concerned
4.14 log file parallel write 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 Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session See section that further describes the segments concerned
3.80 resmgr:waiting in end wait 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 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 Write in direct mode (bypass SGA) in DATAFILES or TEMPFILES. Check sorts disk in the section that relates SORTS
0.98 direct path read (lob) Read LOB in DATAFILES.  
0.88 SQL*Net more data to client The process server is sending more data/messages to the client. Check network delays and connections.
0.66 log file sequential read 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 waits ('latch free','latch:...') to access to a buffer
* 'Sleeps' : Number of failed accesses 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. Increase the shared pool size
There are far too much waiting on latches. You can follow the advices from the table above, and if necessary, put a collection to capture queries to the origin of latches.
Below is an example of SQL STATEMENT that will enable you to identify queries with respect 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 above table. 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 above table. Apply the rules 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 tables, indexes and partitions, 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 above table. 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 shows a list of the most expensive queries with respect to Waiting time classified in the descending order by 'WAIT TIME':


% WAIT Time Hash Value 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 above table have excessive cost with respect reading disk compared to the number of executions. It is essential to check their execution plans.
If your Oracle database is 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 the AWR script (awrsqrpt.sql) in the directory '$ORACLE_HOME/rdbms/admin' to see the execution plan of the queries of 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 show 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 the 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)

These 2 above bar 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 quite 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.

3.2.8 TOP MOST tablespaces - most requested in readings
The chart above shows the tablespaces most requested in readings.
The tablespace ODP_VALEUR_LG is the most 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 conventional". And a number of writing that have bypassed the cache of the instance, those are "physical writes direct", occurring in the case of sorting, parallel queries, load in direct mode, operations on temporary tables, or writing LOB.

3.2.10 TOP MOST tablespaces - most requested in writing

The chart above shows the tablespaces most requested in writings.

The tablespace UNDOTBS is the most 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 shows the most expensive querieswith respect to "Buffer Get/Execution":

% Buffer Get /execution Hash Value 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 above queries have a number of buffer_get / execution which remains constant over time.
Oracle version 9i or higher, 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 the AWR script (awrsqrpt.sql) in the directory '$ORACLE_HOME/rdbms/admin' to see the execution plan of the queries of the table above.

4 - Description of the Instance
Name : 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
Copyright 1991-2017 Cotran Technologies,Ltd. All Rights Reserved.
ONDATAPERF is a registered trademark and software product of Ondatafine, SARL
Site contents, services, solutions and offerings are subject to change without notice.