DBAsupport.com Forums - Powered by vBulletin
Page 4 of 4 FirstFirst ... 234
Results 31 to 34 of 34

Thread: SQL query takes 6 hours to execute

  1. #31
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    can you show use output of from sqlplus

    Code:
    set echo on
    set line 300
    set pagesize 400
    set trimspool on
    
    alter session set cursor_sharing = EXACT;
    explain plan for
      SELECT /* EXACT */
               TO_CHAR (A.ID) A118,
               A.ASSET_GROUP_XID A2,
               A.ACL_ID A106,
               A.OWNER A4,
               CONVERT (A.NAME, 'WE8MACROMAN8') A1,
               A.FIRST_VERSION_CREATED_BY A15,
               LPAD (
                  LTRIM (
                     TO_CHAR (A.FIRST_VERSION_DATE_CREATED, 'SYYYYMMDDHH24MISS')
                  ),
                  15,
                  '+'
               )
                  A16,
               LPAD (LTRIM (TO_CHAR (A.DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A18,
               CONVERT (A.CHECKOUT_LOCATION, 'WE8MACROMAN8') A24,
               TO_CHAR (A.ASSET_SIZE) A7,
               A.CHECKED_OUT_BY A23,
               A.ARCHIVE_STATUS A27,
               A.STORAGE_CHANGE_REQUEST A110,
               A.HAS_HISTORY A74,
               TO_CHAR (A.CONTAINED_IN_COUNT) A73,
               TO_CHAR (A.CONTAINER_OF_COUNT) A72,
               TO_CHAR (A.RELATED_WITH_COUNT) A71,
               CONVERT (A.FILE_TYPE, 'WE8MACROMAN8') A12,
               CONVERT (A.FILE_CREATOR_APPLICATION, 'WE8MACROMAN8') A13,
               CONVERT (A.FILE_EXTENSION, 'WE8MACROMAN8') A9,
               TO_CHAR (A.REVISION) A21,
               A.IS_CURRENT_VERSION A109,
               TO_CHAR (A.FAMILY_ID) A112,
               LPAD (LTRIM (TO_CHAR (A.FILE_DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A11,
               LPAD (
                  LTRIM (TO_CHAR (A.FILE_DATE_LAST_MODIFIED, 'SYYYYMMDDHH24MISS')),
                  15,
                  '+'
               )
                  A10
        FROM   MAM_ASSETS A
       WHERE   (    (A.ACL_ID = 65536)
                AND (A.ASSET_GROUP_XID = 120)
                AND (A.IS_CURRENT_VERSION = 1)
                AND A.ID IN
                         (SELECT   A.ID ASSET_ID
                            FROM   MAM_ASSETS A
                           WHERE   (A.ID IN
                                          (SELECT   SV65539.ID
                                             FROM   MAM_ATTR_STRING_VALUES SV65539
                                            WHERE   (SV65539.LOWER_CASE_STRING_VALUE LIKE
                                                        LOWER(CONVERT (
                                                                 '%813982%',
                                                                 'WE8MSWIN1252',
                                                                 'WE8MACROMAN8'
                                                              )))
                                                    AND (SV65539.ATTRIBUTE_XID =
                                                            65539)))))
    ORDER BY   A.ASSET_SIZE;
    
    select * from table(dbms_xplan.display);
    
    
    alter session set cursor_sharing = FORCE;
    explain plan for
      SELECT /* FORCE */
               TO_CHAR (A.ID) A118,
               A.ASSET_GROUP_XID A2,
               A.ACL_ID A106,
               A.OWNER A4,
               CONVERT (A.NAME, 'WE8MACROMAN8') A1,
               A.FIRST_VERSION_CREATED_BY A15,
               LPAD (
                  LTRIM (
                     TO_CHAR (A.FIRST_VERSION_DATE_CREATED, 'SYYYYMMDDHH24MISS')
                  ),
                  15,
                  '+'
               )
                  A16,
               LPAD (LTRIM (TO_CHAR (A.DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A18,
               CONVERT (A.CHECKOUT_LOCATION, 'WE8MACROMAN8') A24,
               TO_CHAR (A.ASSET_SIZE) A7,
               A.CHECKED_OUT_BY A23,
               A.ARCHIVE_STATUS A27,
               A.STORAGE_CHANGE_REQUEST A110,
               A.HAS_HISTORY A74,
               TO_CHAR (A.CONTAINED_IN_COUNT) A73,
               TO_CHAR (A.CONTAINER_OF_COUNT) A72,
               TO_CHAR (A.RELATED_WITH_COUNT) A71,
               CONVERT (A.FILE_TYPE, 'WE8MACROMAN8') A12,
               CONVERT (A.FILE_CREATOR_APPLICATION, 'WE8MACROMAN8') A13,
               CONVERT (A.FILE_EXTENSION, 'WE8MACROMAN8') A9,
               TO_CHAR (A.REVISION) A21,
               A.IS_CURRENT_VERSION A109,
               TO_CHAR (A.FAMILY_ID) A112,
               LPAD (LTRIM (TO_CHAR (A.FILE_DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A11,
               LPAD (
                  LTRIM (TO_CHAR (A.FILE_DATE_LAST_MODIFIED, 'SYYYYMMDDHH24MISS')),
                  15,
                  '+'
               )
                  A10
        FROM   MAM_ASSETS A
       WHERE   (    (A.ACL_ID = 65536)
                AND (A.ASSET_GROUP_XID = 120)
                AND (A.IS_CURRENT_VERSION = 1)
                AND A.ID IN
                         (SELECT   A.ID ASSET_ID
                            FROM   MAM_ASSETS A
                           WHERE   (A.ID IN
                                          (SELECT   SV65539.ID
                                             FROM   MAM_ATTR_STRING_VALUES SV65539
                                            WHERE   (SV65539.LOWER_CASE_STRING_VALUE LIKE
                                                        LOWER(CONVERT (
                                                                 '%813982%',
                                                                 'WE8MSWIN1252',
                                                                 'WE8MACROMAN8'
                                                              )))
                                                    AND (SV65539.ATTRIBUTE_XID =
                                                            65539)))))
    ORDER BY   A.ASSET_SIZE;
    
    select * from table(dbms_xplan.display);
    
    
    
    select a.index_name, b.column_name, a.distinct_keys, a.num_rows, a.last_analyzed
    from user_indexes a, user_ind_columns b
    where a.index_name = b.index_name
    and a.index_name in ('AST_CURRENT_VERSION_I', 'AST_ASG_FK_I', 'AST_PK')
    order by a.index_name, b.column_position;

    Regards

  2. #32
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    I am complacent with the change I have made. In case, the parameter setting doesn't work after monitoring it for a week, I will do it and post it.

    Quote Originally Posted by pando View Post
    can you show use output of from sqlplus

    Code:
    set echo on
    set line 300
    set pagesize 400
    set trimspool on
    
    alter session set cursor_sharing = EXACT;
    explain plan for
      SELECT /* EXACT */
               TO_CHAR (A.ID) A118,
               A.ASSET_GROUP_XID A2,
               A.ACL_ID A106,
               A.OWNER A4,
               CONVERT (A.NAME, 'WE8MACROMAN8') A1,
               A.FIRST_VERSION_CREATED_BY A15,
               LPAD (
                  LTRIM (
                     TO_CHAR (A.FIRST_VERSION_DATE_CREATED, 'SYYYYMMDDHH24MISS')
                  ),
                  15,
                  '+'
               )
                  A16,
               LPAD (LTRIM (TO_CHAR (A.DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A18,
               CONVERT (A.CHECKOUT_LOCATION, 'WE8MACROMAN8') A24,
               TO_CHAR (A.ASSET_SIZE) A7,
               A.CHECKED_OUT_BY A23,
               A.ARCHIVE_STATUS A27,
               A.STORAGE_CHANGE_REQUEST A110,
               A.HAS_HISTORY A74,
               TO_CHAR (A.CONTAINED_IN_COUNT) A73,
               TO_CHAR (A.CONTAINER_OF_COUNT) A72,
               TO_CHAR (A.RELATED_WITH_COUNT) A71,
               CONVERT (A.FILE_TYPE, 'WE8MACROMAN8') A12,
               CONVERT (A.FILE_CREATOR_APPLICATION, 'WE8MACROMAN8') A13,
               CONVERT (A.FILE_EXTENSION, 'WE8MACROMAN8') A9,
               TO_CHAR (A.REVISION) A21,
               A.IS_CURRENT_VERSION A109,
               TO_CHAR (A.FAMILY_ID) A112,
               LPAD (LTRIM (TO_CHAR (A.FILE_DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A11,
               LPAD (
                  LTRIM (TO_CHAR (A.FILE_DATE_LAST_MODIFIED, 'SYYYYMMDDHH24MISS')),
                  15,
                  '+'
               )
                  A10
        FROM   MAM_ASSETS A
       WHERE   (    (A.ACL_ID = 65536)
                AND (A.ASSET_GROUP_XID = 120)
                AND (A.IS_CURRENT_VERSION = 1)
                AND A.ID IN
                         (SELECT   A.ID ASSET_ID
                            FROM   MAM_ASSETS A
                           WHERE   (A.ID IN
                                          (SELECT   SV65539.ID
                                             FROM   MAM_ATTR_STRING_VALUES SV65539
                                            WHERE   (SV65539.LOWER_CASE_STRING_VALUE LIKE
                                                        LOWER(CONVERT (
                                                                 '%813982%',
                                                                 'WE8MSWIN1252',
                                                                 'WE8MACROMAN8'
                                                              )))
                                                    AND (SV65539.ATTRIBUTE_XID =
                                                            65539)))))
    ORDER BY   A.ASSET_SIZE;
    
    select * from table(dbms_xplan.display);
    
    
    alter session set cursor_sharing = FORCE;
    explain plan for
      SELECT /* FORCE */
               TO_CHAR (A.ID) A118,
               A.ASSET_GROUP_XID A2,
               A.ACL_ID A106,
               A.OWNER A4,
               CONVERT (A.NAME, 'WE8MACROMAN8') A1,
               A.FIRST_VERSION_CREATED_BY A15,
               LPAD (
                  LTRIM (
                     TO_CHAR (A.FIRST_VERSION_DATE_CREATED, 'SYYYYMMDDHH24MISS')
                  ),
                  15,
                  '+'
               )
                  A16,
               LPAD (LTRIM (TO_CHAR (A.DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A18,
               CONVERT (A.CHECKOUT_LOCATION, 'WE8MACROMAN8') A24,
               TO_CHAR (A.ASSET_SIZE) A7,
               A.CHECKED_OUT_BY A23,
               A.ARCHIVE_STATUS A27,
               A.STORAGE_CHANGE_REQUEST A110,
               A.HAS_HISTORY A74,
               TO_CHAR (A.CONTAINED_IN_COUNT) A73,
               TO_CHAR (A.CONTAINER_OF_COUNT) A72,
               TO_CHAR (A.RELATED_WITH_COUNT) A71,
               CONVERT (A.FILE_TYPE, 'WE8MACROMAN8') A12,
               CONVERT (A.FILE_CREATOR_APPLICATION, 'WE8MACROMAN8') A13,
               CONVERT (A.FILE_EXTENSION, 'WE8MACROMAN8') A9,
               TO_CHAR (A.REVISION) A21,
               A.IS_CURRENT_VERSION A109,
               TO_CHAR (A.FAMILY_ID) A112,
               LPAD (LTRIM (TO_CHAR (A.FILE_DATE_CREATED, 'SYYYYMMDDHH24MISS')),
                     15,
                     '+')
                  A11,
               LPAD (
                  LTRIM (TO_CHAR (A.FILE_DATE_LAST_MODIFIED, 'SYYYYMMDDHH24MISS')),
                  15,
                  '+'
               )
                  A10
        FROM   MAM_ASSETS A
       WHERE   (    (A.ACL_ID = 65536)
                AND (A.ASSET_GROUP_XID = 120)
                AND (A.IS_CURRENT_VERSION = 1)
                AND A.ID IN
                         (SELECT   A.ID ASSET_ID
                            FROM   MAM_ASSETS A
                           WHERE   (A.ID IN
                                          (SELECT   SV65539.ID
                                             FROM   MAM_ATTR_STRING_VALUES SV65539
                                            WHERE   (SV65539.LOWER_CASE_STRING_VALUE LIKE
                                                        LOWER(CONVERT (
                                                                 '%813982%',
                                                                 'WE8MSWIN1252',
                                                                 'WE8MACROMAN8'
                                                              )))
                                                    AND (SV65539.ATTRIBUTE_XID =
                                                            65539)))))
    ORDER BY   A.ASSET_SIZE;
    
    select * from table(dbms_xplan.display);
    
    
    
    select a.index_name, b.column_name, a.distinct_keys, a.num_rows, a.last_analyzed
    from user_indexes a, user_ind_columns b
    where a.index_name = b.index_name
    and a.index_name in ('AST_CURRENT_VERSION_I', 'AST_ASG_FK_I', 'AST_PK')
    order by a.index_name, b.column_position;

    Regards
    lucky

  3. #33
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    FYI bitmap conversion/AND operations are not exclusively for bitmap indexes, btree indexes are perfect candidates for this type of operations, most of times is for those queries such as

    select
    from t1
    where c1 = :a -> indexed by t1_idx1
    and c2 = :b -> indexed by t1_idx2

    Oracle will scan both indexes and merge the results using bitmap operations

  4. #34
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by pando View Post
    FYI bitmap conversion/AND operations are not exclusively for bitmap indexes, btree indexes are perfect candidates for this type of operations, most of times is for those queries such as

    select
    from t1
    where c1 = :a -> indexed by t1_idx1
    and c2 = :b -> indexed by t1_idx2

    Oracle will scan both indexes and merge the results using bitmap operations


    Thanks for the information. I will see into it.
    lucky

Posting Permissions

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


Click Here to Expand Forum to Full Width