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

Oracle.Performance.Tuning notes

Chapter II The Method Behind the Madness

When there is a performance problem, it is a good habit to investigate the vandals systematized eventmeme vandalism session rewards eventual repertoire vandalism sessionkeeper wait.

Create a snapshot at point-in-time 1: create table BEGIN_SYS_EVENT as select from vastsystemsystemevent.Create a snapshot at point-2: create table END_SYS_EVENT as select from vastsystemevent.Then you can know the main event during this period by querying:

Select T1.event, (T2.total_waits-T1.total_waits) "total waits", (T2.total_timeouts-T1.total_timeouts) "total timeouts", (T2.time_waited-T1.time_waited) "time waited", (T2.average_wait-T1.average_wait) "average wait" form BEGIN_SYS_EVENT T1, END_SYS_EVENT T2 where T1.event = T2 event

Using the query above, look at the events that are waiting for the most and categorize them. Is it based on the event of Icano? Like db file scattered read, db file sequential read or free buffer waits. It's also memory-based events, like buffer busy waits.

Then drill down and query the same events from v$session_event for system-level events that may affect system performance according to the query v$system_event, and use it in conjunction with v$session:

Select S.username, S.program, SE.Total_Timeouts,SE.Time_Waited, SE.Average_Wait from v$session S, v$session_event SE where S.sid = SE.sid and SE.event not like 'SQL*Net%' and S.status =' ACTIVE' and S.username is not null

It is quite possible that those system-level wait events are caused by a single session or several sessions.

For large tables, or resource is nervous, it is more appropriate to use estimate to collect statistics.

For the sharing SQL in library cache, the last parse passed. If the underlying data tables change their structure, or their statistics is re-collected, then they will be identified as invalid, and if executed later, they will be re-parse.

When the same SQL is reused in a session, there is no need for soft parsing (soft parse) without closing cursor,also. Reducing soft parsing is the same as reducing hard parsing, which is good for improving performance.

Chapter 8 Database Tuning (configuring database block size)

Determine the BLOCK SIZE of the database! The optimized database block size is a very important thing in the database! When planning block sizes, consider the following:

1. How long does it take for 1 data block to be processed by Ihampar O

2. The buffer cache size and usage of the database

3. What is the impact of large index blocks on the system

4. What is the impact of having more than one data block in a row

5. What's the effect of having less space left in a block?

6. If the length of the line is increased during update, what is the effect of the migration?

7. The influence of row migration and linking on the performance of Iripple O

If it is like a data warehouse system, which mainly provides read database system, you should configure the maximum block size supported by that Oracle version! For other systems, it is best to configure the block size with the same size as the operating system block size!

Initrans: the default is 1 for data blocks and 2 for index blocks. Each oracle block has a block area, with a table dictionary and a row dictionary and a transaction slot (transaction slot). When a transaction wants to modify one or more rows in the block, the transaction uses the transaction slot to determine whether the block belongs to itself. These slots play a major role in oracle's row-level locking and read-consistent view technology! When a transaction wants to modify a row in a block, he must first "sign" in the available transaction slot in the block! Then the lock byte of each row modified by the transaction is set to identify which rows are locked. If only 1 slot is not enough, when another transaction needs to modify other rows in the same block (insert, update, delete), you need to borrow 24 bytes from the pctfree of block to allocate 1 slot in block header area. If this happens frequently, it will result in:

1. Dynamic allocation slots degrade performance because oracle manages space at the block level, rather than just modifying the data in the block.

2. Because you need to borrow 24 bytes from pctfree to dynamically generate slots, if this happens in many transactions, it will take up a large amount of space, because the borrowed space will not be released, and the calculation of pctfree will be incorrect, potentially resulting in row migration.

Maxtrans: defines the maximum number of transactions in which one block can modify the data in the block at the same time. The default value is 255th. However, it does not mean that each block can really support 255 transactions, when concurrent transactions to the block, transaction slots need to be allocated.

Freelists: exists the segment header where the table is located, and the available blocks are in the linked list. If the blocks are full, they will be removed here. Basically, 1 buffer busy waits event indicates that there is freelists competition.

Table space planning

If all the extensions in the tablespace are the same, no fragmentation will occur!

1. For tablespaces, create four logical buckets:small, medium, large and x-large.

2. Put the objects in the database into the above 4 buckets. That is, which are small objects, large objects and very large objects.

3. Define the appropriate tablespace default storage parameter (default storage) for each buckets, ensure that initial and next are the same, pctincrease is 0, and create a new tablespace with the default storage parameter (default storage) determined above.

4. Build the appropriate objects in the corresponding tablespace without storage storage parameters, so that those objects accept the default storage parameters in the table space. However, for those objects with high concurrency or very large concurrency, consider using storage storage parameters to define them separately.

5. Repeat the above 4 steps to define each class of objects (table, index, cluster).

An example of a 700g database:

Bucker Size Limit

Small Less than 64M

Medium Large than small,but less than 256M

Large Large than medium,but less than 1024M

X-Large Large than 1024M

Bucker Size for initial and next

Small 256K

Medium 1M

Large 4M

X-Large 16M

Chapter IX Parallel Query Tuning

Set the parallelism level in 3 ways:

1. Table and index level

2. The query is prompted by parallel

3. Number of Oracle Base cpu, default usage level

Create table test

(column1 number (4)

Column2 number (4) parallel (degree 4)

View level: select degree from user_tables where table_name='TEST'

Modification level: alter table test parallel (degree 6)

Create index myindex on test (column) storage (initial 1m next 1m) parallel (degree 4)

Use SQL:

Select / + parallel (alias,6) / coluns from test alias

Select / + noparallel / coluns from test; / / cancel the parallelism level on the table

Select statistic,value from v$pq_sysstat

Select * from v$pq_sesstat

Chapter X contention tuning

Consistent reading of multiple versions

The transaction slot (transaction slots) in the header area of each block is also called interested transaction lists (ITL). The TIL contains three important structures: transaction ID (transaction ID)-"TID,Undo Blcok Address (UBA, which tells before image where to exist), and SCN if the transaction is committed. After the transaction modifies the data in the block, it stores its own transaction ID in the transaction slot, and the corresponding service process (servicing the transaction) then copies the original value (before image) of the column to be modified to the rollback segment assigned by the transaction. Once the data is modified, the transaction slot is not cleared immediately. This work is left to the process of reading the block later, which is also called Block delay cleanup (delayed block cleanout).

Block's header area also contains a system change number (not to be confused with system commit number) and a serial number (sequence number), which is used to determine the version of the block. When you make a data modification, you get a new SCN with a sequence number of one, and each time the row is updated, the sequence number is incremented by 1 until the commit or the sequence number reaches 254.The SCN must be re-obtained. Like the transaction slot of a data block, each rollback segment holds a transaction table in the first block (the header block) of the segment. The transaction table of this rollback segment contains the transaction information that uses the rollback segment, as well as the block address (DBA) of the last rollback block used for that transaction.

When a query starts, the service process gets a SCN of the current database. When the service process reads a data block, it checks the system change number in the ITL to determine whether the block reads consistently. If the ITL of the block contains a higher number, it knows that the block has been changed and committed after the query starts. The service process then has to go to the rollback segment to find the read-consistent (same SCN) version of the block as at the beginning of the query. What if the service process finds that consistent version? It gets a string of transaction Id from the ITL of that block, and then gets the before image according to the transaction ID to the corresponding one or more rollback segments (perhaps multiple transactions have occurred in the block). The transaction ID is important, so it is not deleted from the ITL.

Another situation is that the system change number in ITL is smaller than the SCN in the query, which means that the data is submitted at the beginning of the query, and the service process reads it directly, which is current state.

The last case is that there is no system change number in ITL, and the service process reads the rollback segment header and finds that all transactions have been committed. If the current SCN exists in the transaction table in the rollback segment header, Oracle copies the SCN to the ITL, and then reads the block. But if the current SCN,Oracle is not found, the current block version and the block stored in the rollback segment will be used to create a read-consistent version of the block. The before image stored in the rollback segment is used to reconstruct the data of that block, and the oracle searches the transaction table of each rollback segment header block to see if there is a transaction ID in ITL. If, for some reason, the service process cannot rebuild the read consistent image (before image does not exist in the rollback segment), the query will fail with a "ORA-01555-Snapshot too old" error.

Note: when multiple transactions read and write the same block at the same time, there will be multiple versions of the same block in buffer cache. Oracle may have to rebuild the before image of this data block several times, which is called block cloning. Blocks in Buffer cache are accessed through hash table, and multiple clones of the same block are the same hash address (the physical address of the block does not change no matter how many clones there are). Very many block clones can cause "cache buffers chains latch".

Rollback segment contention

The pre-allocated extensions in each transaction write-back segment are carried out sequentially and in a loop. When an extension has been filled with rollback entries, Oracle continues to write down an available extension. The number of times that the same transaction writes before image to the extended boundary is called wrap, and in V$ROLLSTAT, the column wraps represents the number of times that the extent is crossed when the transaction writes before image.

Writing the rollback segment is also done in buffer cache, because the rollback segment needs to be read into memory before it can be manipulated. The first header block of the rollback segment contains the transaction table. The transaction table in this header block contains all the active transaction information in the segment and will stay in memory for a long time. If there are many transactions, it will be accessed and modified frequently, so there will be competition in this rollback segment header.

How to find that there is competition for rollback segments in the database? Since the rollback segment in buffer cache is also from the buffer cache of the database, if there is competition, it also reflects the "buffer busy waits" event. You can find the segment name by querying the name of the segment, combining dba_data_files,dba_extents and uet$. Query v$waitstat to detect rollback blocks and wait:

Select * from v$waitstat where class in ('undo header','undo block')

Class count time

Undo header 42331 1922

Undo block 32541 1132

Query again after a period of time, if the values are increased, it means there is competition.

Temporary segment competition

When the sort operation starts, it first creates a temporary period, and when the sorting is finished, the temporary segment is deleted. To create and delete any segment in the database, the process needs to get a "space management transaction (ST) enqueue", with only one ST enqueue in the database. So it will lead to temporary competition.

Querying v$sort_segment can help determine the size of temporary tablespaces. It can report the maximum space ever used by all sorting operations in the database.

Latches

Latch is a serialized locking mechanism that is used to serialize access to shared data in SGA. Three latch:cache buffers lru chain, redo copy and cache buffers chains that need to be paid attention to.

Eleventh palm I take O tuning

 RAID1+0: suitable for both large reading and writing

 RAID5: it is suitable for reading large ones, but not suitable for writing large ones.

The  filesystem and RAW:Veritas 's Quick IMaGUP O or other systems support direct IMaGO can bypass the operating system cache, read and write directly to the disk is no worse than using RAW, and is easy to manage.

Chapter XII Operating System Tuning

Configure memory

For cpu whose main frequency is less than 500MHz, each cpu is equipped with at least 512m memory. If it is greater than 500MHz, at least 1 GB of memory is allocated to each cpu.

Configure the kernel

Two important UNIX resources that need to be adjusted are shared memory segments and semaphores. AIX cannot adjust shared memory segments and semaphores. The following lists the various variables that can be adjusted:

 shared memory segments:

SHMMAX: a single maximum shared memory segment in bytes. Oracle first acquires large enough shared memory segments to fit the SGA, and if not, requests multiple shared memory segments as each component of the SGA. However, a large enough continuous shared memory segment must be given to the largest shared pool.

SHMMIN: the smallest shared memory segment, this parameter can be ignored.

SHMMNI: set the maximum number of shared memory segments, which is generally fine by default.

SHMSEG: sets the maximum number of shared memory segments that a processor can use.

 Semaphore:

SEMMNS: sets the maximum number of semaphore available to the system.

SEMMNI: the largest semaphore set (set). UNIX allocates semaphore on a set basis.

The largest number of semaphore in SEMMSL:semaphore set.

Other  needs to be configured: (each unix may be different)

The largest number of users at the maxusers:OS level, session. It affects other parameters, such as npro,nfile,maxuprc.

Npro: the maximum number of processes supported by internal nuclear power. It includes user process and oracle background process. It should be set higher than SEMMNS. Nfile: the maximum number of open files supported by the kernel can be set higher because it requires less memory. Maxuprc: the maximum number of processes each user can have. Generally, oracle-related processes, background processes, etc., all belong to oracle users.

Asynchronous IO

When asynchronous writes to the disk, it returns before it is finished. The operating system sets DISK_ASYNCH_IO. If asynchronous IO is set, set db_writer_processes to 1, not dbwr_io_slaves. Aix can use lsattr-E-l aio0 to view asynchronous IO.

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