Using oracle explain plan

1) Make sure the plan table is present
desc plan_table

2) If not, create it
@?/rdbms/admin/utlxplan

3) If the plan_table is old version, drop it then re-create using (2).
drop table plan_table;

4) Run the "explain plan for my_sql_statement"
EXPLAIN PLAN FOR
select * from emp;

5) Query the plan
set linesize 132
SELECT * FROM TABLE( dbms_xplan.display);


Reference:
http://www.orafaq.com/wiki/SQL*Plus_FAQ

Some tips for sqlplus

1) Display current setting
SQL> show linesize
linesize 100

SQL> show all
...


2) Improve formatting
set linesize 120
set pagesize 20
column somestring format a30
column someint format 999,999,999
column somefloat format 999,999,999.99


3) Show database initialization parameters
SQL> set linesize 150
SQL> column NAME_COL_PLUS_SHOW_PARAM format a35
SQL> column VALUE_COL_PLUS_SHOW_PARAM format a35
SQL> show parameters


4) Current definitions
SQL> define
...
SQL> define _EDITOR="vi"


5) Generate HTML
set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off

XAException in WebSphere AS with Oracle DB

Solution:

1) Run 2 scripts in $ORACLE_HOME/javavm/install
initxa.sql
initjvm.sql

2) Grant correct permission to db user
grant select on pending_trans$ to public;
grant select on dba_2pc_pending to public;
grant select on dba_pending_transactions to public;
grant execute on dbms_system to <>;


Reference:
http://www-1.ibm.com/support/docview.wss?uid=swg21215890
http://www-1.ibm.com/support/docview.wss?rs=0&q1=3979190&uid=swg21196663&loc=en_US&cs=utf-8&cc=us&lang=en

Alternatively, delete off the transaction logs
$WAS_HOME/DeploymentManager/tranlog/dmgr/transaction
$WAS_HOME/AppServer/tranlog/$instance/transaction

Using JRockIt Runtime Analyzer

1) Get the development license
http://commerce.bea.com/downloadproduct.jsp?family=JRMC&major=3.0&minor=0&delivery=1&os=All&intent=purchase

2) Place the license in $JROCKIT_HOME/jre/license.bea

3) Start JRA recording from command
cd $JROCKIT_HOME/bin
./jrcmd $PID startjrarecording recordingtime=600 filename=$NAME.`date +%Y%m%d%H%M`.xml

Note: Help can be found by running
./jrcmd $PID help

4) Analyze the result using JRA Analyzer
export DISPLAY=$IP:0
cd $JROCKIT_HOME/bin
./jrmc &


Reference:
http://dev2dev.bea.com/pub/a/2007/12/jrockit-tuning.html?page=3

Change WAS 5.1.x default log level

1) Create a file "commons-logging.properties" in /properties
org.apache.commons.logging.LogFactory=org.apache.commons.logging.impl.LogFactoryImpl
org.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger

2) Edit logging.properties in /jre/lib
Change the line .level = INFO to .level = ERROR|WARN

# For example, set the com.mypackage.myapp logger to only log SEVERE
# messages:
com.mypackage.myapp.level = SEVERE

Oracle on HP-UX - lock SGA

If oracle instance in HP-UX is set to lock sga as below:

ALTER SYSTEM SET lock_sga=TRUE SCOPE=SPFILE;

There will be error:-

SQL> ORA-27126: unable to lock shared memory segment in core
HP-UX Error: 1: Not owner

Solution:
1) Create a file /etc/privgroup with this entry
dba MLOCK RTSCHED RTPRIO

2) Run
# setprivgroup -f /etc/privgroup

3) Check that above is successful
# getprivgrp
global privileges: CHOWN
dba: RTPRIO MLOCK RTSCHED

Reference:
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=729423

Use WebSphere MQ 6 with WLS 9.2 MP1

0) Copy providerutil.jar & fscontext.jar from <MQM_HOME>/java/lib to <DOMAIN_HOME>/lib

1) Create .bindings file

$ jmsAdmin.sh -cfg myConfig.config
def qcf(myQCF) qmanager(myQM) host(hostname) transport(client) port(1414)
def q(myQ) queue(myQ)

=== jmsAdmin.sh ===
JAVA_HOME=[PATH_TO_JAVA]
MQLIB=/opt/mqm/java/lib
CP=$MQLIB

for i in $MQLIB/*.jar
do
CP=$CP:$i
done

$JAVA_HOME/bin/java -classpath $CP com.ibm.mq.jms.admin.JMSAdmin $@

=== myConfig.config ===
INITIAL_CONTEXT_FACTORY=com.sun.jndi.fscontext.RefFSContextFactory
PROVIDER_URL=file:[some_directory]
SECURITY_AUTHENTICATION=none


2) Define JMS module from admin console -> Services -> Messaging -> JMS Modules -> New
- enter a name and leave the rest empty

3) Create new resource type "Foreign Server"
Name: Anyname
JNDI Initial Context Factory: com.sun.jndi.fscontext.RefFSContextFactory
JNDI Connection URL: file:[some_directory]
Default Targeting Enabled: true

4) Add destination & connection factory
Name: [AS PER MDB]
Local JNDI Name: [SAME AS ABOVE]
Local JNDI Name: [SAME AS ABOVE]