創(chuàng)新互聯(lián)公司是創(chuàng)新、創(chuàng)意、研發(fā)型一體的綜合型網(wǎng)站建設(shè)公司，自成立以來公司不斷探索創(chuàng)新，始終堅持為客戶提供滿意周到的服務(wù)，在本地打下了良好的口碑，在過去的十載時間我們累計服務(wù)了上千家以及全國政企客戶，如發(fā)電機回收等企業(yè)單位，完善的項目管理流程，嚴(yán)格把控項目進度與質(zhì)量監(jiān)控加上過硬的技術(shù)實力獲得客戶的一致贊譽。
禁用sga自動管理機制，分配比較小的數(shù)據(jù)緩沖區(qū)(30m)和共享池(70m)空間
1.1關(guān)閉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
關(guān)閉sga自動管理機制：(部分參數(shù)重啟數(shù)據(jù)庫生效)
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設(shè)置數(shù)據(jù)緩沖區(qū)的大小為30m
idle>alter system set db_cache_size=30m;
System altered.
1.3設(shè)置共享池的大小為70m
idle>alter system set shared_pool_size=70m scope=spfile;
System altered.
1.4驗證設(shè)置好的數(shù)據(jù)緩沖區(qū)和共享池的大小
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創(chuàng)建一個專門用于statspack的表空間tools
idle>create tablespace tools
2 datafile '/u01/app/oracle/oradata/TestDB12/tools01.dbf'
3 size 300m;
Tablespace created.
2.2以sysdba身份執(zhí)行創(chuàng)建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
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
tablespace (identified by *) being used.
Enter value for temporary_tablespace:回車
……
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Statstack安裝完成。
2.3設(shè)置statspack自動產(chǎn)生快照的間隔時間為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/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);
commit;
end;
2.4設(shè)置快照的默認級別為7級
perfstat@TESTDB12>exec statspack.modify_statspack_parameter(i_snap_level=>7);
PL/SQL procedure successfully completed.
創(chuàng)建序列
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搭建查詢環(huán)境
3.1創(chuàng)建新表并插入數(shù)據(jù)
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表設(shè)為nologging
scott@TESTDB12>alter table emp2 nologging;
Table altered.
插入2千萬行數(shù)據(jù)：
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編寫查詢業(yè)務(wù)腳本
{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 < select
* from emp2 where empno=$CNT; exit EOF CNT=`expr
$CNT + 1` done 4.運行查詢業(yè)務(wù)腳本并產(chǎn)生statspack報告 4.1運行查詢業(yè)務(wù)腳本并啟動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 hh34: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:21 Enter value for
end_snap:31 Enter value for report_name: Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for
begin_snap:31 Enter value for
end_snap:32 Enter value for report_name: Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for
begin_snap:32 Enter value for
end_snap:33 Enter value for report_name: Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for
begin_snap:33 Enter value for
end_snap:34 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服務(wù) [root@James ~]# chkconfig smb
on //開機自動啟動 {root@Redhat55.cuug.net:/root}#
smbpasswd -a oracle //將系統(tǒng)oracle用戶添加到samba服務(wù)
網(wǎng)站名稱：部署statspack工具（一）
轉(zhuǎn)載注明：http://weahome.cn/article/jppich.html
重慶分公司
重慶分公司
