Choosing not to install statspack (in the absence of Diagnostics Pack
licenses) seems rather odd, perhaps even self-defeating.
Yes. Statspack will put a (very) small load on the database. But even
sampling at 10 minute intervals (which is more often than most people
sample), you should find that the added load is quite modest.
In return for that "added load", though, you get INFORMATION.
Specifically, you get information that helps you diagnose and fix
performance issues just like the ones you are facing. In fact, all you
need to do to make StatsPack "pay off" is find ONE inefficient query and
improve it -- the reduced load on the database due to the improved SQL is
likely to more than compensate for any load added by StatsPack.
As for your specific problem, I cannot say much -- because you have not
told us much, except that you have consciously elected NOT to gather the
sort of data we would need to help you. :-)
This might be a very good time to sit down with management and force a
decision -- either choose to part with the money needed to license
Diagnostics Pack (and you might as well think about Tuning Pack at the same
time) or make a decision to incur the risk of (modest) resource consumption
in order to use StatsPack to gather the performance data you so clearly
need.
The third option, of course, is to choose to do neither, and declare that
you (or your management) are happy with the system as it is, or in whatever
state it happens to fall into, and perhaps live with making random guesses
when severe performance issues arise.
You could go with a third-party performance monitoring tool, of course.
But then you have all the disadvantages of both of the other options --
capital expenditure *and* overhead on the database.
On Wed, Jul 8, 2015 at 11:29 PM, Chitale, Hemant K <Hemant-K.Chitale@xxxxxx>
wrote:
If you don't have AWR reports or StatsPack reports, how did you determine
the Top SQL ? Did you filter on V$SQLSTATS by CPU_TIME (what about
considering ELAPSED_TIME or BUFFER_GETS and any of the _%WAIT_TIME columns)
over two manual snapshots of V$SQLSTATS ? OR did you use some other query
? OR is this Top SQL from Enterprise Manager / Database Control ?
What version are you running ?
Hemant K Chitale
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Fernando Jose Andrade
Sent: Thursday, July 09, 2015 10:21 AM
To: Oracle-L
Subject: Query to validate database status
Hi All.
I´m looking a database that has high CPU and enqueueing. Since is a RAC
database I thought it has something to do with the interconnect but it’s
not. I’m still analysing it.
Sadly I can use AWR reports because there isn’t a licence for the
corresponding packs. And more sadly I can use statspack since is a heavy
production environment and they don’t want to hear about anything that put
some extra stress on the database.
What it’s strange is when I get the top SQL for the database this query
is the top one in the four instances of the RAC:
select 1 from V$INSTANCE
Plan hash value: 2848324471
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | MERGE JOIN CARTESIAN | | 100 | 4700 | 0 (0)|
| 2 | MERGE JOIN CARTESIAN| | 1 | 47 | 0 (0)|
|* 3 | FIXED TABLE FULL | X$KSUXSINST | 1 | 13 | 0 (0)|
| 4 | BUFFER SORT | | 1 | 34 | 0 (0)|
|* 5 | FIXED TABLE FULL | X$KVIT | 1 | 34 | 0 (0)|
| 6 | BUFFER SORT | | 100 | | 0 (0)|
| 7 | FIXED TABLE FULL | X$QUIESCE | 100 | | 0 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
5 - filter("KVITTAG"='kcbwst')
For sure they are making some sort of validation for the database to be up
before every transaction.
I guess a SELECT 1 from DUAL will have less overhead ( since this database
is a 10.2.0.5 Linux 64 bit ).
I must make a trace analysis, I know, I’m doing this tomorrow. But want to
hear any ideas or experiences about a validation query for knowing if the
database is up.
I´m pretty sure a more elegant solution will be to use any mechanism embed
with the database ( FAN or a POOL ), but I have no influence for doing a
major change in the application, only can give a medium advise ( change the
query at most ).
Thanks.
FJA
PD: Sorry for the rudimentary english, I’m not a native speaker.--
//www.freelists.org/webpage/oracle-l
This email and any attachments are confidential and may also be
privileged. If you are not the intended recipient, please delete all copies
and notify the sender immediately. You may wish to refer to the
incorporation details of Standard Chartered PLC, Standard Chartered Bank
and their subsidiaries at https://www.sc.com/en/incorporation-details.html
