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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of MARK BRINSMEAD
Sent: Thursday, July 09, 2015 2:18 AM
To: correo@xxxxxxxxxxxxx
Cc: ORACLE-L; Hemant-K.Chitale@xxxxxx
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@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
