SQL query takes 6 hours to execute

Printable View

Show 40 post(s) from this thread on one page

04-30-2010, 10:50 AM
mahajanakhil198

Quote:

Originally Posted by PAVB

Two pieces of code...

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.
04-30-2010, 01:24 PM
PAVB

.out is your session log
.prf is the one where the fun begins
04-30-2010, 11:37 PM
hrishy

Hi

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

Also see if you can or already have a index on

MAM_ATTR_STRING_VALUES (ID,ATTRIBUTE_XID,LOWER_CASE_STRING_VALUE )
05-03-2010, 10:25 AM
mahajanakhil198

2 Attachment(s)

Quote:

Originally Posted by PAVB

.out is your session log
.prf is the one where the fun begins

Here are the two files attached.
Could you please guide me on how I could proceed further?

I was seeing the trace file. Can you please tell what is 'pr', 'pw' and 'us' (which is unit of time, I guess it is microsecond)?
05-03-2010, 10:27 AM
mahajanakhil198

Quote:

Originally Posted by hrishy

Hi

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

Also see if you can or already have a index on

MAM_ATTR_STRING_VALUES (ID,ATTRIBUTE_XID,LOWER_CASE_STRING_VALUE )

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.
05-03-2010, 10:53 AM
stecal

Quote:

Originally Posted by mahajanakhil198

Here are the two files attached.
Could you please guide me on how I could proceed further?

I was seeing the trace file. Can you please tell what is 'pr', 'pw' and 'us' (which is unit of time, I guess it is microsecond)?

Put fingers on keyboard and type/search. Open book(s) and read.
http://www.oracle-base.com/articles/...dTkprof10g.php
05-03-2010, 11:01 AM
hrishy

Hi

Are your stats upto date on all the tables involved
How do you collect stats ?
is there a way to get back to the development team on this ?

Whats the acceptable time within which this query should finish ?
05-03-2010, 01:50 PM
mahajanakhil198

Answers are inline.

Quote:

Originally Posted by hrishy

Hi

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.
05-03-2010, 04:31 PM
mahajanakhil198

1 Attachment(s)

I noticed a strange thing.

"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.
05-04-2010, 11:20 AM
hrishy

Hi

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

Show 40 post(s) from this thread on one page