 |
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
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. |
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 /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. |
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 : 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 |