Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ 10046 trace only open for a particular SQL statement     - Xmanager Remote Desktop connection CentOS (Linux)

- CentOS7 installation performance monitoring system (Server)

- Puppet 3.x installed on Debian 7 (Server)

- Tsunami-udp accelerated network transmission (Linux)

- Linux linux system security (Linux)

- Ubuntu 14.04 build Hadoop 2.5.1 standalone and pseudo-distributed environment (32-bit) (Server)

- To change CentOS7 runlevel (Linux)

- Kitematic how seamless and DockerFILE (Server)

- 10 Best Swift Tutorial examples (Programming)

- Chkconfig set boot start under Linux (Linux)

- How to install GIMP 2.8.16 in Ubuntu 16.04,15.10,14.04 (Linux)

- Linux IPTables anti-DDOS attack Shell Scripting (Linux)

- How to use the on-screen keyboard in Linux (Linux)

- Python Flask environment to build (Linux)

- Talk about jsonp (Programming)

- Try to use Lets Encrypt (Linux)

- The temporary operation of the document mkstemp on Linux (Linux)

- XtraBackup achieve non-stop use of master-slave synchronization service (Database)

- Linux dynamic libraries and Guide (Programming)

- Linux system security knowledge (Linux)

  10046 trace only open for a particular SQL statement
  Add Date : 2018-11-21      
  Recently encountered such an interesting question: Is there a SQL statement, most of the time its execution time is several tens of milliseconds; but occasionally a particular execution time longer than 2 seconds. Because it is very sensitive to the application of the execution time of this statement, we must diagnose it because of what causes the occasional execution time is longer than 2 seconds.

Why is this issue challenging it? Because it is difficult to collect slow when 10046 trace: First, we do not know when this problem occurs, do not know what will happen in the session. If all-day open session 10046 trace, it will have a lot larger trace and affect overall database performance.

Fortunately, this database is 11g and 11g in the event ++ feature allows us only to a particular SQL collect 10046 trace. That in turn run 10046 trace when this SQL in SQL runs out after this Close 10046 trace. Such It can significantly reduce the size of the generated trace. But because we can not determine which session will be a problem, so long as the run-off of the session will have a SQL trace file.

Step is to open (put the following piece of SQL awsh60c8mpfu1 replace the SQL_ID):

alter system set events 'sql_trace [sql: awsh60c8mpfu1] level 12';

And the step is closed (put the following piece of SQL awsh60c8mpfu1 replace the SQL_ID):

alter system set events 'sql_trace [sql: awsh60c8mpfu1] off';

After collecting a lot of 10046 trace, and use tkprof format (specify AGGREGATE = NO, so that each execution would tkprof generate summary report), we finally locate the problem occurs when the SQL statement to read physical block has spent more much time.
- How nodeclub constructed Docker image (Server)
- Boost - Memory Management - smart pointers (Programming)
- Some Linux networking tools you might not know (Linux)
- CentOS 7 open ports (Linux)
- SSH Filesystem use a secure connection for network file system (Linux)
- Shell Script: Bulk add users, and set the random character password (Programming)
- Ubuntu batch scp to copy files without password (Linux)
- How to implement large-scale distributed Yahoo depth study on the Hadoop cluster (Server)
- Java development environment to build under Ubuntu (Linux)
- C ++ constant definition (Programming)
- Linux System Getting Started Tutorial: How to Force Change your password at next logon Linux (Linux)
- Docker manage data (Linux)
- QBit development of micro-services (Server)
- Default permissions Linux file and directory permissions and hide - umask, chattr, lsattr, SUID, SGID, SBIT, file (Linux)
- Network security system (Network)
- Linux ps command (Linux)
- Git uses a basic tutorial (Linux)
- Rely on IP chain established Linux firewall (Linux)
- Compile and install Ubuntu Linux 4.0.5 kernel, network and fix vmware kernel module compilation error (Linux)
- Killall five options you might not know (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.