RE: [sqr-users] Performance Issue with Oracle9i

["George Jansen" <GJANSEN@aflcio.org>]

A suggestion to track down the issues between 8i and 9i: dump V$SQL_BIND_DATA: 
drop in a procedure in your select to see what is actually in use. I attach a 
file that can be included, with one procedure to call in your Begin-Select 
paragraph, e.g.

Begin-Select
Z.col
L.othercol
   dump_bind_data('bazball', 1)
From baz z, ball b
Where ....
End-Select



! dump_binds.sqc
!
! a procedure to dump the contents of v$sql_bind_data
!

#define first_bind_dump_file_nbr 50000 

#define max_bind_dumps_per_select 3    ! or however often you care to see it.


!
! Never called -- exists to ensure that the metadata array is created.
!
Begin-Procedure create_bind_dump_array
create-array
    name=bind_dump_metadata
    size=50
    field=fileno:number
    field=repeats:number
    field=has_been_opened:number
    field=is_closed:number
End-Procedure


!
! Called once per select to open the file and initialize array entries.
!
Begin-Procedure setup_bind_dump($select_name, #select_nbr)

  let $file_name = '/tmp/' || $select_name || '.txt'
  let #fileno = {first_bind_dump_file_nbr} + #select_nbr
  open $file_name AS #fileno RECORD=1024:VARY FOR-WRITING
  put #fileno 0 1 INTO bind_dump_metadata(#select_nbr) fileno repeats has_been_o
pened
   write #fileno from 'fileno:position:datatype:value'

End-Procedure setup_bind_dump


!
! Once per select to close file and record it.
!
Begin-Procedure cleanup_bind_dump(#select_nbr)
  get #fileno from bind_dump_metadata(#select_nbr) fileno
  close #fileno
  put 1 into bind_dump_metadata(#select_nbr) is_closed
End-Procedure cleanup_bind_dump


!
!  Called from dump_bind_data every time we wish to write.
!
Begin-Procedure select_and_write_binds(#fileno)

Begin-Select
cursor_num &a
position &b
datatype &c
value &d
  string &a &b &c &d  by ':' into $pline
  write #fileno from $pline
From v$sql_bind_data
Where value IS NOT NULL
End-Select

End-Procedure


!
! This is the only procedure to be called externally.
!
Begin-Procedure dump_bind_data($select_name, #select_nbr)
  get #has_been_opened from bind_dump_metadata(#select_nbr) fileno
  if #has_been_opened = 0
    Do setup_bind_dump($select_name, #select_nbr)  
  end-if
  get #fileno #repeats FROM bind_dump_metadata(#select_nbr) fileno repeats
  if #repeats < {max_bind_dumps_per_select}
    Do select_and_write_binds(#fileno)
    let #repeats = #repeats + 1
    put #repeats into bind_dump_metadata(#select_nbr) repeats
  else
    get #is_closed from bind_dump_metadata(#select_nbr) is_closed
    if #is_closed = 0
      Do cleanup_bind_dump(#select_nbr)
    end-if
  end-if
End-Procedure



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users