| Oracle contain clause performance issue [message #609016] |
Fri, 28 February 2014 00:47  |
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
|
Junior Member |
|
|
I am using contains clause for oracle text index search
-------------------------------------------------------
SELECT DISTINCT reference , COUNTRY, COUNTRY_GRID
FROM
STAGE_REGISTRY stage WHERE contains( PASS , :cont)>0 AND
(COUNTRY = 'USA' AND VALIDATED_FLAG = 'Y' AND rownum < 1000) AND
NVL(stage.status,'A') IN ('A')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.05 28 262 0 0
Fetch 501 0.16 5.61 1370 3040 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 503 0.18 5.67 1398 3302 0 500
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 195 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
3 3 3 COUNT STOPKEY (cr=212 pr=119 pw=0 time=92586 us)
3 3 3 PARTITION RANGE SINGLE PARTITION: 7 7 (cr=212 pr=119 pw=0 time=92582 us cost=4548 size=10098 card=66)
3 3 3 TABLE ACCESS BY LOCAL INDEX ROWID STAGE_REGISTRY PARTITION: 7 7 (cr=212 pr=119 pw=0 time=92561 us cost=4548 size=10098 card=66)
3 3 3 DOMAIN INDEX RG_PAS2 (cr=209 pr=116 pw=0 time=89725 us cost=4548 size=0 card=0)
please help me , how to reduce the elapsed / disk / query ,, i got stuck with performance sql tuning...
How to tune a code , Please advise.
|
|
|
|
| Re: Oracle contain clause performance issue [message #609019 is a reply to message #609016] |
Fri, 28 February 2014 01:13  |
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
|
Junior Member |
|
|
here is the explain plan output
Plan
SELECT STATEMENT ALL_ROWS Cost: 2 Bytes: 75 K Cardinality: 500
4 COUNT STOPKEY
3 PARTITION RANGE SINGLE Cost: 2 Bytes: 75 K Cardinality: 500 Partition #: 2 Partitions accessed #7
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE STAGE_REGISTRY Cost: 2 Bytes: 75 K Cardinality: 500 Partition #: 3 Partitions accessed #7
1 DOMAIN INDEX INDEX (DOMAIN) RG_PAS2 Cost: 2
please advise.
[Updated on: Fri, 28 February 2014 01:13] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
