DATABASE ACTING DEAD SLOW DEAILY

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: ENG: DATABASE ACTING DEAD SLOW DEAILY @ THE SAME SAME TIME WINDOW

Database running very slow between around 2300 and 0200 Hours Everyday.




Top 5 Timed Events
~~~~~~~~~~~~~~~~~~~
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 13,783 31.8
log file sync 737,638 11,829 16 27.3 Commit
log file parallel write 749,259 8,533 11 19.7 System I/O
db file scattered read 305,077 6,418 21 14.8 User I/O
enq: TX - row lock contention 2,234 4,506 2,017 10.4 Application

SQL ordered by Elapsed Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
11,006 4,407 0 25.42 30jv4ktjqj3xz DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
6,350 2,790 0 14.67 10828gd6bbnwt DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...


1,811 55 1 1810.78 4.18 6mcpb06rctk0x DBMS_SCHEDULER call dbms_space.auto_space_adv...
1,715 48 50 34.29 3.96 8szmwam7fysa3 DBMS_SCHEDULER insert into wri$_adv_objspace_...
1,568 187 1 1567.97 3.62 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...

OBSERVATION
===========
30jv4ktjqj3xz
~~~~~~~~~~~~~~~
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; BEGIN BEGIN OPCTF044.MAIN_PROC('J', 'T1'); COMMIT; END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;


OBSERVATION
==========
+ AWR Report is for 300mins instead of request multiple 60mins reports.
+ We can see high waits for "Log file Sync" waits.
+ dbms_stats auto job is running in the night.
+ Besides there are other maintainenance jobs runnning in the night runnign space advisory.

SQL ordered by Reads
~~~~~~~~~~~~~~~~~~~~
Total Disk Reads: 10,792,730
Captured SQL account for 57.5% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
462,457 1 462,457.00 4.28 211.52 1874.71 0vj94pmqvwdux sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
424,535 1 424,535.00 3.93 87.93 838.76 74f7trkxksdzq sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
407,046 1 407,046.00 3.77 79.50 811.24 69qkjfnh3tvtf sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
380,101 1 380,101.00 3.52 1882.00 10834.46 amw3yd0j2ak61 sqlplus@jctpdb01 (TNS V1-V3) select /*+ parallel(t, 16) par...
378,133 1 378,133.00 3.50 49.44 448.18 3vuybk2a7828w sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
355,344 1 355,344.00 3.29 113.11 938.17 cx3nf783fppg4 sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
351,481 1 351,481.00 3.26 27.67 320.92 40kpkjgbfb3dm sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
351,309 1 351,309.00 3.26 31.46 400.39 2cdckjfy4cxhb sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
347,193 1 347,193.00 3.22 23.47 296.92 3w7fvrrccruqp sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
346,058 1 346,058.00 3.21 11.76 285.14 b602h54cr0bp4 sqlplus@jctpdb01 (TNS V1-V3) select substrb(dump(val, 16, 0...
346,058 1 346,058.00 3.21 11.61 295.87 c9586mbmqfknm sqlplus@jctpdb01 (TNS V1-V3) select substrb(dump(val, 16, 0...
346,058 1 346,058.00 3.21 16.47 271.41 f5zz84nsrp5bm sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
344,657 1 344,657.00 3.19 11.97 273.83 0dkxnkp0rnhp7 sqlplus@jctpdb01 (TNS V1-V3) select substrb(dump(val, 16, 0...
284,128 1 284,128.00 2.63 500.83 3903.04 g58ma37a1fkpa sqlplus@jctpdb01 (TNS V1-V3) select /*+ parallel(t, 16) par...
269,680 1 269,680.00 2.50 157.69 1354.73 f7kf4x9bskr9w sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
245,799 1 245,799.00 2.28 127.12 704.66 8wq3bawn5n2p7 sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su...
128,275 1 128,275.00 1.19 98.31 632.91 9h2gcqz93mcay sqlplus@jctpdb01 (TNS V1-V3) select min(minbkt), maxbkt, su


OBSERVATION
============
+ The db time increases over a period of time when the automatic job kick in.
+ We can huge waits for Parallelism. The Queries are mainly all related to statistics gathering.



disable the automatic statistics job for a couple of days and see if the performance is still bad. ?

NOTE.311836.1 How to Disable Automatic Statistics Collection in 10G
NOTE.377143.1 How to check what automatic statistics collection is

Even though observing the "Cluster" waits considering this a non-rac database.
and performance did not enhanced

#########
Bad Time
########


WORKLOAD REPOSITORY report for
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Name DB Id Instance Inst num Release RAC Host
PRD10 1688280630 prd10 1 10.2.0.2.0 NO jctpdb01


Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 25384 05-Sep-09 23:00:40 386 32.1
End Snap: 25385 05-Sep-09 23:30:40 384 32.9
Elapsed: 30.00 (mins)
DB Time: 312.41 (mins)


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~~
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 2,601 13.9
log file sync 101,472 2,091 21 11.2 Commit
PX Deq Credit: send blkd 121,035 1,607 13 8.6 Other
log file parallel write 98,120 1,346 14 7.2 System I/O
db file scattered read 48,401 763 16 4.1 User I/O





WORKLOAD REPOSITORY report for
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Name DB Id Instance Inst num Release RAC Host
PRD10 1688280630 prd10 1 10.2.0.2.0 NO jctpdb01


Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 25385 05-Sep-09 23:30:40 384 32.9
End Snap: 25386 06-Sep-09 00:00:42 439 34.8
Elapsed: 30.04 (mins)
DB Time: 383.26 (mins)


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~~
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
PX Deq Credit: send blkd 1,285,081 5,336 4 23.2 Other
CPU time 4,516 19.6
log file sync 53,710 894 17 3.9 Commit
log file parallel write 57,896 697 12 3.0 System I/O
PX qref latch 48,494,766 487 0 2.1 Other



##########
Good Time
##########


WORKLOAD REPOSITORY report for
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Name DB Id Instance Inst num Release RAC Host
PRD10 1688280630 prd10 1 10.2.0.2.0 NO jctpdb01


Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 25122 29-Aug-09 21:00:05 470 35.8
End Snap: 25123 29-Aug-09 22:00:19 489 37.4
Elapsed: 60.23 (mins)
DB Time: 853.29 (mins)


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~~

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
PX Deq Credit: send blkd 6,082,722 12,816 2 25.0 Other
CPU time 7,820 15.3
PX qref latch 73,108,081 1,449 0 2.8 Other
log file sync 76,710 978 13 1.9 Commit
log file parallel write 84,027 856 10 1.7 System I/O




OBSERVATION
===========
+ In Bad Time, We see on 05-Sep-09, the DB Time was 383.26 (mins).
+ During Good Time, We see on 29-Aug-09 21:00:05, the DB Time was 853.29 (mins)
+ The waits are maing on PX.
+ There are not major critical waits during the bad time.
+ The I/O on the box get a little slower at times.

1. Can you check with the Unix Folks , if they are observing the high Network usage particularly @ night during problem time Daily ?
2.. Considering there 4 databases on this box. have you looked at other databases . I am just wondering if the culprit is someone else and database is merely a victim.


NOTE.301137.1 OS Watcher User Guide

1. Complete OSWatcher output.
2. Exact time when you observed the slowness. , i believe we still have problem from 21:00 -
3. Upload complete alert log.
4. Upload multiple 30mins / 1 hours report covering

i) 1 hour prior to problem time start.
ii) Problem time.
iii) 1 after after the performance was back to normal.

Get Oracle Certifications for all Exams
Free Online Exams.com