Re: Any concerns with the advisor privilege?
Date: Wed, 10 Apr 2013 05:17:10 -0700 (PDT)
Message-ID: <1365596230.45118.YahooMailNeo_at_web126106.mail.ne1.yahoo.com>
The creation and management of SQL Tuning Sets is available with Tuning Pack. The ability to sequentially replay an STS and compare that sequential replay to another replay of that same STS after some tuning changes is the role of SPA. SPA is part of RAT licensing.
The "Access Advisor" (also part of tuning pack) works on an STS as its input. I'd give the developers access to DBMS_ADVISOR as well:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm#PFGRF94876
The advisor will look at the entirety of the queries in an STS an devise an optimal indexing strategy that may include partitioning, MVIEWS, bitmap, fbi, b-turee, etc..
"SQL Access Advisor index recommendations include bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. Bitmap indexes are most commonly used in a data warehouse to index unique or near-unique keys. SQL Access Advisor materialized view recommendations include fast refreshable and full refreshable MVs, for either general rewrite or exact text match rewrite."
...
The "tuning advisor" works on a single SQL statement at time, although can process the set of statements from an STS as well, looking at each individually.
http://docs.oracle.com/cd/E11882_01/server.112/e16638/sql_tune.htm#i34782
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a multi-step process:
- Create a SQL tuning set (if tuning multiple SQL statements)
- Create a SQL tuning task
- Execute a SQL tuning task
- Display the results of a SQL tuning task
- Implement recommendations as appropriate
People do use these tools in production all the time. Creating profiles are often reactive responses to statements causing performance problems in production. Also, keep in mind that the tuning advisor runs in your production environments automatically in the maintenance window.
"Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week."
It won't automatically create profiles unless you let it... but it is running, unless you turned it off.
Job
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 10 2013 - 14:17:10 CEST