WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ SEPBZ212 3429130464 SEPBZ212 1 10.2.0.3.0 NO prodwh Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 7380 07-Nov-10 06:00:56 78 4.2 End Snap: 7382 07-Nov-10 08:00:41 89 4.7 Elapsed: 119.75 (mins) DB Time: 3,090.84 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 600M 612M Std Block Size: 32K Shared Pool Size: 400M 388M Log Buffer: 4,160K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 3,792,735.15 23,676,645.12 Logical reads: 59,110.34 369,004.05 Block changes: 11,749.88 73,350.16 Physical reads: 3,921.39 24,479.78 Physical writes: 825.54 5,153.55 User calls: 1,468.92 9,169.94 Parses: 162.35 1,013.50 Hard parses: 4.92 30.73 Sorts: 37.01 231.05 Logons: 0.17 1.07 Executes: 229.85 1,434.84 Transactions: 0.16 % Blocks changed per Read: 19.88 Recursive Call %: 68.80 Rollback per transaction %: 1.74 Rows per Sort: 9432.40 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.90 Redo NoWait %: 99.98 Buffer Hit %: 94.24 In-memory Sort %: 99.98 Library Hit %: 95.53 Soft Parse %: 96.97 Execute to Parse %: 29.36 Latch Hit %: 99.95 Parse CPU to Parse Elapsd %: 23.58 % Non-Parse CPU: 99.48 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 81.84 76.15 % SQL with executions>1: 86.33 82.88 % Memory for SQL w/exec>1: 91.84 89.33 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- db file sequential read 4,011,073 51,929 13 28.0 User I/O CPU time 46,085 24.9 db file scattered read 881,886 31,848 36 17.2 User I/O PX Deq Credit: send blkd 308,092 19,274 63 10.4 Other direct path read temp 965,156 12,899 13 7.0 User I/O ------------------------------------------------------------- Time Model Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total time in database user-calls (DB Time): 185450.3s -> Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name Statistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time 184,830.3 99.7 DB CPU 46,085.2 24.9 parse time elapsed 1,527.3 .8 hard parse elapsed time 1,381.6 .7 PL/SQL execution elapsed time 1,309.4 .7 connection management call elapsed time 101.8 .1 PL/SQL compilation elapsed time 62.9 .0 failed parse elapsed time 19.4 .0 hard parse (sharing criteria) elapsed time 10.7 .0 Java execution elapsed time 6.2 .0 hard parse (bind mismatch) elapsed time 2.6 .0 repeated bind elapsed time 1.7 .0 sequence load elapsed time 0.9 .0 DB time 185,450.3 N/A background elapsed time 1,736.2 N/A background cpu time 292.9 N/A ------------------------------------------------------------- Wait Class DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc Avg %Time Total Wait wait Waits Wait Class Waits -outs Time (s) (ms) /txn -------------------- ---------------- ------ ---------------- ------- --------- User I/O 6,978,074 .0 105,156 15 6,062.6 Other 224,343,773 99.8 21,511 0 ######### System I/O 315,602 .0 1,075 3 274.2 Concurrency 39,071 15.1 483 12 33.9 Application 21,870 .0 269 12 19.0 Network 11,930,464 .0 110 0 10,365.3 Configuration 2,165 2.0 35 16 1.9 Commit 1,439 .0 6 4 1.3 ------------------------------------------------------------- Wait Events DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- db file sequential read 4,011,073 .0 51,929 13 3,484.9 db file scattered read 881,886 .0 31,848 36 766.2 PX Deq Credit: send blkd 308,092 .1 19,274 63 267.7 direct path read temp 965,156 .0 12,899 13 838.5 read by other session 416,195 .0 6,516 16 361.6 PX qref latch 223,972,145 100.0 2,061 0 ######### direct path write temp 604,157 .0 1,465 2 524.9 db file parallel write 91,976 .0 549 6 79.9 log file parallel write 189,979 .0 469 2 165.1 local write wait 57,247 .0 374 7 49.7 latch: cache buffers chains 12,506 .0 304 24 10.9 enq: RO - fast object reuse 21,560 .0 268 12 18.7 latch free 4,159 .5 77 18 3.6 direct path write 25,727 .0 77 3 22.4 SQL*Net more data from clien 26,575 .0 64 2 23.1 cursor: pin S wait on X 5,972 98.6 60 10 5.2 latch: shared pool 3,396 .0 39 11 3.0 control file sequential read 28,641 .0 38 1 24.9 reliable message 16,770 .0 34 2 14.6 SQL*Net more data to client 1,273,754 .0 29 0 1,106.6 library cache load lock 560 .0 26 46 0.5 direct path read 15,063 .0 24 2 13.1 log buffer space 1,581 .0 24 15 1.4 latch: library cache 1,272 .0 22 17 1.1 db file parallel read 71 .0 21 296 0.1 control file parallel write 4,790 .0 19 4 4.2 latch: row cache objects 690 .0 18 26 0.6 enq: MS - contention 9 66.7 18 1990 0.0 SQL*Net message to client 10,630,135 .0 17 0 9,235.6 rdbms ipc reply 7,883 .0 12 2 6.8 LGWR wait for redo copy 30,048 2.0 12 0 26.1 log file switch completion 272 .0 10 38 0.2 os thread startup 57 .0 7 129 0.0 latch: cache buffers lru cha 937 .0 6 7 0.8 log file sync 1,439 .0 6 4 1.3 latch: session allocation 506 .0 5 10 0.4 Data file init write 1,494 .0 4 3 1.3 kksfbc child completion 83 84.3 3 40 0.1 latch: object queue header o 963 .0 3 3 0.8 latch: enqueue hash chains 47 .0 2 44 0.0 buffer busy waits 1,937 .0 2 1 1.7 row cache lock 434 .0 2 4 0.4 enq: US - contention 146 .0 1 10 0.1 latch: redo allocation 565 .0 1 2 0.5 enq: TX - index contention 13 .0 1 91 0.0 wait list latch free 47 .0 1 22 0.0 latch: In memory undo latch 28 .0 1 34 0.0 cursor: mutex S 11,942 .0 1 0 10.4 enq: TX - row lock contentio 31 .0 0 13 0.0 log file sequential read 108 .0 0 4 0.1 enq: TM - contention 9 .0 0 39 0.0 library cache lock 6 .0 0 59 0.0 PX Deq: Signal ACK 21 28.6 0 14 0.0 kkdlgon 28 .0 0 10 0.0 latch: messages 453 .0 0 1 0.4 free buffer waits 22 81.8 0 8 0.0 latch: library cache pin 4 .0 0 38 0.0 latch: redo writing 248 .0 0 1 0.2 log file single write 108 .0 0 1 0.1 cursor: mutex X 238 .0 0 0 0.2 Wait Events DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- buffer deadlock 28 100.0 0 3 0.0 enq: CI - contention 11 .0 0 7 0.0 SQL*Net break/reset to clien 268 .0 0 0 0.2 SGA: allocation forcing comp 7 71.4 0 9 0.0 enq: TX - allocate ITL entry 11 .0 0 4 0.0 latch: undo global data 6 .0 0 7 0.0 db file single write 5 .0 0 9 0.0 cursor: pin S 757 .0 0 0 0.7 enq: SQ - contention 6 .0 0 3 0.0 enq: CU - contention 1 .0 0 18 0.0 latch: object queue header h 24 .0 0 1 0.0 latch: checkpoint queue latc 25 .0 0 1 0.0 enq: JS - queue lock 2 .0 0 6 0.0 latch: library cache lock 15 .0 0 1 0.0 enq: KO - fast object checkp 2 .0 0 4 0.0 enq: PS - contention 3 .0 0 1 0.0 cursor: pin X 1 100.0 0 3 0.0 enq: CF - contention 1 .0 0 1 0.0 latch: parallel query alloc 5 .0 0 0 0.0 undo segment extension 25 100.0 0 0 0.0 library cache pin 1 .0 0 0 0.0 SQL*Net message from client 10,630,130 .0 20,754 2 9,235.6 PX Deq: Execution Msg 11,355 93.1 20,690 1822 9.9 PX Idle Wait 4,277 98.8 8,271 1934 3.7 class slave wait 104 48.1 7,091 68186 0.1 virtual circuit status 240 100.0 7,014 29225 0.2 ASM background timer 1,727 .0 7,009 4059 1.5 Streams AQ: qmn coordinator 524 51.1 7,009 13376 0.5 Streams AQ: qmn slave idle w 256 .0 7,009 27379 0.2 Streams AQ: waiting for mess 2,327 99.7 6,995 3006 2.0 Streams AQ: waiting for time 32 53.1 6,952 217257 0.0 PL/SQL lock timer 2 100.0 3,077 ####### 0.0 PX Deq: Table Q Normal 1,291,832 .0 2,412 2 1,122.4 PX Deq Credit: need buffer 46,842 .0 1,445 31 40.7 jobq slave wait 285 98.2 826 2897 0.2 PX Deq: Execute Reply 609 18.7 367 603 0.5 pipe get 13 30.8 20 1519 0.0 KSV master wait 55 1.8 7 121 0.0 PX Deq: Parse Reply 40 .0 0 1 0.0 PX Deq: Join ACK 47 .0 0 1 0.0 PX Deq: Msg Fragment 7 .0 0 3 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- db file parallel write 91,976 .0 549 6 79.9 log file parallel write 189,977 .0 469 2 165.1 db file sequential read 4,359 .0 42 10 3.8 events in waitclass Other 32,768 1.9 28 1 28.5 control file parallel write 4,775 .0 19 4 4.1 latch: cache buffers chains 318 .0 16 49 0.3 control file sequential read 3,974 .0 10 3 3.5 latch: shared pool 210 .0 9 41 0.2 direct path read 12,573 .0 6 1 10.9 db file scattered read 303 .0 4 14 0.3 os thread startup 27 .0 3 119 0.0 direct path write 4,063 .0 2 0 3.5 latch: row cache objects 2 .0 0 249 0.0 log file sequential read 108 .0 0 4 0.1 log buffer space 233 .0 0 1 0.2 latch: redo writing 247 .0 0 1 0.2 log file single write 108 .0 0 1 0.1 cursor: pin S wait on X 3 100.0 0 8 0.0 latch: library cache 3 .0 0 4 0.0 read by other session 2 .0 0 4 0.0 log file sync 2 .0 0 1 0.0 rdbms ipc message 551,350 6.3 87,499 159 479.0 ASM background timer 1,727 .0 7,009 4059 1.5 Streams AQ: qmn coordinator 524 51.1 7,009 13376 0.5 Streams AQ: qmn slave idle w 256 .0 7,009 27379 0.2 pmon timer 2,453 100.0 7,003 2855 2.1 Streams AQ: waiting for time 32 53.1 6,952 217257 0.0 smon timer 37,542 .0 6,866 183 32.6 KSV master wait 20 .0 1 30 0.0 ------------------------------------------------------------- Operating System Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 Statistic Total -------------------------------- -------------------- AVG_BUSY_TIME 525,482 AVG_IDLE_TIME 167,641 AVG_IOWAIT_TIME 129,635 AVG_SYS_TIME 48,847 AVG_USER_TIME 476,409 BUSY_TIME 5,782,686 IDLE_TIME 1,846,427 IOWAIT_TIME 1,428,388 SYS_TIME 539,682 USER_TIME 5,243,004 LOAD 1 OS_CPU_WAIT_TIME 399,530,262,800 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 52,936,708 VM_OUT_BYTES 22,519,805 PHYSICAL_MEMORY_BYTES 47,193,268,224 NUM_CPUS 11 NUM_CPU_SOCKETS 11 ------------------------------------------------------------- Service Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- SYS$USERS 177,204.2 40,932.8 ########## ########## SEPBZ212 8,155.8 5,142.8 690,540 ########## SYS$BACKGROUND 0.0 0.0 22,624 330,851 SEPBZ212XDB 0.0 0.0 0 0 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Wait Class info for services in the Service Statistics section. -> Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network -> Time Waited (Wt Time) in centisecond (100th of a second) Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- SYS$USERS 6764936 10312987 36078 43822 0 0 3201053 10107 SEPBZ212 188737 193929 2312 1096 0 0 8727621 850 SYS$BACKGROUND 24517 8638 620 2811 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- 14,040 11,802 0 N/A 7.6 bqh3rk7p1z18y Module: BTUCK ODBC Oracle select a11.ID_DOE_PROCESO ID_DOE_PROCESO, max(a13.DE_DOE_PROCESO) DE_DOE_PROC , a11.ID_MOMENTO_CONTABLE ID_MOMENTO_CONTABLE, a12.AT_PSEUDOCUENTA_EC AT_PSE UDOCUENTA_EC, a11.ID_PRODUCTO_GESTION ID_PRODUCTO_GESTION, a11.ID_CONCEPTO I D_CONCEPTO, a11.ID_ENTIDAD_CUENTA2 ID_ENTIDAD_CUENTA, a11.ID_PRODUCTO_CUENTA 7,052 80 0 N/A 3.8 919d204gh39fr Module: BTUCK ODBC Oracle create table xupq_1380_reb_domestic PCTFREE 0, PCTUSED 99 as select a12.ID_PSI G_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_R EC AT_PERSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT _NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_R 7,043 64 0 N/A 3.8 2b2nrqh228d13 Module: BTUCK ODBC Oracle create table xupq_1380_reb PCTFREE 0, PCTUSED 99 as select a12.ID_xupq_REC ID _PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PE RSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT_NUM_IDE_ FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NU 7,022 74 0 N/A 3.8 87bfaguw09tw5 Module: BTUCK ODBC Oracle create table ZZTIN0OOJZUMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 7,019 6,145 0 N/A 3.8 3b3q2ayggqr6h Module: BTUCK ODBC Oracle select a11.ID_DOE_PROCESO ID_DOE_PROCESO, max(a13.DE_DOE_PROCESO) DE_DOE_PROC , a11.ID_MOMENTO_CONTABLE ID_MOMENTO_CONTABLE, a12.AT_PSEUDOCUENTA_EC AT_PSE UDOCUENTA_EC, a11.ID_PRODUCTO_GESTION ID_PRODUCTO_GESTION, a11.ID_CONCEPTO I D_CONCEPTO, a11.ID_ENTIDAD_CUENTA2 ID_ENTIDAD_CUENTA, a11.ID_PRODUCTO_CUENTA 7,019 330 0 N/A 3.8 97rc1av4y1hjt Module: SQL*Plus INSERT INTO IBC_FACT_OPERACIONES_QV (CLIENTE, BROKER, NEGOCIO, ID_SUBPROD, MERCA DO, BOLSA, DIA, SEMANA, MES, NUMMES, TRIMESTRE, ANYO, GESTOR, TIPCLI, DEABREPEN, GABREPEN, IABREPEN, ABREPEN, DECOMISION, GCOMISION, ICOMISION, COMISION, DEVOLU MEN_SEM, GABREPEN_SEM, IABREPEN_SEM, ABREPEN_SEM, DECOMISION_SEM, GCOMISION_SEM, 7,019 330 0 N/A 3.8 9csp2dh43p0w1 Module: SQL*Plus DECLARE vnomproces varchar2(40); vproces varchar2(10); vnumerr number(2); vcodi number(10); vdescripcio varchar2(255); ct l_error date; vfechaproc NUMBER; MAX_DIA DATE; MAX_DOE_30 DATE; DOE_2ANYS NUMBER; DOE_SEL DATE; Cursor c_tiempo is 7,017 69 0 N/A 3.8 6nv7hqj8yw9vn Module: BTUCK ODBC Oracle create table ZZTIN0XOJYJMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 5,314 3,527 1 5313.7 2.9 7gj6kbsywb3w7 Module: someday.exe with "PENS_COG_MTR_ASSEGURANCES_D" as ( select TO_NUMBER(TO_CHAR(ADD_MONTHS("F". "ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12), 'YYYY')) "ID_ANY", ADD_MO SQL ordered by Elapsed Time DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- NTHS("F"."ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12) "ID_FECHA", TO_NU MBER(TO_CHAR(ADD_MONTHS("F"."ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12 3,526 39 0 N/A 1.9 676fdztwzf54g Module: BTUCK ODBC Oracle create table xupq_1380_exist_seg_vida PCTFREE 0, PCTUSED 99 as select a16.AT_N UM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC0, a12.ID_xupq_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC, a16.AT_PERSON 3,521 721 0 N/A 1.9 9avuhs2u911jh Module: BTUCK ODBC Oracle create table VENDA_CREUADA PCTFREE 0, PCTUSED 99 as select pa11.ID_CLIENTE ID _CLIENTE, pa11.ID_ENTIDAD ID_ENTIDAD, max(a128.AT_NOMBRE) AT_NOMBRE, max(a1 28.DE_CLIENTE) DE_CLIENTE, max(a128.AT_CIF) AT_CIF, max(a128.AT_EDAD) AT_ED AD, max(a128.AT_APELLIDO1) AT_APELLIDO1, max(a128.AT_APELLIDO2) AT_APELLIDO2 3,375 42 0 N/A 1.8 4jy3h13atz4hf Module: BTUCK ODBC Oracle create table ZZTIN1VPIMNMD00R nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_SDOM_CTO) S ALDOMEDIOMESDIPFIXC from DWH_COMERCIAL.MTR_CLI_PRO a11, DWH_COMERCIAL.MTR_CLI_ ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CLIENTE a 1,963 288 0 N/A 1.1 3c40duaxv5961 Module: BTUCK ODBC Oracle create table ZZTIN1RPIMOMD005 nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_INDT_PRCIAL_ CTO) PROTVIDAVINCTENENCIA from DWH_COMERCIAL.MTR_CLI_GRU a11, DWH_COMERCIAL.M TR_CLI_ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CL ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 11,802 14,040 0 N/A 7.6 bqh3rk7p1z18y Module: BTUCK ODBC Oracle select a11.ID_DOE_PROCESO ID_DOE_PROCESO, max(a13.DE_DOE_PROCESO) DE_DOE_PROC , a11.ID_MOMENTO_CONTABLE ID_MOMENTO_CONTABLE, a12.AT_PSEUDOCUENTA_EC AT_PSE UDOCUENTA_EC, a11.ID_PRODUCTO_GESTION ID_PRODUCTO_GESTION, a11.ID_CONCEPTO I D_CONCEPTO, a11.ID_ENTIDAD_CUENTA2 ID_ENTIDAD_CUENTA, a11.ID_PRODUCTO_CUENTA 6,145 7,019 0 N/A 3.8 3b3q2ayggqr6h Module: BTUCK ODBC Oracle select a11.ID_DOE_PROCESO ID_DOE_PROCESO, max(a13.DE_DOE_PROCESO) DE_DOE_PROC , a11.ID_MOMENTO_CONTABLE ID_MOMENTO_CONTABLE, a12.AT_PSEUDOCUENTA_EC AT_PSE UDOCUENTA_EC, a11.ID_PRODUCTO_GESTION ID_PRODUCTO_GESTION, a11.ID_CONCEPTO I D_CONCEPTO, a11.ID_ENTIDAD_CUENTA2 ID_ENTIDAD_CUENTA, a11.ID_PRODUCTO_CUENTA 3,527 5,314 1 3527.46 2.9 7gj6kbsywb3w7 Module: someday.exe with "PENS_COG_MTR_ASSEGURANCES_D" as ( select TO_NUMBER(TO_CHAR(ADD_MONTHS("F". "ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12), 'YYYY')) "ID_ANY", ADD_MO NTHS("F"."ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12) "ID_FECHA", TO_NU MBER(TO_CHAR(ADD_MONTHS("F"."ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12 721 3,521 0 N/A 1.9 9avuhs2u911jh Module: BTUCK ODBC Oracle create table VENDA_CREUADA PCTFREE 0, PCTUSED 99 as select pa11.ID_CLIENTE ID _CLIENTE, pa11.ID_ENTIDAD ID_ENTIDAD, max(a128.AT_NOMBRE) AT_NOMBRE, max(a1 28.DE_CLIENTE) DE_CLIENTE, max(a128.AT_CIF) AT_CIF, max(a128.AT_EDAD) AT_ED AD, max(a128.AT_APELLIDO1) AT_APELLIDO1, max(a128.AT_APELLIDO2) AT_APELLIDO2 704 1,181 1 703.82 0.6 2w5f7zxyf57t1 Module: someday.exe select "COG_MTR_ASSEGURANCES"."FC_DATOS" "FC_DATOS", "COG_MTR_ASSEGURANCES"."ID_ FECHA" "ID_FECHA", to_number(to_char("COG_MTR_ASSEGURANCES"."ID_FECHA", 'MM')) " ID_MES", "COG_MTR_ASSEGURANCES"."ID_PRODUCTO" "ID_PRODUCTO_JOIN", "COG_MTR_ASSEG URANCES"."ID_TIPO_ACTIVO" "ID_TIPO_ACTIVO_JOIN", "COG_MTR_ASSEGURANCES"."ID_INDI 696 897 0 N/A 0.5 cj3h6v7db87hz Module: SQL*Plus BEGIN DBMS_SNAPSHOT.REFRESH( 'CRMOWN03'||'.'||'VM_CRM_ACCPERS_GESTOR','c',atomic _refresh=>false); END; 693 891 0 N/A 0.5 gw3gm27qbm1nr Module: SQL*Plus INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "CRMOWN03 "."VM_CRM_ACCPERS_GESTOR" ( Select b.ID_ACCPERS_G, b.ID_UN, substr(b.ID_CENTRO,1,4) ID_ENTIDAD, substr(b.ID_CENTRO,5) ID_CENTRO, substr(b.ID_CARTERA_DWH,1,4) ID_CENTRO_CARTERA, substr(b.ID_CARTERA_DWH,5 532 1,162 0 N/A 0.6 57kxkgsjyytmk Module: SQL*Plus DECLARE vproces VARCHAR2(40); vnumerr NUMBER(2); vcodi NU MBER(10); vdescripcio VARCHAR2(255); ctlerror DATE; BEGIN vnumerr := 1; vproces := 'dwh03339.sql'; insert /*+ APPEND */ into OPS$EXPLO.MTR_A_CA_SAL POS_D_AUX nologging ( ID_DOE_CONTABLE ,ID_PRODUCTO_GESTION_DWH , 532 1,162 0 N/A 0.6 57vxbhnfpdhw0 Module: SQL*Plus INSERT /*+ APPEND */ INTO OPS$EXPLO.MTR_A_CA_SALPOS_D_AUX NOLOGGING ( ID_DOE_CON TABLE ,ID_PRODUCTO_GESTION_DWH ,ID_DIVISA ,ID_CANAL_DWH ,ID_CARTERA_GEST_DWH ,ID _ENTIDAD_CUENTA ,ID_SEGMENTO_EST_INI ,ID_SEG_COMER_INI ,ID_CPTO_SALDO_POS_DWH ,I D_EMPRESA_CONTABLE ,ID_CENTRO_CONTABLE ,ID_SERVICIO_COSTE_DWH ,ID_VARIABLE_OPERA SQL ordered by CPU Time DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 330 7,019 0 N/A 3.8 97rc1av4y1hjt Module: SQL*Plus INSERT INTO IBC_FACT_OPERACIONES_QV (CLIENTE, BROKER, NEGOCIO, ID_SUBPROD, MERCA DO, BOLSA, DIA, SEMANA, MES, NUMMES, TRIMESTRE, ANYO, GESTOR, TIPCLI, DEABREPEN, GABREPEN, IABREPEN, ABREPEN, DECOMISION, GCOMISION, ICOMISION, COMISION, DEVOLU MEN_SEM, GABREPEN_SEM, IABREPEN_SEM, ABREPEN_SEM, DECOMISION_SEM, GCOMISION_SEM, 330 7,019 0 N/A 3.8 9csp2dh43p0w1 Module: SQL*Plus DECLARE vnomproces varchar2(40); vproces varchar2(10); vnumerr number(2); vcodi number(10); vdescripcio varchar2(255); ct l_error date; vfechaproc NUMBER; MAX_DIA DATE; MAX_DOE_30 DATE; DOE_2ANYS NUMBER; DOE_SEL DATE; Cursor c_tiempo is 288 1,963 0 N/A 1.1 3c40duaxv5961 Module: BTUCK ODBC Oracle create table ZZTIN1RPIMOMD005 nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_INDT_PRCIAL_ CTO) PROTVIDAVINCTENENCIA from DWH_COMERCIAL.MTR_CLI_GRU a11, DWH_COMERCIAL.M TR_CLI_ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CL 80 7,052 0 N/A 3.8 919d204gh39fr Module: BTUCK ODBC Oracle create table xupq_1380_reb_domestic PCTFREE 0, PCTUSED 99 as select a12.ID_PSI G_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_R EC AT_PERSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT _NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_R 74 7,022 0 N/A 3.8 87bfaguw09tw5 Module: BTUCK ODBC Oracle create table ZZTIN0OOJZUMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 69 7,017 0 N/A 3.8 6nv7hqj8yw9vn Module: BTUCK ODBC Oracle create table ZZTIN0XOJYJMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 64 7,043 0 N/A 3.8 2b2nrqh228d13 Module: BTUCK ODBC Oracle create table xupq_1380_reb PCTFREE 0, PCTUSED 99 as select a12.ID_xupq_REC ID _PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PE RSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT_NUM_IDE_ FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NU 42 3,375 0 N/A 1.8 4jy3h13atz4hf Module: BTUCK ODBC Oracle create table ZZTIN1VPIMNMD00R nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_SDOM_CTO) S ALDOMEDIOMESDIPFIXC from DWH_COMERCIAL.MTR_CLI_PRO a11, DWH_COMERCIAL.MTR_CLI_ ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CLIENTE a 39 3,526 0 N/A 1.9 676fdztwzf54g Module: BTUCK ODBC Oracle create table xupq_1380_exist_seg_vida PCTFREE 0, PCTUSED 99 as select a16.AT_N SQL ordered by CPU Time DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- UM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC0, a12.ID_xupq_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC, a16.AT_PERSON ------------------------------------------------------------- SQL ordered by Gets DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 424,723,664 -> Captured SQL account for 55.8% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 231,028,592 1 ############ 54.4 3527.46 5313.66 7gj6kbsywb3w7 Module: someday.exe with "PENS_COG_MTR_ASSEGURANCES_D" as ( select TO_NUMBER(TO_CHAR(ADD_MONTHS("F". "ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12), 'YYYY')) "ID_ANY", ADD_MO NTHS("F"."ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12) "ID_FECHA", TO_NU MBER(TO_CHAR(ADD_MONTHS("F"."ID_FECHA", -"ID_OFFSET_MESOS" - "ID_OFFSET_ANYS"*12 2,232,547 1 2,232,547.0 0.5 703.82 1181.19 2w5f7zxyf57t1 Module: someday.exe select "COG_MTR_ASSEGURANCES"."FC_DATOS" "FC_DATOS", "COG_MTR_ASSEGURANCES"."ID_ FECHA" "ID_FECHA", to_number(to_char("COG_MTR_ASSEGURANCES"."ID_FECHA", 'MM')) " ID_MES", "COG_MTR_ASSEGURANCES"."ID_PRODUCTO" "ID_PRODUCTO_JOIN", "COG_MTR_ASSEG URANCES"."ID_TIPO_ACTIVO" "ID_TIPO_ACTIVO_JOIN", "COG_MTR_ASSEGURANCES"."ID_INDI 2,186,254 0 N/A 0.5 79.68 7051.85 919d204gh39fr Module: BTUCK ODBC Oracle create table xupq_1380_reb_domestic PCTFREE 0, PCTUSED 99 as select a12.ID_PSI G_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_R EC AT_PERSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT _NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_R 1,724,403 2 862,201.5 0.4 12.90 63.46 6q0fr7xfq0c2y Module: dbspicao10@prodwh (TNS V1-V3) SELECT COUNT(*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME not like '%RBS%' and TA BLESPACE_NAME not like '%UNDO%' and TABLESPACE_NAME not like '%TMP%' and TABLESP ACE_NAME not like '%TEMP%' 1,042,571 0 N/A 0.2 69.46 7016.76 6nv7hqj8yw9vn Module: BTUCK ODBC Oracle create table ZZTIN0XOJYJMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 1,034,631 0 N/A 0.2 73.64 7021.96 87bfaguw09tw5 Module: BTUCK ODBC Oracle create table ZZTIN0OOJZUMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 986,863 0 N/A 0.2 287.69 1963.20 3c40duaxv5961 Module: BTUCK ODBC Oracle create table ZZTIN1RPIMOMD005 nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_INDT_PRCIAL_ CTO) PROTVIDAVINCTENENCIA from DWH_COMERCIAL.MTR_CLI_GRU a11, DWH_COMERCIAL.M TR_CLI_ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CL 915,585 0 N/A 0.2 266.88 1410.70 5phzqzduf6u0g Module: BTUCK ODBC Oracle create table ZZTIN1VPIMNMD00Q nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_SDOM_CTO) S ALDOMEDIOMESDIPDIVISA from DWH_COMERCIAL.MTR_CLI_GRU a11, DWH_COMERCIAL.MTR_CL I_ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CLIENTE 788,492 0 N/A 0.2 64.04 7043.23 2b2nrqh228d13 Module: BTUCK ODBC Oracle create table xupq_1380_reb PCTFREE 0, PCTUSED 99 as select a12.ID_xupq_REC ID _PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PE RSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT_NUM_IDE_ SQL ordered by Gets DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 424,723,664 -> Captured SQL account for 55.8% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NU 553,940 0 N/A 0.1 169.78 1140.73 3dc1phyurgbnw Module: SQL*Plus BEGIN DBMS_SNAPSHOT.REFRESH( 'DWH_RASSEGURANCES'||'.'||'MV_DIARIA_8','c',atomic_ refresh=>false); END; ------------------------------------------------------------- SQL ordered by Reads DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total Disk Reads: 28,176,227 -> Captured SQL account for 3.0% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 995,040 0 N/A 3.5 330.03 7018.50 97rc1av4y1hjt Module: SQL*Plus INSERT INTO IBC_FACT_OPERACIONES_QV (CLIENTE, BROKER, NEGOCIO, ID_SUBPROD, MERCA DO, BOLSA, DIA, SEMANA, MES, NUMMES, TRIMESTRE, ANYO, GESTOR, TIPCLI, DEABREPEN, GABREPEN, IABREPEN, ABREPEN, DECOMISION, GCOMISION, ICOMISION, COMISION, DEVOLU MEN_SEM, GABREPEN_SEM, IABREPEN_SEM, ABREPEN_SEM, DECOMISION_SEM, GCOMISION_SEM, 995,040 0 N/A 3.5 330.03 7018.50 9csp2dh43p0w1 Module: SQL*Plus DECLARE vnomproces varchar2(40); vproces varchar2(10); vnumerr number(2); vcodi number(10); vdescripcio varchar2(255); ct l_error date; vfechaproc NUMBER; MAX_DIA DATE; MAX_DOE_30 DATE; DOE_2ANYS NUMBER; DOE_SEL DATE; Cursor c_tiempo is 983,543 0 N/A 3.5 287.69 1963.20 3c40duaxv5961 Module: BTUCK ODBC Oracle create table ZZTIN1RPIMOMD005 nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_INDT_PRCIAL_ CTO) PROTVIDAVINCTENENCIA from DWH_COMERCIAL.MTR_CLI_GRU a11, DWH_COMERCIAL.M TR_CLI_ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CL 912,717 0 N/A 3.2 266.88 1410.70 5phzqzduf6u0g Module: BTUCK ODBC Oracle create table ZZTIN1VPIMNMD00Q nologging as select a11.ID_CLIENTE ID_CLIENTE, a 11.ID_ENTIDAD ID_ENTIDAD, a11.ID_VERSION ID_VERSION, sum(a11.ME_SDOM_CTO) S ALDOMEDIOMESDIPDIVISA from DWH_COMERCIAL.MTR_CLI_GRU a11, DWH_COMERCIAL.MTR_CL I_ANA a12, DWH_COMERCIAL.LKJ_VERSION a13 where a11.ID_CLIENTE = a12.ID_CLIENTE 770,183 0 N/A 2.7 720.73 3521.46 9avuhs2u911jh Module: BTUCK ODBC Oracle create table VENDA_CREUADA PCTFREE 0, PCTUSED 99 as select pa11.ID_CLIENTE ID _CLIENTE, pa11.ID_ENTIDAD ID_ENTIDAD, max(a128.AT_NOMBRE) AT_NOMBRE, max(a1 28.DE_CLIENTE) DE_CLIENTE, max(a128.AT_CIF) AT_CIF, max(a128.AT_EDAD) AT_ED AD, max(a128.AT_APELLIDO1) AT_APELLIDO1, max(a128.AT_APELLIDO2) AT_APELLIDO2 402,306 0 N/A 1.4 79.68 7051.85 919d204gh39fr Module: BTUCK ODBC Oracle create table xupq_1380_reb_domestic PCTFREE 0, PCTUSED 99 as select a12.ID_PSI G_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_R EC AT_PERSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT _NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_R 380,285 0 N/A 1.3 64.04 7043.23 2b2nrqh228d13 Module: BTUCK ODBC Oracle create table xupq_1380_reb PCTFREE 0, PCTUSED 99 as select a12.ID_xupq_REC ID _PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PE RSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT_NUM_IDE_ FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NU 342,008 0 N/A 1.2 63.73 472.86 c8pp94j4ydvuv Module: SQL*Plus DECLARE vproces VARCHAR2(40); vnumerr NUMBER(2); vcodi NUMB ER(10); vdescripcio VARCHAR2(255); ctlerror DATE; BEGIN vproces:='Inse rt DM_GARANTIAS.LKJ_BIEN_EXTERNO'; vnumerr:=2; INSERT /*+ APPEND */ INTO DM_GA RANTIAS.LKJ_BIEN_EXTERNO ( ID_TI_BIENXT, DE_TI_BIENXT ) SELECT DISTINCT b1.ID 333,160 0 N/A 1.2 73.64 7021.96 87bfaguw09tw5 Module: BTUCK ODBC Oracle create table ZZTIN0OOJZUMQ000 nologging as select a11.ID_CTR_DWH ID_CONTRATO_DW H from DWH_SCGI.MTR_CTRNE_PTE_OPE a11, DWH_SCGI.LKJ_PRODUCTO_GESTION a12, DW SQL ordered by Reads DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total Disk Reads: 28,176,227 -> Captured SQL account for 3.0% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- H_SCGI.LKJ_MES_CONTABLE a13, DWH_SCGI.LKJ_CONTRATO a14, DWH_SCGI.LKJ_LLISTA_ EXTERNA_CONTRACTES a15, OPS$INFOCENT.LKJ_CARTERA_GEST a16 where a11.ID_PRODUCT 318,510 0 N/A 1.1 532.13 1162.35 57kxkgsjyytmk Module: SQL*Plus DECLARE vproces VARCHAR2(40); vnumerr NUMBER(2); vcodi NU MBER(10); vdescripcio VARCHAR2(255); ctlerror DATE; BEGIN vnumerr := 1; vproces := 'dwh03339.sql'; insert /*+ APPEND */ into OPS$EXPLO.MTR_A_CA_SAL POS_D_AUX nologging ( ID_DOE_CONTABLE ,ID_PRODUCTO_GESTION_DWH , 318,504 0 N/A 1.1 532.10 1162.27 57vxbhnfpdhw0 Module: SQL*Plus INSERT /*+ APPEND */ INTO OPS$EXPLO.MTR_A_CA_SALPOS_D_AUX NOLOGGING ( ID_DOE_CON TABLE ,ID_PRODUCTO_GESTION_DWH ,ID_DIVISA ,ID_CANAL_DWH ,ID_CARTERA_GEST_DWH ,ID _ENTIDAD_CUENTA ,ID_SEGMENTO_EST_INI ,ID_SEG_COMER_INI ,ID_CPTO_SALDO_POS_DWH ,I D_EMPRESA_CONTABLE ,ID_CENTRO_CONTABLE ,ID_SERVICIO_COSTE_DWH ,ID_VARIABLE_OPERA ------------------------------------------------------------- SQL ordered by Executions DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total Executions: 1,651,502 -> Captured SQL account for 23.5% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 126,605 126,532 1.0 0.00 0.00 2ym6hhaq30r73 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) fro m seg$ where ts#=:1 and file#=:2 and block#=:3 65,873 62,887 1.0 0.00 0.00 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2 55,489 55,489 1.0 0.00 0.00 9qgtwh66xg6nz update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,: 17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 35,093 63,873 1.8 0.00 0.00 bsa0wjtftg3uw select file# from file$ where ts#=:1 29,602 493,392 16.7 0.00 0.00 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 18,654 18,530 1.0 0.00 0.00 5ngzsfstg8tmy select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# ,o.flags from obj$ o where o.obj#=:1 9,875 10,533 1.1 0.00 0.00 53saa2zkr6wc3 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 9,233 8,320 0.9 0.00 0.00 04xtrk7uyhknh select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an d linkname is null and subname is null 4,522 8,049 1.8 0.00 0.00 6769wyy3yf66f select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1 2,673 37,920 14.2 0.00 0.00 83taa7kaw59c1 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total Parse Calls: 1,166,542 -> Captured SQL account for 21.2% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 126,603 126,605 10.85 2ym6hhaq30r73 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) fro m seg$ where ts#=:1 and file#=:2 and block#=:3 55,489 55,489 4.76 9qgtwh66xg6nz update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,: 17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 35,093 35,093 3.01 bsa0wjtftg3uw select file# from file$ where ts#=:1 2,144 2,144 0.18 g00cj285jmgsw update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob j# = :objn 1,875 9,233 0.16 04xtrk7uyhknh select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an d linkname is null and subname is null 1,797 65,873 0.15 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2 1,600 1,600 0.14 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1 1,594 1,594 0.14 cqgv56fmuj63x select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj# (+) order by order# 1,172 18,654 0.10 5ngzsfstg8tmy select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# ,o.flags from obj$ o where o.obj#=:1 1,136 1,136 0.10 grwydz59pu6mc select text from view$ where rowid=:1 ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id ---------------- ------------ -------- ------------- 1,566,534 0 0.39 2b2nrqh228d13 Module: BTUCK ODBC Oracle create table xupq_1380_reb PCTFREE 0, PCTUSED 99 as select a12.ID_xupq_REC ID _PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_REC AT_PE RSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT_NUM_IDE_ FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_REC AT_NU 1,254,062 0 0.31 919d204gh39fr Module: BTUCK ODBC Oracle create table xupq_1380_reb_domestic PCTFREE 0, PCTUSED 99 as select a12.ID_PSI G_REC ID_PERSONA_REC, a12.ID_xupq_REC ID_PERSONA_REC0, a16.AT_PERSONA_BULL_R EC AT_PERSONA_BULL_REC, a16.AT_PERSONA_BULL_REC AT_PERSONA_BULL_REC0, a16.AT _NUM_IDE_FISCAL_PER_REC AT_NUM_IDE_FISCAL_PER_REC, a16.AT_NUM_IDE_FISCAL_PER_R ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 31 417 0kugqg48477gf select distinct(-privilege#),nvl(option$,0) from sysauth$ where grantee#=:1 and privilege#<0 29 9,875 53saa2zkr6wc3 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 28 116 0h6b2sajwb74n select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p rivilege#>0 start with grantee#=:1 and privilege#>0 28 429 5wc2915k44m38 select user#,type# from user$ where name=:1 26 2,382 f8pavn1bvsj7t select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1 25 2,382 a73wbv1yu8x5c select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro wid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1 23 954 0fr8zhn4ymu3v select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtyp e$ where obj# = :1 order by intcol# asc 22 406 32bhha21dkv0v select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intco l# asc 22 617 6qz82dptj0qr7 select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l. pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.o bj# = :1 order by l.intcol# asc 22 69 9g485acn2n30m select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by i ntcol# asc 21 791 0xwby571mq3n2 select name,password,datats#,tempts#,type#,defrole,resource$, ptime, exptime, lt ime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschcla ss),spare1 from user$ where user#=:1 21 406 9rfqm06xmuwu0 select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltyp e$ where obj#=:1 order by intcol# asc 21 406 f3g84j69n0tjh select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc ------------------------------------------------------------- Instance Activity Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 2,767,947 385.2 2,404.8 CPU used when call started 2,541,366 353.7 2,208.0 CR blocks created 92,496 12.9 80.4 Cached Commit SCN referenced 20,181,067 2,808.7 17,533.5 Commit SCN cached 8,030 1.1 7.0 DB time 23,458,652 3,264.8 20,381.1 DBWR checkpoint buffers written 12,893 1.8 11.2 DBWR checkpoints 19,249 2.7 16.7 DBWR object drop buffers written 58,580 8.2 50.9 DBWR parallel query checkpoint b 0 0.0 0.0 DBWR revisited being-written buf 21,554 3.0 18.7 DBWR tablespace checkpoint buffe 0 0.0 0.0 DBWR thread checkpoint buffers w 1,864 0.3 1.6 DBWR transaction table writes 1,526 0.2 1.3 DBWR undo block writes 529,456 73.7 460.0 DDL statements parallelized 0 0.0 0.0 DFO trees parallelized 5 0.0 0.0 IMU CR rollbacks 15 0.0 0.0 IMU Flushes 1,780 0.3 1.6 IMU Redo allocation size 1,556,368 216.6 1,352.2 IMU commits 473 0.1 0.4 IMU contention 21 0.0 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 28 0.0 0.0 IMU recursive-transaction flush 1 0.0 0.0 IMU undo allocation size 1,861,056 259.0 1,616.9 IMU- failed to get a private str 28 0.0 0.0 Misses for writing mapping 0 0.0 0.0 OS Block output operations 0 0.0 0.0 OS Characters read/written 0 0.0 0.0 OS Integral unshared data size 0 0.0 0.0 OS Involuntary context switches 0 0.0 0.0 OS Maximum resident set size 0 0.0 0.0 OS Page faults 0 0.0 0.0 OS Page reclaims 0 0.0 0.0 OS Signals received 0 0.0 0.0 OS Socket messages received 0 0.0 0.0 OS Socket messages sent 0 0.0 0.0 OS System time used 0 0.0 0.0 OS User time used 0 0.0 0.0 OS Voluntary context switches 0 0.0 0.0 PX local messages recv'd 1,832,433 255.0 1,592.0 PX local messages sent 1,832,462 255.0 1,592.1 Parallel operations downgraded 1 0 0.0 0.0 Parallel operations downgraded 2 0 0.0 0.0 Parallel operations downgraded 5 0 0.0 0.0 Parallel operations downgraded t 0 0.0 0.0 Parallel operations not downgrad 5 0.0 0.0 SMON posted for dropping temp se 0 0.0 0.0 SMON posted for undo segment rec 0 0.0 0.0 SMON posted for undo segment shr 3 0.0 0.0 SQL*Net roundtrips to/from clien 10,628,689 1,479.2 9,234.3 SQL*Net roundtrips to/from dblin 0 0.0 0.0 active txn count during cleanout 186,542 26.0 162.1 application wait time 26,851 3.7 23.3 auto extends on undo tablespace 0 0.0 0.0 background checkpoints completed 54 0.0 0.1 background checkpoints started 54 0.0 0.1 background timeouts 32,498 4.5 28.2 branch node splits 341 0.1 0.3 Instance Activity Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- buffer is not pinned count 262,850,182 36,581.8 228,366.8 buffer is pinned count 59,842,202 8,328.5 51,991.5 bytes received via SQL*Net from 4,247,773,659 591,178.2 3,690,507.1 bytes received via SQL*Net from 0 0.0 0.0 bytes sent via SQL*Net to client 6,184,391,680 860,704.4 5,373,059.7 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgs 4,679,380 651.3 4,065.5 calls to kcmgas 2,073,761 288.6 1,801.7 calls to kcmgcs 62,642 8.7 54.4 change write time 49,451 6.9 43.0 cleanout - number of ktugct call 344,337 47.9 299.2 cleanouts and rollbacks - consis 7,909 1.1 6.9 cleanouts only - consistent read 93,662 13.0 81.4 cluster key scan block gets 6,878,491 957.3 5,976.1 cluster key scans 6,614,887 920.6 5,747.1 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 4,308 0.6 3.7 commit batch/immediate requested 4,308 0.6 3.7 commit cleanout failures: block 5,081 0.7 4.4 commit cleanout failures: buffer 139 0.0 0.1 commit cleanout failures: callba 22 0.0 0.0 commit cleanout failures: cannot 119 0.0 0.1 commit cleanouts 462,619 64.4 401.9 commit cleanouts successfully co 457,258 63.6 397.3 commit immediate performed 4,308 0.6 3.7 commit immediate requested 4,308 0.6 3.7 commit txn count during cleanout 315,124 43.9 273.8 concurrency wait time 47,739 6.6 41.5 consistent changes 2,442,241 339.9 2,121.8 consistent gets 307,163,174 42,749.0 266,866.4 consistent gets - examination 174,746,461 24,320.1 151,821.4 consistent gets direct 57,173 8.0 49.7 consistent gets from cache 307,106,001 42,741.1 266,816.7 current blocks converted for CR 122,292 17.0 106.3 cursor authentications 10,231 1.4 8.9 data blocks consistent reads - u 1,766,457 245.8 1,534.7 db block changes 84,426,037 11,749.9 73,350.2 db block gets 117,560,535 16,361.3 102,137.7 db block gets direct 829,497 115.4 720.7 db block gets from cache 116,731,090 16,245.9 101,417.1 deferred (CURRENT) block cleanou 183,193 25.5 159.2 dirty buffers inspected 1,088,610 151.5 945.8 drop segment calls in space pres 0 0.0 0.0 enqueue conversions 318,732 44.4 276.9 enqueue releases 1,731,157 240.9 1,504.1 enqueue requests 1,763,794 245.5 1,532.4 enqueue timeouts 32,650 4.5 28.4 enqueue waits 19,389 2.7 16.9 exchange deadlocks 28 0.0 0.0 execute count 1,651,502 229.9 1,434.8 failed probes on index block rec 0 0.0 0.0 frame signature mismatch 0 0.0 0.0 free buffer inspected 25,298,597 3,520.9 21,979.7 free buffer requested 25,329,241 3,525.2 22,006.3 global undo segment hints helped 204 0.0 0.2 global undo segment hints were s 1 0.0 0.0 heap block compress 22,206 3.1 19.3 hot buffers moved to head of LRU 157,526 21.9 136.9 immediate (CR) block cleanout ap 101,571 14.1 88.3 Instance Activity Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- immediate (CURRENT) block cleano 266,617 37.1 231.6 index crx upgrade (found) 8 0.0 0.0 index crx upgrade (positioned) 67,715 9.4 58.8 index fast full scans (direct re 0 0.0 0.0 index fast full scans (full) 141 0.0 0.1 index fast full scans (rowid ran 156 0.0 0.1 index fetch by key 90,013,898 12,527.6 78,205.0 index scans kdiixs1 1,812,347 252.2 1,574.6 java call heap collected bytes 0 0.0 0.0 java call heap collected count 0 0.0 0.0 java call heap gc count 0 0.0 0.0 java call heap live object count 0 0.0 0.0 java call heap live object count 0 0.0 0.0 java call heap live size 0 0.0 0.0 java call heap live size max 0 0.0 0.0 java call heap object count 0 0.0 0.0 java call heap object count max 0 0.0 0.0 java call heap total size 0 0.0 0.0 java call heap total size max 0 0.0 0.0 java call heap used size 0 0.0 0.0 java call heap used size max 0 0.0 0.0 leaf node 90-10 splits 123,016 17.1 106.9 leaf node splits 128,880 17.9 112.0 lob reads 12,588 1.8 10.9 lob writes 15,507 2.2 13.5 lob writes unaligned 15,507 2.2 13.5 local undo segment hints helped 0 0.0 0.0 local undo segment hints were st 0 0.0 0.0 logons cumulative 1,234 0.2 1.1 messages received 645,943 89.9 561.2 messages sent 645,943 89.9 561.2 no buffer to keep pinned count 38,456 5.4 33.4 no work - consistent read gets 130,987,317 18,230.0 113,803.1 opened cursors cumulative 1,492,174 207.7 1,296.4 parse count (failures) 149 0.0 0.1 parse count (hard) 35,373 4.9 30.7 parse count (total) 1,166,542 162.4 1,013.5 parse time cpu 24,178 3.4 21.0 parse time elapsed 102,519 14.3 89.1 physical read IO requests 6,583,492 916.3 5,719.8 physical read bytes 923,398,635,520 128,512,761.9 ############# physical read total IO requests 6,614,718 920.6 5,746.9 physical read total bytes 923,964,860,416 128,591,565.5 ############# physical read total multi block 1,691,253 235.4 1,469.4 physical reads 28,176,227 3,921.4 24,479.8 physical reads cache 24,474,961 3,406.3 21,264.1 physical reads cache prefetch 19,591,830 2,726.7 17,021.6 physical reads direct 3,704,929 515.6 3,218.9 physical reads direct (lob) 273 0.0 0.2 physical reads direct temporary 3,624,222 504.4 3,148.8 physical write IO requests 2,101,347 292.5 1,825.7 physical write bytes 195,103,752,192 27,153,302.0 ############# physical write total IO requests 1,758,611 244.8 1,527.9 physical write total bytes 223,614,546,944 31,121,253.5 ############# physical write total multi block 1,148,830 159.9 998.1 physical writes 5,931,731 825.5 5,153.6 physical writes direct 4,718,018 656.6 4,099.1 physical writes direct (lob) 9 0.0 0.0 physical writes direct temporary 3,887,731 541.1 3,377.7 physical writes from cache 1,236,076 172.0 1,073.9 Instance Activity Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical writes non checkpoint 5,941,409 826.9 5,162.0 pinned buffers inspected 70,313 9.8 61.1 prefetch clients - default 0 0.0 0.0 prefetched blocks aged out befor 747,778 104.1 649.7 process last non-idle time 7,171 1.0 6.2 queries parallelized 5 0.0 0.0 recursive calls 23,278,629 3,239.8 20,224.7 recursive cpu usage 1,394,183 194.0 1,211.3 redo blocks written 27,100,293 3,771.7 23,545.0 redo buffer allocation retries 11,229 1.6 9.8 redo entries 42,911,976 5,972.2 37,282.3 redo log space requests 9,885 1.4 8.6 redo log space wait time 1,069 0.2 0.9 redo ordering marks 553,091 77.0 480.5 redo size 27,251,818,528 3,792,735.2 23,676,645.1 redo subscn max counts 1,005,965 140.0 874.0 redo synch time 653 0.1 0.6 redo synch writes 6,858 1.0 6.0 redo wastage 114,942,072 15,996.9 99,862.8 redo write time 50,356 7.0 43.8 redo writer latching time 1,198 0.2 1.0 redo writes 189,972 26.4 165.1 rollback changes - undo records 14,175 2.0 12.3 rollbacks only - consistent read 199,983 27.8 173.8 rows fetched via callback 81,109,255 11,288.3 70,468.5 session connect time 0 0.0 0.0 session cursor cache hits 1,350,059 187.9 1,172.9 session logical reads 424,723,664 59,110.3 369,004.1 session pga memory 946,335,128 131,704.9 822,185.2 session pga memory max 1,040,478,936 144,807.3 903,978.2 session uga memory 73,072,569,808 10,169,776.5 63,486,159.7 session uga memory max 27,354,300,344 3,806,997.9 23,765,682.3 shared hash latch upgrades - no 1,514,943 210.8 1,316.2 shared hash latch upgrades - wai 48 0.0 0.0 sorts (disk) 55 0.0 0.1 sorts (memory) 265,889 37.0 231.0 sorts (rows) 2,508,490,724 349,115.8 2,179,401.2 space was found by tune down 0 0.0 0.0 space was not found by tune down 0 0.0 0.0 sql area evicted 13,565 1.9 11.8 sql area purged 500 0.1 0.4 steps of tune down ret. in space 0 0.0 0.0 summed dirty queue length 2,057,413 286.3 1,787.5 switch current to new buffer 5,614 0.8 4.9 table fetch by rowid 91,853,737 12,783.6 79,803.4 table fetch continued row 220,417 30.7 191.5 table scan blocks gotten 30,868,405 4,296.1 26,818.8 table scan rows gotten 9,801,341,403 1,364,088.5 8,515,500.8 table scans (cache partitions) 0 0.0 0.0 table scans (direct read) 220 0.0 0.2 table scans (long tables) 7,173 1.0 6.2 table scans (rowid ranges) 220 0.0 0.2 table scans (short tables) 187,419 26.1 162.8 total number of times SMON poste 37,541 5.2 32.6 transaction rollbacks 4,308 0.6 3.7 transaction tables consistent re 484 0.1 0.4 transaction tables consistent re 613,828 85.4 533.3 tune down retentions in space pr 0 0.0 0.0 undo change vector size 11,311,284,144 1,574,232.7 9,827,353.7 user I/O wait time 10,518,019 1,463.8 9,138.2 Instance Activity Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- user calls 10,554,596 1,468.9 9,169.9 user commits 1,131 0.2 1.0 user rollbacks 20 0.0 0.0 workarea executions - multipass 2 0.0 0.0 workarea executions - onepass 92 0.0 0.1 workarea executions - optimal 117,206 16.3 101.8 write clones created in backgrou 57,320 8.0 49.8 write clones created in foregrou 5,149 0.7 4.5 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: SEPBZ212/SEPBZ212 Snaps: 7 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 1,962,802 1,982,323 opened cursors current 328 414 workarea memory allocated 5,277,708 5,437,444 logons current 78 89 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: SEPBZ212/SEPBZ212 Snaps: 73 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 54 27.06 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TEMP 1,678,928 234 0.0 2.2 827,823 115 0 0.0 TS_RASSEGURANCES_DAT 1,302,065 181 12.6 3.6 396,118 55 16,734 25.6 TS_SCGI_DAT 920,511 128 18.7 2.3 624 0 165,468 18.4 TS_SCGI_IDX 883,441 123 16.9 1.1 725 0 218,241 13.4 UNDOTBS1 302,588 42 7.8 1.0 421,061 59 580 0.9 TS_DMCOMER_DAT 586,221 82 32.5 15.4 1,076 0 9 36.7 SYSTEM 301,792 42 8.2 5.9 5,517 1 10,487 6.7 TS_SCGIDOE_IDX 146,315 20 4.9 1.1 160,954 22 0 0.0 TS_DMCOMER_IDX 165,853 23 17.0 1.0 151 0 0 0.0 TS_CRM_DAT 79,828 11 23.6 20.3 57,456 8 173 24.5 TREBALL 47,641 7 33.1 22.1 60,183 8 1,224 46.8 TS_CRM_IDX 30,693 4 9.2 2.8 50,070 7 8 25.0 TS_GARANTIAS_IDX 12,576 2 8.5 2.8 40,312 6 0 0.0 TS_ODR_IDX 4,257 1 19.3 10.2 34,459 5 1 10.0 TS_SCGIDOE_DAT 14,851 2 43.3 25.8 12,330 2 124 41.8 TS_BSG_DAT 25,774 4 42.1 31.4 40 0 0 0.0 TS_GARANTIAS_DAT 19,038 3 34.6 25.5 4,627 1 1 0.0 TS_ODR_DAT 11,925 2 37.4 27.6 9,926 1 0 0.0 USUARIOS 3,637 1 24.3 12.4 8,340 1 48 0.2 TS_MET_DAT 8,779 1 15.2 2.5 107 0 37 7.0 TS_TES_IDX 4,515 1 7.3 3.7 4,154 1 2 50.0 SYSAUX 5,496 1 10.7 1.1 2,564 0 79 3.8 TS_DAS_DAT 7,610 1 35.4 18.3 42 0 4,732 27.2 TS_OSC_DAT 4,637 1 34.5 26.4 72 0 133 25.4 TS_ODC_DAT 3,178 0 34.4 28.1 62 0 0 0.0 TS_TES_DAT 1,005 0 11.0 3.7 1,046 0 0 0.0 TS_MET_IDX 1,810 0 24.1 19.3 101 0 29 22.8 TS_DCS_DAT 1,463 0 18.5 4.6 256 0 5 50.0 TS_SIG_DAT 505 0 6.7 1.0 92 0 0 0.0 TS_MOT_DAT 342 0 6.8 1.0 64 0 0 0.0 Tablespace IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ USERS 323 0 8.9 1.4 65 0 2 0.0 TS_DCS_IDX 318 0 7.6 1.0 62 0 0 0.0 TS_SIG_IDX 315 0 7.0 1.0 63 0 0 0.0 TS_RFINANCIERO_DAT 236 0 4.5 1.0 60 0 0 0.0 TS_ODC_IDX 220 0 7.0 1.0 61 0 0 0.0 TS_RASSEGURANCES_IDX 186 0 6.5 1.0 83 0 0 0.0 TS_GESTIO_DAT 229 0 7.1 1.0 30 0 0 0.0 TS_RAD_DAT 205 0 7.0 1.0 30 0 0 0.0 TS_PREIBER_DAT 183 0 4.3 1.5 33 0 0 0.0 TS_DATAMARTS_DAT 174 0 11.8 1.0 31 0 0 0.0 TS_DWHIBE_DAT 170 0 3.3 1.0 31 0 0 0.0 TS_DWHIBE_IDX 170 0 3.8 1.0 31 0 0 0.0 TS_FSO_DAT 170 0 4.0 1.0 31 0 0 0.0 TS_FSO_IDX 170 0 3.3 1.0 31 0 0 0.0 TS_MIA_DAT 170 0 2.9 1.0 31 0 0 0.0 TS_MIA_IDX 170 0 3.4 1.0 31 0 0 0.0 TS_ODSFISC_DAT 170 0 2.8 1.0 31 0 0 0.0 TS_ODSFISC_IDX 170 0 3.2 1.0 31 0 0 0.0 TS_PREIBER_IDX 170 0 2.9 1.0 31 0 0 0.0 TS_PMS_DAT 168 0 4.5 1.0 31 0 0 0.0 TS_SICC_DAT 168 0 2.8 1.0 31 0 0 0.0 TS_OSC_IDX 164 0 9.0 1.0 30 0 11 16.4 DWH_OFEX_DAT 156 0 3.5 1.0 31 0 0 0.0 DWH_OFEX_IDX 156 0 3.8 1.0 31 0 0 0.0 TS_BCK_DAT 156 0 3.4 1.0 31 0 0 0.0 TS_BCK_IDX 156 0 3.2 1.0 31 0 0 0.0 TS_DATAMARTS_IDX 157 0 5.2 1.0 30 0 0 0.0 TS_DWHFISC_DAT 156 0 4.0 1.0 31 0 0 0.0 TS_DWHFISC_IDX 156 0 3.5 1.0 31 0 0 0.0 TS_PMS_IDX 156 0 2.8 1.0 31 0 0 0.0 Tablespace IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_RAD_IDX 157 0 4.2 1.0 30 0 0 0.0 TS_SICC_IDX 156 0 2.6 1.0 31 0 0 0.0 TS_VALOWN_DAT 157 0 4.6 1.0 30 0 0 0.0 TS_ODS_TAR_DAT 154 0 4.0 1.0 30 0 0 0.0 TS_DAS_IDX 148 0 4.2 1.0 30 0 0 0.0 TS_OSD_DAT 145 0 3.2 1.0 30 0 0 0.0 TS_OSD_IDX 145 0 4.3 1.0 30 0 0 0.0 TS_TEL_DAT 145 0 3.4 1.0 30 0 0 0.0 TS_VALOWN_IDX 145 0 4.1 1.0 30 0 0 0.0 TS_GESTIO_IDX 142 0 3.7 1.0 30 0 0 0.0 TS_ODS_TAR_IDX 142 0 3.3 1.0 30 0 0 0.0 TS_TEL_IDX 142 0 3.2 1.0 30 0 0 0.0 TS_DMF_DAT 118 0 4.7 1.0 30 0 0 0.0 TS_DMF_IDX 118 0 3.8 1.0 30 0 0 0.0 TS_MOT_IDX 118 0 3.5 1.0 30 0 0 0.0 TS_RFINANCIERO_IDX 118 0 6.7 1.0 30 0 0 0.0 TS_BSG_IDX 90 0 4.8 1.0 31 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ DWH_OFEX_DAT +SEPBZ212_DG01/SEPBZ212/datafile/dwh_ofex_dat.286.66 156 0 3.5 1.0 31 0 0 0.0 DWH_OFEX_IDX +SEPBZ212_DG01/SEPBZ212/datafile/dwh_ofex_idx.285.66 156 0 3.8 1.0 31 0 0 0.0 SYSAUX +PRODWH_DG01/SEPBZ212/datafile/sysaux.274.641486001 5,496 1 10.7 1.1 2,564 0 79 3.8 SYSTEM +PRODWH_DG01/SEPBZ212/datafile/system.272.641485991 301,792 42 8.2 5.9 5,517 1 10,487 6.7 TEMP +SEPBZ212_DG03/SEPBZ212/tempfile/temp.259.647342285 1,371,467 191 0.0 2.2 562,708 78 0 N/A TEMP +SEPBZ212_DG04/SEPBZ212/tempfile/temp.256.647342295 190,019 26 0.0 2.1 230,797 32 0 N/A TEMP +PRODWH_DG01/SEPBZ212/tempfile/temp.275.641486003 117,442 16 0.0 1.6 34,318 5 0 N/A TREBALL +SEPBZ212_DG02/SEPBZ212/datafile/treball.267.6624720 16,304 2 29.0 22.0 20,451 3 433 37.9 TREBALL +SEPBZ212_DG04/SEPBZ212/datafile/treball.257.6482067 14,511 2 32.2 22.4 18,707 3 368 47.7 TREBALL +SEPBZ212_DG06/SEPBZ212/datafile/treball.270.6618565 16,244 2 38.9 22.8 20,715 3 417 55.9 TREBALL +PRODWH_DG01/SEPBZ212/datafile/treball.278.644168925 582 0 5.9 2.3 310 0 6 1.7 TS_BCK_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_bck_dat.282.6614 156 0 3.4 1.0 31 0 0 0.0 TS_BCK_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_bck_idx.283.6614 156 0 3.2 1.0 31 0 0 0.0 TS_BSG_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_bsg_dat.260.6476 25,774 4 42.1 31.4 40 0 0 0.0 TS_BSG_IDX +SEPBZ212_DG03/SEPBZ212/datafile/ts_bsg_idx.261.6477 90 0 4.8 1.0 31 0 0 0.0 TS_CRM_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_crm_dat.256.6460 79,828 11 23.6 20.3 57,456 8 173 24.5 TS_CRM_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_crm_idx.257.6446 30,693 4 9.2 2.8 50,070 7 8 25.0 TS_DAS_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_das_dat.275.6581 7,610 1 35.4 18.3 42 0 4,732 27.2 TS_DAS_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_das_idx.276.6581 148 0 4.2 1.0 30 0 0 0.0 TS_DATAMARTS_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_datamarts_dat.25 174 0 11.8 1.0 31 0 0 0.0 TS_DATAMARTS_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_datamarts_idx.25 157 0 5.2 1.0 30 0 0 0.0 TS_DCS_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_dcs_dat.258.6446 181 0 17.1 4.3 31 0 1 10.0 TS_DCS_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_dcs_dat.259.6446 184 0 21.4 6.0 31 0 1 90.0 TS_DCS_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_dcs_dat.260.6446 196 0 30.5 7.2 33 0 1 50.0 TS_DCS_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_dcs_dat.261.6446 194 0 16.8 4.3 33 0 1 50.0 TS_DCS_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_dcs_dat.256.6446 182 0 15.8 4.2 34 0 1 50.0 TS_DCS_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_dcs_dat.257.6446 179 0 14.6 4.1 32 0 0 0.0 TS_DCS_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_dcs_dat.258.6446 176 0 14.6 3.3 31 0 0 0.0 TS_DCS_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_dcs_dat.259.6446 171 0 16.2 2.7 31 0 0 0.0 TS_DCS_IDX +SEPBZ212_DG03/SEPBZ212/datafile/ts_dcs_idx.256.6447 144 0 7.6 1.0 31 0 0 0.0 File IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_DCS_IDX +SEPBZ212_DG03/SEPBZ212/datafile/ts_dcs_idx.262.6495 174 0 7.6 1.0 31 0 0 0.0 TS_DMCOMER_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_dmcomer_dat.260. 110,020 15 36.7 15.7 120 0 1 30.0 TS_DMCOMER_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_dmcomer_dat.264. 103,057 14 26.5 10.5 244 0 1 70.0 TS_DMCOMER_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_dmcomer_dat.268. 70,068 10 37.1 17.5 121 0 2 35.0 TS_DMCOMER_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_dmcomer_dat.269. 65,265 9 38.3 18.5 101 0 1 10.0 TS_DMCOMER_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_dmcomer_dat.256. 111,136 15 31.6 18.9 251 0 0 0.0 TS_DMCOMER_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_dmcomer_dat.266. 126,675 18 28.9 13.4 239 0 4 37.5 TS_DMCOMER_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_dmcomer_idx.264. 35,685 5 16.9 1.0 30 0 0 0.0 TS_DMCOMER_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_dmcomer_idx.266. 37,720 5 16.6 1.0 30 0 0 0.0 TS_DMCOMER_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_dmcomer_idx.257. 30,399 4 17.1 1.0 31 0 0 0.0 TS_DMCOMER_IDX +SEPBZ212_DG06/SEPBZ212/datafile/ts_dmcomer_idx.257. 29,400 4 17.4 1.0 30 0 0 0.0 TS_DMCOMER_IDX +SEPBZ212_DG06/SEPBZ212/datafile/ts_dmcomer_idx.258. 32,649 5 17.1 1.0 30 0 0 0.0 TS_DMF_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_dmf_dat.262.6521 118 0 4.7 1.0 30 0 0 0.0 TS_DMF_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_dmf_idx.261.6521 118 0 3.8 1.0 30 0 0 0.0 TS_DWHFISC_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_dwhfisc_dat.287. 156 0 4.0 1.0 31 0 0 0.0 TS_DWHFISC_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_dwhfisc_idx.288. 156 0 3.5 1.0 31 0 0 0.0 TS_DWHIBE_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_dwhibe_dat.297.6 170 0 3.3 1.0 31 0 0 0.0 TS_DWHIBE_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_dwhibe_idx.298.6 170 0 3.8 1.0 31 0 0 0.0 TS_FSO_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_fso_dat.291.6680 170 0 4.0 1.0 31 0 0 0.0 TS_FSO_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_fso_idx.292.6680 170 0 3.3 1.0 31 0 0 0.0 TS_GARANTIAS_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_garantias_dat.27 19,038 3 34.6 25.5 4,627 1 1 0.0 TS_GARANTIAS_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_garantias_idx.27 12,576 2 8.5 2.8 40,312 6 0 0.0 TS_GESTIO_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_gestio_dat.277.6 229 0 7.1 1.0 30 0 0 0.0 TS_GESTIO_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_gestio_idx.279.6 142 0 3.7 1.0 30 0 0 0.0 TS_MET_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_met_dat.262.6447 8,779 1 15.2 2.5 107 0 37 7.0 TS_MET_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_met_idx.263.6447 1,810 0 24.1 19.3 101 0 29 22.8 TS_MIA_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_mia_dat.294.6681 170 0 2.9 1.0 31 0 0 0.0 TS_MIA_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_mia_idx.293.6681 170 0 3.4 1.0 31 0 0 0.0 TS_MOT_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_mot_dat.263.6505 181 0 9.6 1.0 33 0 0 0.0 TS_MOT_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_mot_dat.272.6662 161 0 3.7 1.0 31 0 0 0.0 File IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_MOT_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_mot_idx.267.6533 118 0 3.5 1.0 30 0 0 0.0 TS_ODC_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_odc_dat.260.6505 2,988 0 36.0 29.8 32 0 0 0.0 TS_ODC_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_odc_dat.259.6506 190 0 8.5 1.0 30 0 0 0.0 TS_ODC_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_odc_idx.261.6505 90 0 7.0 1.0 31 0 0 0.0 TS_ODC_IDX +SEPBZ212_DG06/SEPBZ212/datafile/ts_odc_idx.260.6506 130 0 7.0 1.0 30 0 0 0.0 TS_ODR_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_odr_dat.264.6610 4,895 1 36.1 26.5 3,944 1 0 0.0 TS_ODR_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_odr_dat.264.6505 7,030 1 38.3 28.4 5,982 1 0 0.0 TS_ODR_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_odr_idx.266.6505 4,257 1 19.3 10.2 34,459 5 1 10.0 TS_ODSFISC_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_odsfisc_dat.289. 170 0 2.8 1.0 31 0 0 0.0 TS_ODSFISC_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_odsfisc_idx.290. 170 0 3.2 1.0 31 0 0 0.0 TS_ODS_TAR_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_ods_tar_dat.266. 154 0 4.0 1.0 30 0 0 0.0 TS_ODS_TAR_IDX +SEPBZ212_DG03/SEPBZ212/datafile/ts_ods_tar_idx.267. 142 0 3.3 1.0 30 0 0 0.0 TS_OSC_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_osc_dat.281.6610 2,391 0 33.9 25.9 32 0 72 26.1 TS_OSC_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_osc_dat.265.6505 2,246 0 35.1 27.0 40 0 61 24.6 TS_OSC_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_osc_idx.267.6505 164 0 9.0 1.0 30 0 11 16.4 TS_OSD_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_osd_dat.262.6572 145 0 3.2 1.0 30 0 0 0.0 TS_OSD_IDX +SEPBZ212_DG06/SEPBZ212/datafile/ts_osd_idx.267.6572 145 0 4.3 1.0 30 0 0 0.0 TS_PMS_DAT +SEPBZ212_DG08/SEPBZ212/datafile/ts_pms_dat.256.6667 168 0 4.5 1.0 31 0 0 0.0 TS_PMS_IDX +SEPBZ212_DG08/SEPBZ212/datafile/ts_pms_idx.257.6667 156 0 2.8 1.0 31 0 0 0.0 TS_PREIBER_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_preiber_dat.295. 183 0 4.3 1.5 33 0 0 0.0 TS_PREIBER_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_preiber_idx.296. 170 0 2.9 1.0 31 0 0 0.0 TS_RAD_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_rad_dat.273.6572 205 0 7.0 1.0 30 0 0 0.0 TS_RAD_IDX +SEPBZ212_DG02/SEPBZ212/datafile/ts_rad_idx.261.6572 157 0 4.2 1.0 30 0 0 0.0 TS_RASSEGURANCES_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_rassegurances_da 400,981 56 14.7 5.2 94,005 13 6,957 29.0 TS_RASSEGURANCES_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_rassegurances_da 901,084 125 11.7 2.9 302,113 42 9,777 23.1 TS_RASSEGURANCES_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_rassegurances_id 186 0 6.5 1.0 83 0 0 0.0 TS_RFINANCIERO_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_rfinanciero_dat. 118 0 4.5 1.0 30 0 0 0.0 TS_RFINANCIERO_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_rfinanciero_dat. 118 0 4.6 1.0 30 0 0 0.0 TS_RFINANCIERO_IDX +SEPBZ212_DG06/SEPBZ212/datafile/ts_rfinanciero_idx. 118 0 6.7 1.0 30 0 0 0.0 TS_SCGIDOE_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_scgidia_dat.263. 5,477 1 40.0 25.5 4,764 1 54 36.1 File IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_SCGIDOE_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_scgidia_dat.268. 4,016 1 41.8 25.1 3,637 1 33 42.7 TS_SCGIDOE_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_scgidia_dat.268. 5,358 1 47.9 26.6 3,929 1 37 49.2 TS_SCGIDOE_IDX +SEPBZ212_DG03/SEPBZ212/datafile/ts_scgidia_idx.269. 73,460 10 4.9 1.1 81,810 11 0 0.0 TS_SCGIDOE_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_scgidia_idx.265. 72,855 10 4.9 1.1 79,144 11 0 0.0 TS_SCGI_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_seom_dta.280.661 63,410 9 18.6 2.2 47 0 11,619 19.4 TS_SCGI_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_seom_dta.284.662 62,562 9 18.8 2.2 42 0 15,087 18.1 TS_SCGI_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_seom_dta.263.660 52,844 7 18.7 2.4 40 0 8,531 18.4 TS_SCGI_DAT +SEPBZ212_DG02/SEPBZ212/datafile/ts_seom_dta.266.661 53,846 7 18.2 2.4 37 0 10,881 17.0 TS_SCGI_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_seom_dta.257.644 61,857 9 18.7 2.3 42 0 10,657 18.6 TS_SCGI_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_seom_dta.270.661 64,821 9 17.7 2.2 39 0 8,791 18.3 TS_SCGI_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_seom_dta.271.661 55,938 8 18.9 2.3 40 0 10,261 17.7 TS_SCGI_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_seom_dta.272.661 65,851 9 18.5 2.1 40 0 14,473 18.3 TS_SCGI_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_seom_dta.264.655 58,771 8 18.8 2.3 49 0 11,297 18.7 TS_SCGI_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_seom_dta.267.661 57,630 8 18.2 2.3 39 0 10,476 18.5 TS_SCGI_DAT +SEPBZ212_DG05/SEPBZ212/datafile/ts_seom_dta.270.661 53,555 7 19.9 2.4 39 0 8,794 19.6 TS_SCGI_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_seom_dta.265.652 60,752 8 19.3 2.3 43 0 10,403 19.2 TS_SCGI_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_seom_dta.269.661 64,862 9 19.0 2.2 40 0 9,718 18.7 TS_SCGI_DAT +SEPBZ212_DG06/SEPBZ212/datafile/ts_seom_dta.271.661 54,284 8 19.4 2.4 39 0 10,476 18.4 TS_SCGI_DAT +SEPBZ212_DG07/SEPBZ212/datafile/ts_seom_dta.258.662 89,528 12 18.5 2.1 48 0 14,004 18.1 TS_SCGI_IDX +SEPBZ212_DG02/SEPBZ212/datafile/ts_scgi_idx.265.661 97,176 14 16.1 1.1 163 0 24,073 12.9 TS_SCGI_IDX +SEPBZ212_DG02/SEPBZ212/datafile/ts_scgi_idx.268.662 93,805 13 16.3 1.1 42 0 24,451 12.9 TS_SCGI_IDX +SEPBZ212_DG03/SEPBZ212/datafile/ts_scgi_idx.268.661 100,911 14 17.1 1.1 88 0 22,167 13.5 TS_SCGI_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_scgi_idx.263.652 102,716 14 17.3 1.1 65 0 23,408 13.7 TS_SCGI_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_scgi_idx.269.661 99,083 14 16.9 1.1 154 0 23,651 13.5 TS_SCGI_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_scgi_idx.262.650 91,566 13 17.6 1.1 46 0 23,815 14.0 TS_SCGI_IDX +SEPBZ212_DG06/SEPBZ212/datafile/ts_scgi_idx.264.652 99,962 14 17.4 1.1 44 0 25,135 14.2 TS_SCGI_IDX +SEPBZ212_DG07/SEPBZ212/datafile/ts_scgi_idx.256.661 99,473 14 16.8 1.1 38 0 26,035 13.0 TS_SCGI_IDX +SEPBZ212_DG07/SEPBZ212/datafile/ts_scgi_idx.257.661 98,749 14 16.4 1.1 85 0 25,506 13.0 TS_SICC_DAT +PRODWH_DG01/SEPBZ212/datafile/ts_sicc_dat.269.66324 168 0 2.8 1.0 31 0 0 0.0 TS_SICC_IDX +PRODWH_DG01/SEPBZ212/datafile/ts_sicc_idx.270.66324 156 0 2.6 1.0 31 0 0 0.0 File IO Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_SIG_DAT +SEPBZ212_DG03/SEPBZ212/datafile/ts_sig_dat.265.6581 157 0 4.4 1.0 30 0 0 0.0 TS_SIG_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_sig_dat.258.6496 174 0 10.1 1.0 31 0 0 0.0 TS_SIG_DAT +SEPBZ212_DG04/SEPBZ212/datafile/ts_sig_dat.260.6498 174 0 5.3 1.0 31 0 0 0.0 TS_SIG_IDX +SEPBZ212_DG04/SEPBZ212/datafile/ts_sig_idx.259.6497 141 0 7.7 1.0 32 0 0 0.0 TS_SIG_IDX +SEPBZ212_DG05/SEPBZ212/datafile/ts_sig_idx.256.6498 174 0 6.4 1.0 31 0 0 0.0 TS_TEL_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_tel_dat.274.6595 145 0 3.4 1.0 30 0 0 0.0 TS_TEL_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_tel_idx.278.6595 142 0 3.2 1.0 30 0 0 0.0 TS_TES_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_tes_dat.268.6546 1,005 0 11.0 3.7 1,046 0 0 0.0 TS_TES_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_tes_idx.269.6546 4,515 1 7.3 3.7 4,154 1 2 50.0 TS_VALOWN_DAT +SEPBZ212_DG01/SEPBZ212/datafile/ts_valown_dat.271.6 157 0 4.6 1.0 30 0 0 0.0 TS_VALOWN_IDX +SEPBZ212_DG01/SEPBZ212/datafile/ts_valown_idx.270.6 145 0 4.1 1.0 30 0 0 0.0 UNDOTBS1 +PRODWH_DG01/SEPBZ212/datafile/undotbs1.273.64148599 302,588 42 7.8 1.0 421,061 59 580 0.9 USERS +PRODWH_DG01/SEPBZ212/datafile/users.276.641486005 179 0 9.7 1.6 34 0 0 0.0 USERS +PRODWH_DG01/SEPBZ212/datafile/users.279.644350559 144 0 8.0 1.2 31 0 2 0.0 USUARIOS +SEPBZ212_DG03/SEPBZ212/datafile/usuarios.258.646750 1,689 0 26.8 13.4 4,165 1 45 0.2 USUARIOS +SEPBZ212_DG03/SEPBZ212/datafile/usuarios.273.670069 1,948 0 22.1 11.5 4,175 1 3 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D 17,915 94 423,807,253 24,471,007 1,236,034 22 0 418,205 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 34 424 19382 44319 3225600 44319 N/A E 0 34 375 3837 3225600 3225600 4014694 N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 60 .1 1,875 1.2 1,957,965,587 D 120 .2 3,750 1.1 1,747,983,794 D 180 .3 5,625 1.0 1,682,170,048 D 240 .4 7,500 1.0 1,661,100,317 D 300 .5 9,375 1.0 1,650,639,971 D 360 .6 11,250 1.0 1,643,719,706 D 420 .7 13,125 1.0 1,638,543,029 D 480 .8 15,000 1.0 1,634,396,439 D 540 .9 16,875 1.0 1,630,928,875 D 600 1.0 18,750 1.0 1,627,940,679 D 608 1.0 19,000 1.0 1,627,587,673 D 660 1.1 20,625 1.0 1,625,292,977 D 720 1.2 22,500 1.0 1,622,917,060 D 780 1.3 24,375 1.0 1,620,744,188 D 840 1.4 26,250 1.0 1,618,690,708 D 900 1.5 28,125 1.0 1,616,610,192 D 960 1.6 30,000 1.0 1,614,674,743 D 1,020 1.7 31,875 1.0 1,612,723,896 D 1,080 1.8 33,750 1.0 1,610,040,535 D 1,140 1.9 35,625 1.0 1,606,338,141 D 1,200 2.0 37,500 1.0 1,602,635,877 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 61.9 129,592 79,933 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - ---------- ---------- ---------- ---------- ------ ------ ------ ---------- B 10,240 7,782 7,578.6 2,909.0 38.4 100.0 .0 1,048,570 E 10,240 7,322 7,341.3 3,025.4 41.2 90.7 9.3 1,048,570 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 110,234 110,234 0 0 64K 128K 1,569 1,569 0 0 128K 256K 1,010 1,010 0 0 256K 512K 1,894 1,894 0 0 512K 1024K 1,348 1,348 0 0 1M 2M 421 419 2 0 2M 4M 166 162 4 0 4M 8M 140 140 0 0 8M 16M 78 77 1 0 16M 32M 117 113 4 0 32M 64M 101 94 7 0 64M 128M 54 47 5 2 128M 256M 30 18 12 0 256M 512M 64 54 10 0 512M 1024M 16 8 8 0 1G 2G 35 1 34 0 2G 4G 7 2 5 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 1,280 0.1 7,136,370.3 5,552,184.0 56.0 2,292 2,560 0.3 7,136,370.3 2,996,668.6 70.0 367 5,120 0.5 7,136,370.3 2,250,183.3 76.0 0 7,680 0.8 7,136,370.3 2,100,126.5 77.0 0 10,240 1.0 7,136,370.3 1,907,036.2 79.0 0 12,288 1.2 7,136,370.3 1,552,253.3 82.0 0 14,336 1.4 7,136,370.3 1,534,890.4 82.0 0 16,384 1.6 7,136,370.3 1,508,943.1 83.0 0 18,432 1.8 7,136,370.3 1,501,561.6 83.0 0 20,480 2.0 7,136,370.3 1,496,523.7 83.0 0 30,720 3.0 7,136,370.3 1,491,533.9 83.0 0 40,960 4.0 7,136,370.3 1,490,512.0 83.0 0 61,440 6.0 7,136,370.3 1,490,512.0 83.0 0 81,920 8.0 7,136,370.3 1,490,512.0 83.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid. Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 308 .8 41 3,041 ####### 1.0 ####### 3.0 84,511,897 348 .9 80 5,513 ####### 1.0 ####### 2.0 84,776,508 388 1.0 119 7,351 ####### 1.0 ####### 1.0 84,965,913 428 1.1 158 8,758 ####### 1.0 35,736 .3 85,077,213 468 1.2 197 10,715 ####### 1.0 1 .0 85,148,259 508 1.3 236 13,391 ####### 1.0 1 .0 85,201,352 548 1.4 275 16,436 ####### 1.0 1 .0 85,243,374 588 1.5 314 18,551 ####### 1.1 1 .0 85,277,045 628 1.6 353 19,830 ####### 1.1 1 .0 85,304,508 668 1.7 392 21,977 ####### 1.1 1 .0 85,326,398 708 1.8 431 24,117 ####### 1.1 1 .0 85,343,185 748 1.9 470 25,935 ####### 1.1 1 .0 85,356,642 788 2.0 509 28,437 ####### 1.1 1 .0 85,368,040 ------------------------------------------------------------- SGA Target Advisory DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 512 0.5 14,336,684 1,678,710,738 768 0.8 13,240,674 1,640,299,284 1,024 1.0 12,833,841 1,627,603,973 1,280 1.3 12,601,551 1,615,396,943 1,536 1.5 12,375,676 1,606,770,642 1,792 1.8 12,212,720 1,599,446,424 2,048 2.0 12,212,736 1,599,446,424 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 4 1.0 0 0 0 0 8 2.0 0 0 0 0 12 3.0 0 0 0 0 16 4.0 0 0 0 0 20 5.0 0 0 0 0 24 6.0 0 0 0 0 28 7.0 0 0 0 0 32 8.0 0 0 0 0 36 9.0 0 0 0 0 40 10.0 0 0 0 0 44 11.0 0 0 0 0 48 12.0 0 0 0 0 52 13.0 0 0 0 0 56 14.0 0 0 0 0 60 15.0 0 0 0 0 64 16.0 0 0 0 0 68 17.0 0 0 0 0 72 18.0 0 0 0 0 76 19.0 0 0 0 0 80 20.0 0 0 0 0 ------------------------------------------------------------- Java Pool Advisory DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 Est LC Est LC Est LC Est LC Java JP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 8 1.0 4 123 29 1.0 ####### 1.0 411 12 1.5 6 165 29 1.0 ####### 1.0 411 16 2.0 8 189 29 1.0 ####### 1.0 411 ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 414,071 6,645 16 segment header 3,091 31 10 file header block 319 1 2 undo block 555 0 1 1st level bmb 32 0 7 2nd level bmb 19 0 9 undo header 19 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- RO-Multiple Object Reuse (fast object reuse) 172,737 172,737 0 19,152 274 14.33 MS-Materialized View Refresh Log 34 31 3 6 18 3,056.67 US-Undo Segment 230,453 230,453 0 146 2 10.39 TX-Transaction (index contention) 44 44 0 13 1 92.85 TX-Transaction (row lock contention) 31 31 0 31 0 12.87 TM-DML 221,763 189,217 32,546 7 0 51.43 CI-Cross-Instance Call Invocation 15,980 15,980 0 9 0 8.78 TX-Transaction (allocate ITL entry) 11 11 0 11 0 4.27 SQ-Sequence Cache 143 143 0 6 0 3.50 CU-Cursor 44,685 44,685 0 1 0 18.00 JS-Job Scheduler (queue lock) 26,069 26,069 0 2 0 6.50 KO-Multiple Object Checkpoint (fast object checkpoint) 18 18 0 2 0 4.00 PS-PX Process Reservation 242 216 26 2 0 2.00 CF-Controlfile Transaction 4,501 4,501 0 1 0 1.00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out of Space count -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- -------- --------- ----- -------------- 1 525.7 261,294 83,350 55 149.38333 2/0 0/0/0/1381/518 ------------------------------------------------------------- Undo Segment Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Most recent 35 Undostat rows, ordered by Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ------------ 07-Nov 08:03 3,797 2,801 83,350 23 171 0/0 0/0/0/7/4032 07-Nov 07:53 13,271 10,616 82,747 23 163 0/0 0/0/0/21/126 07-Nov 07:43 6,895 13,279 82,144 20 157 0/0 0/0/0/10/841 07-Nov 07:33 112,273 26,732 81,540 25 149 0/0 0/0/0/433/11 07-Nov 07:23 88,056 19,022 80,937 20 238 0/0 0/0/0/208/87 07-Nov 07:13 12,578 13,574 80,334 21 449 0/0 0/0/0/42/112 07-Nov 07:03 30,832 18,893 79,706 24 448 0/0 0/0/0/76/303 07-Nov 06:53 161,602 61,567 79,131 22 445 2/0 0/0/0/406/16 07-Nov 06:43 52,889 40,155 78,504 23 569 0/0 0/0/0/142/52 07-Nov 06:33 39,608 26,382 77,902 23 1,038 0/0 0/0/0/36/340 07-Nov 06:23 2,559 5,468 77,299 55 1,202 0/0 0/0/0/0/0/0 07-Nov 06:13 825 21,194 76,697 26 1,196 0/0 0/0/0/0/0/0 07-Nov 06:03 472 1,611 76,095 21 1,188 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests -> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests -> "Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ASM allocation 192 0.0 N/A 0 0 N/A ASM db client latch 51,969 0.0 N/A 0 0 N/A ASM map headers 374 0.0 N/A 0 0 N/A ASM map load waiting lis 204 0.0 N/A 0 0 N/A ASM map operation freeli 105,304 0.0 0.0 0 0 N/A ASM map operation hash t 19,011,374 0.0 0.1 0 0 N/A ASM network background l 3,465 0.0 N/A 0 0 N/A AWR Alerted Metric Eleme 45,732 0.0 N/A 0 0 N/A Consistent RBA 190,191 0.1 0.1 0 0 N/A FIB s.o chain latch 32 0.0 N/A 0 0 N/A FOB s.o list latch 30,038 0.0 0.0 0 0 N/A In memory undo latch 27,942 0.1 0.9 1 5,727 0.0 JOX SGA heap latch 3,411 0.0 N/A 0 0 N/A JS Sh mem access 8 0.0 N/A 0 0 N/A JS broadcast kill buf la 42 0.0 N/A 0 0 N/A JS mem alloc latch 27 0.0 N/A 0 0 N/A JS queue access latch 27 0.0 N/A 0 0 N/A JS queue state obj latch 52,138 0.0 N/A 0 0 N/A JS slv state obj latch 107 0.0 N/A 0 0 N/A KFK SGA context latch 78,864 0.0 N/A 0 0 N/A KFMD SGA 10,548 0.2 0.3 0 0 N/A KMG MMAN ready and start 3,375 0.0 N/A 0 0 N/A KMG resize request state 6 0.0 N/A 0 0 N/A KTF sga latch 105 0.0 N/A 0 2,278 0.0 KWQMN job cache list lat 105 0.0 N/A 0 0 N/A KWQP Prop Status 4 0.0 N/A 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 143 0.0 Memory Management Latch 1,082 0.3 1.0 0 3,373 0.0 OS process 3,858 0.0 N/A 0 0 N/A OS process allocation 4,977 0.0 N/A 0 0 N/A OS process: request allo 2,383 0.0 N/A 0 0 N/A PL/SQL warning settings 88,413 0.0 N/A 0 0 N/A SGA IO buffer pool latch 96,807 0.0 0.2 0 114,187 0.1 SQL memory manager latch 4 0.0 N/A 0 2,379 0.0 SQL memory manager worka 234,215 0.0 0.5 0 0 N/A Shared B-Tree 310 0.0 N/A 0 0 N/A Undo Hint Latch 0 N/A N/A 0 1,209 0.0 active checkpoint queue 498,551 3.0 0.0 0 0 N/A active service list 28,673 0.0 1.0 0 2,453 0.0 archive control 2,400 0.0 N/A 0 0 N/A begin backup scn array 268,098 0.0 0.1 0 0 N/A buffer pool 15 0.0 N/A 0 0 N/A cache buffer handles 206,052 0.0 0.0 0 0 N/A cache buffers chains 908,144,041 0.0 0.1 304 47,870,151 0.4 cache buffers lru chain 3,460,948 0.5 0.1 6 47,178,370 0.9 cache table scan latch 0 N/A N/A 0 873,657 1.0 channel handle pool latc 3,428 0.1 0.0 0 0 N/A channel operations paren 627,430 0.0 0.1 0 0 N/A checkpoint queue latch 6,617,584 0.0 0.0 0 1,231,645 0.0 client/application info 10,161 0.0 N/A 0 0 N/A commit callback allocati 858 0.0 N/A 0 0 N/A compile environment latc 14,634 0.0 N/A 0 0 N/A constraint object alloca 44 0.0 N/A 0 0 N/A dictionary lookup 23,171 0.0 0.3 0 0 N/A dml lock allocation 444,128 0.0 0.0 0 0 N/A dummy allocation 2,511 0.2 0.0 0 0 N/A enqueue hash chains 3,854,829 0.0 0.1 2 13,250 0.0 enqueues 2,578,817 0.1 0.0 0 0 N/A error message lists 70 1.4 0.0 0 0 N/A event group latch 1,197 0.0 N/A 0 0 N/A Latch Activity DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests -> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests -> "Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ file cache latch 79,279 0.0 0.0 0 0 N/A global KZLD latch for me 644 0.0 N/A 0 0 N/A hash table column usage 3,403 0.1 0.3 0 2,022,590 0.0 hash table modification 18,047 0.0 N/A 0 0 N/A internal temp table obje 79 0.0 N/A 0 0 N/A job workq parent latch 0 N/A N/A 0 32 0.0 job_queue_processes para 132 0.0 N/A 0 0 N/A kks stats 150,867 0.0 0.5 0 0 N/A ksuosstats global area 483 0.0 N/A 0 0 N/A ksv instance 4 0.0 N/A 0 0 N/A ktm global data 37,548 0.0 N/A 0 0 N/A kwqbsn:qsga 308 0.0 N/A 0 0 N/A lgwr LWN SCN 190,211 0.0 0.0 0 0 N/A library cache 4,025,302 0.1 0.3 22 160,630 4.0 library cache load lock 122,020 0.0 0.2 0 327 0.0 library cache lock 1,700,930 0.0 0.0 0 1 0.0 library cache lock alloc 24,174 0.0 0.2 0 0 N/A library cache pin 1,378,284 0.0 0.1 0 1 0.0 library cache pin alloca 13,242 0.0 0.0 0 0 N/A list of block allocation 5,044 0.0 N/A 0 0 N/A loader state object free 40,827 0.0 N/A 0 0 N/A logminer context allocat 2 0.0 N/A 0 0 N/A longop free list parent 17,287 0.0 N/A 0 1,542 0.0 message pool operations 135,401 0.0 0.3 0 0 N/A messages 1,984,141 1.6 0.0 0 0 N/A mostly latch-free SCN 195,208 2.3 0.0 0 0 N/A msg queue 52 0.0 N/A 0 52 0.0 multiblock read objects 1,893,703 0.1 0.2 13 0 N/A ncodef allocation latch 117 0.0 N/A 0 0 N/A object queue header heap 1,508,364 0.0 0.3 0 595,056 0.0 object queue header oper 56,144,257 0.0 0.1 3 239 0.0 object stats modificatio 38,466 0.6 0.0 0 1 0.0 parallel query alloc buf 2,434 2.0 0.1 0 0 N/A parallel query stats 65 3.1 0.0 0 0 N/A parameter list 801 0.0 N/A 0 0 N/A parameter table allocati 1,430 0.8 0.0 0 0 N/A post/wait queue 8,061 1.1 0.0 0 3,350 0.0 process allocation 2,388 0.7 4.5 2 1,193 0.3 process group creation 2,383 0.2 0.8 0 0 N/A process queue 994 0.5 0.0 0 0 N/A process queue reference 58,753,200 0.0 0.2 6 2,195,949 ###### qmn task queue latch 1,024 0.0 N/A 0 0 N/A query server freelists 935 7.9 0.0 0 0 N/A query server process 28 0.0 N/A 0 28 0.0 redo allocation 644,100 0.8 0.1 1 42,917,173 0.0 redo copy 0 N/A N/A 0 42,922,033 0.1 redo writing 1,071,346 1.0 0.0 0 0 N/A resmgr group change latc 2,044 0.0 N/A 0 0 N/A resmgr:actses active lis 2,499 0.0 N/A 0 0 N/A resmgr:actses change gro 1,210 0.0 N/A 0 0 N/A resmgr:actses change sta 1 0.0 N/A 0 0 N/A resmgr:free threads list 2,495 0.1 0.0 0 0 N/A resmgr:resource group CP 1 0.0 N/A 0 0 N/A resmgr:schema config 2 0.0 N/A 0 0 N/A row cache objects 14,341,017 0.3 0.0 18 125,121 0.7 rules engine aggregate s 7 0.0 N/A 0 0 N/A rules engine rule set st 214 0.0 N/A 0 0 N/A segmented array pool 32 0.0 N/A 0 0 N/A sequence cache 5,499 0.0 1.0 0 0 N/A session allocation 31,945,558 0.9 0.0 5 0 N/A Latch Activity DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests -> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests -> "Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ session idle bit 21,122,430 0.0 0.1 0 0 N/A session state list latch 4,627 0.6 0.5 0 0 N/A session switching 117 0.0 N/A 0 0 N/A session timer 2,453 0.0 N/A 0 0 N/A shared pool 4,704,906 0.4 0.2 39 0 N/A shared pool simulator 4,112,759 0.0 0.0 0 0 N/A simulator hash latch 18,513,051 0.0 0.2 11 0 N/A simulator lru latch 9,415,827 0.2 0.1 42 6,290,888 0.0 slave class 166 0.0 N/A 0 0 N/A slave class create 147 2.0 1.0 1 0 N/A sort extent pool 159,488 0.0 0.8 0 0 N/A state object free list 4 0.0 N/A 0 0 N/A statistics aggregation 280 0.0 N/A 0 0 N/A temp lob duration state 7 0.0 N/A 0 0 N/A temporary table state ob 8 0.0 N/A 0 0 N/A threshold alerts latch 3,910 0.0 N/A 0 0 N/A transaction allocation 133,595 0.0 N/A 0 0 N/A transaction branch alloc 117 0.0 N/A 0 0 N/A undo global data 4,778,686 0.0 0.1 0 0 N/A user lock 3,730 0.3 0.8 1 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- session allocation 31,945,558 275,330 506 274,838 0 0 0 cache buffers chains 908,144,041 168,390 12,505 156,659 0 0 0 row cache objects 14,341,017 40,362 690 39,698 0 0 0 messages 1,984,141 31,887 453 31,434 0 0 0 shared pool 4,704,906 20,801 3,395 17,805 0 0 0 simulator lru latch 9,415,827 19,040 2,246 16,924 0 0 0 cache buffers lru chain 3,460,948 17,947 937 17,016 0 0 0 active checkpoint queue latch 498,551 15,086 258 14,828 0 0 0 redo writing 1,071,346 10,194 248 9,946 0 0 0 object queue header operation 56,144,257 10,144 963 9,209 0 0 0 process queue reference 58,753,200 5,544 868 4,703 0 0 0 redo allocation 644,100 5,385 565 4,822 0 0 0 mostly latch-free SCN 195,208 4,508 150 4,370 0 0 0 library cache 4,025,302 3,750 1,272 2,611 0 0 0 enqueues 2,578,817 2,051 38 2,015 0 0 0 multiblock read objects 1,893,703 1,014 237 796 0 0 0 checkpoint queue latch 6,617,584 788 25 763 0 0 0 simulator hash latch 18,513,051 683 144 545 0 0 0 enqueue hash chains 3,854,829 511 47 465 0 0 0 library cache lock 1,700,930 308 15 293 0 0 0 object stats modification 38,466 212 2 210 0 0 0 dml lock allocation 444,128 155 6 149 0 0 0 Consistent RBA 190,191 130 8 122 0 0 0 undo global data 4,778,686 118 6 112 0 0 0 object queue header heap 1,508,364 75 24 51 0 0 0 query server freelists 935 74 3 71 0 0 0 ASM map operation hash table 19,011,374 67 10 58 0 0 0 kks stats 150,867 65 30 39 0 0 0 channel operations parent latch 627,430 61 8 53 0 0 0 library cache load lock 122,020 53 9 45 0 0 0 Latch Sleep Breakdown DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- parallel query alloc buffer 2,434 49 5 44 0 0 0 library cache pin 1,378,284 42 4 38 0 0 0 In memory undo latch 27,942 30 28 2 0 0 0 session state list latch 4,627 27 13 15 0 0 0 KFMD SGA 10,548 22 6 18 0 0 0 SGA IO buffer pool latch 96,807 20 4 16 0 0 0 process allocation 2,388 17 77 4 0 0 0 user lock 3,730 13 10 3 0 0 0 sort extent pool 159,488 12 10 2 0 0 0 dictionary lookup 23,171 11 3 8 0 0 0 begin backup scn array 268,098 9 1 8 0 0 0 session idle bit 21,122,430 9 1 8 0 0 0 library cache lock allocation 24,174 5 1 4 0 0 0 process group creation 2,383 5 4 1 0 0 0 SQL memory manager workarea list latch 234,215 4 2 2 0 0 0 hash table column usage latch 3,403 4 1 3 0 0 0 message pool operations parent latch 135,401 4 1 3 0 0 0 Memory Management Latch 1,082 3 3 0 0 0 0 slave class create 147 3 3 0 0 0 0 active service list 28,673 1 1 0 0 0 0 sequence cache 5,499 1 1 0 0 0 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- ASM map operation hash t kffmDoDone_1 0 5 2 ASM map operation hash t kffmTranslate 0 5 7 In memory undo latch ktiFlush: child 0 24 22 In memory undo latch ktiTxnPoolFree 0 2 0 In memory undo latch ktichg: child 0 1 3 In memory undo latch kticmt: child 0 1 3 KFMD SGA kfmdSlvJoin_LU 0 5 6 KFMD SGA kfmdSlvJoinFetch 0 1 0 Memory Management Latch kmgs_perform_parameter_upd 0 3 3 SGA IO buffer pool latch ksfd_alloc_sgabuffer 0 3 0 SGA IO buffer pool latch ksfd_free_sgabuffer 0 1 4 SQL memory manager worka qesmmIRegisterWorkArea 0 1 0 SQL memory manager worka qesmmIUnRegisterWorkArea 0 1 1 active checkpoint queue kcbbacq: scan active check 0 251 257 active checkpoint queue kcbo_add_ckpt: Add last ob 0 3 0 active checkpoint queue kcbkcpa: Checkpoint post-p 0 2 1 active checkpoint queue kcbo_add_ckpt: Add obj ckp 0 1 0 active checkpoint queue kcbo_add_ckpt: Add object 0 1 0 active service list kswssyis: find service ent 0 1 0 begin backup scn array get begin backup scn for f 0 1 1 cache buffers chains kcbzwb 0 9,517 361 cache buffers chains kcbgtcr: kslbegin excl 0 5,740 5,499 cache buffers chains kcbzib: multi-block read: 0 4,320 0 cache buffers chains kcbgtcr: fast path 0 1,961 2,704 cache buffers chains kcbrls: kslbegin 0 1,624 11,973 cache buffers chains kcbzgb: scan from tail. no 0 950 0 cache buffers chains kcbchg: kslbegin: bufs not 0 846 1,427 cache buffers chains kcbgtcr: kslbegin shared 0 607 50 cache buffers chains kcbbic2 0 539 83 cache buffers chains kcbgcur: kslbegin 0 484 857 cache buffers chains kcbbxsv 0 344 293 cache buffers chains kcbgtcr 0 280 1 cache buffers chains kcbnew: new latch again 0 243 41 cache buffers chains kcbchg: kslbegin: call CR 0 229 1,212 cache buffers chains kcbzib: finish free bufs 0 176 1,449 cache buffers chains kcbget: pin buffer 0 164 1,146 cache buffers chains kcbbic1 0 84 80 cache buffers chains kcbrlb1 0 36 399 cache buffers chains kcbget: exchange rls 0 25 90 cache buffers chains kcbget: exchange 0 17 425 cache buffers chains kcbkzs 0 6 34 cache buffers chains kcbnlc 0 6 15 cache buffers chains kcbo_write_process 0 2 7 cache buffers chains kcbzhngcbk1: get hash chai 0 2 0 cache buffers chains kcbesc: escalate 0 1 3 cache buffers chains kcbso1: set no access 0 1 0 cache buffers chains kcbw_next_free 0 1 0 cache buffers lru chain kcbzgws 0 791 0 cache buffers lru chain kcbzar: KSLNBEGIN 0 117 0 cache buffers lru chain kcbo_link_q 0 15 7 cache buffers lru chain kcbbic2 0 3 308 cache buffers lru chain kcbbxsv: move to being wri 0 3 261 cache buffers lru chain kcbrlb 0 3 138 cache buffers lru chain kcbzgb: posted for free bu 0 2 13 cache buffers lru chain kcbbwlru 0 1 199 cache buffers lru chain kcbbxsv:no write case 0 1 0 cache buffers lru chain kcbzfw 0 1 3 channel handle pool latc ksrchconnect() 0 2 2 channel operations paren ksrwait() 0 7 3 channel operations paren ksrapublish() 0 2 3 Latch Miss Sources DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- channel operations paren ksrmfree() 0 2 0 channel operations paren ksrchdelete() 0 1 1 channel operations paren ksrpubwait():1 0 1 6 checkpoint queue latch kcbklbc: Link buffer into 0 25 0 cost function kzulgt: find user res 0 8 8 cost function kzulrl 0 2 2 dictionary lookup kkdlgon: replenish 0 2 0 dictionary lookup kkdlgon: check 0 1 3 dml lock allocation ktaiam 0 3 4 dml lock allocation ktaidm 0 3 2 enqueue hash chains ksqgtl3 0 22 25 enqueue hash chains ksqrcl 0 20 20 enqueue hash chains ksqcnl 0 5 2 enqueues ksqdel 0 20 16 enqueues ksqgel: create enqueue 0 18 22 hash table column usage ksxmfcu 0 1 2 kks stats kks stats alloc/free 0 30 30 lgwr LWN SCN kcs023 0 117 0 library cache kglobpn: child: 0 126 142 library cache kglpndl: child: after proc 0 110 9 library cache kglLockCursor 0 97 35 library cache kglhdgn: child: 0 57 196 library cache kglpin 0 57 26 library cache kgldti: 2child 0 48 24 library cache kglukp: child 0 25 32 library cache kglpndl: child: before pro 0 18 59 library cache kglobld 0 15 44 library cache kglhdgc: child: 0 14 5 library cache kgldte: child 0 0 13 18 library cache kglati 0 6 2 library cache kgldtld: 2child 0 5 5 library cache kglivl: child 0 5 0 library cache kglScanDependency 0 4 1 library cache kglpnp: child 0 3 6 library cache kglini: child 0 2 2 library cache kglupd: child 0 2 1 library cache kglhdbrnl: child 0 1 4 library cache kglhdiv0: parent: invalida 0 1 1 library cache kglhdiv: child 0 1 2 library cache kglic 0 1 0 library cache kglrtl 0 1 0 library cache load lock kglllal: llatch 0 5 5 library cache load lock kglllal: llatch: in loop 0 2 2 library cache load lock kgllldl: llatch 0 2 2 library cache lock kgllkdl: child: no lock ha 0 50 31 library cache lock kgllkdl: child: cleanup 0 8 11 library cache lock kgllkal: child: multiinsta 0 7 4 library cache lock alloc kgllkget 0 1 1 library cache pin kglpndl 0 4 1 message pool operations ksrmfree() 0 1 0 messages ksarcv: after wait 0 195 179 messages ksarcv 0 163 160 messages ksaamb: after wakeup 0 95 113 mostly latch-free SCN kcslcu3 0 33 150 mostly latch-free SCN kcsnew_scn_rba 0 8 0 multiblock read objects kcbzib: normal mbr free 0 135 147 multiblock read objects kcbzib: mbr get 0 102 90 object queue header heap kcbw_link_q:alloc 0 15 8 object queue header heap kcbw_unlink_q 0 6 0 object queue header heap kcbo_link_q:reget 0 3 16 Latch Miss Sources DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- object queue header oper kcbw_unlink_q 0 629 167 object queue header oper kcbw_link_q 0 186 36 object queue header oper kcbo_switch_cq 0 80 373 object queue header oper kcbo_switch_mq_bg 0 77 113 object queue header oper kcbo_swap_buf_bg 0 32 364 object queue header oper kcbo_link_q:reget 0 31 2 object queue header oper kcbo_write_q 0 19 9 object queue header oper kcbo_switch_q_bg 0 13 10 object queue header oper kcbw_unlink_q_bg 0 7 0 object queue header oper kcbo_service_ockpt 0 3 3 object stats modificatio ksoslinelm 0 2 0 parallel query alloc buf kxfpbfre 0 3 3 parallel query alloc buf kxfpbalo 0 2 2 process allocation ksuapc 0 64 76 process allocation ksucrp 0 9 0 process allocation ksudlp 0 4 1 process group creation ksupgpadd 0 4 4 process queue reference kxfpqrsnd 0 867 0 process queue reference kxfpqidqr2 0 1 791 query server freelists kxfpobadf 0 3 3 redo allocation kcrfw_redo_gen: redo alloc 0 557 0 redo allocation kcrfw_redo_gen: redo alloc 0 3 11 redo allocation kcrfw_redo_write: before w 0 2 224 redo allocation kcrfw_redo_gen: redo alloc 0 1 0 redo allocation kcrfw_redo_write: more spa 0 1 330 redo allocation kcrfwnf 0 1 0 redo writing kcrfwcr 0 215 210 redo writing kcrfw_post: rba scn pair 0 22 3 redo writing kcrfw_cal_target_rba 0 9 23 redo writing kcrfw_post: after write 0 2 11 row cache objects kqreqd: reget 0 242 16 row cache objects kqrpre: find obj 0 232 309 row cache objects kqreqd 0 46 51 row cache objects kqrso 0 24 7 row cache objects kqrpre: init complete 0 3 144 row cache objects kqrcmt: clear flag 0 1 19 row cache objects kqrcmt: while loop 0 1 7 row cache objects kqrpup 0 1 15 row cache objects kqrsrd 0 1 16 sequence cache kdnssd 0 1 0 session allocation ksuprc 0 261 203 session allocation ksudlc 0 146 228 session allocation ksuxds: KSUSFCLC not set 0 62 7 session allocation ksucri 0 10 40 session allocation ksufap: active sessions 0 10 1 session allocation ksuxds: not user session 0 8 10 session allocation kxfprdp 0 4 1 session allocation ksursi 0 2 4 session allocation kspallmod 0 1 0 session allocation ksucrp 0 1 2 session allocation ksudlp 0 1 6 session allocation kxfpqidqr 0 1 5 session idle bit ksupuc: set busy 0 1 0 session state list latch kpseqa 0 5 12 session state list latch kpseqd 0 5 1 session state list latch kpscad 0 3 0 shared pool kghalo 0 2,057 1,371 shared pool kghfrunp: clatch: wait 0 665 729 shared pool kghfrunp: alloc: wait 0 606 309 shared pool kghupr1 0 294 1,065 Latch Miss Sources DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- shared pool kghalp 0 208 408 shared pool kghfre 0 110 168 shared pool kghfrunp: clatch: nowait 0 83 0 shared pool kgh_next_free 0 76 0 shared pool kghasp 0 23 66 shared pool kghfrunp: alloc: session d 0 15 3 shared pool kghfrh 0 4 0 shared pool kghfen: not perm alloc cla 0 2 4 simulator hash latch kcbsacc: insert dba 0 67 0 simulator hash latch kcbs_lookup_setid: lookup 0 40 99 simulator hash latch kcbsacc: lookup dba 0 29 22 simulator hash latch kcbsacc: remove dba 0 7 18 simulator hash latch kcbs_get_or_extract_simbuf 0 1 5 simulator lru latch kcbs_simulate: simulate se 0 2,086 2,124 simulator lru latch kcbs_simbufseq 0 125 122 simulator lru latch kcbs_lookup_setid 0 34 0 simulator lru latch kcbssck 0 1 0 slave class create ksvcreate 0 2 0 slave class create ksvsubmit 0 1 0 sort extent pool ktstdt: return extent 0 4 2 sort extent pool ktstaddextent1 0 3 1 sort extent pool ktstdt: dealloc sort exten 0 3 2 undo global data ktudba: KSLBEGIN 0 4 3 undo global data ktudnx:child 0 1 1 undo global data kturimugur: child 0 1 2 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total Logical Reads: 424,723,664 -> Captured Segments account for 91.5% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWH_RASSEG TS_RASSEGU COG_LKJ_FECHA_I01 INDEX 153,897,584 36.23 CRMOWN03 TS_CRM_DAT PK_ACCPERS_GESTOR1 P4914 INDEX 87,855,104 20.69 DWH_RASSEG TS_RASSEGU COG_LKJ_FECHA TABLE 76,883,008 18.10 CRMOWN03 TS_CRM_IDX PK_AG_ACCPERS_GESTOR INDEX 11,697,632 2.75 OPS$EXPLO TREBALL MOV_DIARIO_CONCEP_AU TABLE 9,143,264 2.15 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Total Physical Reads: 28,176,227 -> Captured Segments account for 38.6% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM TYPE$ TABLE 1,297,488 4.60 CRMOWN03 TS_CRM_DAT BS_CRM_ACCPERS_GESTO P4914 TABLE 762,641 2.71 DWH_COMERC TS_DMCOMER LKJ_CLIENTES P_20080930 TABLE 672,179 2.39 DWH_COMERC TS_DMCOMER LKJ_SGC_REL_SET_PERS TABLE 586,283 2.08 DWH_SCGI TS_SCGI_ID UI_LKJ_LLI_EXTERNA_P INDEX 433,064 1.54 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot Row Tablespace Subobject Obj. Lock % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM I_OBJ# INDEX 27 38.03 DWH_SCGI_D TS_SCGIDIA I1_MTR_CTRNE_PTE_SAL P_20081031 INDEX 10 14.08 SYS SYSTEM DBMS_LOCK_ALLOCATED TABLE 6 8.45 SYS SYSTEM I_COL1 INDEX 5 7.04 SYS SYSTEM I_COL3 INDEX 5 7.04 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> % of Capture shows % of ITL waits for each top segment compared -> with total ITL waits for all segments captured by the Snapshot Tablespace Subobject Obj. ITL % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM CCOL$ TABLE 4 44.44 SYS SYSTEM TAB$ TABLE 4 44.44 SYS SYSTEM SEG$ TABLE 1 11.11 ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> % of Capture shows % of Buffer Busy Waits for each top segment compared -> with total Buffer Busy Waits for all segments captured by the Snapshot Buffer Tablespace Subobject Obj. Busy % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM IND$ TABLE 113 12.94 SYS SYSTEM TAB$ TABLE 79 9.05 SYS SYSTEM I_OBJ# INDEX 65 7.45 SYS SYSTEM HIST_HEAD$ TABLE 62 7.10 SYS SYSTEM I_COL2 INDEX 62 7.10 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> "Pct Misses" should be very low (< 2% in most cases) -> "Final Usage" is the number of cache entries being used Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 135 63.7 0 N/A 4 1 dc_constraints 926 67.4 0 N/A 926 48 dc_files 664 59.9 0 N/A 0 0 dc_global_oids 83,303 1.2 0 N/A 0 0 dc_histogram_data 522,536 14.9 0 N/A 92 918 dc_histogram_defs 376,536 37.0 0 N/A 21,679 582 dc_object_grants 45,495 29.8 0 N/A 0 76 dc_object_ids 564,994 6.6 0 N/A 278 179 dc_objects 155,260 27.9 0 N/A 21,899 179 dc_profiles 1,170 5.5 0 N/A 0 1 dc_rollback_segments 960,432 0.0 0 N/A 0 255 dc_segments 657,185 72.7 0 N/A 75,898 182 dc_sequences 152 84.9 0 N/A 152 1 dc_table_scns 721 30.2 0 N/A 4 0 dc_tablespace_quotas 56,711 0.5 0 N/A 5,459 0 dc_tablespaces 807,718 0.3 0 N/A 0 10 dc_usernames 20,499 5.4 0 N/A 0 6 dc_users 841,606 0.8 0 N/A 0 22 global database name 2,148 0.0 0 N/A 0 1 kqlsubheap_object 276 13.0 0 N/A 0 0 outstanding_alerts 1,885 98.4 0 N/A 6 0 ------------------------------------------------------------- Library Cache Activity DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- SQL AREA 24,812 79.7 2,856,929 3.5 21,479 10,882 TABLE/PROCEDURE 74,936 11.6 547,382 10.5 24,494 0 BODY 1,510 11.0 171,725 0.4 501 0 TRIGGER 351 39.0 3,534 34.4 990 0 INDEX 655 57.3 2,093 31.4 168 0 CLUSTER 1,716 3.8 12,004 1.5 120 0 PIPE 313 17.6 322 17.1 0 0 JAVA DATA 3 0.0 0 N/A 0 0 ------------------------------------------------------------- Process Memory Summary DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> B: Begin snap E: End snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time -> Hist Max Alloc is the Historical Max Allocation for still-connected processes -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc - -------- --------- --------- -------- -------- ------- ------- ------ ------ B SQL 3,196.0 3,191.8 47.7 160.2 1,002 1,534 67 60 Other 2,196.3 N/A 27.5 63.5 295 295 80 80 Freeable 2,185.2 .0 44.6 208.2 1,047 N/A 49 49 PL/SQL 1.3 .9 .0 .0 0 0 79 79 JAVA .0 .0 .0 .0 0 2 1 0 E SQL 3,221.9 3,216.8 41.3 138.5 1,002 1,255 78 71 Other 2,975.5 N/A 32.7 80.1 452 452 91 91 Freeable 1,217.9 .0 23.4 116.2 810 N/A 52 52 PL/SQL 1.5 1.0 .0 .0 0 0 90 90 JAVA .8 .8 .4 .6 1 2 2 1 ------------------------------------------------------------- SGA Memory Summary DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 629,145,600 641,728,512 Fixed Size 2,247,464 Redo Buffers 4,259,840 Variable Size 438,088,920 425,506,008 ------------------- sum 1,073,741,824 ------------------------------------------------------------- SGA breakdown difference DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> ordered by Pool, Name -> N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- ------- java free memory 1.1 1.1 0.00 java joxlod exec hp 6.5 6.5 0.00 java joxs heap .3 .3 0.00 large ASM map operations hashta .4 .4 0.00 large PX msg pool 2.0 2.0 0.00 large free memory 1.7 1.7 0.00 shared ASH buffers 20.8 20.8 0.00 shared ASM extent pointer array 75.5 75.5 0.00 shared CCursor 4.3 N/A -100.00 shared KGH: NO ACCESS 31.6 34.2 8.32 shared KQR M PO 4.3 N/A -100.00 shared Sort Segment 9.4 9.4 0.00 shared XDB Schema Cac 7.4 8.9 20.04 shared event statistics per sess 4.9 4.9 0.00 shared free memory 72.6 92.5 27.39 shared init_heap_kfsg 8.9 8.9 0.00 shared kglsim heap 7.9 7.9 0.00 shared kglsim object batch 14.0 14.0 0.00 shared library cache 14.2 14.1 -0.35 shared obj stat memo 38.5 38.5 0.09 shared object level 6.2 6.2 0.06 shared row cache 7.2 7.2 0.00 shared sql area 18.3 10.7 -41.65 shared trace buffer 5.2 5.2 0.00 stream free memory 4.0 4.0 0.00 buffer_cache 600.0 612.0 2.00 fixed_sga 2.1 2.1 0.00 log_buffer 4.1 4.1 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Streams processes ordered by CPU usage -> CPU and I/O Time in micro seconds Session Type CPU Time User I/O Time Sys I/O Time ------------------------- -------------- -------------- -------------- QMON Slaves 953,990 2,435,352 0 QMON Coordinator 135,906 0 0 ------------------------------------------------------------- Streams Capture DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 No data exists for this section of the report. ------------------------------------------------------------- Buffered Subscribers DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 -> Rule Sets ordered by Evaluations Fast SQL CPU Elapsed Ruleset Name Evals Evals Execs Time Time ------------------------- -------- -------- -------- -------- -------- SYS.ALERT_QUE_R 7 0 0 57 280 ------------------------------------------------------------- Resource Limit Stats DB/Inst: SEPBZ212/SEPBZ212 Snap: 7382 -> only rows with Current or Maximum Utilization > 80% of Limit are shown -> ordered by resource name Current Maximum Initial Resource Name Utilization Utilization Allocation Limit ------------------------------ ------------ ------------ ---------- ---------- enqueue_locks 6 6,930 6930 6930 ------------------------------------------------------------- init.ora Parameters DB/Inst: SEPBZ212/SEPBZ212 Snaps: 7380-7382 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _gby_hash_aggregation_enabled FALSE _optimizer_push_pred_cost_bas TRUE audit_file_dest /soft/oracle10/admin/SEPBZ212/adu background_dump_dest /soft/oracle10/admin/SEPBZ212/bdu compatible 10.2.0.3.0 control_files +PRODWH_DG01/SEPBZ212/control01.c core_dump_dest /soft/oracle10/admin/SEPBZ212/cdu db_block_size 32768 db_create_file_dest +PRODWH_DG01 db_domain db_file_multiblock_read_count 32 db_name SEPBZ212 dispatchers (PROTOCOL=TCP) (SERVICE=SEPBZ212X fast_start_parallel_rollback FALSE job_queue_processes 10 local_listener LISTENER_SEPBZ212 nls_language ENGLISH nls_territory USA open_cursors 500 parallel_execution_message_si 4096 parallel_max_servers 80 pga_aggregate_target 10737418240 processes 500 recyclebin OFF remote_login_passwordfile EXCLUSIVE remote_os_authent TRUE sessions 555 sga_target 1073741824 spfile +PRODWH_DG01/SEPBZ212/spfiledwh02 undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest /soft/oracle10/admin/SEPBZ212/udu ------------------------------------------------------------- End of Report