Home » SQL & PL/SQL » SQL & PL/SQL » Query performance in prod environment (Oracle 10G / Linux / SQL Query)
Query performance in prod environment [message #398215] Wed, 15 April 2009 13:04 Go to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

Need help with optimizing the following query - it takes good 3-4 mins at times even 7+ mins to run the query
 SELECT
   *   FROM
   CAP td,
   SETl st,
  CUSTOMERDETAIL cd,
   BANK_INFO bbi
  WHERE
   td.cc_bin=bbi.bin(+) and
st.id=td.settlementId AND
      td.CUSTOMER=cd.CUSTOMERID and td.TRANSACTIONDATE>=TO_Date( '04/14/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
   and td.TRANSACTIONDATE<=TO_Date( '04/14/2009 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
 


5227 rows selected.

Elapsed: 00:08:55.93

Execution Plan
----------------------------------------------------------
Plan hash value: 2906588924

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  5184 |  1711K| 24696   (1)| 00:00:50 |
|   1 |  NESTED LOOPS                  |                  |  5184 |  1711K| 24696   (1)| 00:00:50 |
|*  2 |   HASH JOIN RIGHT OUTER        |                  |  5090 |  1391K| 14478   (1)| 00:00:29 |
|   3 |    TABLE ACCESS FULL           | BANK_INFO        | 10762 |   252K|    79   (2)| 00:00:01 |
|   4 |    NESTED LOOPS                |                  |  5090 |  1272K| 14398   (1)| 00:00:29 |
|   5 |     TABLE ACCESS BY INDEX ROWID| CAP              |  5091 |  1073K|  4178   (1)| 00:00:09 |
|*  6 |      INDEX RANGE SCAN          | CAP_TXNDATE      |  5091 |       |    17   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| SETL             |     1 |    40 |     2   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | SYS_C005748      |     1 |       |     1   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | CUSTOMERDETAIL   |     1 |    58 |     2   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN           | SYS_C005778      |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BBI"."BIN"(+)=TO_NUMBER("TD"."CC_BIN"))
   6 - access("TD"."TRANSACTIONDATE">=TO_DATE('2009-04-14 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "TD"."TRANSACTIONDATE"<=TO_DATE('2009-04-14 23:59:59', 'yyyy-mm-dd
              hh24:mi:ss'))
   8 - access("ST"."ID"="TD"."SETTLEMENTID")
  10 - access("TD"."CUSTOMER"="CD"."CUSTOMERID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      47315  consistent gets
       6118  physical reads
          0  redo size
    1162436  bytes sent via SQL*Net to client
       4297  bytes received via SQL*Net from client
        350  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5227  rows processed

any help is appreciated ...
- Suresh

[Mod-Edit: Frank added code-tags to preserve spacing and thus improve readability]

[Updated on: Thu, 16 April 2009 00:46] by Moderator

Report message to a moderator

Re: Query performance in prod environment [message #398218 is a reply to message #398215] Wed, 15 April 2009 13:11 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Is this database local to you? If not, see how long this takes...
select count(*) from ( {your-query-goes-here} )
Re: Query performance in prod environment [message #398220 is a reply to message #398215] Wed, 15 April 2009 13:14 Go to previous messageGo to next message
smallboy
Messages: 4
Registered: April 2009
Junior Member
Use Between instead of

td.TRANSACTIONDATE>=TO_Date( '04/14/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
and td.TRANSACTIONDATE<=TO_Date( '04/14/2009 23:59:59', 'MM/DD/YYYY HH24:MI:SS')



What are the index name on the tables?

TABLE ACCESS FULL | BANK_INFO 

-Have a look at this.What are the index for this table?


[Updated on: Wed, 15 April 2009 13:16]

Report message to a moderator

Re: Query performance in prod environment [message #398222 is a reply to message #398218] Wed, 15 April 2009 13:17 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

hi - it takes good 4-5 mins
do you see anything obvious in the query which can be finetuned
Re: Query performance in prod environment [message #398223 is a reply to message #398220] Wed, 15 April 2009 13:19 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

thanks for your response - there are about 10K rows in that table - it is fetched one time i guess - the explain plan is also attached for your reference ...
Re: Query performance in prod environment [message #398225 is a reply to message #398215] Wed, 15 April 2009 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Are statistics current on all tables & indexes?
Re: Query performance in prod environment [message #398231 is a reply to message #398220] Wed, 15 April 2009 13:25 Go to previous messageGo to next message
smallboy
Messages: 4
Registered: April 2009
Junior Member
You need to tell us:

1.What are the index for the tables in the query.
2. How many rows are there in table BANK_INFO

As blackswan said,to get the right plan make sure that statistics ar up to date
Re: Query performance in prod environment [message #398232 is a reply to message #398225] Wed, 15 April 2009 13:26 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

yes - stats are updated atleast once a week , more often at times
Re: Query performance in prod environment [message #398236 is a reply to message #398215] Wed, 15 April 2009 13:34 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

bank-bin has 10,000 records, all other columns in query are indexed

td.cc_bin=bbi.bin(+) and
st.id=td.settlementId AND
td.CUSTOMER=cd.CUSTOMERID and td.TRANSACTIONDATE>=TO_Date( '04/14/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
and td.TRANSACTIONDATE<=TO_Date( '04/14/2009 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
st.id , td.settlementid , td.customer , cd.customerid
and td.transactiondate are all indexed
Re: Query performance in prod environment [message #398248 is a reply to message #398236] Wed, 15 April 2009 14:05 Go to previous messageGo to next message
smallboy
Messages: 4
Registered: April 2009
Junior Member
What u mean when u said -"all other columns in query are indexed"

Can you please provide the index list ( on which columns).Its your table.



eg,index used on table test are

index on table test

pk_index_test_id (id);
ci1_index_test(status, dt);



user_ind_columns from where u can have information

[Updated on: Wed, 15 April 2009 14:08]

Report message to a moderator

Re: Query performance in prod environment [message #398324 is a reply to message #398220] Thu, 16 April 2009 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
smallboy wrote on Wed, 15 April 2009 20:14
Use Between instead of

td.TRANSACTIONDATE>=TO_Date( '04/14/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
and td.TRANSACTIONDATE<=TO_Date( '04/14/2009 23:59:59', 'MM/DD/YYYY HH24:MI:SS')



Is this based on anythng? Can you show how this would improve performance? I have never known of or seen any difference.
Re: Query performance in prod environment [message #398329 is a reply to message #398324] Thu, 16 April 2009 01:04 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
access("BBI"."BIN"(+)=TO_NUMBER("TD"."CC_BIN"))


Why is bin field in BBI table a number whereas CC_BIN in td table is not a number? If you have an index field on cc_bin field in TD table then optimizer will not use it because of the implicit to_number function.




[Updated on: Thu, 16 April 2009 01:08]

Report message to a moderator

Re: Query performance in prod environment [message #398358 is a reply to message #398324] Thu, 16 April 2009 01:57 Go to previous messageGo to next message
smallboy
Messages: 4
Registered: April 2009
Junior Member
"Is this based on anythng? Can you show how this would improve performance? I have never known of or seen any difference" ---

There is nothing with the optimization in this case.
Re: Query performance in prod environment [message #398383 is a reply to message #398215] Thu, 16 April 2009 02:31 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi all, if you are saying that the bank_info table is
having the 10000 rows and all column are indexed
then u must be knowing that column used in join
condition query(td.cc_bin=bbi.bin(+)) should return 3 to 5% rows otherwise the indexes on the following column
will not be used secondally the value of the
column must be distinct in case of production
database for btree index to be used(if it so) and
one of the friend here truly said that if their
is internal conversion than also it will not use
any index. if anything occur do it the same for
best optimization result, if still you are not able
to reduce the cost then use optimization hint
Re: Query performance in prod environment [message #398390 is a reply to message #398383] Thu, 16 April 2009 02:39 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

Hi - i have attached the explain plan along with the query - which has the indexes used and the full table scan on bank table
Re: Query performance in prod environment [message #398399 is a reply to message #398383] Thu, 16 April 2009 02:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The FTS is part of a HashJoin
Besides, 10000 rows is not a big deal to do an FTS on.

What percentage of your CAP table is selected using the date-restriction?
If this is a small percentage, you might try to make this table the driving table.

Is there an index on CAP.TRANSACTIONDATE ?
Re: Query performance in prod environment [message #398402 is a reply to message #398399] Thu, 16 April 2009 02:53 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

yes there is an index on transaction date , the number of records selected is about 5200 - i am hoping what is shown in the explain plan is what is selected - it says something like 5091
Re: Query performance in prod environment [message #398407 is a reply to message #398390] Thu, 16 April 2009 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You seem to be having trouble reading some of the posts.

I'll repeat the request, in more detail.

Please provide details of the indexes on the tables in these queries that index any of the columns that this query uses.
Provide the index names, types, and a list of the columns that the indexes use.
Re: Query performance in prod environment [message #398498 is a reply to message #398407] Thu, 16 April 2009 08:24 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member

INDEX_NAME COLUMN_NAME INDEX_TYPE TABLE_NAME

BBI_BNKCODE_IDX BANK_CODE NORMAL Bank_INFO
SYS_C005307 BIN NORMAL Bank_INFO
CAPTURE_PRODUCT_NAME PRODUCT_NAME NORMAL CAP
CAPTURE_THIRD_PARTY_POST_STAT THIRD_PARTY_POSTING_STATUS NORMAL CAP
CAPTURE_TXNDATE TRANSACTIONDATE NORMAL CAP
CAP_CCHV_IDX CC_HASH_VALUE NORMAL CAP
CAP_CUST_TRDT_IDX TRANSACTIONDATE NORMAL CAP
CAP_CUST_TRDT_IDX CUSTOMER NORMAL CAP
CAP_MER_SER_CHG_IDX MERCHANT_SERVICE_CHARGE_ID NORMAL CAP
IDX_CAPTURE_SETID SETTLEMENTID NORMAL CAP
MCHEK_CAPTURE_CUSTOMER CUSTOMER NORMAL CAP
MCHEK_CAPTURE_MERCHANT MERCHANT NORMAL CAP
SYS_C005336 OID NORMAL CAP
STTLMNT_AMOUNT AMOUNT NORMAL SETL
STTLMNT_AUTMSG AUTMESSAGE NORMAL SETL
SYS_C005748 ID NORMAL SETL
SETT_ACQID_IDX ACQUIRERID NORMAL SETL
SETT_FLAGS_IDX FLAGS NORMAL SETL
SETT_RETCODE_IDX RETCODE NORMAL SETL
SETT_TXNDATE_IDX TRANSACTIONDATE NORMAL SETL
IDX_SETTL_MID MID NORMAL SETL
IDX_SETTL_RRN RRN NORMAL SETL
IDX_SETTL_TID TID NORMAL SETL
VTCTDTL_DEVICETYPE_BIDX DEVICETYPE BITMAP CUSTDTL
VTCTDTL_SRVCPRDRCRCL_BIDX SERVICEPROVIDERCIRCLE BITMAP CUSTDTL
VTCUSDTLS_CRDDT_INDX CREATEDDATE NORMAL CUSTDTL
VTCUSTDTL_SRVCPRDR_BIDX SERVICEPROVIDER BITMAP CUSTDTL
VTDTL_CUSTOPERACCTYPE__BIDX CUST_OPER_ACC_TYPE BITMAP CUSTDTL
SYS_C005778 CUSTOMERID NORMAL CUSTDTL
Re: Query performance in prod environment [message #398505 is a reply to message #398498] Thu, 16 April 2009 08:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any chance of having it formatted and readable ?

Re: Query performance in prod environment [message #398512 is a reply to message #398505] Thu, 16 April 2009 08:51 Go to previous messageGo to next message
sanantpurkar
Messages: 9
Registered: April 2009
Location: india
Junior Member



INDEX_NAME COLUMN_NAME TABLE_NAME

BBI_BNKCODE_IDX BANK_CODE Bank_INFO
SYS_C005307 BIN Bank_INFO
CAPTURE_PRODUCT_NAME PRODUCT_NAME CAP
CAPTURE_THIRD_PARTY_POST_STAT THIRD_PARTY_POSTING_STATUS CAP
CAPTURE_TXNDATE TRANSACTIONDATE CAP
CAP_CCHV_IDX CC_HASH_VALUE CAP
CAP_CUST_TRDT_IDX TRANSACTIONDATE CAP
CAP_CUST_TRDT_IDX CUSTOMER CAP
CAP_MER_SER_CHG_IDX MERCHANT_SERVICE_CHARGE_ID CAP
IDX_CAPTURE_SETID SETTLEMENTID CAP
MCHEK_CAPTURE_CUSTOMER CUSTOMER CAP
MCHEK_CAPTURE_MERCHANT MERCHANT CAP
SYS_C005336 OID CAP
STTLMNT_AMOUNT AMOUNT SETL
STTLMNT_AUTMSG AUTMESSAGE SETL
SYS_C005748 ID SETL
SETT_ACQID_IDX ACQUIRERID SETL
SETT_FLAGS_IDX FLAGS SETL
SETT_RETCODE_IDX RETCODE SETL
SETT_TXNDATE_IDX TRANSACTIONDATE SETL
IDX_SETTL_MID MID SETL
IDX_SETTL_RRN RRN SETL
IDX_SETTL_TID TID SETL
VTCTDTL_DEVICETYPE_BIDX DEVICETYPE CUSTDTL
VTCTDTL_SRVCPRDRCRCL_BIDX SERVICEPROVIDERCIRCLE CUSTDTL
VTCUSDTLS_CRDDT_INDX CREATEDDATE CUSTDTL
VTCUSTDTL_SRVCPRDR_BIDX SERVICEPROVIDER CUSTDTL
VTDTL_CUSTOPERACCTYPE__BIDX CUST_OPER_ACC_TYPE CUSTDTL
SYS_C005778 CUSTOMERID CUSTDTL
Re: Query performance in prod environment [message #398514 is a reply to message #398505] Thu, 16 April 2009 08:53 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Thu, 16 April 2009 15:37
Any chance of having it formatted and readable ?

It seems the answer is: no. Sad

Regards
Michel

Previous Topic: How to know the constraints hostory. [merged]
Next Topic: how to clear sql%rowcount value
Goto Forum:
  


Current Time: Thu Dec 08 02:11:28 CST 2016

Total time taken to generate the page: 0.12030 seconds