Home » RDBMS Server » Performance Tuning » Need help in tuning query
Need help in tuning query [message #602082] Fri, 29 November 2013 04:27 Go to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
We have this below query in few programs and causing a big issue.

CURSOR c_Invoice(p_Order_Number VARCHAR2) IS
SELECT DISTINCT Rct.Customer_Trx_Id Cust_Trx_Id,
Rct.Trx_Number Invoice_Num,
Rct.Org_Id Org_Id,
Rct.Bill_To_Customer_Id Customer_Number, Aps.Amount_Due_Remaining Invoice_Amt
FROM Ra_Customer_Trx_Lines_All Rctl,
Ra_Customer_Trx_All Rct,
Ar_Payment_Schedules_All Aps
WHERE Rctl.Interface_Line_Attribute1 = p_Order_Number
AND Rctl.Customer_Trx_Id = Rct.Customer_Trx_Id
AND Rct.Customer_Trx_Id = Aps.Customer_Trx_Id;


But other challenge is, in RA_CUSTOMER_TRX_ALL table,

select count(1) from apps.ra_customer_Trx_All;--522871 Records

select count(1) from apps.ra_customer_Trx_lines_All;--1469438Records

What are the way you suggest to tune and expedite this query.
Re: Need help in tuning query [message #602083 is a reply to message #602082] Fri, 29 November 2013 04:29 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
The table RCTL is not needed. i want to remove but even after removing the plan is not promising. Please see it and correct. Its urgent.


WITH RCTL:

EXPLAIN PLAN FOR
SELECT COUNT(DISTINCT Rct.Customer_Trx_Id)
--INTO l_Cust_Trx_Count
FROM Ra_Customer_Trx_Lines_All Rctl,
Ra_Customer_Trx_All Rct,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE RCT.INTERFACE_HEADER_ATTRIBUTE1 = :L_ORDER_NUMBER
AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND Rct.Customer_Trx_Id = Aps.Customer_Trx_Id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','','BASIC'));

------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | VW_DAG_0 |
| 3 | HASH GROUP BY | |
| 4 | NESTED LOOPS | |
| 5 | NESTED LOOPS | |
| 6 | TABLE ACCESS FULL| RA_CUSTOMER_TRX_LINES_ALL |
| 7 | INDEX UNIQUE SCAN| RA_CUSTOMER_TRX_U1 |
| 8 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_N2 |
------------------------------------------------------------
WITHOUT RCTL

EXPLAIN PLAN FOR
SELECT COUNT(DISTINCT Rct.Customer_Trx_Id)
--INTO l_Cust_Trx_Count
FROM --Ra_Customer_Trx_Lines_All Rctl,
Ra_Customer_Trx_All Rct,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE RCT.INTERFACE_HEADER_ATTRIBUTE1 = :L_ORDER_NUMBER
--AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND Rct.Customer_Trx_Id = Aps.Customer_Trx_Id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','','BASIC'));

---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | VW_DAG_0 |
| 3 | HASH GROUP BY | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS FULL| RA_CUSTOMER_TRX_ALL |
| 6 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_N2 |
---------------------------------------------------------

EXPLAIN PLAN FOR
SELECT DISTINCT RCT.CUSTOMER_TRX_ID CUST_TRX_ID,
RCT.TRX_NUMBER INVOICE_NUM,
RCT.ORG_ID ORG_ID,
RCT.BILL_TO_CUSTOMER_ID CUSTOMER_NUMBER,
APS.AMOUNT_DUE_REMAINING INVOICE_AMT
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL,
RA_CUSTOMER_TRX_ALL RCT,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE RCTL.INTERFACE_LINE_ATTRIBUTE1 = :B1
and RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','','BASIC'));


---------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH UNIQUE | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_LINES_ALL |
| 6 | TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL |
| 7 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 |
| 8 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_N2 |
| 9 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL |
---------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT DISTINCT RCT.CUSTOMER_TRX_ID CUST_TRX_ID,
RCT.TRX_NUMBER INVOICE_NUM,
RCT.ORG_ID ORG_ID,
RCT.BILL_TO_CUSTOMER_ID CUSTOMER_NUMBER,
APS.AMOUNT_DUE_REMAINING INVOICE_AMT
FROM --RA_CUSTOMER_TRX_LINES_ALL RCTL,
RA_CUSTOMER_TRX_ALL RCT,
AR_PAYMENT_SCHEDULES_ALL APS
where RCT.INTERFACE_HEADER_ATTRIBUTE1 = :B1
--and RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','','BASIC'));


------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH UNIQUE | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_ALL |
| 5 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_N2 |
| 6 | TABLE ACCESS BY INDEX ROWID| AR_PAYMENT_SCHEDULES_ALL |
------------------------------------------------------------------

Re: Need help in tuning query [message #602084 is a reply to message #602083] Fri, 29 November 2013 04:33 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Your code ans exec plans are unreadable. Please format them properly, and enclose within [code] tags. You have been asked to do this before.
Re: Need help in tuning query [message #602208 is a reply to message #602082] Sun, 01 December 2013 09:46 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
Your query plans are also missing the predicates information section. This is one of the critical pieces of a plan that shows you more of what the optimizer was thinking when it settled on this plan.

That said, this is actually a very simple query and query plan. It is also a great example of the primary conflict the optimizer engages in: deciding whether to use a NESTED LOOPS JOIN with SUPPORTING INDEX ON THE INNER TABLE OF THE JOIN, or to use a HASH JOIN and likely FULL TABLE SCANS.

It appears that the query has decided to use NL but also is doing a FTS. This is likely because of a missing index.

To know what really should have happened, we need to have row counts. You provided these which is good. This tells us the number of rows on the tables involved.

select count(1) from apps.ra_customer_Trx_All;--522871 Records

select count(1) from apps.ra_customer_Trx_lines_All;--1469438Records

But you also need to tell us the FILTERED CARDINALITY of various query steps. In particular we need to know this:

select count(*) FROM  Ra_Customer_Trx_All Rct WHERE RCT.INTERFACE_HEADER_ATTRIBUTE1 = :L_ORDER_NUMBER;

where you plug in the specific values you are interested in. If there is more than one common value you search for run the query for each.

Additionally you should provide the indexes that exist on these tables in order for us to see an index is missing that might have been used. This will depend upon the row count we see from the above query.

Lastly you have chopped the ROWS column off the plan. Please use ALLSTATS LAST instead of BASIC. And then please post all the requested info. in addition to your database version.

Kevin

[Updated on: Sun, 01 December 2013 09:58]

Report message to a moderator

Re: Need help in tuning query [message #602213 is a reply to message #602083] Sun, 01 December 2013 10:38 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Following Kevin's post (with correctly formatted code!) I just checked the indexes on AR.RA_CUSTOMER_TRX_ALL and INTERFACE_HEADER_ATTRIBUTE1 is not an indexed column. You could index it, but there are already 23 indexes on that table. Are you sure your query could not use a predicate on some other column that is already indexed, such as PURCHASE_ORDER ?
Re: Need help in tuning query [message #602222 is a reply to message #602213] Sun, 01 December 2013 17:29 Go to previous message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks John. Glad you can see the indexes???

23 indexes could be bad, but it could also be good. Depends upon the nature of the system and what the indexes are for. One could also argure that after 23 indexes, what is one more. I suppose it all depends upon how important this query is. Let me elaborate a little.

When it comes to indexes there are always coflicting needs. Here is an incomplete list but one that fits this situation.

1. an index has a maintenance cost. Each insert causes an index update for each index on the table. A delete causes same. An update causes two for each index column modified. An update is a delete/insert for each changed column. In old versions of Oracle this would be two for each index on the table because the database was dumb and simply modified the block anyway regardless of if the column value had changed. Today oracle is smarter and so it is now only two for each value that actually has changed so indexing is less expensive in update scenarios today than it used to be. Some people might want to update their knowledge base with this fact. 23 indexes means potentially a lot of maintenance cost IF THIS TABLE IS ACTIVE. Active is relative of course and I won't define it here except to say that if it hurts too much then it is an active table. How you define hurts is up to you.

2. BUT BUT BUT... indexes serve a purpose. Additionally this puporse is different for different situations. Indexing is sometimes looked at as a tradeoff of in costs, and sometimes a natural part of design. For example:

For OLTP systems, we need indexes on all PK/UK/FK constraints so that our atomic transactions will operate quickely. This is because lookups in OLTP systems are 99% aligned to constraints. So if these 23 indexes are all on individual foreign keys then that might make sense as long as there is no transaction in your online data capture systems that is going too slow (hurts too much).

For DSS (REPORTING) system, if we are looking at a table that is large and partitioned by date and all but the current partition is static, then 23 indexes again might be a good choice. This gets right back to maintenance workload and how the data is maintained. These types of system generally look at indexing as a tradeoff in the design.

For ANALYTIC systems, tables may be maintained via TRUNC and LOAD and indexes built on top of mostly static tables. In this case we might see 23 BITMAP indexes on this analytic FACT table. This would in fact be normal and not an issue.

3. it is also likely that many of these indexes may not be needed or are borderline cases. this is a judgement call that has to be made by whoever owns the system but the reality is we likely don't make any such choices until we have pain that prompts us to do so which takes us back to the original idea of how much does it hurt.

I am not suggesting any direction for the OP here, just offering up some thoughts. Kevin
Previous Topic: Automatic SQL Tuning
Next Topic: Changing the defaut number of Plans checked by optimzer
Goto Forum:
  


Current Time: Thu Apr 17 19:47:13 CDT 2014

Total time taken to generate the page: 0.13384 seconds