Query taking long time

by Shree
(India)

Hi I am not able to find out what is the problem with the query it takes a very long time to respond.


Following is the data in the tables
Table Name    Number of records
soappkey      17360994
SoRecord      8685193
SoTransaction 8685193
SoResponse    8561626

This is the query
SELECT A.soId
, A.sourceType
, A.sourceId
, A.transactionId
, A.soType
, A.priority
, A.soReplayed
, A.receiptTimestamp
, A.regionId
, B.startTimestamp
, B.soState
, B.soCompleteTimestamp
, B.owningStp
, B.sleGeneration
, B.sleState
, C.soResult
, C.responseTimestamp
, C.respondedTimestamp
FROM nps.soappkey Z
    , nps.SoRecord A
    , nps.SoTransaction B
    , nps.SoResponse C
WHERE A.soId = Z.soId (+)
AND A.soId = B.soId (+)
AND A.soId = C.soId (+)
AND Z.appkeyname = 'MSISDN'
AND Z.appkeyvalue = '2348139276082'
ORDER BY A.soId DESC

And the execution plan
Plan hash value: 555964720

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 | 1468K|
| 1 | NESTED LOOPS OUTER | | 1 | 173 | 1468K|
| 2 | NESTED LOOPS OUTER | | 1 | 134 | 1468K|
| 3 | NESTED LOOPS | | 1 | 109 | 1468K|
| 4 | TABLE ACCESS BY INDEX ROWID| SORECORD | 9179K| 709M| 91362 |
| 5 | INDEX FULL SCAN DESCENDING| SORECORDPKEY | 9179K| | 2820 |
|* 6 | TABLE ACCESS BY INDEX ROWID| SOAPPKEY | 1 | 28 | 1 |
|* 7 | INDEX UNIQUE SCAN | SOAPPKEYPKEY | 1 | | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | SORESPONSE | 1 | 25 | 1 |
|* 9 | INDEX UNIQUE SCAN | SORESPONSEPKEY | 1 | | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID | SOTRANSACTION | 1 | 39 | 1 |
|* 11 | INDEX UNIQUE SCAN | SOTRANSACTIONPKEY | 1 | | 1 |
------------------------------------------------------------------------------------

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

6 - filter("Z"."APPKEYVALUE"='2348139276082')
7 - access("A"."SOID"="Z"."SOID" AND "Z"."APPKEYNAME"='MSISDN')
9 - access("A"."SOID"="C"."SOID"(+))
11 - access("A"."SOID"="B"."SOID"(+))

Note
-----
- cpu costing is off (consider enabling it)

Can someone help?

-------------------


Without knowing anything about your application and with the limited amount of information you have provided, it is difficult to be of much help. However we can offer a few general tips.

Firstly, the number of records in the tables is of not much relevance to SQL tuning. If Oracle has accurate statistics it will decide whether or not to use indexes depending on the proportion of the table that is expected to be read by the query and the size of the the table. If a large proportion of data is required or the table is small, then Oracle will chose a full table scan. If a small proportion of the table is required and the table is large then Oracle will use indexed reads.

In this example, as we can see from the plan, Oracle has decided to use indexed reads because the 2 constants provided in the where clause of your SQL statement apply to a small number of records.

In summary, the first tip is to make sure your table statistics are accurate by running one of the procedures in DBMS_STATS to gather statistics at the table, schema or database level.

Secondly, your SQL statement involves outer joins - meaning that if a match is not found in one table for a key value from the other table then a NULL value is returned. You should check that outer joins are in fact required. Does your application permit child/related records to be created before the parent record? And if so, should it (meaning is this a business requirement or just sloppy design or programming)? If it's a business requirement then outer joins are required. If not, then they are redundant. See http://www.asktheoracle.net/oracle-sql-joins.html to learn about the different types of joins in Oracle.

Thirdly, it may not be possible to get this query to run any faster given the amount of data in your database. In which case there are a couple of possibilities. If this is a data warehouse, you could consider partitioning your data (depending on your license) or you could use materialized views. A materialized view is merely a SQL query that has already been run and the results stored in a table. Materialized views have a number of options regarding the refreshing of the view. For more details see the Oracle Datawarehousing Guide.

See also the following questions and answers about Oracle performance tuning:

Finally, see here for a more in-depth discussion of Oracle performance tuning and see the Oracle training page for information about formal training courses.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.