RE: Query to validate database status

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 9 Jul 2015 07:13:13 -0400
Message-ID: <03cf01d0ba38$4173d6f0$c45b84d0$_at_rsiz.com>

All good thoughts.

Before you rip this query out, though, you’ll need to verify it is not a test that the database is up but rather whether access to any v$ views are available to the account or something like that.  

As usual, before you start thinking this is very expensive you’ll want to do something like:

select

--+gather_plan_statistics

    1 from v$instance;  

select * from table(dbms_xplan.display_cursor(format=>'COST ALLSTATS LAST'));  

to see the actual costs. It won’t be quite as cheap as

select

--+gather_plan_statistics

    1 from dual;  

but it won’t be much. It could quite well indicate someone is reusing a not-too-smart startup call that has been blindly copied into every query set or procedure. In addition to a bias against metric collection overhead in your organization, blind copy/paste is a disease you should check for a bit. Regarding metric collection overhead: The database is already doing most of the work as a fixed cost. As long as you don’t allow the number of folks simultaneously querying the metrics already available grow wild it is unlikely you’ll be able to detect the load of siphoning them off to a non-production server for analysis.  

Whether you’re currently experiencing poor response time to important business functions or not (which you can find out by asking users and/or checking batch job completion times; read the Cary Millsap/Jeff Holt works about Method-R if you need a comprehensive explanation of that bit), you should always be collecting and retaining time stamped metric information about all your production databases in your Data warehouse for the DBA.        

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MARK BRINSMEAD Sent: Thursday, July 09, 2015 2:18 AM
To: correo_at_fjandrade.com
Cc: ORACLE-L; Hemant-K.Chitale_at_sc.com Subject: Re: Query to validate database status  

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-l

Received on Thu Jul 09 2015 - 13:13:13 CEST