SQL query takes 6 hours to execute

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

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

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

---

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

Quote:

---

Originally Posted by pando

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.