Home » RDBMS Server » Performance Tuning » TKprof shows fetching taking long time (Database: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
TKprof shows fetching taking long time [message #395525] Wed, 01 April 2009 13:53 Go to next message
spur230
Messages: 25
Registered: April 2009
Location: US
Junior Member
Query just takes a second from toad but when used inside a procedure as a cursor it takes takes 3 to 5 minutes.

Following is the Tkprof information when running from procedure.
SELECT CHCLP.CLM_PRVDR_TYPE_LKPCD, CHCLP.PRVDR_LCTN_IID, TO_CHAR
(CHCLP.MODIFIED_DATE, 'MM-dd-yyyy hh24:mi:ss') MODIFIED_DATE,
CHCLP.PRVDR_LCTN_IDENTIFIER, CHCLP.CLM_HDR_CLM_LN_X_PVDR_LCTN_SID
FROM
CLM_HDR_CLM_LN_X_PRVDR_LCTN CHCLP WHERE CHCLP.CLAIM_HEADER_SID = :B1 AND
CHCLP.CLAIM_LINE_SID IS NULL AND CHCLP.IDNTFR_TYPE_CID = 7

call count cpu elapsed disk quey current rows
Parse 0 0 0 0 0 0
Execute 1 0 0 0 0 0
Fetch 1 110.79 247.79 568931 576111 0 3
total 2 110.79 247.79 568931 576111 0 3

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93 (CMSAPP) (recursive depth: 1)

Rows Execution Plan

------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 PARTITION RANGE (SINGLE) PARTITION:KEYKEY
0 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF
'CLM_HDR_CLM_LN_X_PRVDR_LCTN' (TABLE) PARTITION:KEYKEY
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'XAK1CLM_HDR_CLM_LN_X_PRVDR_LCT' (INDEX (UNIQUE))
PARTITION:KEYKEY


Execution plan when running just the query from TOAD is: (it comes out in a second)
Plan
SELECT STATEMENT ALL_ROWSCost: 6 Bytes: 100 Cardinality: 2
3 PARTITION RANGE SINGLE Cost: 6 Bytes: 100 Cardinality: 2 Partition #: 1 Partitions accessed #13
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE CMSAPP.CLM_HDR_CLM_LN_X_PRVDR_LCTN Cost: 6 Bytes: 100 Cardinality: 2 Partition #: 2 Partitions accessed #13

Why would fetching take such a long time? Please let me know if you need any other information.
Thank You.
Re: TKprof shows fetching taking long time [message #395762 is a reply to message #395525] Thu, 02 April 2009 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Please use <code tags> to make post readable.
Re: TKprof shows fetching taking long time [message #396002 is a reply to message #395525] Fri, 03 April 2009 08:35 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Please take note that toad does not retrieve all records at once, it probably display first few records (ie: 500), then as you scroll down, it retrieves the rest of records. So this might be a reason of the differents in timing


[Updated on: Fri, 03 April 2009 08:36]

Report message to a moderator

Re: TKprof shows fetching taking long time [message #397547 is a reply to message #395525] Sun, 12 April 2009 02:49 Go to previous messageGo to next message
mayur316
Messages: 4
Registered: April 2009
Junior Member
Try bulk binding(bulk collect,bulk fetch etc) and avoid context switching.
Re: TKprof shows fetching taking long time [message #397574 is a reply to message #397547] Sun, 12 April 2009 18:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I possibly agree with @ajitpal.s. It only LOOKS fast because it starts returning rows quickly, but it will take much longer to return EVERY row.

See this article for some techniques on benchmarking a SQL properly whilst tuning.


I said "possibly" though: what makes this one strange is that your TKPROF says you fetched only 3 rows, even though you read more than 500K blocks from disk.

My guess is that:
- The index is on columns (IDNTFR_TYPE_CID, CLAIM_HEADER_SID)
- CLAIM_HEADER_SID is a VARCHAR column
- Your bind variable in PL/SQL is NUMERIC

In this case, it still uses the index, but only for scanning the first column. There are probably millions of rows that match on IDNTFR_TYPE_CID, but only 3 of those millions match on CLAIM_HEADER_SID.

The reason it works in TOAD is because TOAD does not use the same bind variable value as in PL/SQL. In this case, the bind variable is supplied as a VARCHAR, the column is not cast to a number, and the index scan uses both columns.

Check the data type of CLAIM_HEADER_SID in the table, and check the data type of the bind variable you supply. Make sure they are the same data type. If they are not, consider the wisdom of using %TYPE declarations in PL/SQL to avoid this sort of problem.

Ross Leishman

Re: TKprof shows fetching taking long time [message #397575 is a reply to message #397574] Sun, 12 April 2009 19:13 Go to previous messageGo to next message
spur230
Messages: 25
Registered: April 2009
Location: US
Junior Member
Thank you every body for the reply. Ross , your explanation was great. However, in my procedure bind variable (claim_header_sid) is also a number and the also has the column defined as number. Infact, the column is using %type while declaration.

The index being used XAK1CLM_HDR_CLM_LN_X_PRVDR_LCT is defined based on claim_header_sid, claim_line_sid but does not have identifier_type_cid.

However, for a claim_header_sid there is normally only 20 rows is average and at most 100 records can be found in that table. This issue is also intermediate. The code was working fine but suddenly become very slow on occasion. We did check database load and network traffic. They are all normal.

Once again thanks for your time.
Re: TKprof shows fetching taking long time [message #397578 is a reply to message #395525] Sun, 12 April 2009 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT chclp.clm_prvdr_type_lkpcd, 
       chclp.prvdr_lctn_iid, 
       To_char(chclp.modified_date,'MM-dd-yyyy hh24:mi:ss') modified_date, 
       chclp.prvdr_lctn_identifier, 
       chclp.clm_hdr_clm_ln_x_pvdr_lctn_sid 
FROM   clm_hdr_clm_ln_x_prvdr_lctn chclp 
WHERE  chclp.claim_header_sid = :B1 
       AND chclp.claim_line_sid IS NULL 
       AND chclp.idntfr_type_cid = 7


>AND chclp.claim_line_sid IS NULL
The filter above results in a Full Table Scan regardless of any & all indexes on this table.



Re: TKprof shows fetching taking long time [message #397588 is a reply to message #397578] Sun, 12 April 2009 23:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
BlackSwan wrote on Mon, 13 April 2009 11:06

>AND chclp.claim_line_sid IS NULL
The filter above results in a Full Table Scan regardless of any & all indexes on this table.

Wha-huh??????

The OP says the index leads with column claim_header_sid, which is used in an equals-filter with a bind variable. It can index scan on that column and simply filter the results on the IS NULL predicate.


OK, so you claim the data types are matching. So one of two things is happening:

- TKPROF says it is using the index, but maybe it isn't. If you run TKPROF with the EXPLAIN=uid/pwd option, it includes two plans: the one it used and the one it WOULD use if you ran it now under the UID/PWD supplied. Often they are the same, but sometimes they are different. When they are different, the row counts on the second (theoretical) plan all show zero...like yours....hmmmmm. Run TK*Prof again without the EXPLAIN= option and post the plan IN CODE TAGS THIS TIME PLEASE

- It really IS using the index. If so, then it is pulling a LOT of rows - much more than 300. Run a check to see what the most common value of claim_header_sid is.
SELECT MAX(c)
FROM (
    SELECT claim_header_sid, count(*) AS c
    FROM clm_hdr_clm_ln_x_prvdr_lctn
    GROUP BY claim_header_sid
)


Ross Leishman
Previous Topic: create dynamic partitions in oracle 9
Next Topic: Explain Plan
Goto Forum:
  


Current Time: Sun Dec 04 02:35:45 CST 2016

Total time taken to generate the page: 0.21189 seconds