Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2008
    Posts
    136

    Unanswered: query is taking time

    Dear All,
    Below query is taking 15 secs in oracle while same query takes less than 1sec in mysql.

    Records in table :-1148352
    have index also on field FkTauPropertyId



    select
    inrtauprop0_.FkTauPropertyId as col_0_0_,
    inrtauprop0_.ImagePath as col_1_0_,
    inrtauprop0_.ImageCaption as col_2_0_,
    inrtauprop0_.ImageThumbnailPath as col_3_0_
    from
    inr_taupropertyimage inrtauprop0_
    where
    inrtauprop0_.FkTauPropertyId
    in
    ('345081' , '344840' , '344999' , '345806' , '350094' , '351486' , '351708' , '353289' , '353284' , '303661' , '353288' , '353285' , '314496' , '353290' , '349530' , '341361' , '331226' , '332287' , '343901' , '274683' , '274917' , '276438' , '278470' , '280298' , '281909' , '276933' , '277819' , '286069' , '284296' , '286148' , '300525' , '286933' , '287361' , '300496' , '235047' , '236046' , '240271' , '239091' , '237484' , '237699' , '239737' , '239020' , '239088' , '237882' , '239013' , '235052' , '235102' , '235152' , '235384' , '237493' , '236983' , '239634' , '239334' , '48574' , '34178' , '50338' , '175479' , '178553' , '175823' , '33928' , '175819' , '175500' , '136024' , '53525' , '138085' , '149081' , '175840' , '178334' , '178545' , '35457' , '40905' , '178561' , '179658' , '181272' , '43713' , '195846' , '195905' , '195863' , '195881' , '195899' , '33439' , '32603' , '30123' , '30156' , '31780' , '33348' , '30157' , '33486' , '32266' , '32468' , '30125' , '34302' , '33892' , '32204' , '32287' , '32336' , '32668' , '32732' , '33093' , '30119' , '32975' , '31812' , '32250' , '33079' , '33080' , '33098' , '212617' , '212628' , '215062' , '216927' , '216944' , '215300' , '195897' , '195904' , '195915' , '195922' , '195929' , '212639' , '219363' , '196065' , '216845' , '217413' , '195990' , '219389' , '25662' , '24667' , '21816' , '22066' , '24671' , '25359' , '32681' , '32831' , '34186' , '34188' , '32882' , '17534' , '17741' , '30121' , '31928' , '32395' , '32428' , '16312' , '31927' , '33346' , '32505' , '32539' , '32913' , '33081' , '33249' , '32669' , '232072' , '232079' , '230575' , '242495' , '244384' , '232508' , '250451' , '250453' , '248290' , '244617' , '246272' , '249201' , '233451' , '249305' , '249388' , '248843' , '251706' , '254126' , '232833' , '245629' , '252861' , '252612' , '250426' , '254326' , '255340' , '252682' , '234630' , '234812' , '238474' , '238481' , '256006' , '235173' , '235287' , '236459' , '240210' , '238606' , '239074' , '239649' , '266762' , '33086' , '33116' , '33872' , '31872' , '31947' , '32690' , '34490' , '33891' , '33989' , '33855' , '33887');

    Please suggest what need to do?

    Regards
    Pawan
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from following SQL statements

    Code:
    SELECT * FROM V$VERSION;
    
    SELECT COUNT(*)
    FROM   inr_taupropertyimage inrtauprop0_;
    
    SELECT COUNT(*)
    FROM   inr_taupropertyimage inrtauprop0_
    WHERE  inrtauprop0_.fktaupropertyid IN ( '345081', '344840', '344999', '345806',
                                             '350094', '351486', '351708', '353289',
                                             '353284', '303661', '353288', '353285',
                                             '314496', '353290', '349530', '341361',
                                             '331226', '332287', '343901', '274683',
                                             '274917', '276438', '278470', '280298',
                                             '281909', '276933', '277819', '286069',
                                             '284296', '286148', '300525', '286933',
                                             '287361', '300496', '235047', '236046',
                                             '240271', '239091', '237484', '237699',
                                             '239737', '239020', '239088', '237882',
                                             '239013', '235052', '235102', '235152',
                                             '235384', '237493', '236983', '239634',
                                             '239334', '48574', '34178', '50338',
                                             '175479', '178553', '175823', '33928',
                                             '175819', '175500', '136024', '53525',
                                             '138085', '149081', '175840', '178334',
                                             '178545', '35457', '40905', '178561',
                                             '179658', '181272', '43713', '195846',
                                             '195905', '195863', '195881', '195899',
                                             '33439', '32603', '30123', '30156',
                                             '31780', '33348', '30157', '33486',
                                             '32266', '32468', '30125', '34302',
                                             '33892', '32204', '32287', '32336',
                                             '32668', '32732', '33093', '30119',
                                             '32975', '31812', '32250', '33079',
                                             '33080', '33098', '212617', '212628',
                                             '215062', '216927', '216944', '215300',
                                             '195897', '195904', '195915', '195922',
                                             '195929', '212639', '219363', '196065',
                                             '216845', '217413', '195990', '219389',
                                             '25662', '24667', '21816', '22066',
                                             '24671', '25359', '32681', '32831',
                                             '34186', '34188', '32882', '17534',
                                             '17741', '30121', '31928', '32395',
                                             '32428', '16312', '31927', '33346',
                                             '32505', '32539', '32913', '33081',
                                             '33249', '32669', '232072', '232079',
                                             '230575', '242495', '244384', '232508',
                                             '250451', '250453', '248290', '244617',
                                             '246272', '249201', '233451', '249305',
                                             '249388', '248843', '251706', '254126',
                                             '232833', '245629', '252861', '252612',
                                             '250426', '254326', '255340', '252682',
                                             '234630', '234812', '238474', '238481',
                                             '256006', '235173', '235287', '236459',
                                             '240210', '238606', '239074', '239649',
                                             '266762', '33086', '33116', '33872',
                                             '31872', '31947', '32690', '34490',
                                             '33891', '33989', '33855', '33887' );
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2008
    Posts
    136
    OUTPUT 1:-BANNER

    CORE 11.1.0.6.0 Production
    NLSRTL Version 11.1.0.6.0 - Production
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    PL/SQL Release 11.1.0.6.0 - Production
    TNS for Linux: Version 11.1.0.6.0 - Production

    OUTPUT 2:-1148352

    OUTPUT 3:-3300
    Regards
    Pawan Kumar

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post results from following SQL

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MIS';

    select last_analyzed from user_tables where table_name = 'INR_TAUPROPERTYIMAGE';

    select index_name, last_analyzed from user_indexes where table_name = 'INR_TAUPROPERTYIMAGE';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What datatype is inr_taupropertyimage.fktaupropertyid?
    Do you have an index on that column?

  6. #6
    Join Date
    Mar 2008
    Posts
    136
    Output1: 28-MAY-10

    Output2 IDX_INR_TAUPROPERTYIMAGE_1 29-MAY-10

    Datatype of field FKTAUPROPERTYID is number(16) and have index.
    Regards
    Pawan Kumar

  7. #7
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Another output request (and please make sure you post the result in code tags for readability):

    Code:
    EXPLAIN PLAN FOR
    SELECT  inrtauprop0_.FkTauPropertyId as col_0_0_
               ,inrtauprop0_.ImagePath as col_1_0_
               ,inrtauprop0_.ImageCaption as col_2_0_
               ,inrtauprop0_.ImageThumbnailPath as col_3_0_
    FROM    inr_taupropertyimage inrtauprop0_
    WHERE  inrtauprop0_.fktaupropertyid IN ( '345081', '344840', '344999', '345806',
                                             '350094', '351486', '351708', '353289',
                                             '353284', '303661', '353288', '353285',
                                             '314496', '353290', '349530', '341361',
                                             '331226', '332287', '343901', '274683',
                                             '274917', '276438', '278470', '280298',
                                             '281909', '276933', '277819', '286069',
                                             '284296', '286148', '300525', '286933',
                                             '287361', '300496', '235047', '236046',
                                             '240271', '239091', '237484', '237699',
                                             '239737', '239020', '239088', '237882',
                                             '239013', '235052', '235102', '235152',
                                             '235384', '237493', '236983', '239634',
                                             '239334', '48574', '34178', '50338',
                                             '175479', '178553', '175823', '33928',
                                             '175819', '175500', '136024', '53525',
                                             '138085', '149081', '175840', '178334',
                                             '178545', '35457', '40905', '178561',
                                             '179658', '181272', '43713', '195846',
                                             '195905', '195863', '195881', '195899',
                                             '33439', '32603', '30123', '30156',
                                             '31780', '33348', '30157', '33486',
                                             '32266', '32468', '30125', '34302',
                                             '33892', '32204', '32287', '32336',
                                             '32668', '32732', '33093', '30119',
                                             '32975', '31812', '32250', '33079',
                                             '33080', '33098', '212617', '212628',
                                             '215062', '216927', '216944', '215300',
                                             '195897', '195904', '195915', '195922',
                                             '195929', '212639', '219363', '196065',
                                             '216845', '217413', '195990', '219389',
                                             '25662', '24667', '21816', '22066',
                                             '24671', '25359', '32681', '32831',
                                             '34186', '34188', '32882', '17534',
                                             '17741', '30121', '31928', '32395',
                                             '32428', '16312', '31927', '33346',
                                             '32505', '32539', '32913', '33081',
                                             '33249', '32669', '232072', '232079',
                                             '230575', '242495', '244384', '232508',
                                             '250451', '250453', '248290', '244617',
                                             '246272', '249201', '233451', '249305',
                                             '249388', '248843', '251706', '254126',
                                             '232833', '245629', '252861', '252612',
                                             '250426', '254326', '255340', '252682',
                                             '234630', '234812', '238474', '238481',
                                             '256006', '235173', '235287', '236459',
                                             '240210', '238606', '239074', '239649',
                                             '266762', '33086', '33116', '33872',
                                             '31872', '31947', '32690', '34490',
                                             '33891', '33989', '33855', '33887' );
    
    select * from table(dbms_xplan.display);
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  8. #8
    Join Date
    Mar 2008
    Posts
    136
    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2875246709

    -----------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 2843 | 391K| 82 (0)| 00:00:01 |
    | 1 | INLIST ITERATOR | | | | | |
    | 2 | TABLE ACCESS BY INDEX ROWID| INR_TAUPROPERTYIMAGE | 2843 | 391K| 82 (0)| 00:00:01 |
    |* 3 | INDEX RANGE SCAN | IDX_INR_TAUPROPERTYIMAGE_1 | 2843 | | 10 (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("INRTAUPROP0_"."FKTAUPROPERTYID"=16312 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=17534 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=17741 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=21816 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=22066 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=24667 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=24671 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=25359 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=25662 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=30119 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=30121 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=30123 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=30125 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=30156 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=30157 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=31780 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=31812 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=31872 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=31927 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=31928 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=31947 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32204 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32250 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32266 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32287 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32336 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32395 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32428 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32468 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32505 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32539 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32603 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32668 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32669 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32681 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32690 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32732 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32831 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32882 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=32913 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=32975 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33079 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33080 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33081 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33086 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33093 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33098 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33116 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33249 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33346 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33348 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33439 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33486 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33855 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33872 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33887 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33891 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33892 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=33928 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=33989 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=34178 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=34186 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=34188 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=34302 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=34490 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=35457 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=40905 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=43713 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=48574 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=50338 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=53525 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=136024 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=138085 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=149081 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=175479 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=175500 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=175819 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=175823 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=175840 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=178334 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=178545 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=178553 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=178561 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=179658 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=181272 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=195846 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=195863 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=195881 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=195897 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=195899 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=195904 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=195905 OR
    "INRTAUPROP0_"."FKTAUPROPERTYID"=195915 OR "INRTAUPROP0_"."FKTAUPROPERTYID"=195922 OR
    "INRTAUPROP0_"."FKTAUPROPERT)

    62 rows selected.
    Regards
    Pawan Kumar

  9. #9
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    O.k., this execution plan is as fast as it will get (0.01 seconds CPU time).

    So your problem is either the I/O (very slow disks) or a very small (or - depending on your OS - a very large) SGA.

    What are the values of your SGA_MAX_SIZE and SGA_TARGET parameters (you can query it in sqlplus:

    Code:
    show parameter sga_max_size;
    show parameter sga_target
    ),
    how much physical memory does your server have and what else is running on this server ?
    Is it possible, that you have sized your SGA to an extent, that the OS is paging ?

    Which and how many disks are you using (SAN?, SATA?)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  10. #10
    Join Date
    Mar 2008
    Posts
    136
    RAM is 1 GB.
    [oracle@oracle ~]$ uname -a
    Linux oracle.anmsoft.com 2.6.23.1-42.fc8 #1 SMP Tue Oct 30 13:55:12 EDT 2007 i686 athlon i386 GNU/Linux

    SQL> show parameter sga_max_size;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    sga_max_size big integer 392M
    SQL> show parameter sga_target;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    sga_target big integer 0
    Regards
    Pawan Kumar

  11. #11
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    You are at the absolute lower edge of memory resources where Oracle can function (the Installation Guide states 1GB Memory minimum on systems where nothing but Oracle is running)

    Check, if the OS is swapping the SGA.

    Two more infos please:

    select * from v$sga;
    select component, current_size from v$sga_dynamic_components;
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  12. #12
    Join Date
    Mar 2008
    Posts
    136
    SQL> select * from v$sga;

    NAME VALUE
    -------------------- ----------
    Fixed Size 1300268
    Variable Size 301992148
    Database Buffers 100663296
    Redo Buffers 6156288


    SQL> select component, current_size from v$sga_dynamic_components;

    COMPONENT CURRENT_SIZE
    ---------------------------------------------------------------- ------------
    shared pool 176160768
    large pool 4194304
    java pool 12582912
    streams pool 4194304
    DEFAULT buffer cache 100663296
    KEEP buffer cache 0
    RECYCLE buffer cache 0
    DEFAULT 2K buffer cache 0
    DEFAULT 4K buffer cache 0
    DEFAULT 8K buffer cache 0
    DEFAULT 16K buffer cache 0

    COMPONENT CURRENT_SIZE
    ---------------------------------------------------------------- ------------
    DEFAULT 32K buffer cache 0
    Shared IO Pool 0
    ASM Buffer Cache 0
    Regards
    Pawan Kumar

  13. #13
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Your system resources are not really overwhelming, but there is nothing out of proportion, so 15 secs are WAY too much for this query. And if it takes 1 sec on mySQL on the same platform, there is something wrong with this Oracle Instance.

    I'd suggest to open a SR (service request) with Oracle Support.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  14. #14
    Join Date
    Jun 2010
    Posts
    2
    Your indexed column has a type of Number(16). But you use quotation marks ( ' ) in your query. This may avoid usage of the index.Try writing your parameters without this mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •