Shulou(Shulou.com)06/01 Report--

Disable sga automatic management mechanism and allocate relatively small data buffer (30m) and shared pool (70m) space

1.1 turn off SGA automatic management mechanism

Check to see if ASSM is enabled

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

Disable sga automatic management mechanism: (some parameters restart the database to take effect)

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 set the size of the data buffer to 30m

Idle > alter system set db_cache_size=30m

System altered.

1.3 set the size of the shared pool to 70m

Idle > alter system set shared_pool_size=70m scope=spfile

System altered.

1.4 verify the size of the set data buffer and shared pool

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.

two。 Deploy statspack

2.1Create a tablespace tools dedicated to statspack

Idle > create tablespace tools

2 datafile'/ u01qqappActionoradataUniplicTestDB12Universe tools01.dbf'

3 size 300m

Tablespace created.

2.2 execute the script to create the prefstat object as sysdba

SQL > @? / rdbms/admin/spcreate.sql / sppurge.sql is to delete a snapshot

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 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 will result in the database's default Temporary

Tablespace (identified by *) being used.

Enter value for temporary_tablespace: enter

... ...

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

The Statstack installation is complete.

2.3 set the interval for statspack to automatically generate snapshots to 15 minutes (24 hours, 96 15 minutes a day)

{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 set the default level for snapshots to level 7

Perfstat@TESTDB12 > exec statspack.modify_statspack_parameter (iSnapshot level = > 7)

PL/SQL procedure successfully completed.

Create a sequence

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. Set up query environment

3.1 create a new table and insert data

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.

Set the emp2 table to nologging

Scott@TESTDB12 > alter table emp2 nologging

Table altered.

Insert 20 million rows of data:

Scott@TESTDB12 > begin

2 for i in 1..20000000 loop

3 insert into emp2

4 values (emp2_empno.nextval,'cuug' | | iMagazine Salesmand Magazine 7698, sysdatememe, 1600, 300, 30)

5 if mod (iMe1000) = 0 then

6 commit

7 end if

8 end loop

9 commit

10 end

11 /

3.2 write query business scripts

{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 @? / rdbms/admin/spauto

-gorgeous dividing line -

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 Compact 16Compact 00-1191805

Perfstat@TESTDB12 > Rem cdialeri 12-06-99-1059172, 1103031

Perfstat@TESTDB12 > Rem cdialeri 08Compact 13Compact 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

-

twenty-three

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.

-gorgeous dividing line -

4.2 verify reports automatically generated by 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

Generate statspack analysis report

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 cancel statspack automatic snapshot generation

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. Start samba sharing and report copy to the windows host for the generated statspack

[root@James ~] # service smb start / / start the samba service

[root@James ~] # chkconfig smb on / / Boot starts automatically

{root@Redhat55.cuug.net:/root} # smbpasswd-an oracle / / add system oracle users to the samba service

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.