1

I have been investigating some performance issues in our production system. One piece of SQL jumped out as having a very high execution count, coupled with non stellar performance, peaking at 20+ times a second per node, with an execution time of ~ 1 second. This does not tally up with the total executions/fetched I see in V$SQL, or the expected behaviour of the application.

Some info

  • We have a limited performance toolkit, this data was from hour long statspack snaps. We are running standard edition so no AWR.
  • It's running on a 2 node 11g RAC installation.
  • Looking at figures for yesterday, I see > 500,000 executions per node in a 9 hour period. V$SQL is showing me ~ 50,000 executions from a first load time of 8 days ago.
  • I get matching data when I directly query the statspack tables, not a dodgy SPREPORT.
  • The executions per hour are spikey, we get 1 or 2 a day that are 10-20 times the average on each node. The times are different per node. The usual figures sound a little elevated from how the app is supposed to behave, but acceptable.

The dev manager insists the application cannot be behaving like this, which sounds reasonable. But what could be causing the mismatch in reporting?

It's possible that statspack is misbehaving, but why just periodically? Could it be a RAC issue (I'm totally new to RAC)?

Any suggestions on a cause or further troubleshooting tips?

Carlovski
  • 121
  • 4

2 Answers2

1

Use GV$SQL instead of V$SQL to see results from all nodes.

Keep in mind that data can age out of GV$SQL for several reasons. Most of the data will be removed if someone runs alter system flush shared_pool;. Values can disappear if the cursors are invalidated because of a statistics change. And values can disappear if they age out, possibly because the shared pool is too small or there's some other large amount of activity.

I don't have experience with standard edition or statspack. But you may want to look into an open source option like orasash.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Yeah, sorry - did mean gv$sql! It's not aged out as far as I can tell, the SQL is running all the time and nobody other than my team should have access to run a flush (and we haven't!) Orasash is a good idea, and we have been looking at that (along with ASBO) and it might help me pinpoint it. I need to review the code before putting into live though (Am certain it's fine, but running someone elses installs as SYS into a production system with sensitive data gets me nervous) – Carlovski Apr 25 '18 at 08:49
  • I'll do a quick and dirty bit of logging though, just snapshot the stats for that particular piece of SQL into my own table for the day, – Carlovski Apr 25 '18 at 08:53
1

Ok, my logging, and Jon's suggestions appears to have explained this (Mostly). We had multiple versions of the query in the shared pool (i'm not totally sure why, or what caused the invalidations). The execute count of these versions stay static while the active version increases. I can see this happen in my 2 minute snapshots. At some point, these versions do age out, so the total execute/parses suddenly falls.

Statspack, and the query I was using (Own fault, just lifted something from a blog post) both appear to only check the difference between the snapshot values. So when the count drops by 50,000 - it reports it as 50,000 executions.

Which seems stupid, but is the only thing that makes sense.

Carlovski
  • 121
  • 4