Monday Feb 14, 2011

Oracle Solaris Studio C/C++: Tuning iropt for inline control

It is desirable to inline as many hot routines as possible to reduce the runtime overhead of CPU intensive applications. In general, compilers go by their own rules when to inline and when to not inline a routine. This blog post is intended to introduce some of the not widely known (or used) compiler internal flags to tweak the pre-defined rules of compiler.

Consider the following trivial C code:


% cat inline.c

#include <stdio.h>
#include <stdlib.h>

inline void freememory(int \*ptr)
{
        free(ptr);
}

extern inline void swapdata(int \*ptr1, int \*ptr2)
{
        int \*temp;

        temp = (int \*) malloc (sizeof (int));
        printf("\\nswapdata(): before swap ->");

        \*temp = \*ptr1;
        \*ptr1 = \*ptr2;
        \*ptr2 = \*temp;

        printf("\\nswapdata(): after swap ->");

        free (temp);
}

inline void printdata(int \*ptr)
{
        printf("\\nAddress = %x\\tStored Data = %d", ptr, \*ptr);
}

inline void storedata(int \*ptr, int data)
{
        \*ptr = data;
}

inline int \*getintptr()
{
        int \*ptr;
        ptr = (int \*) malloc (sizeof(int));
        return (ptr);
}

inline void AllocLoadAndSwap(int val1, int val2)
{
        int \*intptr1, \*intptr2;

        intptr1 = getintptr();
        intptr2 = getintptr();
        storedata(intptr1, val1);
        storedata(intptr2, val2);
        printf("\\nBefore swapping .. ->");
        printdata(intptr1);
        printdata(intptr2);
        swapdata(intptr1, intptr2);
        printf("\\nAfter swapping .. ->");
        printdata(intptr1);
        printdata(intptr2);
        freememory(intptr1);
        freememory(intptr2);
}

inline void InitAllocLoadAndSwap()
{
        printf("\\nSnapshot 1\\n___________");
        AllocLoadAndSwap(100, 200);
        printf("\\n\\nSnapshot 2\\n___________");
        AllocLoadAndSwap(435, 135);
}

int main() {
        InitAllocLoadAndSwap();
        return (0);
}

By default auto inlining is turned off in Oracle Studio compilers; and to turn it on, the code must be compiled with -O4 or higher optimization. This example attempts to hint the compiler to inline all the routines with the help of inline keyword. Note that inline keyword is a suggestion/request for the compiler to inline the function. However there is no guarantee that compiler honors the suggestion/request. Just like everything else in the world, compiler has a pre-defined set of rules. And based on those rules, it tries to do its best as long as those rules are not violated. If the compiler chooses to inline a routine, the function body will be expanded at all the call sites (just like a macro expansion).

When this code is compiled with Oracle Studio C compiler, it doesn't print any diagnostic information on stdout or stderr - so, using nm or elfdump is one way to find what routines are inlined and what routines are not.


% cc -xO3 -c inline.c
% nm inline.o

inline.o:

[Index]   Value      Size      Type  Bind  Other Shndx   Name

[4]     |         0|       0|NOTY |LOCL |0    |3      |Bbss.bss
[6]     |         0|       0|NOTY |LOCL |0    |4      |Ddata.data
[8]     |         0|       0|NOTY |LOCL |0    |5      |Drodata.rodata
[16]    |         0|       0|NOTY |GLOB |0    |ABS    |__fsr_init_value
[14]    |         0|       0|FUNC |GLOB |0    |UNDEF  |InitAllocLoadAndSwap
[1]     |         0|       0|FILE |LOCL |0    |ABS    |inline.c
[15]    |         0|      20|FUNC |GLOB |0    |2      |main

From this output, we can see that InitAllocLoadAndSwap() is not inlined yet there is no information as to why this function is not inlined.

Compiler commentary with er_src

To get some useful diagnostic information, Oracle Studio compiler collection offers a utility called er_src. When the source code was compiled with debug flag (-g or -g0), er_src can print the compiler commentary. However since compiler does auto inlining only at O4 or later optimization levels, unfortunately compiler commentary for inlining is not available at O3 optimization level.

iropt's inlining report

"iropt" is the global optimizer in Oracle Solaris Studio compiler collection. Inlining will be taken care by iropt. It performs inlining for callees in the same file unless compiler options for cross file optimizations such as -xipo, -xcrossfile are specified on compile line. Some of the iropt options can be used to control inlining heuristics. These options have no dependency on the optimization level.

Finding the list of iropt phases and the supported options

Oracle Studio C/C++ compilers on SPARC support a variety of options for function inline control. -help displays the list of supported flags/options.


% CC -V
CC: Sun C++ 5.9 SunOS_sparc Patch 124863-01 2007/07/25

% cc -V
cc: Sun C 5.9 SunOS_sparc Patch 124867-01 2007/07/12

% iropt -help

  \*\*\*\*\*\*  General Usage Information about IROPT  \*\*\*\*\*\*

To get general help information about IROPT, use -help
To list all the optimization phases in IROPT, use -phases
To get help on a particular phase, use -help=phase
To turn on phases, use -A++...+
To turn off phases, use -R++...+
To use phase-specific flags, use -A:

% iropt -phases


  \*\*\*\*\*\* List of Optimization Phases in IROPT \*\*\*\*\*\*

    Phase Name          Description
-------------------------------------------------------------
bitfield	     Bitfield transformations
iv		     Strength Reduction
loop		     Loop Invariant Code Motion
cse		     Common Subexpression Elimination
copy		     Copy Propagation
const		     Const Propagation and Folding
reg		     Virtual Register Allocation
unroll		     Data Dependence Loop Unrolling
merge		     Merge Basic Blocks
reassoc		     Reconstruction of associative and/or distributive expressions
composite_breaker	     
tail		     Tail Recursion Optimization
rename		     Scalar Rename
reduction	     
mvl		     Two-version loops for parallelization
loop_dist	     Loop Distribution
ivsub		     Induction Variables Substitution: New Algorithm
ddint		     Loop Interchange
fusion		     Loop Fusion
eliminate	     Scalar Replacement on def-def and def-use
private		     Private Array Analysis
scalarrep	     Scalar Replacement for use-use
tile		     Cache Blocking
ujam		     Register Blocking
ddrefs		     Loop Invariant Array References Moving
invcc		     Invariant Conditional Code Motion
sprof		     Synthetic Profiling
restrict_g	     Assume global pointers as restrict
dead		     Dead code elimination
pde		     Partial dead code elimination
reassoc2	     loop invariant reassociative tranfsformations
distr		     distributive reassociative tranfsformations
height2		     tree height reassociative reduction
ansi_alias	     Apply ANSI Aliase Rules to Pointer References
perfect		     
yas		     Scalar Replacement for reduction arrays
pf		     Prefetch Analysis
cond_elim	     Conditional Code Elimination
vector		     Vectorizing Some Intrinsics Functions Calls in Loops
whole		     Whole Program Mode
bopt		     Branches Reordering based on Profile Data
invccexp	     Invariant Conditional Code Expansion
bcopy		     Memcpy and Memset Transformations
ccse		     Cross Iteration CSE
data_access	     Array Access Regions Analysis
ipa		     Interprocedual Analysis
contract	     Array Contraction Analysis
symbol		     Symbolic Analysis
ppg2		     optimistic strategy of constant propagation
parallel	     Parallelization
pcg		     Parallel Code Generator
lazy		     Lazy Code Motion
region		     Region-based Optimization
loop_peeling	     Loop Peeling
loop_reform	     Loop Reformulation
loop_shifting	     Loop Shifting
loop_collapsing	     Loop Collapsing
memopt		     Merge memory allocations
inline		     IPA-based inlining phase
clone		     Routine cloning phase
norm_ir		     clean-up and normalize ir
ipa_ppg		     interprocedural constant propagation
sr		     Strength reduction (new)
ivsub3		     Induction Variable Substitution
icall_opt	     indirect call optimization
cha		     Class Hierarchy Analysis
ippt		     Interprocedual pointer tracking
reverse_invcc	     reverse invariant condition code hoisting
crit		     Critical path optimisations
loop_norm	     loop normalization
loop_unimodular	     loop unimodular transformation
scalar_repl	     Scalar Replacement
loop_bound	     Redundant Loop Bound Checking Elimination
loop_condition	     Invariant Loop Bound Checking Hoisting
memopt_pattern	     Memory Access Optimization
loop_improvement	     Loop structure improvement by code specialization
pbranch_opt	     C++ Java Pbranch Optimizations
norm_ldst	     short ld/st normalisation
micro_vector	     Micro vectorization for x86
ipa_symbol_ppg	     interprocedural symbolic analysis
optinfo		     Compile-time information about loop and inlining transformations
vp		     Value profiling and code specialization
pass_ti		     Pass IR type trees to the backend
fully_unroll	     Fully Loop Unrolling
builtin_opt	     Builtin Optimization


% iropt -help=inline

NAME
    inline - Qoption for IPA-based inlining phase.

SYNOPSIS
    -Ainline[:][:]:...[:] - turn on inline.
    -Rinline                             - turn off inline

DESCRIPTION
    inline is on by default now. -Ainline turns it on.
    -Rinline turns it off. 
    
    NOTE: the following is a brief description of the old inliner qoptions
          1. Old inliner qoptions that do not have equivalent 
             options in the new inliner--avoid to use them later: 
             -Ml -Mi -Mm -Ma -Mc -Me -Mg -Mw -Mx -Mx -MC -MS 

          2. Old inliner qoptions that have equivalent option 
             in the new inliner--use the new options later: 
             Old options     new options 
                -Msn          recursion=n 
                -Mrn          irs=n      
                -Mtn          cs=n       
                -Mpn          cp=n       
                -MA           chk_alias  
                -MR           chk_reshape 
                -MI           chk_reshape=no 
                -MF           mi         
 
    The acceptable sub-options are:

      report[=n] - dump inlining report.
                  n=chain: 
                        show to-be-inlined call chains.
                  n=user_request: 
                        show the inlining status of user-requests.
                  n=0:  show inlined calls only.
                  n=1:  (default):  show both inlined and 
                        non-inlined calls and reasons for 
                        inlining/non-inlining.
                  n=2:  n=1 plus call id and node id
                  n=3:  show inlining summary only
                  n=4:  n=2 and iropt aborts after the 
                        inlining report is dumped out.
      cgraph     - dump cgraph.
      call_in_pragma[=no|yes]:
                 - call_in_pragma or call_in_pragma=yes: 
                   Inline a call that in the Parallel region 
		      into the original routine 
                 - call_in_pragma=no: (default) 
                   Don't inline a call that in the Parallel region
		      into the original routine 
      inline_into_mfunction[=no|yes]:(only for Fortran) 
		    - inline_into_mfunction or inline_into_mfunction=yes:(default) 
		      Inline a call into the mfunction if it is in the
		      Parallel Region
                 - inline_into_mfunction=no: 
                   Don't inline a call into the mfunction if it 
                   in the Parallel Region
NOTE: for other languages, if you specify inline_into_mfunction=yes 
	 The compiler will silently ignore this qoption. As a result, 
	 Calls in parallel region will still be inlined into pragma constructs
      rs=n       - max number of triples in inlinable routines.
                   iropt defines a routine as inlinable or not
                   based on this number. So no routines over 
                   this limit will be considered for inlining.
      irs=n      - max number of triples in a inlining routine,
                   including size increase by inlining its calls
      cs=n       - max number of triples in a callee. 
                   In general, iropt only inline calls whose 
                   callee is inlinable (defined by rs) AND 
                   whose callee size is not greater than n.
                   But some calls to inlinable routines are 
                   actually inlined because of other factors
                   such as constant actuals, etc. 
      recursion=n  
                 - max level of resursive call that is 
                   considered for inlining.
      cp=n       - minimum profile feedback counter of a call.
                   No call with counter less than this limit 
                   would be inlined.
      inc=n      - percentage of the total number of triples 
                   increased after inlining. No inlining over
                   this percentage. For instance, 'inc=30' 
                   means inlining is allowed to increase the 
                   total program size by 30%.
      create_iconf=:
      use_iconf=:
                   This creates/uses an inlining configuration.
                   The file lists calls and routines that are
                   inlined and routines that inline their calls.
                   Its format is:
                      air      /\* actual inlining routines \*/
                      r11 r12 r13 ...
                      r21 r22 r23 ...
                      .....
                      ari      /\* actual routines inlined \*/
                      r11 r12 r13 ...
                      r21 r22 r23 ...
                      .....
                      aci      /\* actual calls inlined \*/
                      (r11,c11) (r12,c12) (r13,c13) ...
                      (r21,c21) (r22,c22) (r23,c23) ...
                      .....
                   The numbers are callids (cxx) and nodeids(rxx) 
                   printed out when report=2. It is used for
                   debugging. The usual usage is to use
                   create_iconf= to create a config file.
                   then, comment (by preceding numbers line
                   with #) to disallow inlining for those 
                   calls or routines. For instance, 
                       aci
                       (2,3) (2,5) (2,6) (3,9)
                       (3,10) (6,4) (6,7) (7,6)
                       #(7,10) (8,21) (8,22)
                   with the above config file, calls whose
                   nodeids and callids are (7,10),(8,21) and 
                   (8,22) will not be inlined.

                   NOTE:for the aci part of the configure file,
                        in each pair (rij,cij), the parentheses
                        are not necessary, but the comma is necessary 
                        and there should not be any space between
                        rji and comma, comma and cij.
      do_inline=:
                 - guide inliner to do inlining for a given
                   routine only.
      mi:
                 - Do maximum inlining for given routines if do_inline
                   is used; otherwise, do maximum inlining for main routine.
                   (The inliner will not check inlining parameters.
      inline_level[=1|2|3]: 
                 - specify the level of inline: 
                     inline_level=1    basic inlining 
                     inline_level or inline_level=2    medium inlining (default) 
                     inline_level=3 or inline_level=4,5...   aggressive inlining 
      remove_ip[=no|yes]:
                 - remove_ip or remove_ip=yes:
                      removing inliningPlan after inlining.
                 - remove_ip=no [default]:
                      keep inliningPlan after inlining.
      chk_alias[=no|yes]:
                 - chk_alias or chk_alias=yes [default]:
                      Don't inline a call if inlining it causes
                      aliases among callee's formal arrays.
                 - chk_alias=no:
                      Ignore such checking.
      chk_reshape[=no|yes]:
                 - chk_reshape or chk_reshape=yes [default]:
                      Don't inline a call if its array argument
                      is reshaped between caller and callee.
                 - chk_reshape=no:
                      Ignore such checking.
      chk_mismatch[=no|yes]:
                 - chk_mismatch or chk_mismatch=yes [default]:
                      Don't inline a call if any real argument
                      mismatches with its formal in type.
                 - chk_mismatch=no:
                      Ignore such checking.
      do_chain[=no|yes]:
                 - do_chain or do_chain=yes [default]:
                      Enable inlining for call chains.
                 - do_chain=no:
                      Disable inlining for call chains.
      callonce[=no|yes]:
                 - callonce=no [default]:
                      Disable inlining a routine that is
                      called only once.
                 - callonce or callonce=yes:
                      Enable inlining a routine that is
                      called only once.
      icall_recurse[=no|yes]:
                 - icall_recurse=no [default]:
                      Disable recursive inlining of indirect
                      and virtual call sites
                 - icall_recurse=yes:
                      Enable recursive inlining of indirect
                      and virtual call sites
      formal_dbgsym[=no|yes]: (default = no)
                 - Specify to preserve the debug information for
                   formal parameter of inlined funcion

Some of these options can be used to get all the diagnostic information from compile time. Especially the sub-option (report) to -Ainline is useful in obtaining the inlining report. To pass special flags to iropt, specify -W2,<option>:<sub-option> on compile line.

Here is an example.


% cc -xO3 -c -W2,-Ainline:report=2 inline.c

INLINING SUMMARY

   inc=400: percentage of program size increase.
   irs=4096: max number of triples allowed per routine after inlining.
   rs=450: max routine size for an inlinable routine.
   cs=400: call size for inlinable call.
   recursion=1: max level for inlining recursive calls.
   Auto inlining: OFF

   Total inlinable calls: 14
   Total inlined calls: 36
   Total inlined routines: 7
   Total inlinable routines: 7
   Total inlining routines: 3
   Program size: 199
   Program size increase: 744
   Total number of call graph nodes: 11

   Notes for selecting inlining parameters

    1. "Not inlined, compiler decision":
       If a call is not inlined by this reason, try to
       increase inc in order to inline it by
          -Qoption iropt -Ainline:inc=  for FORTRAN, C++
          -W2,-Ainline:inc=  for C

    2. "Not inlined, routine too big after inlining":
       If a call is not inlined by this reason, try to
       increase irs in order to inline it by
          -Qoption iropt -Ainline:irs=  for FORTRAN, C++
          -W2,-Ainline:irs=  for C

    3. "Not inlined, callee's size too big":
       If a call is not inlined by this reason, try to
       increase cs in order to inline it by
          -Qoption iropt -Ainline:cs=  for FORTRAN, C++
          -W2,-Ainline:cs=  for C

    4. "Not inlined, recursive call":
       If a call is not inlined by this reason, try to
       increase recursion level in order to inline it by
          -Qoption iropt -Ainline:recrusion=  for FORTRAN, C++
          -W2,-Ainline:recrusion=  for C

    5. "Routine not inlined, too many operations":
       If a routine is not inlinable by this reason, try to
       increase rs in order to make it inlinable by
          -Qoption iropt -Ainline:rs=  for FORTRAN, C++
          -W2,-Ainline:rs=  for C


ROUTINES NOT INLINABLE:

 main [id=7] (inline.c)
   Routine not inlined, user requested

CALL INLINING REPORT:

 Routine: freememory [id=0] (inline.c)
  Nothing inlined.

 Routine: swapdata [id=1] (inline.c)
  Nothing inlined.

 Routine: printdata [id=2] (inline.c)
  Nothing inlined.

 Routine: storedata [id=3] (inline.c)
  Nothing inlined.

 Routine: getintptr [id=4] (inline.c)
  Nothing inlined.

 Routine: AllocLoadAndSwap [id=5] (inline.c)
   getintptr [call_id=8], line 46: Auto inlined
   getintptr [call_id=9], line 47: Auto inlined
   storedata [call_id=10], line 48: Auto inlined
   storedata [call_id=11], line 49: Auto inlined
   printdata [call_id=13], line 51: Auto inlined
   printdata [call_id=14], line 52: Auto inlined
   swapdata [call_id=15], line 53: Auto inlined
   printdata [call_id=17], line 55: Auto inlined
   printdata [call_id=18], line 56: Auto inlined
   freememory [call_id=19], line 57: Auto inlined
   freememory [call_id=20], line 58: Auto inlined

 Routine: InitAllocLoadAndSwap [id=6] (inline.c)
   AllocLoadAndSwap [call_id=22], line 64: Not inlined, compiler decision
     (inc limit reached. See INLININING SUMMARY)
   AllocLoadAndSwap [call_id=24], line 66: Auto inlined
     swapdata [call_id=15], line 53: Auto inlined
     getintptr [call_id=8], line 46: Auto inlined
     getintptr [call_id=9], line 47: Auto inlined
     printdata [call_id=13], line 51: Auto inlined
     printdata [call_id=14], line 52: Auto inlined
     printdata [call_id=17], line 55: Auto inlined
     printdata [call_id=18], line 56: Auto inlined
     freememory [call_id=19], line 57: Auto inlined
     freememory [call_id=20], line 58: Auto inlined
     storedata [call_id=10], line 48: Auto inlined
     storedata [call_id=11], line 49: Auto inlined

 Routine: main [id=7] (inline.c)
   InitAllocLoadAndSwap [call_id=25], line 70: Auto inlined
     AllocLoadAndSwap [call_id=22], line 64: Not inlined, compiler decision
       (inc limit reached. See INLININING SUMMARY)
     AllocLoadAndSwap [call_id=24], line 66: Auto inlined
       swapdata [call_id=15], line 53: Auto inlined
       getintptr [call_id=8], line 46: Auto inlined
       getintptr [call_id=9], line 47: Auto inlined
       printdata [call_id=13], line 51: Auto inlined
       printdata [call_id=14], line 52: Auto inlined
       printdata [call_id=17], line 55: Auto inlined
       printdata [call_id=18], line 56: Auto inlined
       freememory [call_id=19], line 57: Auto inlined
       freememory [call_id=20], line 58: Auto inlined
       storedata [call_id=10], line 48: Auto inlined
       storedata [call_id=11], line 49: Auto inlined

The above report shows the threshold values being used while making decisions, all the routines and information about whether a call to any function is inlined; if not, the reason for not inlining it, and some suggestions on how to make it succeed. This is cool stuff.

Going back to the example: based on the report, the compiler is trying to inline all the routines as long as the program size doesn't go beyond 400% of the original size (ie., without inlining). Unfortunately AllocLoadAndSwap() went beyond the limits and as a result, compiler decides not to inline it. Fair enough. If we don't bother about the size of the binary and if we really want this routine inlined, one solution is to increase the value for inc in such a way that AllocLoadAndSwap()'s inclusion would fit into the newer limits.

eg.,


% cc -xO3 -c -W2,-Ainline:report=2,-Ainline:inc=650 inline.c
INLINING SUMMARY

   inc=650: percentage of program size increase.
   irs=4096: max number of triples allowed per routine after inlining.
   rs=450: max routine size for an inlinable routine.
   cs=400: call size for inlinable call.
   recursion=1: max level for inlining recursive calls.
   Auto inlining: OFF

   Total inlinable calls: 14
   Total inlined calls: 60
   Total inlined routines: 7
   Total inlinable routines: 7
   Total inlining routines: 3
   Program size: 199
   Program size increase: 1260
   Total number of call graph nodes: 11

   Notes for selecting inlining parameters

    ... skip ... (see prev reports for the text that goes here)

ROUTINES NOT INLINABLE:

 main [id=7] (inline.c)
   Routine not inlined, user requested


CALL INLINING REPORT:

 Routine: freememory [id=0] (inline.c)
  Nothing inlined.

 Routine: swapdata [id=1] (inline.c)
  Nothing inlined.

 Routine: printdata [id=2] (inline.c)
  Nothing inlined.

 Routine: storedata [id=3] (inline.c)
  Nothing inlined.

 Routine: getintptr [id=4] (inline.c)
  Nothing inlined.

 Routine: AllocLoadAndSwap [id=5] (inline.c)
   getintptr [call_id=8], line 46: Auto inlined
   getintptr [call_id=9], line 47: Auto inlined
   storedata [call_id=10], line 48: Auto inlined
   storedata [call_id=11], line 49: Auto inlined
   printdata [call_id=13], line 51: Auto inlined
   printdata [call_id=14], line 52: Auto inlined
   swapdata [call_id=15], line 53: Auto inlined
   printdata [call_id=17], line 55: Auto inlined
   printdata [call_id=18], line 56: Auto inlined
   freememory [call_id=19], line 57: Auto inlined
   freememory [call_id=20], line 58: Auto inlined

 Routine: InitAllocLoadAndSwap [id=6] (inline.c)
   AllocLoadAndSwap [call_id=22], line 64: Auto inlined
     swapdata [call_id=15], line 53: Auto inlined
     getintptr [call_id=8], line 46: Auto inlined
     getintptr [call_id=9], line 47: Auto inlined
     printdata [call_id=13], line 51: Auto inlined
     printdata [call_id=14], line 52: Auto inlined
     printdata [call_id=17], line 55: Auto inlined
     printdata [call_id=18], line 56: Auto inlined
     freememory [call_id=19], line 57: Auto inlined
     freememory [call_id=20], line 58: Auto inlined
     storedata [call_id=10], line 48: Auto inlined
     storedata [call_id=11], line 49: Auto inlined
   AllocLoadAndSwap [call_id=24], line 66: Auto inlined
     swapdata [call_id=15], line 53: Auto inlined
     getintptr [call_id=8], line 46: Auto inlined
     getintptr [call_id=9], line 47: Auto inlined
     printdata [call_id=13], line 51: Auto inlined
     printdata [call_id=14], line 52: Auto inlined
     printdata [call_id=17], line 55: Auto inlined
     printdata [call_id=18], line 56: Auto inlined
     freememory [call_id=19], line 57: Auto inlined
     freememory [call_id=20], line 58: Auto inlined
     storedata [call_id=10], line 48: Auto inlined
     storedata [call_id=11], line 49: Auto inlined

 Routine: main [id=7] (inline.c)
   InitAllocLoadAndSwap [call_id=25], line 70: Auto inlined
     AllocLoadAndSwap [call_id=22], line 64: Auto inlined
       swapdata [call_id=15], line 53: Auto inlined
       getintptr [call_id=8], line 46: Auto inlined
       getintptr [call_id=9], line 47: Auto inlined
       printdata [call_id=13], line 51: Auto inlined
       printdata [call_id=14], line 52: Auto inlined
       printdata [call_id=17], line 55: Auto inlined
       printdata [call_id=18], line 56: Auto inlined
       freememory [call_id=19], line 57: Auto inlined
       freememory [call_id=20], line 58: Auto inlined
       storedata [call_id=10], line 48: Auto inlined
       storedata [call_id=11], line 49: Auto inlined
     AllocLoadAndSwap [call_id=24], line 66: Auto inlined
       swapdata [call_id=15], line 53: Auto inlined
       getintptr [call_id=8], line 46: Auto inlined
       getintptr [call_id=9], line 47: Auto inlined
       printdata [call_id=13], line 51: Auto inlined
       printdata [call_id=14], line 52: Auto inlined
       printdata [call_id=17], line 55: Auto inlined
       printdata [call_id=18], line 56: Auto inlined
       freememory [call_id=19], line 57: Auto inlined
       freememory [call_id=20], line 58: Auto inlined
       storedata [call_id=10], line 48: Auto inlined
       storedata [call_id=11], line 49: Auto inlined

From the above output we can conclude that AllocLoadAndSwap() was inlined by the compiler when we let the program size to increase by 650%.

Notes:

  • Multiple iropt options separated by a comma (,) can be specified after -W2
    eg., -W2,-Ainline:report=2,-Ainline:inc=650

  • For C++ code, use -Qoption to specify iropt options.
    eg., -Qoption iropt -Ainline:report=2
    -Qoption iropt -Ainline:report=2,-Ainline:inc=650

  • Inlining those functions whose function call overhead is large relative to the routine code improves performance. Improvement is the result of elimination of the function call, stack frame manipulation and the function return

  • Even though inlining may increase the runtime performance of an application, do not try to inline too many functions. Inline only those functions (from profiling data) that could benefit from inlining

  • In general, compiler threshold values are good enough for inlining the functions. Use iropt's options only if some very hot routines couldn't make it due to some reason. Turn on auto inlining with -xO4 option

  • Inline functions increase build time and program sizes. Sometimes it is possible that some of the very large routines (when inlined) may not fit into processor's cache and may lead to poor performance mainly due to the increased cache miss rate

ALSO SEE
Oracle Solaris Studio: Advanced Compiler Options for Performance

(Original blog post is at:
http://technopark02.blogspot.com/2005/11/sun-studio-cc-tuning-iropt-for-inline.html)

Wednesday Dec 15, 2010

Oracle Solaris Studio C/C++ : Inline Functions

Function inlining improves runtime performance by replacing a call to a function with the body of the function. This eliminates the overhead of jumping to and returning from a subroutine. An additional advantage is that placing the function code "inline" exposes it to further optimization.

The C++ compiler performs two kinds of inlining: front-end (parser) and back-end (code generator). The C and Fortran compilers support only back-end inlining. The same code generator is used for all compilers on a platform. The C++ compiler performs front-end inlining because it can use its knowledge of C++ semantics to eliminate extra copies of objects among other things that the code generator would not be able to do. The back-end inlining does not depend on the programming language.

The C++ compiler front end attempts to inline a function declared inline. If the function is too large, a warning message will be printed on stdout when +w or +w2 ("more warnings") option is used. The +d option prevents the front end from attempting to inline any function. The -g option also turns off front-end inlining. The -O options do not affect front-end inlining. C++ front end turns off function inlining when a combination of -g and -O options are specified on compile line. It may lead to loss of runtime performance. To avoid this, it is suggested to use -g0 instead of -g. C does not have -g0 so use -g instead.

With an optimization level of -O4 or higher, the code generator examines all functions independent of how they were declared in source code and replace function calls with inline code where it thinks the replacement will be beneficial. No diagnostic messages are displayed about back-end inlining. The +d option has no impact on back-end inlining.

Couple of trivial examples to demonstrate the compiler behavior.

eg.,

% cat inline.c

#include <stdio.h>

inline void printmespam() {
        printf("print me"); printf("print me"); 
        printf("print me"); printf("print me");
        printf("print me"); printf("print me"); 
        printf("print me"); printf("print me");
        printf("print me"); printf("print me"); 
        printf("print me"); printf("print me");
        printf("print me"); printf("print me"); 
        printf("print me"); printf("print me");
        printf("print me");
}

inline void printme() {
        printf("print me");
}

int main() {
        printme();
        printmespam();
        return (0);
}

% CC +w2 inline.c
"inline.c", line 17: Warning: "printmespam()" is too large and will not be expanded inline.
1 Warning(s) detected.

In the above example, printmespam() was not inlined by the compiler though it was explicitly requested to do so. The keyword inline is only a request but not a guarantee.

How to check if a routine is inlined?

A: Check the symbol table of the executable. If the routine doesn't show up in the symbol table, it is an indication that the missing routine is inlined. This is because the compiler might have replaced the function call with the body of the function.

% elfdump -CsN.symtab a.out | grep printme
      [85]  0x00010e68 0x000000a4  FUNC GLOB  D    0 .text       void printmespam()

printme is inlined where as printmespam is not.

Another way is to check the assembly code being generated. To generate the assembly code, compile the code with -S option of Oracle Solaris Studio compilers.

eg.,

% cat swap.c

void swap (int \*a, int \*b) {
        int t = \*a;
        \*a = \*b;
        \*b = t;
}

int main (int argc, char \*argv) {
        int x = 5, y = 2;
        swap (&x,&y);
        return (0);
}

% CC +w2 -S swap.c

% grep call swap.s
        call    __1cEswap6Fpi0_v_

% dem __1cEswap6Fpi0_v_
__1cEswap6Fpi0_v_ == void swap(int\*,int\*)

From the above output(s), it is clear that the function is not inlined since an assembly instruction has been generated with a call to routine swap. Let's add the keyword inline to the function definition.

% cat swap.c

inline void swap (int \*a, int \*b) {
        int t = \*a;
        \*a = \*b;
        \*b = t;
}

int main (..) { .. }

% CC +w2 -S swap.c
% grep call swap.s
%

After instructing the compiler to inline the routine swap, the compiler was able to inline the function in main() mainly because it was not too big. That is why no assembly instruction has been generated with a call to swap.

Another example to demonstrate slightly different behavior.

eg.,

% cat inline2.c

#include <stdio.h>

int globvar = 0;

inline void setglob () {
        globvar= 25;
}

int main (int argc, char \*argv[]) {
        globvar= 5;
        setglob ();
        printf ("Now global variable holds %d\\n", globvar);
        return (0);
}

% cc -o test inline2.c
Undefined                       first referenced
 symbol                             in file
setglob                             inline2.o
ld: fatal: Symbol referencing errors. No output written to test

The above code violates a C rule. An inline definition without an extern directive does not create an instance of the function. Calling the function has undefined results. The fix is to declare setglob with external linkage as shown below.

C++ has a different rule for inline functions. The compiler is required to figure out how to generate a defining instance if one is needed without any special action by the programmer. So the above example has valid C++ code but it is valid in C.

 
% cat inline2.c

#include <stdio.h>

int globvar = 0;

extern inline void setglob () {
        globvar= 25;
}

int main (..) { .. }

% cc inline2.c
% ./a.out
Now global variable holds 25

Notes:

  1. Do not use if(0) in an inline function. Use #if 0 instead

  2. Do not put a return statement in the "then" part of an "if" statement. Rearrange the code to put the return in the "else" part or outside the if-else entirely.

Acknowledgements:
Steve Clamage

(Location of original blogpost:
http://technopark02.blogspot.com/2005/04/sun-cc-compilers-inlining-routines.html)

Sunday Nov 07, 2010

Instructions to Turn ON/OFF Hardware Prefetch on SPARC64 Systems

The hardware prefetch is ON by default on M-series servers such as M8000/M9000, M4000/M5000, M3000

The following excerpt is from a SPARC64 document:

Hardware speculatively issues the prefetch operation based on the prediction that there is high possibility to access to the following continuous address in the future, if there have been load accesses for a consecutive address.

Although this feature is designed to improve the performance of various workloads, due to the speculative nature, not all workloads may benefit with the default behavior. For example, in our experiments, we noticed 10+% improvement in CPU utilization while running some of the PeopleSoft workloads on M-series hardware with hardware prefetch turned off. Hence irrespective of the application/workload, the recommended approach is to conduct few experiments by running representative customer workloads on target M-series hardware with and without the hardware prefetch turned on.

Instructions to Turn On/Off Hardware Prefetch:

  1. Connect to the system Service Processor (XSCF)

    % ssh -l <userid> <host>
    
  2. Check the current prefetch mode by running the following command at XSCF> prompt

    XSCF> showprefetchmode
    
  3. Find the domain id of all mounted system boards (or skip to next step)

    XSCF> showboards -a
    
  4. Power-off all configured domains

    XSCF> poweroff -d <domainid> [OR]
    XSCF> poweroff -a
    

    From my experience, on larger systems with multiple domains configured, all domains must be powered off before the SP lets changing the prefetch mode. If someone has a correction to this information or better instruction that minimizes disruption, please let me know. I'd be happy to update these instructions.

  5. Wait until the domain(s) are completely powered off. Check the status by running showlogs command

    XSCF> showlogs power
    
  6. Change the prefetch mode to the desired value

    XSCF> setprefetchmode -s [on|off]
    
  7. Verify the prefetch mode

    XSCF> showprefetchmode
    
  8. Finally power-on all configured domains

    XSCF> poweron -d <domainid> [OR]
    XSCF> poweron -a
    
  9. Disconnect from SP, and wait for the OS to boot up

Note to Sun-Oracle customers:

If the default value of hardware prefetch is changed, please make sure to mention this in any service requests, bug reports, etc., that you may file with Oracle Corporation. Unfortunately none of the standard commands on Solaris report the status of hardware prefetch - so, providing this additional piece of information beforehand will help the person who is analyzing/diagnosing the case.

Friday May 28, 2010

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.


SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE in the DBMS_SHARED_POOL package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.

The syntax for the PURGE procedure is shown below.


procedure PURGE (
        name VARCHAR2, 
        flag CHAR DEFAULT 'P', 
        heaps NUMBER DEFAULT 1)

Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS 	 HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Note to Oracle 10g R2 Customers

The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.

Also see:

Tuesday Feb 02, 2010

Extracting DDL Statements from a PeopleSoft Data Mover exported DAT file

Case in hand: Given a PeopleSoft Data Mover exported data file (db or dat file), how to extract the DDL statements [from that data file] which gets executed as part of the Data Mover's data import process?

Here is a quick way to do it:

  1. Insert the SET EXTRACT statements in the Data Mover script (DMS) before the IMPORT .. statement.

    eg.,
    
    % cat /tmp/retrieveddl.dms
    
    ..
    SET EXTRACT OUTPUT /tmp/ddl_stmts.log;
    SET EXTRACT DDL;
    ..
    
    IMPORT \*;
    
    

    It is mandatory that the SET EXTRACT OUPUT statement must appear before any SET EXTRACT statements.

  2. Run the Data Mover utility with the modified DMS script as an argument.

    eg., OS: Solaris

    
    % psdmtx -CT ORACLE -CD NAP11 -CO NAP11 -CP NAP11 -CI people -CW peop1e -FP /tmp/retrieveddl.dms
    
    

    On successful completion, you will find the DDL statements in /tmp/retrieveddl.dms file.

Check chapter #2 "Using PeopleSoft Data Mover" in Enterprise PeopleTools x.xx PeopleBook: Data Management document for more ideas.

---

Updated 07/16/2010:

It appears PeopleSoft introduced a bug in Data Mover functionality on \*NIX platforms somewhere in PeopleTools 8.49 releases. If Data Mover repeatedly fails with "Error: Unable to open OUTPUT: " when extracting statements or actions using "SET EXTRACT", run the same DMS script on any Windows system as a workaround. For more information, check Oracle Support Document "E-DM: 'Error: Unable to open OUTPUT:' when attempting to extract DDL with Data Mover (Doc ID 887792.1)". From the same document: the fix to this bug on \*NIX platforms is targeted to fix after PeopleTools 8.51 release.

Wednesday Dec 23, 2009

Accessing MySQL Database(s) from StarOffice / OpenOffice.org Suite of Applications

This blog post is organized into two major sections and several sub-sections. The major sections focus on the tasks to be performed at the MySQL server and the \*Office client while the sub-sections talk about the steps to be performed in detail.

To show the examples in this exercise, we will be creating a new MySQL database user with user ID SOUSER. This new user will be granted read-only access to couple of tables in a MySQL database called ISVe. The database can be accessed from any host in the network. ben10.sfbay is the hostname of the MySQL server.

Tasks to be Performed at the MySQL Server

This section is intended only for the MySQL Server Administrators. If you are an end-user, skip ahead to Tasks to be Performed at the Client side.

Create a new MySQL user and grant required privileges.

eg.,

% mysql -u root -p
Enter password: \*\*\*\*\*
Server version: 5.1.25-rc-standard Source distribution
..

mysql> CREATE USER SOUSER IDENTIFIED BY 'SOUSER';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON \*.\* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '\*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE ISVe;
Database changed

mysql> show tables;
+--------------------------+
| Tables_in_ISVe           |
+--------------------------+
| CustomReport             | 
| CustomSQL                | 
| ISVeOldProjects          | 
| ISVeOrg                  | 
| ISVeProject              | 
| ISVeProjectExecution     | 
| ISVeProjectGoalAlignment | 
| ISVeProjectMiscInfo      | 
| ISVeProjectScoping       | 
| ISVeProjectStatus        | 
| ISVeProjects             | 
| ISVeProjectsVW           | 
| ISVeSearchLog            | 
| LastRefreshed            | 
+--------------------------+
14 rows in set (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeOldProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON \*.\* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '\*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
| GRANT SELECT ON `ISVe`.`ISVeOldProjects` TO 'SOUSER'@'%'                                              | 
| GRANT SELECT ON `ISVe`.`ISVeProjects` TO 'SOUSER'@'%'                                                 | 
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

Check the database connectivity and the accessibility from a remote location.


% mysql -h ben10.sfbay -D ISVe -u SOUSER -pSOUSER
Server version: 5.1.25-rc-standard Source distribution

mysql> show tables;
+-----------------+
| Tables_in_ISVe  |
+-----------------+
| ISVeOldProjects |
| ISVeProjects    |
+-----------------+
2 rows in set (0.03 sec)

mysql> select count(\*) from ISVeOldProjects;
+----------+
| count(\*) |
+----------+
|     2880 |
+----------+
1 row in set (0.04 sec)

mysql> select count(\*) from ISVeProjects;
+----------+
| count(\*) |
+----------+
|     4967 |
+----------+
1 row in set (0.33 sec)

mysql> delete from ISVeOldProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeOldProjects'

mysql> delete from ISVeProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeProjects'

mysql> quit
Bye


Tasks to be Performed at the Client side (End-User's Workstation)


StarOffice and OpenOffice suite of applications can access the MySQL Server using JDBC or native drivers.

MySQL Connector/J is a platform independent JDBC Type 4 driver that is developed specifically to connect to a MySQL database. Using Connector/J, it is possible to connect to almost any version of MySQL Server from any version of StarOffice or OpenOffice.org

Sun|MySQL recently developed a native MySQL driver to facilitate connecting from StarOffice / OpenOffice.org suite of applications to a MySQL database. The new native driver is called MySQL Connector/OpenOffice.org. However the current version of the MySQL Connector for OO.o is compatible only with OpenOffice 3.1, StarOffice 9.1 or newer and it can connect only to MySQL Server 5.1 or later versions. This native connector is supposed to be faster in comparison with the Java connector.

We will explore both MySQL connectors in this section.

Note:
As an end user, you need not be concerned about the internal workings of these MySQL connectors. You just need to worry about installing and configuring the drivers so the \*Office applications can connect to the MySQL database in a seamless fashion.

I. Connector/J approach

  1. Installation steps for MySQL Connector/J

    Using the following navigation, find the location of the JRE that is being used by StarOffice / OpenOffice.org

    • Launch StarOffice / OpenOffice.org
    • Tools Menu -> Options
    • In the 'Options' window, StarOffice / OpenOffice.org -> Java

    Here is a sample screen capture from a Mac running StarOffice 9.

    In the above example, /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home is the location of the JRE. Here onwards, this location will be referred as JRE_HOME.

    1. Download the connector from the following web page:

              http://dev.mysql.com/downloads/connector/j/

      As of this writing, 5.1.10 is the current version for Connector/J

    2. Extract the driver and the rest of the files from the compressed [downloaded] archive

      eg.,
      
      % gunzip -c mysql-connector-java-5.1.10.tar.gz | tar -xvf -
      
      
    3. Locate the jar file that contains the driver --- mysql-connector-java-5.1.10-bin.jar, and copy it into the <JRE_HOME>/lib/ext directory with 'root' privileges.

      eg.,
      
      % sudo cp mysql-connector-java-5.1.10-bin.jar /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext
      
      % ls -l /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/\*connector\*jar
      /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/mysql-connector-java-5.1.10-bin.jar
      
      
    4. Restart StarOffice / OpenOffice.org

    This concludes the installation of MySQL Connector/J.



    2. Configuration steps for Connector/J
    1. Launch StarOffice / OpenOffice.org

    2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.

    3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

      Click on "Next >>" button

    4. In the next screen, select JDBC by clicking on "Connect using JDBC (Java Database Connectivity)" radio button

      Click on "Next >>" button

    5. In "Set up connection to a MySQL database using JDBC" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections.

      MySQL JDBC driver class text field will be automatically filled with the string com.mysql.jdbc.Driver. Leave that string intact, and click on "Test Class" button to make sure that the relevant class can be loaded with no issues. Unless the driver class is loaded successfully, you will not be able to connect to the MySQL database. In case of unsuccessful class loading, double check the installation steps for MySQL Connector/J.

      Click on "Next >>" button

      Note:
      In the above screenshot, notice that the "Name of the database" was filled with ISVe?zeroDateTimeBehavior=convertToNull (It is not completely visible in the above screen capture, but you just have to believe me). In this example, ISVe is the database name and zeroDateTimeBehavior is the configuration property which was set to a value of convertToNull. Without this configuration property, Connector/J throws an exception when it encounters date values such as 0000-00-00. In such cases, the error message will be something similar to java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date.

      Configuration properties define how Connector/J will make a connection to a MySQL server. The list of Connector/J configuration properties are documented in the following web page:

              http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

      If you have more than one configuration property, you can define all of those properties in the "Name of the database" field. The syntax would be:
          <MySQL_DB_Name>?<Config_Property1=Value>&<Config_Property2=Value>&..&<Config_Propertyn=Value>

    6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

      Click on "Next >>" button

    7. In the final screen, simply accept the default values and click on 'Finish' button.

      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.

    8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.

      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.




    II Connector/OpenOffice.org approach

    MySQL Connector for OpenOffice.org is a MySQL driver for OpenOffice suite of applications. Even though it appears to be a native driver, MySQL Connector/OpenOffice.org has no implementation for the MySQL Client Server protocol. It is in reality a proxy on the top of MySQL Connector for C++ aka MySQL Connector/C++.

    Unlike MySQL Connector/J, Connector/OpenOffice.org has no dependency on JRE, and it can easily be installed using the OpenOffice.org Extension Manager. Due to the underlying native code, Connector/OpenOffice.org may outperform Connector/J in performance.

    1. Installation steps for MySQL Connector/OpenOffice.org

    Before installing the connector, make sure that you have OpenOffice.org 3.1 [or later] -OR- StarOffice 9.1 [or later] suite installed, and the version of the MySQL server on which the database is hosted is at least 5.1. If any of these requirements are not met, skip this entire section and check the I. Connector/J approach for the instructions that may work with your current versions of StarOffice / OpenOffice and MySQL server.

    1. Download the connector for your platform from the following location:

              http://extensions.services.openoffice.org/project/mysql_connector
    2. Launch StarOffice / OpenOffice.org

    3. Bring up the "Extension Manager" by clicking on Tools Menu -> Extension Manager ...

    4. Click on "Add" button, then locate the OpenOffice connector that you downloaded in step #1 (see two steps above). Click on "Open" button. The name of the connector will be something similar to mysql-connector-ooo-....oxt.

    5. Choose appropriate response to the question "For whom do you want to install the extension?". In this example, I chose the option "Only for me".

    6. Read the "Extension Software License Agreement" and accept the agreement to install the Connector/OpenOffice.org as an extension to StarOffice / OpenOffice.org

    7. Restart StarOffice / OpenOffice.org to complete the installation.



    2. Configuration steps for MySQL Connector/OpenOffice.org
    1. Launch StarOffice / OpenOffice.org

    2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.

    3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

      Click on "Next >>" button

    4. In the next screen, select "Connect native" radio button

      Click on "Next >>" button

    5. In "Set up connection to a MySQL database" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections. If the MySQL Server is running on the same machine as that of the StarOffice / OpenOffice.org application, you can provide the location of the socket under "Socket" field. If not, leave it blank.

      Click on "Next >>" button

    6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

      Click on "Next >>" button

    7. In the final screen, simply accept the default values and click on 'Finish' button.

      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.

    8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.

      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.


    That is all there is to it in installing and configuring the MySQL connectors for \*Office suite of applications. Now enjoy the flexibility of fetching the data from your favorite office productivity software.

Thursday Dec 10, 2009

Oracle RDBMS: Extracting the Table, Index & View Definitions (DDL) and Indexed Columns

(Reproducing a 30 month old blog post from my other blog at blogger. Source URL:
    http://technopark02.blogspot.com/2007/05/oracle-how-to-get-tableview-definition.html
)

Q: How to extract the table definition (DDL statement) from an Oracle database without having to go through a stack of dictionary views?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;

eg.,
SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','PERSON') from DUAL;

  CREATE TABLE "FS890"."PERSON"
   (    "SSN" VARCHAR2(12),
        "FIRST_NAME" VARCHAR2(25),
        "LAST_NAME" VARCHAR2(25),
        "STREET" VARCHAR2(40),
        "CITY" VARCHAR2(30),
        "STATE" VARCHAR2(30),
        "ZIP" VARCHAR2(15),
        "COUNTRY" VARCHAR2(35)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)  TABLESPACE "PSDEFAULT"



Q: How to extract the index definition (DDL statement) from an Oracle database for a given index nam?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

eg.,
SQL> create index PERSON_IDX on PERSON ( SSN ); 

Index created.

SQL> set long 1000
SQL> set pagesize 0

SQL> select  DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;

  CREATE INDEX "FS890"."PERSON_IDX" ON "FS890"."PERSON" ("SSN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSDEFAULT"

Note:

If the interest is only to get the indexed column names for an index, simply query COLUMN_NAME of table USER_IND_COLUMNS.

Syntax:
select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = '<index_name>';

eg.,
SQL> column COLUMN_NAME format A15
SQL> select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = 'PERSON_IDX';

COLUMN_NAME
---------------
SSN



Q: Given a view name, how do we get the definition of the view? i.e., how to get the corresponding DDL statement that was used to create the view?

A: Query the TEXT column of table DBA_VIEWS.

Syntax:
SQL> set long 10000

SQL> select TEXT
  2  FROM DBA_VIEWS
  3  where OWNER = '<owner_name>'
  4  and VIEW_NAME  = '<view_name>';
Here is an example:
% sqlplus fs890/fs890@fs890

SQL> create table PERSON (
  2  SSN        VARCHAR2(12),
  3  FIRST_NAME VARCHAR2(25),
  4  LAST_NAME  VARCHAR2(25),
  5  STREET     VARCHAR2(40),
  6  CITY       VARCHAR2(30),
  7  STATE      VARCHAR2(30),
  8  ZIP        VARCHAR2(15),
  9  COUNTRY    VARCHAR2(35));

Table created.

SQL> create view PERSON_VW as
  2  select SSN, FIRST_NAME, LAST_NAME from PERSON;

View created.

SQL> set long 1000
SQL> select TEXT
  2  from DBA_VIEWS
  3  where OWNER = 'FS890'
  4  and VIEW_NAME  = 'PERSON_VW';

TEXT
--------------------------------------------------------------------------------
SELECT SSN, FIRST_NAME, LAST_NAME FROM PERSON



Q: How to find the schema name and the DB user name from an active session?

A: Run the following query:

select sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

Alternatively run select USER from DUAL; to find the current {session} user name.

sys_context() function returns the value of parameter associated with the context namespace. USERENV is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

eg.,
SQL> column SESSION_USER format A15
SQL> column CURRENT_SEHEMA format A15

SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER, 
  2  sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

SESSION_USER    CURRENT_SCHEMA
--------------- ---------------
FS890           FS890

SQL> column USER format A6

SQL> select USER from DUAL;

USER
------
FS890



Note:

Be aware that there are multiple ways of extracting the same piece of information from an Oracle database. I just provided the ones that I frequently use as part of my work.

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today