Home » SQL & PL/SQL » SQL & PL/SQL » SQL Performance Analyzer Recommendation (Enterprise Oracle 11gR2, Tuning Pack and RHED 5.2 (2.6 kernel))
SQL Performance Analyzer Recommendation [message #568326] Wed, 10 October 2012 16:33
debrucer2
Messages: 6
Registered: August 2012
Location: San Diego California
Junior Member
SQL Performance Analyzer (SPA) reports code created by a call to Oracle Label Security (OLS) procedure or function as "top SQL". It gives a tuning recommendation in the form of an alternate execution plan that is a 50% improvement over default. This piece of code is executed 100s of 1000s of times and consumes approximately 25% of DB Time. There is no problem implementing the new plan; but, there is a second recommendation to "Use bigger fetch arrays while fetching results from the SELECT statement with SQL ID '6nb2tcsxgrbhw'".

Where would we implement this? The code is generated by Oracle (lbacsys schema) internally as a result of a function call to OLS. "defaultRowPrefetch" may be implemented in a data source; but, this value would then be used for all SQL created using this data source, and it would more than likely be negative for code where this recommendation was not made.

Internet searches for this recommendation do not produce clear results. It appears to be requesting the execution of a Java method for "fetch size", which may also be implemented with "defaultRowPrefetch" when "setFetchSize" is zero. I don't know where to execute this, either.

The code that SPA makes this recommendation for follows:

SELECT  A.pol#,
        nlabel,
        CASE
                WHEN A.pol# IN (1,0)
                THEN DECODE(LBAC$SA.enforce_write(pol_name,ilabel),1,21,2,42,0)
        END
FROM    lbac$lab A,
        lbac$pol B
WHERE   B.pol#               = A.pol#
        AND bitand(A.flags,1)=1
        AND bitand(B.flags,1)=1
        AND tag#             > sys_context('LBAC$LABELS','LBAC$LASTSEQ')
        AND
        (
                CASE
                        WHEN A.pol# IN (1,0)
                                AND sys_context('LBAC$'
                                ||
                                B.pol#
                                ||
                                '_LAB','LBAC$BYPASS')<2
                        THEN
                                CASE
                                        WHEN sys_context('LBAC$'
                                                ||
                                                B.pol#
                                                ||
                                                '_LAB','LBAC$BYPASS')<1
                                        THEN LBAC$SA.enforce_read(pol_name,ilabel)
                                        ELSE LBAC$SA.enforce_write(pol_name,ilabel)
                                END
                        ELSE 0
                END
        )
        >= 1
ORDER BY nlabel ASC

When run as stand-alone SQL (in SQL*Plus) connected as the LBACSYS user, it returns zero rows. That would hardly be reason to increase the size of the fetch buffer.

Our application is written using the iBATIS framework, Java and JVM in the JBoss application server. The OLS schema (LBACSYS) has three triggers, and this code is none of them. I have also looked through procedures, packages, etc., to see where it gets generated, and can find nothing.

Any suggestions as to how to implement this recommendation would be greatly appreciated.

Thank you,
David Russell
San Diego, CA
Previous Topic: Create multiple records based on condition
Next Topic: wrong time offset calculation
Goto Forum:
  


Current Time: Wed Jul 30 22:29:43 CDT 2014

Total time taken to generate the page: 0.11793 seconds