Home » RDBMS Server » Performance Tuning » COUNT STOPKEY issue -- performance issue (oracle 11gr2/unix)
COUNT STOPKEY issue -- performance issue [message #611722] Mon, 07 April 2014 23:21 Go to next message
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
Junior Member
i below sql is taking more time , also explain plan shows COUNT STOPKEY and cost : 4

i have composite index on COUNTRY and POST_CODE.

but it is talking 12 minutes. Please advise . thanks in advance.

tkprof:

SELECT REGION,STATE,COUNTY,CITY,POST_CODE, COUNTRY_NAME,REGION_NAME,
STATE_NAME,COUNTY_NAME,CITY_NAME,POST_CODE_NAME
FROM
temp WHERE COUNTRY=:B2 AND POST_CODE=:B1 AND ROWNUM <=2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 512 0.00 0.00 0 0 0 0
Fetch 512 751.34 753.09 0 47877017 132 493
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1025 751.35 753.10 0 47877017 132 493

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 242 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 COUNT STOPKEY (cr=100708 pr=0 pw=0 time=1857724 us)
1 1 1 TABLE ACCESS FULL temp (cr=100708 pr=0 pw=0 time=1857720 us cost=75 size=216 card=2)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache pin 64 0.00 0.01
library cache load lock 9 0.01 0.02
row cache lock 13 0.00 0.00
library cache: mutex X 66 0.01 0.13
library cache lock 3 0.00 0.00
KJC: Wait for msg sends to complete 4 0.00 0.00
latch: ges resource hash list 2 0.00 0.00
utl_file I/O 1197 0.00 0.02
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

[Updated on: Mon, 07 April 2014 23:24]

Report message to a moderator

Re: COUNT STOPKEY issue -- performance issue [message #611725 is a reply to message #611722] Tue, 08 April 2014 01:56 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
Why would you execute/fetch 1 row 512 times? Something doesnt add up.

[Updated on: Tue, 08 April 2014 01:56]

Report message to a moderator

Re: COUNT STOPKEY issue -- performance issue [message #611780 is a reply to message #611725] Tue, 08 April 2014 19:51 Go to previous message
rleishman
Messages: 3701
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You have an index on COUNTRY and POST_CODE you say, but Oracle is not using it. Check your bind variables :B2 and :B1; are they the same data type as COUNTRY and POST_CODE. If POST_CODE is VARCHAR2 and :B1 is a numeric, then Oracle would cast POST_CODE to a number and potentially disable the index.

Ross Leishman
Previous Topic: 5M record insert takes two hours
Next Topic: DATA BASE IS WORKING VERY SLOW
Goto Forum:
  


Current Time: Sat Dec 20 01:34:40 CST 2014

Total time taken to generate the page: 0.08601 seconds