Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Subject: Why creation of sql profiles take forever

25 views
Skip to first unread message

zigz...@yahoo.com

unread,
May 4, 2012, 8:58:59 AM5/4/12
to

I am on Oracle 10.2.0.4 on HP UNIX 11i. I am creating sql profiles using following logic. Sometimes sql profile is generated in 1-2 minutes. Many times , even after an hour, no sql profile is created and I get timeout errors . I increased time limit from 3600 (1 hr) to 7200 (2 hrs), still timeout error, why?

-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '$SQLSTMTID', scope =
> DBMS_SQLTUNE.scope_comprehensive, time_limit => 3600, task_name => '${SQLSTMTID}_AWR_tuning_tas
k', description => 'Tuning task for statement ${SQLSTMTID} in AWR.');
--DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '${SQLSTMTID}_AWR_tuning_task');

John Hurley

unread,
May 4, 2012, 9:52:26 AM5/4/12
to
One possibility is of course that you are on 10.2.0.4 and perhaps
oracle has fixed something related here by 10.2.0.6 ...

Have you tried running a 10046 trace on it when it is running long?
What is it waiting on and why?

Mladen Gogala

unread,
May 5, 2012, 4:16:12 PM5/5/12
to
On Fri, 04 May 2012 06:52:26 -0700, John Hurley wrote:

> oracle has fixed something related here by 10.2.0.6 ...

10.2.0.6? I thought that 10.2.0.5 was the terminal version for 10.2?



--
http://mgogala.byethost5.com

John Hurley

unread,
May 8, 2012, 9:15:23 PM5/8/12
to
Yes I think you are correct we moved off 10.2 before 10.2.0.5 ever
became available though ... my bad.
0 new messages