Oracle tuning (Getting Started and improving)

Over the past decade, Oracle has become one of the most professional databases in the world. For IT professionals, it is important to ensure that Oracle's powerful features are used to improve the productivity of their companies. One of the most effective ways to do this is through Oracle tuning. It has a large number of tuning parameters and techniques to improve the performance of your Oracle database.
Oracle tuning is a complex subject. You can write a whole book about tuning, but in order to improve the performance of Oracle databases, there are some basic concepts that every Oracle DBA should follow.
In this brief introduction, we will briefly describe the following Oracle topics:
-External adjustment: We should remember that Oracle is not running alone. So we'll look at this by tuning the Oracle server to get high performance.
--row re-sequencing to reduce disk I/O: We should understand that the most important goal of Oracle tuning is to reduce I/O.
--oracle SQL tuning. Oracle SQL Tuning is one of the most important areas of Oracle tuning, and it's not surprising that you can dramatically improve the performance of SQL statements with a few simple SQL tuning rules.
--Adjusting Oracle sorting: Sorting has a significant impact on Oracle performance.
--Adjust Oracle's competition: Table and index parameter settings have a significant impact on the performance of UPDATE and INSERT.
We begin by adjusting the environment outside of Oracle. Any Oracle tuning is not helpful if the memory and CPU resources are low.
External performance issues
Oracle is not running alone. The performance of Oracle databases has a lot to do with external environments. These external conditions include the following:
． The lack of CPU--CPU resources slows down queries. When queries exceed the CPU performance of an Oracle server, your database performance is limited by the CPU.
． Memory-The amount of memory available for Oralce can also affect SQL performance, especially in data buffering and memory sequencing.
． Network--A large number of NET8 traffic slows down the performance of SQL.
Many beginners mistakenly believe that the Oracle database should be tuned first, rather than confirming that external resources are sufficient. In fact, any number of Oracle tweaks are not helpful if there is a bottleneck in the external environment.
When examining Oracle's external environment, there are two areas to be aware of:
1. When the number of running queues exceeds the number of CPUs in the server, the performance of the server is limited by the CPU. The remedy is to add extra CPUs to the server or to shut down components that require a lot of processing resources, such as Oracle Parallel Query.
2, memory paging. When memory is paged out, the memory capacity is not sufficient, and the memory page interacts with the swap area on the disk. The remedy is to add more memory, reduce the size of the Oracle SGA, or turn off Oracle's multithreaded servers.
You can use a variety of standard server tools to get statistics on your servers, such as Vmstat,glance,top and SAR. The goal of the DBA is to ensure that the database server has sufficient CPU and memory resources to handle Oracle requests.
Let's take a look at how Oracle's row-resequencing can dramatically reduce disk I/O.
Row-resequencing (reordering of rows)
As we mentioned above, experienced Oracle DBAs know I/O is the biggest component of response time. Disk I/O is particularly powerful because when Oracle gets a block of data from a file on disk, the read process must wait for the physical I/O operation to complete. Disk operations are 10,000 times slower than data buffering. Therefore, if I can minimize I/O, or reduce bottlenecks caused by file competition on disk, the performance of Oracle databases can be greatly improved.
If the system responds very slowly, there can be a quick improvement by reducing disk I/O. If you are accessing a table in a transaction by searching the Primary-key index in a certain range, then the CTAS method of organizing the table will be your primary strategy for reducing I/O. You can speed up the  by physically sorting the rows into the same order as the Primary-key index.
As with disk load balancing, the reordering of rows is simple and fast. By working with other DBA management techniques, you can dramatically reduce response time in highly I/O systems.
In a high-volume online transaction processing environment (on-line transaction processing, OLTP), data is obtained from a primary index, and reordering the rows of a table allows sequential blocks to be the same order as their primary index, This allows you to reduce physical I/O and improve response time in index-driven table queries. This technique works only when the application selects multiple rows, or when multiple queries are issued using the index range search and application to obtain a sequential key. Access to a random, unique Primary-key (primary key) will not benefit from row reordering.
Let's take a look at how it works. Consider one of the following SQL queries, which uses a lasso to get 100 rows:
Selectsalaryfromemployeewherelast_name like ' b% ';
This query will use Last_name_index to search each row to get the target row. This query will use at least 100 reads from the physical disk, because the rows of the employee are stored in different blocks of data.
But what happens to the same query if the rows in the table have been reordered as Last_name_index? We can see that this query only requires three disk I/O to read all 100 employees (one read for the index at a time, two for the reading of the Block), and 97 less chunk reads.
The extent to which the reordering brings about performance is how well you start the row, and how many rows you need to access from the sequence. As to how the rows in a table match the index's sort key, you can view the dba_indexes and Dba_tables views in the data dictionary.
In the Dba_indexes view, view the Clustering_factor column. If the value of the clustering_factor is roughly the same as the number of blocks in the table, then the order of your table and index is the same. However, if the value of the Clustering_factor is close to the number of rows in the table, it indicates that the order of rows and indexes in the table is not the same.
The role of row reordering is not to be underestimated. In large tables that require a wide range of index searches, row reordering can increase the performance of the query by three times times.
Once you have decided to reorder the rows in the table, you can use one of the following tools to rearrange the tables.
. Copy a table using the Oracle Create table as Select (CTAS) syntax
. Oracle9i the table with the form to rearrange the tools
Below, let's look at the tuning of the following SQL statement.
SQL Tuning
Oracle's SQL tuning is a complex topic, and even a whole book is needed to introduce the nuances of Oracle SQL tuning. But there are some basic rules that every Oracle DBA needs to follow, and these rules can improve the performance of their systems. The goal of SQL tuning is simple:
. Eliminate unnecessary large table full table searches: Unnecessary full-table searches result in a large amount of unnecessary I/O, slowing down the performance of the entire database. The tuning expert first evaluates SQL based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or returns less than 7% rows in an unordered table, the query can be adjusted to use an index instead of a full table search. The most common tuning method for unnecessary full table searches is to increase the index. You can add a standard B-tree index to a table, or you can add bitmap and a function-based index. To decide whether to eliminate a full table search, you can carefully examine the I/O cost of the index search and the cost of the full table search, their cost and the reading of the block and possible parallel execution, and compare the two. In some cases, the elimination of unnecessary full table searches can be achieved by forcing the use of a single index, with the addition of an indexed hint in the SQL statement.
. When a full table search is one of the fastest access methods, place the entire table search of the small table in the cache, and the tuning expert should ensure that a dedicated data buffer is used as a row buffer. In Oracle7, you can use the ALTER TABLE XXX cache statement, in Oracle8 or above, where a small table can be forced to be buffered in a KEEP pool.
. Ensure optimal indexing use: This is particularly important for improving the speed of queries. Sometimes Oracle can select multiple indexes to query, and the tuning expert must check each index and make sure that Oracle uses the correct index. It also includes the use of bitmap and function based indexes.
. Ensure optimal Join operations: Some queries use NESTED LOOP JOIN faster, some are HASH join faster, others are sort-merge join faster.
These rules seem simple, but they account for 90% of the SQL Tuning task, and they do not need to fully understand the internal workings of Oracle SQL. Here's a quick overview of the following Oracle SQL optimizations.
Let's start with a brief look at Oracle's sort, and see how the sort operation affects performance.
To adjust Oracle sorting operations
Sorting is a small aspect of the SQL syntax, but it is important that, in Oracle's tuning, it is often overlooked. When you use a statement with CREATE INDEX, order by, or GROUP by, the Oracle database automatically performs the sort operation. In general, Oracle will sort the operations in the following situations:
SQL statement with ORDER BY
Using the SQL Statement of Group by 1 2 Next page > Full text reading tip: Try "←→" button, turn the page more convenient Oh!