Dbask Q & a session 6



Recently, we have newly connected DB preparation room in dbask applet and added more than 60 technical experts. We look forward to more experts in database field and authors of public account joining motianlun to create an open, mutual and professional database technology community.

Quiz collection

Next, we will share the problems and diagnosis summary sorted out in this issue for your reference and study. The detailed diagnosis and analysis process can be viewed in the applet through the title link.

Question 1: why does Oracle not need double write like MySQL?

In order to solve the problem of partial page write, when MySQL flushes the dirty data to the data file, first use memcopy to copy the dirty data to the double write buffer in memory, and then use the double write buffer to write 1MB to the shared table space twice, and then immediately call the fsync function to synchronize to the disk to avoid the problem caused by the buffer. After completing the double write write After entering, the double write buffer is written to each tablespace file, which is a discrete write. This process is MySQL double write. Question: Oracle will have a similar situation. Why are you so confident? You don’t need double write

Diagnosis conclusion: this can only be said to be the design and implementation of InnoDB, different product design ideas are different. Because InnoDB is only a storage engine, the situation to be considered is more complicated. MySQL engine system also leads to the coexistence of redo and binlog. Under the leadership of Oracle in the future, these will be changed gradually.

Question 2. What’s the reason why the truncate partition table is very slow for three hours

The partition table has more than 200 gigabytes. The update global indexes option is used to delete one of the partitions through truncate, which has not been implemented for 3 hours. The global index exists in the partition table, which eventually makes the business impossible to operate. Kill the session recovery operation of truncate partition table. Please ask.

1. Are these three hours waiting for update global indexes?

2. After the truncate session is killed, business recovery and table data are not deleted. Isn’t it true that truncate doesn’t go undo? Why can the kill session be rolled back?

Diagnosis conclusion: 1. From the description, it is likely to be blocked on the update global indexes; it can be seen in combination with ash. 2. Truncate is DDL, and redo is not included in the record, but medata still has undo. If you fail this operation, it will roll back naturally.

Problem 3: the Oracle predefined processing statement is converted to MySQL

Oracle is written as follows:



How to convert to MySQL statement writing? Read on the Internet that MySQL uses declare, or can it realize the conversion of this writing method?

Diagnosis conclusion: similar to this writing: declare exit handler for sqlwarning, not found, sqlexception, but there is still a gap between MySQL exit and Oracle return.

Question 4. What is the impact on setting the large table field to unuse?

In Oracle 11.2, what is the impact of setting the large table field to unuse on subsequent management?

Diagnosis conclusion: set unused and drop unused columns is a standard processing method for tables with large amount of data. In fact, most of them will choose the right time to drop these columns within a few days after set unused. Therefore, there are not many opportunities for continuous management in the future.

Problem 5. The problem of restart caused by Rac heartbeat mechanism

RAC heartbeat mechanism includes network and disk heartbeat. In case of timeout of heartbeat between nodes (possibly due to server hang or network problems), will non primary node servers be restarted?

Does node restart mean cluster service restart or server restart? If the server is hung, it can be understood that the disk cannot be read or written, and the disk heartbeat timeout problem will surely restart the server. In addition, according to MOS documents, node eviction does not necessarily lead to server restart in versions after

Diagnosis conclusion: starting from, when a node in the cluster is expelled (such as losing network heartbeat) or ocssd.bin of the node has problems, the cluster will not restart the node directly, but first try to restart GI stack to solve the problem. If GI stack can not complete the scalable shutdown within the specified time (short disk I / O timeout), it will Restart the node. Please check the details about the mechanism of network heartbeat and disk heartbeat.

Problem 6: Oracle GoldenGate implements one to many replication

At present, a set of Oracle RAC, as the source, needs to be synchronized to the same computer room, one copy of data from another computer room, and one copy of data from another computer room. Does one to many replication with one set of Ogg have a great impact on the source performance? Or synchronize to the same machine room, and synchronize the data from the target end of the different machine to the different place?

Is there a better way to synchronize the data of different machines in the same computer room and different places?

Diagnosis conclusion: one to many can share the same extraction process, only one more delivery process is needed.

As long as it is the same platform and version of Oracle disaster recovery, ADG is basically used now. However, since the source is an IBM small computer and the target is an x86 server, ADG is not considered. Basically, only Ogg can be used. However, Ogg is not very stable, especially in the case of full library synchronization and frequent DDL.

Problem 7. PDB level load monitoring performance diagnosis

Before 12C, you can judge the database load by the indicator of dbtime. After upgrading to 12C and 18C, is there an indicator for database load monitoring to determine which PDB occupies the most CDB resources in the current container? When using oratop, you can only see the load information at the CDB level, and other indicators can quickly locate the PDB with high resource share.

Diagnosis conclusion: 12.2 can generate the AWR report of PDB level, and also can check the load of each PDB through OEM CC monitoring.

Question 8. When restarting multipath and udev, do you need to shut down the data and database cluster?

When adding new disks to ASM, do you need to shut down the data and database cluster when you restart multipath and udev by using multipath and udev, modifying multipath.conf de?

Diagnosis conclusion: in general, restarting udev and multipath will not affect the cluster, the original links are all in place, and the restart process is very fast. However, during the restart process, there may also be a case where the link times out or the original link is rammed, causing the cluster to restart. Also, if the network card uses udev binding, the node will restart. In addition, multipath has the reload command.

Question 9: can Oracle directory. Cache files be cleaned?

The utilization rate of the file system / u01 where the database is located is basically fast and full. According to find / – szie + 500m – type F, check the files larger than 500m to clean up and free up space. Ask you if the suffix is. Cache file can be cleaned up, and the file size is 500m, or those files can be cleaned up. I will find them one by one to clean up.

Expert answer: I don’t know if I can delete the files. It’s better to MV them to other places and delete them after a period of time. In addition, the slow file system is not necessarily the occupation of large files. It may be many small files, such as Oracle’s trace file. Use the Du command to find the files or directories that occupy a large space, and then consider deleting them.

Question 10. How does Oracle 11g clean up tnslsnr alert logs

How can Oracle 11g safely clean up the alert logs of the production environment? I want to clean up. Diag / tnslsnr / erp-db / listener / Alert

Diagnostic conclusion: These are listening log files. If you don’t need to use them, you can directly delete all XML files with numbers.
In addition, to disable the generation of listening logs in XML form, you can set the following parameters in lister.ora:


In addition, if it is the database alert log, you can compress the backup alert log before executing: > alert_sid.log

Question 11. How can I quickly locate slow statements in stored procedures

How to quickly locate the slow statements in stored procedures

Diagnosis conclusion: you can find the main SQL of the stored procedure through ash, then find all the recursive SQL in turn, and then sort the SQL resource consumption; the stored procedure records the log; track the session running the stored procedure with 10046; debug with the profiler of PLSQL developer, and show the running time of each SQL.