--1. Create perfstat table space Create TableSpace Perfstat DataFile '/Home/rapaid/oradata/prdyp/perfstat.dbf' size 500m; - 2. Sys Dogin Execute the following script @ / home / orapaid / product / 92 / rdbms . File_name from dba_data_files; create tablespace perfstat data/PrDyp/perfstat.dbf 'size 500m; - Before installation, the system parameters can be installed and running StatsPack you may need to set the following system Parameters: 1. Job_Queue_Processes In order to be able to create automatic tasks, execute data collection, which requires greater than 0. You can modify this parameter in the initial test parameter file. Modify this / home / oraPAID / Admin / PrDYP / PFile. ORA file needs to restart the database 2. TIMED_STATISTICS collects the timing information of the operating system, which can be used to display time-like statistics, optimize the database 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. Timed_statistics parameters may be changed at the instance level SQL> alter system set timed_statistics = true; System altered alter system set timed_statistics = false; - before mounting view in this directory file sql> host dir sp * oracle 8i performing $ ORACLE_HOME / rdbms / Admin / Statscre.Sqloracle 9i Execute $Oracle_home/rdbms/admin/spCreate.sql@/home/newvers/product/92/rdbms/admin/spcreate - After the installation is installed (there should be more .lis File) SQL> Host Dir sp * SQL> Host Find "ORA-" * .lissql> Host Find "Err" * .lis - On UNIX, you can view the appropriate error information with the following command $ ls * .lis $ GREP ORA- * .LIS $ grep err * .lis in this step, if an error occurs, then 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 privilege: / * II. Test installed StatsPack runs statspack.snap can generate system snapshots, run twice, then execute spreport.sql can generate a based on two time points report. If everything is normal, the installation is successful.
Has perfstat / perfstat landing * / SQL> execute statspack.snapSQL> execute statspack.snapSQL> @ / home / newvers / product / 92 / rdbms / admin / spreportSQL> @ / home / newvers / product / 92 / rdbms / admin / spauto - making four statspack collection system status 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 cdialeri 02/16/00 - 1191805Rem Cdiaaleri 12/06/99 - 1059172, 1103031Rem Cdialeri 08/13/99 - CreatedRempool spauto.lis ---- Schedule a Snapshot to be run on this instance every hour, on the hourvariable jobno number; variable instno number; beginselect 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;
/ Promptprompt Job number for automated statistics collection for this instanceprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~ Prompt note that this job number is needed when this Job Number is neededhen model 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子 帖子~~~~~~~~~~~~ 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_secfrom user_jobswhere job =: jobno; spool off; - 5. Generate analysis report call @ / home / orapaid / product / 92 / rdbms / admin / spreport Generate this procedure To enter Start Snapshot and Termination Snaps - Six Removal Timers SQL> EXECUTE DBMS_Job.Remove ('JOB_ID'); - Seven Delete Historical Data Deletes the data in the Stats $ SNAPSHOT table, and the data in other tables will delete Oracle to delete Oracle for Truncate. [ORAPAID @ bj37 admin] $ cat sptrunc.sqlremem $ header: sptrunc.sql 19-Feb-2002.11: 36: 28 VBarr ier 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 cdialeri 04/13/01 - 9.0Rem cdialeri 09/12/00 - sp_1404195Rem Cdialeri 04/11/00 - 1261813Rem Cdialeri 03/15/00 - CreatedRemundefine AnystringSet ShowMode 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
Rem This is required to allow further snapshots to work without Rem recreating package or restarting the instancealter package statspack compile; promptprompt Truncate operation completeprompt / * ---------------------- -------------------------------------------------- - * / spool off; WHENEVER SQLERROR Continue; set echo on; [ORAPAID @ bj37 admin] $ - Nine adjustment StatsPack's 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); - modified threshold value by the following statement default SQL> execute statspack.modify_statspack_parameter (i_buffer_gets_th => 100000, i_disk_reads_th => 100000); - 10 Squiring Analysis Report - 11 Use shell to generate performance analysis reports (can also be generated) Oracle_SID = $ ORACLE_SIDEXPORT ORACLE_SIDORACLE_HOME = 'CAT / etc / oracle_home =' CAT / etc / oracle_Home = 'CAT / ETC / ORATAB | GREP ^ $ ORACLE_SID: | CUT -F2 -D': ! 'export ORACLE_HOMEPATH = $ ORACLE_HOME / bin: $ PATHEXPORT PATHecho ". please enter the number of seconds between snapshots" read elapsed $ ORACLE_HOME / bin / sqlplus -s perfstat / perfstat << execute statspack.snap; exit sleep $ elapsed $! Oracle _HOME / bin / sqlplus -s perfstat / perfstat << execute statspack.snap; select name, snap_id, to_char (start_time, 'yyyymmdd: hh24: mi: ss')! From stats / $ snapshot, v / $ databasewhere snap_id> ( SELECT MAX (SNAP_ID) -2 from Stats / $ SNAPSHOT); - Stats $ SQL_STATISTICS View - This view is used for the total overhead of the statistics system SQL and the percentage of SQL is reused. Set lines 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 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 Record the latch failed to latch the Oracle database select * from stats $ latch_misses_summary-- generate reports latch 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 statistics Siter is one of the most important tables in the STATSPACK tool. - SQL adjustment often greatly affects Oracle The system's performance --Stats $ parameterselect * from stats $ parameter where name limited '% Optimizer_Mode%' ************************************* ******************************************** StatsPack system table Stats $ ROLLSTATATATS $ LATCHSELECT * from Stats $ latch_childrenselect * from Stats $ librarycache (library cache table The hit rate of the item must be kept 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 freedom The table is waiting for the free table to wait very high, indicating that the table has a competitive inert or Update Task these tables do not define enough free table Select * from stats $ enqueue_statselect * from stats $ enqueue_stat where failed_req #> 0 Analysis Stats $ When the enqueue_stat table is a very important thing is to remember the queue waiting is the normal part of Oracle processing. Select * from Stats $ sysstatselect * from statnameselect * from V $ STATNAMESELECT * FROM Stats $ sgastat ********* *********************************************************** *************** StatsPack transaction table Stats $ buffer_pool_statistics select * from stats $ buffer_pool_statistics This table is the general metric of buffer pool efficiency Select * from Stats $ filestatxsstats $ filestatxs is about Oracle adjustment One of the most important tables includes details of the Oracle data file, including reading IO, writing IO quantity, and waiting for I / O subsystem load balancing found "
Hotspots and Hot Table Find a peak event for database read and write activities ********************************************** ******************* statspack event table select * from stats $ system_eventselect * from stats $ session_eventselect * from stats $ idle_eventselect * from stats $ bg_event_summary stats $ bg_event_summary summary of all databases STAT $ SYSTEM_EVENT *********************************************************************************** *********************************************************** ************* - Extended StatsPack Collection Server Statistics - A total of 5 times per second [newvers @ bj37 newvers] $ VMSTAT 2 5PROCS MEMORY SWAP IO SYSTEM CPUR BW SWPD FREE BUFF CACHE SI SO BI BO IN CS US Sy Id0 0 0 118136 49908 98268 1668004 0 0 0 0 0 0 0 0 0 0 0 118136 51472 98276 1668012 0 0 4 88 177 790 2 0 970 0 0 118136 51468 98276 1668012 0 0 0 0 0 118136 49608 98276 1668092 0 0 30 138 286 1144 4 1 950 0 0 118136 51340 98276 166811 0 0 0 12 92 183 263 0 0 0 100R run queue to exceed the server The number of CPUs will exist that the CPU bottleneck PI is a page change page Replacement operation indicates that the server has a memory shortage, USA is the user cpusy is the system cpuid is idle WA is waiting for all CPU values (US SY ID WA) Is equal to 100% - use VMSTAT to identify CPU bottleneck Linux View CPU Number / Proc / Directory has Common System Information CAT / PROC / CP UINFO | GREP Processor | WC -Libm AIX and HP_UXLSDEV-C | Grep Processor | WC -LSolaris Display CPU Number PSRINFO -V | GREP "Status of Process" | WC -L - VMSTAT identifies frequent CPUs to use SY and approximation 100 indicate that the CPU runs uniquely pointed out that the CPU bottleneck is running queue "R"
The value exceeds the number of CPUs recognition RAM memory bottleneck HP / UX display memory size DMESG display DEC-UNIX memory size UERF -R 300 | Grep -i memaix display memory size first step: LSDEV-C | GREP MEM second step 2 : lsattr -el MEM0 Display SQLARIS on Memory Size PrtConf | Grep -i MEM - Use TOP Tools to display Ramtop -D 2 to refresh once every 2 seconds - HP and Solaris can use Glance to view memory RAM memory and swap disks because of exchange ( PI) Time will cost a lot of time to copy the memory to the RAM from the swap disk, so he slows down the speed of the server, on the Oracle Database Server, Solution to Page Instrument: Smaller SGA More RAM Reduce RAM Demand (Reduce the demand for the memory of the program global zone to reduce the RAM consumption of the database server.) - Detecting potential I / O bottleneck WA columns in AIX indicating that the CPU percentage of the external OS service is not necessarily It is to have I / O bottlenecks to get the script connect perfstat vmstat information / perfstatdrop table stats $ vmstatcreate 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 PerfstatStorage (Initial 10mnext 1 MpctIncrease 0); - Linux operating system VMSTAT Get Tool Script 1. You must set Oracle_home to your directory: Oracle_home = / home / newvers / product / 922. must . sqlplus command set ORACLE_SID $ ORACLE_HOME / bin / sqlplus perfstat / perfstat @ prdyp << EOF3 necessary to change the sampling time by setting sAMPLE_TIME: sAMPLE_TIME = 300 - get_vmstat.kshORACLE_HOME = / home / newvers / product / 92export OR ACLE_HOMEPATH = $ ORACLE_HOME / bin: $ PATHexport PATHSERVER_NAME = uname -a | awk '{print $ 2}' typeset -u SERVER_NAMEexport SERVER_NAMESAMPLE_TIME = 300while true do vmstat $ {SAMPLE_TIME} 2> / tmp / msg $$ # run vmstat and direct the Output Into The Oracle Tablecat / 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 << EOFinsert into perfstat.stats / $ vmstatvalues (sysdate, $ sAMPLE_TIME,' $ SERVER_NAME ', $ RUNQUE, $ Page_IN, $ PAGE_OUT, $ USER_CPU, $ SYSTEM_CPU, $ IDLE_CPU, 0);
EXITEOFDONEDONERM / TMP / MSG $$ ************************************************************** *********************************************************** ************ - Adjust the server environment 1. Adjust the server environment is a prerequisite for adjusting the Oracle database If the CPU or memory on the server is overloaded or bottleneck It is impossible to resolve performance issues with any Oracle 2. Maximum utilization is a sacred target extra processing capabilities and RAM will be difficult to use - Online Server Surveillance Tool Use Top and SAR tools to view the details of CPU and memory activities - Adjust the CPU Consumption - Adjusting Memory Consumption Research Basic Memory Management, Server Memory Skills Page Instrumentation Represents Oracle Server The need to exceed the number of RAM memory, the longest remedy is the size of the SGA Or add a memory for the database server - Report server statistics use Top Monitoring Server Topload Average The load average has 3 values 1. The first is an instant load in the past 1 minute. 2. The second is the load average of the past 5 minutes. 3. The third is that the load average of the past 15 minutes is no matter whether the load average exceeds 1, it can be considered that the processor should run VMSTATATA immediately to view the running queue value Top View the details of the CPU Details Process ID --- PID User Name --User Dispatched Priority - Pri Priority - Ni Memory Size - Size Status Execution Time TOP provides a lot of information Oracle DBA only need to understand the average of several load averages more than 1 indicates that the server appears Overload CPULOAD demonstrates that the load NI of each CPU is a task assignment priority - server task load balancing determines the peak time of the CPU, avoiding the execution of the batch program and uses the CPU to use too high (try to transfer it to the night) Execution) - RPT_TOP_SQL.SQL - Find 10 points and 3 pm Most 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') = 15o Rder by rows_processed desc;
- Sort by row - View priority ps -elf | more change NICE Value - If the memory is paged, you can reduce the need to reduce Sort_Area_size to reduce the value of Sort_Pool or DB_BLOCK_BUFFERS to reduce memory - Server Memory Settings For memory settings (SHMMAX, SHMMNI, DB_MAX_PCT) are critical to obtain effective Oracle performance, and you should repeatedly check all kernel parameters to ensure that server memory has been properly configured, and the switching disk is configured, swap disks for Accepting Most Servers from Physical RAM Page Testing Memory Frames It is recommended to set the size of the swap disk to a double 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: 1 5 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 Orara_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 - Server This process generates all required database calls to query the service. It returns the result to the result of the process DNNNORA_D000_PRDYP - Server DNNN Scheduler ORA_ARC0_PRDYP - Archive Process 1 ORA_ARC1_PRDYP - Archive Process 2 OA_QMN0_PRDYP - ORA_P000_PRDYP - Parallel Query Process 1 (Because the table's parallelism is set, the background is started) ORA_P001_PRDYP - Parallel Query Process 2ORA_P002_PRDYP - Parallel Query Process 3OR_P003_PRDYP - Parallel Query Process 4ORA_P004_PRDYP - Parallel Query Process 5ORACLEPRDYP (LOCAL = NO) - Local Connection - View background process Select * from v $ bgprocess where paddr <> 00 '; - Common archived hanging problem Process caused by Arch Database suspend? When the database defaults, it is generally in non-archive mode, and the user 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 Both are in the range of Oracle, not capable of adjusting Oracle NETs only in the Oracle environment, one layer of OSI mode, is located on a specific network protocol, which is actually in the Oracle environment. For improving performance, the Oracle NET can work. Few DBA can control the size and frequency of the network package to change the update interval of the snapshot, and deliver more data on the network at smaller frequency intervals. This chapter includes the following involves network adjustment problems. Part: Optimizing Oracle NET Configuring Other Oracle Features Other Oracle Features Using StatPack Monitoring Network Performance Adjustment Distribution Network - Optimizing Oracle Net Configuration There are several adjustment parameters to affect server Oracle NET connection performance should be helpful in qualified network administrators Under the adjustment network The following parameter files include settings that can affect the size and frequency of the network package passed, the frequency SQLNET.ORA server file .automatic_ipcsqlnet.ora customer file. Break_poll_skiptnsnames.sdu tdulistener.ra.sdu tduPtotocol.ora.sdu tduptotocol.ora.tcp.nodelay1.- -Ptotocol.ora, TCP.NodeLayoracle recommends that only use TCP.Nodelay when there is a lot of communication between the database server when you encounter TCP timeouts, and set TCP.NodeLay to improve performance 2. - SQLNET.ORA's Automatic_IPCautomatic_iPC parameters can accelerate local connections because it can get through the network layer. If Automatic_iPC = On Oracle Net will first check if there is a local database defined by the same alias if there is an explanation to a connection Local connection, this bypassing the network layer 3. All Oracle Net should use this setting to improve performance - Oracle recommends according to (MTU maximum transmission unit, this value is fixed) Set SDU tnsnames.ora.sdu Tdulistener .ora.sdu tdu - SQLNET.ORA's break_poll_skip parameter - SQLNET.ORA's Disable_oob parameter EPC_DISABLED environment variable strongly recommended DBA Disable Otrace1. Close Database and Listening Programs 2. From $ Oracle_Home / Otrace / Admin Directory Remove * .dat file 3. Re-establish the DAT file with UNIX 4. In Unix Oracle.Profile .Login or .CSHRC registration file runture "EPC_DISABLED = true". This will disable Otrace function 5. Modify Listener . ORA file so that all databases are specified in SID_DESC in SID_DESC. Restart Database and Listening Programs 7. Run Otrccref Command from $ Oracle_Home / BIN - Oracle Features that affect network behavior can use these technical management networks Activity usually there are several options that use group acquisition Use ODBC Use ODBC to use ODBC to use ODBC to increase network throughput using the array. Use the multithreaded server unless the connection on the server More than 300, otherwise Oracle does not recommend using MTSselect * from V $ queueselect * from v $ dispatcher This 2 view will point out if the number of MTS distributors is too low, although the number of dispatcher is specified in the init.ra file, but You can also use ALTER SESTEM SET MTS_DISPATCHERS = 'TCPIP, 4' online changes in SQL * DBA. If you encounter problems related to MTS, you can call SVRMGRL>
ALTER System Set MTS_Dispatcher = 0; Command Quick Returns to Dedicated Server - Connection Sharing and Network Performance Use ODBC Database Connections Generate a large amount of load in many Oracle applications - Adjour Oracle Copy - Monitor Network Performance from Oracle StatPack Select * From stats $ system_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 is overloaded, this report is very suitable for displaying a specific time - -rpt_event.sqlselect 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 $ SNAPSHOT SNWHERE E.SNAP_ID = sn.snap_idand B . Snap_id = e.snap_id-1and B.Event = E.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 Using NetStat Monitoring Network Activity NetStat -SP TCP - Using StatsPack Adjusting Disk I / O Subsystem Affect Disk IO Oracle Adjustment Factors Understanding Example Parameters How to influence the three areas in disk oo iOracle settings can be directly Impact Disk IO Number 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 ioracle instance large DB_BLOCK_SIZE big DB_CACHE_SIZE use more A block size Multiple Database Write (DBWR) Process Big Sort_AREA_SIZE Large Online Revision Log Oracle Object Inside the database (table and index settings can reduce physical disk IO) low Pctused PCTUSEED, then subsequent SQL insertion The less IO, the less PCTFree, if PCTFree is set, to allow all lines to be extended without split, then generate less disk IO to generate a clustered row in the subsequent SQL selection. Place the table 3 Oracle SQL in the SQL statement in the same physical order in the SQL statement, there is a number of techniques to reduce physical disk IO to use indexes or hints (hint) to prevent unnecessary full-table search use bitmapped index application SQL Tip 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) C10FROM 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 with StataPack detection instances. Overview 2. Adjust Oracle Data Buffer 3. Adjust the Sharing Pool Overview 4 Adjust the library cache 5. Adjust Oracle Sort 6. Adjust the Rolling Segment 7. Oracle 9i RAM Memory Adjustment Usually View Oracle Real Examples include two components: System Global Area (SGA), and Oracle Background Processes We usually control SGA and background processes when Oracle startup when Oracle starts to create a RAM memory area, this SGA It is often also also known as Oracle Region Oracle DBA to control SGA's size correct SGA management can greatly affect performance although the initialization parameters are hundreds of thousands of but only few Oracle9i parameters are very important: buffer_pool_keep This data buffer pool is used for storage Perform a full table scanned small table buffer_pool_recycle This pool uses a very large table of table blocks that perform full mete scans. 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 The database block size can be generated (as a general rule, the larger the size, the less physical IO, the faster the overall performance) DB_FILE_MULTIBLOCK_READ_COUNT This parameter is used for full table search or large table scanning, multi-block Read Large_Pool_SZIE This is a special area in the shared pool used for SGA using multi-threaded servers. The biggest pool is also used for parallel Query RAM process log_buffer This parameter will determine the memory allocated for Oracle Relief log buffers. Quantity. If you have a lot of update activities,
You should allocate more space shared_pool_size this parameter to define all users in the system, including the SQL area and data dictionary cache. The rate of more than 90% DBA can control the internal structure of the data buffer hit rate database buffer pool by adding the initialization parameters - the use of the StatsPack monitoring pool - Buffer pool hits the hit rate and STATPACKSELECT * from Stats $ buffer_pool_StatisticsSSGA_MAX_SIZE = 6000MDB_BLOCK_SIZE = 16384DB_CACHE_SIZE = 5000MBUFFER_POOL_KEEP = (1400,3) bUFFER_POOL_RECYCLE = (900,3) - can be used ALTER TABLE CUSTOMER STORAGE (buffer_pool KEEP) in oracle8; ALTER TABLE USER.TABLE_NAME STORAGE (buffer_pool keep); - advanced KEEP pool The candidate recognition is outside the small table for full-tunition scanning, the Keep buffer is also very suitable for setting frequent data segments of data segments - using the X $ BH view to identify the number of internal blocks of more than 5 times, and occupying in the cache over 20 blocks of data objects --hot_buffer.sql-- identify hotspots objects select object_type mytype, object_name myname, blocks, count (1) buffers, avg (tch) avg_touchesfromsys.x $ bh a, dba_objects b, dba_segments swhere a. Obj = B.Object_idand B. Object_name = s.SEGMENT_NAMEAND B.OWNER NOT IN ('sys', 'system') Group by Object_name, Object_Type, Blocks, Objhaving Avg (TCH)> 5and Count (1)> 20; Identification After 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 the index column, if you want to be ke EP pool adds a page table, you need to add 104 blocks to the buffer_pool_keep parameter for init.ora - Adjust the target of the Recycle Pool Place the object of the Recycle pool to separate the large table of the full table search frequency, in order to find a full table search The big table, we must help to search report from Access.SQL: Access_Recycle_Syntax.sqlselect 'alter table' || p.WNER || '.' || p.Name || 'Storage (buffer_pool recryle) ; 'from dba_tables t, dba_segments s, sqltemp s, (select distinct statement_id stid, object_owner owner, object_name namefrom plan_table where operation =' TABLE ACCESS'and options = 'FULL') pwhere s.addr || ':' || TO_CHAR (s.SHASHVAL) = P.Stidand T.TABLE_NAME = P.Nameand T.OWNER = P.OWNERAND T.BUFFER_POOL <> 'Recycle'
Having s.blocks> 1000group by P.WNER, P.Name, T.Num_Rows, S.BlocksOrder by SUM (S.Executions) desc; - Assign Recy Pool ALTER TABLE.TABLE_NAME Storage (Buffer_Pool Recycle) to the table; : DBA should extract SQL source code before adding any table to the Recycle pool, and verify that this query gets 40% of the 40% of the table in the table - Advanced Recycle pool adjustment The following query uses x $ bh.tch to identify One buffer contact count, but the total amount exceeds the 5% cache of the entire cache, which is a candidate object that is based on the RECYCLE buffer pool, because they may make data blocks that will not be reused. Taking a lot of cache space Select Object_type mytype, object_name myname, block, 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 trace_enabled = false; - STATISTICS_LEVELThe 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 Advisories Or Statistics Are 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 = typical; ALTER SYSTEM SET 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 $ Statisti cs_levelORDER BY statistics_name; A comparison between the levels can 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_settable6 FROM v $ statistics_level7 ORDER BY statistics_name ;
Session System Activation SessionStatistics Name Status Status Level SetTable -------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALL YESSegment Level Statistics DISABLED DISABLED TYPICAL NOShared Pool Advice DISABLED DISABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics DISABLED DISABLED TYPICAL YES8 rows selected.SQL> ALTER SYSTEM SET statistics_level = typical; System altered.SQL> SELECT statistics_name, 2 session_status, 3 system_status , 4 Activation_Level, 5 session_settable6 from v $ statistics_lease; session system activity sessionstatistics name status status level settable --------------------------- --- ---------- ---------- ---------- ---------- Buffer Cache Advice ENABLED ENABLED TYPICAL NOMTTR Advice Enabled Enabled Typical NopGA Advice Enable D ENABLED TYPICAL NOPlan Execution Statistics DISABLED DISABLED 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_settable6 from v $ statistics_level7 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 ENABLED ALL YESTimed Statistics ENABLED ENABLED TYPICAL YES8 rows selected.SQL> Hope this helps Regards Tim ...-- adjust memory select * from v $ sga; - Adjust SGANAME VALUE ---------------------------- Fixed Size 452184Variable size 402653184database buffers 251658240red * from v $ SGASTAT ;
Pool Name Bytes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- fixed_sga 452184buffer_cache 251658240log_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 61446860POOL 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 19376952POOL NAME BYTES ----------- - -------------------------- --------- Shared pool miscellaneous 8639216shared pool PLS NON-LIB HP 2068Shared Pool Jox HEAP INIT 4220Shared Pool Table Definiti 2632Shared Pool Trigger Defini 1128Shared Pool Trigger Inform 528shared Pool Trigg er source 624shared pool Checkpoint queue 564608shared pool VIRTUAL CIRCUITS 265160shared pool dictionary cache 1614976shared pool KSXR receive buffers 1032500POOL NAME BYTES ----------- ----------------- --------- ---------- shared pool 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 allocation callback 268large Pool Free Memory 83886080java pool free memory 8388608041 ROWS SELECTED. The size of the UGA, the UGA mainly contains a portion of memory settings show parameters area_size;
