How to determine if PGA is set properly

May 31, 2011 2 Comments

PGA is a memory region, that contains data and control information for a server process. This is not shared region. For each server process there is one PGA allocated.

In order to determine the proper size of PGA, we need to examine measuring criteria from performance views.

1. v$PGASTAT –displays PGA memory usage statistics.

select name,value
from v$pgastat
where name in ('aggregate PGA target parameter'
               ,'aggregate PGA auto target'
               ,'total PGA inuse'
               ,'total PGA allocated'
               ,'over allocation count'
               ,'extra bytes read/written'
               ,'cache hit percentage')

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter                                   8589934592
aggregate PGA auto target                                         536870912
total PGA inuse                                                 18699225088
total PGA allocated                                             23640664064
over allocation count                                                187532
extra bytes read/written                                       305283407872
cache hit percentage                                                  99.2

7 rows selected
 
As you can see “total PGA inuse” is greater than “aggregate PGA target parameter” this happens when PGA_AGGREGATE_TARGET is too small and it in turn causes lots of system I/O as indicated “extra bytes read/written”.
 
2. v$SQL_WORKAREA – displays information about work areas used by SQL cursors. SQL statements, that are stored in the shared pool have one or more child cursor that are shown in V$SQL view. And v$SQL_WORKAREA shows all work areas needed by these child cursors.
The important columns of this view are the followings:

OPTIMAL_EXECUTIONS– number of times, this work area ran optimally without using temporary tablespace.

ONEPASS_EXECUTIONS– number of times, this work area used temporary tablespace only once to get it finished.

MULTIPASSES_EXECUTIONS-number of times, this work area used temporary tablespace in multiple times to get it finished.

Let’s see the total picture, how are they distributed:

select sum(optimal_executions) optimal,
       sum(onepass_executions) onepass,
       sum(multipasses_executions) multipass
from v$sql_workarea

—My output

OPTIMAL    ONEPASS  MULTIPASS
------- ---------- ----------
0487582         51         16
 
We have 51 onepass and 16 multipass executions… They are not big numbers but it would be better if all of them would be optimal.
So we should think about properly adjusting PGA size.
 
3. If we join the following views v$SESSTAT, v$SYSSTAT and v$STATNAME we can see if PGA is properly set or not:
 
select b.name,sum(a.value) value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC#
and b.name like '%workarea executions – %'
group by b.name;

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - onepass                                            19
workarea executions - multipass                                           0
workarea executions - optimal                                       2633589

 

select b.name, sum(a.value) value
from v$sysstat a, v$statname b
where a.STATISTIC#=b.STATISTIC#
and b.name like '%workarea executions - %'
group by b.name;

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - onepass                                          4712
workarea executions - multipass                                         800
workarea executions - optimal                                    9760345515

 

The result above shows that PGA size is not properly set because number of onepass and multipass are considerable.

4. v$PGA_TARGET_ADVICE– view shows the predicted cache hit-ratio improvement.

select round(pga_target_for_estimate/1024/1024) pga_size_mb
       ,estd_pga_cache_hit_percentage
       ,estd_overalloc_count
from v$pga_target_advice;

—My output

PGA_SIZE_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------- ----------------------------- --------------------
       1024                            96                38530
       2048                            97                28413
       4096                            97                19187
       6144                            97                17741
       8192                           100                16326
       9830                           100                15458
      11469                           100                15049
      13107                           100                14336
      14746                           100                13136
      16384                           100                10570
      24576                           100                    0
      32768                           100                    0
      49152                           100                    0
      65536                           100                    0

Setting PGA to 24576MB can eliminate over allocation count and gain maximum hit ratio.

To change PGA size:

alter system set pga_aggregate_target=24576m;

Filed under DB Tagged with ,

NO_UNNEST hint, query optimization

May 27, 2011 1 Comment

There are multiple useful hints, that are changing Oracle execution plan for some SQL statement and reduces  the cost. One of them is NO_UNNEST hint.

First of all, let’s discuss briefly what UNNEST hint does.

UNNEST hint “Instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.”

For example, if we have the query with inline view like that:

select *
from   hr.employees outer
where  outer.salary > (
              select avg(inner.salary)
              from   hr.employees inner
              where  inner.department_id = outer.department_id
       );

What UNNEST hint actually does, is the following:

select *
from   hr.employees outer,
       (
              select department_id, avg(salary) avg_sal
              from   hr.employees
              group by department_id
       )      inner
Where
       outer.department_id = inner.department_id
and    outer.salary > inner.avg_sal;

 

Now, when we already know what UNNEST hint does. Let’s see how optimizes the sql statement its opposite NO_UNNEST hint.

–Original SQL Statement

SELECT /*+ index(v1.table1 table1_IX1) */
      v1.col1,
      v1.col2,
      v1.col3,
      v1.col4,
      v1.col5
FROM VIEW1 v1
WHERE v1.code = :B1
AND v1.ID = NVL(NULL, v1.ID)
AND v1.ID IN
             (SELECT v2.sid
              FROM VIEW2 v2                 
              WHERE 'N' = 'N'
              AND v2.Key1 = NVL(NULL, Key1)
              AND NVL(NULL, Active_Flag) = Active_Flag
              AND NVL(NULL, Inform_Flag) = Inform_Flag
              OR('Y' = 'Y' AND :b2 = KEY1 AND Active_Flag = 'Y')
             )

–Explain Plan

--------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |     1 |   244
|   1 |  HASH JOIN SEMI                   |                     |     1 |   244
|   2 |   NESTED LOOPS OUTER              |                     |     1 |   231
|   3 |    TABLE ACCESS BY INDEX ROWID    | TABLE1              |     1 |   110
|   4 |     INDEX RANGE SCAN              | TABLE1_IX1          |     2 |
|   5 |    TABLE ACCESS BY INDEX ROWID    | TABLE2              |     1 |   121
|   6 |     INDEX UNIQUE SCAN             | TABLE2_PK           |     1 |
|   7 |   VIEW                            | VW_NSO_1            |     2 |    26
|   8 |    CONCATENATION                  |                     |       |
|   9 |     TABLE ACCESS BY INDEX ROWID   | TABLE3              |     1 |    21
|  10 |      NESTED LOOPS                 |                     |     1 |    49
|  11 |       NESTED LOOPS                |                     |     1 |    28
|  12 |        TABLE ACCESS BY INDEX ROWID| TABLE4              |     1 |    18
|  13 |         INDEX UNIQUE SCAN         | TABLE4_PK           |     1 |
|  14 |        TABLE ACCESS BY INDEX ROWID| TABLE5              |     1 |    10
|  15 |         INDEX RANGE SCAN          | TABLE5_PK           |     1 |
|  16 |       INDEX RANGE SCAN            | TABLE1_IX1          |     1 |
|  17 |     TABLE ACCESS BY INDEX ROWID   | TABLE5              |     1 |    10
|  18 |      NESTED LOOPS                 |                     |     1 |    49
|  19 |       NESTED LOOPS                |                     |     1 |    39
|  20 |        TABLE ACCESS FULL          | TABLE3              |  4559 | 95739
|  21 |        TABLE ACCESS BY INDEX ROWID| TABLE4              |     1 |    18
|  22 |         INDEX UNIQUE SCAN         | TABLE4_PK           |     1 |
|  23 |       INDEX RANGE SCAN            | TABLE5_PK           |     1 |
--------------------------------------------------------------------------------

COST IS:  9192

–With hint

SELECT /*+ index(v1.table1 table1_IX1) NO_UNNEST(@sq1)*/
      v1.col1,
      v1.col2,
      v1.col3,
      v1.col4,
      v1.col5
FROM VIEW1 v1
WHERE v1.code = :B1
AND v1.ID = NVL(NULL, v1.ID)
AND v1.ID IN
             (SELECT /*+ qb_name(sq1)*/v2.sid
              FROM VIEW2 v2
              WHERE 'N' = 'N'
              AND v2.Key1 = NVL(NULL, Key1)
              AND NVL(NULL, Active_Flag) = Active_Flag
              AND NVL(NULL, Inform_Flag) = Inform_Flag
              OR('Y' = 'Y' AND :b2 = KEY1 AND Active_Flag = 'Y')
             )

Note: I used query naming qb_name.

–Explain plan

--------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |     1 |   231 |
|   1 |  FILTER                         |                      |       |       |
|   2 |   NESTED LOOPS OUTER            |                      |     1 |   231 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | TABLE1               |     1 |   110 |
|   4 |     INDEX RANGE SCAN            | TABLE1_IX1           |     2 |       |
|   5 |    TABLE ACCESS BY INDEX ROWID  | TABLE2               |     1 |   121 |
|   6 |     INDEX UNIQUE SCAN           | TABLE2_PK            |     1 |       |
|   7 |   TABLE ACCESS BY INDEX ROWID   | TABLE5               |     1 |    10 |
|   8 |    NESTED LOOPS                 |                      |     1 |    49 |
|   9 |     NESTED LOOPS                |                      |     1 |    39 |
|  10 |      TABLE ACCESS BY INDEX ROWID| TABLE3               |     3 |    63 |
|  11 |       INDEX RANGE SCAN          | TABLE3_IX1           |     3 |       |
|  12 |      TABLE ACCESS BY INDEX ROWID| TABLE4               |     1 |    18 |
|  13 |       INDEX UNIQUE SCAN         | TABLE4_PK            |     1 |       |
|  14 |     INDEX RANGE SCAN            | TABLE5_PK            |     1 |       |

COST IS: 16

I hope it was helpful…

Filed under DB Tagged with ,

ExtJs 4 Tree rootVisible false problem

May 21, 2011 3 Comments

I was building Ext.tree.Panel like this:


Ext.create('Ext.tree.Panel', {
    rootVisible : false,
    root   : {
        text    : 'Menu',
        children : [{
            text : 'Item 1',
            children : [{
                 text : 'Item 1.1',
                 leaf  : true
            },{
                 text : 'Item 1.2',
                 leaf  : true
            }]
        }, {
            text : 'Item 2',
            children : [{
                 text : 'Item 2.1',
                 leaf  : true
            }]
        }]
    }
});

And while instantiating I got this error: “Cannot read property ‘elements’ of undefined”. I searched this error w/o any luck. Then after looking demos and thinking a little bit I got the solution: root item must have expanded : true. So code must look like this:


Ext.create('Ext.tree.Panel', {
    rootVisible : false,
    root   : {
        text    : 'Menu',
        expanded : true,
        children : [{
            text : 'Item 1',
            children : [{
                 text : 'Item 1.1',
                 leaf  : true
            },{
                 text : 'Item 1.2',
                 leaf  : true
            }]
        }, {
            text : 'Item 2',
            children : [{
                 text : 'Item 2.1',
                 leaf  : true
            }]
        }]
    }
});

I hope this post will save your minimum 15 minutes 🙂

Filed under extjs, JavaScript, Programming

Dbink hangs; enq: DX – contention

May 18, 2011 Leave a comment

When I was monitoring v$session view, I’ve noticed the following event “enq: DX – contention”.

I want to share the information, which I’ve collected about this problem and also provide you by my solution.

This problem happens, when a query includes the references for remote objects via database link as well as its local function, that doesn’t exist at remote site.

Look at the following query:

select *
from  employees@db_link
where local_package.local_function(var1)=1

 

In this example, data is queried from employees table via “db_link” database link and there is also used the function “local_function”. That is causing “enq: DX – contention” event.

This case can make blocking lock on remote database.

As many administrators are saying this is an Oracle bug.

My solution is very simple and might be very helpful for many administrators:

I recommend you to make the following changes into your application, with the help of developers.

Try to migrate all remote objects that are used in this SQL into local database , if it is time and resource consuming then try the inverse-> migrate Local functions that are used in this SQL from local database to remote database.

So my solutions for the example shown above would be the followings:

1. Migrate local_package.local_function to remote database

or

2.Migrate employees table to local database.

I prefer the first, if the local function do not use other local functions.

I hope, it was helpful for many DBAs.

Filed under DB Tagged with ,

Install Oracle ODBC on Solaris(Unix)_Connect Oracle_to_SQL_Server

May 17, 2011 Leave a comment

I’ve searched a lot of software for to connect Oracle and SQL Server databases. There are a lot of software, but I couldn’t use any of them except DataDirect ODBC. Unfortunately, this soft is not free. I couldn’t find any free software that works on unix.

Let’s start installation.

1. Download DataDirect ODBC driver from here.

Choose your appropriate system(64-bit UNIX/Linux, 32-bit UNIX/Linux,…). After you choose it, there will appear Registration region. Fill it with your information and when you see the following section, do as it is shown on the picture:

DataDirect Registration Region

I chose Oracle, SQL Server and Solaris SPARC…Because I am trying to connect Oracle Database to SQL Server Database, note Oracle Database is installed on Solaris SPARC.

2.  Extract Downloaded file:

# gunzip PROGRESS_DATADIRECT_CONNECT_ODBC_6.1.1_SOL_32.tar.Z

# tar -xvf PROGRESS_DATADIRECT_CONNECT_ODBC_6.1.1_SOL_32.tar

3. Go to the extracted file and run the following:

# ./unixmi.ksh
Installation……………………………………………………………………………

Solaris
Is this the current operating system on your machine (Y/N) ? [Y] PRESS ENTER

READ THE LICENSE TERMS(PRESS ENTER to read whole content)

…

EULA - Connect Products (revised 2/21/11)

Enter YES to accept the above agreement : YES

…

In the Key field, enter either EVAL or the Key provided.

Fill it with your registration information. I am writing EVAL because I just want to try it.

Name          :Mariam
Company       :JSC Bank of Georgia
Serial Number :EVAL
Key           :EVAL

…

1.   Drivers for All Supported Databases
2.   Single Driver
Please enter an option [1]: 2

1.   DB2 Wire Protocol
2.   dBASE
3.   Greenplum Wire Protocol
4.   Informix Wire Protocol
5.   MySQL Wire Protocol
6.   Oracle Wire Protocol
7.   PostgreSQL Wire Protocol
8.   SQL Server Wire Protocol
9.   Sybase Wire Protocol
10.  Teradata
11.  Text
12.  Informix (client)
13.  Oracle (client)
14.  SQL Server Legacy Wire Protocol

Please enter driver option: 8

To change this information, enter C. Otherwise, press Enter to continue. : PRESS ENTER
Enter the full path to the temporary install directory.[/tmp]:PRESS ENTER
Enter the full path to the install directory.[/opt/Progress/DataDirect/Connect_for_ODBC_61]: PRESS ENTER
Would you like to install another product (Y/N) ? [Y] N

…

Installation ended successfully.

3. Configure odbc.ini file, located in /opt/Progress/DataDirect/Connect_for_ODBC_61.

There will be written some information when you open this file, but the following entries are enough:

[MS_SQLServer2005]
Driver=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib/ivsqls25.so
Description=SQL Server ODBC driver
Database=DB_NAME
Address=10.0.0.88,1433
Quoteld=No

Note I have changed the following values:

MS_SQLServer2000 – is your desired name, name it as you wish.(Will be used later)
Description – Just a descriptive text, write what you wish.
Database – Name of the SQL Server Database.
Address – Ip address of the server on which SQL Server Database is installed. 1433 is a default port for SQL Server.  Ensure that this port is opened on  firewall(Or you will not  be able to connect SQL Server)

4. Tnsnames.ora

DB_NAME_ALIAS=  
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.11)(PORT=1522))
     (CONNECT_DATA=(sid=DB_NAME))
     (HS=OK)
  )

Where,

DB_NAME_ALIAS – is your desired name.
DB_NAME – Is SQL Server Database name
1522 – Is any free port, If you have multiple listeners on the server(where Oracle is installed) choose any port that is available.
10.0.0.11 – Ip of the server, where Oracle is installed

5. listener.ora

LISTENER_DB_NAME_ALIAS = 
  (DESCRIPTION_LIST=
     (DESCRIPTION =
       (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = tcp)(HOST = 10.0.0.11)(PORT = 1522))
       )
     ) 
  ) 

SID_LIST_LISTENER_DB_NAME_ALIAS=
   (SID_LIST=
     (SID_DESC=
     (SID_NAME=DB_NAME_ALIAS)
     (ORACLE_HOME=/u0/app/oracle/10.2.0/db)
     (PROGRAM= hsodbc)
     (ENVS="LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib:/u0/app/oracle/10.2.0/db/bin")
    )
)

Underlined entry is very important, or you will get  ORA-28500 error.

6.  Go to  the following directory: $ORACLE_HOME/hs/admin

There will be the file called inithsodbc.ora, copy that file and rename it like this: initDB_NAME_ALIAS.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MS_SQLServer2005
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = hsMSSqlMari.trc
HS_FDS_SHAREABLE_NAME=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib/libodbc.so
set ODBCINI=/opt/Progress/DataDirect/Connect_for_ODBC_61/odbc.ini
#
# Environment variables required for the non-Oracle system
#

7.  Start the listener.

Before starting listener, ensure that ODBC lib directory is specified in the shared library environment variable:

LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib:/u0/app/oracle/10.2.0/db/bin

After that we can start listener:

$ lsnrctl start LISTENER_DB_NAME_ALIAS

8. Create database link:

SQL> create database link LINK_NAME     
     connect to “username”     
     identified by “password”     
     using ‘DB_NAME_ALIAS’;

Where,

username– is the user in SQL Server Database.
password – is its password

Do not forget to use quotas(“”).

9. Check if it works

SQL> select * from dual@LINK_NAME;

D
---
X

That’s it Smile

Filed under DB, Unix Tagged with , ,

Install SQL Developer on Open Suse

May 15, 2011 Leave a comment

This post will be very useful for whom is new in Linux and is trying to install SQL Developer.

1. Donwload SQL Developer installation from Oracle SQL Developer RPM for Linux.

2. Install that rpm package:

> su
Password:

# rpm -Uhv sqldeveloper-3.0.04.34-1.noarch.rpm

3. Download JDK from here.

Go to the directory, where you have downloaded JDK:

> su
Password:

Make it executable:

#chmod a+x jdk-6u25-linux-i586-rpm.bin

Run that file:

# ./jdk-6u25-linux-i586-rpm.bin

4. Add/change the following environment variables:

JAVA_HOME=$JAVA_HOME:/usr/java/jdk1.6.0_25/
PATH=$PATH:/opt/sqldeveloper/

Note: That variables should be added/edited in .profile, if you don’t want to set them every time you log on.

5. Run SQL Developer:

# sqldeveloper

If you have some problems with running it, simply exit from the terminal and reconnect it and try above steps again. Or check environment variables if they are correctly set. This may help.

 

 

 

Filed under DB, Linux Tagged with , ,

Open Suse 11.4 Wireless is connected(active) but there is no internet [SOLVED]

May 13, 2011 Leave a comment

In this post, I am going to provide you by one of the solution which helped me…

I simply renamed resolv.conf file which is located in /etc:

:~> su
Password:

# cd /etc
# mv resolv.conf resolv111.conf

That’s it.

Let’s expalin a little bit more:

What is resolv.conf?

Resolv.conf is a resolver configuration file. This file defines which Doman Name Servers to use.

See the content of this file:

#cat resolv.conf

search domain.com site
nameserver 192.168.1.111
nameserver 192.168.1.143

Means-> when you type http://www.google.com It will go to the first DNS server 192.168.1.111 and ask what IP does this google.com corresponds. If this nameserver doesn’t know the answer, 192.168.1.143 will be next nameserver to be asked.

 

If you know the correct name servers just modify this file with correct entries(instead of renaming it).

But if it doesn’t work… Run the Network Settings, in Global Options tab, in the  IPv6 Protocol Settings section, uncheck the box Enable IPv6.

 

Filed under Linux Tagged with ,

Install Oracle 10g on Open Suse 11.4

May 10, 2011 1 Comment

1. Download Oracle Databse 10g software from oracle site, from here.

2. Make sure that you have the following packages installed:

# rpm -q gcc gcc-c++ glibc libaio libaio-devel make openmotif openmotif-libs

My output:

gcc-4.5-19.1.i586
gcc-c++-4.5-19.1.i586
glibc-2.11.3-12.15.1.i686
libaio-0.3.107-7.1.i586
libaio-devel-0.3.107-7.1.i586
make-3.82-140.1.i586
openmotif-2.3.2-5.1.i586
openmotif-libs-2.3.2-5.1.i586

If any of them is not installed  run the following command for each of the uninstalled package. For example:
# zypper install gcc
And install the following package:
# zypper install libstdc++33
3.Create necessary groups, user and change the password for the newly created user:
# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle
4. If the oracle user’s home directory doesn’t exist, create it:
# mkdir /home/oracle
# chown -R oracle:dba /home/oracle
5. Let’s create the directory, where Oracle software should be installed:
# mkdir -p /u01/app/oracle/product/10.2.0/db_1
# chown -R oracle:oinstall /u01
6. Setting ORACLE_BASE, ORACLE_HOME environement variables:
# vi /etc/profile.d/oracle.sh
——————————————And add the following entries
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
And also:
# vi /etc/sysconfig/oracle
——————————————And add the following entries
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
Note that the default value for ORACLE_BASE is /opt/oracle
7. Connect as an oracle user and unzip downloaded file:
# su – oracle
oracle@mariamsuse:~> unzip 10201_database_linux32.zip
If you have already unzipped this file by another user just change the permissions, like:
# chown oracle:oinstall 10201_database_linux32.zip
and then extract.
8. Edit the file database/install/oraparam.ini 
[Certified Versions]
Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2,SuSE-11

 

Note: At the end I have added SuSE-11

9. Go to the folder ……database/ and run:
# su – oracle
Password: 
           
oracle@mariamsuse:~> ./runInstaller
If something doesn’t work… exit from the existing terminal(command line) and reconnect.
Oracle Universal Installer should be started…
“During installation, there are 1 warning and 4 Waiting for verification. Just check all of them and proceed to installation.”

Filed under DB, Linux Tagged with ,

Oracle Latch Library Cache Troubleshooting

May 3, 2011 Leave a comment

What are latches?

Latches are serialization mechanisms that protect areas of Oracle’s shared memory(the SGA). In simple terms latches prevent two processes from simultaneously  updating-and possible corrupting-the same area of the SGA.

The total number of latches in the system is defined by _kgl_latch_count parameter.(The maximum value of this parameter is 66). It cannot be seen in v$parameter. To change its value you should add it in init.ora file.

The major cause of library cache latch contention is the failure to use bind variables.

Note: The only changes that you can do at Oracle database level is to increase total number of latches(_kgl_latch_count parameter). Your aim is to find problematic SQLs that may have failure in using bind variables, or are badly written(junk) SQLs…

For to find possible problematic SQL statements we can do the following:

select p1text,p1raw
from v$session_wait
where EVENT='latch: library cache'

 

where value of p1text must be “address”.

Now let’s get the latch address:

select CHILD#
from v$latch_children
where ADDR=p1raw
 
and then query the following:
 
select *
from v$sqlarea
where CHILD_LATCH=child#
order by EXECUTIONS desc

 

Top of the result will be all SQL statements that have high execution number, accordingly they tend to be problematic statements, which should be optimized.

The following statements will help you to distinguish problematic and non-problematic SQLs.

These are several ways for to find them:

1. SQLs with high executions

select *
from v$db_object_cache
where CHILD_LATCH=child#
order by EXECUTIONS


2. SQLs with high lock counts

select *
from v$db_object_cache
where CHILD_LATCH=child#
order by lock


3. SQLs with high pin counts

select *
from v$db_object_cache
where CHILD_LATCH=child#
order by pins

Filed under DB Tagged with ,