Maybe you are tired of the development of the five nights, starting to test OCP; Maybe you just walked out of the ivory tower, determined to do a big job in database management? After a effort, I finally had a job of DBA, sitting next to the computer, excited to sweat, but I don't know how to adjust, optimize the database; facing the sudden failure, the phone is not stopping, the boss It is desirable to stand next to it, I don't know if you can calm down?
Many database management, tuning, backup and recovery, PL / SQL development, may also have a lot of troubleshooting experiments, but when the fault is really falling, it is so terrible, usually the production database that is usually running. It has been in a trend of performance, and the trouble always appears from what you unexpected, Amen.
The database system itself is always worth paying attention to the creed: countless bugs, object failures, magnetic pieces, index reconstruction, and many unexpected emergencies; programmers without SQL experience are also very harmful. Trouble Manufacturer: Writing poor performance SQL and creating a poor storage object; who is the most terrible troublemaker? Hey, it is from DBA itself, a minor revision of the database, may result in a disaster.
As a newbie DBA, the concept of Oracle system structure is very important. If you want to be more thoroughly understood, you must do a lot of experiments, you have to get a small life, you can do something, huh, 千Don't do it on the production library; if you want to grow from trouble makers to a trouble end, you only care about it, after all, your production environment and learning environment is very limited, through the relevant forum Post, from the experience and lessons of netizens, expand the skills of discovering and solving problems.
Independent learning and thinking is the key to the rapid growth of DBA. Many newbie discovers the problem or unknown phenomenon, I always go to consult with senior DBA in the first time. In fact, this is bad habits, try to analyze and reasonably on the problem. If there is no short, you can send it on Google or related forum. Passed help, there will always be many unexpected surprises on the Internet. I believe that 90% of the problem has an answer. How is the key to find it.
Don't pay attention to INTERNAL, the foundation is the main, there must be a certain PL / SQL programming technology, firmly grasp the database backup and recovery, then improve the ability of system tuning and SQL optimization, when the technology is accumulated At the level, it is natural to naturally naturally naturally naturally naturally.
Good communication skills help solve problems faster. Many times, there may have been solved the problem, but I don't know why this problem will be produced. At this time, I can consult the project leader or related programmer, try to clear the roots of the problem, if the problem is not fundamentally solved, the problem must be rolled.
As a DBA, you need to provide a unified "database development specification" for the programmer of the project group. If possible, you can also do training as a programmer to write SQL writing and SQL optimization skills, try to make a poor SQL fetal dead belly , Newbie DBA, more integration into the project group, understand the needs of the business system, and master certain database modeling knowledge, and lay the foundation for database structure optimization and SQL optimization by mastering the database structure.
It is essential to study DBA, need to pay attention to: It is not the knowledge of all aspects. There is a choice to study aware of the skills to break through the generals; don't care too much to study DataGuard, install trivial things such as RAC, and install a small technology; (http://www.cnoug.org/viewthread.php?tid= 2226) This is the Oracle FAQ collected by Piner users. I believe that no matter the newcomer, it can be turned over. "Want to do something, must be aware of the unit", as DBA, you must build a working environment for yourself and programmers (this article is taken as an example of Linux platform). On the Linux platform, SQLPlus does not have a callback function, how to build SQLPLUS with a callback environment? (Http://www.dbanotes.net/oracle/uniread-howto.htm) You can refer to the post of Fenng users. There is also the Help and SQL syntax of SQLPLUS, you can refer to the following post (http://www.cnoug.org/viewthread.php?tid=1710). In the version after 9i, the HELP of SQLPlus is installed by default, and the SQL syntax must be installed.
URLs that need newers need to pay attention to: http://tahiti.racle.com http://metalink.oracle.com
Adjustment to operating system / network parameters
As a DBA, there should be quite foundations for Linux / UNIX. Understand the concept of RAID, RAW, LVM, OCFS, ASM, etc. Crtab, etc .; the application server has a certain understanding; about Linux / UNIX problem, you can find the answer to http://www.puschitz.com http://www.puschitz.com/.
Adjustment of initialization parameters (SGA)
Deep understanding of Oracle's initialization parameters is an essential homework that DBA, but does not use adjustment parameters as a life-saving straw, and inappropriate parameters will bring performance decline in performance, and even dangerous data loss; do not think to use hidden The parameters are proud, doing things have intended to be intended, and it can be parallel to it in the system failure.
Without any format, you can meet the needs of SGA adjustments, usually have multiple adjustments to achieve more harmonious effects.
http://blog.9cbs.net/biti_rainy/archive/2004/07/03/learn_oracle_20040703_7.aspx
This post is Biti_Rainy summary for SGA adjustment, which is basically suitable for most situations.
In the 32bit operating system, SGA has
1.7G
Limit, if the phase is broken on the 32bit operating system
1.7G
Restrictions, you need to use special means,
(Http://www.itpub.net/showthread.php?s=&threadid=124424) This post is a breakthrough for each platform SGA for each platform.
1.7G
Summary of restrictions.
In the 64bit operating system, SGA does not require a special method.
3.9g
If you want to break through
4G
If the method breaks through the 32bit system
1.7G
The method is similar, that is, the parameter USE_INDIRECT_DATA_Buffers = True must be used to set the size of the buffer cache using db_block_buffers. Some suggestions about STATSPACK
Don't report to the StatsPack, it can only tell you the operational status of the database in the past, and the performance trend in the future (initialization parameters can not be significantly adjusted and the business has no big changes), Statspack's report, DBA can further fine tune the initialization parameters.
Statspack can tell you the performance bottleneck, just this, the root cause of performance bottlenecks must be dba personally to check; of course, the cause of performance bottlenecks may have been collected, in many collected SQLs need to be careful, If the SQL statement is too long, it is more troublesome, because too long SQL will be truncated; in any case, Statspack is a helper that DBA is not less, (http://www.eygle.com/more/more/ Statspack_list.htm) This is an Eygle netizen's series of research posts for Statspack, and I hope to be useful to you.
If you need to make StatsPack performance trend reports, you can usually use Excel to do it, it is trouble, even written a tool specializing in the StatsPack report, not only can make a beautiful statement faster, And you can manage knowledge. (Http://www.cnoug.org/viewthread.php?tid=20115)
Application about logmnr in tuning
For all, Logmnr is not recommended by tuning. It is mainly used for security audits. In fact, in the investigation system bottleneck, Logmnr is unique, through the review of the log (there is enough patience, it is necessary to have enough patience), can know more clearly Oracle did what time, this is not reasonable? Of course, the logmnr can't tell you reasonably, you must judge yourself.
In the application of the B / S structure, set the session's client_info with dbms_Application_info.set_client_info when connecting, so that when logging with LogMNR, you know that this is the same, the range is smaller; in C / S In the application of the structure, it is usually a long time to disconnect the session for a long time. Every time the customer opens a service module, it is best to set the session's client_info information.
Application about Materialized View in tuning
In the OLAP environment, MVIEW is an effective means of spatial time, less physical read / write, fewer CPU time, faster response speed, so it is not suitable for high-end OLTP environments; in the OLTP environment The larger reports are suitable for use MVIEW to improve query performance. (Http://www.itpub.net/224536.html) This post can be downloaded to the "Expert One On One Oracle" Chinese scan version, and Chapter 13 of the book specifically tells the use of mView.
Application about Stored Outlines in SQL Optimization
Stored Outlines is the function launched to maintain SQL execution plan stability. It is mainly suitable for the migration of the test environment to the product database environment. To modify the source code, etc. In order to ensure the good operation of the product database, we need to stabilize the plan. Artificially adjusts some specific SQL, we can use SQL to be cautious to determine the Outlines required for SQL. (Excerpted from Biti_Rainy, the original URL is as follows.) (Click here the URL will not open the relevant link, copy to the IE address bar) About Stored Outlines,
Http://blog.itpub.net/post/96/1548 can also be referred to as it. Holding Stored Outlines, but found that Outlines is not so good in practical use, usually use Outlines to stabilize the plan more appropriate when SQL uses bind variable.
When the parameter cursor_sharing = exact is initialized, if the query condition is different, there is no way to use the store.
Outlined; If the business logic is encapsulated in Stored Pro, the variables in Procedure will appear in the form of Bind Variable. You can use Stored Outlines to stabilize the implementation plan. For details, you can see it; if there is no text variable in SQL (constant) You can use Stored Outlines.
How to test Stored Procedure with DBMS_Profiler
About DBMS_Profiler Package is mainly used for performance testing of PL / SQL Block and Stored Procedure, in the development phase programmer or DBA needs to perform performance testing of various storage objects, through dbms_profiler package, you can find poor performance in storage objects. , Then make changes; it can be seen that the difference between dbms_profile and Outline is: a stage for development, need to modify the program, one for formal running phase, do not have to modify the program, only change SQL execution plan. Two posts about DBMS_Profiler Package:
http://www.samoratech.com/plsqlprofiler.htm
http://pages.videotron.com/rautils/pages/dbms_profile.htm
How to adjust and optimize SQL
Optimizing SQL is where you can reflect DBA wisdom and value. Wai Event usually caused by TOP 5 in StatsPack, is mainly caused by poor performance; most of the TEMP TABLESPACE Water Water Slips is related to SQL, not excluding the creation and reconstruction index, but this reason should be the responsibility of DBA The big table must be in the system idle in the creation or reconstruction index.
How is the performance of SQL production? This problem is more complicated: bad database structures will lead to bad SQL; there is a programmer's SQL write skills; don't expect the programmer to be SQL write experts, according to the experience of the development, The fastest time to complete the project is the most important, so the programmer will not be too concerned about the performance of SQL, that is, care, it is also very limited.
Programmer conducts a suitable training for SQL optimization, improve their sense of responsibility, explain the cases of the system, and the programmers will work hard to avoid many low-level mistakes; to communicate with programmers, try to guide programmers as possible Describe his difficulties in the database and put forward guidance comments and solutions. For novice DBA, it is usually very interest to tune system parameters or SQL, but I don't know how to do it. In the system parameters, there must be some understanding, or you can ask for discussion with senior DBA. It is advised not to hold too much hope, and you can analyze the system parameters according to the StatsPack report; in SQL tuning Aspects, must be able to recover the poor performance of SQL.
How do I find a poor performance SQL? The following performance indicators (Responds) are usually integrated; to query the eligible SQL query from the V $ SQL or V $ SQLTEXT_NEW_WITHLINES dictionary according to your own situation:
SET LINES 99
COL SQL_TEXT FORMAT A81
Col Bgets_Per Format 99999999.9
SET Long 9999999999
Set PageSize 9999
Select Address, Hash_Value, Disk_Reads, Elapsed_time / 1000000 AS
"ELAPSD_TIME (S)", CPU_TIME / 1000000 AS "CPU_TIME (S)",
Buffer_gets / executions bgets_per, first_load_time, SQL_Text
From v $ SQL
WHERE DISK_READS> 1000 or (Executions> 0 and Buffer_gets / Executions> 50000);
The above query mainly found the SQL statement of Physical Reads> 1000 and Consistent Gets> 50000, of course, you can also limit the response time, usually onsistent gets larger or Physical Reads larger SQL, its response Time It will also be relatively large.
How to generate an execution plan when SQL is executed? Enter SET Autot ON on SQLPLUS to generate a more detailed execution plan; set autot off is to let SQLPlus cancel the execution plan; SET AutoTTRACEONLY only displays the number of rows affected by SQL, executes plan, execute statistics, no output set The set autot ON EXP outputs the resulting result set and execution plan; set autot on stat outputs the executionful result set and statistics. Explain Plan only analyzes SQL, generates execution trees, and outputs the Explain Plan generates the execution plan with select * from table (dbms_xplan.display).
Set autot [RACE] {OFF | ON | Trace [Only]} [exp [lain]] [stat [iStics]]
Explain Plan [Set Statement_ID = & item_ID] for & SQL;
Select * from table (dbms_xplan.display);
How to optimize the performance of poor performance, want to have challenging for any DBA. On this link, DBA must master how to view SQL execution plans and have a certain understanding of the result of the return; if it is a novice, you can use some SQL optimization tools to be tuned, which can be borrowed with Lecco SQL Expert and Quest Toad. It is more difficult to simultaneously understand the tool's understanding of the tool, and I wrote Chinese diagrams for Lecco SQL EXPERT. SQL EXPERT tutorial http://www.cnoug.org/viewthread.php?tid=22327
Quest Toad Tutorial http://www.cnoug.org/viewthread.php?tid=3242 (Acknowledgment to the original author)
Any tool is relatively low, if you think that Lecco or Toad is more written, don't indulge, they are just a cane, you must go beyond it, otherwise your value is worthy of doubt; for SQL optimization, you must More mobile hand test, but also to read all books, inspiration in the experience from others.
When optimizing SQL, it is necessary to analyze SQL in a layer. First, analyze the syntax of SQL, eliminate redundant or wrong query conditions (possibly a programmer handle), the flower is not a lot, the performance can be greatly improved, do not trust the programmer too much, they write It is not correct; followed by the structure of SQL involving the table, especially complex SQL, to check if there is a better connection route, whether the connection field has index, the selectivity of the index, etc. The third repayment is different HINTS. Change the driver of the table. http://www.adp-gmbh.ch/ora/sql/hints.html This post is a list of Oracle Hints, and Hints specific usage can check http://tahiti.Oracle.com.
About SQL tuning details, it is impossible to list, the specific environment must be subject to the implementation plan, through the understanding of SQL, enhance the rationality of the database structure to speculate, reasonable database structure, will be the performance of SQL There is a big improvement; in some cases, the database structure is modified, and the corresponding changes are required on the program, such as partitioning a big table, creating MVIEW. For SQL optimization, you can also study the series of posts for netizens Black_Snail, have a detailed example:
Http://www.dbonline.cn/source/oracle/20031218/oracle SQL Performance TUNING1.HTML
How to track session and TKPROF
Tracking sessions, Oracle provides a lot of means, not only can track the current connection session, or tracking other users' session; through analysis of Trace files, not only master the session activity, you can also find Out of the bottleneck in this session, the tracking of the session is the common method of system tuning and troubleshooting.
Alter session set SQL_TRACE = true / false
Track and stop tracking the current session
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, Serial #, & SQL_TRACE);
You can track and stop tracking the SESSION of the current session, other users.
Alter Session Set Events '& Event Trace Name Context Forever, Level & Level'; ALTER Session Set Events '& Event Trace Name Context Off'
Exec dbms_system.set_ev (& SID, & Serial #, & Event_10046, & Level_12, '');
ORADEBUG EVENT 10046 Trace Name Context ForeVer, Level 12
Detailed discussion on Event Tracking You can refer to HRB_QIUYB posts:
Http://blog.9cbs.net/hrb_qiuyb/archive/2004/06/30559.aspx
Event, SQL TRACE and other tools collect SQL performance status data that is being executed and recorded in the trace file. This tracking file provides many useful information, such as the number of parses. Perform time, CPU usage, physical read, logical reading, etc. These data will be able to optimize your system. IUSER_DUMP_DEST parameter illustrates the directory where the tracking file is generated, and the SQL TRACE must first set the timed_statistics to TRUE in Init & Sid.ora, so that it can get those important time states. Because SQL TRACE is generated TRACE file is very difficult, so use TKPROF to convert it, TkProf has many execution parameters, you can refer to http://tahiti.Oracle.com documentation
The second part http://blog.itpub.net/post/96/15634
Performance diagnosis method based on wait event
Wait Event is a naming section of the Oracle core code, there are two types of waiting events: idle evenets, non-idle events, free events, Oracle, is waiting for some kind of job. Common Free Waiting Events: Client Message, Null Event, Pipe Get, PMON / SMON Timer, RdBMS RPC Message and SQL * NET, Non-Free Waiting Event: Buffer Busy Waits, DB File Scattered Read, DB File Sequential Read, Enqueue, Free Buffer Waits, Latch Free, Log File Sync, Log File Paalle Write, etc.
What is a bottleneck? Once you are familiar with the system's waiting event, you can grasp the key to the problem, and you can use the corresponding method to process the bottleneck of the blocking system, you must not optimize casually, otherwise a wave does not work again, you can be familiar with V $ system_event The total waiting situation of the system, then view the SESSION in the system via V $ session_event, and finally locate the bottleneck object via the V $ session_wait. V $ session_wait is the session level, which contains the real-time information of the session. The most important thing is: it shows more in-depth information waiting for the event and the corresponding resource, which can clearly locate the range of optimization.
V $ session_wait's P1, P2, P3 tells us to wait for the specific meaning of the event, if Wait Event is DB File Scattered Read, P1 = file_id / p2 = block_id / p3 = blocks, then use DBA_EXTENTS to determine hot spot objects; if it is Latch free, P2 is latch, pointing to V $ latch.
Col Event Format A32
COL Name Format A32
SELECT SID, EVENT, P1 AS File_ID, P2 AS "Block_ID / Latch", P3 As Blocks, L.Name
From v $ session_wait sw, v $ latch l
WHERE EVENT NOT LIKE '% SQL%' And Event Not Like '% RDBMS%' And Event Not Like '% MON%'
And sw.p2 = l.latch # ( );
- Waiting for the event and its corresponding latch
Col Owner Format A18
Col Segment_name Format A32
Col Segment_Type Format A32
Select Owner, Segment_Name, Segment_Type
From DBA_EXTENTS
WHERE FILE_ID = & File_ID AND BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID & BLOCKS - 1;
- Seeking events and their hot objects
Select sw.SID, Event, L.Name, de.segment_name
From v $ session_wait sw, v $ latch l, dba_extents de
WHERE EVENT NOT LIKE '% SQL%' And Event Not Like '% RDBMS%' And Event Not Like '% MON%'
And sw.p2 = l.latch # ( ) and sw.p1 = de.file_id ( ) and p2 between de.block_id and de.block_id de.blocks - 1;
- Integrated two SQL, simultaneous display of Latch and hotspots (slower speed)
SELECT SQL_TEXT
From V $ SQLTEXT_WITH_NEWLINES ST, V $ Session SE
Where st.address = se.sql_address and st.hash_value = se.SQL_HASH_VALUE
And se.SID = & wait_sid order by piece;
- If it is a non-idle waiting event, the SID that is waiting for the session can obtain the SQL executed in the execution
By waiting for the event to find out the SQL consumed in the system, it is one of the means for system diagnosis of the DBA. It is just that the process is slightly invisible. Since session is dynamic, it is often a moment of changing, not capture (but targeted), but By filtering V $ SQL or V $ SQLAREA, press [How to Adjust and Optimize SQL] The SQL provided in the section can find SQL with performance issues, capture V $ SQL for a long time, and The captured SQL performs analysis and processing, which can have a large extent to solve performance problems.
Performance diagnosis method based on resource restrictions
If you want to use the Resource Limit function, you must initialize the parameter resource_limit = true, of course, you can also specify the relevant resource_manager_plan parameters more detailed manages resource; for a user's resource limit, you can implement it through the user's profile. About CREATE PROFILE, you can see related documents in Tahiti. If you don't want to create a profile, you can use the default profile, each user is not specified when you specify a profile, will use the default profile. If you have created a PROFILE, you can specify a new profile with ALTER USER & User_name profile & profile_name; you must have a relatively clear consequence of each item in Profile, otherwise it is not set, you will bear your complaint Treatment, the setting information of all Profile can be found through the DBA_PROFILES / DBA_USERS dictionary. The role of storage structure in database design
Understanding the Oracle's storage structure has certain guiding significance in the database (modeling) design. On the unconstrained field, I believe that everyone can select the correct data type for the field, but in some cases, it is necessary to have the principle of storage. Understand, such as Char (1) and Number (1) Who is more suitable? Char (1) has more choices than Number (1) on the value, and storage is also one byte than Number (1), but char (1) and Number (1) speak in performance, and There is not much difference, nothing more than digital conversion formulas are more complicated than characters.
Create Table T1 (ID Char (1), ID2 Number (1))
SELECT ID, DUMP (ID) DID, ID2, DUMP (ID2) DID2, TO_CHAR (193, 'XX') HEX from T1;
I DID ID2 DID2 HEX
- - ----------------------------------
A TYP = 96 LEN = 1: 97 1 TYP = 2 LEN = 2: 193,
2
C1
http://www.itpub.net/211094.html This is a post about the storage principle of the data type, which gives the different data types of reverse calculation formula, the character type is better understood, the character is stored when stored The ASCII code value, which is apparent from the above, but the storage should be a hexadecimal in the data file; the digital storage is more complex, and the 193, 2 is converted into hexadecimal C1 02, then use my own written function UF_DEC to obtain data in the actual write table, http://blog.itpub.net/post/96/2240 This post is the conversion function uf_dec, you can use the hexadecimal Strings turn to decimal.
You can find a compress attribute to reach the compress property to the TAHITI's compress property of the object to mitigate the requirements of Disk Space. As can be seen from the following two colons, the COMPRESS property can be greatly reduced, and the use of the CPU is slightly higher than NOCOMPRESS.
SQL> CREATE TABLE COMPRESS_1 COMPRESS AS SELECT * FROM DBA_OBJECTS;
SQL> CREATE TABLE COMPRESS_2 AS SELECT * FROM DBA_OBJECTS;
SQL> EXEC SHOW_SPACE ('Compress_1', 'Auto'); Total Blocks ............................ 64
Total Bytes ........................... 524288
Unused blocks .........................
Unused bytes ............................ 49152
Last used ext fileid .................... 5
Last Used Ext Blockid ................... 544
Last use block ....................... 2
SQL> EXEC SHOW_SPACE ('Compress_2', 'Auto');
Total Blocks ............................ 256
Total Bytes .........................................................
Unused blocks ......................... 110
Unused bytes ............................ 901120
Last used ext fileid .................... 5
Last Used Ext Blockid ................... 11656
Last use block ......................... 18
It seems that the show_space process comes from http://asktom.Oracle.com, which browss Table / INDEX storage information, http://www.itpub.net/239697.html
This post discusses the use of the Show_Space process, and some netizens have improved this process, I hope to help you.
Recommendations for online heavy definition table
Online heavy definition table is a new feature of 9i, can be reorganized, eliminating the debris in the table, eliminating the debris in the table, http: //www.cnoug.org/viewthread.php? TID = 1680 This is Piner Write About a post on the online heavy definition, introduces the process of online heavy definitions, even if there is not much to say, simply introduce the experience and lessons of my use online heavy definition table.
After the table is redefined, it is found that some of the previously allowed fields to be changed to Not null by dbms_redefinition package. The result has some SQL error, and a very important field default value is also redefined. The result is caused. Be careful from HANG, so be careful when using online heavy definition tables, confident that the online heavy definition does not change the part you don't want him to change.
Advice on partition tables in database design
If the expected amount of data intended in the table is usually necessary to consider the use of partition tables, determine the use partition table, and determine what type of partition (Range Partition, Hash Partition, List Partient, etc.), the partition interval size, etc. The creation of the partition is a kind of tacit understanding. Even the partition table is created, and the partition is defined according to the month, but the program is in the query, the default start time and end time is: the current date -30 to the current date, such as the day Is 9.18, the query condition is generated as 8.18-9.18, and the result is not significantly improved after the partition. Later, the procedure is adjusted, and the system's load is much smaller. For some operations of the partition table, please refer to this post http://www.itpub.net/221061.html
Advice on DataGuard in high availability
DataGuard is a high availability scheme that is launched by Oracle. The main node is synchronized with the alternate node to ensure data synchronization, the standby node as the backup of the primary node, can achieve fast switching and catastrophic recovery,
9.2.0
Start, DataGuard supports logical standby libraries. The physical spare library maximizes data security, reducing system downtime; logic spare libraries allows for hardware investment, assumes report services in the system, with regard to DataGuard's configuration, refer to Piner's post http: //www.cnoug. ORG / viewthread.php? TID = 22640
