Skip to Main Content

Application Development Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SSL & Portal Configuration

LBROCKSOct 3 2008 — edited Oct 9 2008
We have devloped a portal application based on version 10.2 which is running on non-SSL mode, now we trying to run the same application is=n SSL mode, but the login page comes with default 7002 port, but all reference image and redirect losing the SSL port number. Do we need to do any specific configuration to make it work.

Thanks

Comments

John Brady - UK

C. Boutet wrote:

DB Version: 9.2

OS : AIX 5.3

Below query currently takes 10 minutes to execute. It returns rougly 450 rows.Business want this query to complete much faster.

From the below execution plan, do you know which step is taking more time ? I want to know what is causing Full Table Scan for PDTCOST_CHARGE_MAP table in step2 shown in red below

Step2 of predicate section in the execution plan shows FTS

But, PDTCOST_CHARGE_MAP_PK index has both TRACKING_ID and TRACKING_ID_SERV in it in the correct order. Then why is the optimizer not choosing PDTCOST_CHARGE_MAP_PK index ? Stats are up-to-date in PDTCOST_CHARGE_MAP table.

Step 2 is a HASH JOIN in your execution plan, it is step 3 that is the Full Table Scan.

Assuming your indexes are okay (I don't have time to look at them all fully), the thing that strikes me is that the previous step in the execution plan - 4 HASH JOIN - is estimated to produce 11 million rows.  This is then joined to your 30 million row table.  Assuming at least a 1 to 1 row match on the join, then that is at least one third of the rows in the PDTCOST_CHARGE_MAP, and for that high volume of rows in the table Oracle is preferring the Full Table Scan i.e. it believes that most of the rows in the table will be used, so it is "cheaper" to read all of the data blocks of the table using multi-block reads then using an index and doing single row / block look up reads.

Maybe you should be looking at why step 7 NESTED LOOPS is estiimated to produce 11 million rows.  If this can be reduced somehow then the Optimizer may end up choosing the index for the final join.

EDITED: Corrected the reference to be to step 7 from the original mistype of 11.

Jonathan Lewis

That's quite an interesting execution plan - for those who haven't noticed the HASH JOIN at operation 2 has THREE child operations, the third being the SORT AGGREGATE at operation 13.

This is a case of something I've previously called the "missing filter" anomaly, combined with the "constant subquery" effect. I may blog about this post some time soon.

The query is inherently a problem query for the optimizer (and it doesn't make it any easier that you're running 9.2 rather than a more modern, more sophisticated optimizer).

The key issue is the predicate set:

       AND ( (P.BILLING_INACTIVE_DT IS NULL AND PCM.INACTIVE_DT IS NULL

OR (PCM.ACTIVE_DT = 

            (SELECT  MAX (ACTIVE_DT)   FROM PDTCOST_CHARGE_MAP PCM1 ))); 

You have to do the join to PCM for ALL the rows from P that you identify from the earlier predicates against BID et. al; you can't eliminate any P rows early on the billing_inactive_dt because you may still have to accept a row based on the PCM active_dt.

As John Brady then says - you need to examine the 11M rows at operation 7 (I think his reference to line 11 was just a mental glitch due to thinking ahead to 11M rows). If this estimate is accurate then you have a messy problem; if it's vastly overestimated then you can start to think about forcing Oracle into a nested loop join.

The messy problem arises because Oracle is not good at optimizing "simple predicate OR (subquery)" - I've written about this problem a couple of times, but only using very simple examples (  https://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/  ). Essentially you may have to rewrite the query as a UNION ALL query where the first half of the query doesn't include the subquery section, allowing the second half to be engineered with an "AND (subquery)" while eliminating rows previously identified from the first half.

I think your first steps, though, should simply be to examine two simpler queries - removing the subquery, and removing the join to PCM to see how many rows are returned

First query should include the predicate p.billing_active_date is null, the second query should not include this predicate.

You could then run a query which bring back the join, but not the subquery, and further check how many rows have pcm.inactive_dt null and not null.

Regards

Jonathan Lewis

John Brady - UK

First, Yes, I meant operation 7 not 11 in my previous reply, as Jonathan pointed out.  My fingers must have been ahead of my brain when I was typing that in, as I did it very quickly.

Your query joins 6 tables together:

  1. FROM SERVICE S, 
  2.       PDTCOST P,
  3.       PDTCOST_CHARGE_MAP PCM,
  4.       BILL_INVOICE_DETAIL BID,
  5.       BILL_INVOICE BI,
  6.       JDL_WORK_LIST JWL

You have only told us details about the PCM table, at 30 million rows and 2 GB in size.  What about the other 5 tables?  They all form part of the query and impact the execution plan produced by the Optimizer.  How many rows do each of them have, and what indexes?  This is the kind of information the Optimizer is using when it produces the execution plan.  If we don't know as much as the Optimizer does then it is unlikely that we can produce a better execution plan for you.

You only have 2 real filter conditions on tables, BID and PCM, the other conditions are all joins:

  1. AND BID.TYPE_CODE = 4 
  2. AND BID.AMOUNT != 0 
  3. AND BID.BILLING_LEVEL = 1 
  4. AND ( (P.BILLING_INACTIVE_DT IS NULL AND PCM.INACTIVE_DT IS NULL
  5. OR (PCM.ACTIVE_DT =  (SELECT  MAX (ACTIVE_DT)   FROM PDTCOST_CHARGE_MAP PCM1 ))); 

As Jonathan has pointed out there are complications involved with the last filter because it has an OR in it, and mixes different columns from different tables in each part.  And as Jonathan says you might be better off doing it as 2 separate queries and a UNION ALL of all of the matching rows, and getting each query to run efficiently.

We don't know how selective the filters on BID are - how many rows out of the total in the table will match those conditions?  From the names of the columns and the values used I would guess that these are not very selective, because those columns contain very few distinct values each, and are probably Indicator or Type data columns.  If this is true then an index on these columns may not help, as too many rows are expected to meet the condition.  The Optimizer will only use an index when the total cost of access is below that of a Full Table Scan, and the main factor affecting this is the number of matching rows, though there are other factors too such as the clustering factor of the index.

I'd also say that your condition on PCM.ACTIVE_DT uses a sub-query on the same table PCM again which has no WHERE clause in it i.e. the sub-query is not correlated and will produce one and only one value for the whole table.  To make the MAX and the join to it run quicker I would be looking at creating an index on PCM (ACTIVE_DT) i.e. the one column only, or at least an index with that as the leading, first column.  This would mean that the sub-query for the MAX can run quicker, and the join back on ACTIVE_DT can run quickly too.

The logical way to execute this query would be to start with BID, applying the filters to it, then joining out to the other tables.  Unfortunately although the execution plan does seem to start on BID (operation 14 INDEX RANGE SCAN on its PK) it does not apply the filter conditions until step 6, which is much later.  That is probably why there are 11 million rows estimated for step 7, before the filters are applied in step 6.  Note that these filters only reduce the row count estimate from 11M to 5.8M, which is only really halving the row count i.e. Oracle is estimating that half of the rows in the BID table will match the filter conditions on it in the query.

Depending on how many rows are in the other tables, which we don't know yet, and how selective the filters on BID are, which we also don't know, I would consider the following indexes if they don't already exist.

BID (TYPE_CODE, BILLING_LEVEL, AMOUNT, BILL_REF_NO, BILL_REF_RESETS, TRACKING_ID, TRACKING_ID_SERV, SUBSCR_NO, SUBSCR_NO_RESETS)

JWL (BILL_REF_NO, BILL_REF_RESETS)

BI (BILL_REF_NO, BILL_REF_RESETS)

P (TRACKING_ID, TRACKING_SERV_NO, BILLING_INACTIVE_DT)

PCM (TRACKING_ID, TRACKING_SERV_NO, INACTIVE_DT)

S (SUBSCR_NO, SUBSCR_NO_RESETS)

This would potentially let the Optimizer start on BID and then join out to the other tables using indexes.  But it would only do this if the filters on BID were selective enough - if too many rows are estimated to match in BID then it would do a Full Table Scan on BID and join out to other tables instead.  Which means you would end up with a similar execution plan to the one you currently have, and the row count estimates during the execution itself would still be high because the filters do not reduce the row count that much.

An alternative index to try on PCM as well would be on (ACTIVE_DT, TRACKING_ID, TRACKING_SERV_NO).  Potentially the execution plan could start on PCM instead and join out to the other tables.  But as Jonathan as pointed out, the filter on PCM.ACTIVE_DT is buried within an OR that references other columns from PCM and P.  And you are using a very old version of Oracle, and the Optimizer is probably not up to being able to handle both conditions in the OR simultaneously with a single, simple access to those tables.  So you could try such an index, but its benefit would be limited.

And another index to try would be on PCM (BILLING_INACTIVE_DT, TRACKING_ID, TRACKING_SERV_NO), assuming that TRACKING_ID and TRACKING_SERV_NO do not allow NULL values to be stored in them.  This would mean that such an index would actually contain entries for when BILLING_ACTIVE_DT is NULL, as the other 2 columns would always have a data value for each row.

If you could somehow simplify that last WHERE condition with the OR in the middle, then the Optimizer might be able to produce a better execution plan.  But you are also limited by how selective your filters are (how many or few rows in BID or PCM match those conditions), and how big the other tables are.

John Thorton

>It returns rougly 450 rows

CBO calculates the number of returned rows to by about 137K; which is incorrect by more than a factor of 100.

In V9 Oracle the CBO is/was still in its infancy. You may not be able to work around this inherent limitation.

Jonathan Lewis

In principle one step you will have to take is to write the query as a UNION ALL of two very similar queries, splitting the last two bits of your predicate into different pieces.

In the first half of the query you should have:

AND (P.BILLING_INACTIVE_DT IS NULL AND PCM.INACTIVE_DT IS NULL

In the second half you should have

AND (PCM.ACTIVE_DT =  (SELECT  MAX (ACTIVE_DT)   FROM PDTCOST_CHARGE_MAP PCM1 ))

AND (P.BILLING_INACTIVE_DT IS NOT NULL OR PCM.INACTIVE_DT IS NOT NULL)                 -- note that the AND in the middle here has changed to an OR

This will make it possible for the optimizer to do something efficient, but if the statistics still make the optimizer think its handling a large number of rows then this change on its own may not be enough.

Regards

Jonathan Lewis

unknown-7404

Please post the DDL for ALL of the tables and indexes.

  1. FROM SERVICE S, 
  2.        PDTCOST P, 
  3.        PDTCOST_CHARGE_MAP PCM, 
  4.        BILL_INVOICE_DETAIL BID, 
  5.        BILL_INVOICE BI, 
  6.        JDL_WORK_LIST JWL 
  7. WHERE  JWL.BILL_REF_NO = BID.BILL_REF_NO 
  8. AND JWL.BILL_REF_RESETS = BID.BILL_REF_RESETS 
  9. AND JWL.BILL_REF_NO = BI.BILL_REF_NO 
  10. AND JWL.BILL_REF_RESETS = BI.BILL_REF_RESETS 
  11. AND BID.TYPE_CODE = 4 
  12. AND BID.AMOUNT != 0 
  13. AND BID.BILLING_LEVEL = 1 
  14. AND BID.TRACKING_ID = P.TRACKING_ID 
  15. AND BID.TRACKING_ID_SERV = P.TRACKING_ID_SERV 
  16. AND BID.SUBSCR_NO = S.SUBSCR_NO 
  17. AND BID.SUBSCR_NO_RESETS = S.SUBSCR_NO_RESETS 
  18. AND P.TRACKING_ID = PCM.TRACKING_ID 
  19. AND P.TRACKING_ID_SERV = PCM.TRACKING_ID_SERV 
  20. AND ( (P.BILLING_INACTIVE_DT IS NULL AND PCM.INACTIVE_DT IS NULL
  21. OR (PCM.ACTIVE_DT = 
  22.             (SELECT  MAX (ACTIVE_DT)   FROM PDTCOST_CHARGE_MAP PCM1 ))); 

Note that there is no specific mention of: SERVICE, PDT_COST or BILL_INVOICE_DETAIL in the plan.

1. Service is just used in two joins to the same table - are those two columns available in an index for that table?

2. PDT_COST is used in two joins to the same table - but is also used in an 'IS NULL' predicate. Does Oracle need to scan the table to determine that NULL condition? Normal, single column indexes don't include null values so ask yourself how Oracle would determine if the value is null or not without scanning the entire table (using either a scan or hash probe).

Two of the questions the CBO, and you, always need to answer are:

1. Where is the data I need (for the projection, joins or predicates)? Table, index(s) or both

2. What is the most efficient way to get that data.

The two questions YOU (anyone) needs to ask yourself anytime you wonder why an FTS is being used are:

1. Is the table the ONLY source of the data?

2. What are the possible paths to getting that data.

Anytime you see predicates that include NULL checks or 'not equal' checks you need to look into those predicates re those questions above.

If the data you need is ONLY in a table there are only two ways to get to that data: full scan the table or probe the table using a value (e.g. primary key or other) from a literal or other table)

We need to see ALL of the DDL for the indexes, in particular, in order to rule out some of those issues.

AndrewSayer

Some good points to take on board by the others. No one seems to have mentioned the statistics on JDL_WORK_LIST though. You say:

"JDL_WORK_LIST is a small table with a size of 120 MB with 300,000 records"

But Oracle thinks that it has a cost of 2 to full table scan and it will return 2 rows. Oracle has chosen to begin the execution from this table on the grounds that it will only find 2 rows, if it realises it will return 300,000 rows then perhaps a different leading table will be chosen.

As you're on 9.2 you can enable row source execution statistics so you can actually determine where the time is going - currently you are guessing it's the full table scan, that might be correct but it also might be due to reads/writes with temp, it might be due to an aggressive nested loop. https://blogs.oracle.com/optimizer/entry/how_do_i_know_if  Explains how to get this information.

Looking at your query, there doesn't strike me as being a leading predicate to use, but I don't know the data. You know the data better than us, where would you expect the execution plan to lead from? How often do you expect to execute the query - it does look like something you might only run once a day, could you just kick off the execution 10 minutes earlier?

teits

Hi,

In Addition, make sure you try to avoid or reduce disk sort. check 1,2,4.

if necessary, you can increase your sort/pga related parameters.

Tobi

Pavan Kumar

Jonathan,

QQ - Why would Optimizer doesn't performed un-nesting in this case ? I mean as per your blog info, it may tried to re-written the query. I was trying to find out why it would have failed.

I hope its not 11.2 (perhaps 9.2)

- Pavan Kumar N

Jonathan Lewis

Unnesting typically means creating a "select distinct" inline view to use as the driving table in a join; but in this case the distinct (in fact single) active_dt can't be used to drive a join into PCM because there may be rows in that table where the date doesn't match but the other predicates would be satisfied.

Regards

Jonathan Lewis

C. Boutet

As you're on 9.2 you can enable row source execution statistics so you can actually determine where the time is going - currently you are guessing it's the full table scan

Hi Andrew,

Any idea how to generate Row source stats in 9i ? Although GATHER_PLAN_STATISTICS hint seems to be valid , DISPLAY_CURSOR is not present in 9i's DBMS_XPLAN

SQL> select * from v$version where rownum < 2;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

SQL>

SQL>

SQL>

SQL>

SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*) from PDTCOST_CHARGE_MAP where rownum < 60 ;

  COUNT(*)

----------

        59

SQL> set linesize 1000 pagesize 300

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'allstats last'));

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'allstats last'))

                                                    *

ERROR at line 1:

ORA-00907: missing right parenthesis

SQL> desc DBMS_XPLAN

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name                  Type                    In/Out Default?

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

TABLE_NAME                     VARCHAR2                IN     DEFAULT

STATEMENT_ID                   VARCHAR2                IN     DEFAULT

FORMAT                         VARCHAR2                IN     DEFAULT

SQL>

berx

If you want to emulate DBMS_XPLAN.DISPLAY_CURSOR in 9i, please check this post
Formatted SQL Plan Display in Oracle 9i | ora-solutions.net - Martin Decker

(there are similar descriptions out there, this was just my first hit).

I've seen people complaining about high SGA activity (latches) of such scripts, so please be careful.

hth

Martin

Jonathan Lewis

Commented on the hash join / 3rd child and general 'or subquery' issue here:  http://jonathanlewis.wordpress.com/2016/11/07/filter-subquery/

Regards

Jonathan Lewis

1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 6 2008
Added on Oct 3 2008
12 comments
606 views