Give Plsql wings to fly-plsql optimize

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

This article summarizes the author's work experience for nearly more than 10 years and is based on the most basic and most effective for RBO, CBO, Index, The Where condition is explained at the same time with a large number of cases to help readers from this article learned from the relevant theoretical knowledge quickly applied to 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 (?) of knowledge

Oracle's Optimizer

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

Optimization mode of the optimizer

CBO mode

Rbo mode

Take a look at the evolution of the Oracle Optimizer

So, we know that after ORACLE10 started using the CBO by default, Oracle automatically chooses the optimal execution plan at the CBO, and sometimes we think it's better to take the index, but for the CBO, a full TABLE access is more efficient than the 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 the most direct performance improvement without the need to modify SQL, when to build the index, how to build? How to make your index more reasonable?
Indexes are categorized by internal structure

Index by Function classification

Index by Index Object category

The methodology of index building

The above describes the classification of such a number of indexes, the following is the establishment of the index of the methodology bar, we may be more concerned about this, because this is a summary of experience is also practical useful tool ha.

Not recommended for index-based scenarios

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

Conditions in which the index does not take effect

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

Take the case to illustrate

Plsql optimization > A no-go index optimization case

This example shows that if you have a word for a field that is queried by the where condition, but it is involved in the operator, it is not indexed in Oracle's internal execution plan, so we made a small change, how many times is the efficiency improved? 5.3 Times times, 530%, hehe!

In the example above, we can make a summary for indexing.

Summary of index creation

Table Analyze

Analyze Table VS Dbms_stats

Import & Export

Speaking of Import & Export command, people will say ... Eh, this is not very simple, 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 put a library that contains at least 30 tables and each table has more than 12 million recorded data into a. dmp file

Then you go and try this time-consuming.

General Practice of Import
This is a real case, we in the CCC is the world-renowned car insurance company project, we regularly and CCC headquarters in Chicago synchronization of about 8GB. dmp files into our database, because of security reasons need to rely on. DMP Interchange file in the form of 0 morning synchronization to the Chinese database, and in the t+ 1 the next morning before 8:00 complete synchronization.
So, our DBA began to come ...

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:
I just folded the original imp into 2 articles:

• First, only the data, not the index, and set to 10,000 data a commit, and set up a buffer pool
• Second, only index, no data, and set to 10,000 data a commit, and set up a buffer pool

The results are amazing ... Found that the last only 40 minutes less than two statements all completed, the import ... In fact, I can use the following 2 examples to illustrate the reasons:

1. A directory containing 8GB files content, download with the FTP client, you will find that it seems to never wait for the end, a few hours of such consumption, and then you change the directory into a compressed package, and then the FTP client download, a few 10 minutes can be done.
2. You use JDBC to write a for loop to insert 1 million records ... The result is that Oracle explodes directly and you use bulk commit ... The result is amazing!

In fact, we did the folding, the principle is like the above two cases, is the same truth, reduce IO Read and write, set buffer, batch submission. If your business is too big ...

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


Plsql optimizing-select in, or, and Union interactions

Take a look at 3 changes, last time, how many times? 11.2850-0.0261 divided by 0.0261=431.375,431.375 times ... A SQL Ah ... In the Internet application of gold, a single SQL increased by 431.375 times times ... What is this concept!!!
you hate!!! Again!!!

Plsql optimization-Index optimization in paginated statements
Here is a paging statement, we do an index to the created_date, etc... And so on, and so on, this side of the index is not a general index, we translate the graphical tool 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, the entire SQL query improves the 300%-340%


Plsql optimization-inner JOIN VS WHERE



Optimization points of Plsql optimization-where statement
Note that the following example, just the WHERE condition after the order upside down, it increases the efficiency of 10 times times, hehe.


Select the most efficient table name order in the WHERE statement



It's fun!!! Again!!!




Plsql optimization-replace or with union

Let's take a look at the following example:


A little attention when writing SQL ... This efficiency ... This performance ... 123% ... 123% of the improvement ah.
Plsql optimization > Shared sql
We used a few examples in front of the plsql some of the most basic performance can be brought about by the improvement, here we need to mention a buffered SQL result set hit ratio of Oracle tool



So, we have to write SQL to use Java PreparedStatement, to use: 1 of such things to do the value, because Oracle is the own SQL buffer pool, and in addition to say more, although the ORACLE10 began with ASM (automatic memory management), But sometimes ASM is not omnipotent, for some large web 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 you to improve your system performance by 2-digit-3-digit numbers, and subsequent interested readers can continue to see:



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



is not very self-abuse ha ...
Then we'll use the famous opensource: Play by yourself, play with it.
In Chinese, that is: self-inflicted and happy.。
To become "Oriental undefeated ... "--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