The desc command is not a SQL standard. desc is the sqlplus command.SQL Developer runs a big sql query and sql navigator a simple sql query.SQL Navigator for Oracle Version 6.2.1 does not support ORACLE version 11.2.
Viewed 1000+ timesYou Asked Hi Tom,I'm using the event 10046 to trace other sessions. But sometimes I find that there is not execution plan information included in the result trace file. Here is an example:1. From my session, I executeddchen@SAPU> exec sys.dbms_system.set_ev(130, 54378, 10046, 8, '')PL/SQL procedure successfully completed.2. From another session using SQL*Navigator (sid=130 and serial#=54378), executedselect count(*) from carrier.sl_conversion_phase p, carrier.sl_claim_number s, sap.claims c where p.reg_office = s.reg_office and c.claim_number = s.opus# and c.claim_id in (select claim_id from sap.sl_spc_claims where created_by_user_id = 'SLIC' and policy_coverage_id is null );3. then, back to my session, issueddchen@SAPU> exec sys.dbms_system.set_ev(130, 54378, 10046, 0, '')PL/SQL procedure successfully completed.4. ran TKPROF on the trace file ORA03688.TRC, I gotTKPROF: Release 8.1.7.0.0 - Production on Sun Dec 8 16:25:04 2002(c) Copyright 2000 Oracle Corporation. All rights reserved.Trace file: ora03688.trcSort options: default********************************************************************************count = number of times OCI procedure was executedcpu = cpu time in seconds executing elapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call********************************************************************************select count(*) from carrier.sl_conversion_phase p, carrier.sl_claim_number s, sap.claims c where p.reg_office = s.reg_office and c.claim_number = s.opus# and c.claim_id in (select claim_id from sap.sl_spc_claims where created_by_user_id = 'SLIC' and policy_coverage_id is null )call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.88 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 19.74 19.82 0 1866464 12 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 5 19.74 20.70 0 1866464 12 1Misses in library cache during parse: 0Optimizer goal: CHOOSEParsing user id: 44 ********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.88 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 19.74 19.82 0 1866464 12 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 5 19.74 20.70 0 1866464 12 1Misses in library cache during parse: 0OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 0 0.00 0.00 0 0 0 0Execute 0 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 0 0.00 0.00 0 0 0 0Misses in library cache during parse: 0 2 user SQL statements in session. 0 internal SQL statements in session. 2 SQL statements in session.********************************************************************************Trace file: ora03688.trcTrace file compatibility: 8.00.04Sort options: default 0 session in tracefile. 2 user SQL statements in trace file. 0 internal SQL statements in trace file. 2 SQL statements in trace file. 1 unique SQL statements in trace file. 60 lines in trace file.But, if we check the trace shown as follow you can see the execution plan information is there:*** 2002-12-08 16:19:16.642WAIT #5: nam='SQL*Net message from client' ela= 50139 p1=1413697536 p2=1 p3=0STAT #5 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE 'STAT #5 id=2 cnt=57 pid=1 pos=1 obj=0 op='NESTED LOOPS 'STAT #5 id=3 cnt=933091 pid=2 pos=1 obj=0 op='HASH JOIN 'STAT #5 id=4 cnt=1026 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN 'STAT #5 id=5 cnt=58 pid=4 pos=1 obj=0 op='VIEW VW_NSO_1 'STAT #5 id=6 cnt=58 pid=5 pos=1 obj=0 op='SORT UNIQUE 'STAT #5 id=7 cnt=57 pid=6 pos=1 obj=96712 op='TABLE ACCESS FULL SL_SPC_CLAIMS 'STAT #5 id=8 cnt=1026 pid=4 pos=2 obj=0 op='SORT JOIN 'STAT #5 id=9 cnt=18 pid=8 pos=1 obj=54410 op='TABLE ACCESS FULL SL_CONVERSION_PHASE 'STAT #5 id=10 cnt=16394 pid=3 pos=2 obj=52985 op='TABLE ACCESS FULL SL_CLAIM_NUMBER 'STAT #5 id=11 cnt=57 pid=2 pos=2 obj=99323 op='INDEX RANGE SCAN 'WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0=====================PARSING IN CURSOR #1 len=366 dep=0 uid=44 oct=3 lid=44 tim=14735176 hv=876398650 ad='1297c6e4'select count(*) from carrier.sl_conversion_phase p, carrier.sl_claim_number s, sap.claims c where p.reg_office = s.reg_office and c.claim_number = s.opus# and c.claim_id in (select claim_id from sap.sl_spc_claims where created_by_user_id = 'SLIC' and policy_coverage_id is null )END OF STMTPARSE #1:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14735217WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0WAIT #1: nam='SQL*Net message from client' ela= 2 p1=1413697536 p2=1 p3=0WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0=====================PARSING IN CURSOR #5 len=366 dep=0 uid=44 oct=3 lid=44 tim=14735237 hv=876398650 ad='1297c6e4'select count(*) from carrier.sl_conversion_phase p, carrier.sl_claim_number s, sap.claims c where p.reg_office = s.reg_office and c.claim_number = s.opus# and c.claim_id in (select claim_id from sap.sl_spc_claims where created_by_user_id = 'SLIC' and policy_coverage_id is null )END OF STMTPARSE #5:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14735278WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14735287*** 2002-12-08 16:19:38.095WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0FETCH #5:c=1974,e=1982,p=0,cr=1866464,cu=12,mis=0,r=1,dep=0,og=4,tim=14737272WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=14737278WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0If I use SQL_TRACE to trace the same query from my owner session, I can get the execution plan information in the TKPROF output report.Here is the SQL_TRACE output from TKPROF:TKPROF: Release 8.1.7.0.0 - Production on Sun Dec 8 15:48:31 2002(c) Copyright 2000 Oracle Corporation. All rights reserved.Trace file: ora03952.trcSort options: default********************************************************************************count = number of times OCI procedure was executedcpu = cpu time in seconds executing elapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call********************************************************************************alter session set sql_trace=truecall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 0 0.00 0.00 0 0 0 0Execute 1 0.00 0.59 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 1 0.00 0.59 0 0 0 0Misses in library cache during parse: 0Misses in library cache during execute: 1Optimizer goal: CHOOSEParsing user id: 44 ********************************************************************************select count(*) from carrier.sl_conversion_phase p, carrier.sl_claim_number s, sap.claims c where p.reg_office = s.reg_office and c.claim_number = s.opus# and c.claim_id in (select claim_id from sap.sl_spc_claims where created_by_user_id = 'SLIC' and policy_coverage_id is null )call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.48 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 19.99 19.98 0 1866464 12 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 19.99 20.46 0 1866464 12 1Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 44 Rows Row Source Operation------- --------------------------------------------------- 1 SORT AGGREGATE 57 NESTED LOOPS 933091 HASH JOIN 1026 MERGE JOIN CARTESIAN 58 VIEW VW_NSO_1 58 SORT UNIQUE 57 TABLE ACCESS FULL SL_SPC_CLAIMS 1026 SORT JOIN 18 TABLE ACCESS FULL SL_CONVERSION_PHASE 16394 TABLE ACCESS FULL SL_CLAIM_NUMBER 57 INDEX RANGE SCAN (object id 99323)********************************************************************************alter session set sql_trace=falsecall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.13 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.13 0 0 0 0Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 44 ********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.61 0 0 0 0Execute 3 0.00 0.59 0 0 0 0Fetch 2 19.99 19.98 0 1866464 12 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 7 19.99 21.18 0 1866464 12 1Misses in library cache during parse: 2Misses in library cache during execute: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 0 0.00 0.00 0 0 0 0Execute 0 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 0 0.00 0.00 0 0 0 0Misses in library cache during parse: 0 3 user SQL statements in session. 0 internal SQL statements in session. 3 SQL statements in session.********************************************************************************Trace file: ora03952.trcTrace file compatibility: 8.00.04Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 0 internal SQL statements in trace file. 3 SQL statements in trace file. 3 unique SQL statements in trace file. 63 lines in trace file.Here are my questions:1. Why dose tkprof ignore the execution plan in the trace file generated from event 10046 in some cases? I know it won't recognize those waits related information but not STATS related information.2. In the case shown in my case, the event 10046 generated two sections in the trace file for the same query statement. I only issued it once. Therefore, we got 2 counts of parses. How could this happan?Thanks. and Tom said...1) trace files are linear -- the stat records you show are for some "other cursor #5" -- no the cursor #5 you show. The stat records are only printed out when the cursor is closed -- I believe the other session you are monitoring is keeping the cursor open (hence the stat records won't make it out)2) sql navigator must "parse the query" in order to describe it (cause their programmers didn't know how to program Oracle) and then they parse it (wastefully) again in order to execute and display it.SQL_TRACE shows you reality - navigator is parsing the query two times.so, here is what i think - you ran the query in navigator. THEN you enabled trace. THEN you ran again in navigator. navigator closed the other query -- that dumped the stats records (for the PREVIOUS execution of that query). navigator then parsed the query -- to describe it. navigator then parsed and executed the query in order to print it. then you turned off trace -- hence NO stats records for that cursor. Rating (11 ratings)Is this answer out of date? If it is, please let us know via a Comment Comments Comment Does it mean SQL Navigator tool has problemsAjeet, December 09, 2002 - 1:50 am UTC
sql navigator 6 5 keygen 22
DOWNLOAD: https://urlcod.com/2vFFaM
2ff7e9595c
Commenti