In the previous post (Oracle TKProf Usage), we saw how to generate a SQL Trace and then how to format it using TKProf. In that post we create a trace for following SQL statement:- select * from all_objects; In this post we would interpret TKProf output of that SQL query. Following is the TKProf Output…
The TKProf facility accepts as input a SQL trace file and produces a formatted output file. TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename] filename_source: The trace file that needs to be formatted.filename_output: The output file where the formatted file will be written.EXPLAIN: This option causes TKProf to generate an execution plan for each SQL statement in…
Event 10046 is used to gather extended SQL_TRACE information for Oracle sessions. This article describes how to enable event 10046 to get extended information to troubleshoot a performance issue. To gather 10046 trace at the session level: alter session set tracefile_identifier=’10046′; alter session set timed_statistics = true;alter session set statistics_level=all;alter session set max_dump_file_size = unlimited;alter…
This article focuses on the Oracle Listener Tracing & Troubleshooting, in this article we would explore the parameters available to enable tracing on Oracle listener so that additional data is available to troubleshoot network related issues between client & server. To enable Listener tracing add following parameters to listener.ora:- TRACE_LEVEL_LISTENER=[0-16|USER|ADMIN|SUPPORT|OFF]#This parameter defines the level of…
This error is received when the listener received a request to establish a connection to a database or other service but the connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for…
This error appears when you try to issue a DDL against a global temporary table thats created with “on commit preserve rows” clause.:- create global temporary table TEST (object_id number, object_name varchar2(30)) on commit PRESERVE rows; Such tables get bound to the session with the first insert and then this binding is ONLY removed when…
This error appears when the process of starting up a dedicated server process failed and it could be either because the executable could not be found or the environment isn’t set up correctly. Turn on tracing at the ADMIN level and re-execute the operation. Tracing can be set by adding following clause in listener.ora file…
This error occurs when there was no default service configured for the listener and SERVICE_NAME was missing from the request received by the listener. When a user tries to connect to the listener, it may be servicing several different databases. If user specifies a service name in the connect request then the listener will connect…
TNS:listener received no CONNECT_DATA from clientCause: No CONNECT_DATA was passed to the listener.Action: Check that the service name resolved from TNSNAMES.ORA has the CONNECT_DATA component of the connect descriptor. Below is the sample entry of tnsnames.ora:- #When using SERVICE_NAME in CONNECT_DATA componentorcl.example.com = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT =…
Sometimes due to missing library you can get following error in the errorlog:- error while loading shared libraries: libkonavm.so: cannot open shared object file: No such file or directory To fix such issues, find the path where libkonavm.so file is kept:- locate libkonavm.so Once the path is located, lets say its kept in /opt/sybase/ASE-16_0/symlib/libkonavm.so, create…
Oracle Database Internal Mechanism 