increase session_cached_cursors

Hello all,

We have performance problem on prod DB, and Oracle ADDM advice us increase size of session_cached_cursors parameter.

We decide find reason of problem.

Here you can find statement which you should run where problem appear.

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(3000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);



now we can see result on statement



select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2 desc
/

we can see selects which is reason of performance problem.

In these select please use bind variables.

Gather schema statistic exluding some tables

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
* Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:


Using dbms_stat procedure:

dbms_stat procedure gather statistics for DB object, after gathering them, oracle build right sql_plans and optimize SQL statement.

Example gathering statistic for schema, this statement built SQL text.

select 'execute dbms_stats.gather_table_stats (ownname=>''' || upper(owner)
|| ''', tabname=> ''' || upper(table_name) || ''', degree=>10, cascade=> true);' from DBA_TABLES
where upper(owner) in ('SCOTT') and table_name not in
('TABLE1','TABLE2') order by owner, table_name;

note:
if you have very large tables you can exclude it for quickly gathering statistics

ownname means schema name
degree means that 10 oracle process will start gather information
cascade means that statistics gather for all indexes on tables

Installing Oracle Management Agent 11g with response file

Hello Again,

As you know, we installed Oracle EM Grid control 11g in Linux server 2 day ago, now we are trying install management agent in server where is our DB's

First of all download install packages from:
http://www.oracle.com/technology/software/products/oem/htdocs/agentsoft.html

Our example is for :
Agent Software for 64-bit Platforms,Linux x86-64

1. copy zip files in server

2. unzip Linux_x86_64_Grid_Control_agent_download_11_1_0_1_0.zip file(for example /0 folder)

3. make changes on file additional_agent.rsp located linux_x64/response sub folder.

We should set some init parameters

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
ORACLE_AGENT_HOME_LOCATION=/u1/app/oracle/agent11g(you can change as you wish)
OMS_HOST=OEMserver (host where is installed EM grid 11g)
OMS_PORT=4889
AGENT_REGISTRATION_PASSWORD=****** (Agent registration password, if you set it during Oracle EM installation )

4. execute runinstaller with specified parameters
./runInstaller -silent -responseFile /0/linux_x64/response/additional_agent.rsp


If you have some question please contact me by e-mail: vmantua@gmail.com

Install Grid Control 11g on linux CentOS(32 bit)

Hi All,

I'm writing manual of installation grid control 11g in Linux (32 bit ). Installation of this product not like early versions of grid control, cause of this is that grid control instead of oracle application server is use oracle web logic server now.

Let's Start!

First of all we should install oracle DBMS and create database for repository OEM.

A. Installation DBMS

1. Install rpm’s

Login on server with user root

1.1 run and install rpm’s with command rpm -Uvh

libaio-devel-0.3.106-3.2.i386.rpm

unixODBC-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.x86_64.rpm

1.2 delete old version of rmp ksh

rpm -e ksh-20060214-1.7.i386

1.3 Install new version of ksh: rpm –Uvh pdksh-5.2.14-36.el5.i386.rpm

2. Configuring Linux for Oracle

mkdir -p /u0/app/oracle

groupadd oinstall
groupadd dba
useradd –s /bin/bash –d /u0/app/oracle –g oinstall –G dba oracle

passwd oracle

chown -R oracle:oinstall /u0/app

chmod -R 777 /u0/app 

change /etc/sysctl.conf, add lines:

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

Reboot system

3. Install Oracle software

3.1 Download from http://www.oracle.com/technology/software/products/database/index.html

Installation packages

Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86

Linux_11gR2_database_1of2.zip (1,285,396,902 bytes) (cksum - 2237015228)

Linux_11gR2_database_2of2.zip (995,359,177 bytes) (cksum - 2649514514)

3.2 login on server with user oracle , unzip zip files and run runinstaller

B. Install Oracle Web Logic Server And Grid Control

1. Install Oracle RDMS 11g
Please see part A

2. Install Oracle Web Logic server

2.1 download installation package http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html Oracle WebLogic Server 11gR1 (10.3.2) - Package Installer

2.2 Copy wls1032_linux32.bin file in server and run command chmod a+x *.bin

2.3 Run ./ wls1032_linux32.bin for installation web logic server

3. Create DB , configure listener.ora file and tnsnames.ora file for connecting DB(You can use dbca utility)

4.Change Oracle instance parameters and increase size of UNDO TBS: 

  

   ALTER system SET session_cached_cursors = 200 scope=spfile;

    ALTER system SET log_buffer = 10485760 scope=spfile;

    ALTER system SET processes = 500 scope=spfile;

    ALTER system SET open_cursors = 300 scope=spfile;

   Restart DB and run command :

    ALTER DATABASE DATAFILE ‘/u1/oradata/OEMREP/undotbs01.dbf’ RESIZE 250M;

5. Download Grig control soft from

       

http://www.oracle.com/technology/software/products/oem/htdocs/linuxsoft.html

      

GridControl_11.1.0.1.0_Linux_1of3.zip (1,346,827,162 bytes) (cksum - 4036510904)   

           GridControl_11.1.0.1.0_Linux_2of3.zip (1,504,676,900 bytes) (cksum - 2185370223)   

       GridControl_11.1.0.1.0_Linux_3of3.zip (1,336,577,399 bytes) (cksum - 4010010869)

Copy these files in server unzip and run runinstaller

6. After install

Navigate to the Oracle home of the OMS and run the following command to see a message that confirms that OMS is up and running.

$<OMS_HOME>/bin/emctl status oms

Go to the Oracle home of the Management Agent and run the following command to see a message that confirms that the Management Agent is up and running.

$<AGENT_HOME>/bin/emctl status agent

Run the following command to see a message that confirms that EMD upload completed successfully.

[$<AGENT_HOME>/bin/emctl upload

6.1. Adding ports to firewall rules.

• Don’t forget to open ports on your firewall:

Default Ports to be opened:

Enterprise Manager Upload Http Port	4889
Enterprise Manager Upload Http SSL Port	4900
Enterprise Manager Central Console Http SSL Port	7799
Enterprise Manager Central Console Http Port	7202
Enterprise Manager Central Console Http Port	7788
Node Manager Http SSL Port	7403
Managed Server Http SSL Port	7301
Oracle Management Agent Port	3872
Admin Server Http SSL Port	7101

Login to EM Grid Control console (sysman/password)

SSL – standard approach:
https://hostname.local:7799/em

It’s also possible to avoid SSL connection. To do that, first you must unlock http access:

[oracle@hostname bin]$ ./emctl secure unlock -console
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :******
OMS Console is unlocked. HTTP ports too can be used to access console.
http://hostname.local:7202/em/console/home

Login without SSL (default port 7202):
http:// hostname.local:7202/em

7. Start/stop the environment: On OMS Server

• to start Oracle Enterprise Manager Grid Control services:

$<OMS_HOME>/bin/ emctl start oms –all

• to stop Oracle Enterprise Manager Grid Control services:

$<OMS_HOME>/bin/ emctl stop oms –all

Veritas Netbackup problem for Oracle 11gR2

Problem:

After migration DB in Oracle 11g, we found out problem with backuping DB over Veritas Netbackup.
In Alert log we saw message likes this :
ORA-07445: exception encountered: core dump [strcpy()+16] [SIGSEGV] [ADDR:0x12][PC:0x3E16079190] [Address not mapped to object] []

On oracle metalink we found that:

The latest version of Netbackup is not compatible with 11gR2
we have client version 6.5.2 which was not supported for 11gR2
also you can read it in this article :
Document ID: 337527
RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
URL: http://support.veritas.com/docs/337527


Solution:

We download Veritas Netbackup client patch 6.5.5 , for linux Red hat. you need to download this patches: (You should extract these patches in a same location for avoiding install problem)

NB_CLT_6.5.5_337786.tar
NB_6.5.5.linuxR_x86_337780.tar
NB_JAV_6.5.5_337809.tar

Also you should download this patch:

NB_6.5.5_ET1940073_1_346033.zip(Bug fixing patch for oracle , netbackup version 6.5.5)
install instruction for this bug : http://seer.entsupport.symantec.com/docs/313221.htm


After applying this patch problem resolved

library cache pin

When you try compile package on Oracle DB 10g , compile process may be hang, reason of this is any session which using this package, on this scenario you should see wait event like that library cache pin.
The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

use this select to see which session lock your compile process. run it with sys user!

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'

How Find Large Object in DB

This is small script how oracle developer can find large object in oracle DB for a schema.

script :

select round(sum(s),2),t
from (
select sum(b.bytes)/1024/1024/1024 s, a.table_name t from dba_indexes a, dba_segments b
where b.segment_type in ('INDEX','INDEX PARTITION')
and a.index_name=b.segment_name
and a.owner=&SCHEMA
group by a.table_name

union all

select sum(a.bytes)/1024/1024/1024 s,a.segment_name t from dba_segments a
where a.owner=&SCHEMA
and a.segment_type in ('TABLE','TABLE PARTITION')
group by a.segment_name

union all

select sum(b.bytes)/1024/1024/1024 s,a.table_name t from dba_lobs a, dba_segments b
where a.owner=&SCHEMA
and a.segment_name=b.segment_name
group by a.table_name

)
group by t
order by 1 desc