If you can imagine the operation of I/O and the disk, it will be easier to understand the phenomena generated by Oracle.

　Here, the basic functions of Oracle are taken up as "essential keywords for understanding Oracle", and we will look at the relationship between I/O (INPUT/OUTPUT) and the disk in detail.You may not usually be conscious, but if you can imagine the operation of I/O and disk, it will be easier to understand the various phenomena generated by Oracle.Let's take the first step to become an Applied Oracle engineer!

Required keywords to understand Oracle

　First, here are three keywords to understand Oracle.This is the keyword that appears in this book.

　The internal structure of DBMS (database management system), not limited to Oracle, is complicated.The reason is mainly from these three characteristics.And these are incompatible.For example, I want to write the data on the disk at the time of Commit to protect the data that has been Commit, but if I do so, the response will worsen.

"It enables parallel processing and achieving high throughput" is not easy.In parallel processing, lock [ * 1] is required so that contradictory processing is not performed, but the lock may not have performance.

　In this book, the topic is determined for each chapter, and only the basic functions of Oracle's DBMS are explained.Recent Oracle is rich in function, but these basic functions are based on.Please wear it firmly.Also, the numbers in this book are reference values to help readers understand, and consider it as a guide.

Oracle and disk (hard disk)

　Oracle is a database management system (DBMS).And the database in Oracle refers to data in the disk (hard disk) under Oracle's control.Oracle reads the data from the disk, processes it, and writes the data on the disk.In other words, the data handled by Oracle is taken out of the disk and returns to the disk.Therefore, it can be said that Oracle and disk are inseparable.

　So, this time I will explain the disc.If you have a disk that can be broken, please open it with a driver and read while looking at the contents.However, please note that once you open it, you will never be able to use the disk again.

　I would like you to have the first operation image here.First, imagine that the disk is like a music record or CD (Fig. 1).

　The disk is almost always rotating, and the head moves and reads and write data that is not music.The difference from the record is that the disk is overlapping, and the actuator uses both sides without having to reverse the disk, but it is not important here.Speaking of another difference is speed?If you compare it to music, the head will move quickly enough to be able to head 100 times per second.In addition, the rotation speed is tremendous, about 10,000 times per minute (Fig. 2).

Disk operation

　Now that you have a terrible high -speed record, let's look at the actions of the disk required for I/O processing.To read (or write) data, you must do so -called "head".The task of finding the purpose of this purpose is called "Seek" in the term of the disk.

　After that, wait until the information you want is rotated.This waiting time is called "rotation waiting time".Finally, I read and write data (Fig. 3).

　Access to the memory can be performed in NSEC (nanotond), while the access to the disk takes time in MSEC (milliseconds).It is access to the disk that seems to work quickly in terms of human time, but it is very slow from the computer speed.

　To put it simply, the disk is mechanical operation for the memory processed by an electric signal.I/O to the disk is necessary for DBMS, but it should also be reduced from the viewpoint of reducing processing time.

　From now on, the total time required from the seque to the rotation time is explained as 10 msec, and the transfer speed of the disk (the possible amount of processing when reading and writing data without moving the head) is explained as 20MB/sec.

How do you reduce the waiting time for I/O?

　So how do you actually reduce the time you wait for I/O?To do so, I will first explain the sequential access.The sequential is "one after another (followed by step)", which means access (read / write) without overpaying from the top.In the case of full scan (reading all the data in the table), a sequential access will occur if there is no data in the memory (Fig. 4).Sequential access and full scan -These two are important terms to understand the database, so keep in mind.

　By the way, if the table size is 1GB, it takes 50 seconds (size/transfer speed = 1000MB/20MB/sec) simply by reading all with sequential access.Even if the table size is 100MB, it takes 5 seconds.If it takes so long, you can't meet the performance requirements of SQL.

　So the idea of an index (index) comes out.What do you do if you want to find something in a book?There are few people who read all pages from the beginning and search, and in most cases you will use an index.As you know, the index has keywords in order, and the page number is also listed.Looking at the page number, you quickly reach the necessary pages and get the necessary information.

　The same is true for database indexes.The database index describes the key value used during search (the value of the condition to be written in the WHERE clause in the SQL statement) and the address with the key (Fig. 5).

Examples of indexes

　For example, if you want to find out about Rally in a book, check out what is written on page 240 with the index, open the relevant page and get the information of Rally.The same is true for the SQL statement, describe the WHERE clause that you want to know "Rally", and write the item you want to know in the select clause (the next example is a "company").

　SELECT "The company belonging" from "personal data" personal name "=" rally ";

　When processing this SQL statement using an index, first check the index with the "personal name".As a result, you can get the address (ROWID) of "Rally", so read the data based on that address.Rally's data is available in the loaded data, so we return the data of the "company" to the user (Fig. 6).

　This is a very convenient index, but does it still take time to process as the index itself becomes larger? That's not true.Internally, it automatically becomes a multi -stage configuration like an index index (Fig. 7).This will be different from the book index.

　By the way, such a multi -stage structure is called the "tree structure" (it looks like a tree is upside down).The advantage is that you don't have to read the index -related parts.

Random access

　Let's return the story to I/O.If you use an index, it is enough to read only the necessary parts.However, there are few necessary parts continuously on the disk.Therefore, you will access the jump while moving the head.Such accesses are called "random access" and have the opposite of sequential access.

　This random access is inefficient from a disk.Each time you access to the tip, you will have a shik and a rotation waiting, and you spend time.For example, if the block size of Oracle is 8KB, and the number of times that can be seen per second is 100 times, only about 800KB can be read per second.This is 25MB/sec if the disk transfer speed (the amount of processing that can be read and written for all the time without moving the head) is 20MB/sec, and if you do not have a siku, you can read 20MB per second.It is one of the minutes.

　If you compare it to listen to music, it's equivalent to listening to music for only two and a half minutes in one hour.The remaining 57 and a half minutes are time to go.

　In fact, from the efficiency of data transfer, DBMS I/O is such a thing.Because there is a circumstances of repeating such a seque, IOPS (I/O Per Sec: I/O per second) is important for disk indexes for DBMS (especially OLTP [ * 2]).It is said that.And most disk IOPS is about 100 or 200 times, so if you create a database with one or two disks, the shik will not catch up when the load is concentrated and the disk becomes a bottleneck.(Fig. 8).

上級者向けTips　「インデックスアクセスが有利なのはデータの15％未満」なのはなぜ？

　The reason is the characteristics of sequential access and random access.If the data on the table is large and you can find one line from it, you can find the index access faster.On the other hand, if you look at all the data, you will be slower while drawing the index (I want to read all the contents of the book, but nobody reads the index).

　So what about 50 % data? What about 25 % data? Here, the characteristics of "random access to disks are inferior to sequential access in terms of data reading efficiency." For example, consider the case when taking out half a 10,000 lines from the table with 20,000 lines. Here is 8KB for one line. If you use the performance value of the disk you have used so far, it takes about 100 seconds for random access (index is frequently used, so it is assumed that it is on the cache). On the other hand, the sequential access to read all 20,000 lines will end in about 8 seconds even if all rows (20,000 lines) are read from the disk. In other words, from the characteristics of the disk, it is clear that it is faster to make a full scan with a sequential access if it is not all.

　However, in fact, data may be listed in the cache, and one -block has multiple lines data and you can read many rows in one I/O.Furthermore, since index data is sometimes read from the disk, it cannot be said that 15 % is a good value.Please just be a guide.

Disk to guarantee data

　Even if the Oracle process ends abnormally, the data is OK.This is one of the differences between DBMS and other programs.For example, Excel loses data after saving (saving).As you know, the data is lost when the program ends abnormally, and the data is lost even if the power button is pressed and the power is turned off suddenly.

　On the other hand, DBMS must withstand any obstacle.Whether the CPU is broken, the memory is broken, or a power outage cannot be lost.The information in the main memory of the computer is electricity, so if it is a power outage, it will disappear from the memory.Despite such severe conditions, how is the characteristics of "protecting the Commit data" introduced in the keyword?

　The answer is simple, and after changing the data and entering Commit, Oracle writes data on the disk (Figure 9).You may think, "Isn't this slow?"On the other hand, the structure of DBMS, including Oracle, is complicated because of the speeding mechanism, but this is described in this book.

What does COLUMN "sequential" mean?

　Oracle has a lot of information.It is also recorded how much I/O was done in it.However, there are points that are easy to get confused in that information.

　For example, when a sequential access is displayed, "DB File Scattered Read" is displayed, and random access is displayed as "DB File Sequential Read".“Scattered” means “distributed”, and “Sequential” means “sequential, continuous”.It seems to be the opposite when considering the way of access and meaning, but this does not mean that the display is wrong and has the following meaning.

　Oracle reads data in blocks and placed on memory.The sequential access is read, as the meaning of "Read in order (followed)", multiple blocks are read without overpassing.At this time, multiple blocks that have been loaded are placed in a continuous (distributed) place on the memory.Therefore, "Scattered" is displayed.

　Random access, on the other hand, has one data block to read, inevitably placed in a continuous area on memory.Therefore, it is displayed as "Sequential".

　The name is changing from the perspective of how the reading block is placed on the memory.

　I would like the author to change the name sequential to refer to "sequential I/O", but once it has been decided, it will not change in the future.Please keep in mind that it is easy to make a mistake.

Reference: Manual "Oracle Database Performance Tuning Guide 18C" 10.3.3 DB File Scattered Read and 10.3.4 DB File sequentially

summary

　There are three things I want you to hold down.I want you to come to mind these images in your head.

　The disk was actually slow, and the index could access data efficiently.I will explain how these will develop in the future in this book, so please look forward to it.If you introduce only one, there is a buffer cache to prevent the slow disk from affecting the performance of the SQL statement as much as possible.

　Finally, here are some recommended articles for those who want to learn more disks.

Reference: Introduction to storage starting from zero

On -site IT glossary, 24365, on -premises, on -premises

　In the field, terms that do not appear in text often appear.The meaning of such terms is difficult to hear from people.Here are some of the terms of such sites.

　It's a hard disk.This refers to the whole (Figure 1.2), not one disk (Fig. 1.1).Speaking of "100g ball", it refers to a hard disk with a 100GB capacity, and two balls mean two hard disks.This word "ball" is frequently used.

　It is a system that operates 24 hours a day, 365 days a year.We need measures to maintain high availability and secure state.

　It is to have a computer or system on its own.In recent years, the "cloud" that puts virtual servers on the Internet has spread, so it has been used as a synonym for distinguishing.

Amazon SESHOP Others

絵で見てわかるOracleの仕組み 新装版

Author: Atsushi Sugita, Yuka Tsujii, Ryo Teramura, Hiromiko Yamamoto, Keiji Oda Release: March 11, 2019 (Monday) Price: 2,484 yen (tax included)

本書について

A new version of a long seller released in 2006.In addition to modernizing the content, columns and appendix (basic commands) have been newly established.It is a book that not only helps everyday Oracle operation / management work, but also has knowledge to become an applied engineer.