What are the solutions when the database slows down
We use the computer and cell phone when the most intolerable is the device is stuck and slow, seriously affecting our work or game experience. When the database slows down, how should we start, the following solutions.
Chapter 1 Checking the Status of the System
1.1 Using sar to check the operating system for IO problems
1.2 Focusing on Memory vmstat
1.3 Finding the Oracle session that is using a particularly large amount of resources, as well as the statements that it executes
1.4 Find the top ten poorly performing sql statements
How do we start when the database slows down
When the application administrator notices that the application is now slow and the database is slow, and when the OracleDBA finds the same problem when doing a few sample Selects on the database, there are times when there is nothing to be done because the DBA believes that the database’s Various life rates are to meet the recommendations of the Oracle documentation. In fact, today’s optimization has been optimized to optimize the wait (waits) transition, the actual performance optimization of the most fundamental point of focus on I/O, which is the most important aspect of the impact of the performance by the system to find the wait in the Oracle library in the lack of operating system, the use of certain resources is a better way to use the irrational. Here are some practical experience to share with you, this article measured in the Unix environment.
Chapter 1: Checking the state of the system
Some of the tools through the operating system to check the state of the system, such as CPU, memory, switching, disk utilization, based on experience or compared with the normal state of the system, sometimes the system seems to look at the surface to see the idle, which may not be a normal state, because the cpu may be waiting for the completion of the IO. In addition to this, you should also look at the processes that are taking up system resources (cpu, memory).
1.1 Using sar to check the OS for IO issues
#sar-u210 – i.e., check every 2 seconds for a total of 20 executions.
Note: Under redhat, %system is what is known as %wio.
Ø%usr refers to the percentage of cpu resources used by the user’s process;
Ø%sys refers to the percentage of cpu resources used by the system resource
Ø%wio is the percentage waiting for io to complete, which is worth watching;
Ø%idle is the percentage idle.
If the wio column has a large value, such as above 35%, it means that there is a bottleneck in the system’s IO, and the CPU spends a lot of time waiting for I/O to complete.Idle is very small which means that the system’s CPU is busy. Like the example above, you can see that the average wio value is 11, indicating that there is nothing particularly wrong with I/O, and the idle value is zero, indicating that the cpu is already running at full capacity.
When the system has IO problems, they can be solved in the following ways:
ØContact the technical support of the corresponding operating system to optimize this aspect, such as hp-ux in delineating the volume group when the striping and other aspects.
Ø Find the Oracle unreasonable sql statement, optimize it;
Ø Oracle frequently accessed tables in addition to reasonable indexing, and then these tables are divided into tablespace storage to avoid access to hotspots, and then there is a reasonable partitioning of the table.
1.2 Focus on memory
A common tool is vmstat, for hp-unix, you can use glance, for Aix, you can use topas. when you find that the pi column of vmstat is non-zero, the value of free column in memory is very small, and the utilization rate of memory in glance, topas is over 80%, it means that the memory should be adjusted. , this is an indication that the memory side should be adjusted. There are several ways to do this:
Ø The memory allocated to Oracle should not be more than 1/2 of the system memory, and it is generally beneficial to keep it at 40% of the system memory.
Ø Increase memory for the system;
Ø If you have a particularly large number of connections, you can use the MTS approach;
Ø Patch the whole thing to prevent memory vulnerabilities.
1.3 Find Oracle’s sessions that are using particularly large resources and the statements they execute
Hp-unix can use glance or top. ibmaix can use topas. in addition, you can use the command ps.
Through these programs you can find the process number of these processes that use a particularly large amount of system resources, you can use the following sql statement to find out which sql is being executed by this pid, and this sql is best executed in software such as pl/sqldeveloper, toad, etc.:
You can analyze this sql you get to see if its execution plan goes to an index. Optimize it to avoid full table scans to reduce IO waits and thus speed up the execution of the statement.
Tip: In doing optimization sql, often encountered the use of in the statement, then be sure to use the exists to replace it, because Oracle in the processing of In is done in the way Or, even if the use of indexes will be very slow. For example:
This can be replaced with:
1.4 Find the top ten poorly performing sql statements
Chapter 2 Examining Session Status
To quickly discover the cause of OracleServer’s performance problems, you can turn to the v$session_wait view to see what these sessions of the system are waiting for, and how much IO is being used.The following is the reference script:
–Script description: To see the running sessions that account for a large amount of I/O:
A few notes on the retrieved results:
1. The above is ordered by the physical reads that have already taken place for each of the sessions that are waiting as it pertains to real I/O.
2. It is possible to see what these waiting processes are busy with and whether the statements make sense?
Executing the above two statements gives you the statement for this session.
You can also kill the session with altersystemkillsession’s ‘sid,serial#’;.
3. Should pay attention to the event column, which is a key column in the tuning, the following is a brief description of the frequent event:
1) bufferbusywaits, freebufferwaits These two parameters identify whether the dbwr is enough to use the problem, and the IO is very related to when v$ session_wait in the freebufferwaits, when v$ session_wait in the freebufferwaits. session_wait in the freebufferwait entry is very small or no, that the system’s dbwr process is definitely enough, do not need to adjust; freebufferwait entry is a lot, the system must feel very slow, this time shows that the dbwr has been insufficient, it produces wio has become a bottleneck in database performance, this time the The solution is as follows:
ØIncrease the write process, at the same time to adjust the db_block_lru_latches parameter:
Example: modify or add the following two parameters
Ø Open asynchronous IO. IBM is much simpler in this regard, hp is a bit more troublesome, you can contact Hp engineers.
2) dbfilesequentialread, refers to the sequential read, that is, the full table scan, which is also the part that should be minimized, the solution is to use indexes, sql tuning, and at the same time, you can increase the parameter db_file_multiblock_read_count.
3) dbfilescatteredread parameter refers to reading through the index, the same can be increased by increasing db_file_multiblock_read_count this parameter to improve performance.
4) latchfree is related to bolts and needs to be tuned specifically.
5) Other parameters can not be specifically noted
Supplement: common techniques to solve the system slowdown method
1, in the window of my computer, right-click on the disk to be cleaned up – “properties” – “clean disk”. “Clean disk” – check the files to be deleted – OK – Yes.
2, right-click the browser e – Properties – point 2 delete 1 clear (all to determine one by one) – OK.
3, C:\WINDOWS\Prefetch (pre-read files) to delete all the files inside
4, use Optimizer or Super Rabbit to clean up the registry and junk files.
5, “Start” – Run in the input msconfig – determine – Start – in addition to the input method ctfmon other than the hook all removed.
6, right-click on my computer “- Properties – point advanced – point startup and failover in the Settings -Remove all the hooks – Write debugging information select “none” – OK! –Point Advanced under Error Reporting –Point Disable — 2 times OK.
7, “Start” …. Open the Control Panel in the folder options …. Tap View… Point to remove the end of the automatic search folder in front of the check box … OK.
8, right-click my computer – Properties – Hardware – Device Manager – double-click the IDE controller – secondary channel – advanced settings – transfer mode are selected DMA – device Select None for Type – OK – the same settings for the primary channel – OK.
9, right-click the C drive for disk cleanup and other options in the system restore cleanup.
How to recover SQL database files
MSSqlServer database is one of the most widely used types of databases because of its ease of use, easy to operate, especially for small and medium-sized enterprises and individuals. Generally, due to the destruction of data files, resulting in the database can not work properly, the symptoms of the phenomenon is usually: when attaching the database, there is an error code 823 error, in this case, SQL database data recovery is obviously the most important. Generally speaking, we have three most common ways to recover sql database. The first is manual retrieval. This is probably the easiest way for people who know computer technology, usually takes less than five minutes to get it done, but for people who have little computer knowledge, don’t try it easily, so as not to cause permanent deletion and loss of database data. The second use of the appropriate database repair software for recovery. This is also a more common way. That is, by looking for a suitable sql database recovery software, through the prompt operation of this software to complete the entire database retrieval, this way to use the crowd more widely. For example, Xunlong data recovery software, invincible data recovery software and so on. Third, through the professional data recovery company to retrieve. Relative to the above two ways, this way to spend more expensive, but the highest security. After all, database loss data is not like ordinary file deletion, which has greater stakes. If you have encountered the above problems, this time you may wish to come to Shanghai Tianshield Data Recovery Center to solve the problem! After a long period of practice, Tianshield Data Recovery Center has accumulated valuable data recovery experience. Using the latest “lossless recovery” program, technical experts on the server equipment lost files on the depth of the scan, there is a 95% success rate can be completely restored. In addition to this can also provide server data recovery, hard disk data recovery, raid data recovery, mac apple data recovery, the second open disk recovery and other services and digital photo recovery software, cell phone data recovery software and other software.
How to recover SQL database files
How to solve the SQL database connection card
Press Win + R key, enter services.msc in the run to find the SQLSERVER (MSSQLSERVER), look at this service open or not, do not open the manually open a bit, and then connect.
What is the method to repair SQLserver database
SQLServer2000 database LDF corruption, only mdf recovery method.
The phenomenon of SQLServer2000 database files being corrupted occurs frequently, can database errors be repaired? The answer is yes, this log to a sqlserver2000 database, the database log file ldf is damaged, mdf is normal, the database failed to attach the repair method to summarize, database data recovery is more complex in many cases, when there are a large number of errors in the database, the use of DBCC repair is also not possible, you need to disassemble the database to rescue important data, the following is one of the more common SQL Server2000 database files are corrupted, only mdf recovery method. The following is a more common SQLServer2000 database repair:
1) First, the original database files in a timely manner (such as test.mdf) backed up to other places.
2) Stop the server.
3) Delete this test.mdf.
4) Re-create a database with the same name as test.
5) Delete the test.ldf file of this newly created test database and overwrite the newly created test.mdf file with the test.mdf file you started backing up.
6) start the database server. At this time, you will see the status of the database test “doubt”. You can’t do anything with this database at this time.
. Set the database to allow direct operation of the system tables. You can do this by selecting the database server in SQLServerEnterpriseManager, right-clicking it, selecting Properties, and then setting “Allow direct modification of system directories” in the Server Settings page. Allow direct changes to the system catalog” on the “Server Settings” page.
7) Set test to Emergency Repair Mode
At this point, you can see the database is in “read-only” mode in SQLServerEnterpriseManager. The database is in “read-only\doubtful\offline\emergency mode” and you can see the tables in the database, but only the system tables
8) The following is the real recovery operation, rebuild the database log file
dbccrebuild_log(‘test’,’C: \\ ProgramFiles\MicrosoftSQLServer\MSSQL\Data\test_log.ldf’)
During execution, if the following message is encountered:
Server:Message 5030, Level 16, Status 1, Row 1
Failed to exclusively lock the database to perform this operation.
DBCC execution completed. If DBCC outputs an error message, contact your system administrator.
Indicates that another of your programs is using the database, if you just used SQLServerEnterpriseManager to open the system tables of the test library during the operation, then exiting SQLServerEnterpriseManager is sufficient.
The prompt for a properly executed completion should look something like:
Warning:The log for database ‘test’ has been rebuilt. Transaction consistency has been lost. DBCCCHECKDB should be run to verify physical consistency. Will have to reset database options and may need to delete extra log files.
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.
At this point, you can open SQLServerEnterpriseManager and see that the database status is “DBO Only”. You can now access the user tables in the database.
9) Verify database consistency
10. Set the database to the normal state
If there are no errors, then congratulations, you can now You can now use the restored database normally.
11) In the last step, we need to restore the “Allow direct modification of system directories” set in step 6;