| TKprof shows fetching taking long time [message #395525] |
Wed, 01 April 2009 13:53  |
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 #397574 is a reply to message #397547] |
Sun, 12 April 2009 18:30  |
rleishman
Messages: 3728
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 |
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 #397588 is a reply to message #397578] |
Sun, 12 April 2009 23:16 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
