OraFAQ Forum: Security » OLS query problem

Home » RDBMS Server » Security » OLS query problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OLS query problem [message #264832]

Tue, 04 September 2007 13:10

Agus211
Messages: 39
Registered: September 2007

Member

Hi, I wanted to ask if you could help me with a performance problem Im having with an OLS policy. The thing is that when i execute this query:

SELECT *
FROM (SELECT /*+ FIRST_ROWS(10) */
ID, code, news, place, theme,
TO_CHAR (date_charged, 'dd/mm/yyyy HH24:MI:SS')
FROM news
WHERE ouid = 1
ORDER BY date_charged DESC)
WHERE ROWNUM < 11;

Its taking like 10 seconds to retrieve me the information(there are like 300000 records in the table). The table has an NON UNIQUE/ DECREMENTAL index called IDX_DATE, but the explain plan is:

Plan hash value: 1026461953

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 11270 | 7136 (1)| 00:01:26 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 38 | 42826 | 7136 (1)| 00:01:26 |
|* 3 | SORT ORDER BY STOPKEY| | 38 | 3876 | 7136 (1)| 00:01:26 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL | NEWS | 38 | 3876 | 7135 (1)| 00:01:26 |
-----------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
4 - filter(TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL'))<=TO_NUMB
ER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MAXLABEL')))
5 - filter("OUID"=1 AND "OLS_COLUMN_SEC">=TO_NUMBER(SYS_CONTEXT('LBAC$4_
LAB','LBAC$MINLABEL')) AND "OLS_COLUMN_SEC"<=TO_NUMBER(SYS_CONTEXT('LBAC$4_
LAB','LBAC$MAXLABEL')) AND TO_NUMBER(SYS_CONTEXT('LBAC$LABELS',TO_CHAR("OLS
_COLUMN_SEC")))>=0)

When I deactivate the OLS policy, the query works perfectly, and the explain plan I get is:
Plan hash value: 1444026940

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 11270 | 7 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 12 | 13524 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| NEWS | 313K| 28M| 7 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| IDX_DATE | 13 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
3 - filter("OUID"=1)

Could you help me to figure this out?. Thanks in advance.

Report message to a moderator