Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Tuesday, November 29, 2011

Logging in PL/SQL

Whilst tracking a PL/SQL problem with some code utilising excessive TEMP tablespace, I came upon this article for logging real-time in PL/SQL using DBMS_PIPES.
This looks perfect for real time interface monitoring:

Saturday, November 26, 2011

Check if Hyperthreading is enabled in HP-UX 11iv3

UPDATED: Inaccessible links to HP.com have been removed.  I couldn't find the original PDF docs anywhere online any more, so I've just removed the links.

You should note that HP-UX 11iv3 enables the default PSET setting for HT by default, but you may still need to enable the firmware setting.
You should also note that HP-UX 11iv2 does not support HT.

All the checks below should return 1 (for enabled).

Check HT is supported in your chipset:
$ getconf _SC_HT_CAPABLE

Check the firmware setting is enabled:
$ getconf _SC_HT_ENABLED

Check the default PSET has HT enabled:
$ kctune -v -q lcpu_attr

Enjoy some HT goodness.

Sunday, November 20, 2011

Oracle Tracing & Interpreting Traces - Notes

Oracle Trace Details; abstracted from MYOS Note ID 376442.1 and added flair.

This section details various ways of actually getting an Oracle session level trace file, interpreting the trace file and useful links to MYOS notes about the trace data:

/* Oracle 10g+ trace session using DBMS_MONITOR includes BINDS and WAITS */
/* However, this is limited to one session only */
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id =>1181,
                                       serial_num =>7218,
                                       waits      => TRUE,
                                       binds      => TRUE);

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 1181,

                                        serial_num => 7218);

/* Oracle 9i Trace excludes BINDS and WAITS */
/* Again, limited to one session only */
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(16,38779,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(16,38779,FALSE);


/ * Trace your own session with BINDS and WAITS */
ALTER SESSION SET SQL_TRACE=TRUE;
or
ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'';

/* Trace using OraDebug with BINDS and WAITS */
connect / as sysdba
oradebug setorapid 9834  << Insert your Oracle PID here.
oradebug unlimit
oradebug event 10046 trace name context forever,level 12


/* And switch it off again */
oradebug event 10046 trace name context off



/* Logon trigger trace for Oracle 9i+ */
/* This is the best way to trace when using Oracle Forms because it allows for multiple spontaneous sessions.
I can also confirm that this traces sessions executed on a database link */
/* First you must grant ALTER SESSION to the user */
GRANT ALTER SESSION TO <USERNAME> ;

/ * Now create the trigger */
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/


/* Simply disable the trigger when done */
ALTER TRIGGER SYS.SET_TRACE DISABLE;


/* List non-system users, very useful when attempting to trace a session */
SELECT username,sid,serial#,status,module
   FROM v$session
 WHERE username is not null
      AND username <> 'SYS'
 ORDER BY username;


/* Enable the trace on ALL current sessions of a specific user in the system. */
set serveroutput on size 10000;
DECLARE
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Enabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => c1_row.sid, serial_num => c1_row.serial# ,waits => TRUE, binds => TRUE);
DBMS_OUTPUT.PUT_LINE('Tracing for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
select username,sql_trace from v$session order by username;


/* Disable the trace on ALL current sessions of a specific user in the system. */
set serveroutput on size 10000;
DECLARE
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Disabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => c1_row.sid, serial_num => c1_row.serial#);
DBMS_OUTPUT.PUT_LINE('Tracing disabled for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
select username,sql_trace from v$session order by username;


/* Run TKProf as sysdba with EXPLAIN plan included */
/* See TKProf Interpretation (9i and above) [ID 760786.1] */
tkprof <tracefile>.trc /tmp/<tracefile>.trc.tk explain=\" / as sysdba\"

/* TKProf sorting by highest elapsed time */
tkprof <tracefile>.trc /tmp/<tracefile>.trc.tk sort=fchela,exeela,prsela

Once you have your trace file and you have run it through TKProf and are still struggling, you may need to read it raw.
Check out the following MYOS articles:
"Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1]".

The excellent but slightly unheard of "Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]".

Friday, November 11, 2011

SAP Language Pack Enabling for Login in R/3 Enterprise 4.7

In an SAP system you can see what language packs are installed using transaction SMLT.
Sometimes however, you may find that you are unable to log into the system using one of the languages that are installed.  You get prompted to select a valid language.
This is usually due to a miss-configuration of the zcsa/installed_languages profile parameter.

You can check the system language configuration using the report RSCPINST.
You should note that the zcsa/installed_languages parameter should be set in the DEFAULT profile *only*.

Some useful SAP notes on language packs:

73606 “Supported Languages and Code Pages”
352941 “Languages and Support Packages”
533888 “Example for Language Import and Errors”

Saturday, November 05, 2011

IBM MQ Error - AMQ6118: An internal WebSphere MQ error has occurred (20806013)

In the /var/mqm/errors/AMQERR*.LOG file the following error was visible:

11/01/11 16:20:23 - Process(1408.3) User(mqm) Program(amqrmppa_nd)
AMQ6118: An internal WebSphere MQ error has occurred (20806013)

EXPLANATION:
An error has been detected, and the MQ error recording routine has been called.
ACTION:
Use the standard facilities supplied with your system to record the problem
identifier, and to save the generated output files. Contact your IBM support
center.  Do not discard these files until the problem has been resolved.


There was an FDC file present also in the same directory.
It contained the following information header:
+-----------------------------------------------------------------------------+
|                                                                             |
| WebSphere MQ First Failure Symptom Report                                   |
| =========================================                                   |
|                                                                             |
| Date/Time         :- Tuesday November 01 14:52:07 GMT 2011                  |
| Host Name         :- xxxxxxxx (HP-UX B.11.23)                               |
| PIDS              :- 5724H7208                                              |
| LVLS              :- 6.0.1.0                                                |
| Product Long Name :- WebSphere MQ for HP-UX (Itanium platform)              |
| Vendor            :- IBM                                                    |
| Probe Id          :- XC028018                                               |
| Application Name  :- MQM                                                    |
| Component         :- xcsReleaseMutexSem                                     |
| SCCS Info         :- lib/cs/unix/generic/amqxlfmx.c, 1.147.1.1              |
| Line Number       :- 3229                                                   |
| Build Date        :- Oct 21 2005                                            |
| CMVC level        :- p600-100-051021                                        |
| Build Type        :- IKAP - (Production)                                    |
| UserID            :- 00002440 (mqm)                                         |
| Program Name      :- amqrmppa_nd                                            |
| Addressing mode   :- 64-bit                                                 |
| Process           :- 25271                                                  |
| Thread            :- 3                                                      |
| QueueManager      :- MQINTUX                                                |
| ConnId(1) IPCC    :- 26                                                     |
| Major Errorcode   :- xecL_E_NOT_OWNER                                       |
| Minor Errorcode   :- OK                                                     |
| Probe Type        :- INCORROUT                                              |
| Probe Severity    :- 2                                                      |
| Probe Description :- AMQ6125: An internal WebSphere MQ error has occurred.  |
| FDCSequenceNumber :- 0                                                      |
|                                                                             |
+-----------------------------------------------------------------------------+

Check the “Component” and “Major Errorcode” sections of the header.
I was seeing “xecL_E_NOT_OWNER” for the “xcsReleaseMutexSem” component.

This prompted me to check the semaphores (HP-UX) using command "ipcs -sa".
I could see that there were some semaphores hanging around as the “mqm” user (shown in the header of the FDC file also).
So, clearing them as root and using “ipcrm -s” and the ID from the “ipcs -sa” command output, fixed the issue and prevented the need for a reboot.

The issue was caused by running MQ as the wrong UNIX user (it wasn't supposed to be running as the mqm user in our environment).

Linking Micro Focus Cobol (Server) 5.0 to Oracle

After implementing an Oracle upgrade, you will need to re-link the Micro Focus Cobol runtime environment.
However, after the relink you may need to ensure that the link to the new shared library exists:
After seeing this dump in /var/mfcobol/es/<SERVER>/casdumpa.rec

Load error : file 'librtsora.sl'
error code: 173, pc=0, call=1, seg=0
173     Called program file not found in drive/directory

cd $ORACLE_HOME/lib32
ln -s rtsora_t.sl librtsora.sl



If you're using Itanium, you will be using rtsora_t.so.

Tuesday, November 01, 2011

Oracle Index Rows Per Leaf Block

The query below can be used to obtain the number of rows per index leaf block.
You will need to know the index name, the table name that the index references, the pk column in the index and the object id for the index:

SELECT object_id
from user_objects
WHERE object_name = 'MGMT_VERSIONS_PK';

SELECT rows_per_block, count(*) blocks
FROM (
      SELECT /*+ cursor_sharing_exact
                 dynamic_sampling(0)
                 no_monitoring
                 no_expand
                 index_ffs(serv_inst,ix_serv_inst)
                 noparallel_index(serv_inst,ix_serv_inst)
              */
             sys_op_lbid(349440,              -- << INDEX OBJECT ID HERE
                                 'L',rowid) block_id,
             count(*) rows_per_block
        FROM MGMT_VERSIONS                    -- << TABLE NAME HERE
       WHERE COMPONENT_NAME IS NOT NULL       -- << INDEX COL NAME HERE
      GROUP BY sys_op_lbid(349440,            -- << INDEX OBJECT ID HERE
                           'L',rowid)

     )
GROUP BY rows_per_block;


This is useful for determining sparse index blocks:
http://www.dba-oracle.com/t_sys_op_lbid_index_node_density.htm

It could be combined with this script (http://jonathanlewis.wordpress.com/segment-scans/) which helps to detect full table scans and index fast full scans:

select
       owner,

       object_type,
       object_name,
       obj#,
       subobject_name,

       tablespace_name,
       value scans
from
       V$segment_statistics
where
       statistic_name = 'segment scans'
  and  value != 0
order by owner, value;


(When I get time, I'll combine it and post it here).

Running the first SQL statement, then checking if the table has "high" segment scans would then give a good indication if the index is used frequently for large multi-block operations and is very sparse (block wise) and potentially up for a re-build.