 very simple but very slow query [message #406659] |
Fri, 05 June 2009 04:16  |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
hi,i have such a simple query :
select a.userid,a.login_ip,b.area_code from user_login_srl a,dict_ip_range b
where a.login_ip between b.start_ip and end_ip;
it takes about 4 hours to complete,and cpu and mem utilization are very low.
related table info as follows :
create table user_login_srl
(userid number,
login_ip number,
login_dt date);
about 2M rows
create table dict_ip_range
(start_ip number,
end_ip number,
area_code number);
about 80k rows
can someone help me,thks in advance.
|
|
|
|
|
|
| Re: very simple but very slow query [message #406675 is a reply to message #406659] |
Fri, 05 June 2009 05:51  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What indexes are there on the two tables?
Also, do the following in SQL*plus and post the results:explain plan for
select a.userid
,a.login_ip
,b.area_code
from user_login_srl a
,dict_ip_range b
where a.login_ip between b.start_ip and end_ip;
select * from table(dbms_xplan.display); |
|
|
|
| Re: very simple but very slow query [message #406681 is a reply to message #406675] |
Fri, 05 June 2009 06:23 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| JRowbottom wrote on Fri, 05 June 2009 05:51 | What indexes are there on the two tables?
Also, do the following in SQL*plus and post the results:explain plan for
select a.userid
,a.login_ip
,b.area_code
from user_login_srl a
,dict_ip_range b
where a.login_ip between b.start_ip and end_ip;
select * from table(dbms_xplan.display);
|
there is an index IX_DICT_IP_RANGE_01 ON table DICT_IP_RANGE,columns and position (START_IP,END_IP,AREA_CODE)
query plan as follow:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 471M| 11G| | 124K (89)| 00:24:53 |
| 1 | MERGE JOIN | | 471M| 11G| | 124K (89)| 00:24:53 |
| 2 | SORT JOIN | | 85621 | 1254K| 4728K| 648 (2)| 00:00:08 |
| 3 | INDEX FAST FULL SCAN| IX_DICT_IP_RANGE_01 | 85621 | 1254K| | 198 (1)| 00:00:03 |
|* 4 | FILTER | | | | | | |
|* 5 | SORT JOIN | | 2203K| 25M| 84M| 13644 (3)| 00:02:44 |
| 6 | TABLE ACCESS FULL | USER_LOGIN_SRL | 2203K| 25M| | 3397 (2)| 00:00:41 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."LOGIN_IP "<="B"."END_IP")
5 - access("A"."LOGIN_IP">="B"."START_IP")
filter("A"."LOGIN_IP">="B"."START_IP")
20 rows selected.
SQL>
|
|
|
|
|
|
|
|
| Re: very simple but very slow query [message #406689 is a reply to message #406675] |
Fri, 05 June 2009 06:50 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| JRowbottom wrote on Fri, 05 June 2009 05:51 | What indexes are there on the two tables?
Also, do the following in SQL*plus and post the results:explain plan for
select a.userid
,a.login_ip
,b.area_code
from user_login_srl a
,dict_ip_range b
where a.login_ip between b.start_ip and end_ip;
select * from table(dbms_xplan.display);
|
hi,JRowbottom,
here is the query plan:
SQL> set pagesize 0
SQL> /
Plan hash value: 1512012232
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 658M| 39G| | 179K (86)| 00:35:53 |
| 1 | MERGE JOIN | | 658M| 39G| | 179K (86)| 00:35:53 |
| 2 | SORT JOIN | | 100K| 3810K| 9M| 1186 (2)| 00:00:15 |
| 3 | TABLE ACCESS FULL | DICT_IP_RANGE | 100K| 3810K| | 170 (2)| 00:00:03 |
|* 4 | FILTER | | | | | | |
|* 5 | SORT JOIN | | 2631K| 65M| 181M| 24564 (2)| 00:04:55 |
| 6 | TABLE ACCESS FULL| USER_LOGIN_SRL | 2631K| 65M| | 4858 (2)| 00:00:59 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."LOGIN_IP"<="END_IP")
5 - access("A"."LOGIN_IP">="B"."START_IP")
filter("A"."LOGIN_IP">="B"."START_IP")
Note
-----
- dynamic sampling used for this statement
24 rows selected.
SQL>
and i created the following index:
create index ix_dict_ip_range_01 on dict_ip_range(start_ip,end_ip,area_code);
best regards!
|
|
|
|
| Re: very simple but very slow query [message #406692 is a reply to message #406689] |
Fri, 05 June 2009 07:00 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
query plan after taking analyze on all relate tables;
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1512012232
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 471M| 11G| | 125K (88)| 00:25:10 |
| 1 | MERGE JOIN | | 471M| 11G| | 125K (88)| 00:25:10 |
| 2 | SORT JOIN | | 85621 | 1254K| 4728K| 619 (2)| 00:00:08 |
| 3 | TABLE ACCESS FULL | DICT_IP_RANGE | 85621 | 1254K| | 170 (2)| 00:00:03 |
|* 4 | FILTER | | | | | | |
|* 5 | SORT JOIN | | 2203K| 25M| 84M| 15096 (3)| 00:03:02 |
| 6 | TABLE ACCESS FULL| USER_LOGIN_SRL | 2203K| 25M| | 4850 (2)| 00:00:59 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."LOGIN_IP"<="END_IP")
5 - access("A"."LOGIN_IP">="B"."START_IP")
filter("A"."LOGIN_IP">="B"."START_IP")
20 rows selected.
SQL>
|
|
|
|
| Re: very simple but very slow query [message #406705 is a reply to message #406692] |
Fri, 05 June 2009 08:05 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
That plan actually looks pretty optimal to me.
With that many rows and no filtering, you don't want to be using an index.
4 hours is too long though. The sorts will be utilising TEMP I would guess. I wonder if you are allocating too little memory to the sorts? What is your PGA_AGGREGATE_TARGET?
Try running autotrace in SQL Plus. It will tell you how much IO it is performing. It may help identify a possible HWM problem.
Ross Leishman
|
|
|
|
| Re: very simple but very slow query [message #406785 is a reply to message #406705] |
Fri, 05 June 2009 20:39 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
i set PGA_AGGREGATE_TARGET to 7GB,and i create an index
on table user_login_srl as follows,but it help nothing:
create index ix_user_login_srl_01 on user_login_srl(login_ip);
there will only be one match area_code for each login_ip
in table user_login_srl,i changed the sql as follows,also
do nothing:
select a.userid,a.login_ip,
(select b.area_code from dict_ip_range b where a.login_ip between b.start_ip and end_ip) area_code
from user_login_srl a;
here is the spfile setting:
orcl.__db_cache_size=704643072
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__shared_pool_size=234881024
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/orcl/adump'
*.background_dump_dest='/home/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/oradata/orcl/control01.ctl',
'/home/oracle/oradata/orcl/control02.ctl',
'/home/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/home/oracle/admin/orcl/cdump'
*.db_16k_cache_size=2000000000
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=2
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_WRITER_PROCESSES=8
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.parallel_execution_message_size=32768
*.parallel_max_servers=200
*.parallel_min_percent=50
*.parallel_threads_per_cpu=2
*.pga_aggregate_target=7000000000
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3016387584
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/orcl/udump'
here is the v$pgastat info when execute the query:
SQL> select * from v$pgastat;
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------------------
aggregate PGA target parameter 6999999488 bytes
aggregate PGA auto target 6275303424 bytes
global memory bound 699996160 bytes
total PGA inuse 103069696 bytes
total PGA allocated 131904512 bytes
maximum PGA allocated 133442560 bytes
total freeable PGA memory 5701632 bytes
process count 27
max processes count 31
PGA memory freed back to OS 3347251200 bytes
total PGA used for auto workareas 75637760 bytes
maximum PGA used for auto workareas 77719552 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 531456 bytes
over allocation count 0
bytes processed 1278881792 bytes
extra bytes read/written 34311168 bytes
cache hit percentage 97.38 percent
recompute count (total) 47174
19 rows selected.
SQL>
|
|
|
|
| Re: very simple but very slow query [message #406786 is a reply to message #406785] |
Fri, 05 June 2009 20:55 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
here is the vmstat output when execute the query:
sdw1:~ # vmstat 1 50
procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 112 4044600 80 11950400 0 0 60 21 2 3 1 0 99 0 0
1 0 112 4044600 80 11950400 0 0 0 48 262 82 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 8 255 32 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 0 254 30 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 48 268 90 12 0 87 0 0
2 0 112 4044600 80 11950400 0 0 0 0 254 39 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 254 29 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 48 260 78 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 32 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 30 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 48 268 80 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 44 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 8 255 32 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 48 260 74 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 30 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 30 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 140 290 132 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 8 255 42 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 28 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 48 267 82 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 254 34 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 0 254 34 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 64 271 88 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 0 255 40 13 0 87 0 0
1 0 112 4044600 80 11950400 0 0 0 0 253 30 12 0 88 0 0
1 0 112 4044600 80 11950400 0 0 0 48 267 82 12 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 0 254 34 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 8 254 32 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 48 282 106 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 0 254 40 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 0 253 28 12 0 88 0 0
1 0 112 4049196 80 11950400 0 0 0 48 259 76 13 0 87 0 0
1 0 112 4049196 80 11950400 0 0 0 0 253 38 13 0 87 0 0
1 0 112 4049196 80 11950400 0 0 0 0 253 26 12 0 88 0 0
1 0 112 4049196 80 11950400 0 0 0 48 267 84 12 0 87 0 0
1 0 112 4049196 80 11950400 0 0 0 0 254 36 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 12 258 78 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 48 260 68 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 0 253 36 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 0 253 26 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 48 258 74 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 0 253 40 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 0 254 36 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 48 259 70 12 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 0 253 38 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 0 254 28 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 64 273 88 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 0 253 36 13 0 87 0 0
1 0 112 4044724 80 11950400 0 0 0 0 254 32 12 0 88 0 0
1 0 112 4044724 80 11950400 0 0 0 48 260 76 12 0 88 0 0
sdw1:~ #
|
|
|
|
|
|
| Re: very simple but very slow query [message #406788 is a reply to message #406787] |
Fri, 05 June 2009 21:46 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| BlackSwan wrote on Fri, 05 June 2009 20:58 | select * from V_$PGA_TARGET_ADVICE
Post results from SQL above.
I suspect that the results are as good as they will ever be.
|
here is the result:
SQL> select * from V_$PGA_TARGET_ADVICE;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADVICE BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- ------ --------------- ------------------- ----------------------------- --------------------
1610612736 .125 ON 190389248 0 100 0
3221225472 .25 ON 190389248 0 100 0
6442450944 .5 ON 190389248 0 100 0
9663676416 .75 ON 190389248 0 100 0
1.2885E+10 1 ON 190389248 0 100 0
1.5462E+10 1.2 ON 190389248 0 100 0
1.8039E+10 1.4 ON 190389248 0 100 0
2.0616E+10 1.6 ON 190389248 0 100 0
2.3193E+10 1.8 ON 190389248 0 100 0
2.5770E+10 2 ON 190389248 0 100 0
3.8655E+10 3 ON 190389248 0 100 0
5.1540E+10 4 ON 190389248 0 100 0
12 rows selected.
SQL>
|
|
|
|
|
|
| Re: very simple but very slow query [message #406791 is a reply to message #406789] |
Fri, 05 June 2009 22:51 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| BlackSwan wrote on Fri, 05 June 2009 22:12 | The results of the query shows me that the PGA is way, way, way oversized.
The PGA could be reduced to be on 10% of current size & possibly be still oversized.
Apply, lather, rinse & repeat.
|
after set the pga_aggregate_target to 1G:
SQL>
SQL> select * from V_$PGA_TARGET_ADVICE;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADVICE BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- ------ --------------- ------------------- ----------------------------- --------------------
134217728 .125 ON 321536 0 100 0
268435456 .25 ON 321536 0 100 0
536870912 .5 ON 321536 0 100 0
805306368 .75 ON 321536 0 100 0
1073741824 1 ON 321536 0 100 0
1288489984 1.2 ON 321536 0 100 0
1503238144 1.4 ON 321536 0 100 0
1717986304 1.6 ON 321536 0 100 0
1932734464 1.8 ON 321536 0 100 0
2147483648 2 ON 321536 0 100 0
3221225472 3 ON 321536 0 100 0
4294967296 4 ON 321536 0 100 0
6442450944 6 ON 321536 0 100 0
8589934592 8 ON 321536 0 100 0
14 rows selected.
SQL>
|
|
|
|
| Re: very simple but very slow query [message #406794 is a reply to message #406659] |
Fri, 05 June 2009 23:08 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
If the PGA was reduced to 1GB, then problem SQL, then select * from V_$PGA_TARGET_ADVICE; was run shows PGA is still oversized.
In order to be really useful select * from V_$PGA_TARGET_ADVICE should be run after normal workload has occurred.
You can see the "sweet spot" where additional reductions will have adverse impact on performance.
So you want target PGA sized somewhat or "slightly" higher than this sweet spot so enough satisfies requirements but not so high as to be wasteful.
[Updated on: Fri, 05 June 2009 23:09] Report message to a moderator |
|
|
|
| Re: very simple but very slow query [message #406795 is a reply to message #406794] |
Fri, 05 June 2009 23:14 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| BlackSwan wrote on Fri, 05 June 2009 23:08 | If the PGA was reduced to 1GB, then problem SQL, then select * from V_$PGA_TARGET_ADVICE; was run shows PGA is still oversized.
In order to be really useful select * from V_$PGA_TARGET_ADVICE should be run after normal workload has occurred.
You can see the "sweet spot" where additional reductions will have adverse impact on performance.
So you want target PGA sized somewhat or "slightly" higher than this sweet spot so enough satisfies requirements but not so high as to be wasteful.
|
but how can i optimize the query to reduce the respond time,any tips ?
|
|
|
|
|
|
| Re: very simple but very slow query [message #406840 is a reply to message #406659] |
Sat, 06 June 2009 05:10 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Is the data already unique?
Try the following:-
select a.userid
,a.login_ip
,b.area_code
from (select distinct userid, login_ip from user_login_srl) a
,(select distinct start_ip, end_ip, area_code from dict_ip_range) b
where a.login_ip between b.start_ip and end_ip;
|
|
|
|
| Re: very simple but very slow query [message #406937 is a reply to message #406840] |
Sun, 07 June 2009 19:45 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| coleing wrote on Sat, 06 June 2009 05:10 | Is the data already unique?
Try the following:-
select a.userid
,a.login_ip
,b.area_code
from (select distinct userid, login_ip from user_login_srl) a
,(select distinct start_ip, end_ip, area_code from dict_ip_range) b
where a.login_ip between b.start_ip and end_ip;
|
yes,data is already unique,and each login_ip in table user_login_srl will match only one area_code in the table dict_ip_range
|
|
|
|
| Re: very simple but very slow query [message #406941 is a reply to message #406937] |
Sun, 07 June 2009 21:53 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I find it implausible that this query is taking 4 hours with this plan.
Prove it to me by running a SQL Trace and posting the TK*Prof output. That way we can see the elapsed time against each statement.
Ross Leishman
|
|
|
|
| Re: very simple but very slow query [message #407004 is a reply to message #406941] |
Mon, 08 June 2009 02:54 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
| rleishman wrote on Sun, 07 June 2009 21:53 | I find it implausible that this query is taking 4 hours with this plan.
Prove it to me by running a SQL Trace and posting the TK*Prof output. That way we can see the elapsed time against each statement.
Ross Leishman
|
below is the tkproof output,i only part of the result row set:
select a.userid,a.login_ip,
(select b.area_code from dict_ip_range b where a.login_ip between b.start_ip and end_ip) area_code
from user_login_srl a
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 836 84.84 82.81 0 1754277 0 12522
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 838 84.84 82.81 0 1754277 0 12522
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
10967 INDEX RANGE SCAN IX_DICT_IP_RANGE_01 (cr=1753318 pr=0 pw=0 time=82779911 us)(object id 52142)
12523 TABLE ACCESS FULL USER_LOGIN_SRL (cr=880 pr=0 pw=0 time=62 us)
[Updated on: Mon, 08 June 2009 02:56] Report message to a moderator |
|
|
|
| Re: very simple but very slow query [message #407053 is a reply to message #407004] |
Mon, 08 June 2009 05:25 |
beingman
Messages: 19
Registered: August 2005
|
Junior Member |
|
|
we have overcome the problem by deviding the dict_ip_range into more detailed pieces,giving the property of the IP range,so we can use equality criteria to filter out specific possible ip ranges to do match,the times lower to 3 minitues.
thanks for all help !
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
