Home » RDBMS Server » Security » OLS query problem
OLS query problem [message #264832] Tue, 04 September 2007 13:10 Go to next message
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.
Re: OLS query problem [message #264879 is a reply to message #264832] Tue, 04 September 2007 21:57 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Have a look at,
http://www.orafaq.com/forum/t/88153/0/
Previous Topic: how to make a user able to use tables in diferent table spaces
Next Topic: user grants via role work only sometimes
Goto Forum:
  


Current Time: Thu Mar 28 23:48:57 CDT 2024