Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ 10046 trace only open for a particular SQL statement     - Under CentOS yum install Nginx smooth switch mounted to Tengine (Server)

- Oracle Sql Loader tool has shown signs (Database)

- Getting Started with Linux system to learn: How do I know which processes are running on the CPU core (Linux)

- OpenSSL for secure transmission and use of skills of files between Windows and Linux (Linux)

- Oracle data row split multiple lines (Database)

- How to configure SNMPv3 on Ubuntu, CentOS and Cisco systems (Linux)

- Linux system on how to use rsync to synchronize data (Server)

- SUSE Linux install Oracle 10g and problem solving (Linux)

- SQLite database commonly used sentences and visualization tools on MAC MeasSQLlite use (Database)

- The wrong in Linux: too many open files (Linux)

- SSH security note (Linux)

- Oracle 11g dataguard main library backup and recovery to the test environment in one database error (Database)

- Java Graphics2D Paint (Programming)

- Spring Data JPA call a stored procedure examples (Programming)

- Use netcat [nc] command on Linux and Unix port scan (Server)

- Getting Started with Linux: Learn how to install and access CentOS 7 Remote Desktop on a VPS (Server)

- Remote database using RMAN recovery test (RAC return to single-instance database) (Database)

- CentOS / Linux NIC set IP address configuration (Linux)

- Win7 used Eclipse to connect the virtual machine in Ubuntu Hadoop2.4 (Server)

- Java, boolean operators & =, | = ^ = use (Programming)

 
         
  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:      
 
- How to install the Linux text editor Atom 0.124.0 (Linux)
- Linux System Getting Started Learning: install software packages on Ubuntu and Fedora (Linux)
- Android imitation UC browser scroll up and down functions (Programming)
- Build the first ASP.NET 5 Web project in Mac OS X Yosemite 10.10.3 (Server)
- Oracle database NUMBER (x, y) data types (Database)
- Linux platform host to prevent hacking skills (Linux)
- PL / SQL how to make the program every few seconds to insert a data (Database)
- The basic principle of pointers in C ++ (Programming)
- Configuring a Linux operating system security management services Explain (Linux)
- Binding unofficial Google Drive and Ubuntu 14.04 LTS (Linux)
- Ubuntu Gnome and Fedora 22 Gnome desktop, extended to achieve global menu (Linux)
- Linux Getting Started tutorial: XWindow what (Linux)
- Linux environment variables inside (Linux)
- C language macro definition #define Usage (Programming)
- Using the Linux folder wc statistics number of lines of code for all files (including subdirectories) (Linux)
- Ubuntu and derived versions of the user how to install G Mic 1.5.8.5 (Linux)
- Linux system installation Gitlab (Server)
- Kubernetes cluster deployment (Server)
- RegExp object implements regular match --JavaScript (Programming)
- Polymorphism of the C ++ compiler and run-time polymorphism (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.