This discussion is archived
10 Replies Latest reply: Apr 18, 2012 9:36 PM by 839439 RSS

Exam 1 (Tuning SQL)

846231 Newbie
Currently Being Moderated
Hi All,

I am applying a new job because I am very stressed at my current company :( . because they want to do everything under the SUN?
Are your companies expecting the same of you? Is this really the trend for all companies now? That you be all around skilled worker?
Even I applied as DBA position, they will throws me network problems, hardware error problems, applications error problems, Operationg system error problems,
Programmer's error problems. etc, etc.

I can not find anymore company like the first one I joined which has each own separate technical group.
The have a network group, a developers group, a DBA group, a Systems Admin Group, and Hardware Engineers.
Maybe this kind of setup will burn down the financial strength of this company?

Can you share how do you fair in your company? Are you happy and contented with you current technical job?
Or do you have your own service provider tech support company?
I understand everyone need change and can not stay long in one company or maybe will not be "contented" forever?


Thanks a lot,

Edited by: KinsaKaUy? on 17-Apr-2012 19:31
  • 1. Re: Exam 1 (Tuning SQL)
    900442 Explorer
    Currently Being Moderated
    Well, it might be the case in some companies where they are tied up for resources. I dont know if that is a trend, as a DBA is generally considered a specialized skillset with it's own fair set of issues to deal with. Some companies can ask DBA's to learn other skillsets like SQL Server, DB/2, etc.
    Discussing this clearly during the interview phase of what really is expected from you will certainly help clear some of the doubts regarding the job scope and role.
    Contendedness is subjective, and there are people who work in the best of environments and still are not contended. Clearly outline what your expectations from a job and a company are and apply accordingly.
    Speaking to the manager if the workload becomes too much to handle (easier said than done) will help. Remember, if unrelated work is being getting done by you, you are accepting it.
    Within certain limits though, stuff related to application,network or server is a good thing to learn as it sets it up as a good platform later on.

    Regards....
  • 2. Re: Exam 1 (Tuning SQL)
    846231 Newbie
    Currently Being Moderated
    Very well said raj tanks :)


    While applying a new job, I was given a actual scenario exam:

    Given: a sql query
    ===========
    select  
    A.PER_ID,  
    A.ACCT_ID,  
    A.PREM_ID,  
    A.PER_ID_NBR,  
    A.ID_TYPE,  
    A.FUEL_TYPE   
    from  
    (SELECT  DISTINCT  
    ACTPER.PER_ID,  
    ACTPER.ACCT_ID,  
    PREM.PREM_ID,  
    NVL(PERID.PER_ID_NBR,' ') AS PER_ID_NBR,  
    NVL(IT.DESCR,' ') AS ID_TYPE,  
    SATC.CHAR_VAL AS FUEL_TYPE,  
    ACT.ACCESS_GRP_CD,  
    LAG(ACTPER.ACCT_ID, 1, 0) OVER (PARTITION BY ACTPER.PER_ID,ACCT_ID ORDER BY SATC.CHAR_VAL,PREM.PREM_ID DESC NULLS LAST) LAST_ACCT 
    FROM CI_ACCT ACT  
    LEFT JOIN CI_SA SA ON SA.ACCT_ID=ACT.ACCT_ID   
    LEFT JOIN CI_SA_TYPE_CHAR SATC ON (SATC.CIS_DIVISION = SA.CIS_DIVISION AND SATC.SA_TYPE_CD = SA.SA_TYPE_CD AND SATC.CHAR_TYPE_CD = 'FUELIND')   
    LEFT JOIN CI_PREM PREM ON SA.CHAR_PREM_ID=PREM.PREM_ID and sa_status_flg in ('05','10','20','30','40','50') AND SATC.CHAR_TYPE_CD = 'FUELIND'   
    LEFT JOIN CI_ACCT_PER ACTPER ON (ACTPER.ACCT_ID = ACT.ACCT_ID)  
    LEFT JOIN CI_PER_ID PERID ON (PERID.PER_ID=ACTPER.PER_ID AND PERID.PRIM_SW='Y')   
    LEFT JOIN CI_ID_TYPE_L IT ON (PERID.ID_TYPE_CD=IT.ID_TYPE_CD AND IT.LANGUAGE_CD=:LANGUAGE)   
    WHERE (ACT.ACCT_ID LIKE :F5 OR SA.OLD_ACCT_ID LIKE :F5) 
    ) A 
    WHERE ((A.ACCT_ID <> A.LAST_ACCT)  OR (A.ACCT_ID = A.LAST_ACCT and A.PREM_ID IS NOT NULL and A.FUEL_TYPE IS NOT NULL))  
    AND A.ACCESS_GRP_CD IN (SELECT ACCESS_GRP_CD FROM CI_USR_ACC_GRP_VW WHERE USER_ID=:USERID)  
    and rownum < 301;
    And given its trace file:
    =============
    Trace file c:\oracle\admin\truibmd2\bdump\diag\rdbms\truibmd2\truibmd2\trace\truibmd2_ora_4968_ISS-5045.trc
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Windows Server 2003 Version V5.2 Service Pack 2
    CPU                 : 4 - type 586, 1 Physical Cores
    Process Affinity    : 0x00000000
    Memory (Avail/Total): Ph:12447M/15359M, Ph+PgF:14136M/17164M, VA:1311M/2047M
    Instance name: truibmd2
    Redo thread mounted by this instance: 1
    Oracle process number: 58
    Windows thread id: 4968, image: ORACLE.EXE (SHAD)
    
    
    *** 2012-04-16 13:17:24.131
    *** SESSION ID:(138.4242) 2012-04-16 13:17:24.131
    *** CLIENT ID:() 2012-04-16 13:17:24.131
    *** SERVICE NAME:(TRUIBMD2) 2012-04-16 13:17:24.131
    *** MODULE NAME:(SQL*Plus) 2012-04-16 13:17:24.131
    *** ACTION NAME:() 2012-04-16 13:17:24.131
     
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=563198177150
    WAIT #1: nam='SQL*Net message from client' ela= 2413 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=563198180634
    =====================
    PARSING IN CURSOR #2 len=1258 dep=0 uid=44 oct=3 lid=44 tim=563198181649 hv=790944548 ad='210afa90' sqlid='6r8yq5crk9qt4'
    select
    A.PER_ID,
    A.ACCT_ID,
    A.PREM_ID,
    A.PER_ID_NBR,
    A.ID_TYPE,
    A.FUEL_TYPE
    from
    (SELECT  DISTINCT
    ACTPER.PER_ID,
    ACTPER.ACCT_ID,
    PREM.PREM_ID,
    NVL(PERID.PER_ID_NBR,' ') AS PER_ID_NBR,
    NVL(IT.DESCR,' ') AS ID_TYPE,
    SATC.CHAR_VAL AS FUEL_TYPE,
    ACT.ACCESS_GRP_CD,
    LAG(ACTPER.ACCT_ID, 1, 0) OVER (PARTITION BY ACTPER.PER_ID,ACCT_ID ORDER BY SATC.CHAR_VAL,PREM.PREM_ID DESC NULLS LAST) LAST_ACCT
    FROM CI_ACCT ACT
    LEFT JOIN CI_SA SA ON SA.ACCT_ID=ACT.ACCT_ID
    LEFT JOIN CI_SA_TYPE_CHAR SATC ON (SATC.CIS_DIVISION = SA.CIS_DIVISION AND SATC.SA_TYPE_CD = SA.SA_TYPE_CD AND SATC.CHAR_TYPE_CD = 'FUELIND')
    LEFT JOIN CI_PREM PREM ON SA.CHAR_PREM_ID=PREM.PREM_ID and sa_status_flg in ('05','10','20','30','40','50') AND SATC.CHAR_TYPE_CD = 'FUELIND'
    LEFT JOIN CI_ACCT_PER ACTPER ON (ACTPER.ACCT_ID = ACT.ACCT_ID)
    LEFT JOIN CI_PER_ID PERID ON (PERID.PER_ID=ACTPER.PER_ID AND PERID.PRIM_SW='Y')
    LEFT JOIN CI_ID_TYPE_L IT ON (PERID.ID_TYPE_CD=IT.ID_TYPE_CD AND IT.LANGUAGE_CD=:LANGUAGE)
    WHERE (ACT.ACCT_ID LIKE :F5 OR SA.OLD_ACCT_ID LIKE :F5)
    ) A
    WHERE ((A.ACCT_ID <> A.LAST_ACCT)  OR (A.ACCT_ID = A.LAST_ACCT and A.PREM_ID IS NOT NULL and A.FUEL_TYPE IS NOT NULL))
    AND A.ACCESS_GRP_CD IN (SELECT ACCESS_GRP_CD FROM CI_USR_ACC_GRP_VW WHERE USER_ID=:USERID)
    and rownum < 301
    END OF STMT
    PARSE #2:c=0,e=923,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=563198181645
    =====================
    PARSING IN CURSOR #1 len=37 dep=1 uid=0 oct=3 lid=0 tim=563198191503 hv=1398610540 ad='21205150' sqlid='grwydz59pu6mc'
    select text from view$ where rowid=:1
    END OF STMT
    PARSE #1:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=563198191500
    BINDS #1:
     Bind#0
      oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
      oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
      kxsbbbfp=22ced8a0  bln=16  avl=16  flg=05
      value=00005B8E.0005.0001
    EXEC #1:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=563198191666
    WAIT #1: nam='db file sequential read' ela= 44921 file#=1 block#=23438 blocks=1 obj#=-1 tim=563198238073
    FETCH #1:c=0,e=46511,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=563198238200
    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=1 time=0 us cost=1 size=15 card=1)'
    BINDS #2:
     Bind#0
      oacdty=01 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000000 frm=01 csi=871 siz=256 off=0
      kxsbbbfp=22cedb10  bln=128  avl=03  flg=05
      value="ENG"
     Bind#1
      oacdty=01 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000000 frm=01 csi=871 siz=0 off=128
      kxsbbbfp=22cedb90  bln=128  avl=12  flg=01
      value="736869533800"
     Bind#2
      No oacdef for this bind.
     Bind#3
      oacdty=01 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000000 frm=01 csi=871 siz=128 off=0
      kxsbbbfp=22ceda84  bln=128  avl=07  flg=05
      value="SYSUSER"
    EXEC #2:c=0,e=75899,p=1,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=563198257774
    WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=563198257877
    WAIT #2: nam='db file sequential read' ela= 11195 file#=5 block#=6124 blocks=1 obj#=53045 tim=563198270541
    WAIT #2: nam='db file scattered read' ela= 16610 file#=5 block#=6125 blocks=13 obj#=53045 tim=563198287296
    WAIT #2: nam='db file scattered read' ela= 1239 file#=5 block#=6139 blocks=8 obj#=53045 tim=563198289991
    WAIT #2: nam='db file sequential read' ela= 1938 file#=5 block#=6148 blocks=1 obj#=53045 tim=563198292729
    WAIT #2: nam='db file scattered read' ela= 804 file#=5 block#=6150 blocks=3 obj#=53045 tim=563198293682
    WAIT #2: nam='db file scattered read' ela= 8158 file#=5 block#=6155 blocks=6 obj#=53045 tim=563198302084
    WAIT #2: nam='db file scattered read' ela= 5568 file#=5 block#=6162 blocks=2 obj#=53045 tim=563198308029
    WAIT #2: nam='db file scattered read' ela= 28154 file#=5 block#=6165 blocks=11 obj#=53045 tim=563198336471
    WAIT #2: nam='db file scattered read' ela= 4283 file#=5 block#=6178 blocks=3 obj#=53045 tim=563198341991
    WAIT #2: nam='db file sequential read' ela= 848 file#=5 block#=6184 blocks=1 obj#=53045 tim=563198343235
    WAIT #2: nam='db file sequential read' ela= 5259 file#=5 block#=585331 blocks=1 obj#=57218 tim=563198350177
    WAIT #2: nam='db file sequential read' ela= 5784 file#=5 block#=6993 blocks=1 obj#=53071 tim=563198356016
    WAIT #2: nam='db file sequential read' ela= 7933 file#=5 block#=544781 blocks=1 obj#=56875 tim=563198364076
    WAIT #2: nam='db file sequential read' ela= 10010 file#=5 block#=544782 blocks=1 obj#=56875 tim=563198374156
    FETCH #2:c=15625,e=125364,p=53,cr=193,cu=0,mis=0,r=0,dep=0,og=1,tim=563198383272
    STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=193 pr=53 pw=53 time=0 us)'
    STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='HASH JOIN SEMI (cr=193 pr=53 pw=53 time=0 us cost=61 size=258 card=2)'
    STAT #2 id=3 cnt=1 pid=2 pos=1 obj=0 op='VIEW  (cr=192 pr=53 pw=53 time=0 us cost=59 size=232 card=2)'
    STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op='HASH UNIQUE (cr=192 pr=53 pw=53 time=0 us cost=59 size=424 card=2)'
    STAT #2 id=5 cnt=1 pid=4 pos=1 obj=0 op='WINDOW SORT (cr=192 pr=53 pw=53 time=0 us cost=59 size=424 card=2)'
    STAT #2 id=6 cnt=1 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=192 pr=53 pw=53 time=0 us cost=57 size=424 card=2)'
    STAT #2 id=7 cnt=1 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=190 pr=53 pw=53 time=0 us cost=56 size=402 card=2)'
    STAT #2 id=8 cnt=1 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=190 pr=53 pw=53 time=0 us cost=55 size=324 card=2)'
    STAT #2 id=9 cnt=1 pid=8 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=188 pr=51 pw=51 time=0 us cost=54 size=260 card=2)'
    STAT #2 id=10 cnt=1 pid=9 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=186 pr=50 pw=50 time=0 us cost=53 size=216 card=2)'
    STAT #2 id=11 cnt=1 pid=10 pos=1 obj=0 op='FILTER  (cr=183 pr=49 pw=49 time=0 us)'
    STAT #2 id=12 cnt=4204 pid=11 pos=1 obj=0 op='HASH JOIN OUTER (cr=183 pr=49 pw=49 time=7235 us cost=52 size=134 card=2)'
    STAT #2 id=13 cnt=2930 pid=12 pos=1 obj=53045 op='TABLE ACCESS FULL CI_ACCT (cr=61 pr=49 pw=49 time=52835 us cost=17 size=70296 card=2929)'
    STAT #2 id=14 cnt=3343 pid=12 pos=2 obj=57127 op='TABLE ACCESS FULL CI_SA (cr=122 pr=0 pw=0 time=2196 us cost=34 size=143663 card=3341)'
    STAT #2 id=15 cnt=1 pid=10 pos=2 obj=57218 op='TABLE ACCESS BY INDEX ROWID CI_SA_TYPE_CHAR (cr=3 pr=1 pw=1 time=0 us cost=1 size=41 card=1)'
    STAT #2 id=16 cnt=1 pid=15 pos=1 obj=57219 op='INDEX UNIQUE SCAN XC323P0 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
    STAT #2 id=17 cnt=1 pid=9 pos=2 obj=53071 op='INDEX RANGE SCAN XM150P0 (cr=2 pr=1 pw=1 time=0 us cost=1 size=22 card=1)'
    STAT #2 id=18 cnt=0 pid=8 pos=2 obj=56873 op='TABLE ACCESS BY INDEX ROWID CI_PER_ID (cr=2 pr=2 pw=2 time=0 us cost=1 size=32 card=1)'
    STAT #2 id=19 cnt=0 pid=18 pos=1 obj=56875 op='INDEX RANGE SCAN XM170P0 (cr=2 pr=2 pw=2 time=0 us cost=1 size=0 card=1)'
    STAT #2 id=20 cnt=0 pid=7 pos=2 obj=54935 op='TABLE ACCESS BY INDEX ROWID CI_ID_TYPE_L (cr=0 pr=0 pw=0 time=0 us cost=1 size=39 card=1)'
    STAT #2 id=21 cnt=0 pid=20 pos=1 obj=54936 op='INDEX UNIQUE SCAN XC539P0 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
    STAT #2 id=22 cnt=1 pid=6 pos=2 obj=0 op='VIEW  (cr=2 pr=0 pw=0 time=0 us cost=1 size=11 card=1)'
    STAT #2 id=23 cnt=1 pid=22 pos=1 obj=0 op='FILTER  (cr=2 pr=0 pw=0 time=0 us)'
    STAT #2 id=24 cnt=1 pid=23 pos=1 obj=56954 op='INDEX UNIQUE SCAN XM174P0 (cr=2 pr=0 pw=0 time=0 us cost=1 size=11 card=1)'
    STAT #2 id=25 cnt=0 pid=2 pos=2 obj=0 op='VIEW  VW_NSO_1 (cr=1 pr=0 pw=0 time=0 us cost=2 size=14 card=1)'
    STAT #2 id=26 cnt=0 pid=25 pos=1 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=0 us cost=2 size=56 card=1)'
    STAT #2 id=27 cnt=0 pid=26 pos=1 obj=54232 op='TABLE ACCESS BY INDEX ROWID CI_DAR_USR (cr=1 pr=0 pw=0 time=0 us cost=1 size=30 card=1)'
    STAT #2 id=28 cnt=0 pid=27 pos=1 obj=54233 op='INDEX SKIP SCAN XC691P0 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
    STAT #2 id=29 cnt=0 pid=26 pos=2 obj=53083 op='INDEX FULL SCAN XC692P0 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)'
    WAIT #2: nam='SQL*Net message from client' ela= 2426 driver id=1413697536 #bytes=1 p3=0 obj#=56875 tim=563198386424
    =====================
    PARSING IN CURSOR #1 len=56 dep=0 uid=44 oct=42 lid=44 tim=563198386501 hv=1729844458 ad='0' sqlid='5dt9w7dmjqp7a'
     alter session set events '10046 trace name context off'
    END OF STMT
    PARSE #1:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=563198386499
    EXEC #1:c=0,e=143,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=563198386677
    Question:

    1. Based on the trace file, is the SQL performing good? performing Bad? or can not be determined at all, and why.



    Thanks a lot
  • 3. Re: Exam 1 (Tuning SQL)
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    "good" and "bad" are relative concepts, and should be determined by the users, but assuming that there is no catch here, the query is executed not in a loop, but one-by-one, interactively by a user, I'd say it's pretty good.
    PARSING IN CURSOR #2 len=1258 dep=0 uid=44 oct=3 lid=44 tim=563198181649 hv=790944548 ad='210afa90' sqlid='6r8yq5crk9qt4'[...]
    WAIT #2: nam='SQL*Net message from client' ela= 2426 driver id=1413697536 #bytes=1 p3=0 obj#=56875 tim=563198386424
    , i.e.
    start of parse at 563198181649, end of fetch, switched to waiting for SQL*Net message from the client at 563198386424, the difference is 204775 microseconds, i.e. 0.2 s -- so the end user won't experience any delay.

    The question is not a trick one, it simply checks whether or not you have worked with 10046 trace files (i.e. know fields, units, notations etc.).

    Best regards,
    Nikolay
  • 4. Re: Exam 1 (Tuning SQL)
    P.Forstmann Guru
    Currently Being Moderated
    When given a raw trace file, you should use TKPROF to get readable output. Normally there is no need to try to analyze it without any tool like TKPROF (unless tools are not able to analyze it or to get what you are looking for):
    TKPROF: Release 11.2.0.2.0 - Development on Mer. Avr. 18 07:58:27 2012
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Trace file: input.trc
    Sort options: default
    
    ********************************************************************************
    count    = number of times OCI procedure was executed
    cpu      = cpu time in seconds executing 
    elapsed  = elapsed time in seconds executing
    disk     = number of physical reads of buffers from disk
    query    = number of buffers gotten for consistent read
    current  = number of buffers gotten in current mode (usually for update)
    rows     = number of rows processed by the fetch or execute call
    ********************************************************************************
    
    select
    A.PER_ID,
    A.ACCT_ID,
    A.PREM_ID,
    A.PER_ID_NBR,
    A.ID_TYPE,
    A.FUEL_TYPE
    from
    (SELECT  DISTINCT
    ACTPER.PER_ID,
    ACTPER.ACCT_ID,
    PREM.PREM_ID,
    NVL(PERID.PER_ID_NBR,' ') AS PER_ID_NBR,
    NVL(IT.DESCR,' ') AS ID_TYPE,
    SATC.CHAR_VAL AS FUEL_TYPE,
    ACT.ACCESS_GRP_CD,
    LAG(ACTPER.ACCT_ID, 1, 0) OVER (PARTITION BY ACTPER.PER_ID,ACCT_ID ORDER BY SATC.CHAR_VAL,PREM.PREM_ID DESC NULLS LAST) LAST_ACCT
    FROM CI_ACCT ACT
    LEFT JOIN CI_SA SA ON SA.ACCT_ID=ACT.ACCT_ID
    LEFT JOIN CI_SA_TYPE_CHAR SATC ON (SATC.CIS_DIVISION = SA.CIS_DIVISION AND SATC.SA_TYPE_CD = SA.SA_TYPE_CD AND SATC.CHAR_TYPE_CD = 'FUELIND')
    LEFT JOIN CI_PREM PREM ON SA.CHAR_PREM_ID=PREM.PREM_ID and sa_status_flg in ('05','10','20','30','40','50') AND SATC.CHAR_TYPE_CD = 'FUELIND'
    LEFT JOIN CI_ACCT_PER ACTPER ON (ACTPER.ACCT_ID = ACT.ACCT_ID)
    LEFT JOIN CI_PER_ID PERID ON (PERID.PER_ID=ACTPER.PER_ID AND PERID.PRIM_SW='Y')
    LEFT JOIN CI_ID_TYPE_L IT ON (PERID.ID_TYPE_CD=IT.ID_TYPE_CD AND IT.LANGUAGE_CD=:LANGUAGE)
    WHERE (ACT.ACCT_ID LIKE :F5 OR SA.OLD_ACCT_ID LIKE :F5)
    ) A
    WHERE ((A.ACCT_ID  A.LAST_ACCT)  OR (A.ACCT_ID = A.LAST_ACCT and A.PREM_ID IS NOT NULL and A.FUEL_TYPE IS NOT NULL))
    AND A.ACCESS_GRP_CD IN (SELECT ACCESS_GRP_CD FROM CI_USR_ACC_GRP_VW WHERE USER_ID=:USERID)
    and rownum < 301
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.02          0          0          0           0
    Fetch        1      0.01       0.12         53        193          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.01       0.15         53        193          0           0
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 44  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  COUNT STOPKEY (cr=193 pr=53 pw=53 time=0 us)
             0          0          0   HASH JOIN SEMI (cr=193 pr=53 pw=53 time=0 us cost=61 size=258 card=2)
             1          1          1    VIEW  (cr=192 pr=53 pw=53 time=0 us cost=59 size=232 card=2)
             1          1          1     HASH UNIQUE (cr=192 pr=53 pw=53 time=0 us cost=59 size=424 card=2)
             1          1          1      WINDOW SORT (cr=192 pr=53 pw=53 time=0 us cost=59 size=424 card=2)
             1          1          1       NESTED LOOPS OUTER (cr=192 pr=53 pw=53 time=0 us cost=57 size=424 card=2)
             1          1          1        NESTED LOOPS OUTER (cr=190 pr=53 pw=53 time=0 us cost=56 size=402 card=2)
             1          1          1         NESTED LOOPS OUTER (cr=190 pr=53 pw=53 time=0 us cost=55 size=324 card=2)
             1          1          1          NESTED LOOPS OUTER (cr=188 pr=51 pw=51 time=0 us cost=54 size=260 card=2)
             1          1          1           NESTED LOOPS OUTER (cr=186 pr=50 pw=50 time=0 us cost=53 size=216 card=2)
             1          1          1            FILTER  (cr=183 pr=49 pw=49 time=0 us)
          4204       4204       4204             HASH JOIN OUTER (cr=183 pr=49 pw=49 time=7235 us cost=52 size=134 card=2)
          2930       2930       2930              TABLE ACCESS FULL CI_ACCT (cr=61 pr=49 pw=49 time=52835 us cost=17 size=70296 card=2929)
          3343       3343       3343              TABLE ACCESS FULL CI_SA (cr=122 pr=0 pw=0 time=2196 us cost=34 size=143663 card=3341)
             1          1          1            TABLE ACCESS BY INDEX ROWID CI_SA_TYPE_CHAR (cr=3 pr=1 pw=1 time=0 us cost=1 size=41 card=1)
             1          1          1             INDEX UNIQUE SCAN XC323P0 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 57219)
             1          1          1           INDEX RANGE SCAN XM150P0 (cr=2 pr=1 pw=1 time=0 us cost=1 size=22 card=1)(object id 53071)
             0          0          0          TABLE ACCESS BY INDEX ROWID CI_PER_ID (cr=2 pr=2 pw=2 time=0 us cost=1 size=32 card=1)
             0          0          0           INDEX RANGE SCAN XM170P0 (cr=2 pr=2 pw=2 time=0 us cost=1 size=0 card=1)(object id 56875)
             0          0          0         TABLE ACCESS BY INDEX ROWID CI_ID_TYPE_L (cr=0 pr=0 pw=0 time=0 us cost=1 size=39 card=1)
             0          0          0          INDEX UNIQUE SCAN XC539P0 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 54936)
             1          1          1        VIEW  (cr=2 pr=0 pw=0 time=0 us cost=1 size=11 card=1)
             1          1          1         FILTER  (cr=2 pr=0 pw=0 time=0 us)
             1          1          1          INDEX UNIQUE SCAN XM174P0 (cr=2 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 56954)
             0          0          0    VIEW  VW_NSO_1 (cr=1 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
             0          0          0     NESTED LOOPS  (cr=1 pr=0 pw=0 time=0 us cost=2 size=56 card=1)
             0          0          0      TABLE ACCESS BY INDEX ROWID CI_DAR_USR (cr=1 pr=0 pw=0 time=0 us cost=1 size=30 card=1)
             0          0          0       INDEX SKIP SCAN XC691P0 (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 54233)
             0          0          0      INDEX FULL SCAN XC692P0 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)(object id 53083)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       1        0.00          0.00
      db file sequential read                         7        0.01          0.04
      db file scattered read                          7        0.02          0.06
      SQL*Net message from client                     1        0.00          0.00
    ********************************************************************************
    
    SQL ID: grwydz59pu6mc
    
    select text 
    from
     view$ where rowid=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.00       0.04          1          2          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.00       0.04          1          2          0           1
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=1 time=0 us cost=1 size=15 card=1)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       1        0.00          0.00
      SQL*Net message from client                     1        0.00          0.00
      db file sequential read                         1        0.04          0.04
    ********************************************************************************
    
    SQL ID: 5dt9w7dmjqp7a
    
    alter session set events '10046 trace name context off'
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          0          0           0
    
    Misses in library cache during parse: 0
    Parsing user id: 44  
    
    
    
    ********************************************************************************
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.02          0          0          0           0
    Fetch        1      0.01       0.12         53        193          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.01       0.15         53        193          0           0
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      SQL*Net message from client                     2        0.00          0.00
      db file sequential read                         7        0.01          0.04
      db file scattered read                          7        0.02          0.06
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.00       0.04          1          2          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.00       0.04          1          2          0           1
    
    Misses in library cache during parse: 0
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      db file sequential read                         1        0.04          0.04
    
        2  user  SQL statements in session.
        1  internal SQL statements in session.
        3  SQL statements in session.
    ********************************************************************************
    Trace file: input.trc
    Trace file compatibility: 11.01.00
    Sort options: default
    
           1  session in tracefile.
           2  user  SQL statements in trace file.
           1  internal SQL statements in trace file.
           3  SQL statements in trace file.
           3  unique SQL statements in trace file.
         140  lines in trace file.
           0  elapsed seconds in trace file.
  • 5. Re: Exam 1 (Tuning SQL)
    Think_dba Journeyer
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi All,

    I am applying a new job because I am very stressed at my current company :( . because they want to do everything under the SUN?
    Are your companies expecting the same of you? Is this really the trend for all companies now? That you be all around skilled worker?
    Even I applied as DBA position, they will throws me network problems, hardware error problems, applications error problems, Operationg system error problems,
    Programmer's error problems. etc, etc.

    I can not find anymore company like the first one I joined which has each own separate technical group.
    The have a network group, a developers group, a DBA group, a Systems Admin Group, and Hardware Engineers.
    Maybe this kind of setup will burn down the financial strength of this company?

    Can you share how do you fair in your company? Are you happy and contented with you current technical job?
    Or do you have your own service provider tech support company?
    I understand everyone need change and can not stay long in one company or maybe will not be "contented" forever?


    Thanks a lot,

    Edited by: KinsaKaUy? on 17-Apr-2012 19:31
    DBA is a very specific and critical position which require a person is to be dedicately assigned to database activities.

    I think u should not be "Jack of all trades, master of none".

    Specialize in one field..
  • 6. Re: Exam 1 (Tuning SQL)
    CharlesHooper Expert
    Currently Being Moderated
    KinsaKaUy? wrote:
    Very well said raj tanks :)


    While applying a new job, I was given a actual scenario exam:

    Given: a sql query
    ===========
    (snip)
    And given its trace file:
    =============
    Trace file c:\oracle\admin\truibmd2\bdump\diag\rdbms\truibmd2\truibmd2\trace\truibmd2_ora_4968_ISS-5045.trc
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Windows Server 2003 Version V5.2 Service Pack 2
    CPU                 : 4 - type 586, 1 Physical Cores
    Process Affinity    : 0x00000000
    Memory (Avail/Total): Ph:12447M/15359M, Ph+PgF:14136M/17164M, VA:1311M/2047M
    Instance name: truibmd2
    Redo thread mounted by this instance: 1
    Oracle process number: 58
    Windows thread id: 4968, image: ORACLE.EXE (SHAD)
    
    
    *** 2012-04-16 13:17:24.131
    *** SESSION ID:(138.4242) 2012-04-16 13:17:24.131
    *** CLIENT ID:() 2012-04-16 13:17:24.131
    *** SERVICE NAME:(TRUIBMD2) 2012-04-16 13:17:24.131
    *** MODULE NAME:(SQL*Plus) 2012-04-16 13:17:24.131
    *** ACTION NAME:() 2012-04-16 13:17:24.131
    Question:
    1. Based on the trace file, is the SQL performing good? performing Bad? or can not be determined at all, and why.
    How long were you given to provide an answer? Were you provided a computer to use, or just the information in printed form?

    The more that I look at this question, the more I like the question as a valid interview question for a DBA. Here are a couple of reasons:
    * It tests how you will approach unfamiliar problems if you encounter the problem once hired - it may not be this exact question, but this may be similar to a real problem that was faced by the company's DBAs. Do you panic and say that it cannot be solved, do you say that the job belongs to someone else, do you say that this is no point in looking at the raw trace file, or do you dive into the problem and think about what could happen if... (yes, there is something here, but I do not want to spoil the fun for other candidates that may face this exact problem).
    * It determines in part what types of material you have read to further enhance your knowledge of Oracle Database.
    * It determines whether or not you recognize the potential problems that may be associated with specific Oracle Database releases (unpatched and feeling ANSI?)
    * It determines whether or not you recognize differences in predicated cardinality and the actual number of rows returned, and how that may affect the performance outcome. Is it better to filter early or filter late, and does that apply to this situation?
    * Is there a DISTINCT possibility that the ROWNUM function may allow an arbitrary set of rows to be returned, possibly dependent on the value of the OPTIMIZER_FEATURES_ENABLE parameter?
    * It determines whether or not you follow logic or magic when troubleshooting problems.

    The statements made by Nikolay are valid. However, consider what the interviewer may have tried to learn about you from the exercise. Understanding performance tuning should fall under the umbrella of Oracle Database DBA.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re: Exam 1 (Tuning SQL)
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Charles,

    agree with you 100% -- that's one of the best Oracle interview questions I've seen or heard (including questions asked to me, by me or just found on the internet). I like not just the question itself, but rather the general idea behind it -- using real-life scenarios instead of testing book knowledge.

    I wish OCP exams were like that...

    Best regards,
    Nikolay
  • 8. Re: Exam 1 (Tuning SQL)
    713555 Pro
    Currently Being Moderated
    >
    I wish OCP exams were like that...

    >

    I disagree here, .... someone would release the practicals to the exam sites 20 minutes after they came out.

    If you gave me a guy who could deal with the above answer the correct way as charles laid out, I would take him over an OCP who could NOT answer it. I have interviewed OCPs who did not have 1 day real life experience.
  • 9. Re: Exam 1 (Tuning SQL)
    FahdMirza Oracle ACE
    Currently Being Moderated
    Scenario based questions are two-edged sword. If they are the in the hand of right interviewer, they are awesome. The answers to the scenario based questions may be vague, but objectives should be very much clear to the interviewer. As we know that there could be many right answers to one questions in technology, and there are many ways to accomplish one task in Oracle, interviewer must be looking to gauge the approach of interviewee and his/her depth of knowledge. One trait I always find cherishable and rare in DBAs is fearlessness. Fearlessness to face unknown problems and strive to resolve them by giving 100%.
  • 10. Re: Exam 1 (Tuning SQL)
    839439 Pro
    Currently Being Moderated
    Hi

    This is really a very good interview question and this tests the real environment dba skills . The answer given by Nikolay and charles are superb.... anywat thnks for posting good question ....


    Good Luck :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points