- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s fr...
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
тАО01-28-2008 12:40 AM
тАО01-28-2008 12:40 AM
I have a server N4000 with 4 CPU's and 16Gb RAM, patched. Normally the server works fine, but sometimes the efficiency of oracle DB down. In this case, I see the uptime of server that is normally (more or less 2), but when I execute a sar -m I see that de sema/s is about 1500, when is a normal situation is about 200.
I have clients who work locally and other that work via listener. How can I know which sentences are affecting the efficiency of the DB? If I list the process (ps -ef) I only see the processes but no the sentences. I need to know what sentence (select, insert, delete, ...) is affecting the server.
Thank you in advance.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2008 01:10 AM
тАО01-28-2008 01:10 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
Most of the time this comes down to two things:
1) Bad application code from developers (Have the dba review all new sql for efficiency)
2) Database bugs (good luck with this one your db is so way far out of support as is your OS)
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2008 02:56 AM
тАО01-28-2008 02:56 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
It's possible to know which process is making theses semop() calls?
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2008 03:04 AM
тАО01-28-2008 03:04 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
Glance would show you the counts.
tusc would also show it but would be harder.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-28-2008 04:21 AM
тАО01-28-2008 04:21 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
Thank you very much.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-01-2008 01:25 AM
тАО02-01-2008 01:25 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
"Normally the server works fine, but sometimes the efficiency of oracle DB down." Humm, it sounds familiar to me...
Can you post the following (attach an excel or post the first 5 entries) results:
select *
from v$system_event
where event not like 'rdbms%'
and event not like 'SQL%'
and event not like '%time%'
and event not like '%pipe get%'
and event not like '%dle%'
and event not like 'queue%'
and event not like 'jobq%'
order by time_waited desc
Best Regards,
Eric
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-01-2008 01:55 AM
тАО02-01-2008 01:55 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
Here is the XLS file with result of the select.
We stop every night the DB to make the backup, so I think that the select should be launched after the DB has the problem and before the shutdown.Isn't it?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-01-2008 07:57 AM
тАО02-01-2008 07:57 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
Yes, after the DB has the problem and before the shutdown it's the right time.
From the excel you attached, you may be facing some latch contention. What kind of application are you runing?
Can you post an excel for the following:
select pc.name stat_name, pc.value*100/decode(ec.value,0,1,ec.value) "PARSE COUNT (HARD) RATIO < 3%"
from v$sysstat ec, v$sysstat pc
where ec.name='execute count'
and pc.name like '%parse count%'
Best Regards,
Eric
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-01-2008 08:56 AM
тАО02-01-2008 08:56 AM
Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m
The result of the select is:
STAT_NAME PARSE COUNT (HARD) RATIO < 3%
parse count 24,0558122357901
The DB is used from application servers with JBOSS and from a local application developed with Forms, aproximately 500 users locally and 1500 via listener.
A lot of thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-06-2008 07:13 AM
тАО02-06-2008 07:13 AM
SolutionSorry for the delay but it was holiday here in Portugal. ;-)
As I said before, your application(s) may have a lot of literal SQLs (SQL that don't use bind variables):
select count(*), sum( q.executions), q.module, q.sql_text
from v$sql q
where q.executions between 0 and 1
group by q.module, q.sql_text
order by 1 desc
Those in the top are the problematic ones.
If you have Metalink access, check also Note 16347.1 that is specific for Oracle 7.
Best Regards,
Eric Antunes