Home » RDBMS Server » Performance Tuning » about sql access advisor (oracle10g, 10.2.0, RHEL AS4)
about sql access advisor [message #353161] Sun, 12 October 2008 02:21 Go to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
Hi,

I have problem in using SQL Access Advisor on test machine(10.2.0).Can anybody send a copy to use SQL access advisor and attributes with an example?

Thanx in advance. suresh
Re: about sql access advisor [message #353163 is a reply to message #353161] Sun, 12 October 2008 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you do and what is your problem?

Regards
Michel
Re: about sql access advisor [message #353164 is a reply to message #353161] Sun, 12 October 2008 02:35 Go to previous messageGo to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
This is the script I executed but I am getting the following error.

BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM emp WHERE EMPNO = 7788');
END;
/


SYSTEM>/
BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid
table reference filter
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1782
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 180
ORA-06512: at "SYS.PRVT_ADVISOR", line 3227
ORA-06512: at "SYS.DBMS_ADVISOR", line 706
ORA-06512: at line 2

Regards,
suresh
Re: about sql access advisor [message #353167 is a reply to message #353164] Sun, 12 October 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try with "scott.emp" instead of "emp" alone. "emp" is not a SYSTEM table.

Regards
Michel
Re: about sql access advisor [message #353171 is a reply to message #353161] Sun, 12 October 2008 03:30 Go to previous messageGo to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
I created EMP table in system schema.

Regards,
suresh
Re: about sql access advisor [message #353173 is a reply to message #353161] Sun, 12 October 2008 03:45 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

The reason is the table contained inside SYSTEM and SYS schema can't be tuned.

So create emp table inside scott and tune that.
Previous Topic: query slow after 10g upgrade
Next Topic: Bulk insert
Goto Forum:
  


Current Time: Thu Dec 08 00:29:41 CST 2016

Total time taken to generate the page: 0.08674 seconds