Home » RDBMS Server » Performance Tuning » Advice on Hints
| Advice on Hints [message #199906] |
Thu, 26 October 2006 14:09  |
ravgopal
Messages: 5
Registered: April 2005
Location: Texas
|
Junior Member |
|
|
Hi
I am running the following sql query and ending it with a error
ERROR at line 19:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
My database is of size 11GB and having 10GB of Temp tablespace. DBA are strict in increasing the temp tablespace for this database, since 10GB is more than enough for a 11GB database.
My QUERY :
select /*+ ORDERED*/
sec.DBPARENTPLANOGRAMKEY AS PLANOGRAMKEY
, prd.dbkey AS PRODUCTkey
, prd.ID
, st.STORENUMBER
, wk.NETSALES AS AVGNETSALES
from
JCPIKB.IX_STR_STORE st, --(1067)
JCPIKB.IX_FLR_FLOORPLAN flr, --(7125)
jcpikb.ix_spc_planogram spcpln, --(17570)
JCPIKB.IX_FLR_SECTION sec, --(121687)
JCPIKB.IX_STR_STORE_FLOORPLAN stfl, --(286592)
jcpikb.ix_spc_position sppos, --(503574)
JCPIKB.IX_SPC_PRODUCT prd --(512766)
JCPSYS.STORELINESALES wk --(21079563)
where flr.dbstatus = 1
and sppos.DBPARENTPLANOGRAMKEY = spcpln.dbkey
and sec.DBPARENTPLANOGRAMKEY = spcpln.dbkey
and prd.dbkey = sppos.DBPARENTPRODUCTKEY
and stfl.DBPARENTFLOORPLANKEY = flr.DBKEY
and flr.DBKEY = sec.DBPARENTFLOORPLANKEY
and st.DBKEY = stfl.DBPARENTSTOREKEY
and wk.STORE = st.STORENUMBER
AND wk.LINE = prd.ID
The number inside the bracket is the total number of records in the table. I did the following before running the query
1) Analysed the table
2) Checked whether all the fields are having indexes.
I am attaching a image which indicates the table links and Explain plan result.
Question : I want Oracle to use the tables and where clause in the order I have given. but if I see the explain plan it is not using the order which I have given. I tried giving /* + Ordered */ and /* + ordered_predicates */ but no use.
Any idea why the tables are not taken in the order I have specified in the FROM clause??
-
Attachment: QUERY.zip
(Size: 92.60KB, Downloaded 1276 times)
|
|
|
|
|
|
| Re: Advice on Hints [message #199910 is a reply to message #199907] |
Thu, 26 October 2006 14:55  |
ravgopal
Messages: 5
Registered: April 2005
Location: Texas
|
Junior Member |
|
|
Hi Anacedent,
Thanks for getting back.
I forgot to mention,
earlier I tried the same logic with these two tables
JCPIKB.IX_FLR_FLOORPLAN flr, --(7125)
jcpikb.ix_spc_planogram spcpln, --(17570)
but no improvement. All the DB parent Keys are 1:Many. hence oracle using the Hash join and consuming more space.
I will try with all the three tables which you have mentioned. Let us see is there any improvement.
Thanks
RG
|
|
|
|
| Re: Advice on Hints [message #199913 is a reply to message #199910] |
Thu, 26 October 2006 16:41 |
ravgopal
Messages: 5
Registered: April 2005
Location: Texas
|
Junior Member |
|
|
I tried moving all the four tables to where clause.
I have attached the new Explain plan for this query.
Here is new query -
select /*+ ORDERED*/
sec.DBPARENTPLANOGRAMKEY AS PLANOGRAMKEY
, prd.dbkey AS PRODUCTkey
, wk.NETSALES AS AVGNETSALES
, wk.UNITSALES AS SUMUNITSALES
, wk.UNITSALES AS AVGUNITSALES
, wk.UNITCOST AS AVGUNITCOST
, wk.NETSALES AS SUMNETSALES
, wk.AVGUNITS4WK AS SUMUNITS4WK
, wk.AVGSALES4WK AS SUMSALES4WK
, wk.AVGUNITS8WK AS SUMUNITS8WK
, wk.AVGSALES8WK AS SUMSALES8WK
, wk.AVGUNITS12WK AS SUMUNITS12WK
, wk.AVGSALES12WK AS SUMSALES12WK
, wk.FISCALWEEK AS FISCALWEEK
from
JCPSYS.STORELINESALES wk,
JCPIKB.IX_STR_STORE st,
JCPIKB.IX_SPC_PRODUCT prd,
JCPIKB.IX_FLR_SECTION sec
where
prd.dbkey = sec.DBPARENTPLANOGRAMKEY
and st.DBKEY = sec.DBPARENTFLOORPLANKEY
and wk.STORE = st.STORENUMBER
and wk.LINE = prd.ID
and exists (select 1 from jcpikb.ix_spc_position sppos
where sppos.DBPARENTPLANOGRAMKEY= sec.DBPARENTPLANOGRAMKEY
and sppos.DBPARENTPRODUCTKEY = prd.dbkey)
and exists (select 1 from jcpikb.ix_spc_planogram spcpln
where spcpln.DBKEY = sec.DBPARENTPLANOGRAMKEY)
and exists (select 1 from JCPIKB.IX_FLR_FLOORPLAN flr
where flr.dbstatus = 1 and flr.DBKEY = sec.DBPARENTFLOORPLANKEY)
and exists (select 1 from JCPIKB.IX_STR_STORE_FLOORPLAN stfl
where stfl.DBPARENTSTOREKEY = st.DBKEY
and stfl.DBPARENTFLOORPLANKEY = sec.DBPARENTFLOORPLANKEY)
-
Attachment: xplan4.txt
(Size: 5.27KB, Downloaded 1210 times)
|
|
|
|
| Re: Advice on Hints [message #199918 is a reply to message #199913] |
Thu, 26 October 2006 21:10 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
This suggestion of moving tables from the join into sub-queries - often combined with grandiose promises of performance improvements - comes up too often. It makes some critical and unjustified assumptions about the query:
- That the join-keys are indexed. If this assumption does not hold, you may inadvertently nest a FTS, which will be glacially slow.
- That the cardinality of the resulting sub-query is many-to-one or many-to-many with the outer query. For a nested (and indexed) EXISTS to perform better than an equivalent Nested Loops Indexed join, it must perform less IO. EXISTS can achieve this by dropping out after the first row is found. If the join is on a unique key, there will be only one row anyway - no improvement. However, if the cardinality of the sub-query IS many-to-one or many-to-many with the outer query, then the sub-query will be semantically different from the join - ie. the join will return more rows. If the outer query has a SELECT DISTINCT, GROUP BY, or UNION/INTERSECT/MINUS to eliminate duplicates, fine, otherwise the EXISTs will change the output.
Now I'm not saying that converting a join to a sub-query will NEVER help the query, just that it PROBABLY won't, and almost CERTAINLY won't if the above assumptions do not hold.
Now, on to the OP's question:
The thing with /*+ ORDERED */ is that the tables need to be joined in that order. Your have ST followed by FLR, but FLR is not joined to ST. I'm surprised Oracle ignored the hint. Usually it throws its hands in the air and gives you a cartesian product, which is essentially what you are asking it to do.
But this is not your problem. The diagram you attached is not laid out very well. This is easier (for me) to analyse:
PRD SPCPLN FLR ST>-+
| | | | |
+--+---+ +---+--+ +--+--+ | |
^ ^ ^ ^ ^ ^ ^ |
WK SPPOS SEC STFL |
v |
+-----------------------------+
So, lets start with ST as you asked the CBO to do. There are 1067 ST rows, each of which join to an average of 268 STFLR rows (thats 1067x268 = 285956 rows so far - ie. one row per STFLR). FLR is a unique join, so we can pick it up and still have 285956 rows. But now we join to SEC: on average 17 rows per FLR, so 285956 x 17 = 4861252 rows in our building result set. SPCPLN is a unique join, so in it goes with still just 4861252 rows. But each SPCPLN has on average 29 rows in SPPOS. Our result set is now 29 * 4861252 = 140976308 rows. Nearly there: join in PRD on a unique key - no extra rows. But, hang on, what this? WRK with an average of 41 rows per PRD (it also joins to ST, but that is many-to-many, so I can't get a cardinality estimate). 41 x 140976308 = 5,780,028,628 - almost 6 BILLION ROWS.
Is it any wonder its running out of Temp Space?
There is a fundamental problem with your query. I don't know what you are trying to achieve, but if you wanted to get all (or even a subset) of the rows in WK, and then pick up some additional detail from the surrounding tables, this won't do it. As you can see from the example above, every time you follow a one-to-many relationship, you multiply your result set. This query will return the same WK row THOUSANDS of times.
Since you are only trying to pick up colums from WK, PRD, SEC, and ST, it is very likely that there are many-to-one relationships between WK and those tables that you do not know about. It is just not reasonable to follow the path of joins you are using.
Here's what I'd do:
- Ask a colleage who knows the data model: "How do I join WK to SEC?" And if they answer "via PRD, SPPOS, and SPCPLN" then try another colleage.
- If that goes well, ask the same person: "Joining WK to ST on STORENUMBER gives me a many-to-many join. How do I make it many-to-one?"
When you have solved both of those problems, rewrite the query using only those tables, and only many-to-one joins. Problem solved.
Now, this is critically important. There is a lot of detail above; some of it is complex. I don't expect you to get it first time, but don't just post back stright-away and tell me you don't get it, because I can't explain it any better. Get together with a colleage and work through what I've said - many times if necessary. Two heads are better than one. It will all begin to make sense.
Ross Leishman
|
|
|
|
|
|
| Re: Advice on Hints [message #200393 is a reply to message #199907] |
Mon, 30 October 2006 12:57 |
ravgopal
Messages: 5
Registered: April 2005
Location: Texas
|
Junior Member |
|
|
Hi Guys,
I got the solution. I broke the query in to two different entity
a) combined rest of the tables in such a way that it selects only those fields which are required to join to WK table.
b) WK as a seperate table
Step1: Run the following query in the given order.
select /* +ORDERED */
prd.ID, st.STORENUMBER
from
JCPIKB.IX_STR_STORE st, --(
JCPIKB.IX_FLR_FLOORPLAN flr, --(7)
JCPIKB.IX_FLR_SECTION sec, --(5)
jcpikb.ix_spc_planogram spcpln, --(6)
jcpikb.ix_spc_position sppos, --(3)
JCPIKB.IX_STR_STORE_FLOORPLAN stfl, --(4)
JCPIKB.IX_SPC_PRODUCT prd --(2)
where st.DBKEY = stfl.DBPARENTSTOREKEY
and stfl.DBPARENTFLOORPLANKEY = flr.DBKEY
and flr.DBKEY = sec.DBPARENTFLOORPLANKEY
and sec.DBPARENTPLANOGRAMKEY = spcpln.dbkey
and spcpln.dbkey=sppos.DBPARENTPLANOGRAMKEY
and sppos.DBPARENTPRODUCTKEY=prd.dbkey
and flr.dbstatus = 1
The highlight changes what I made in this query is Re-arranged the tables in such a way that the table which is having minimum number of records as the driving table i.e store table as the driving table. Arranged the rest of the tables in ascending number of records order. So that table will have the maximum number of records.
Arrange the where clause in such a way that it filters each table in the same order how it is put in the from clause. That is filter the first table with the second table and second table with the third table ....
When this query is successfully run, we have achieved 80% of the result.
Now do an intersect with the WK table.
select wk.LINE, wk.store from JCPSYS.STORELINESALES wk
intersect
SELECT c.id ID, st.STORENUMBER STORENUMBER
FROM
JCPIKB.IX_STR_STORE st,
JCPIKB.IX_FLR_FLOORPLAN a,
JCPIKB.IX_FLR_SECTION b,
JCPIKB.IX_SPC_PLANOGRAM d,
JCPIKB.IX_SPC_POSITION e,
JCPIKB.IX_STR_STORE_FLOORPLAN stfl,
JCPIKB.IX_SPC_PRODUCT c
where
a.dbkey = b.dbparentfloorplankey
AND b.dbparentplanogramkey = d.dbkey
AND d.dbkey=e.dbparentplanogramkey
AND e.dbparentproductkey = c.dbkey
AND a.dbstatus =1
AND stfl.DBPARENTFLOORPLANKEY = a.DBKEY
and st.DBKEY = stfl.DBPARENTSTOREKEY
This is the result I wanted it. Now I can add the rest of the columns which I need to the result set either by altering the above query or use IN clause by making he result set as a table.
Learing from this tuning
1) Order of the table is must based on the number of records
2) the driving table should be the one which has minimum number of records
3) the where clause should be in such a way that it filters in the same order in which the tables are existing in the where clause. like first table with second table and second table with third table. Don't give joins to tables in randon order.
4)We can use the INTERSECT funtion to get the equijoin records.
Thanks for all those who has taken time to respond my question
Rg
|
|
|
|
| Re: Advice on Hints [message #200410 is a reply to message #200393] |
Mon, 30 October 2006 16:57 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| ravgopal wrote on Tue, 31 October 2006 05:57 | Learing from this tuning
1) Order of the table is must based on the number of records
2) the driving table should be the one which has minimum number of records
3) the where clause should be in such a way that it filters in the same order in which the tables are existing in the where clause. like first table with second table and second table with third table. Don't give joins to tables in randon order.
|
No, no, NO! You are clearly happy with your query now (so you can ignore the rest of my rant), but I can't abide posting false "learnings" without a response.
- Do not use the ORDERED hint with tables ordered by the number of records. If I join A to B and then B to C, but ask Oracle to join then in the order A,C,B; what do you think will happen in the intermediate step when A joins to C? Since there are no join predicates, you will get a cartesian join with every row in A joining to every row in C. You can only get away with this if A and C are relatively small (say, < 1000 rows). Even then its not that efficient.
Fortunately, you do not specify the ORDERED hint in your final SQL, so Oracle ignores the table order you specified and chooses its own order.
- The driving table should not necessarily be the one with the least rows. In fact, the absolute fastest way to join one large table to many small tables is to hash-join the lot with the large table as the driving table. The reason for this is because - if the small tables are small enough - they can all be hashed into memory without paging, and you can make a single pass through the large table.
Your join order should be dictated by where most of the filtering occurs. If you removed all filter clauses from the WHERE, leaving only joins, how many rows would be returned. Now treat the filter clauses one by one; which ones filter the most rows? The tables owning these filter columns will be the best at the beginning of the join, and then all other tables in order of join predicates, not in order of size.
- The order of join predicates in the WHERE clause makes no difference at all. The order of filter predicates is somewhat important. If you don't provide the ORDERED_PREDICATES hint, AND clauses are processed bottom-up, and OR predicates top-down. Here is a demonstration:
SQL> select * from dual where 1 = 1/0 and sysdate is null;
no rows selected
SQL> select * from dual where sysdate is null and 1 = 1/0;
select * from dual where sysdate is null and 1 = 1/0
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL> select * from dual where 1 = 1/0 or sysdate is not null;
select * from dual where 1 = 1/0 or sysdate is not null
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL> select * from dual where sysdate is not null or 1 = 1/0;
D
-
X
Your query is most likely doing EXACTLY the same thing as it used to, except you've mitigated the effect by removing the biggest table from the join. You have just replaced the DISTINCT with an INTERSECT, which also removes duplicates and hides the real problem.
I really, honestly think that you are missing some vital piece of information about the data model. Your query seems to want a list of all products and the stores in which they are sold. This seems so simple that I can't believe your data model doesn't support it. OK, so you might have to go through a few tables, but it should be a hierarchy, always moving up. Your query goes up and down though three different hierarchies.
Logically, it should be something like: A PRODUCT is sold in a particular SECTION of the store; that section is part of a FLOORPLAN, which in turn is part of a FLOOR, which in turn is part of a STORE. I'm not saying thats exactly how your model works, but it should be close because it makes sense.
To demonstrate how ridiculous your data model appears from that query, a FLOOR could belong to two or more STORES. Now this might make sense in a shopping mall, but if a product is sold in just ONE store in the mall, your query will return a result showing it was sold by EVERY store on the same floor of the mall.
Talk to some people - some users if you have to. They are the ones who use the system, and they will know how products hierarchically roll up into a store.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 13:58:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
