Section 5.8. Exercises | Optimizing Oracle Performance

5.8 Exercises

In Example 5-8, which WAIT lines refer to wait events made within database calls, and which refer to wait events made between database calls? Describe how each c , e , and ela statistic shown fits into the relationship e c + S ela .

Example 5-8. Extended SQL trace data file excerpt

 ...   Many WAIT #1 lines are omitted for clarity   ... = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #1 len=253 dep=0 uid=18 oct=3 lid=18 tim=1024427939516845 hv=1223272015  ad='80cbc5b8' ...   SQL text is omitted for clarity   ... END OF STMT PARSE #1:c=60000,e=55973,p=3,cr=44,cu=6,mis=1,r=0,dep=0,og=4,tim=1024427939516823 EXEC #1:c=0,e=140,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1024427939517471 WAIT #1: nam='SQL*Net message to client' ela= 15 p1=1650815232 p2=1 p3=0 WAIT #1: nam='db file sequential read' ela= 678 p1=7 p2=11146 p3=1 WAIT #1: nam='db file sequential read' ela= 815 p1=7 p2=11274 p3=1 FETCH #1:c=200000,e=259460,p=2,cr=12,cu=24,mis=0,r=1,dep=0,og=4,tim=1024427939777318 WAIT #1: nam='SQL*Net message from client' ela= 1450 p1=1650815232 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1650815232 p2=1 p3=0 FETCH #1:c=0,e=339,p=0,cr=0,cu=0,mis=0,r=12,dep=0,og=4,tim=1024427939779621 WAIT #1: nam='SQL*Net message from client' ela= 7828 p1=1650815232 p2=1 p3=0 ...   STAT lines are omitted for clarity   ... = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #1 len=55 dep=0 uid=18 oct=42 lid=18 tim=1024427939789693 hv=3381932903  ad='80c9e33c' alter session set events '10046 trace name context off' END OF STMT PARSE #1:c=0,e=810,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1024427939789677

For Example 5-9, construct a graph like the one shown in Figure 5-3 that illustrates the recursive relationships among database calls. Compute the contribution to e of each database call. What type of application would perform the actions shown here?

Example 5-9. SQL trace file exhibiting recursive SQL behavior (level-1 output is shown to reduce clutter for the exercise)

 /u01/oradata/admin/V901/udump/ora_23317_recursive.trc      *** TRACE DUMP CONTINUED FROM FILE  ***      Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production ORACLE_HOME = /u01/oradata/app/9.0.1 System name:    Linux Node name:  research Release:    2.4.4-4GB Version:    #1 Fri May 18 14:11:12 GMT 2001 Machine:    i686 Instance name: V901 Redo thread mounted by this instance: 1 Oracle process number: 9 Unix process pid: 23317, image: oracle@research (TNS V1-V3)      *** 2003-05-18 11:14:59.469 *** SESSION ID:(8.1578) 2003-05-18 11:14:59.469 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #1 len=68 dep=0 uid=5 oct=42 lid=5 tim=1053274499469370 hv=1635464953  ad='51f65c00' alter session set events '10046 trace name context forever, level 1' END OF STMT EXEC #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1053274499469133 = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #2 len=175 dep=1 uid=0 oct=3 lid=0 tim=1053274499471797 hv=1491008679  ad='52107fa8' select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled  from obj$ o,user$  u,trigger$ t  where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user#  order by o. obj# END OF STMT PARSE #2:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1053274499471765 EXEC #2:c=0,e=160,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1053274499483293 FETCH #2:c=0,e=32228,p=1,cr=8,cu=0,mis=0,r=1,dep=1,og=3,tim=1053274499515571 FETCH #2:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1053274499515717 = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #1 len=44 dep=0 uid=5 oct=2 lid=5 tim=1053274499516502 hv=2583883  ad='51f224f8' insert into t values (1001, rpad(1001,1000)) END OF STMT PARSE #1:c=0,e=45515,p=1,cr=8,cu=0,mis=1,r=0,dep=0,og=3,tim=1053274499516473 = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #2 len=22 dep=1 uid=5 oct=3 lid=5 tim=1053274499535321 hv=4140187373  ad='521444c8' SELECT count(*) from t END OF STMT PARSE #2:c=0,e=1003,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,tim=1053274499535287 EXEC #2:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1053274499535550 *** 2003-05-18 11:15:13.212 FETCH #2: c=3730000,e=13676722,p=127292,cr=127894,cu=260,mis=0,r=1,dep=1,og=3,tim=1053274513212315 EXEC #1: c=3730000,e=13695999,p=127293,cr=127897,cu=264,mis=0,r=1,dep=0,og=3,tim=1053274513212610 = = = = = = = = = = = = = = = = = = = = = PARSING IN CURSOR #4 len=52 dep=0 uid=5 oct=47 lid=5 tim=1053274513254792 hv=1697159799  ad='51f59e44' BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; END OF STMT PARSE #4:c=0,e=149,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1053274513254759 EXEC #4:c=0,e=38900,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=3,tim=1053274513293822 STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE ' STAT #2 id=2 cnt=1 pid=1 pos=1 obj=31159 op='TABLE ACCESS FULL T ' XCTEND rlbk=0, rd_only=0

Trace a DDL command, such as DROP TABLE . How many dictionary operations does the Oracle kernel perform implicitly for you when you drop a table? How does the number of operations change if the table being dropped has indexes? What if there are histograms in place on columns ? What about constraints? What if the table is involved in a materialized view, or is subject to a security policy?

Top