Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ 10046 trace only open for a particular SQL statement     - Hibernate in profile (Database)

- Search Linux commands and files - which, whereis, locate, find (Linux)

- PHP security Programming Advice (Programming)

- Some of the bibliographic management tools to good use on linux (Linux)

- GNU / Linux system, how to clean up memory space (Linux)

- HDFS Hadoop Distributed File System Works (Server)

- 25 Git Usage Tips (Linux)

- Hadoop vs spark (Server)

- HttpClient4 usage upgrade from HttpClient3 (Programming)

- Installing software on Ubuntu: apt-get and dpkg difference (Linux)

- How to configure a development environment elegant Lua (Linux)

- GitLab Guide installation under Ubuntu 14.04 (Server)

- CentOS 6.5 using Virtualenv under development environment to build Python3 (Linux)

- Use PuTTY key authentication mechanism for remote login Linux (Linux)

- Applications Linux leap second problem caused (Linux)

- Fedora 22 Server how to upgrade to Fedora 23 Beta Server (Linux)

- jdbc Oracle database connection string writing pluggable (Database)

- Oracle first Automated Installation Packages (Database)

- Python script running in the background (Programming)

- Command line tool Tmux (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.
     
         
         
         
  More:      
 
- File easier to compare tools under Linux (Linux)
- Linux firewall settings instance (Linux)
- Zabbix configure DataGuard monitoring (Database)
- Mac OS X Server installation and application (Linux)
- CentOS 6.5 installation Python3.0 (Linux)
- CentOS / Linux install VNC Server (Linux)
- CentOS 5.10 installed Oracle 11G R2 (Database)
- KVM QEMU virtual machine installation configuration under CentOS (Linux)
- Installation through the network Debian 7 (Wheezy) (Linux)
- Install and configure GO 1.2.1 under CentOS 6.5 (Linux)
- Notebook computer forget password solution (Linux)
- Node.js simple interface server (Server)
- Awk include binding capacity larger than the specified size of all files directory (Linux)
- Java semaphores (Programming)
- Linux install and configure Heartbeat (Server)
- CentOS system Amoeba + MySQL Master-slave configuration (Database)
- Linux Security trust with users (Linux)
- Elasticsearch 2.20 Highlight (Server)
- Ubuntu U disk do not have write privileges can only read but not write (Linux)
- REDO LOG records management (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.