- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COM...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 03:55 AM
тАО10-21-2003 03:55 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
thanks for your responses so far, but the problem is:
- it is 8.16
- it runs on a SPARC
- the whole DB resides in "ufs" logging filesystem (on "MD" mirrors)
- Oracle *was* installed with all cpus active (and even relinked later)
- the size and amount of redologs is not the problem, as *they* (the cpus, or whoever else) slows down when accessing the *one and only current* redo-logfile
- a slower single-cpu SPARC is much faster (not for anything else, only for the "commit" statements) in total
- the customer cannot/will not change the patch level, as some other software depends on what is already there...
so, to ask in other words: does anybody know any problem of multi-cpu SPARCs running oracle getting into some
- race-condition
- deadly embrace (well, not deadly, but dog-slow)
- file I/O concurrency
- filesystem concurrency
what I cannot pinpoint is wether the delay is caused by oracle (internally) or by the filesystem (Solaris, then). From application point of view only the SQL-statement "commit" is slow, and the Oracle performance tools tell me that the most time is spent on "syncing" the data to the current redolog...
any hints?
TIA,
Wodisch
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 04:26 AM
тАО10-21-2003 04:26 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
which Solaris version is it 7 or 8 ?
Is Solaris Async-IO enabled ?
If yes, there had been quite a few op-sys patches in Solaris for this feature (up to date ?).
If not, consider to set up
Oracle-LGWR_IO_SLAVES to parallelwrite the redo info (Oracles own implementation of application-async-io). DO NOT USE BOTH!
Logwriter writes, if log_buffer in one third full or if a commit is issued. How big is your log_buffer ? How many Commits do you get per minute ?
Metalink has some stuff for tuning redo_log_sync, mostly sugesting to reduce the amount of commits on application side *smile*.
What do you compare ? A single commit on both platforms ? A bunch of 1000 in parallel ?
I guess, this one will be hard to track down ....
Good hunting
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 05:13 AM
тАО10-21-2003 05:13 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
seen on Metalink some entries with same type of pb (SPARC) wher one cpu SPARC is better that multiple cpu box !
Check doc id 134172.999 on Metalink
This was 1st reported as a bug but then it was not a big ?!
Not sure this is xactly your pb , but this is close to it.
other docs, seacrh on 929709 (the bug number , but not a bug ?!)
Rgds,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 06:43 AM
тАО10-21-2003 06:43 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
what's the top 5 waits events seen on the statspack reports ?
Is too many waits spent on log file sync, redo log file parallel write, log buffer space ?
how many txns are you commiting / sec (should be seen in statspack)
Is your FS using direct io, whats the value set for disk_async_io
-
Stan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 07:05 AM
тАО10-21-2003 07:05 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
it is Solaris8, and I never use async-IO ;-)
The redologs are 100MB each, and logswitches happen only every couple of minutes, that does not seem to be the problem.
Reducing the amount of commits is NOT an option ;-)
The most simple way to reproduce the bottleneck was to do some inserts and a few selects then commit. Repeated a 1000 times.
So actually no parallelism inside the program itself...
The statspack's snapshot output shows:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync 1,002 2,468 41.29
log file parallel write 1,004 2,456 41.09
control file parallel write 116 1,051 17.58
file open 32 1 .02
db file sequential read 4 1 .02
-------------------------------------------------------------
The values for a much slower single cpu system is 1/10th for the first two events!
Oh, and the disks on the multi-cpu system are much faster, too, as are the controllers...
I am pretty lost on that. My guess that Solaris8 is doing "spinlocks" on the multi-cpu system and waisting a hell-of-a-lot-of-time on that.
We do experience that behaviour on two pretty different systems, 2-cpu-system, and a 4-cpu-system, both with different hardware - so it looks pretty much like a software issue.
Any other ideas?
Thanks a lot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 08:05 AM
тАО10-21-2003 08:05 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
it is Solaris8, and I never use async-IO ;-)
>
yes, solaris by default uses threaded async/io to simulate kernalized async/io. though its not as efficient as kaio, but still its okay as oppose to nothing :)
The redologs are 100MB each, and logswitches happen only every couple of minutes, that does not seem to be the problem.
>
yes, thats not your problem. i don't see any wait events alluding to checkpointing.
Reducing the amount of commits is NOT an option ;-)
>
absolutely not. many suggest these options, but ideally this will not be the solution to most of 3 party vendor applications ;) you know what i mean.
The most simple way to reproduce the bottleneck was to do some inserts and a few selects then commit. Repeated a 1000 times.
So actually no parallelism inside the program itself...
The statspack's snapshot output shows:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync 1,002 2,468 41.29
log file parallel write 1,004 2,456 41.09
control file parallel write 116 1,051 17.58
file open 32 1 .02
db file sequential read 4 1 .02
-------------------------------------------------------------
>
seems like your fs is not coping with lgwr. do you multiplex redologs. if yes, then try not multiplexing. since your are mirrioring the logs, you should be okay. other thing is, use direct/io on the fs that has redologs on it. That will improve the lgwr performance.
The values for a much slower single cpu system is 1/10th for the first two events!
>
any chance the tests are carried out bit different as oppose to multiple cpus for e.g. test were done via pl/sql procedure, This can make lot of difference.
Oh, and the disks on the multi-cpu system are much faster, too, as are the controllers...
I am pretty lost on that. My guess that Solaris8 is doing "spinlocks" on the multi-cpu system and waisting a hell-of-a-lot-of-time on that.
>
If thats the case you should see cpu cycles being use a lot. do you see them? i don't think so.
We do experience that behaviour on two pretty different systems, 2-cpu-system, and a 4-cpu-system, both with different hardware - so it looks pretty much like a software issue.
Any other ideas?
Thanks a lot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2003 08:46 AM
тАО10-21-2003 08:46 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
.
Also, if your're using cooked UFS for your datafiles -- make sure you are using at least Solaris 8 07/01 (cat /etc/release) and at least a -13 Kernel Rev.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-22-2003 07:01 PM
тАО10-22-2003 07:01 PM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
we are still working on that :-(
To clarify some points:
- only single-member redologs are used
- the problem happens on different HW and even on Oracle8.1.7
Regards,
Wodisch
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-23-2003 04:49 AM
тАО10-23-2003 04:49 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
I'm very much intrested on the outcome of the issue and how you fixed it. Keep us updated.
-
Thanks,
Stan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-25-2003 09:47 AM
тАО10-25-2003 09:47 AM
Re: Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs
if possible I'll do that.
But it may be one of those "unresolved" questions of mine :-(
Regards,
Wodisch