| COUNT STOPKEY issue -- performance issue [message #611722] |
Mon, 07 April 2014 23:21  |
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 #611780 is a reply to message #611725] |
Tue, 08 April 2014 19:51  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
