RE: Query to validate database status

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Thu, 9 Jul 2015 03:29:31 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA63708A2A1_at_HKWPIPXMB03C.zone1.scb.net>

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 †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Thu Jul 09 2015 - 05:29:31 CEST