Re: Query to validate database status
Date: Thu, 9 Jul 2015 02:17:46 -0400
Message-ID: <CAAaXtLDk4TLPV-ya8A8-Xwyky6isTr2DUMNPP+YLC60Q_aFaDA_at_mail.gmail.com>
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_at_sc.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> 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.--
> http://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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 09 2015 - 08:17:46 CEST