Step by step test Sql Performance Analyzer of Real Application Test

–Test version: 11.1.0.6
–
–create an analysis task
–
–Use the following procedures to create a sql tuning set
– populate the tuning set from the cursor cache
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ‘my_sts’,
description  => ‘Sql tunning set for SPA’);
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
‘parsing_schema_name <> ”SYS” ‘,
NULL, NULL, NULL, NULL, 1, NULL,
‘ALL’)) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => ‘my_sts’,
populate_cursor => cur);
END;
/
–show sql tune set contents
SELECT * FROM [...]

Step by step test Database Replay of Real Application Test

–Test version: 11.1.0.6

–
–1.Capture workload
–
Before capturing a database workload, carefully consider the following options:
*Restarting the Database
–to avoid capture partial transaction
*Defining the Workload Filters
–inclusion filters or exclusion filters, not both
*Setting Up the Capture Directory
–capturing a database workload using APIs, Adding and Removing Workload Filters
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => ‘user_t’,
fattribute => ‘USER’,
fvalue => ‘T’);
END;
/
–this will filte out all sessions beloings [...]

Step by step test Sql Plan Management

–Test Version: 11.1.0.6
–Plan history is maintained only for repeatable SQL statements.A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged.
–How to turn on Automatic Plan Capture
Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE
–Manual Plan Loading
–Loading Plans from SQL Tuning Sets
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => ‘tset1′);
END;
/
–you can load plans [...]

Step by step test 11g adaptive cursor

DB version: 11.1.0.6
–
–Adaptive Cursors in 11g
–
–create a test table
T@ORCL11G>alter system flush shared_pool;
System altered.
T@ORCL11G>create table t2
as
select case when rownum = 1 then 1 else 99 end id, a.*
from dba_objects a
/
Table created.
T@ORCL11G>create index t2_id on t2(id);
Index created.
T@ORCL11G> begin
dbms_stats.gather_table_stats
( user, ‘T2′,
estimate_percent => 100,
method_opt=> ‘for all indexed columns’,cascade=>TRUE);
end;
/
PL/SQL procedure successfully completed.
–gather 100% is necessary as the data is skew
–bind [...]

Step by step test flashback data archives, a.k.a. Total Recall

–Flashback Data Archives, a.k.a. Total Recall
–  A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime.
–  Test version: 11.1.0.6
–create a system-wide default flashback archive on an existing tablespace.
conn / as sysdba
SYS@orcl11g>create flashback archive default fb tablespace example retention 1 year;
Flashback archive created.
–create [...]

Step by step test 11g new partition methods

DB version: 11.1.0.6
–new partition methods, ** are tested
**interval partition
**referencial partition
**virtual column partition
**system partition
*other new composite partitions
*Range-Range
*List-Range
*List-Hash
*List-List
*Interval-Range
*Interval-Hash
*Interval-List
*partition advisor, part of the SQL Access Advisor
*partition mode data pump, tables=[schema].[table_name].[partiton_name], PARTITION_OPTIONS = {NONE | DEPARTITION | MERGE}
–
–**interval partition, a kind of range partition
–
–use a sale table as example
create table sales
(prod_id        NUMBER,
cust_id        NUMBER,
time_id        DATE
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’)) STORE [...]

Step by step setup ASM using os files in 11g

Environment: Windows
DB version: 11.1.0.6
–Creating the ASM Instance Manually, using dbca is also ok
–create CSS service if it doesn’t exist in services
WINNT> <ORACLE_HOME>\bin\localconfig add
–create 2 1000MB os files as asmdisk
asmtool -create D:\asm\asmdisk1 1000
asmtool -create D:\asm\asmdisk2 1000
–if get an error msg, using commnd “net helpmsg errornumber” to know why
–create spfile
file: <ORACLE_HOME>\database\init+ASM.ora
INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
ASM_DISKSTRING=’D:\asm\*’
LARGE_POOL_SIZE=16M
_ASM_ALLOW_ONLY_RAW_DISKS= FALSE
–Note: The undocumented parameter _ASM_ALLOW_ONLY_RAW_DISKS is [...]

Step by step test oracle 11g physical standby database

–Enviroment
db version: 11.1.0.6.0
Primary          orcl11g        D:\oracle\oradata\orcl11g\orcl11g\
Physical standby     standby11g    C:\oracle\oradata\standby11g\
in oracle 11g using adrci command to check alert log, cause alert log in xml format under diag home
–Physical standby top new features, ** is tested
**Open and Recover physical standby at same time
**Snapshot standby, updatable standby database that provides full data protection for a [...]

Step by step to test stream on 10gr2

db version: 10.2.0.4
test platform: windows xp
source db: orcl
dest db: orcl1
–default all execute on source database except specified
SQL> show parameter compati
NAME                                 TYPE        VALUE
———————————— ———– ————
compatible                           string      10.2.0.3.0
sga_max_size                         big integer 276M
sga_target                           big integer 276M
processes                            integer     150
timed_statistics                     boolean     TRUE
log_archive_dest_1                   string      LOCATION=D:\oracle\oradata\orcl\archive
log_archive_format                   string      ORCL_ARC%S_%R.%T
undo_retention                       integer     7200
remote_archive_enable                string      TRUE
global_names                         boolean     TRUE
open_links                           integer     10
job_queue_processes                  integer     10
parallel_max_servers                 integer     20
streams_pool_size                    big integer 44M

–enable [...]