-
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.
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
lucky
-
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
-
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|