How to analyze why Oracle is slow

Problem description The data of a table in the oracle database has been more than 100 million, and this table has created an independent index, due to business needs, it is necessary to insert 10,000 records into this table twice a day, due to the large amount of data, each insertion takes more than an hour, which seriously affects the efficiency, so the algorithm of the system is modified to store only the new records of the day in this table, and after the table is truncate, when the update operation on this table is executed the next day, it is very time-consuming, and when there are more than 100 million pieces of data in the table, this sql statement takes time in the second table When there are 10,000 pieces of data, this SQL statement takes several hours, and after consulting DBA, it is concluded that the index needs to be rebuilt, and this operation is completed in seconds, but the problem still appears on the third day, and DBA is looking for the reason. Is it necessary to rebuild the index every time the truncate table is rebuilt?

For this problem, DBA does not give an explanation for the slag segment theory, and speculates that the main reason is Oracle's complex query optimization algorithm.

The solution given by the final backup DBA.

truncate table

drop index

insert data

create index

yze table table_name pute statistics;Newly generated stats for re-imitating Liang Tu.

lishixinzhi/article/program/oracle/201311/16938

I also saw your problem in the development process, I had more than 6 million records at the time, and it seems fast to execute the internal process alone, but sometimes it is very slow, let's talk about the solution first: you need to design the corresponding index for your table according to the query conditions, sometimes it is possible for you to create several indexes for a table according to your needs. And then you will find that what used to take 30 seconds can now be done in a few tenths of a second, which has nothing to do with using a cursor.

The use of cursors is completely determined by the needs of your application, and I believe that you will definitely not choose to use cursor processing if you can do without it, and generally choose cursors to do when you really can't do it.

The reason why your query is slow is that if you execute it in the storage process, when there is no index, he will check it one by one and scan all the 80 tens of thousands of pieces of data until you finish checking it before you see a result, but the reason why you execute it alone alone outside is that plsql will first check a few pieces of data and return it to you first, so you will see the result immediately, but in fact, the query is not over, if you pull down in the case of a lot of data, you will send the letter very slowly, In fact, the query process is not over at all.

In short, the first step of writing the stored process is to be able to write, and the second step is to do a good job of optimization, otherwise when you have a large amount of data, your process is not well written, and the problem will be exposed immediately.