Oracle Database 10g - SQLAccess Advisor.pdf - Oracle - musli_com

Performance and Scalability - Business Intelligence

T URBOCHARGE YOUR D ATABASE : U SE THE O RACLE DATABASE 10 G

SQLA CCESS A DVISOR

Dr. Lilian Hobbs, Oracle Corporation Ltd

I MPROVING D ATABASE P ERFORMANCE

Probably every DBA or person responsible for managing a computer system, would like to improve the query

response times and throughput of their system. However, as anyone who has ever tried to do this will tell you, it is not

easy. There are many techniques that can be used to tune a computer system, some involve changing the hardware and

many involve software changes; at the operating system, application and database level.

The problem when tuning a database is that when it is done manually it can be quite a time consuming operation,

typically taking days and if it proves to be a challenge, weeks or even months to achieve optimum performance. A

major concern is that as you tuning, are you using a representative example of how the database is actually being used.

If not, then you may be creating objects like indexes and materialized views, that will not improve your system

response time.

SQLA CCESS A DVISOR

Once all the traditional tuning options have been explored, and performance could still be improved, our database

tuner is now looking for new ways to tune the database and this is where the SQLAccess Advisor in Oracle Database

10g can help. Alternatively, if you are just starting out building a system and you are not sure which indexes or

materialized views you may need, then the SQLAccess Advisor can help her also.

Figure 1 Advisor Central

The SQLAccess Advisor is a new feature which will

recommend indexes and materialized views to create,

retain or drop based on the workload supplied.

The SQLAccess Advisor is part of the Oracle Database

10g Server Manageability feature and is completely

integrated into that environment. Therefore, the

SQLAccess Advisor can be run from Advisor Central ,

shown in figure 1, thus providing an easy to use interface

although there is a command line option if you prefer that

approach.

F OUR S IMPLE S TEPS TO G ENERATE R ECOMMENDATIONS & I MPLEMENT

Only a few steps have to be completed in order to generate some recommendations. All the information about this

tuning process, such as what is to be tuned, the recommendations and actions are held inside a task . If you are using

the SQLAccess Advisor from Enterprise Manager, then the task is transparent and you only need to give it a name.

S TEP 1 – C REATE THE T ASK

There is no limit to the number of tasks that you can create. Therefore you can create tasks to model a number of

different scenarios. For example, you could configure one task to represent your daily environment, another task for

Paper 40150

Performance and Scalability - Business Intelligence

your overnight batch work and then compare the recommendations from each task to see if they have anything in

common. Since tasks do not have to be deleted, this would enable you to build up a view of your system over a

period of time. Therefore, you could compare a task that represents your typical daily activity now, to one that you

created say six months ago and see if anything has changed.

If you are using the command line interface then a task can be used as a template for another task, which can be very

useful when you may want to modify the tuning parameters for a task slightly to see if that has any effect on the

recommendations.

S TEP 2 – D EFINE A W ORKLOAD

Obtaining a good set of recommendations is very dependent on having a representative workload for your system.

Database tuners will tell you that this is one of the most challenging aspects of database tuning, as gathering this data

is not easy. The SQLAccess Advisor allows the following workload sources:

• Current contents of the SQL cache

• User-defined via a table containing SQL statements

• SQL Tuning Set from the Workload Repository

• Hypothetical workload, by referencing schemas(s) in the database

• Oracle 9i Advisor workload (command line only)

• Manually add SQL statements to a workload (command line only)

With all these workload sources, the database tuner now has plenty of opportunity to gather good data for the tuning

process. In Figure 2 we see the first screen in the SQLAccess Advisor where the workload source is specified.

Figure 2 SQLAccess Advisor Workload Source

will generate what it thinks a workload may represent.

W ORKLOAD F ILTERING

Depending on the source of the workload, it is very easy to create one that contains far more data than you require,

especially if it comes from say the current contents of the SQL cache. In this scenario, if you have many applications

running, then you would extract SQL statements for all of those applications, but you may be only interested in tuning

some of them. Therefore all workloads can be filtered by any of the following:

• Application or module name

• Number of SQL statements

• Only queries during a specified time window

Within Server Manageability there is a repository of

workloads, which are known as SQL Tuning sets,

therefore, any one of these can be selected by the

SQLAccess Advisor. Since they can be created from

problems identified by the other Advisors, they can be a

good workload source to use for tuning.

Alternatively you may prefer to specify your workload

manually. Perhaps you extracted the SQL generated by

your tool and then stored that in a table, which can be

used to create a workload.

If your system is being initially designed, then you may not

have any workloads available. In this case, you can use the

hypothetical workload, where you specify one or more

schemas, and from their structure, the SQLAccess Advisor

Paper 40150

Performance and Scalability - Business Intelligence

• Username

• Tables that either must or must-not be the list specified

S TEP 3 – GENERATE THE R ECOMMENDATIONS

Once the workload exists a set of recommendations can then be generated. In Figure 3 we see how this is done from

within Oracle Enterprise Manager. Here we have the ability to say what type of recommendations are required, only

indexes, only materialized views, or both. There is also the ability to say in which mode the advisor should run, limited

or comprehensive .

Depending on the complexity of the workload, the number of possible solutions with respect to the indexes and

materialized views that could be created can be quite significant. There are times, when you may not be interested in

looking at all of these solutions and only considering the most important ones. This can be achieved by using the

limited mode. Results will be shown quicker and then if they are not thought to be adequate, then the SQLAccess

Advisor can be run again in comprehensive mode.

Figure 3 SQLAccess Advisor Recommendation Options

Clicking on the Advanced Options shown in Figure 3 will

also allow you to specify any space restrictions that the

recommendations must work within. A default tablespace

and schema into which the indexes and materialized views

will be placed and to specify the priority to be used for

tuning the SQL statements such as average elapsed time or

average disk reads.

Now that all of the information has been supplied the

recommendations can be generated. In Enterprise

Manager a job will be submitted and its progress can be

monitored from Advisor Central which is shown in Figure

S TEP 4 – V IEWING & O PTIONALLY I MPLEMENTING THE R ECOMMENDATIONS

Once the recommendations have been generated, they will remain until the task is deleted, thus giving the database

tuner plenty of time to review the suggestions from the SQLAccess Advisor. This means that the recommendation

process can be repeated many times, trying different workloads and options to see what effect this has on the

suggested recommendations.

When the SQLAccess Advisor is used from within Enterprise Manager, the recommendations can be viewed by:

• Recommendations by workload cost benefit

• SQL Statements by workload cost benefit

This gives the database tuner the ability to see which recommendations will give the best improvement to the

workload or individual SQL statements can be viewed to see how they can be specifically tuned, and the impact that

will have on system performance.

In Figure 4 we see the Recommendations by Workload Cost Benefit . In this example we can see that there are 6

recommendations, where the improvement to the workload will be between 3% and 19%. In Figure 5 we see the

SQL statements which comprise the workload and how each of these can be improved. Although this example shows

a one to one mapping between SQL statement in the workload and recommendation, the SQLAccess Advisor

recommendations can be used by multiple SQL statements.

Paper 40150

Performance and Scalability - Business Intelligence

Figure 4 Recommendation by Workload Cost

Figure 5 Recommendation by SQL statement

Any of these links can be clicked on to view the appropriate

information. For example, selecting recommendation 3, we

can see in Figure 6, what the SQLAccess Advisor is

proposing, which is both a materialized view and an index

to improve the performance for this query and clicking on

the CREATE MATERIALIZED VIEW will then display the actual statement.

Figure 6 Recommendation 3

The SQLAccess Advisor first makes its

recommendations, and then it lets you decide whether to

implement all of its recommendations, or only some of

them, or none. This is achieved by simply selecting the

recommendations you require using either of the screens

shown in Figure 4 or Figure 5.

At this stage, you have a choice on how to implement the

recommendations. If you prefer to do it yourself, then the

SQLAccess Advisor will generate a SQL script for you to

use. You can then edit this if required, to match your

company standards for data naming etc and then

implement it at your leisure. Alternatively, you can

schedule a job in Enterprise Manager and it will execute

the script to implement these recommendations.

C OMMAND L INE I NTERFACE

Although this paper has concentrated on showing how the SQLAccess Advisor is used from within Oracle Enterprise

manager, it does have a command line interface, via the DBMS_ADVISOR package, which may be used if you prefer

this approach. Everything described here is in fact done using the DBMS_ADVISOR package and within Enterprise

Manager you can view these commands.

There are a couple of additional features that are available from the command line. This includes the ability to make a

task or workload a template, that can be used to create another task or workload respectively. SQL statements can be

added, changed or removed from a workload and it is possible to use a workload created for the Oracle 9i advisor.

Paper 40150

Performance and Scalability - Business Intelligence

There is also a option to tune a single statement using the DBMS_ADVISOR..QUICK-TUNE which takes as its

input a SQL statement.

O RACLE 9 I S UMMARY A DVISOR

If you are now interested in what the SQLAccess Advisor could do for your systems, but are not yet using Oracle

Database 10g, but you are using Oracle 9i. Then you could try the Summary Advisor in Oracle 9i, which has similar

functionality to the SQLAccess Advisor, but it only, recommends materialized views. Its interface is different, but it

will give you an opportunity to see how the advisors work and any workloads that you create, can be used as input to

the SQLAccess Advisor when you start using Oracle Database 10g.

CONCLUSION

The SQLAccess Advisor provides a very easy to use interface to help tune your systems. It requires very little system

knowledge and can run without affecting production systems, since the data can be gathered from the production

system and taken to another machine where the SQLAccess Advisor can be run. Since all data can be retained, it

enables you to build up a library or workloads and recommendations, so you can see over a period of time, whether

the patterns of usage of your systems are changing. Anyone who has to tune a database should consider using the

SQLAccess Advisor because its so quick to use, that you may find that it decides that a part of your system needed

tuning that you never even thought was a problem.

Paper 40150

Oracle Database 10g - SQLAccess Advisor.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: