| Need help in tuning query [message #602082] |
Fri, 29 November 2013 04:27  |
 |
nishant87
Messages: 53
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  |
|
nishant87
Messages: 53
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 #602208 is a reply to message #602082] |
Sun, 01 December 2013 09:46 |
 |
Kevin Meade
Messages: 2103
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 |
John Watson
Messages: 8960
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 |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
