Slow Performance on Oracle Partitioned Table

[manishiiita]

I have been facing issues with a table on Oracle DB with ~30 Million Records. It has transactions data.

I have created Month Wise Partition, using Date field on the table. It is working fine when I perform any query on dates having same year (within couple of seconds). If I query anything with dates having two or more than one years, the query takes very long time (more than 10-15 minutes) and large space to execute.

I think, there is some problem with the partitions I have created. Can someone please guide on this? Thanks in advance for your help. Please me know if any further information required on this.

[LinusH]

Do you the same performance if you issue the query directly in Oracle?

[manishiiita]

Yes, we tried to run it from SQL developer facing same issue.

[LinusH]

Then I'm sorry, this is an Oracle tuning issue, not a SAS one.

Reach to your Oracle DBA's for table or query tuning, or to an Oracle community.

[SuryaKiran]

It might help us understand how your queries are running if you provide them, if possible can you provide your both queries.

You can also find more details in the log when you set the below options before running the queries. It you show you what queries are passed to database and how many records are fetched by SAS.

options sastrace=',,,d' sastraceloc=SASlog nostsuffix;

[manishiiita]

I am using above options while running SAS Query. Also, ran the query using SQL Developer and from there I have found this behavior. The queries are as follows:

/********** 1   ******************/
select TRN.TRANSACTION_KEY, ALT.TEMP_NUM_1_VALUE as alert_id
from FSK_TEMP_TABLE ALT
join FSK_ACCOUNT_KEYS_PTY KEYS on ALT.TEMP_NUM_1_VALUE = KEYS.alert_id
join FSK_REPLICATION_DATES DTE on ALT.TEMP_NUM_1_VALUE = DTE.alert_id
join fsc_account_event_fact FCT on FCT.account_key = KEYS.account_key
join FSC_TRANSACTION_DIM TRN on (FCT.transaction_key = TRN.transaction_key and (TRN.date_key between 20180105 and 20180520));

/**********  2 **********************/
select TRN.TRANSACTION_KEY, ALT.TEMP_NUM_1_VALUE as alert_id
from FSK_TEMP_TABLE ALT
join FSK_ACCOUNT_KEYS_PTY KEYS on ALT.TEMP_NUM_1_VALUE = KEYS.alert_id
join FSK_REPLICATION_DATES DTE on ALT.TEMP_NUM_1_VALUE = DTE.alert_id
join fsc_account_event_fact FCT on FCT.account_key = KEYS.account_key
join FSC_TRANSACTION_DIM TRN on (FCT.transaction_key = TRN.transaction_key and (TRN.date_key between 20171105 and 20180220));

For your further information on this, table FSC_TRANSACTION_DIM has ~33 Millions records, fsc_account_event_fact has no records, and all other tables have few hundreds of records. FSC_TRANSACTION_DIM is Monthly Partitioned using date key field with indexes on Date Key. Query #1 finishes within seconds, while Query #2 takes very long time however the date range is larger in Query #1. Query #2 goes out of TEMP tablespace (~62 GB space is there in TEMP). On looking into execution plan, I found that second query #2 is going through sort (Buffer Sort), attaching execution plan herewith.

[SuryaKiran]

Adding a where clause might limit the data retrial.

Check the log info how much amount of data movement is happening. Are you joining a SAS table with Oracle tables, then try considering some performance considerations like limiting the data retrial by where clause or using DBKEY= option or push your SAS table to Oracle and do in-database joins.

select TRN.TRANSACTION_KEY, ALT.TEMP_NUM_1_VALUE as alert_id
from FSK_TEMP_TABLE ALT
join FSK_ACCOUNT_KEYS_PTY KEYS on ALT.TEMP_NUM_1_VALUE = KEYS.alert_id
join FSK_REPLICATION_DATES DTE on ALT.TEMP_NUM_1_VALUE = DTE.alert_id
join fsc_account_event_fact FCT on FCT.account_key = KEYS.account_key
join FSC_TRANSACTION_DIM TRN on FCT.transaction_key = TRN.transaction_key 

Where TRN.date_key between 20171105 and 20180220;

Check my SAS paper: https://www.lexjansen.com/sesug/2018/SESUG2018_Paper-154_Final_PDF.pdf

[cmurugesan]

It might be late response.  Faced the same kind of issue in Oracle query when dealing with Partition.

TRN.date_key between 20180105 and 20180520 --> might cause full table scan.

use in clause by mentioning date key (dkey1,dkey2..etc). might help

[Patrick]

@manishiiita 

This is a 100% Oracle question where you should try to get support from an on-site DBA or Oracle developer - or then ask the question in an Oracle forum.

I've worked with Oracle partitions in the past and remember that defining the "correct" indexes together with the partitions makes a big difference. Something a DBA helped me with. Or may be some hint could instruct Oracle to choose another execution plan - or may-be it's just about updating the statistics..... 

It's eventually very worthwhile to try what @cmurugesan proposes.

[manishiiita · Accepted solution]

Thank you all for your responses.

This is a very old question. It was resolved long back with the help of support. Sorry for keeping it open for so long.

This problem was more of a Oracle related issue, as everyone mentioned here. Although, I had faced it in AML jobs (AGP). There were multiple aspects of this problem. First point we found was about the query being used. The queries optimised in latest hotfixes released (as recommended by support team). We have applied hotfixes in our system. Then, we have created partitions and added new index (Account_Key & Date_key, composite index) in above table 'Accout_Event_Fact'. DBA had also performed some analysis to optimise performance of database.

I feel, above index was most crucial here. 

[SASKiwi]

@manishiiita  - would be good if you could update the post as answered as well.