Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ 10046 trace only open for a particular SQL statement     - Ubuntu Server (Ubuntu 14.04 LTS 64-bit) installation libgdiplus 2.10.9 error solution (Linux)

- Linux virtual memory and physical memory (Linux)

- Achieve camera preview by ffplay (Linux)

- A deep understanding of Java enum (Programming)

- Java semaphores (Programming)

- Use lsof restore accidentally deleted log files or database (Linux)

- Ubuntu 15.04 / CentOS 7.0 to set custom boot (Linux)

- CentOS 7 install Hadoop-cdh-2.5 on Mesos (Server)

- Linux cut Command Study Notes (Linux)

- Install Visual Studio Code in Ubuntu (Linux)

- The most common and most effective security settings under linux (Linux)

- Hadoop2.0 configuration yarn success (Server)

- To install and deploy Apache under the CentOS (Server)

- Access.log Apache access log analysis and how to import it into MySQL (Server)

- Installation and Configuration Munin monitoring server on Linux (Server)

- ORA-12154 TNS could not resolve the specified identifier (Database)

- CentOS 6.5 / 6.6 modify the default SSH port number (Linux)

- To help you easily protect the Linux System (Linux)

- Common Linux System Troubleshooting (Linux)

- Based kubernetes Construction Docker Cluster Management Comments (Server)

 
         
  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.
     
         
         
         
  More:      
 
- Modular JavaScript (Programming)
- Linux some lessons learned about network security (Linux)
- Use Docker containers (Linux)
- Use CutyCapt to convert HTML pages to png images on Linux (Linux)
- Heartbeat (v1, v2, pacemaker) cluster components Overview (Server)
- C ++ function object (Programming)
- Ubuntu and Archlinux install Notepadqq 0.50.2 (Linux)
- redis main building and disaster recovery from a cluster deployment (Database)
- GCC and gfortran write MEX program (Matlab2012a) under Ubuntu 14.04 (Programming)
- Detailed installation of CentOS 6.x (Linux)
- Ubuntu installed Gimp 2.6.11 (stable version) with PPA (Linux)
- Linux-du and df command (Linux)
- Git remote repository Explanation (Linux)
- Management DB2 logs (Database)
- Creating and extracting archives 11 tar command examples in Linux (Linux)
- Debian users to install FFmpeg 2.2.2 (Linux)
- Sleuth Kit: used to analyze a disk image and restore files open source forensics tools (Linux)
- To install Python-Pip and Fabric under CentOS / Ubuntu (Linux)
- linux server security (Linux)
- To install Emacs under CentOS 6.5 (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.