--1. Create perfstat table space
Create TableSpace Perfstat DataFile '/Home/rapaid/oradata/prdyp/perfstat.dbf' size 500M;
--2. Sys login to do the following scripts @ / home / orapaid / product / 92 / rdbms / admin / catdbsyn.sql @ / home / orapaid / product / 92 / rdbms / admin / dbmspool.sql
--3. Running the installation script - Sys User Login
Select Instance_name, Host_Name, Version, Startup_time from V $ INSTANCE
SELECT FILE_NAME from DBA_DATA_FILES;
Create TableSpace Perfstat DataFile '/Home/rapaid/oradata/prdyp/perfstat.dbf' size 500M;
- Before installation, the system parameters can be able to install and run StatsPack you may need to set the following system parameters: 1. Job_Queue_Processes In order to create automatic tasks, execute data collection, this parameter is greater than 0. You can modify this parameter in the initial test parameter file. Modify this directory / home / orapaid / admin / prdyp / pfile. Old file needs to restart the database
2. TIMED_STATISTICS collects timing information for the operating system, which can be used to display time-like statistics, optimize databases and SQL statements. To prevent overhead due to request time from operating system, set this value to false. When collecting statistics using StatsPack, it is recommended to set this value to true, otherwise the resulting statistics can only play only 10% of the role, and the performance impact of TIMED_STATISTICS is set to TRUE is negligible. This parameter stores the collected time information in the V $ SESSSTATS and V $ SysStats dynamic performance view.
The TIMED_STATISTICS parameter can be changed in the instance level
SQL> ALTER System Set Timed_statistics = true; system altered
Alter system set timed_statistics = false;
- View the files in this directory before installation SQL> Host Dir sp *
Oracle 8i Execute $ Oracle_Home / Rdbms / Admin / Statscre.sqloracle 9i Perform $ Oracle_Home / Rdbms / Admin / SpCreate.sql
@ / home / newvers / product / 92 / rdbms / admin / spcreate
- View the files in this directory after installation (there should be more .lis files) SQL> Host Dir sp *
SQL> Host Find "ORA-" * .lis
SQL> Host Find "Err" * .lis
- On UNIX, you can view the appropriate error message by following the following command.
$ ls * .lis
$ GREP ORA- * .LIS $ GREP Err * .lis
In this step, if an error occurs, you can run the spdrop.sql script to delete these objects. Then re-run spcreate.sql to create these objects. Run SQL * Plus to log in to users with SYSDBA privileges:
/ *
III. Test installed StatsPack runs statspack.snap can generate system snapshots, run twice, then execute spreport.sql to generate a report based on two time points. If everything is normal, the installation is successful.
PERFSTAT / Perfstat Login * / SQL> Execute StatsPack.snap
SQL> Execute statspack.snap
SQL> @ / home / newvers / product / 92 / rdbms / admin / spreport
SQL> @ / home / newvers / product / 92 / rdbms / admin / spauto
- 4. Make StatsPack automatically collect system conditions
Alert System Set Job_Queue_Processes = 10;
Alert System Set Job_Queue_processes = 10 Scope = Both;
Alter system set timed_statistics = true;
[Orapaid @ bj37 admin] $ cat spauto.sqlRemRem $ Header: spauto.sql 16-feb-00.16: 49: 37 cdialeri Exp $ RemRem spauto.sqlRemRem Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.RemRem NAMERem spauto .sqlRemRem DESCRIPTIONRem SQL * PLUS command file to automate the collection of STATPACKRem statistics.RemRem NOTESRem Should be run as the STATSPACK owner, PERFSTAT.Rem Requires job_queue_processes init.ora parameter to beRem set to a number> 0 before automatic statistics gatheringRem will run. Remrem Modified (mm / dd / yy) Rem Cdiaaleri 02/16/00 - 1191805Rem Cdialeri 12/06/99 - 1059172, 1103031Rem Cdialeri 08/13/99 - CreateDrem
Spool spauto.lis
---- Schedule a Snapshot To Be Run on this Instance Every Hour, on The Hour
variable jobno number; variable instno number; begin select instance_number into: instno from v $ instance; dbms_job.submit (: jobno, 'statspack.snap;', trunc (sysdate 1/24, 'HH'), 'trunc (SYSDATE 1/24, '' hh '') ', true,: instno; commit; end; /
promptprompt Job number for automated statistics collection for this instanceprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~ Prompt Note this Job Number is needed when this job: Print Jobno
promptprompt Job queue processprompt ~~~~~~~~~~~~~~~~~ prompt Below is the current setting of the job_queue_processes init.oraprompt parameter - the value for this parameter must be greater prompt than 0 to use automatic statistics gathering: show parameter job_queue_processespromptpromptprompt Next scheduled runprompt ~~~~~~~~~~~~~~~~~~ prompt The next scheduled run for this job is: select job, next_date, next_sec from user_jobs where job =: jobno;
Spool OFF;
- 5. Generating an Analysis Report Call @ / Home / ORAPAID / Product / 92 / RDBMS / Admin / Spreport
In the process, you want to enter a start snapshot and terminate snapshot number.
- Six Remove Timers SQL> Execute DBMS_Job.Remove ('JOB_ID');
- Seven Delete Historical Data
Delete data in the Stats $ SNAPSHOT table, deleted the data in other tables
Oracle provides scripts for Truncate
[Orapaid @ bj37 admin] $ cat sptrunc.sqlRemRem $ Header: sptrunc.sql 19-feb-2002.11: 36:. 28 vbarrier Exp $ RemRem sptrunc.sqlRemRem Copyright (c) 2000, 2002, Oracle Corporation All rights reserved RemRem NAMERem. sptrunc.sql - STATSPACK - Truncate tablesRemRem DESCRIPTIONRem Truncates data in Statspack tablesRemRem NOTESRem Should be run as STATSPACK user, PERFSTAT.RemRem The following tables should NOT be truncatedRem STATS $ LEVEL_DESCRIPTIONRem STATS $ IDLE_EVENTRem STATS $ STATSPACK_PARAMETERRemRem MODIFIED (MM / DD / YY) Rem VBarrier 03/05/02 - Segment StatisticsRem Cdiaaleri 04/13/01 - 9.0Rem Cdialeri 09/12 - SP_1404195Rem Cdialeri 04/11/00 - 1261813Rem Cdialeri 03/15/00 - CreateDrem
undefine AnystringSet ShowMode Off Echo Off; WHENEVER SQLERROR EXIT;
Sptrunc.lis
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------- * /
promptprompt Warningprompt ~~~~~~~ prompt Running sptrunc.sql removes ALL data from Statspack tables. You mayprompt wish to export the data before continuing.promptpromptprompt About to Truncate Statspack Tablesprompt ~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~ prompt If you would like to continue, press
Delete from Stats $ Snapshot; delete from stats $ database_instance;
COMMIT;
Rem this is required to allow Work without Rem review package or restarting the instance.
Promptprompt Truncate Operation CompletePrompt
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------- * / spool OFF;
WHENEVER SQLERROR Continue; Set Echo On; [ORAPAID @ bj37 admin] $
- Nine adjustment of Statspack collection threshold
SQL> Execute statspack.snap (i_snap_level => 0, i_modify_parameter => 'True');
SQL> execute statspack.snap (i_snap_level => 10);
SQL> Execute statspack.snap (i_snap_level => 5);
- Modify the default value of the threshold by the following statement
SQL> execute statspack.modify_statspack_parameter (i_buffer_gets_th => 100000, i_disk_reads_th => 100000);
--10 Qualification analysis report
--11 Generate performance analysis report with shell (you can also generate)
Oracle_sid = $ oracle_sidexport oracle_sidoracle_home = 'cat / etc / oratab | Grep ^ $ ORACLE_SID: | CUT -F2 -D': 'Export Oracle_HomePath = $ Oracle_Home / BIN: $ PATHEXPORT PATH
echo "please enter the number of seconds between snapshots." Read Elapsed
$ Oracle_home / bin / sqlplus -s perfstat / perfstat <
Sleep $ ELAPSED $ ORACLE_HOME / BIN / SQLPLUS -S Perfstat / Perfstat <
Select Name, Snap_ID, TO_CHAR (Start_time, 'YYYYMMDD: HH24: MI: SS') from Stats / $ SNAPSHOT, V / $ Database
WHERE SNAP_ID> (SELECT MAX (SNAP_ID) -2 from stats / $ snapshot);
--Stats $ SQL_STATISTICS View - This view is especially useful for the total overhead of the statistical system SQL and the percentage of SQL is reused.
set lines 80; set pages 999; column mydate heading 'Yr.Mo Dy Hr' format a16; column c1 heading 'Tot SQL' format 999,999,999; column c2 heading 'SINGLE USE SQL' format 999,999; column c3 heading 'Percent re_used SQL' Format 999,999; Column C4 Heading 'Total SQL Ram' Format 999,999,999;
Break on MyDate Skip 2;
select to_char (snap_time, 'yyyy-mm-dd hh24) mydate, total_sql c1, single_use_sql c2, (single_use_sql / total_sql) * 100 c3, total_sql_mem c4from stats $ sql_statistics sq, stats $ snapshot snwhere sn.snap_id = sq.snap_id; - -Oracle 8i stats $ sqltext is used to delete the huge storage overhead related to the stats_sql_summary table. - Oracle 9i Stats $ SQLText (SQL Source Code)
SELECT * from Stats $ SQL_TEXT
--Stats $ latch_misses_summary records the latch failed in the Oracle database
Select * from stats $ latch_misses_summary - Generate latch report
select sn.snap_time, sl.parent_name, sl.where_in_code, sum (nwfail_count) sum_nwfail, sum (sleep_count) sum_sleep from stats $ latch_misses_summary sl, stats $ snapshot snwhere sn.snap_id = sl.snap_id group by sn.snap_time, sl.parent_name , sl.where_in_code;
--Stats $ SQL_SUMMARY Table
--SQL Statistical Sarm is one of the most important tables in the StatsPack tool. - SQL adjustment can often greatly affect the performance of Oracle systems
--Stats $ Parameter
Select * from stats $ parameter where name like '% Optimizer_Mode%'
*********************************************************** ************** STATSPACK system table
Stats $ ROLLSTAT
Stats $ latch
Select * from stats $ latch_children
Select * from Stats $ librarycache (the hit rate of the library cache entries must be maintained in more than 90%, otherwise adjust the SGA Oracle shared pool)
Select * from stats $ waitstat where wait_count> 0 Judging whether the object is not correct, one of the best ways is to observe the free table waiting if the free table is waiting very high, indicating that the table has competitive INERT or UPDATE Task These tables do not define enough free tables
Select * from stats $ enqueue_statselect * from stats $ enqueue_stat where failed_req #> 0 Analysis Stats $ enqueue_stat table is important to remember the queue waiting is the normal part of Oracle processing
Select * from Stats $ sysstat
Select * from Stats $ SESSSTAT
SELECT * FROM V $ STATNAME
Select * from stats $ SGASTAT ********************************************************* **************************************** Statspack transaction table Stats $ buffer_pool_statistics
Select * from stats $ buffer_pool_statistics This table is general metric for buffer pool efficiency
Select * from Stats $ FILESTATXSSTATS $ FILESTATXS is the most important table of Oracle Adjustments, including the details of the Oracle data file, including the number of IOs, write IO quantity, and the processing process experience I / O subsystem Load Balancing Find the "Hot" file and hot table find the peak event of database read and write activities
*********************************************************** ** statspack event table
SESESTEM_EVENT
SELECT * from Stats $ session_event
SELECT * from Stats $ IDLE_EVENT
Select * from Stats $ BG_EVENT_SUMMARY Stats $ bg_event_summary aggregated background events for all database instances and stats $ system_event
*********************************************************** *********************************************************** *************
- Extended STATSPACK Collection Server Statistics - Collection of 5 times per second
[newvers @ bj37 newvers] $ VMSTAT 2 5 Procs Memory Swap IO System CPU R b W SWPD Free Buff Cache Si So Bi Bo in CS US Sy ID 0 0 0 118136 49908 98268 1668004 0 0 0 0 0 0 0 0 1 0 0 0 118136 1668012 0 0 4 88 177 790 2 0 97 0 0 0 118136 51468 98276 1668012 0 0 0 0 0 0 118136 49608 98276 1668092 0 0 30 138 286 1144 4 1 95 0 0 0 118136 51340 98276 1668116 0 0 12 92 183 263 0 0 100
r Run Quage When this value exceeds the number of CPUs on the server, there is a CPU bottle neck PI is a page switch page to refer to the server's memory shortage US is the user cpusy is the system cpuid is idle WA is waiting for all CPU values (US SY ID WA) Always equal 100%
- Use VMSTAT to identify CPU bottlenecks
Linux View common system information for CPUs / proc / directory CAT / Proc / CPUInfo | GREP Processor | WC -L
IBM AIX and HP_UXLSDEV-C | GREP Processor | WC -L
The number of CPUs in Solaris Psrinfo -V | GREP "Status of Processor" | WC -L
--VMSTAT recognizes frequently used CPUs
If US and SY are proximatively, the CPU is running unique to the full load operation. It can pointed out that the value of the CPU bottleneck is the value of the run queue "R" exceeds the number of CPUs.
Identify RAM memory bottleneck
HP / UX shows memory size
Dmesg
Show DEC-UNIX memory size
UERF -R 300 | grep -i mem
Memory size is displayed on AIX
Step 1: LSDEV-C | GREP MEM Step 2: Lsattr -El Mem0
Show SQLARIS on Memory Size Prtconf | GREP -I MEM
- Use the TOP tool to display RAM
TOP-D 2 is refreshed every 2 seconds
--HP and Solaris, view memory with Glance
RAM Memory and Exchange Disks Because the exchange (PI) spends a lot of time to copy memory back to RAM from the swap disk, he will slow down the speed of the server, on the Oracle Database Server, to solve the page in exchange problem: more Small SGA more RAM reduces RAM requirements (reducing the demand for "(PGA) memory of the program global zone to reduce the RAM consumption of the database server.)
- Test potential I / O bottleneck in AIX
The WA column indicates that the CPU percentage of the external OS service is not necessarily I / O bottleneck.
Get scripts for VMSTAT information
Connect Perfstat / Perfstat
Drop Table Stats $ VMSTAT
create table stats $ vmstat (start_date date, duration number, server_name varchar2 (20), runque_waits number, page_in number, page_out number, user_cpu number, system_cpu number, idle_cpu number, wait_cpu number
TableSpace Perfstatostorage (Initial 10M Next 1M Pctincrease 0);
--Linux operating system VMSTAT get tool script
1. You must set Oracle_home to your directory:
Oracle_Home = / home / newvers / product / 922. Must set in the sqlplus command to set Oracle_sid $ oracle_home / bin / sqlplus perfstat / perfstat @ prdyp << EOF
3. Must change the sampling time by setting Sample_Time: Sample_Time = 300
--get_vmstat.ksh
Oracle_home = / home / newvers / product / 92export oracle_home
PATH = $ ORACLE_HOME / BIN: $ PATHEXPORT PATH
Server_name = uname -a | awk '{print $ 2}' typeset -u server_nameExport server_name
SAMPLE_TIME = 300
While True Do Vmstat $ {Sample_Time} 2> / TMP / MSG $$
# Run Vmstat and Direct The Output Into The Oracle Table
CAT / TMP / MSG $$ | SED 1, 3D | awk '{Printf ("% S% S% S% S% S% S / N", $ 1, $ 8, $ 9, $ 14, $ 15, $ 16)}' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPUdo $ ORACLE_HOME / bin / sqlplus -a perfstat / perfstat @ prdyp << EOF insert into perfstat.stats / $ vmstat values (sysdate, $ sAMPLE_TIME, '$ SERVER_NAME', $ RUNQUE, $ PAGE_IN, $ Page_OUT, $ USER_CPU, $ SYSTEM_CPU, $ IDLE_CPU, 0); EXIT EOF DONE DONERM / TMP / MSG $$ ********************************** *********************************************************** ************************** - Adjusting the server environment
1. Adjusting the server environment is a prerequisite for adjusting the Oracle database If the CPU or memory on the server is overloaded or bottlenecks, it is impossible to use any Oracle to resolve performance issues.
2. Maximize the utilization is a sacred target excess processing ability and RAM will be difficult to use
- Online Server Monitoring Tools Use Top and SAR Tools to view details of CPU and memory activities
- Adjust the CPU consumption
- Adjust memory consumption research basic memory management, server memory segmentation skills
Page replacement indicates that the Oracle server needs to exceed the number of RAM memory
The longest remedy for exchanges is to increase the size of the SGA or increase memory for the database server.
- Report server statistics
Use TOP monitoring server TOP
Load averas load average
A total of 3 values
1. The first is an instant load in the past 1 minute. The second is the load average of the previous 5 minutes. The third is the load average of the past 15 minutes.
Regardless of any time, the load average exceeds 1 can be considered to be overloaded.
You should run VMSTATA immediately to view run queue values.
Top Viewing the details of the CPU
Process ID --- PID User Name - ISER Dispatched Priority - Primpi - Ni Memory Size of Ni Task - Size Status Execution Time
TOP provides a lot of information, Oracle DBA only needs to understand several columns.
The load average this value exceeds 1 indicates that the server has overloaded CPULOAD to display the load NI of each CPU is the assignment priority of the task.
- Server Task Load Balancing
Determine the CPU peak time to avoid executive batch programs in peak time and use excessive programs to CPUs (try to transfer them to night)
--rpt_top_sql.sql
- Find 10 points and 3 pm Most consumption CPU SQL_TextSelect To_Char (Snap_time, 'YYYY-MM-DD HH24'), SUBSTR (SQL_Text, 1, 50) from Stats $ SQL_SUMMARY A, Stats $ Snapshot Snwhere A.Snap_ID = sn.snap_idand to_char (snap_time, 'hh24') = 10OR to_CHAR (Snap_time, 'HH24') = 15ORDER BY ROWS_PROCESSED DESC; - Drawing by Drainage - View Priority PS --elf | More
Change the NICE value
- If the memory occurs, you can use it.
Reduce sort_area_size to implement multithreaded servers and reduce shared_pool or db_block_buffers to reduce memory needs - server memory settings
The kernel settings for memory (SHMMAX, SHMMNI, DB_MAX_PCT) are key to obtain effective Oracle performance, and all kernel parameters should be checked to ensure that server memory has been properly configured.
It is also necessary to verify the configuration of the swap disk, and the swap disk is used to accept that most of the server from physical RAM page replacement memory frame is recommended to set the size of the switch to the size of the physical RAM.
*********************************************************** *********************************************************** *******************
newvers 1382 1 0 Feb16? 00:01:38 ora_pmon_prdypnewvers 1384 1 0 Feb16? 00:00:34 ora_dbw0_prdypnewvers 1386 1 0 Feb16? 00:02:21 ora_lgwr_prdypnewvers 1388 1 0 Feb16? 00:02:06 ora_ckpt_prdypnewvers 1390 1 0 Feb16? 00:00:28 ora_smon_prdypnewvers 1392 1 0 Feb16? 00:00:00 ora_reco_prdypnewvers 1394 1 0 Feb16? 00:01:08 ora_cjq0_prdypnewvers 1398 1 0 Feb16? 00:00:00 ora_s000_prdypnewvers 1400 1 0 Feb16? 00:00:00 ora_d000_prdypnewvers 1402 1 0 Feb16? 00:00:09 ora_arc0_prdypnewvers 1404 1 0 Feb16? 00:00:05 ora_arc1_prdypnewvers 1410 1 0 Feb16? 00:02:17 ora_qmn0_prdypnewvers 11849 1 0 Feb26? 00:17:15 ora_p000_prdypnewvers 11851 1 0 Feb26? 00 : 17: 18 ORA_P001_PRDYPNEWVERS 11853 1 0 Feb26? 00:17:33 ORA_P002_PRDYPNEWVERS 11855 1 0 Feb26? 00:12:53 ORA_P003_PRDYPNEWVERS 11857 1 0 Feb26 00:06:28 ORA_P004_PRDYPNEWVERS 7180 1 0 14:20? 00:00:00 OraclePrDyp (local = no) newvers 10195 1 0 17:22? 00:00:00 OraclePrDyp (local = no) newvers 10197 1 0 17:22? 00:00:00 OraclePrDyp (local = no) newvers 10199 8672 0 17:22 PTS / 0 00:00:00 Grep ORA
ora_pmon_prdyp - Process Monitor process ora_dbw0_prdyp - database writer process ora_lgwr_prdyp - log writer process ora_ckpt_prdyp - checkpoint process ora_smon_prdyp - System Monitor process ora_reco_prdyp - Distributed recovery ora_cjq0_prdyp --ora_s000_prdyp - this server The process generates all the required database calls to query the service. It returns the result to the result of the process DNNNORA_D000_PRDYP - Server DNNNNNND 000_PrC0_PrDYP - Archive Process 1 ORA_ARC1_PRDYP - Archive Process 2 ORA_QMN0_PRDYP - OA_P000_PRDYP - Parallel Query Process 1 (because of the parallelism of the table, the background is started) ORA_P001_PRDYP - Parallel Query Process 2OA_P002_PRDYP - Parallel Query Process 3ORA_P003_PRDYP - Parallel Query Process 4ORA_P004_PRDYP - Parallel Query Process 5ORACLEPRDYP (LOCAL = no) - Local connection - View the background process Select * from v $ bgprocess where paddr <> 00 ';
- Treatment of common archive suspend problems caused by Arch? Database default installation, usually in non-invasive mode, users can change to archive mode as needed. In many cases, the user only modifies the pattern of the database, and does not start the ARCH process. In this case, after a period of operation, the entire database hangs, query the V $ session_wait view, will find Archive Required events. Manually archive or automatically start Arch to resolve.
Another situation is that the database is running in archive, but the archive log requires insufficient space, and the database hangs, the solution is to solve the incapacity of the archiving, and then restart the archive process.
*********************************************************** *********************************************************** ********** - 7. Adjusting the network environment for Oracle NET is the most common misunderstanding is: Improve network performance by adjusting the Oracle network parameters! In addition to a few exceptions, all network communications Are in the range of Oracle, it is not possible to adjust the Oracle NET only in the Oracle environment. It is only the one-layer on the OSI mode. It is actually all network adjustments in the Oracle environment.
In fact, it is very small for Oracle Net to improve performance.
DBA can control the size and frequency of the network package
You can change the update interval of the snapshot to deliver more data on the network with a smaller frequency interval.
This chapter includes the following parties involving network adjustment issues: Optimizing Oracle NET Configuring Oracle Features Other Oracle Features for Network Performance Using STATPACK Monitoring Network Performance Adjustment Distribution Network
- Optimize Oracle Net Configuration
There are several adjustment parameters that can affect the server between Oracle NET connection performance should be adjusted with the help of qualified network administrators.
The settings containing the following parameter files can affect the size and frequency of the package delivery.
SQLNET.ORA server file .automatic_ipcsqlnet.ora customer file. Break_poll_skiptnsnames.ora .sdu Tdulistener.ora .sdu Tduptotocol.ora .tcp.nodelay
1 .-- Ptotocol.ora's.tcp.nodelayoracle suggestion only When you encounter TCP times, use TCP.Nodelay when there is a lot of communication between database servers, setting TCP.Nodelay can greatly improve performance 2 The automatic_ipcautomatic_ipc parameter accelerates to the local connection because it can first check if the automatic_ipc = on oracle NET will first check if there is a local database with the same alias if there is a connection. The interpretation is a local connection, which bypass the network layer 3. All Oracle Net should use this setting to improve performance
- Oracle recommends (MTU maximum transmission unit, this value is fixed) Set SDU tnsnames.ora .sdu Tdulistener.ora .sdu TDU
--Sqlnet.ora's Break_poll_skip parameters
- SQLNET.ORA DISABLE_OOB parameters
EPC_DISABLED environment variable strongly recommended DBA disabled Otrace
1. Turn off the database and listening program 2. From the $ ORACLE_HOME / OTRACE / Admin directory to remove the * .dat file 3. Re-establish the DAT file with UNIX 4. Register or .cshrc registration in Unix Oracle .login or .cshrc "EPC_DISABED = TRUE" specifies "EPC_DISABLED = TRUE" in the operating environment of the file. This will disable the Otrace function 5. Modify the listner.ora file to specify EPC_DISABLED = True6 in SID_DESC to all databases. Restart the database and listening program 7. From $ Oracle_home / bin runs otrccref command
- Oracle features that affect network behavior
There are usually several options that can be used to manage network activities.
Use the multithreaded server (MTS) using a multithreader server (MTS) using ODBC to use Oracle replication
- Use array to get network throughput
- Using multithreaded servers
Unless the connection on the server exceeds 300, Oracle does not recommend using MTS
SELECT * FROM V $ quecheselect * from V $ dispatcher This two views will point out if the number of MTS distributors is too low. Although the number of dispatcher is specified in the init.ora file, it can also be used in SQL * DBA.
ALTER SYSTEM SET MTS_DISPATCHERS = 'TCPIP, 4' online change
If you encounter problems related to MTS, you can call SVRMGRL> ALTER System SET MTS_DISPATCHERS = 0; the command quickly returns to a dedicated server
- Connect sharing and network performance
Database connection using ODBC will generate a large amount of load in many Oracle applications
- Adjust Oracle Copy
- Monitor network performance from Oracle Statpack
SESTEM_EVENT WHERE EVENT LIKE 'SQL%';
This is an example of an output report, which shows the event and the waiting time for each time. When the network has a packet transfer amount overload, this report is very suitable for displaying a specific time - rpt_event.sql
SELECT TO_CHAR (SNAP_TIME, 'YYYY-MM-DD HH24') MyDate, EVENT, E.TOTAL_WAITS-NVL (B. TOTAL_WAITS, 0) Waits, (((E.TIME_WAITED_MICRO-NVL (B.Time_WaITED_Micro, 0)) / 100) / NVL ((E. TOTAL_WAITS, 0)) ,. 01) AVG_WAIT_SECS
From Stats $ SYSTEM_EVENT B, STATS $ SYSTEM_EVENT E, Stats $ SNAPSHOT SNWHERE E.SNAP_ID = sn.snap_idand b.snap_id = E.SNAP_ID-1and B.Event = EVENTAND E.Event Like 'SQL * Net%' and E .total_waits-b.total_waits> 100and e.time_waited_micro-b.time_waited_micro> 100; - Adjust Distributed Network
Use NetStat to monitor network activities
NetStat -sp TCP
- Adjust disk I / O subsystem using Statspack
Oracle adjustment factors affecting disk IO
Understand how instance parameters affect disk IO.
The three areas in Oracle settings can directly affect the number of disk IOs.
1.oracle instance (init.ora) settings can affect disk io2.oracle objects (tables and index) settings can also affect disk IO3.Oracle SQL execution plans also directly affect disk IO
Oracle instance
Big DB_BLOCK_SIZE big DB_CACHE_SIZE uses multiple block size multiple databases to write (dbwr) process big Sort_Area_size large online re-log log
Oracle object
Inside the database (the settings of the tables and indexes can reduce physical disk IO)
The smaller the value of the low PCTUSED PCTUSEED, the less IO that appears in SQL insertion, the less low PCTFree, if PCTFree is set, allowing all lines to be extended without segmentation, then less in subsequent SQL selection, less Disk IO Use the index to reorganize the table to cluster rows If the same physical order placement in the most commonly used index
3 Oracle SQL In the SQL statement, there are many technologies to reduce physical disk IO.
Use indexes or hints (hint) to prevent unnecessary full surface search
Use bit mapping index
Apply SQL prompt
Oracle internal structure and disk IO
- Find a sparse table (free table lost balance!)
SELECT SUBSTR (DT.TABLE_NAME, 1, 10) C3, DS.EXTENTS C5, DS.BYTES / 1048576 C4, DT.NEXT_EXTENT / 1048576 C8, (Dt.empty_Blocks * 4096) / 1048576 C7, (DS.BYTES * 4096) / 1048576 C6, (AVG_ROW_LEN * NUM_ROWS) / (db.blocks * 4096) C10
From sys.dba_segments ds, sys.dba_tables dtwhere
- Adjust Oracle Database instance
Next we must adjust the Oracle database instance, and view all parameters, configuration, and settings that affect performance.
STATAPACK detection example potential performance problem
1. Oracle Example Overview
2. Adjust the Oracle data buffer
3. Adjust the sharing pool overview
4. Adjust the library cache
5. Adjust Oracle Sort
6. Adjust the rollback segment
7. Oracle 9i RAM Memory Adjustment
Usually, the Oracle instance includes two components: system global zone (SGA) and Oracle background process
We usually control SGA and background processes by adjusting Oracle parameters.
When Oracle is started, Oracle uses the malloc () command to create a RAM memory area, which is often also known as an Oracle area.
Oracle DBA can control the size of SGA's size of SGA management that can greatly affect performance
Although initialization parameters have hundreds of thousands of but only few Oracle9i parameters are very important: buffer_pool_keep This data buffer pool is used to perform a full table scanned small table buffer_pool_recycle This pool is used to save a full table scan. The table block DB_CACHE_SIZE This parameter determines the number of database block buffers in Oracle SGA, which is the most important parameter of Oracle Memory DB_BLOCK_SIZE database block size can be generated (as a general rule, the larger the size, the more physical IO Less, the faster the overall performance) DB_FILE_MULTIBLOCK_READ_COUNT This parameter is used for full-table search or large table scanning, and multi-threaded Large_Pool_Szie is a sharing pool for SGA usage when using multithreaded servers. Special area. The biggest pool is also used for parallel Query RAM process log_buffer This parameter will determine the amount of memory allocated for Oracle Re-log buffer. If you have a lot of update activities, you should assign more space shared_put_size this parameter definition to log_buffer. All users in the system include the SQL area and data dictionary cache. - Three Oracle parameters can affect the size of the data buffer DB_CACHE_SIZEBUFFER_POOL_KEEPBUFFER_POOL_RECYCLE
Oracle recommends that the hit rate of the buffer exceeds 90% DBA to control the data buffer hit by adding the number of data blocks to the initialization parameter.
Internal structure of database buffer pool
- Use the StatsPack to monitor the use of the buffer pool - buffer pool hits the rate and statack
SELECT * from Stats $ buffer_pool_statistics
SGA_MAX_SIZE = 6000mdb_block_size = 16384db_cache_size = 5000MBuffer_Pool_Keep = (1400, 3) Buffer_pool_recycle = (900, 3)
- can use in Oracle8
Alter Table Customer Storage (Buffer_Pool Keep);
ALTER TABLE User.table_name storage (buffer_pool keep);
- Advanced Keep Pool Candidate Identification In addition to the small tables scanned by full menu, the Keep buffer is also ideal for setting frequent data segments of data segments.
- Use the X $ BH view to identify the average number of blocks more than 5 times, and occupy more than 20 data blocks in the cache --hot_buffer.sql - Identify hotspot objects
select object_type mytype, object_name myname, blocks, count (1) buffers, avg (tch) avg_touchesfrom sys.x $ bh a, dba_objects b, dba_segments swhere a.obj = b.object_id and b.object_name = s.segment_name and b. Owner not in ('sys', 'system') Group by Object_name, Object_Type, Blocks, Objhaving Avg (TCH)> 5and Count (1)> 20;
After identifying the hotspot object, you can decide to isolate the object to the KEEP pool as a general rule. There should be enough RAM storage to be used for the entire table or an index number, if you want to increase the page table for the Keep pool, you need to add to init. . ORA's buffer_pool_keep parameter adds 104 blocks
- Adjust the target of the Recycle Pool Place the object of the Recycle pool is to separate the big table of the full table search frequency. In order to find a big table for full-test search, we must help the full table search report obtained from Access.SQL: Access_recycle_syntax.sql
select 'alter table' || p.owner || || p.name || 'storage (buffer_pool recyle);' '.' from dba_tables t, dba_segments s, sqltemp s, (select distinct statement_id stid, object_owner owner, object_name Name from plan_table where operation = 'table access' and options = 'full') Pwhere s.addr || ':' || to_char (s.hashval) = p.stid and t.table_name = p.Name and T.OWNER = P.WOWNER AND T.BUFFER_POOL <> 'RECLE'HAVING S.BLOCKS> 1000Group by P.WNER, P.NAME, T.NUM_ROWS, S.BLOCKSORDER by SUM (S.Executions) DESC;
- Assign Recycle pool to the table
ALTER TABLE User.table_name storage (buffer_pool recycle);
Note: DBA should take the SQL source code before adding any table to the Recycle pool, and verify that this query gets 40% of the table.
- Advanced Recycle Pool Adjustment The following query uses X $ BH.TCH to identify the object with a buffer contact count, but the total amount exceeds the 5% data cache of the entire cache, which is potential in Recycle buffer. The candidate object placed in the pool, because they may make data blocks that will not be reused to occupy a large number of cache spaces
Select Object_Type Mytype, Object_name MyName, Blocks, Count (1) Buffers, 100 * (Count (1) / TOTSIZE) PCT_CACHEFROM SYS.X $ BH A, DBA_OBJECTS B, DBA_SEGMENTS S, ()
- Cancel tracking function ALTER system set_enabled = false;
--Statistics_level
The STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database The level of the setting affects the number of statistics and advisories that are enabled.:
Basic: No Advisories or Statistics Are Collected.
Typical: The Following Advisories or statistics is collected:
Buffer cache advisory MTTR advisory Shared Pool sizing advisory Segment level statistics PGA target advisory Timed statistics ALL: All of TYPICAL, plus the following: Timed operating system statistics Row source execution statistics The parameter is dynamic and can be altered using: ALTER SYSTEM SET statistics_level = Basic; alter system set statistics_level = type statistics_level = all; current settings for parameters can be shown using:
SHOW PARAMETER statistics_levelSHOW PARAMETER timed_statisticsOracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile. By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level, along with any other conflicting Parameters:
Alter System RESET TIMED_STATISTICS Scope = SPFILE SID = '*'; This Setting Will Not Take Effect Until The Database Is Restarted.
At this Point The Affect of the Statistics Level Can Be Shown Using The Following Query:
COLUMN statistics_name FORMAT A30 HEADING "Statistics Name" COLUMN session_status FORMAT A10 HEADING "Session | Status" COLUMN system_status FORMAT A10 HEADING "System | Status" COLUMN activation_level FORMAT A10 HEADING "Activation | Level" COLUMN session_settable FORMAT A10 HEADING "Session | Settable"
Select statistics_name, session_status, system_status, activation_level, session_settablefrom v $ statistics_lease; a Comparison Between The Levels Can Be BE Shown As Follows:
SQL> ALTER System Set Statistics_level = Basic;
System altered.
SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v $ statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable --------------- ------------------------------------- ----- Buffer Cache Advice DISABLED DISABLED TYPICAL NOMTTR Advice DISABLED DISABLED TYPICAL NOPGA Advice DISABLED DISABLED TYPICAL NOPlan Execution Statistics DISABLED DISABLED ALL YESSegment Level Statistics DISABLED DISABLED TYPICAL NOShared Pool Advice DISABLED DISABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics DISABLED DISABLED TYPICAL YES
Rows SELECTED.
SQL> ALTER system set statistics_level = type;
System altered.
SQL> Select statistics_name, 2 session_status, 3 system_status, 4 activation_lease, 5 session_settable 6 from v $ statistics_level 7 Order by statistics_name;
Session System Activation SessionStatistics Name Status Status Level SetTable -------------------------------------- ------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALL YESSegment Level Statistics ENABLED ENABLED TYPICAL NOShared Pool Advice ENABLED ENABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics ENABLED ENABLED TYPICAL YES8 rows selected.
SQL> ALTER System Set Statistics_level = all;
System altered.
SQL> Select statistics_name, 2 session_status, 3 system_status, 4 activation_lease, 5 session_settable 6 from v $ statistics_level 7 Order by statistics_name;
Session System Activation SessionStatistics Name Status Status Level SetTable -------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ All Yessegment Level Statistics Enabled Enabled Typical Noshared Pool Advice Enabled enabled Typical Notimed OS Statistics Enabled All Yestimed Statistics Enabled ISTISTISTICS Enabled Typical Yes
Rows SELECTED.
SQL> Hope this helps. Regards Tim ...-- memory adjustment
SELECT * FROM V $ SGA;
- Adjust SGA
Name Value ---------------------------- Fixed Size 452184Variable size 402653184database buffers 251658240redo buffers 667648
SELECT * FROM V $ SGASTAT;
Pool Name Bytes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- fixed_sga 452184 buffer_cache 251658240 log_buffer 656384shared pool errors 8940shared pool enqueue 171860shared pool KGK heap 3756shared pool KQR M PO 1393788shared pool KQR S PO 177272shared pool KQR S SO 5120shared pool sessions 410040shared pool sql area 61446860
Pool Name Bytes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- shared pool 1M buffer 2098176shared pool KGLS heap 2613480shared pool PX subheap 19684shared pool parameters 39012shared pool free memory 125812664shared pool PL / SQL DIANA 3445584shared pool FileOpenBlock 695504shared pool PL / SQL MPCODE 637644shared pool PL / SQL PPCODE 48400shared pool PL / SQL SOURCE 14344shared pool library cache 19376952
Pool Name Bytes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- shared pool miscellaneous 8639216shared pool PLS non-lib hp 2068shared pool joxs heap init 4220shared pool table definiti 2632shared pool trigger defini 1128shared pool trigger inform 528shared pool trigger source 624shared pool Checkpoint queue 564608shared pool VIRTUAL CIRCUITS 265160shared pool dictionary cache 1614976shared pool KSXR receive buffers 1032500POOL NAME Bytes ----------------------------------------------- Shared POL character set object 432136shared pool FileIdentificatonBlock 319452shared pool message pool freequeue 833032shared pool KSXR pending messages que 840636shared pool event statistics per sess 1908760shared pool fixed al Location callback 268lage pool free memory 83886080java pool free memory 83886080
41 rows selected.
- UGA size, UGA mainly contains partial memory settings
Show parameters area_size;
Name Type Value --------------------------------- ------------------------------ bitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608hash_area_size integer 1048576sort_area_size integer 524288workarea_size_policy string AUTO
- Calculate data buffer hits
Select Value from V $ sysstat where name = 'Physical Reads' 4383475
Select * from v $ sysstat where name = 'Physical Reads Direct' 3834798select * from v $ sysstat where name = 'Physical Reads Direct (LOB)' 374616
Select * from v $ sysstat where name like 'consistent gets' 1198738167
Select * from v $ sysstat where name like 'db block gets' 53472785
x = Physical Reads Direct Physical Reads Direct (LOB)
100- (Physical READS-X) / (Consistent Gets DB Block Gets-x) * 100
100- (4383475-3834798-374616) / (1198738167 53472785-3834798-374616) * 100
- Hit rate of shared pool Select SUM (Pinhits) / SUM (Pins) * 100 "Hit Radio" from v $ librarycache;
- About the sort part
SELECT NAME, VALUE from V $ SSSTAT WHERE Name Like '% Sort%
Select Sorts / (Sorts (Memory Sorts (Disk) from DUAL
SELECT 0 / (17038425 0) from Dual
- About LOG_BUFFER
SELECT NAME, VALUE from V $ SSSTAT WHERE NAME IN ('Redo Entries', 'Redo Buffer Allocation Retries');
Redo Buffer Allocation Retries / Redo Entries> 1% Consider adding log_buffer
- Other views V $ db_cache_advice, v $ PGA_TARGET_ADVICE, V $ java_pool_advice and v $ db_shared_pool_advice
