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
