Data, the Cloud and other thoughts.
You cannot simply add more copies of voting disks to increase redundancy because the number of voting disks is managed by Oracle ASM and determined by the redundancy of the disk group.
To increase the number of voting disks, you need to move them to a disk group with higher redundancy.
So, create the new disk group or identify one that meets your requirements and use the:
crsctl replace votedisk command to move your voting disks to the new disk group.
SQL performance analyzer performs a similar job to the SQL Tuning Advisor with one major difference, SQL performance analyser allows you to compare the performance of SQL statements before and after a change, allowing you to predict the impact of changes to the system on SQL performance as well as pre tuning your database before making changes.
So, The SQL Tuning Advisor is designed to help tune SQL in the system as it currently exists where as the SQL Performance Analyzer is designed to help determine the impact of system changes to SQL performance.
It allows you to compare a wide range of changes from operating system changes to database upgrades and down to the level of index changes.
The recommended approach would be to replecate your productoin system and run your tests on that system.
The basic procedure for using SQL Performance Analyzer is as follows:
A SQL tuning set is a database object that includes one or more SQL statements , along with their execution statistics and execution context.
SQL statements can be loaded into a SQL tuning set from many different sources. It’s possible to use the cursor cache, Automatic Workload Repository (AWR), or existing SQL tuning sets.
Using a SQL tuning set enables you to:
Useful SQL Performance Adviser views:
A great article with examples can be found on Oracle Base
Processes
DBMS_SQLTUNE package:
*
CREATE_SQLSET – creates tuning set object in DB
LOAD_SQLSET – populates tuning set
CREATE_SGTAB_SQLSET – creates staging table
PACK_SGTAB_SQLSET – copy tuning set from SYS schema to staging table
UNPACK_SGTAB_SQLSET – copy tuning set from staging table to sql tuning set schema
DBMS_SQLPA package:
CREATE_ANALYSIS_TASK
EXECUTE_ANALYSIS_TASK
REPORT_ANALYSIS_TASK
The automated tuning process in Oracle 11g is implemented by a number of functions in the Oracle database:
Automatic Database Diagnostic Monitor (ADDM)
Takes input from the AWR reports which it analyses in order to identify potential bottlenecks. If the statistics level parameter is set to TYPICAL or ALL then the ADDM is ran every time an AWR snapshot is taken.
Reports can be vierwed through Enterprise manager but can also be generated with the addmrpt.sql script located at $ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL Tuning Advisor
The optimizer can run in 2 modes, Normal and Tuning. In Normal mode, the optimizer must produce a plan in a very short timeframe and so has to accept the information that it has is up to date and accurate. The alternative is Tuning mode where the optimizer is given more time so that it can perform analysis and gather more information in rder to produce a more accurate and efficient plan.
The ADDM identifies SQL that could be improved and passes it to the SQL Tuning Advisor. This invokes the Automatic Tuning Optimizer and evaluates possibilities for improvement. The automatic Tuning Advisor performs a range of different types of analysis including statistics analysis (gathering stats), SQL profiling (creating new SQL profiles), Access path analysis (adding aindexes to allow faster access to data) and SQL structure analysis (actually rewriting or rather suggesting options for rewriting SQl statements).
From Version 11g, the Automatic SQL Tuning Advisor is ran (by default) during the nightly maintanance window. While it does not automatically implement suggestoins, it can be set up to do so.
You can use the ‘ENABLE‘ or ‘DISABLE‘ procedures of the DBMS_AUTO_TASK_ADMIN package to let the automatic tuning job run or not. Setting the STATISTICS_LEVEL to BASIC will also disable it because that stops stats gathering be the AWR.
— Enable
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/
When it does run, the procedure is as folows:
These profiles will have a type of AUTO in DBA_SQL_PROFILES
How to Setup and Configure Automatic SQL Tuning
Automatic SQL tuning task can be configured with the DBMS_SQLTUNE package. Only SYS can do this.
You can turn on or off the acceptance of automatically generated SQL profiles with the SET_TUNING_TASK_PARAMETER procedure.
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER( task_name = > ‘SYS_AUTO_SQL_TUNING_TASK’,
parameter = > ‘ACCEPT_SQL_PROFILES’,
value = > ‘TRUE’);
END;
Additional parameters:
Viewing Automatic SQL Tuning Reports
You can view the Automatic SQL Tuning Reports through Enterprise Manager as well as via the DBMS_SQLTUNE package – DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, — ‘TEXT’
level => DBMS_SQLTUNE.level_typical, — ‘TYPICAL’
section => DBMS_SQLTUNE.section_all, — ‘ALL’
object_id => NULL,
result_limit => NULL);
END;
/
SET LONG 1000000
PRINT :l_report
There are several useful views that can be referred to and are worth exploring to gain more information on Automatic SQL Tuning.
To make administration and problem diagnosis easier in Oracle Clusterware, there is a single consolidated directory structure for the main clusterware log files.
This is found under $GRID_HOME/log/[hostname]/
The CRSD logfile – crsd.log is stored in the /crsd/ directory and is archived every 10mb.
The CSS logfiles – cssd.log is stored in the /cssd/ directory. This is archived every 20mb.
EVM logs are under /evmd/
SRVM (srvctl) is found along with the OCR logfiles under /client/ and also under $ORACLE_HOME/log/[hostname]/client/
In order to enable clusterware resource debugging you need to set the resource attribute USR_ORA_DEBUG to 1 for the resource that you wish to debug.
$ crsctl set log res “ora.host01.vip:1”
When you are finished you issue almost the same command to disable the clusterware debugging (set USR_ORA_DEBUG to 0):
$ crsctl set log res “ora.host01.vip:0”
You can also use an initialization file to configure the debugging. This file is located at $GRID_HOME/log/hostname/admin/ and it is names after the process that you are debugging (process_name.ini).