analyze
shared pool
Illustration:
Inside the library cache, it can be considered stored for the time being:
1. SQL and the corresponding execution plan (which takes up less space);
2, stored procedures, functions, triggers, packages, their compiled objects (the space occupied tends to be relatively large, especially the package occupied by the larger)
For shared pool management and research, row cache generally does not appear to be a problem, so in general we do not study the row cache, will be a problem is often the library cache, so we often study it
If you want to execute a procedure inside the package, oracle will be the entire package header (package specification) part of the library cache inside to go, which may result in a relatively large object suddenly be transferred to the library cache to go, this time it is possible to report a 4013 error; so when writing packages, try not to put too much stored procedure or functions into a package
In the shared pool, sometimes it may cause a relatively large object to be suddenly transferred to the library cache, this time there may be reported 4013 error, so the shared pool specifically set up a relatively large object suddenly transferred to the library cache space: reserved space, used to store The reserved space is used to store large objects that suddenly come in, accounting for 5% of the size of the shared pool space, and the reserved space is used to store large objects that suddenly come in.
Suppose the size of the library cache is 5G, caching a large number of SQL and SQL execution plan, now, to execute a SQL, in the library cache inside these large number of SQL and SQL execution plan to find, there is a problem, how to find it? Suppose there are 1 million rows in the library cache, and then to compare them one by one, you have to find them 1 million times, which is obviously not appropriate;
Managing SQL and SQL execution plans
Chain
So how is the SQL and the SQL execution plan organized and managed inside the library cache so that it is easy to find the SQL to execute?
Illustration:
Use chain to chain up SQL one by one, suppose library cache has 500M size, use 4 chains to manage SQL; for SQL1: after SQL1 is hashed, get the hash value, then calculate: hash value/4 remainder (0, 1, 2, 3 ......) For SQL1: SQL1 gets the hash value after hashing, then calculate the remainder (0, 1, 2, 3 ): if the remainder = 0, SQL1 will be put on chain 0; if SQL1 is in the library cache, the server process thinks that SQL1 must be on chain 0, and then looks for SQL1 on chain 0, without looking for SQL1 on the other chain; in addition, suppose SQL2 goes through the process of hashing, and then calculates the remainder, and assumes that the remainder is = 1, and then it will look for SQL1 on chain 1, and if it doesn't find it eventually, it won't look for it. If it doesn't find it, it doesn't look for it, because it can't be on any other chain, and then SQL2 has to generate a hard parse.
Chain features:
1, the chain of a way to access: can only traverse, can not be randomized access (to find the head of the chain can be found one by one, has been to find the end of the chain)
2, a kind of chain a kind of role (such as SQL through the hash, and then hang on the chain, looking for the time is also SQL through the hash and then in the chain to find)
The chain inside the library cache: that is, SQL is hooked up by means of a hash value (how it was organized in the first place, and how it will be found when looking for it)
The size of the library cache affects the number of chains (how big is the library cache and how many chains is the number of chains, this is something that oracle itself adjusts and adapts; we can also adjust the relevant parameters to adjust the number of chains)
A chain can be thought of as a bucket.
Hash (which is actually a function)
For example, forSQL：select name from t where id = :x;
oracle takes each letter inside the SQL statement and converts it to an ASCII value, with each letter corresponding to a code.
One SQL --> a bunch of text letters --> a bunch of numbers
There are several correspondences between hash values and SQL:
1. If SQL1 and SQL2 are exactly the same, then their hash values must be equal
2. If SQL1 and SQL2 are not the same, then their hash values must not be equal
3. If the hash values of SQL1 and SQL2 are equal, but SQL1 and SQL2 are not necessarily identical
So when comparing two SQLs:
1, If the hash values of two SQLs are not equal, then the two SQLs are not equal;
2, if the two SQL hash values are equal, then you have to compare the two SQL, one letter at a time.
free space
How is the chunk inside free managed?
It is also managed using chains
How to find free chunks from inside free space?
Find the chunk according to its size; for example, if we need a 4k chunk, we will find a 4k chunk in free, and if that doesn't work, we will look for a chunk a little bit bigger than 4k (e.g., 5k, 8k, 12k), and then look for a 5k chunk, so free is a chain of chunks that are hung on top of each other by a chain of sizes of the free chunks.
Illustration:
There are three chains: 2k, 8k, and 16k, now you need a 9k, so you look on the 8k chain, you find a 12k, it's ok, so you use it, you use the 9k you have 3k left, and you hook it up to the 2k chain again.
Cursor
An SQL and its corresponding execution plan, called a cursor, are inside the library cache.
parent cursor
The parent cursor says: SQL text; the same SQL may correspond to more than one execution plan (the execution plan may be different because of the different users accessing it, because the tables have the same name (they are all t-tables) but the contents of the tables are not the same, or because of the difference in the value of the binding parameter (for the same value, there are 10 million rows in one table, and only 10 rows in the other (the table has the same name in this case)))
children cursor
Subcursors are: execution plans; the number of subcursors is variable, depending on the situation (e.g., 10, 100 is uncertain)
version count
For example, if a parent cursor has 10 child cursors, then the number of versions it has is 10
latch: locks (memory locks, latches), used to protect the chain of
Illustration:
Now there is a problem: oracle has a lot of server processes; suppose server process1 executes SQL1 and server process2 executes SQL2, then server process1 has to parse SQL1 when it executes SQL1, and it doesn't find it in the library cache, and SQL1 has to be hard parsed; when it executes SQL2, the same thing happens, and SQL2 also needs to be hard parsed; and parsed at the same time. hard parse; when executing SQL2, the same thing happens, SQL2 also needs to be hard parsed; and at the same time, parsing;
When parsing, you need to find a free chunk inside the free, and write it in; suppose parsing SQL1 needs a ,9k free chunk, and parsing SQL2, a 10k free chunk, so both need to find it in the 8k chain, which has a concurrency problem: suppose SQL1 and SQL2 find two neighboring free chunks; SQL1 has to take down the free chunk2 it found and point free chunk1 to free chunk3; SQL2 takes down the free chunk3 it found and points free chunk2 to free chunk4; at this time, both free chunk2 and free chunk3 are gone. free chunk2 and free chunk3 are gone, and the chain is broken, so for this case the chain needs concurrency protection, using locks (latch) for protection;
Latch (for the 0 chain application of a memory structure), used to protect the 0 chain, now server process1 reads this latch (there is no write-related information in it) and finds that it is empty, server process1 writes on it with S. server process1 accesses the 0 chain with S;
The latch is empty, which means there is no process to protect the chain; then server process2 wants to access the chain 0, and finds that there is a process accessing it in the way of S. Server process2 wants to add a way of X, but server process1 holds the latch in the way of S. Server process2 also wants to hold the latch, but S and X are not compatible, so server process2 cannot hold the latch. process2 also wants to hold the latch, but S and X are not compatible, so server process2 can't hold the latch, then server process2 has a latch misses;
Suppose server process1 is working on cpu1 and server process2 is working on cpu2 (there are two cpus), at this point, there is another server process3 waiting for the cpu to be empty before going in, now for server process3 two options:
1, server process2 exit (latch lost), and then enter the cpu2, server process3 work for a period of time after the exit, server process2 and then go in, continue to execute the previous operation is not finished, this process is called: context switch (CS);
2、server process1 holds the latch for a very short time, a very short time to execute, at this time, server process2 does not come out, it executes a for loop, occupying cpu2, and wait for server process1 to finish the execution of the S after the release of the latch to go in, at this time, server process3 can then use cpu1;
Then suppose, there are four cpu, server process2 doesn't come out, occupying cpu2, at this time, server process3 also holds latch, server process2 again and again latch is lost, then server process4 again holds latch, server Then server process4 holds the latch again, server process2 idles the cpu, server process2 loses the latch again, after server process2 loses the latch for many times, server process2 turns to sleep state, and then it quits the cpu, other server process can occupy the cpu.
Thus, if the database has severe latch expropriation, it will show a busy cpu
sleep state, indicating multiple latch misses (loss of latch)
When server process1 wants to access chain 0, there are two ways to access it:
1. The S (shared lock) approach (read the chain, find the size on the chain)
2. The X (exclusionary lock) approach (modifying the chain, that is, hanging things on the chain and removing things from the chain)
Relationship between S-mode and X-mode
Example: 1, SQL1: S 2, SQL1: S 3, SQL1: X
SQL2：S SQL2：X SQL2：S
1. SQL1 and SQL2 can be performed simultaneously (S and S can be compatible)
2, SQL1 and SQL2 can not be done at the same time, to wait for SQL1 to find the end of the SQL2 to modify (S, X is not compatible)
3, SQL1 and SQL2 can not be carried out at the same time, to wait for SQL1 modification is complete, SQL2 to find (S, X is not compatible)
Another case of latch:
Multiple chains protected by a single latch are very likely to have lost latches, so we can make multiple chains protected by multiple latches by adjusting the parameter
Types of latch (the vast majority):
1、fatherlatch：library cache latch
2, sub-latch: each chain on the latch (library cache latch inside one by one inside the latch)
The way latch works:
1, sp1: S way holds the latch
sp2: X way, latch misses, idle cpu, after many latch misses, it becomes sleep.
2, sp1: S way holds the latch 0
sp2: X way, also wants to hold latch 0, with latch misses; but sp2 can find what it wants from the chain on latch 0, and it can find what it wants from the chain on latch 1, just the same; at that point, sp2 runs to latch 1 to find the thing
But the vast majority work in the first way of working (willing to waiter): those who are willing to wait, or who must wait