回答英语SPA性能测试step by step

SPA

性能测试

一、

源库抓取

SQL

负载

1.

建立一个

SQL Tuning Set

SQL> exec dbms__sqlset('upg_sql');

PL/SQL procedure successfully completed.

2.

导入

SQL

到

STS

这里选择从

AWR

负载库中抓取昨天一天的

sql

快照信息如下：

Instance

DB Name

Snap Id

Snap Started

Level

------------ ------------ --------- ------------------ -----

db99

DB99

6431 20 Apr 2015 00:00

1

6432 20 Apr 2015 01:00

1

6433 20 Apr 2015 02:00

1

6434 20 Apr 2015 03:00

1

6435 20 Apr 2015 04:00

1

6436 20 Apr 2015 05:00

1

6437 20 Apr 2015 06:00

1

6438 20 Apr 2015 07:00

1

6439 20 Apr 2015 08:00

1

6440 20 Apr 2015 09:00

1

6441 20 Apr 2015 10:00

1

6442 20 Apr 2015 11:00

1

6443 20 Apr 2015 12:00

1

6444 20 Apr 2015 13:00

1

6445 20 Apr 2015 14:00

1

6446 20 Apr 2015 15:00

1

6447 20 Apr 2015 16:00

1

6448 20 Apr 2015 17:00

1

6449 20 Apr 2015 18:00

1

6450 20 Apr 2015 19:01

1

6451 20 Apr 2015 20:00

1

6452 20 Apr 2015 21:00

1

6453 20 Apr 2015 22:00

1

6454 20 Apr 2015 23:00

1

6455 21 Apr 2015 00:00

1

SQL> DECLARE

2

cur sys_refcursor;

3

BEGIN

4

open cur for

5

select value(p)

6

from table(dbms__workload_repository(6431, 6455)) p;

7

dbms__sqlset('upg_sql', cur);

8

close cur;

9

END;

10

/

3.

查看

sql_set

和收集到的语句< /p>

SQL> select name,statement_count from dba_sqlset;

NAME

STATEMENT_COUNT

------------------------------ ---------------

sql_test

275

upg_sql

352

SQL> select sql_text from dba_sqlset_statements where sqlset_name='upg_sql';

4.

创建表

UPGSQLSET_TAB

存储采集的

SQL

信息

SQL> BEGIN

2

DBMS__stgtab_sqlset(table_name => 'UPGSQLSET_TAB',

3

schema_name => 'SPA',

4

tablespace_name => 'USERS');

5

END;

6

/

5.

将

SQL TUNING SET Pack

到表中

SQL> BEGIN

2

DBMS__stgtab_sqlset(sqlset_name => 'upg_sql',

3

sqlset_owner => 'SYS',

4

staging_table_name => 'UPGSQLSET_TAB',

5

staging_schema_owner => 'SPA');

6

END;

7

/

6.

查看表内容

desc SET_TAB;

select count(*) from SET_TAB;

SQL>

select count(*) from SET_TAB;

COUNT(*)

----------

4197

7.

导出表

SET_TAB

[oracle@oradb99 ~]$$ expdp spa/sap dumpfile=UPGSQLSET_ directory=PATCH16_PATCH

tables=UPGSQLSET_TAB logfile=UPGSQLSET_

Export: Release 11.2.0.4.0 - Production on Tue Apr 21 13:35:04 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.

All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP

, Data Mining and Real Application Testing options

Starting

spa/********

dumpfile=UPGSQLSET_

directory=PATCH16_PATCH tables=UPGSQLSET_TAB logfile=UPGSQLSET_

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 5.187 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported

2.867 MB

4197 rows

Master table