Major performance issue since upgrade to 10g from 9i

[Kenton]

Hello ...
A query that used to take 20 mins to complete under 9i now does not return a result after 24 hours.

Looking at the CBO execution plans we get ...
for 9i
[tt]-------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| INSERT STATEMENT | | 0 | 0 | 7359 | | | | | |
| SORT GROUP BY | | 20 | 3700 | 6559 | | | | | |
| FILTER | | 0 | 0 | 0 | | | | | |
| NESTED LOOPS | | 20 | 3700 | 6555 |9949,2|P->S |QC (RANDOM)| | |
| HASH JOIN | | 20 | 2460 | 6552 |9949,2|PCWP | | | |
| NESTED LOOPS | | 135 | 7965 | 3 |9949,1|P->P |BROADCAST | | |
| TABLE ACCESS BY INDEX RO | D_PERIOD | 1 | 22 | 2 |9949,0|S->P |BROADCAST | | |
| INDEX UNIQUE SCAN | PK_D_PERIOD | 1 | 0 | 1 | | | | | |
| TABLE ACCESS FULL | D_AGENT_INSURER | 135 | 4995 | 1 |9949,1|PCWP | | | |
| PARTITION RANGE ALL | | 0 | 0 | 0 |9949,2|PCWP | | 1 | 8 |
| TABLE ACCESS FULL | F_POLICY | 8072 | 504K | 6549 |9949,2|PCWP | | 1 | 8 |
| TABLE ACCESS BY INDEX ROWI | D_EMPLOYER | 1 | 62 | 1 |9949,2|PCWP | | | |
| INDEX UNIQUE SCAN | PK_D_EMPLOYER | 1 | 0 | 0 |9949,2|PCWP | | | |
| SORT AGGREGATE | | 1 | 20 | 0 | | | | | |
| PARTITION RANGE SINGLE | | 0 | 0 | 0 | | | | KEY | KEY |
| TABLE ACCESS BY LOCAL IND | F_POLICY | 1 | 20 | 40 | | | | KEY | KEY |
| INDEX RANGE SCAN | F_POLICY_FK_D_POL_IDX| 39 | 0 | 3 | | | | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------[/tt]

and under 10g:
[tt]------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop |
------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0 | INSERT STATEMENT | | | | 6 | | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 238 | 6 | 00:00:01 |:Q1004| P->S |QC (RANDOM)| | |
| 3 | HASH GROUP BY | | 1 | 238 | 6 | 00:00:01 |:Q1004| PCWP | | | |
| 4 | PX RECEIVE | | 1 | 238 | 6 | 00:00:01 |:Q1004| PCWP | | | |
| 5 | PX SEND HASH | :TQ10003 | 1 | 238 | 6 | 00:00:01 |:Q1003| P->P |HASH | | |
| 6 | HASH GROUP BY | | 1 | 238 | 6 | 00:00:01 |:Q1003| PCWP | | | |
| 7 | VIEW | | 1 | 238 | 5 | 00:00:01 |:Q1003| PCWP | | | |
| 8 | FILTER | | | | | |:Q1003| PCWC | | | |
| 9 | SORT GROUP BY | | 1 | 422 | 5 | 00:00:01 |:Q1003| PCWP | | | |
| 10 | PX RECEIVE | | 1 | 422 | 5 | 00:00:01 |:Q1003| PCWP | | | |
| 11 | PX SEND HASH | :TQ10002 | 1 | 422 | 5 | 00:00:01 |:Q1002| P->P |HASH | | |
| 12 | SORT GROUP BY | | 1 | 422 | 5 | 00:00:01 |:Q1002| PCWP | | | |
| 13 | NESTED LOOPS | | 1 | 422 | 4 | 00:00:01 |:Q1002| PCWP | | | |
| 14 | NESTED LOOPS | | 1 | 351 | 4 | 00:00:01 |:Q1002| PCWP | | | |
| 15 | PX RECEIVE | | | | | |:Q1002| PCWP | | | |
| 16 | PX SEND BROADCAST | :TQ10001 | | | | |:Q1001| P->P |BROADCAST | | |
| 17 | NESTED LOOPS | | 1 | 268 | 4 | 00:00:01 |:Q1001| PCWP | | | |
| 18 | NESTED LOOPS | | 1 | 222 | 4 | 00:00:01 |:Q1001| PCWP | | | |
| 19 | BUFFER SORT | | | | | |:Q1001| PCWC | | | |
| 20 | PX RECEIVE | | | | | |:Q1001| PCWP | | | |
| 21 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P |BROADCAST | | |
| 22 | TABLE ACCESS BY INDEX ROWID | D_PERIOD | 1 | 30 | 2 | 00:00:01 | | | | | |
| 23 | INDEX UNIQUE SCAN | PK_D_PERIOD | 1 | | 1 | 00:00:01 | | | | | |
| 24 | PX BLOCK ITERATOR | | 1 | 192 | 2 | 00:00:01 |:Q1001| PCWC | | 1 | 8 |
| 25 | TABLE ACCESS FULL | F_POLICY | 1 | 192 | 2 | 00:00:01 |:Q1001| PCWP | | 1 | 8 |
| 26 | TABLE ACCESS BY INDEX ROWID | D_AGENT_INSURER | 1 | 46 | 0 | |:Q1001| PCWP | | | |
| 27 | INDEX UNIQUE SCAN | PK_D_AGENT_INSURER| 1 | | 0 | |:Q1001| PCWP | | | |
| 28 | PX BLOCK ITERATOR | | 1 | 83 | 0 | |:Q1002| PCWC | | KEY | KEY |
| 29 | TABLE ACCESS FULL | F_POLICY | 1 | 83 | 0 | |:Q1002| PCWP | | KEY | KEY |
| 30 | TABLE ACCESS BY INDEX ROWID | D_EMPLOYER | 1 | 71 | 1 | 00:00:01 |:Q1002| PCWP | | | |
| 31 | INDEX UNIQUE SCAN | PK_D_EMPLOYER | 1 | | 1 | 00:00:01 |:Q1002| PCWP | | | |
------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+[/tt]


The tables and indexes are all the same in both databases (they are running concurrently so it's easy to test/compare)

I am at an absolute loss as to what is wrong. The 10G upgrade will need to be cancelled if this is not resolved.

Can anyone out there help me?



Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[Turkbear]

Hi,
Can you post the query? 10 and 9 process some Subqueries and joins differently - especially Right and Left Outer ones..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

Solaris/HPUX/AIX?

I remember a bug on these platforms which caused the same issue. The solution was to set one of the optimizer "hidden" parameters like:

_optimizer_push_pred_cost_based=false

Or use the following hint:

OPTIMIZER_FEATURES_ENABLED=9.2.0



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[taupirho]

Which optimisation were you using on 9, rule or cost. Is it the same on 10g?


In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Also, let's confirm the freshness of your statistics with this query:

select trunc(last_analyzed),count(*)
from user_tables
group by trunc(last_analyzed);

Please post the results here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Kenton]

Thanks everyone ...
here are my answers:
Turkbear ...

   insert into S_POLICY_BY_PRY
        select fpol.policy_wca_id,
               fpol.AGENT_INSURER_ID,
               fpol.EMPLOYER_ID,
               fpol.SOURCE_POLICY_NUMBER,
               fpol.POLICY_RENEWAL_YEAR,
               fpol.SOURCE_INSURER_NUMBER Agent_Insurer_No,
               dagi.NAME Agent_Insurer_Name,
               demp.LEGAL_NAME Employer_Legal_Name,
               demp.TRADING_NAME Employer_Trading_Name,
               demp.ABN Employer_ABN,
               sum(fpol.BASIC_TARIFF_PREMIUM_AMT),
               sum(fpol.EXPERIENCE_PREMIUM_AMT),
               sum(fpol.TOTAL_PREMIUM_PLUS_GST_AMT),
               sum(fpol.CUMULATIVE_PREMIUM_RECEIVED),
               fpol.EMPLOYER_SIZE_LEVEL_1_DESC
        from   f_policy          fpol,
               d_agent_insurer   dagi,
               d_employer        demp,
               d_period          dper
        where  dagi.AGENT_INSURER_ID    = fpol.AGENT_INSURER_ID
          and  demp.EMPLOYER_ID         = fpol.EMPLOYER_ID  
          and  fpol.policy_reportable_fg = 'Y'
--          and  fpol.LATEST_IMAGE_FG     = 'Y'  -- wrong - should be latest image per PRY as in next line
          and    FPOL.TRANSACTION_DATE_ID = (select max(TRANSACTION_DATE_ID)
                                               from   bid.f_policy
                                              where  policy_renewal_year = fpol.policy_renewal_year  -- for partitioning
                                                and    policy_wca_id = fpol.policy_wca_id
                                                and    cover_commencement_date = fpol.cover_commencement_date)   
          and  dper.DATE_ID             = RUNDATE
          and (   (    dper.FINANCIAL_QTR = 4
                   and dper.DAY_IN_FINANCIAL_QTR = 91    -- If last day of fin year, use last 5 years 
                   and fpol.POLICY_RENEWAL_YEAR between substr(dper.FINANCIAL_YEAR_NAME,1,4) - 4 and substr(dper.FINANCIAL_YEAR_NAME,1,4))  
               or (  (   dper.FINANCIAL_QTR <> 4 
                      or dper.DAY_IN_FINANCIAL_QTR <> 91)-- If NOT last day of fin year, use last 5 years + part year 
                      and fpol.POLICY_RENEWAL_YEAR between substr(dper.FINANCIAL_YEAR_NAME,1,4) - 5 and substr(dper.FINANCIAL_YEAR_NAME,1,4)))
        group by fpol.policy_wca_id,
                 fpol.AGENT_INSURER_ID,
                 fpol.EMPLOYER_ID,
                 fpol.SOURCE_POLICY_NUMBER,
                 fpol.POLICY_RENEWAL_YEAR,
                 fpol.SOURCE_INSURER_NUMBER,
                 dagi.NAME,
                 demp.LEGAL_NAME,
                 demp.TRADING_NAME,
                 demp.ABN,
                 fpol.EMPLOYER_SIZE_LEVEL_1_DESC
         ;

LKBrwnDBA ... I'll try that hint and see how it goes

taupirho ... Sorry, I don't know. Is this a DBA thing or is there something I can do to find this out?

Mufasa ... All the tables were analysed since last load (can't run your script right now because I'm re-analysing after turning off parallelism on the F_POLICY table)

Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[Kenton]

Mufasa ... your results

TRUNC(LAS   COUNT(*)
--------- ----------
14/OCT/08          2
07/OCT/08          1
04/OCT/08          6
11/OCT/08          1
10/OCT/08          1
15/OCT/08          1
13/OCT/08         29

7 rows selected.

LKBrwnDBA .... tried the hint but it made no difference



Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[taupirho]

Well, the easiest way to test is to put the /*+ RULE */ hint
on your insert.
Run it and see if the run-time changes. If not replace it with the /*+ FIRST_ROWS */ hint which uses COST optimisation explicitly, re-run it again and see if there's a difference this time.


In order to understand recursion, you must first understand recursion.

 

[emblem]

Hi Kenton,

My first suggestion is to consider the selectivity of indexes on f_policy. If the number of rows to be inserted is a small fraction of the total rowcount of f_policy, perhaps a more selective index would permit a range scan instead of the full table scan your plan is using. A good index will contain most of the fields in your WHERE clause.

Since this is a data warehouse query, you could also try the /*+ star_transformation */ hint. And I have had good results using bitmap indexes in this situation.

good luck,
emblem

 

[Kenton]

Thanks for the help guys!!!

I will try your suggestions to try ans squeeze more life out of it but I have found an acceptable solution anyway.

I added the hint INDEX_SS(f_policy F_POLICY_FK_D_POL_IDX) to both the main query and nested subquery and it returned in 15 mins.

The plan cost jumped up to 74,000 but it worked.



Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)

 

[sem]

How about optimizer parameters optimizer_index_cost_adj and optimizer_index_caching. Are they the same in both databases?



Regards, Dima

http://seminihin.narod.ru

 

[Kenton]

Hi Sem

Same for both:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100


Thanks
Kenton

(Note: I must remember to add something witty & technical for my signature)