Home » RDBMS Server » Performance Tuning » very simple but very slow query (Oracle Database 10g, Suse Linux 10)  () 1 Vote
icon5.gif  very simple but very slow query [message #406659] Fri, 05 June 2009 04:16 Go to next message
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 #406667 is a reply to message #406659] Fri, 05 June 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: very simple but very slow query [message #406675 is a reply to message #406659] Fri, 05 June 2009 05:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #406683 is a reply to message #406681] Fri, 05 June 2009 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: very simple but very slow query [message #406686 is a reply to message #406683] Fri, 05 June 2009 06:35 Go to previous messageGo to next message
beingman
Messages: 19
Registered: August 2005
Junior Member

thank you,Michel.i will do as you remind later.
Re: very simple but very slow query [message #406689 is a reply to message #406675] Fri, 05 June 2009 06:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #406787 is a reply to message #406659] Fri, 05 June 2009 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
select * from V_$PGA_TARGET_ADVICE

Post results from SQL above.

I suspect that the results are as good as they will ever be.
Re: very simple but very slow query [message #406788 is a reply to message #406787] Fri, 05 June 2009 21:46 Go to previous messageGo to next message
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 #406789 is a reply to message #406659] Fri, 05 June 2009 22:12 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
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.
Re: very simple but very slow query [message #406791 is a reply to message #406789] Fri, 05 June 2009 22:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25045
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 Go to previous messageGo to next message
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 #406798 is a reply to message #406659] Fri, 05 June 2009 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I suspect that the results are as good as they will ever be.

At some point it time, reality is as good as it gets.

Without modifying the table structures, I am not sure performance can be improved.

Re: very simple but very slow query [message #406840 is a reply to message #406659] Sat, 06 June 2009 05:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
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 !
Previous Topic: Performance degrades with big block size tablespace
Next Topic: Latch:Library Cache
Goto Forum:
  


Current Time: Wed Dec 07 16:36:11 CST 2016

Total time taken to generate the page: 0.08927 seconds