Note: Oracle is slow due to unknown reasons.

Note: Oracle is slow due to unknown reasons.

Recently, it is found that the Oracle server is super slow, and the slow speed is not caused by application performance. Even running proc precompiled programs is very slow. It can be seen that the problem lies in the Oracle server.

Itself.
First, check the main Oracle memory parameters: Select "num", "name", "type", "value"/1024 as "kb ",
"Isdefault", "isses_modifiable", "issys_modifiable ",
"Ismodified", "isadjusted", "Description", "update_comment"
From v $ Parameter
Where name in ('db _ block_size ', 'db _ cache_size ',
'Java _ pool_size ', 'large _ pool_size', 'pga _ aggregate_target ',
'Shared _ pool_size ', 'sort _ area_size ')

Except that the large pool (large_pool_size) is 16 MB, each item of memory is not small.
I suspect that the shared pool is full. I found an article on the Internet to check the usage of the Shared Pool:
How to calculate the size of an Oracle Shared Pool http://blog.sina.com.cn/s/blog_3ebdf8ad010006bk.html
After execution, the shared pool actually occupies 113% of the total capacity. It exceeded !!! Do not understand!

So I tried to modify the parameters:
1. in Linux: su-Oracle
2. sqlplus "/As sysdba"
3. Create pfile = 'ahfu. ora 'from spfile;
4. in Linux: CD, Oracle, ora9, product, 9.2, DBS/
5. VI ahfu. ora
6. Modify the parameters as follows:
Db_cache_size (data cache) 256 MB-> 512 MB
Java_pool_size (Java pool) 84 MB-> 0 MB
Large_pool_size (large pool) 16 MB-> 64 MB
Query_rewrite_enabled (query rewriting) False-> true
7. Save and close the database: shutdown immediate;
8. Start from the new pfile: startup pfile = '/Oracle/ora9/product/9.2/dbs/ahfu. ora ';
9. reconnect to the database and find that the connection and query become fast. The database has been restarted multiple times and is still very fast.

On the one hand, this is because the sharing pool is full, leading to slow database, and on the other hand, the large pool is too small. However, the restart speed does not mean that the problem is actually solved. It takes a while to run it.

.

========================================================== ================
Appendix 1: Analysis of the Shared Pool
The Shared Pool caches the SQL statements executed in the system. If a large number of different SQL statements appear, the shared pool may be full. When the sharing pool is full, the shared pool is scanned every time an SQL statement is executed to find

If the same SQL statement is not found, scan the entire shared pool and set the SQL statement that has been executed for a long time to expire to allocate space for the new SQL statement. Therefore, even simple SQL statements are executed repeatedly.

Scan the sharing pool, making the entire system very slow. The problem of full sharing pool does not occur immediately. It requires a certain accumulation process. So when the database was just started, the sharing pool was empty,

Execution is always fast. After the database runs for a period of time and the shared pool is full, the database will be found to be slow. A large number of different SQL statements may cause shared pool problems:
For example, select user_name from webuser. user_info where user_id =: userid;
And select user_name from webuser. user_info where user_id = 1
In the preceding two SQL statements, the first statement is bound with a parameter, which is hit multiple times in the Shared Pool and has high performance. In the second statement, the parameter is constant, which is hard to be hit and must be resolved again each time.

And occupy the space of the Shared Pool.

Appendix 2: Analysis of query rewriting
The query rewriting function means that the database engine forcibly modifies all SQL parameters to the format of parameter binding.
For example, the database forces the select user_name from webuser. user_info where user_id = 1 SQL statement
Force modify to select user_name from webuser. user_info where user_id =: userid
Query Rewriting can effectively avoid full sharing pool, but it will increase the SQL parsing time.