Thinking Out Loud

July 27, 2012

Notes on Cloud Control 12c BP1 for Solaris 64 Sparc Installation

Filed under: Cloud Control — mdinh @ 5:57 am

This will not be a duplication of the many step by step guides already out there.

The purpose is to provide references to aid in installation.

Metalink Notes:

Announcing Enterprise Manager Cloud Control 12c Release 12.1.0.1 Bundle Patch 1(BP1) and 12.1.0.2 Plug-ins [ID 1395505.1]

Mandatory Enterprise Manager Cloud Control 12c Release 12.1.0.1 Bundle Patch 1 (BP1) for all available platforms [ID 1430518.1]

How to Install Enterprise Manager Cloud Control 12.1.0.1 (12c) on Linux [ID 1359176.1]

EMDIAG Master Index [ID 421053.1]

How to Run the RDA against a Cloud Control Installation Release 12c [ID 1370739.1]

12c Cloud Control EM CLI: How to Install and Setup 12c emcli (Enterprise Manager Command Line Interface)? [ID 1369864.1]

12c Cloud Control: Overview of the EMCTL Options Available for Managing the 12c OMS [ID 1383533.1]

12c Cloud Control: Overview of the EMCTL Options Available for Managing the Cloud Control Agent [ID 1387667.1]

11g Grid Control: Details of the Directory Structure and Commonly Used Locations in a 11g OMS Installation [ID 1276554.1]

Understanding the Enterprise Manager Directory Structure

Step 1:

Review the documentation: A Overview of EM Prerequisite Kit Utility

Database will be created using OMF (want/need the practice using OMF).

Table A-7 Setting Variable Initialization Parameters

> cat initgrid01.ora
*.audit_file_dest=’/u01/app/oracle/admin/audit’
*.audit_sys_operations=FALSE
*.audit_trail=’NONE’
*.compatible=’11.2.0.3′
*.db_block_size=8192
*.db_create_file_dest=’/oracle/oradata’
*.db_name=’GRID01′
*.db_recovery_file_dest=’/oracle/oradata/flashrecovery’
*.db_recovery_file_dest_size=16G
*.db_unique_name=’SAN_GRID01′
*.deferred_segment_creation=FALSE
*.diagnostic_dest=’/u01/app/oracle’
*.job_queue_processes=20
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))’
*.log_archive_max_processes=2
*.log_buffer=4194304
*.open_cursors=400
*.pga_aggregate_target=2G
*.processes=500
*.recyclebin=’OFF’
*.remote_login_passwordfile=’EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=4G
*.sga_target=4G
*.shared_pool_size=1G
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’

Important: create spfile from pfile before create database.

Create spfile:
create spfile from pfile=’initgrid01.ora’;

Create Database:
CREATE DATABASE
CHARACTER SET AL32UTF8
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
sysaux DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
LOGFILE GROUP 1 SIZE 300M,GROUP 2 SIZE 300M,GROUP 3 SIZE 300M,GROUP 4 SIZE 300M,GROUP 5 SIZE 300M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
DEFAULT TABLESPACE users DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
UNDO TABLESPACE undotbs DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K;

Create Catalog:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catio.sql
@?/rdbms/admin/catoctk.sql
@?/rdbms/admin/userlock.sql
@?/rdbms/admin/utldtree.sql

Archive Log Mode:
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
conn system
@?/sqlplus/admin/pupbld.sql

Disable stats collection and password expiration:
execute dbms_auto_task_admin.disable(‘auto optimizer stats collection’,null,null);
alter profile default limit password_life_time unlimited;

Example Create Database Output:
oracle@gridcontrol:grid01:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs
> sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 23 09:00:44 2012

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

Connected to an idle instance.

SYS@grid01> create spfile from pfile=’initgrid01.ora’;

File created.

SYS@grid01> startup nomount
ORACLE instance started.

Total System Global Area 4277059584 bytes
Fixed Size 2166160 bytes
Variable Size 1107300976 bytes
Database Buffers 3154116608 bytes
Redo Buffers 13475840 bytes
SYS@grid01> CREATE DATABASE
2 CHARACTER SET AL32UTF8
EXTENT MANAGEMENT LOCAL
3 4 DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
sysaux DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
5 6 LOGFILE GROUP 1 SIZE 300M,GROUP 2 SIZE 300M,GROUP 3 SIZE 300M,GROUP 4 SIZE 300M,GROUP 5 SIZE 300M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
7 8 DEFAULT TABLESPACE users DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
9 UNDO TABLESPACE undotbs DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K;

Database created.

SYS@grid01>

Step 2:

Install WLS using current jdk 1.6.0_27 versus standard jdk 1.6.v24 install.

/usr/jdk/instances/jdk1.6.0_27/bin/java -d64 -jar /home/oracle/ora_stage/grid_stage/wls/wls1035_generic.jar

Step 3:

After WLS installation, please review and follow How to Install Cloud Control 12c on Linux.

Installation Information:
> cat /var/opt/oracle/oragchomelist
/u01/app/oracle/product/cloud12c/middleware/oms
/u01/app/oracle/product/cloud12c/middleware/agent/core/12.1.0.1.0:/u01/app/oracle/product/cloud12c/middleware/agent/agent_inst

Step 4:

Optional

Unlock console and upload:
cd $OMS_HOME/bin
./emctl config emkey -remove_from_repos
./emctl secure unlock -console -upload
./emctl stop oms -all
./emctl start oms
./emctl status oms -details

Unsecure agent:
cd $AGENT_HOME
./emctl unsecure agent

Step 5:

Configure environment.

Environment Variables:

export ORATAB=/var/opt/oracle/oratab
export ORAENV_ASK=’NO’
export ORACLE_UNQNAME=savvis_grid01
export JAVA_HOME=/usr/jdk/instances/jdk1.6.0_27
export BASEPATH=$JAVA_HOME/bin:/opt/oracle/bin:/usr/sbin:/usr/ccs/bin:/usr/bin/X11:/usr/local/bin:/etc:/usr/bin
export LD_LIBRARY_PATH=/lib:/usr/lib;
export ORACLE_BASE=/u01/app/oracle
export MW_HOME=$ORACLE_BASE/product/cloud12c/middleware
export WLS_HOME=$MW_HOME/wlserver_10.3
export AGENT_BASE=$MW_HOME/agent
export AGENT_HOME=$AGENT_BASE/core/12.1.0.1.0
export AGENT_INSTANCE_HOME=$AGENT_BASE/agent_inst
export PLUGIN_HOME=$AGENT_BASE/plugins
export SBIN_HOME=$AGENT_BASE/sbin
export OMS_HOME=$MW_HOME/oms
export EM_INSTANCE_NAME=EMGC_OMS1
export EM_INSTANCE_BASE=$MW_HOME/gc_inst
export EM_INSTANCE_HOME=$EM_INSTANCE_BASE/em/$EM_INSTANCE_NAME
export PATH=$BASEPATH

/var/opt/oracle/oratab

grid01:/u01/app/oracle/product/11.2.0.3/dbhome_1:Y
oms:/u01/app/oracle/product/cloud12c/middleware/oms:N
agent:/u01/app/oracle/product/cloud12c/middleware/agent/agent_inst:N

Source Environments:

> export ORACLE_SID=grid01
> . oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1 is /u01/app/oracle

> export ORACLE_SID=oms
> . oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/cloud12c/middleware/oms is /u01/app/oracle

> export ORACLE_SID=agent
> . oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/cloud12c/middleware/agent/core/12.1.0.1.0 is /u01/app/oracle

July 19, 2012

Know Filter Data

Filed under: 11g,oracle,performance — mdinh @ 2:12 am

I got a request on how to improve performance for table update with 33,617,240 rows running for 2.5 hours as shown:

UPDATE a
SET a.status =
(CASE WHEN a.dt1 IS NOT NULL THEN ‘PROCESSED’ WHEN a.dt2 IS NULL THEN NULL END);

32935613 rows updated.
Elapsed: 02:23:39.85

As written, 32,935,613 of 33,617,240 rows updated.

This is a good demonstration on how filtering early and understanding the data is a HUGE benefit.

If status is already ‘PROCESSED’, then is it necessary to update?
select count(*) from a where dt1 IS NOT NULL and status<>’PROCESSED’;

COUNT(*)
———-
14032

If status is already NULL, then is it necessary to update?
select count(*) from a where dt2 IS NULL and status IS NOT NULL;

COUNT(*)
———-
572

The answer to both questions is NO and we don’t want to peform blanket update.

The SQL can be written as:

UPDATE (
SELECT /*+ PARALLEL(a,4) FULL(a) */
status,
(CASE
WHEN dt1 IS NOT NULL THEN ‘PROCESSED’
WHEN dt2 IS NULL THEN NULL
END) new_status
FROM a
WHERE (dt1 IS NOT NULL AND status <> ‘PROCESSED’) OR (dt2 IS NULL AND status IS NOT NULL)
)
SET status=new_status;

14604 rows updated.
Elapsed: 00:04:43.04

The result is only 14,604 versus 32,935,613 rows updated.
Huge difference in performnace.

Without hints: 47s direct path read + 28s CPU
With hints: 17s direct path read + 13s CPU

July 10, 2012

dbms_stats preference

Filed under: 11g,oracle — mdinh @ 2:33 am

Notes for how to set and get preferences from dbms_stats.

TABLE Level:

exec DBMS_STATS.SET_TABLE_PREFS (USER,'FCT_TABLE,'INCREMENTAL','FALSE');

SELECT 
  owner, table_name,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_tables
WHERE table_name like 'FCT%'
ORDER BY owner, table_name;

Note: Use the DBA view versus the above for better performance

select * from DBA_TAB_STAT_PREFS;

SCHEMA Level:

exec DBMS_STATS.SET_SCHEMA_PREFS (USER,'STALE_PERCENT','8');

SELECT 
  username,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incremental,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') granularity,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') stale_percent,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') estimate_percent,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_users
WHERE username like '%WH'
ORDER BY username;

DATABASE Level:

exec DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

SELECT 
  DBMS_STATS.get_prefs(pname=>'INCREMENTAL') incremental,
  DBMS_STATS.get_prefs(pname=>'GRANULARITY') granularity,
  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT') publish,
  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT') estimate_percent,
  DBMS_STATS.get_prefs(pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dual;

Reference: DBMS_STATS

Blog at WordPress.com.