| 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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
