Home » RDBMS Server » Performance Tuning » SQL Performance
SQL Performance [message #230593] Thu, 12 April 2007 04:44 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have another problem.

Eventhough it is using indexes. For execution it takes long time.

SELECT UPPER('fem_savings') , to_date('31-Jan-2007','DD-Mon-YYYY') , 
FEM_SAVINGS.ISO_CURRENCY_CD, 
MIN(REF_ETL_PRODUCT_ASSUMPTIONS.RM_COA_ID), NULL, NULL,
ADD_MONTHS (to_date('31-Jan-2007','DD-Mon-YYYY') , 10 *12), 
SUM(NVL(FEM_SAVINGS.ACCRUED_INTEREST,0)),1151 
FROM FEM_SAVINGS,REF_ETL_PRODUCT_ASSUMPTIONS 
WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY')
AND REF_ETL_PRODUCT_ASSUMPTIONS.PRODUCT_TYPE_CD = 182 
GROUP BY FEM_SAVINGS.ISO_CURRENCY_CD


0		SELECT STATEMENT 	
1	0	 SORT GROUP BY	
2	1	  MERGE JOIN CARTESIAN	
3	2	   TABLE ACCESS BY INDEX ROWID	REF_ETL_PRODUCT_ASSUMPTIONS
4	3	    INDEX RANGE SCAN	REF_ETL_PRODUCT_ASSUMPTIONS_1
5	2	   BUFFER SORT	
6	5	    TABLE ACCESS BY INDEX ROWID	FEM_SAVINGS
7	6	     INDEX RANGE SCAN	FEM_SAVINGS_RM


Brayan.
Re: SQL Performance [message #230599 is a reply to message #230593] Thu, 12 April 2007 04:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
CHeck the statistics.
Seems Some of the involved tables have outdated statistics.
Re: SQL Performance [message #230600 is a reply to message #230599] Thu, 12 April 2007 04:55 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Both the index and table are analyzed last week. After that there was nothing has been loaded into this table.

Regards,
Brayan
Re: SQL Performance [message #230614 is a reply to message #230600] Thu, 12 April 2007 05:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are not joining the tables, hence the MERGE JOIN CARTESIAN.

Are you missing a WHERE clause?

Ross Leishman
Re: SQL Performance [message #230619 is a reply to message #230614] Thu, 12 April 2007 06:20 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

The cartesian is correct.

REF_ETL_PRODUCT_ASSUMPTIONS.PRODUCT_TYPE_CD = 182 selects only one record. These queries are from the Oracle financial Services application.

Regards,
Ronald.
Re: SQL Performance [message #230647 is a reply to message #230593] Thu, 12 April 2007 08:18 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You can try:

SELECT UPPER('fem_savings') ,
   to_date('31-Jan-2007','DD-Mon-YYYY') , 
   T.ISO_CURRENCY_CD,
   R.RM_COA_ID,
   ADD_MONTHS (to_date('31-Jan-2007','DD-Mon-YYYY') , 10 *12), 
   T.TOTAL_SUM,
   1151
FROM
( SELECT ISO_CURRENCY_CD,
     SUM(NVL(FEM_SAVINGS.ACCRUED_INTEREST,0)) TOTAL_SUM
  FROM FEM_SAVINGS
  WHERE
    AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY')
  GROUP BY ISO_CURRENCY_CD ) T,
  REF_ETL_PRODUCT_ASSUMPTIONS R
WHERE R.PRODUCT_TYPE_CD = 182 


Anyway - post the description on involved indexes (columns and their order).

HTH.
Michael
Re: SQL Performance [message #230651 is a reply to message #230593] Thu, 12 April 2007 08:39 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

The index this query picks is

FEM_SAVINGS_RM is on columns ->
(AS_OF_DATE, RM_COA_ID, ORG_UNIT_ID, ISO_CURRENCY_CD)

I can not change the query, I should think of tuning from backend.

My doubt is eventhough it goes BY INDEX ROWID why it is slow.

Regards,
Ronald
Re: SQL Performance [message #230690 is a reply to message #230651] Thu, 12 April 2007 10:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, so REF_ETL_PRODUCT_ASSUMPTIONS.PRODUCT_TYPE_CD = 182 selects only one record. We want to make sure that that record is fetched just once in that case - we wouldn't want the join to fetch that one record over and over for each row in FEM_SAVINGS.

Looking at the Explain Plan, it comes first in the join, so no problem there. It picks up your 1 record and then goes and scans every row in the FEM_SAVINGS_RM index with AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY').

How many of these rows are there? And how many rows in total in FEM_SAVINGS?

If you try Michael's solution, you should get a small improvement because the GROUP BY is only sorting ISO_CURRENCY_CD rather than all of the other GROUP BY columns you included. I wouldn't expect orders of magnitude improvement though because it still has to retrieve the same data from the indexes/tables. I also like to do NVL(SUM(..)) rather than SUM(NVL(..)) - it reduces the number of function calls. No real practical performance improvement, just make you feel like you're being nice to Oracle.


Ross Leishman
Re: SQL Performance [message #230769 is a reply to message #230593] Thu, 12 April 2007 14:37 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Run SQL_TRACE and post TKPROF.
Re: SQL Performance [message #230863 is a reply to message #230769] Fri, 13 April 2007 02:17 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Following are the total no of records.

FEM_SAVINGS -- 77006824
REF_ETL_PRODUCT_ASSUMPTIONS --362

Attached is the 10046 event trace.

Brayan.
Re: SQL Performance [message #230938 is a reply to message #230593] Fri, 13 April 2007 08:21 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi, Brayan.

IMO there is a problem with index access:

SELECT UPPER('fem_savings') , to_date('31-Jan-2007','DD-Mon-YYYY') , 
FEM_SAVINGS.ISO_CURRENCY_CD, 
MIN(REF_ETL_PRODUCT_ASSUMPTIONS.RM_COA_ID), NULL, NULL,
ADD_MONTHS (to_date('31-Jan-2007','DD-Mon-YYYY') , 10 *12), 
SUM(NVL(FEM_SAVINGS.ACCRUED_INTEREST,0)),1151 
FROM FEM_SAVINGS,REF_ETL_PRODUCT_ASSUMPTIONS 
WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY')
AND REF_ETL_PRODUCT_ASSUMPTIONS.PRODUCT_TYPE_CD = 182 
GROUP BY FEM_SAVINGS.ISO_CURRENCY_CD

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     96.81     369.95     643759     626585          2           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     96.84     369.98     643759     626585          2           3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 108  (BOIDW)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (GROUP BY)
      0    MERGE JOIN (CARTESIAN)
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                'REF_ETL_PRODUCT_ASSUMPTIONS'
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                 'REF_ETL_PRODUCT_ASSUMPTIONS_1' (NON-UNIQUE)
      0     BUFFER (SORT)
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'FEM_SAVINGS'
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'FEM_SAVINGS_RM' 
                  (NON-UNIQUE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       68.63         68.64
  db file sequential read                    616163        0.05        318.20
  direct path write                               2        0.00          0.00
  direct path read                             5519        0.00          0.00

As you see there is a LONG wait on "db file sequential read" -
318.2 seconds.

So another question (already asked by Ross):
How many rows comply with AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY') condition (out of 77006824)?

Is it possible to create another index (I know it's not that simple for 77M rows table):

CREATE INDEX ... ON FEM_SAVINGS
( AS_OF_DATE, ISO_CURRENCY_CD, ACCRUED_INTEREST );

I think it may eliminate TABLE ACCESS for FEM_SAVINGS table
and to improve execution time.

TKPROF didn't display rows statistics, so you have to look at the RAW trace file and identify the waits (are these waits caused by index access or by table access). Try formatting the trace file with TRACE ANALYZER instead of TKPROF - it may help as well.

HTH.
Michael
Re: SQL Performance [message #231092 is a reply to message #230938] Sat, 14 April 2007 01:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The missing row counts are possibly caused by the trace file being truncated because it is too large.

There could be something wrong with the index. This will help you tell whether it is fragmented.

Can you also run the following for us:

SELECT count(*)
FROM FEM_SAVINGS
WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY');


SELECT count(*)
FROM REF_ETL_PRODUCT_ASSUMPTIONS 
WHERE PRODUCT_TYPE_CD = 182;


If the 1st SQL returns more that 5M, then it is a bad idea to use the index - you will be better with a Full Table Scan.

If the second SQL returns more than 1, you were mistaken in your original assertion that the cartesian join is OK.

Ross Leishman
Re: SQL Performance [message #231313 is a reply to message #230593] Mon, 16 April 2007 08:05 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Ross,

Following is the info.

SQL> SELECT count(*)
  2  FROM FEM_SAVINGS
  3  WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY');

 COUNT(*)
---------
  6226364

SQL> SELECT count(*)
  2  FROM REF_ETL_PRODUCT_ASSUMPTIONS 
  3  WHERE PRODUCT_TYPE_CD = 182;

 COUNT(*)
---------
        1

SQL> 


Since in FEM_SAVINGS there are more than 5M records with the selection mentioned in the query. I think if I use FULL tablescan it will take ages to retrieve.

Brayan.
Re: SQL Performance [message #231316 is a reply to message #230593] Mon, 16 April 2007 08:16 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

SQL> l
  1  SELECT  i.table_name
  2  ,       i.index_name
  3  ,       NULL as partition_name
  4  ,       i.num_rows / i.leaf_blocks AS rows_per_block
  5  FROM    user_indexes i
  6  LEFT OUTER JOIN user_part_indexes p
  7  ON      p.index_name = i.index_name
  8  WHERE   p.index_name IS NULL
  9  AND     i.num_rows >= 5000
 10  AND     i.leaf_blocks > 0
 11  AND     i.table_name LIKE '&pTab'
 12  UNION ALL
 13  SELECT  i.table_name
 14  ,       p.index_name
 15  ,       p.partition_name
 16  ,       p.num_rows / p.leaf_blocks AS rows_per_block
 17  FROM    user_ind_partitions p
 18  JOIN    user_indexes i
 19  ON      i.index_name = p.index_name
 20  WHERE   p.num_rows >= 5000
 21  AND     p.leaf_blocks > 0
 22* AND     i.table_name LIKE '&pTab'
SQL> /
old  11: AND     i.table_name LIKE '&pTab'
new  11: AND     i.table_name LIKE 'FEM_SAVINGS'
old  22: AND     i.table_name LIKE '&pTab'
new  22: AND     i.table_name LIKE 'FEM_SAVINGS'

TABLE_NAME      INDEX_NAME           PARTITION_ ROWS_PER_BLOCK
--------------- -------------------- ---------- --------------
FEM_SAVINGS     FEM_SAVINGS                          207.14123
FEM_SAVINGS     FEM_SAVINGS_RM                       170.54415

SQL> 



Here how we can judge whether to rebuild the index or not?

Brayan.
Re: SQL Performance [message #231362 is a reply to message #230593] Mon, 16 April 2007 13:47 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi, Bryan.

IMHO you have to try FULL table scan:
For now your query performs 643759 disk accesses (TKPROF), so it may be faster to scan all rows using MULTIBLOCK_IO_COUNT and reading a number of blocks in 1 I/O (just like Ross alredy mentioned).
You may try also to use parallel query here as well.

HTH.
Michael
icon12.gif  Re: SQL Performance [message #231507 is a reply to message #231362] Tue, 17 April 2007 06:12 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Hi,
How about creating Function Based Index(FBI)? Since function is used in WHERE clause, It will bring down the response time.
Re: SQL Performance [message #231540 is a reply to message #231507] Tue, 17 April 2007 07:45 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So you're selecting 6.2M rows and it's taking a long time. Well what did you expect? This is a classic example of not providing all of the information up-front. If you had of told us there were 6M rows for that date, you would have had your answer 6 days ago.

>10% is definitely too great a proportion of the table to read via an index. This is discussed further in this article. You need to do a full table scan.

As Michael suggested earlier, you could include ACCRUED_INTEREST in the index to avoid the table access, this would be faster than the full table scan.

Sorry about that link on fragmented indexes, it is missing some info. The rest of it is here. You need to compare the results to the expected number of rows per block.

Having said that, your problem is with volume, not fragmentation.

Ross Leishman
Previous Topic: efficiently populating tables
Next Topic: SQL Tuning
Goto Forum:
  


Current Time: Tue Dec 06 16:20:37 CST 2016

Total time taken to generate the page: 0.10775 seconds