禁用sga自动管理机制，分配比较小的数据缓冲区(30m)和共享池(70m)空间
1.1关闭SGA自动管理机制
查看是否开启了ASSM
idle>show
parameter sga;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 804M
sga_target big integer 500M
关闭sga自动管理机制：(部分参数重启数据库生效)
sys@TESTDB12>alter
system set memory_target=0;
idle>alter
system set sga_target=0;
idle>show
parameter memory;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 804M
memory_target big integer 0
shared_memory_address integer 0
idle>show
parameter sga;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 804M
sga_target big integer 0
1.2设置数据缓冲区的大小为30m
idle>alter
system set db_cache_size=30m;
System
altered.
1.3 设置共享池的大小为70m
idle>alter
system set shared_pool_size=70m scope=spfile;
System
altered.
1.4 验证设置好的数据缓冲区和共享池的大小
idle>show
parameter db_cache_size;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_cache_size big integer 32M
idle>show
parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
shared_pool_size big integer 72M
sys@TESTDB12>select
component,current_size/1024/1024 from v$sga_dynamic_components;
COMPONENT
CURRENT_SIZE/1024/1024
----------------------------------------------------------------
----------------------
shared
pool
80
large
pool
4
java
pool
4
streams
pool
4
DEFAULT
buffer cache
24
KEEP
buffer cache
0
RECYCLE
buffer cache
0
DEFAULT
2K buffer cache
0
DEFAULT
4K buffer cache
0
DEFAULT
8K buffer cache
0
DEFAULT
16K buffer cache
0
DEFAULT
32K buffer cache
0
Shared
IO Pool
0
ASM
Buffer Cache
0
14
rows selected.
2.部署statspack
2.1创建一个专门用于statspack的表空间tools
idle>create
tablespace tools
2
datafile '/u01/app/oracle/oradata/TestDB12/tools01.dbf'
3 size
300m;
Tablespace
created.
2.2以sysdba身份执行创建prefstat对象的脚本
SQL> @?/rdbms/admin/spcreate.sql
/sppurge.sql是删除快照
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the
installation FAILING
Enter value for
perfstat_password: oracle
Choose the Default tablespace for the PERFSTAT
user
---------------------------------------------------
Below is the list of online tablespaces in this
database which can
store user data.
Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation
FAILING, as
using SYSTEM for performance data is not
supported.
Choose the PERFSTAT users's default
tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be
created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ ---------
----------------------------
EXAMPLE PERMANENT
SYSAUX PERMANENT *
TOOLS PERMANENT
USERS PERMANENT
Pressing <return> will result in STATSPACK's
recommended default
tablespace (identified by *) being used.
Enter value for
default_tablespace: tools
Using tablespace TOOLS as PERFSTAT default
tablespace.
Choose the Temporary tablespace for the PERFSTAT
user
-----------------------------------------------------
Below is the list of online tablespaces in this
database which can
store temporary data (e.g. for sort
workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace
will result in the
installation FAILING, as using SYSTEM for
workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ ---------
--------------------------
TEMP TEMPORARY *
Pressing <return> will result in the
database's default Temporary
tablespace (identified by *) being used.
Enter value
for temporary_tablespace:回车
… …
NOTE:
SPCPKG complete. Please check spcpkg.lis for any
errors.
Statstack安装完成。
2.3设置statspack自动产生快照的间隔时间为15分钟（一天有24小时96个15分钟）
{oracle@Redhat55.cuug.net:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin}$
vi /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/spauto.sql
variable jobno number;
variable instno number;
begin
select
instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/,',''MI'')',
TRUE, :instno);
commit;
end;
2.4设置快照的默认级别为7级
perfstat@TESTDB12>exec
statspack.modify_statspack_parameter(i_snap_level=>7);
PL/SQL procedure successfully completed.
创建序列
sys@TESTDB12>alter
user scott identified by tiger;
User
altered.
sys@TESTDB12>conn
scott/tiger;
Connected.
scott@TESTDB12>CREATE
SEQUENCE emp2_empno
2
INCREMENT BY 1
3
START WITH 1
4
MAXVALUE 100000000
5
CACHE 10000
6
NOCYCLE;
Sequence
created.
3搭建查询环境
3.1创建新表并插入数据
scott@TESTDB12>create
table emp2 as select * from emp where 1=2;
Table
created.
scott@TESTDB12>alter
table emp2 modify empno number(10);
Table
altered.
scott@TESTDB12>alter
table emp2 modify ename varchar(30);
Table
altered.
将emp2 表设为nologging
scott@TESTDB12>alter
table emp2 nologging;
Table
altered.
插入2千万行数据：
scott@TESTDB12>begin
2 for
i in 1..20000000 loop
3
insert into emp2
4
values
(emp2_empno.nextval,'cuug'||i,'SALESMAN',7698,sysdate,1600,300,30);
5 if
mod(i,1000)=0 then
6
commit;
7 end
if;
8 end
loop;
9
commit;
10 end;
11 /
3.2编写查询业务脚本
{oracle@Redhat55.cuug.net:/home/oracle}$
mkdir -p script/bin/
{oracle@Redhat55.cuug.net:/home/oracle/script/bin}$ vi script/bin/share_pool_sql_1.sh
#!/bin/bash
CNT=1
while
[ $CNT -lt 20000000 ]
do
sqlplus
scott/tiger <<EOF
select
* from emp2 where empno=$CNT;
exit
EOF
CNT=`expr
$CNT + 1`
done
4.运行查询业务脚本并产生statspack报告
4.1运行查询业务脚本并启动statspack的自动快照
{oracle@Redhat55.cuug.net:/home/oracle/script/bin}$
sh share_pool_sql_1.sh
sys@TESTDB12>conn
perfstat/oracle
Connected.
perfstat@TESTDB12>@?/rdbms/admin/spauto
----------------------------------------------华丽的分割线----------------------------------------------------------------------------------
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem
$Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem
spauto.sql
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem Copyright (c) Oracle Corporation 1999, 2000.
All Rights Reserved.
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem NAME
perfstat@TESTDB12>Rem spauto.sql
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem DESCRIPTION
perfstat@TESTDB12>Rem SQL*PLUS command file to automate the
collection of STATPACK
perfstat@TESTDB12>Rem statistics.
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem NOTES
perfstat@TESTDB12>Rem Should be run as the STATSPACK owner,
PERFSTAT.
perfstat@TESTDB12>Rem Requires job_queue_processes init.ora
parameter to be
perfstat@TESTDB12>Rem set to a number >0 before automatic
statistics gathering
perfstat@TESTDB12>Rem will run.
perfstat@TESTDB12>Rem
perfstat@TESTDB12>Rem MODIFIED
(MM/DD/YY)
perfstat@TESTDB12>Rem cdialeri
02/16/00 - 1191805
perfstat@TESTDB12>Rem cdialeri
12/06/99 - 1059172, 1103031
perfstat@TESTDB12>Rem cdialeri
08/13/99 - Created
perfstat@TESTDB12>Rem
perfstat@TESTDB12>
perfstat@TESTDB12>
perfstat@TESTDB12>spool
spauto.lis
perfstat@TESTDB12>
perfstat@TESTDB12>--
perfstat@TESTDB12>-- Schedule a snapshot to be run on this
instance every hour, on the hour
perfstat@TESTDB12>
perfstat@TESTDB12>variable
jobno number;
perfstat@TESTDB12>variable
instno number;
perfstat@TESTDB12>begin
2
select instance_number into :instno from v$instance;
3
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/96,'MI'),
'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);
4
commit;
5 end;
6 /
PL/SQL
procedure successfully completed.
perfstat@TESTDB12>
perfstat@TESTDB12>
perfstat@TESTDB12>prompt
perfstat@TESTDB12>prompt Job number for automated statistics
collection for this instance
Job
number for automated statistics collection for this instance
perfstat@TESTDB12>prompt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
perfstat@TESTDB12>prompt Note that this job number is needed
when modifying or removing
Note
that this job number is needed when modifying or removing
perfstat@TESTDB12>prompt the job:
the
job:
perfstat@TESTDB12>print
jobno
JOBNO
----------
23
perfstat@TESTDB12>
perfstat@TESTDB12>prompt
perfstat@TESTDB12>prompt Job queue process
Job
queue process
perfstat@TESTDB12>prompt ~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~
perfstat@TESTDB12>prompt Below is the current setting of the
job_queue_processes init.ora
Below
is the current setting of the job_queue_processes init.ora
perfstat@TESTDB12>prompt parameter - the value for this
parameter must be greater
parameter
- the value for this parameter must be greater
perfstat@TESTDB12>prompt than 0 to use automatic statistics
gathering:
than
0 to use automatic statistics gathering:
perfstat@TESTDB12>show
parameter job_queue_processes
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
job_queue_processes integer 1000
perfstat@TESTDB12>prompt
perfstat@TESTDB12>
perfstat@TESTDB12>prompt
perfstat@TESTDB12>prompt Next scheduled run
Next
scheduled run
perfstat@TESTDB12>prompt ~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~
perfstat@TESTDB12>prompt The next scheduled run for this job is:
The
next scheduled run for this job is:
perfstat@TESTDB12>select
job, next_date, next_sec
2
from user_jobs
3
where job = :jobno;
JOB NEXT_DATE NEXT_SEC
----------
--------- --------------------------------
23 28-JUL-14 04:31:00
1
row selected.
----------------------------------------------华丽的分割线----------------------------------------------------------------------------------
4.2验证statspack自动生成的报告
perfstat@TESTDB12>alter
session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
perfstat@TESTDB12>select
snap_id,snap_time,snap_level from stats$snapshot order by snap_time;
SNAP_ID SNAP_TIME SNAP_LEVEL
----------
------------------- ----------
1 2014-07-28 23:07:05 7
11 2014-07-28 04:46:00 7
12 2014-07-28 05:01:04 7
13 2014-07-28 05:16:02 7
14 2014-07-28 05:31:04 7
生成statspack分析报告
SQL> @?/rdbms/admin/spreport
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for
begin_snap:
Enter value for
end_snap:
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for
begin_snap:
Enter value for
end_snap:
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for
begin_snap:
Enter value for
end_snap:
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for
begin_snap:
Enter value for
end_snap:
Enter value for report_name:
4.3取消statspack自动生成快照
perfstat@TESTDB12>select
job,log_user,last_date,next_date from user_jobs;
JOB LOG_USER LAST_DATE
----------
------------------------------ -------------------
NEXT_DATE
-------------------
23 PERFSTAT 2014-07-28 05:31:04
2014-07-28
05:46:00
perfstat@TESTDB12>exec
dbms_job.remove('23');
5.启samba共享并对生成的statspack报告copy到windows主机
[root@James ~]# service smb
start //启动samba服务
[root@James ~]# chkconfig smb
on //开机自动启动
{root@Redhat55.cuug.net:/root}#
smbpasswd -a oracle //将系统oracle用户添加到samba服务