Give Plsql wings to fly-plsql optimize

60-80% of database performance issues is related to poorly performing SQL. The 60-80% database performance problem is due to poor SQL statements in production!

This article summarizes the author's recent more than 10 years of work experience and is based on the most important and most effective for Oracle database RBO, CBO, Index, Where conditions are explained at the same time with a large number of cases to help readers from this article learned from the relevant theoretical knowledge of the high-speed application of the production environment in which they are engaged in the optimization process.

The theoretical basis of optimization

By select Count (?) Go on a journey of optimisation

Don't look at Baidu or Google say the following 3 differences?

SELECT COUNT (*)


SELECT COUNT (1)


SELECT COUNT (field name)

SELECT Count (?

) Knowledge of

Oracle's Optimizer

To say Plsql optimization, we need to talk about the Oracle Optimizer knowledge:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Optimization mode of the optimizer

CBO mode

Rbo mode

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Take a look at the evolution of the Oracle Optimizer

So. We know that ORACLE10 starts with the CBO by default. At the CBO, Oracle will take the initiative to choose the optimal running plan, and sometimes we feel that this should be better indexed. But for the CBO. A full TABLE access is more efficient than an index.

So. In the CBO model, what we need to do is:

1. Doing well the statistics of database information
2. Rational construction of our index
3. Optimize our SQL

Let's start with the basics of indexing.
Index is not required to change SQL most directly bring performance improvement of the weapon, when to build the index, how to build? How to make your index more reasonable?
Indexes are categorized by internal structure

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Index by Function classification

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Index by Index Object category

The methodology of index building

The above describes the classification of so many indexes, in order to talk about the methodology of building the index. People may be more concerned about this. Because this is a summary of the experience is also a practical tool ha.

Not recommended for index-based scenarios

The index is very wonderful, but the index is not omnipotent, sometimes you built the index is useless or Perkins, do not work, why? Let's watch it go.

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Conditions in which the index does not take effect

So the index does not build, sometimes built is also Perkins. Why is it? Take a look at the following case studies:

Take the case to illustrate

Plsql optimization > A no-go index optimization case

This sample illustrates the case. Suppose you have a word that participates in the field of the Where condition query. However, it participates in the operator, so it is not indexed in Oracle's internal run-out plan. So we made a little change. How many times has the efficiency improved? 5.3 Times times, 530%. Oh!

In the example above, we can make a summary of the index creation.

Summary of index creation

Table Analyze

Analyze Table VS Dbms_stats

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

Import & Export

Speaking of Import & Export command, people will say ... Hey, this is not very easy, that is: Imp username/[email protected] file=path? Hey..

。

Imagine:

1. You need to import a. dmp file of about 8GB into the database
2. You need to have a library that contains at least 30 tables and that each table has more than 12 million records entered into a. dmp file

Then you go and try this time-consuming.

General Practice of Import
This is a real case. We are in the CCC is the world famous car insurance company project. We regularly synchronize with the CCC headquarters in Chicago, about 8GB. dmp files into our database, because of security reasons need to rely on. DMP interchange files are synchronized to the Chinese database in 0 morning and are synchronized before 8:00 t+1 the next morning.
So. Our DBA has started.

。。。

。

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

From 0 morning to the second day 8:00, obstinately did not lead, a check, the database session has timed out, for 2 consecutive days or so.

So, we changed the original statement a little bit:
Only the original IMP was folded into 2:

• The first one, only the data, not the index. and set it to 10,000 data at a time commit, set up a buffer pool at the same time
• The second, only index. Data is not being directed. and set it to 10,000 data at a time commit, set up a buffer pool at the same time

The results are amazing ... found that in the end it took only 40 minutes to complete, two statements all run. Complete the import:

。 In fact, this is the reason I can use the following 2 examples to illustrate:

1. A folder containing the contents of 8GB files, download with ftpclient, you will find that it seems to never wait for the end, a few hours of such consumption, and then you change to the folder first into a compressed package, and then use ftpclient download. It'll be done in 10 minutes.
2. You use JDBC to write a for loop to insert 1 million records.

   。

   。 As a result, Oracle explodes directly. And you take the batch submission ... The result is amazing!

In fact, we did at the time of the fold, the principle is like the above two cases, is the same truth, reduce IO Read and write, set buffer, batch submission. Let's say your transaction is too large.

。。。。

。

To illustrate the optimization of plsql by cases
Plsql optimization-select in with SELECT EXISTS


How much has it improved here? See the IO to know how much improved, hehe, very fun? Again!
Several optimizations of Plsql optimized-select in

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>


Plsql optimizing-select in, or, and Union interactions

See 3 changes, one last time. How many times has it been lifted? 11.2850-0.0261 divided by 0.0261=431.375,431.375 times ... A SQL.

。。

In the Internet application of gold, a single SQL increased by 431.375 times times ... What is this concept.!!

You hate it.!! Again.!!

Plsql optimization-improved indexing in pagination statements
Here is a paging statement, we do an index to the created_date, etc... Wait, wait, this way. The index is not a generic index. We translate the graphical tool-built index into SQL:

Create INDEX idx_warehouse_ct on T_warehouse (Created_date DESC);

Our table is a table with 10 million records, only this one trick. The entire SQL query improves the 300%-340%


Plsql optimization-inner JOIN VS WHERE



Optimization points of Plsql optimization-where statement
Note the following example. Just the Where condition after the order is upside down. Increased the efficiency of 10 times times, hehe.


Select the most efficient table name order in the WHERE statement

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>


It's fun!

！！ Again!

！！

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>


Plsql optimization-use union instead of or

Let's take a look at the following example:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

is not to write a little bit of SQL attention ... This efficiency.

。

。 This performance ... 123%.

。

。

123% of the improvement ah.
Plsql optimization > Shared sql
In the front we used several examples to talk about some of the major performance improvements in Plsql. This way, we need to mention a tool that comes with a buffered SQL result set hit ratio from Oracle



So. We are writing SQL to use the Java PreparedStatement, to use: 1 this thing to do the value, because Oracle is a self-brought SQL buffer pool, in addition to say one more. Although ORACLE10 starts with ASM (self-active memory management). But sometimes ASM is not omnipotent, and for some large-form sites, sometimes our DBAs need to manually adjust the Oracle's SGA, namely:


As a result, this presents a higher requirement for our Oracle DBA.
The basics of Plsql optimization have mastered these points, basically allowing your system performance to increase by 2-digit-3-digit numbers. Perhaps interested readers can continue to see:



How to teach Yourself
How do you learn about Oracle's PLSQL-related tuning?

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /southeast "/>

is not very self-abusing ha.

。。
Then we'll use the famous opensource: Play by yourself, play with it.
In Chinese, that is: self-inflicted and happy .
To become "the east unbeaten."

。。 "--Cang hai.

。。 Laugh... The tide on both sides of Tao Tao ... Oh.

Author's contact information:
qq:42948648
：

Give Plsql wings to fly-plsql optimize