I cant read anything useful, cant you format the output or paste a screenshot :-? On Wed, Mar 4, 2015 at 4:28 PM, Sandra Becker <sbecker6925@xxxxxxxxx> wrote: > AWR from old server > > Host CPU (CPUs: 32 Cores: 16 Sockets: 4) > > Top 5 Timed Foreground Events > > EventWaitsTime(s)Avg wait (ms)% DB timeWait Class db file parallel > read72,5704,3556050.98User I/O DB CPU 2,092 24.49 db file sequential > read387,1051,308315.31User I/O direct path write temp3,2275091585.96User > I/O db file scattered read133,0512362 2.27 User I/O > > > Operating System Statistics - Detail Snap > TimeLoad%busy%user%sys%idle%iowait 24-Feb 10:00:421.06 24-Feb > 11:00:592.024.401.742.6695.600.00 > > AWR from new server > > Host CPU (CPUs: 32 Cores: 4 Sockets: 1) > > Top 5 Timed Foreground Events > > EventWaitsTime(s)Avg wait (ms)% DB timeWait Class db file parallel > read46,33718,80840643.47User I/O db file sequential > read154,0626,8614515.86User I/O direct path write temp8,3943,2033827.40User > I/O log file sync3,0021,5645213.61Commit DB CPU 1,433 3.31 > > > > Operating System Statistics - Detail Snap > TimeLoad%busy%user%sys%idle%iowait 03-Mar 10:00:422.73 03-Mar > 11:00:372.957.124.692.4392.880.00 > I asked the SEs to look at both the server and storage for any errors, > anomalies, etc. They report no issues and the load on the server is lower > than it ever was on the old server, even with two additional databases on > the new server. Part of our consolidation project--fewer but bigger, more > powerful servers. I also asked them to take a look regarding the > information Al suggested regarding the EMC. > > Sandy > > On Wed, Mar 4, 2015 at 7:47 AM, Sandra Becker <sbecker6925@xxxxxxxxx> > wrote: > >> Unfortunately, I cannot share the entire AWR report with you due to >> company policy. I do see that avg wait ms is higher for reads and writes >> now. I have another fire I have to fight, but will share those stats as >> soon as possible. >> >> We do have a license for the tuning pack. I'll have to do it manually >> since I wasn't allowed to set up grid in the new environment. Waiting on >> DatAvail to come in and handle that maybe next week. >> >> Some very good suggestions that I will work on. Thank you. >> >> >> >> On Wed, Mar 4, 2015 at 7:24 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx> >> wrote: >> >>> This does look like a tough one. However, calibrate_io shouldnt break >>> anything. If you have the tuning pack, you might want to run the sql >>> tuning advisor and see if it makes any suggestions. Also, you might want >>> to look at the plan and verify whether or not it is running partition >>> pruning. Did you check your init.ora settings and see if their are any >>> major differences that could account for the problem? >>> >>> On Wed, Mar 4, 2015 at 7:25 AM, Sandra Becker <sbecker6925@xxxxxxxxx> >>> wrote: >>> >>>> OS: Solaris Sparc 10 (64-bit) >>>> Oracle: EE 11.2.0.2 >>>> >>>> The OS and Oracle versions are identical on both the old and new >>>> servers. Storage attached to the new server is a new EMC disk array. >>>> Sorry I don't have any more details on the storage and the only additional >>>> information I have on the server is that it is a T5. >>>> >>>> We created a standby on the new hardware and did a switchover last >>>> Friday night. On Saturday I completed gathering stats on the application >>>> schema tables as requested by the product manager. As usual, very little >>>> activity on this database over the weekend. Yesterday morning we were >>>> contacted by internal users that performance was much worse than on the old >>>> hardware for a specific query on a really ugly view. A look at the >>>> execution plan shows multiple full table scans on some partitioned tables, >>>> some very large. There are about 15 tables joined to create the view, some >>>> more than once. They claim the view is no longer doing partition pruning, >>>> as it did before the switchover. I can't prove that it was/wasn't >>>> exhibiting this behavior before the switchover. They are insisting we run >>>> I/O calibration. I'm not familiar with it so I went to the docs. This >>>> database shares storage with quite a few production databases so I want to >>>> be very careful how I go about this. >>>> >>>> Questions: >>>> >>>> 1. What will running the I/O calibration do? Does it only provide >>>> information on the I/O subsystem, or does it change the way the optimizer >>>> behaves? The development team insists it will improve performance. >>>> 2. I've looked at AWR reports before/after the switchover and see that >>>> the query in question was doing a similar amount of I/O in both reports. >>>> Is there any way for me to get more detail on the before execution plan? >>>> 3. One of the large partitioned tables has no indexes. Would creating >>>> an index be of any benefit? I understand that it's possible to negatively >>>> affect other queries, so it should be considered with caution. Development >>>> insists that indexing would be a waste of time and definitely cause >>>> problems, although they have never tested it. >>>> 4. I want to trace the query, but it runs in parallel and produces >>>> more trace data that I have available disk to handle. Is there anything I >>>> can do on that front to get a trace I can feed into my Method-R tool and >>>> supply to oracle support? >>>> >>>> As I reviewed how the view, I recall them having issues with it before >>>> and me suggesting it should be optimized. I was told no and here we are >>>> again. The obvious concern is that the results would be different and >>>> changes require a lot of testing they don't have time to do. Any other >>>> recommendations would be appreciated. >>>> >>>> -- >>>> Sandy >>>> GHX >>>> >>> >>> >>> >>> -- >>> Andrew W. Kerber >>> >>> 'If at first you dont succeed, dont take up skydiving.' >>> >> >> >> >> -- >> Sandy >> GHX >> > > > > -- > Sandy > GHX >