Question
Are you using Oracle Streams?
Yes, actively in production
Yes, for test or less important systems
No, but we use GoldenGate
No
No idea or not applicable
Answer and see the results
DBA Top 10
1 M. Cadot 99600
2 B. Vroman 43700
3 A. Kavsek 39400
4 T. Boles 38200
5 P. Wisse 27500
6 Y. Naguib 23500
7 A. Khan 16550
8 J. PĂ©ran 16300
9 A. Hudspith 15850
10 F. Pachot 15150
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 54 now available

Feb 04, 2014
(recommended update: build 1403)
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered46916
Total active users29461
Act. users last 24h135
Act. users last hour0
Hits last week1932
Hits last month6836
Go up

Performance problem with Subquery factoring and scalar subqueries
Next thread: oracle security
Prev thread: reg function

Message Score Author Date
Hi I have the following query which after upgra...... Hrishiskesj samant Dec 30, 2006
Hi The plan looks like <pre> | Id | Operatio...... Hrishiskesj samant Dec 30, 2006
Hi Predicate information <pre> redicate Infor...... Hrishiskesj samant Dec 30, 2006
Why this: with pos_qry as ( select * from...... Younes Naguib Dec 30, 2006
I may also be the star_transformation, Try to ru...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Younes Naguib Dec 30, 2006
Is there an index on SKU table on sku_nbr? How is...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Zoran Hudec Dec 31, 2006
Hi You say the query ran in 30 mins before the ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Nick Havard Dec 31, 2006
Hi Younes ,Nick,Zoran Thank you very much for c...... Hrishiskesj samant Dec 31, 2006
Hi Whn I asked how the upgrade was done I meant...... Nick Havard Dec 31, 2006
Hi Nick The upgrade was done using oracle suppl...... Hrishiskesj samant Dec 31, 2006
Hi Zoran I tried this the way you suggested and...... Hrishiskesj samant Dec 31, 2006
Hi Nick Yes i normally run the query as a CTAS ...... Hrishiskesj samant Dec 31, 2006
Hi, In addition. I should verify whether any c...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Guy Lambregts Dec 31, 2006
Hi Nick,Younus,Guy,Zoran,Guy I think i was care...... Hrishiskesj samant Jan 02, 2007
Hi I traced the original query with 10046 trace...... Hrishiskesj samant Jan 04, 2007
Hi, this is a Parallel Query Slave event and is g...... Alessandro Deledda Jan 04, 2007
Hi I did a stat collection after the upgrade. ...... Hrishiskesj samant Jan 05, 2007
what about CPU cost before and after the upgrade? ...... Alessandro Deledda Jan 05, 2007
Hi Alessandro We have not enabled cpu costing. ...... Hrishiskesj samant Jan 06, 2007
Hi, if you believe the problem is the optimizer an...... Alberto Pedretti May 15, 2007

Follow up by mail Click here


Subject: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 30, 2006, 2767 days ago
Os info: Sun5.8
Oracle info: 9.2.0.7
Message: Hi

I have the following query which after upgrading my oracle version to 9.2.0.7 has suddenly become horrible going from 30 minutes to over 5 hours.

Any idea how i improve the response time


explain plan for
create table Hrishy parallel 8 nologging as
with pos_qry as
(
select * from FACTS.POS_TXN_DTL_PANEL p
),
dt_rng_qry as
(
select date_dt, qtr_cd
from day d, calendar_quarter cq
where cq.qtr_cd = 20063
and (d.date_dt = ADD_MONTHS((cq.qtr_end_dt+1), -12) OR d.date_dt = (qtr_end_dt+1))
)
SELECT (SELECT MAX(qtr_cd) from dt_rng_qry) qtr_cd, a.*
from
(
select cat_nbr, subcat_nbr, seg_nbr, brand_nbr,
xtra_card_nbr,
count(distinct to_char(date_dt, 'yyyymmdd hh24:mi:ss')
||to_char(p.store_nbr, '000000')||
to_char(p.xtra_card_nbr, '000000000')
||to_char(visit_nbr, '000000')) txn_cnt,
sum(extnd_scan_amt) extnd_scan_amt
from pos_qry p, sku s
where p.sku_nbr = s.sku_nbr
and p.date_dt between (select min(date_dt) from dt_rng_qry)
and (select max(date_dt)-(1/24/60/60) from dt_rng_qry)
group by cat_nbr, subcat_nbr, seg_nbr, brand_nbr, xtra_card_nbr
order by cat_nbr, subcat_nbr, seg_nbr, brand_nbr, xtra_card_nbr
) a

/

select * from table(dbms_xplan.display)


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 30, 2006, 2767 days ago
Message: Hi

The plan looks like

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |start| Pstop |-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1098K| 95M| | 96240 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | VIEW | | 2 | 26 | | 2 | | |
| 3 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68D3_9E9DB5ED | 2 | 26 | | 2 | | |
| 5 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 4 | RECURSIVE EXECUTION | SYS_LE_5_0 | | | | | | |
| 0 | INSERT STATEMENT | | 2 | 42 | | 2 | | |
| 1 | LOAD AS SELECT | | | | | | | |
| 2 | CONCATENATION | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 21 | | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| CALENDAR_QUARTER | 1 | 13 | | 1 | | |
|* 5 | INDEX UNIQUE SCAN | C_Q_PK | 1 | | | | | |
|* 6 | INDEX UNIQUE SCAN | DAY_DT_PK | 1 | 8 | | | | |
| 7 | NESTED LOOPS | | 1 | 21 | | 1 | | |
| 8 | TABLE ACCESS BY INDEX ROWID| CALENDAR_QUARTER | 1 | 13 | | 1 | | |
|* 9 | INDEX UNIQUE SCAN | C_Q_PK | 1 | | | | | |
|* 10 | INDEX UNIQUE SCAN | DAY_DT_PK | 1 | 8 | | | | |
| 6 | VIEW | | 1098K| 95M| | 96240 | | |
| 7 | SORT GROUP BY | | 1098K| 56M| 151M| 96240 | | |
|* 8 | HASH JOIN | | 1098K| 56M| | 92375 | | |
| 9 | TABLE ACCESS FULL | SKU | 195K| 4210K| | 298 | | |
| 10 | PARTITION RANGE ITERATOR | | | | | | KY | KEY |
|* 11 | TABLE ACCESS FULL | POS_TXN_DTL_PANEL | 1098K| 33M| | 92077 | KEY | KEY |
| 12 | SORT AGGREGATE | | 1 | 9 | | | | |
| 13 | VIEW | | 2 | 18 | | 2 | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68D3_9E9DB5ED | 2 | 26 | | 2 | | |
| 15 | SORT AGGREGATE | | 1 | 9 | | | | |
| 16 | VIEW | | 2 | 18 | | 2 | | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68D3_9E9DB5ED | 2 | 26 | | 2 | | |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 30, 2006, 2767 days ago
Message: Hi

Predicate information

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

5 - access("CQ"."QTR_CD"=20063)
6 - access("D"."DATE_DT"="CQ"."QTR_END_DT"+1)
9 - access("CQ"."QTR_CD"=20063)
10 - access("D"."DATE_DT"=ADD_MONTHS("CQ"."QTR_END_DT"+1,-12))
filter(LNNVL("D"."DATE_DT"="CQ"."QTR_END_DT"+1))
8 - access("P"."SKU_NBR"="S"."SKU_NBR")
11 - filter("P"."DATE_DT">= (SELECT /*+ */ MIN("DT_RNG_QRY"."DATE_DT") FROM (SELECT /*+ CACHE_TEMP_TABLE
"T1") */
"T1"."C0" "DATE_DT","T1"."C1" "QTR_CD" FROM "SYS"."SYS_TEMP_0FD9D68D3_9E9DB5ED" "T1") "DT_RNG
QRY") AND "P"."DATE_DT"<=
(SELECT /*+ */ MAX("DT_RNG_QRY"."DATE_DT")-.00001157407407407407407407407407407407407407 FROM
(SELECT /*+
CACHE_TEMP_TABLE("T1") */ "T1"."C0" "DATE_DT","T1"."C1" "QTR_CD" FROM "SYS"."SYS_TEMP_0FD9D68
3_9E9DB5ED" "T1")
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Younes Naguib, Canada
Date: Dec 30, 2006, 2767 days ago
Message: Why this:
with pos_qry as
(
select * from FACTS.POS_TXN_DTL_PANEL p
),

Be aware that if a with subquery returns to many rows, a temporary table will be created (See your plan LOAD), which makes no-sense in this case. This query can be writen without the with clause.

With clause have to be used with caution and only when the subquery returns a small set of data!

Hope this helps,
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Younes Naguib, Canada
Date: Dec 30, 2006, 2767 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: I may also be the star_transformation,
Try to run it using:
alter session set star_transformation=temp_disable;
or
alter session set star_transformation=false;

Hope this helps,

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Zoran Hudec, Australia
Date: Dec 31, 2006, 2767 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Is there an index on SKU table on sku_nbr?
How is the table POS_TXN_DTL_PANEL indexed, and partitioned?

Consider retrieving and grouping all rows from POS_TXN_DTL_PANEL by date range, and then joining the result by SKU table, rather than joining and then grouping.

Also,
when presenting so complex statement - we can't guess where is a column comming from - you have to prefix all of them.

I agree with Younes regarding the usage of WITH.

In this query you have three full scans on one table! Regardless how small a table is.. that is really not a good practice.

Try this variation ( fix the column prefixes if I got them wrong)
Also try with and without the hint. Most likely it is not required, but I put it there just in case it is.

create table Hrishy parallel 8 nologging as
select /*+ push_subq */
maxqtr_cd qtr_cd,
P.cat_nbr, P.subcat_nbr, P.seg_nbr, P.brand_nbr,
P.xtra_card_nbr,
count(distinct to_char(P.date_dt, 'yyyymmdd hh24:mi:ss')
||to_char(p.store_nbr, '000000')||
to_char(p.xtra_card_nbr, '000000000')
||to_char(P.visit_nbr, '000000')) txn_cnt,
sum(P.extnd_scan_amt) extnd_scan_amt
from FACTS.POS_TXN_DTL_PANEL p, sku s,
(
select min(date_dt) mindate,max(date_dt) maxdate, MAX(qtr_cd) maxqtr_cd
from day d, calendar_quarter cq
where cq.qtr_cd = 20063
and (d.date_dt = ADD_MONTHS((cq.qtr_end_dt+1), -12) OR d.date_dt = (qtr_end_dt+1))
) D
where p.sku_nbr = s.sku_nbr
and p.date_dt between mindate and maxdate-(1/24/60/60)
group by P.cat_nbr, P.subcat_nbr, P.seg_nbr, P.brand_nbr, P.xtra_card_nbr
order by P.cat_nbr, P.subcat_nbr, P.seg_nbr, P.brand_nbr, P.xtra_card_nbr
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Nick Havard, United Kingdom
Date: Dec 31, 2006, 2766 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi

You say the query ran in 30 mins before the upgrade and 5 hours afterwards!

What was the version pre-upgrade? How did you upgrade?

Are you able to get an explain plan on a database of the original version?

What other changes have taken place in the environment?

Nick.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 31, 2006, 2766 days ago
Message: Hi Younes ,Nick,Zoran

Thank you very much for comming to my rescue.It was really wunderful of you working out suggestions.

Now comming to the answer's
The star_transaformation parameter was always disabled
The POS_TXN_DTL_PANEL has over 2 billion rows and is partitioned by date
SKU table has a index on the sku_nbr column but if hint oracel to use the index the plan looks even uglier with a index full scan.
Are we sure about the load part and the huge temp table creation using a with clause then i am wundering how come this query rna in under 30 minutes in version 9.2.0.6


But the version earlier was 9.2.0.6 and in that version this query ran in 30 minutes but after the upgrade this running for over 4.5 hours.

The upgrade was done by the DBA's

I dont have a explain plan for the previous version unfortunately.

I dont have an idea of what other changes have taken place.

regards
Hrishy

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Nick Havard, United Kingdom
Date: Dec 31, 2006, 2766 days ago
Message: Hi

Whn I asked how the upgrade was done I meant what method was used; a "proper" upgrade or import/export. I'll assume the former using the Oracle supplied upgrade scripts. If it is import/export check your statistics. Its probably worth checking your statistics anyway. You can export the current statistics first if you want.

When you say "hint oracel to use the index the plan looks even uglier with a index full scan", you need to remember than full table scans are not always bad!

Do you nomally run the query as a CTAS operation?

What wait event occurs during the execution of the statement?

Nick.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 31, 2006, 2766 days ago
Message: Hi Nick

The upgrade was done using oracle supplied scripts and not export import.

The prominent wait event seems to be direct path read followed by db file sequential read but the major one seems to be direct path read.

regards
Hrishy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 31, 2006, 2766 days ago
Message: Hi Zoran

I tried this the way you suggested and the query does complete in 30 minutes.
But i have atleast 46 such queries i am not really keen on a rewrite.

regards
Hrishy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Dec 31, 2006, 2766 days ago
Message: Hi Nick

Yes i normally run the query as a CTAS operation.

regards
Hrishy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Guy Lambregts, Belgium
Date: Dec 31, 2006, 2766 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

In addition.
I should verify whether any changes are made at the level of
1. pga_aggregate_target
2. workarea_size_policy
3. sort_area_size
4. hash_area_size
as well on the optimizer parameters like
5. optimizer_mode
6. optimizer_features_enabled
7. optimizer_index_caching
8. optimizer_index_cost_adj
9. db_file_multiblock_read_count
Also I should consider whether system stats were gathered before and after the upgrade

Direct path read waits let me think on read performance ot temp segments ... If running with workarea_size_policy = auto ... I should try with ( I admit "try")
sql > alter session set workarea_size_policy=manual
sql > alter session set sort_area_size = 200000000
sql > alter session set hash_area_size = 200000000
sql > set autotrace traceonly explain
sql > run your statement again

I should throw an eye on the SKU table as well. Since the hash join between the smaller SKU table and the bigger FACTS.POS_TXN_DTL_PANEL results in temp segment usage I should wonder whether a fast full scan of a composite index on SKU could replace the full table scan of SKU. Whether this is relevant depends on the amount of columns in the SKU table compared with the one you really need in the query.

Regards
Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Jan 02, 2007, 2764 days ago
Message: Hi Nick,Younus,Guy,Zoran,Guy

I think i was careless on reporting about the wait event.

The wiat event that is predominant seems to be db_file_scattered read.

So what is your take on me bumping up the db_file_multiblock_read_count parameter from the current of 32 and running this query ?

regards
Hrishy



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Jan 04, 2007, 2763 days ago
Message: Hi

I traced the original query with 10046 trace events and it looks like th wait event that is predominant is
''PX Deq: Table Q Normal'.

I wunder if parallel query is working at all for this particular query
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Alessandro Deledda, Italy
Date: Jan 04, 2007, 2762 days ago
Message: Hi,
this is a Parallel Query Slave event and is generally considered an idle event but if this is too high maybe you nedd to tune the Parallel Option..
you can trace 10053 to see any changes on CBO parameters from 9.2.0.6 to 9.2.0.7, you can check if your statistics are up to date, especially after an upgrade


Regards
Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Jan 05, 2007, 2761 days ago
Message: Hi

I did a stat collection after the upgrade.

Not sure what is that elusive optimizer parameter that changed between 9.2.0.6 and 9.2.0.7

regards
Hrishy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Alessandro Deledda, Italy
Date: Jan 05, 2007, 2761 days ago
Message: what about CPU cost before and after the upgrade?

Regards
Alessandro
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Hrishiskesj samant, India
Date: Jan 06, 2007, 2761 days ago
Message: Hi Alessandro

We have not enabled cpu costing.

regards
Hrishy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Performance problem with Subquery factoring and scalar subqueries
Author: Alberto Pedretti, Argentina
Date: May 15, 2007, 2631 days ago
Message: Hi, if you believe the problem is the optimizer and their new execution plans, why don't you set it back to what it was ?
Use alter system set optimizer_features_enable ='9.2.0.1' scope=spfile

That parameter is static so you need to restart your DB,
Plese note that valid values are:

alter system set optimizer_features_enable ='9.2.0.5' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value 9.2.0.5 for parameter optimizer_features_enable, must
be from among 9.2.0.1, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3,
8.1.0, 8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here