Home » RDBMS Server » Performance Tuning » Oracle contain clause performance issue (Oracle / 11g r2/unix)
Oracle contain clause performance issue [message #609016] Fri, 28 February 2014 00:47 Go to next message
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 Go to previous messageGo to next message
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

Re: Oracle contain clause performance issue [message #609028 is a reply to message #609019] Fri, 28 February 2014 02:13 Go to previous message
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
Junior Member
also source table has 300000000 rows..
Previous Topic: Oracle Architecture
Next Topic: AWR - "db file parallel read" + "direct path read" wait events, wait class - "User I/O"
Goto Forum:
  


Current Time: Thu Mar 28 05:44:34 CDT 2024