Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Parsing a SQL statement

    I was trying to show that a SQL statement was parsed only the first time it is used in Oracle, as long as the prior statement was still in the Shared Pool. The following shows that it's not, I think. Since the parse count is = 1 in both cases, doesn't that mean that it's re-parsed?

    Code:
    [oracle@ora4 udump]$ sqlplus forbesc@dv
    
    SQL*Plus: Release 10.1.0.3.0 - Production on Wed Aug 23 10:32:57 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    Enter password: 
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> begin
      2  dbms_monitor.session_trace_enable(waits=>true, binds=>true);
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from na_division where 1=1;
    
    DI DIVISION_DESCRIPTION                DIVISI PHO PHO PHON PHON CREATE_D
    -- ----------------------------------- ------ --- --- ---- ---- --------
    CREATE_ID                      MOD_DATE MOD_ID
    ------------------------------ -------- ------------------------------
    00                                            000 000 0000 0000 12/21/91
    KLW                            12/29/91 KLW
    ...
    12 rows selected.
    
    SQL> exit
    
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    [oracle@ora4 udump]$ sqlplus forbesc@dv
    
    SQL*Plus: Release 10.1.0.3.0 - Production on Wed Aug 23 10:33:43 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    Enter password: 
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> begin
      2  dbms_monitor.session_trace_enable(waits=>true, binds=>true);
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from na_division where 1=1;
    
    DI DIVISION_DESCRIPTION                DIVISI PHO PHO PHON PHON CREATE_D
    -- ----------------------------------- ------ --- --- ---- ---- --------
    CREATE_ID                      MOD_DATE MOD_ID
    ------------------------------ -------- ------------------------------
    00                                            000 000 0000 0000 12/21/91
    KLW                            12/29/91 KLW
    ...
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    [oracle@ora4 udump]$ tkprof dev_ora_20019.trc dev_ora_20019.tkprof
    
    TKPROF: Release 10.1.0.3.0 - Production on Wed Aug 23 10:34:52 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    
    [oracle@ora4 udump]$ more dev_ora_20019.tkprof
    
    TKPROF: Release 10.1.0.3.0 - Production on Wed Aug 23 10:34:52 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    ...
    
    select * 
    from
     na_division where 1=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        2      0.00       0.00          0          4          0          12
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          4          0          12
    
    ...
    
    
    [oracle@ora4 udump]$ tkprof dev_ora_20149.trc dev_ora_20149.tkprof
    
    TKPROF: Release 10.1.0.3.0 - Production on Wed Aug 23 10:35:10 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    [oracle@ora4 udump]$ more dev_ora_20149.tkprof
    
    TKPROF: Release 10.1.0.3.0 - Production on Wed Aug 23 10:35:10 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    ...
    
    select * 
    from
     na_division where 1=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        2      0.00       0.00          0          4          0          12
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          4          0          12
    -cf

  2. #2
    Join Date
    May 2006
    Posts
    132
    The first one was probably a hard parse and the second one was a soft parse.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    How do you tell the difference? Is there another method for discovering when there's a soft parse and when there's a hard parse?
    -cf

  4. #4
    Join Date
    May 2006
    Posts
    132
    Within the TKPROF you should see "Misses in library cache during parse". This count will indicate how many are hard parses.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    here is the area in tkprof to look at:
    Code:
    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        9      0.22       0.41        173       2971          0         127
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       11      0.22       0.41        173       2971          0         127
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 27
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •