1- target sql statement. my_target_sql.sql in this example.
2- trace shell script. my_trace_script.sh in this example - see below.
Once you have both of them in place.
a) set your oracle_home
b) set your oracle_sid
c) nohup my_trace_script.sh > my_trace.out &
d) tkprof trace file on user_dump_destination with sys=no option
Build your my_trace_script.sh including code below...
sqlplus -s / <
set linesize 180
set pagesize 0
set timing on
spool my_trace_script.out
select instance_name, host_name from v\$instance;
drop table PLAN_TABLE;
@?/rdbms/admin/utlxplan
alter session set timed_statistics = true;
alter session set sql_trace = true;
set autotrace on explain
alter session set events '10046 trace name context forever, level 12';
-- execute sql to be traced like...
@my_target_sql.sql
--
alter session set sql_trace = false;
alter session set timed_statistics = false;
spool off
EOF
Last but not least, if my_target_sql.sql is expected to return a large number of rows you may want to wrap it up on a count shell like...
select count(*) from(
--put your sql statement here
);
...after all you are looking for trace/execution plan, not for the actual output.
Thanks.
So, I will need to look into two files at the end of execution of this script.
1. The generated spool file (so as to see the execution file).
2. The formatted output file after TKPROFing the generated trace file.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Check if this gets you the same result as your original query
Code:
SELECT 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
EXISTS
(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)
AND A.ID=SV65539.ID)
ORDER BY A.ASSET_SIZE
Check if this gets you the same result as your original query
Code:
SELECT 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
EXISTS
(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)
AND A.ID=SV65539.ID)
ORDER BY A.ASSET_SIZE
I cannot change the query. It is generated by the application by the UI of user session. Moreover, the development on application has stopped and it is only under support plan. Thanks for your effort anyways.
Last edited by mahajanakhil198; 05-03-2010 at 10:42 AM.
Are your stats upto date on all the tables involved --- Yes
How do you collect stats ? ---- There is an automated job that runs every day at 12 midnight and it uses dbms_stats.gather_schema_stats.
is there a way to get back to the development team on this ? --- No
Whats the acceptable time within which this query should finish ? --- Probably 5 minutes as the UI based user session logs off with an error if it doesn't get the results within this time frame. There is no way this value can be increased.
"select count(*) from ....." version of the posted query executes in 2 seconds. There seems to be some problem with the select list. I tried to spot it out using the binary method but the problem seems to be with multiple columns in select list.
Description of MAM_ASSETS is attached.
I am trying to fix it up with this information. Please let me know if you have any comments regarding the same.
Did it ever run in less then 5 minutes ?
(your users are very patient to wait for 5 minutes )
Have you thought of a MV
Code:
CREATE MATERIALIZED VIEW LOG ON MAM_ATTR_STRING_VALUES WITH ROWID
CREATE MATERIALIZED VIEW MAM_ATTR_STRING_VALUES_MV
CLUSTER m_lt_li (id)
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
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