Tuesday Jun 12, 2012

Oracle E-Business Suite Tip : SQL Tracing

Issue:

Attempts to enable SQL tracing from concurrent request form fails with error:

Function not available to this responsibility.
Change Responsibilities or contact your System Administrator

Resolution:

Switch responsibility to "System Administrator". Navigate to System -> Profiles, and query for "%Diagnostics% ("Utilities : Diagnostics")". Once found the profile, change its value to "Yes". Restart web browser and try enabling SQL trace again.

Sunday Jan 09, 2011

Oracle 11g : Poor Performance Accessing V$SESSION_FIX_CONTROL

PeopleSoft HCM, Financials/SCM 9.x customers may have to patch their Oracle database server with RDBMS patch 9699654. Rest of the Oracle customers: read the symptoms and decide.

In couple of PeopleSoft deployments it is observed that the following SQL is the top query when all queries are sorted by elapsed time or CPU time. 11.2.0.1.0 is the Oracle database server version.


SELECT VALUE FROM V$SESSION_FIX_CONTROL WHERE BUGNO = :B1 AND SESSION_ID = USERENV('SID')

The target query is being executed thousands of times. The poor performance is due to the lack of a proper index. Here is the explain plan that exhibits the performance issue.

-------------------------------------------------------------------------------
| Id  | Operation	 | Name       | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	      |      1 |	|      1 |00:00:00.02 |
|\*  1 |  FIXED TABLE FULL| X$QKSBGSES |      1 |      1 |      1 |00:00:00.02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("BUGNO_QKSBGSEROW"=:B1 AND
	      "SID_QKSBGSEROW"=USERENV('SID') AND "INST_ID"=USERENV('INSTANCE')))

20 rows selected.

Oracle Corporation accepted this behavior as a bug and agreed to fix in Oracle RDBMS 12.1. Meanwhile an RDBMS patch was made available to the customers running 11.2.0.1 or later. 9699654 is the bug# (Bad performance of V$SESSION_FIX_CONTROL query) - so, Solaris SPARC customers can download the RDBMS patch 9699654 directly from the support web site. Customers on other platforms: please search the bug database and support web site with appropriate keywords.

After applying the RDBMS patch 9699654, the optimizer was using an index and the query performance was improved as expected. Also the target SQL query was no longer the top SQL - in fact, no references to this particular query were found in the AWR report. The new explain plan is shown below.

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |      1 |        |      1 |00:00:00.01 |
|\*  1 |  FIXED TABLE FIXED INDEX| X$QKSBGSES (ind:1) |      1 |      1 |      1 |00:00:00.01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("BUGNO_QKSBGSEROW"=:B1 AND "SID_QKSBGSEROW"=USERENV('SID') AND
              "INST_ID"=USERENV('INSTANCE')))

20 rows selected.

Sunday Feb 01, 2009

Workaround to "eject of cdrom /dev/dsk/cxtxdxsx failed" Error on SXCE b106

Symptom

On Solaris Express Community Edition build 106, eject(1) fails intermittently (esp. when inserted the blank media) with the error: eject of cdrom /dev/dsk/cxtxdxsx failed.


# cat /etc/release
                  Solaris Express Community Edition snv_106 X86
           Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
                        Use is subject to license terms.
                            Assembled 13 January 2009

# eject
eject of cdrom /dev/dsk/c1t0d0s2 failed: A security policy in place prevents this sender 
from sending this message to this recipient, see message bus configuration file (rejected 
message had interface "org.freedesktop.Hal.Device.Storage" member "Eject" error name 
"(unset)" destination "org.freedesktop.Hal")

Solution

Wait for snv_107 build. Meanwhile check the bug #6791982.

Workaround
  1. Edit /etc/dbus-1/system.d/hal.conf. Add the following line to the <policy context="default"> section.
    <allow send_interface="org.freedesktop.Hal.Device.Storage"/>
  2. Restart the D-Bus message bus system service.
    svcadm restart svc:/system/dbus:default

    On a totally unrelated note, to see all the existing SMF services with a simple description, run:

    svcs -o FMRI,DESC
  3. Finally try to eject the CD/DVD disk by typing the eject command.

Tuesday Dec 16, 2008

Quick Fix to the Linker Error - ld: fatal: library -lgcc_s: not found

Symptom(s):

When building some of the open source applications like MySQL Cluster Carrier Grade Edition with the GCC compiler, the linking step may fail with the fatal error message library -lgcc_s: not found despite the fact that the gcc_s library is available on the system and is in the linker search path -- for example, LD_LIBRARY_PATH on Solaris.

Quick Fix / Workaround:

  1. Add the absolute path to the gcc_s library on the link line, OR

  2. (Recommended) If the library's filename has any major and minor version numbers appended to it, simply create a soft link to the library in the same location without the major and minor version numbers in the target filename.

eg.,

On a Solaris 10 system, by default, the gcc_s library might be available under /usr/sfw/lib (32-bit SPARC and x86 copy), /usr/sfw/lib/sparcv9 (64-bit SPARC copy) and /usr/sfw/lib/amd64 (64-bit x64 copy). If all you can find is the gcc_s library with the version number(s) in the filename like libgcc_s.so.1, simply run the command 'ln -s libgcc_s.so.1 libgcc_s.so' in the same directory where you found the libgcc_s.so.1.

Note:

Probably this particular error message may show up on any OS platform with any compiler, and the workaround mentioned in this post may work on all those failed cases. I cannot guarantee that, but I know for sure that it works on Solaris (both SPARC and x86/x64 versions).

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today