Home » RDBMS Server » Performance Tuning » how do oracle filter data? (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
icon7.gif  how do oracle filter data? [message #551953] Sun, 22 April 2012 21:18 Go to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
t_user(id,name,provcode,areacode,salary)
the table have no index,key,partiton.
it have 3,000,000 rows

the sql
select provcode,sum(salary)
from t_user
where provcode in (1,2,3,4,5)
group by provcode


the sql plant
----------------------------------------------------------
Plan hash value: 2578068701

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    26 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |        |     1 |    26 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_USER |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("PROVCODE"=1 OR "PROVCODE"=2 OR "PROVCODE"=3 OR
"PROVCODE"=4 OR "PROVCODE"=5)

Note
-----
- dynamic sampling used for this statement


I want it that filter step
1 read all block into buffer
2 filter no include rows block
3 fetch rows from include rows block into new buffer block
4 fetch columns from new buffer block into new buffer block
5 sort include a few columns block
6 goup by and sum rows
7 send result to USER



I want the oracle filter setp is True?

Why temp table is faster than direct sum()?

the temp table :
1 create t_user_temp(provcode,salary)
2 insert into t_user_temp
select provcode,salary
from t_user
where provcode in (1,2,3,4,5)
3 select provcode sum(salary)
from t_user_temp
Re: how do oracle filter data? [message #551954 is a reply to message #551953] Sun, 22 April 2012 21:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it have 3,000,000 rows
Then why does EXPLAIN PLAN show only 1 row?
If/when CBO does not have accurate statistics, it can produce incorrect/slow execution plan.
Re: how do oracle filter data? [message #551955 is a reply to message #551954] Sun, 22 April 2012 21:54 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
Thank you
The plan is empt table to test and now generation 120,000 rows

the new plan it is :


----------------------------------------------------------
Plan hash value: 2578068701

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     5 |    40 |   163   (3)| 00:00:04 |
|   1 |  HASH GROUP BY     |        |     5 |    40 |   163   (3)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T_USER | 19335 |   151K|   162   (2)| 00:00:04 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("PROVCODE"=1 OR "PROVCODE"=2 OR "PROVCODE"=3 OR
"PROVCODE"=4 OR "PROVCODE"=5)
Re: how do oracle filter data? [message #551956 is a reply to message #551955] Sun, 22 April 2012 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The plan is empt table to test and now generation 120,000 rows
>Rows=19335 from most recent PLAN;
which is still a 6 fold discrepancy.


>Why temp table is faster than direct sum()?
we have seen no proof above is true or reproducible.

Do the results change when the order of the tests are reversed?
What is the impact of having rows already in SGA for second & subsequent invocations?
Re: how do oracle filter data? [message #551957 is a reply to message #551956] Sun, 22 April 2012 22:20 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
i have see other tool to plan
the plan "TABLE ACCESS FULL" do filter data!
>Rows=19335 from most recent PLAN;
>which is still a 6 fold discrepancy.

SQL> select count(*) from t_user;

COUNT(*)
----------
120002

SQL> select count(*) from t_user where provcode in(1,2,3,4,5);

COUNT(*)
----------
19448
Re: how do oracle filter data? [message #551958 is a reply to message #551957] Sun, 22 April 2012 22:24 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
I have forget FLUSH SHARED_POOL at run procedure
Slow than direct sum()!
Re: how do oracle filter data? [message #551959 is a reply to message #551958] Sun, 22 April 2012 22:24 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
I want the oracle filter setp is True?

1 read all block into buffer
2 filter no include rows block
3 fetch rows from include rows block into new buffer block
4 fetch columns from new buffer block into new buffer block
5 sort include a few columns block
6 goup by and sum rows
7 send result to USER




Re: how do oracle filter data? [message #551960 is a reply to message #551959] Sun, 22 April 2012 22:31 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK, it is TRUE.
So what actually changes for you now?
Previous Topic: Oracle Apps/Software installed in a dB Server
Next Topic: How to Tune Long running queries?
Goto Forum:
  


Current Time: Fri Mar 29 01:56:11 CDT 2024