Oracle Advanced Training Course 7th Study Notes by jackyang (JackYang.sh@gmail.com) Date: 2006-10-20 this course experiment has passed the computer verification. Performance tuning the following notes correspond to the file ora_train6.ppt provided by the instructor. It is not only about DBA but also closely related to system developers. Performance Tuning is very important in application design and programming, so many performance problems will not occur. Misunderstanding: Optimization after development. After the development is complete, it is very difficult to tune again. It is also related to the system administrator, and whether the Unix and Windows system parameters are optimized. How to optimize? What is tuning? Optimization goals include the availability database hit rate of the response time database. Many tasks in Oracle are carried out in memory. If the desired data cannot be found in the memory. Read from the file. Efficiency will be affected. Optimization goals include: access as few data blocks on the hard disk as possible. Try to access data in the memory. Buffer data blocks in the memory. Do not compile or parse an SQL statement. But access from the shared memory. A type of hit rate. Find the code that has been parsed by SQL in the memory. The target is not found in the memory. If I/O operations must be performed, I/O operations should be as fast as possible. You do not need to wait for resources for any user's operations. Adjust resource contention through optimization. We hope that backup operations and daily management operations will have the minimum impact on the normal operation of the Oracle database. Try to do it at night. Main adjustment items: optimize instances to improve the probability of SQL statement reuse. You want to find the compiled code in the memory. Space management. Including memory space management and data file management to optimize data access. Optimization steps adjustment design adjustment application adjustment SQA adjustment memory adjustment redo log buffer, shared buffer, large buffer adjustment Io adjustment contention (conflict) Adjustment lock, latch (Latch) Adjustment operating system. Files that are closely related to performance adjustments in windows and Windows ORACLE: Alert Log File warning files. Important Oracle operations and internal errors will generate some information in the Alert Log File. Non-default parameters are also displayed. DBA should check Alert Log File once a day, regardless of whether the database is normal or not. Because some problems are accumulated, the sooner the problem is found, the easier it is to solve. Alert Log Files will continue to grow. After checking every day, if you feel OK, clear it. You can also back up data. Use the backgroup_dump_dest parameter to set the location of alert_log_file. This parameter should be set for each database to ensure that the alert files of each database are located in different places, making it easier to find them. You can view the information generated by different processes in the Alert Log File. Background trace fileoracle can trace every background process and generate a trace file after tracking. Trace information generated by different processes is stored in different trace files. The trace file corresponds to each background process. Each future process has many trace files. User trace file users can also generate trace files to record SQL statements. Set trace to true. Each SQL statement is recorded in the trace file. These trace files need to be translated using Oracle tools. This function affects performance. Therefore, this function is generally disabled and can only be enabled during debugging. If the SQL _trace parameter in the Three-Level Initialization parameter file is set to true, the user trace is enabled and the alter session command is used. In the current user session, a procedure set_ SQL _trace_in_session is included in the Oracle program to activate the user trace function, it can be called in an application. Set the user_dump_dest parameter in the initialization parameter file to specify the path of the user trace file. Optimization tool: the dynamic view v $ headers view DBA _ headers utlbstat. SQL and utlestat. sqlutlbstat. SQL start to collect ORACLE data. Utlestat. SQL stops collecting ORACLE data. These two SQL files were used before oracle8 and are not recommended. The generated reports are not easy to read and the results are not saved. After Oracle9i, use statspack to replace the two SQL files and run them at the start and end times. This statspack can be run at any time. When this program is run, snapshots of the database at that time will be generated, including CPU, memory, Io, locks, latches, and contention. In addition, a statspack report can be generated, which is easy to read. Enterprise Manager, easy to use GUI. Oracle diagnostic adjustment package. It can be called through Enterprise Manager. On www.oracle.com.cn, some Oracle experts often download the tool. Some dba_tablesdba_tab_columnsdba_clustersdba_indexesindex_stats views that use a large number of views do not have any statistical data in these tables. The corresponding statistical data is available only after the analyze command is run. X $ tables is supported by Oracle developers and Oracle worldwide. The content format is not public. The table/dynamic view that will be encountered during optimization, five Views v $ statname contains the names of all parameters v $ sysstat top priority in terms of memory-related and disk-related and contention-related and user sessions, statistics of the entire system. Collect the accumulated data from the start of the database. The source data of the statspack tool is obtained from this dynamic view. Contains the statistical values of all parameters statistic # statistical label name parameter name class value parameter value v $ sgastat adjusted Oracle memory parameter pool used to differentiate various uses of poolname corresponding parameter name bytes memory the size of V $ event_name is adjusted for the Oracle database, one depends on the statistical parameters, and the other depends on the event. Each wait for an event to generate an event, locate the event, and locate the problem. Event # nameparameter1parameter2parameter3 v $ system_eventtotal_waits Total wait time total_timeouts total time-out time_waited average_wait average wait time is equally important in front of several tables is sysstat, the session name can be found when sessionstatv $ sesstat and V $ statname are linked. V $ Session V $ session_event v $ event_name v $ session_wait when you see the statistical parameters in these dynamic views, set timed_statistics to true by default in trueoracle9i, in Oracle8i, this parameter defaults to false8i to 9i, which is mainly used to automate management. What we can see in the statistics report is a very important oracle table stats $ lib decode (gethits, gethits) gethits. If it is 0, it becomes 1, if it is not 0, it is equal to gethits. Avoid the gethits value being 0.
Lab 1:Objective: To optimize performance 1. DOS> Cd ora9lab2. DOS> set oracle_sid = db913. dos> sqlplus/nolog4. SQL> connect sys/ora123 as sysdba5. SQL> startup6. SQL> select name from V $ datafile; check which directory the data file is stored in. 7. SQL> Create tablespace perfstat datafile 'd:/perfstat/perfstat1.dbf'size 200 m autoextend on; before creating a tablespace, create the perfstat subdirectory under drive D 8. SQL> @? /Rdbms/admin/spcreate input perfstat_password value: Enter ora123 input default_tablespace value: Enter perfstat input temporary_tablespace value: Enter temp, a temporary tablespace corresponds to the command @? /Rdbms/admin/spdrop 9. SQL> exec statspack. Snap starts statistics and takes snapshots. After doing some operations, take a snapshot. Enter the perfstat directory 10. open another window 11. DOS> Cd ora9lab12. DOS> sqlplus/nolog13. SQL> connect sys/ora123 @ dbbench as sysdba14. SQL> select * from Scott. dept; 15. return to the original window 16. SQL> exec statspack. take another snapshot 17. SQL> select username from V $ session to view the current user 18. SQL> @? /Rdbms/admin/spreport to create a report. You can see that several snapshots have been created. Enter begin_snap value: Input 1, enter end_snap value, input 2, enter report_name value, and press Enter between options, the report name is generated in the default directory. 19. run SQL> host dir to check the directory in which the report is named sp_1_2.lst SP _ start snapshot No. _ end snapshot No.. lst redo Nowait should be greater than 90% and adjusted when the report is smaller. In-memory sort must be greater than 90%. If it is less than 70%, execute to parse will execute the top 5 timed events five events with the longest wait time, which is the key to discovering performance problems. Log File parallel write writes the redo log file background wait events background process waiting for waits from the redo log buffer generally refers to the number of times this report should be carefully studied. 20. SQL> @? /Rdbms/admin/sppurge delete a range of snapshots. Enter the value of losnapid: ID at the beginning of the range, 1 enter the value of hisnapid: ID number at the end of the range, 2. When an application runs between two snapshots, the statistical data is accurate.