Home » RDBMS Server » Performance Tuning » Optimize sql (Oracle 10.2.0.3)
| Optimize sql [message #456648] |
Wed, 19 May 2010 02:02  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
The below sql taking more time in prod. The statistics are upto date.
Need your advise to tune the sql below:
SELECT q.inst_cde
, item_cde
, item_reg_nbr
, issue_date
, wo_cde
, issue_id
, doc_flag
, qual_year
FROM (SELECT items.item_cde
, item_reg_nbr
, items.issue_id
, wo.wo_cde
, (SELECT qual_year
FROM writer_tbl wt
, writer_qual_tbl wqt
WHERE wt.item_cde = items.item_cde
AND wt.writer_id = wqt.writer_id
AND wt.w_seq_nbr = (SELECT MIN (w_seq_nbr)
FROM writer_tbl wt2
WHERE wt2.item_cde = wt.item_cde)
AND wqt.wqt_seq_nbr = (SELECT MIN (wqt_seq_nbr)
FROM writer_qual_tbl dg1
WHERE dg1.writer_id = wt.writer_id)) qual_year
, (SELECT inst_cde
FROM inst_tbl
WHERE inst_id = items.inst_id UNION
SELECT inst_cde
FROM inst_tbl
WHERE inst_id = (SELECT inst_id
FROM inst_instr_tbl ins
, trans_tbl st
WHERE st.in_id = ins.in_id
AND st.tran_cde = items.tran_cde)) inst_cde
, (SELECT MAX (TRUNC (created_dte))
FROM route_wo_tbl
WHERE wo_cde IN ('C', 'I')
AND w_id = items.w_id) issue_date
, (SELECT DECODE (COUNT (1)
, 0, 'N'
, 'Y'
)
FROM type_tbl
WHERE item_cde = items.item_cde
AND type_cde = 'DOC') doc_flag
FROM items_tbl items
, wo_tbl wo
WHERE issue_id <= ANY (7890, 4567,8700)
AND items.w_id = wo.w_id
AND wo.wo_cde IN ('C', 'R')
AND NOT EXISTS (
SELECT 1
FROM rule_tbl_a rta
WHERE rta.item_cde = items.item_cde
AND rta.rta_cde IN ('001', '002', '003', '006', '008', '009')
AND TRUNC (SYSDATE) BETWEEN TRUNC (rta.start_dte) AND TRUNC (rta.end_dte))
AND NOT EXISTS (
SELECT 1
FROM rule_tbl_b rtb
WHERE rtb.item_cde = items.item_cde
AND rtb.rtb_cde IN ('E', 'N', 'R','X')
AND TRUNC (SYSDATE) BETWEEN TRUNC (rtb.start_dte) AND TRUNC (rtb.end_dte))) q
, institution_a insta
WHERE q.inst_cde = insta.inst_cde
AND ( TO_DATE (q.issue_date,'DD/MM/YYYY') BETWEEN issue_start_dte1 AND issue_end_dte
OR (TO_DATE (q.issue_date, 'DD/MM/YYYY') BETWEEN issue_start_dt2 AND SYSDATE));
PLAN_TABLE_OUTPUT
Plan hash value: 582423559
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8166 | 1244K| | 29207 (5)| 00:05:51 |
| 1 | SORT UNIQUE | | 2 | 18 | | 9 (78)| 00:00:01 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | inst_tbl | 1 | 9 | | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | inst_cnst_pk01 | 1 | | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | inst_tbl | 1 | 9 | | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | inst_cnst_pk01 | 1 | | | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 17 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | trans_tbl | 1 | 9 | | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | trans_cnst_pk01 | 1 | | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | inst_instr_tbl | 2950 | 23600 | | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | ins_cnst_pk01 | 1 | | | 0 (0)| 00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 16 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | route_wo_tbl | 1 | 16 | | 6 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | rwt_indx_fk01 | 3 | | | 3 (0)| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 11 | | | |
|* 16 | INDEX UNIQUE SCAN | type_cnst_ui01 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 17 | FILTER | | | | | | |
| 18 | HASH GROUP BY | | 1 | 71 | | 13 (8)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | writer_qual_tbl | 1 | 29 | | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 71 | | 9 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 42 | | 7 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | writer_tbl | 1 | 27 | | 4 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | wt_cnst_ui01 | 1 | | | 3 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 9 | | | |
| 25 | FIRST ROW | | 1 | 9 | | 3 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN (MIN/MAX)| wt_cnst_ui01 | 1 | 9 | | 3 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | writer_qual_tbl | 1 | 15 | | 3 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | wqt_indx_fk01 | 1 | | | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | wqt_indx_fk01 | 1 | | | 2 (0)| 00:00:01 |
|* 30 | HASH JOIN | | 8166 | 1244K| | 29207 (5)| 00:05:51 |
| 31 | TABLE ACCESS FULL | institution_a | 347 | 7287 | | 3 (0)| 00:00:01 |
| 32 | VIEW | | 1635K| 210M| | 29163 (5)| 00:05:50 |
|* 33 | HASH JOIN RIGHT ANTI | | 1635K| 109M| | 29163 (5)| 00:05:50 |
|* 34 | TABLE ACCESS FULL | rule_tbl_b | 493 | 10846 | | 796 (17)| 00:00:10 |
|* 35 | HASH JOIN RIGHT ANTI | | 1635K| 74M| | 28327 (4)| 00:05:40 |
|* 36 | TABLE ACCESS FULL | rule_tbl_a | 2072 | 37296 | | 2177 (20)| 00:00:27 |
|* 37 | HASH JOIN | | 1637K| 46M| 1544K| 26109 (3)| 00:05:14 |
|* 38 | TABLE ACCESS FULL | wo_tbl | 82900 | 566K| | 702 (4)| 00:00:09 |
|* 39 | TABLE ACCESS FULL | items_tbl | 1637K| 35M| | 22533 (3)| 00:04:31 |
-----------------------------------------------------------------------------------------------------------------------
Other information:
select count(*) from items_tbl
--3154036
select count(*) from wo_tbl
--299392
SELECT count(*) FROM rule_tbl_a
--1076964
SELECT count(*) FROM rule_tbl_b
--320468
select count(*) from institution_a
--347
Index info on some of the tables are:
UNIQUE INDEX items_cnst_ui01 ON items_tbl
(item_reg_nbr)
UNIQUE INDEX items_cnst_pk01 ON items_tbl
(item_cde)
INDEX items_indx_fk02 ON items_tbl
(inst_id)
INDEX items_indx_fk01 ON items_tbl
(w_id)
items_indx_fk04 ON items_tbl
(tran_cde)
INDEX items_indx_fk03 ON items_tbl
(issue_id)
INDEX rwt_indx_fk02 ON route_wo_tbl
(wo_cde)
INDEX rwt_indx_fk01 ON route_wo_tbl
(w_id)
UNIQUE INDEX rta_cnst_ui01 ON rule_tbl_a
(rta_cde, start_dte, item_cde)
INDEX rta_indx_fk02 ON rule_tbl_a
(rta_cde)
INDEX rta_indx_fk01 ON rule_tbl_a
(item_cde)
UNIQUE INDEX rtb_cnst_ui01 ON rule_tbl_b
(rtb_cde, start_dte, item_cde)
INDEX rtb_indx_fk01 ON rule_tbl_b
(item_cde)
INDEX rtb_indx_fk02 ON rule_tbl_b
(rtb_cde)
Thanks for the help!
Regards
Ved
|
|
|
|
| Re: Optimize sql [message #456654 is a reply to message #456648] |
Wed, 19 May 2010 02:20  |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
What about the Ram size?
And Your memory parameter settings?(PGA,hash are size etc....)
What happend when you run the sql advisor for this?
What about the (input and out put operations ) and temp space utilization
sriram 
[Updated on: Wed, 19 May 2010 02:27] Report message to a moderator |
|
|
|
| Re: Optimize sql [message #456659 is a reply to message #456648] |
Wed, 19 May 2010 02:34 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
TABLE ACCESS FULL | rule_tbl_b | 493 | 10846 | | 796 (17)| 00:00:10 |
|* 35 | HASH JOIN RIGHT ANTI | | 1635K| 74M| | 28327 (4)| 00:05:40 |
|* 36 | TABLE ACCESS FULL | rule_tbl_a
I used index hint to avoid ful table scan for the above and was been able to tune to make performance a bit better but still it taking more time.
[code]
37 | HASH JOIN | | 1637K| 46M| 1544K| 26109 (3)| 00:05:14 |
|* 38 | TABLE ACCESS FULL | wo_tbl | 82900 | 566K| | 702 (4)| 00:00:09 |
|* 39 | TABLE ACCESS FULL | items_tbl | 1637K| 35M| | 22533 (3)| 00:04:31 |
-----------------------------------------------------------------------------------------------------------------------
Can we do something for the above to make it better?
Thanks
Ved
[Updated on: Wed, 19 May 2010 02:36] Report message to a moderator |
|
|
|
| Re: Optimize sql [message #456660 is a reply to message #456654] |
Wed, 19 May 2010 02:38 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The scalar subqueries in the SELECT clause are the problem.
See this article for why. You need to get rid of them and implement them as joins in the FROM clause.
Ross Leishman
|
|
|
|
| Re: Optimize sql [message #456661 is a reply to message #456660] |
Wed, 19 May 2010 02:53 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Thanks Ross for the reply. Is there any hint I can use to make it optimized for quick fix?
Many thanks for the advise!
Regards
Ved
|
|
|
|
|
|
| Re: Optimize sql [message #456676 is a reply to message #456661] |
Wed, 19 May 2010 04:01 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Some other thoughts on the code:
Isn't this:
issue_id <= ANY (7890, 4567,8700)
And do you really need all the trunc's? They'll stop you using indexes on dates.
Also why are you to_dateing issue_date - isn't it a date already?
|
|
|
|
| Re: Optimize sql [message #456684 is a reply to message #456676] |
Wed, 19 May 2010 04:14 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Also this:
(SELECT inst_cde
FROM inst_tbl
WHERE inst_id = items.inst_id UNION
SELECT inst_cde
FROM inst_tbl
WHERE inst_id = (SELECT inst_id
FROM inst_instr_tbl ins
, trans_tbl st
WHERE st.in_id = ins.in_id
AND st.tran_cde = items.tran_cde)) inst_cde
Looks like an ORA-01427 waiting to happen.
|
|
|
|
| Re: Optimize sql [message #456695 is a reply to message #456684] |
Wed, 19 May 2010 04:33 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
No this was written to handle if inst_id is null from the first sql.
Thanks Ross for the reply but it did not help much.I tried other alternative hint as well mentioned in the link
but it did not help! Any other suggestion?
Good Catch Cookie.But still it did not help much
Thanks
Ved
|
|
|
|
|
|
| Re: Optimize sql [message #456701 is a reply to message #456695] |
Wed, 19 May 2010 05:00 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Its_me_ved wrote on Wed, 19 May 2010 10:33No this was written to handle if inst_id is null from the first sql.
Well it doesn't do that. So you've got a bug you need to fix.
Its_me_ved wrote on Wed, 19 May 2010 10:33
Thanks Ross for the reply but it did not help much.I tried other alternative hint as well mentioned in the link
but it did not help! Any other suggestion?
Ross's main advice was to rewrite the query to remove all the sub-queries (or as many as possible), have you done that?
|
|
|
|
| Re: Optimize sql [message #456740 is a reply to message #456660] |
Wed, 19 May 2010 07:04 |
 |
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
rleishman wrote on Wed, 19 May 2010 09:38The scalar subqueries in the SELECT clause are the problem.
See this article for why. You need to get rid of them and implement them as joins in the FROM clause.
Ross Leishman
This is not always true.
When the scalar subqueries are evaluating aggregations, if I follow your rule The OP should substitute each scalar subquery with an outer join to a subquery with a GROUP BY clause that groups aggregations for each join column, but there are cases where this operation introduces performance degradation.
Once you use aggregation too early you lose the chance to use indexes for consequent joins, perhaps performing the aggregations before filtering the data, and this may not be the desired behavior.
Here is a sample of what I intend
SQL> set autotrace traceonly
SQL> set timing on
SQL> set linesize 32767
SQL>
SQL>
SQL> create table my_objs as
2 select distinct object_name
3 from user_objects
4 /
Table created.
Elapsed: 00:00:00.04
SQL>
SQL> create table all_objs as
2 select owner,object_name,object_type
3 from dba_objects
4 /
Table created.
Elapsed: 00:00:00.61
SQL>
SQL>
SQL> create index all_objs_idx on all_objs (object_name) compress
2 /
Index created.
Elapsed: 00:00:00.31
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(ownname => user,tabname => 'MY_OBJS',cascade => true);
3 dbms_stats.gather_table_stats(ownname => user,tabname => 'ALL_OBJS',cascade => true);
4 end;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
SQL>
SQL>
SQL> select m.object_name,(
2 select count(*)
3 from all_objs a
4 where m.object_name = a.object_name
5 ) as cnt
6 from my_objs m
7 /
115 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1709469764
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1200 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | INDEX RANGE SCAN| ALL_OBJS_IDX | 2 | 42 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| MY_OBJS | 100 | 1200 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
135 consistent gets
0 physical reads
0 redo size
2729 bytes sent via SQL*Net to client
284 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
115 rows processed
SQL>
SQL> select m.object_name,a.cnt
2 from my_objs m
3 left outer join (
4 select object_name,count(*) as cnt
5 from all_objs
6 group by object_name
7 ) a on (m.object_name = a.object_name)
8 /
115 rows selected.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 4076793827
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | | 510 (2)| 00:00:07 |
|* 1 | HASH JOIN OUTER | | 100 | 4600 | | 510 (2)| 00:00:07 |
| 2 | TABLE ACCESS FULL | MY_OBJS | 115 | 1380 | | 3 (0)| 00:00:01 |
| 3 | VIEW | | 47674 | 1582K| | 506 (2)| 00:00:07 |
| 4 | HASH GROUP BY | | 47674 | 977K| 3648K| 506 (2)| 00:00:07 |
| 5 | TABLE ACCESS FULL| ALL_OBJS | 84614 | 1735K| | 109 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M"."OBJECT_NAME"="A"."OBJECT_NAME"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
475 consistent gets
0 physical reads
0 redo size
2793 bytes sent via SQL*Net to client
284 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
115 rows processed
SQL>
Look at increased number of consistent gets obtained after the use of the outer join: that higher value is determined by the fact that the aggregation was evaluated also for successively filtered records.
Bye Alessandro
|
|
|
|
| Re: Optimize sql [message #456777 is a reply to message #456648] |
Wed, 19 May 2010 08:39 |
|
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
In the first subquery you scan the tables writer_tbl and writer_qual_tbl twice while you could try to use first and last functions to get a similar result with a single scan on each table.
If you would have used table aliases it was possible to show you an alternative, but the following two links will point you to what I was talking about.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions056.htm#i1000901
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions071.htm#i1000905
I would also change the second subquery in order to change the use of the union with a nvl.
, nvl((SELECT inst_cde
FROM inst_tbl
WHERE inst_id = items.inst_id and rownum = 1),(
SELECT inst_cde
FROM inst_tbl
WHERE inst_id = (SELECT inst_id
FROM inst_instr_tbl ins
, trans_tbl st
WHERE st.in_id = ins.in_id
AND st.tran_cde = items.tran_cde and rownum = 1))) inst_cde
For the last subquery there is a better way to check for the existence of correlated a record in a table: the exists clause.
,case when exists (
SELECT *
FROM type_tbl
WHERE item_cde = items.item_cde
AND type_cde = 'DOC'
) then 'Y' else 'N' end as doc_flag
And the I suggest you to add a condition on start_date to the two last correlated subqueries to drive the optimizer in the choice of an index.
AND NOT EXISTS (
SELECT 1
FROM rule_tbl_a rta
WHERE rta.item_cde = items.item_cde
AND rta.rta_cde IN ('001', '002', '003', '006', '008', '009')
AND TRUNC (SYSDATE) BETWEEN TRUNC (rta.start_dte) AND TRUNC (rta.end_dte)
and rta.start_dte <= trunc(sysdate+1)
)
AND NOT EXISTS (
SELECT 1
FROM rule_tbl_b rtb
WHERE rtb.item_cde = items.item_cde
AND rtb.rtb_cde IN ('E', 'N', 'R','X')
AND TRUNC (SYSDATE) BETWEEN TRUNC (rtb.start_dte) AND TRUNC (rtb.end_dte)
and rtb.start_dte <= trunc(sysdate+1)
)
In addition the indexes you described
UNIQUE INDEX rta_cnst_ui01 ON rule_tbl_a
(rta_cde, start_dte, item_cde)
UNIQUE INDEX rtb_cnst_ui01 ON rule_tbl_b
(rtb_cde, start_dte, item_cde)
should have start_dte as last indexed column in order to permit an index range scan to perform the <= on start_dte.
so they would became
UNIQUE INDEX rta_cnst_ui01 ON rule_tbl_a
(rta_cde, item_cde, start_dte)
UNIQUE INDEX rtb_cnst_ui01 ON rule_tbl_b
(rtb_cde, item_cde, start_dte)
Bye Alessandro
/*
Edit
Changed conditions on correlated subqueries
*/
[Updated on: Wed, 19 May 2010 10:40] Report message to a moderator |
|
|
|
| Re: Optimize sql [message #456790 is a reply to message #456777] |
Wed, 19 May 2010 08:51 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
AND NOT EXISTS (
SELECT 1
FROM rule_tbl_b rtb
WHERE rtb.item_cde = items.item_cde
AND rtb.rtb_cde IN ('E', 'N', 'R','X')
AND TRUNC (SYSDATE) BETWEEN TRUNC (rtb.start_dte) AND TRUNC (rtb.end_dte)
and trunc(sysdate+1)<= rtb.start_dte
)
How's that going to work? sysdate has to be greater than start_dte.
Even if you made it -1 it won't work if the gap between the dates is greater than a day.
@Ved - Next time you post a SQL make sure all the columns have table aliases. If we don't know which columns come from which tables it drastically limits the advice we can give.
EDIT: typo
[Updated on: Wed, 19 May 2010 08:52] Report message to a moderator |
|
|
|
|
|
| Re: Optimize sql [message #457172 is a reply to message #456815] |
Fri, 21 May 2010 08:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Alessandro,
The example you showed above is not subject to my advice. By picking only a small number of rows out of ALL_OBJS, your example is a clear cut case of a "low-volume SQL". What I mean is that you are interested in a small number of rows from a large table. I went to some pains in the article to explain that the techniques described were for high-volume SQL.
Even though I qualify my statements against scalar sub-queries as being bad for high-volume, I tend to avoid them for low volume as well - mainly by personal preference rather than as a rule.
In your example, I would probably offer a PUSH_PREDICATE hint to give the optimizer a chance at the NESTED LOOPS join that you achieve with the scalar sub-query. In this way, I can simply achieve either plan if I find that I have misunderstood the distribution of data in my tables, rather than being locked in to a Nested Loops plan that I cannot control.
I guess the only way we'll know if this is a good plan for the OP is if he tries it.
Ross Leishman
|
|
|
|
| Re: Optimize sql [message #457175 is a reply to message #457172] |
Fri, 21 May 2010 09:04 |
|
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
There may be high-volume elaborations with the need of a intensive data filtering need too. And in those cases FTS are not optimal anyway.
Anyway I tried so many times PUSH_PRED over a group by on 10gR2 and older versions but it never worked as you said.
I still didn't have the opportunity to test it on a 11g but, if you had successful experiences with it, I may have missed something also if I followed the manual.
SQL> select /*+PUSH_PRED(a)*/m.object_name,a.cnt
2 from my_objs m
3 left outer join (
4 select object_name,count(*) as cnt
5 from all_objs
6 group by object_name
7 ) a on (m.object_name = a.object_name)
8 /
115 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4076793827
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | | 510 (2)| 00:00:07 |
|* 1 | HASH JOIN OUTER | | 100 | 4600 | | 510 (2)| 00:00:07 |
| 2 | TABLE ACCESS FULL | MY_OBJS | 115 | 1380 | | 3 (0)| 00:00:01 |
| 3 | VIEW | | 47674 | 1582K| | 506 (2)| 00:00:07 |
| 4 | HASH GROUP BY | | 47674 | 977K| 3648K| 506 (2)| 00:00:07 |
| 5 | TABLE ACCESS FULL| ALL_OBJS | 84614 | 1735K| | 109 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M"."OBJECT_NAME"="A"."OBJECT_NAME"(+))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
475 consistent gets
0 physical reads
0 redo size
2793 bytes sent via SQL*Net to client
284 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
115 rows processed
SQL>
Bye Alessandro
[Updated on: Fri, 21 May 2010 09:09] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Optimize sql [message #457229 is a reply to message #457216] |
Sat, 22 May 2010 03:41 |
|
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
Probably they noticed about this bad behavior and on the 11g they enhanced the optimizer. In my 11g I don't even need to specify hints for pushing predicate, while in 10g this doesn't happens.
It's something good to know.
11g
SQL> select *
2 from v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.03
SQL> set autotrace traceonly
SQL> select m.object_name,a.cnt
2 from my_objs m
3 left outer join (
4 select object_name,count(*) as cnt
5 from all_objs
6 group by object_name
7 ) a on (m.object_name = a.object_name)
8 /
33 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 634344269
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 858 | 36 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 33 | 858 | 36 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | MY_OBJS | 33 | 429 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | SORT AGGREGATE | | 1 | 25 | | |
|* 6 | INDEX RANGE SCAN | ALL_OBJS_IDX | 2 | 50 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(COUNT(*)>0)
6 - access("OBJECT_NAME"="M"."OBJECT_NAME")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
1381 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
SQL>
10g
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select m.object_name,a.cnt
2 from my_objs m
3 left outer join (
4 select object_name,count(*) as cnt
5 from all_objs
6 group by object_name
7 ) a on (m.object_name = a.object_name)
8 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3375894571
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | | 91 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 87 | | 91 (5)| 00:00:02 |
| 2 | TABLE ACCESS FULL | MY_OBJS | 1 | 8 | | 2 (0)| 00:00:01 |
| 3 | VIEW | | 9920 | 765K| | 88 (4)| 00:00:02 |
| 4 | HASH GROUP BY | | 9920 | 184K| 640K| 88 (4)| 00:00:02 |
| 5 | TABLE ACCESS FULL| ALL_OBJS | 12798 | 237K| | 19 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M"."OBJECT_NAME"="A"."OBJECT_NAME"(+))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
475 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+push_pred(a)*/m.object_name,a.cnt
2 from my_objs m
3 left outer join (
4 select object_name,count(*) as cnt
5 from all_objs
6 group by object_name
7 ) a on (m.object_name = a.object_name)
8 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3375894571
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | | 91 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 87 | | 91 (5)| 00:00:02 |
| 2 | TABLE ACCESS FULL | MY_OBJS | 1 | 8 | | 2 (0)| 00:00:01 |
| 3 | VIEW | | 9920 | 765K| | 88 (4)| 00:00:02 |
| 4 | HASH GROUP BY | | 9920 | 184K| 640K| 88 (4)| 00:00:02 |
| 5 | TABLE ACCESS FULL| ALL_OBJS | 12798 | 237K| | 19 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M"."OBJECT_NAME"="A"."OBJECT_NAME"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
475 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select m.object_name,a.cnt
2 from my_objs m
3 left outer join (
4 select /*+push_pred*/object_name,count(*) as cnt
5 from all_objs
6 group by object_name
7 ) a on (m.object_name = a.object_name)
8 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3375894571
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | | 91 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 87 | | 91 (5)| 00:00:02 |
| 2 | TABLE ACCESS FULL | MY_OBJS | 1 | 8 | | 2 (0)| 00:00:01 |
| 3 | VIEW | | 9920 | 765K| | 88 (4)| 00:00:02 |
| 4 | HASH GROUP BY | | 9920 | 184K| 640K| 88 (4)| 00:00:02 |
| 5 | TABLE ACCESS FULL| ALL_OBJS | 12798 | 237K| | 19 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M"."OBJECT_NAME"="A"."OBJECT_NAME"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
475 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Bye Alessandro
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 07:44:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
