Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

How to find Top Sql through Queries

How to find Top Sql through Queries

If someone ask for top sql they from enterprise manager he can find some expensive queries. But the result may vary based on the metrics or criteria of expensive. Measurement may vary on,
a) Logical IO’s per execution?
b) Physical IO’s per execution?
c) Cpu usage?
d) Based on number of parse calls?
e) Elapsed time used?
f) Number of executions?
g) Size consumed in shared pool?
h) Number of child versions found?
i) Based on Wait time?

However from DBA_HIST_SQLSTAT we can get a complete picture of historical SQL statistics. AWR does a pretty job. Between two snapshot it displays the top sql statements in the enterprise manager. We can also see it from sql queries.

In fact Enterprise Manager displays information of SQL text from DBA_HIST_SQLTEXT view which captures information from V$SQL and is used with the DBA_HIST_SQLSTAT view.

Along with the DBA_HIST_SQLSTAT and DBA_HIST_SQLSTAT we can use the view DBA_HIST_SNAPSHOT in order to specify the range of snapshots in between analysis will be done.

We can specify the BEGIN_INTERVAL_TIME and END_INTERVAL_TIME column of the view DBA_HIST_SNAPSHOT instead of specifying SNAP_ID if we are determined to calculate the top sql query between date range.

From the combination of the three above views here is one query while calculates the top sql from date 09/01/2008 to 09/09/2008 based on the CPU time.

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE(’09/01/2008′,’MM/DD/YYYY’)
AND END_INTERVAL_TIME<=TO_DATE(’09/09/2008′,’MM/DD/YYYY’)) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;

August 18, 2008 Posted by | oracle | 1 Comment

ORA-00205: error in identifying control file

ORA-00205: error in identifying control file

 

Problem Description


Whenever you try to mount your database  it fails with ORA-00205 error message as below.
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 306185108 bytes
Database Buffers 222298112 bytes
Redo Buffers 5844992 bytes
ORA-00205: error in identifying control file, check alert log for more info

Error Investigation
As error message “ORA-00205: error in identifying control file, check alert log for more info” suggests  examine the   error stack in the alert log file following the error.

Based on this error message you might get various types of alert log entries which would really help you to solve your problem. Following is the several  error stack messages.

A.
ORA-00202: control file: ‘/oracle/controlfile/contro101.ctl’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Feb 8 17:19:42 2008
ORA-205 signalled during: ALTER DATABASE MOUNT…
Fri Feb 8 17:19:50 2008
Shutting down instance (abort)

B.
ORA-00202: controlfile: ‘/u01/oracle/controlfile/CONTROL01.CTL’
ORA-27086: skgfglk: unable to lock file – already in use
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

C.
ORA-00202: control file: ‘C:\ORACLE\CONTROL.CTL’
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 38) Reached the end of the file.

D.
Errors in file /oracle/9.2.0/admin/bdump/abc_ckpt_3117.trc:
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: ‘/oracle/data/ctl03.dbf’
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system
Additional information: 2

General Cause And Solution of ORA-00205 Problem:


The ORA-00205 problem is reported because the database could not find a control file with the specified name. The name of control file is specified by the CONTROL_FILES initialization parameter within spfile/pfile. While mounting the database oracle produces an error if the file specified by the CONTROL_FILES parameter is either missing or corrupted or with wrong permissions.

If you see ORA-00205  reported while mounting the oracle database then check that the proper control filename is listed in the CONTROL_FILES initialization parameter in the initialization file.  If not check the correct spfile/pfile or fix the location of CONTROL_FILES parameter within spfile/pfile and try again to mount the database.

Whenever you are using mirrored control files, that is, more than one control file is listed in the initialization parameter file, remove the control filename listed in the message from the initialization parameter file and restart the instance. If the message does not recur create another copy of the control file with a new filename in the initialization parameter file.

Step by step Solution


1.  After you see ORA-00205 immediately look for alert log files for further investigation.

2.  Check the control_files parameter in your initialization parameter. You can simply check by ,
SQL> show parameter control_files

Now compare this location with the location of controlfile in your disk system.

From the alert log if you see error message like version A i.e you have noticed “ORA-00202: control file: ‘/u01/oracle/controlfile/contro101.ctl'” then from your CONTROL_FILES initialization parameter remove the entry /u01/oracle/controlfile/contro101.ctl if you have multiple version of controlfiles and start the database. If you fail then again check for alert logs. If it is same error message like points to another controlfile then try to remove that control file too if you have at least three versions of controlfiles. If you fail then possibly all of your controlfiles are lost. In that case you need to restore controlfile from previous backup or create a new one if you don’t have any previous backup of controlfile.
If you see other version of error versions then take necessary actions as alert log suggests. For example if you see error message like version B that is
“ORA-00202: controlfile: ‘/oracle/controlfile/CONTROL01.CTL’
ORA-27086: skgfglk: unable to lock file – already in use”
then ensure that oracle database is shutdown cleanly and no other process is holding lock of the control file. After you ensure that try starting database again.

If you see error message like version C that is
“ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file”
then possibly your control file got corrupted and follow the same steps as it is demonstrated in step 2 for error version 01.

If you see error message like version D that is
“ORA-00202: controlfile: ‘/oracle/data/ctl03.dbf’
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system”
then ensure that your control file has proper permission set from operating system. That means it must have read, write permission to oracle owner user.

Summary:


After you identify and solve the problem make sure that you have multiple copies of your controlfile specified in the CONTROL_FILES parameter.

August 15, 2008 Posted by | oracle | 2 Comments

Raw Devices to be deprecated in 11gR2

Raw Devices to be deprecated in 11gR2

I was looked at Metalink Note:578455.1 – Announcement of De-Support of RAW devices in Release 11gR2

If you go through the note, it mentions de-support of Raw devices from Oracle Database 12G. Article also lists out possibility of using ASM,OCFS as few of the alternative storage mediums for keeping OCR and Voting Disks (Used in Oracle Real Application Clusters(RAC)) .

OCFS2 already supports the storage of OCR and Voting Disk. But note also talks about ASM will be supporting the files. Hmmm… If this has to be true, lot of changes will be required in the architecture. Currently ASM instance starts after CSS (and other clusterware services in RAC) service has been started. But this change will mean that ASM has to start before these processes. Currently if you try to start the ASM instance with CSS service down, you get following error

[oracle@raclinux2 ~]$ export ORACLE_SID=+ASM1

[oracle@raclinux2 ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Aug 10 08:16:09 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup

ORA-29701: unable to connect to Cluster Manager

SQL> exit

Disconnected

[oracle@raclinux2 ~]$ oerr ora 29701

29701, 00000, “unable to connect to Cluster Manager”

// *Cause: Connect to CM failed or timed out.

// *Action: Verify that the CM was started. If the CM was not started,

// start it and then retry the database startup. If the CM died

// or is not responding, check the Oracle and CM trace files for

// errors.

Apart from changing the architecture, it will also involve lot of effort from Oracle DBA’s to unlearn and learn new concepts. At the same time, it will help you start the ASM Instance even though CSS is not up!! (I know many people will be having a sigh of relief after reading the last line) Or is there something else in store for us! There are also lot of RAC and ASM features expected in 11gR2. So let’s wait and watch..

August 12, 2008 Posted by | oracle | Leave a comment