Hello all,
Oracle Standard Edition 11.2.0.3 (application requires this specific version) running on Windows. Comments about how great Linux is will be studiously ignored (I'm talking about you, @"Billy~Verreynne"
I've got a situation that I'm struggling to understand. I'm reviewing a statspack report (yes, I know AWR is better, but we're on Standard Edition). In that statspack report, I'm trying to figure out why we have such a high number of parses (more than 600 per second). When I look at SQL ordered by parse calls, I'm seeing the following (this is a 30 minute statspack report):
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
- 28,000 parses. No module name given
insert into sys.aud$
...<snipped> - nearly 28,000 parses. Module name is one of our programs
select value$ from props$ where name = 'GLOBAL_DB_NAME'
- nearly 28,000 parses. No module name given
insert into sys.a$ud
..<snipped> - nearly 28,000 parses. Module name is one of our programs, different and unrelated to the program above
I'd certainly be interested in what is going on with sys.auth$ and prop$ that makes those queries be parsed 15 times per second (any thoughts certainly welcomed), but let's have a look at the parses being done by our programs.
The thing that is very confusing to me is specifically with respect to the 4th query inserting into sys.aud$. That particular program only runs from one single host, so I went to have a look in the audit trail (which has 14 days of information in it), and there are only 63,506 rows in there (dba_audit_trail) for that particular hostname. If we are inserting nearly 28,000 records into sys.aud$ every 30 minutes (as I would presume, given that we're parsing the insert that often - unfortunately, this statement isn't on the "top sql by executions" list), I would expect more like 18 million records in the audit trail for that hostname. Our program certainly wouldn't (I expect) be doing that insert itself. The only records I see in the audit trail from that host are for LOGON and LOGOFF (well, 1 logoff by cleanup).
Any explanations as to what might be going on here? The second statement in the list (also into sys.aud$) is a bit harder to pick apart, as that program runs on 9 different hosts, and there is lots more going on with those hosts, so it's not as easy to see what's going on.
I'd welcome any thoughts on what might be happening here. In fact - when I look at the top sql by parse calls, 3 of them list our programs as the module name (2 are for aud$ and one is just someone forgetting to turn on statement caching), and the other 7 are against internal tables/views (sysauth$, prop$, service$, v$instance, sysauth$ again, proxy_info$) with no module listed - no idea how to fix those at all - unless perhaps there is some rogue program that is logging on and logging off at an insane rate.
Thanks,
John