plugins/org.eclipse.osee.support.dev/database/db_performance.txt

turn off auto db stat collection

Oracle document ids:
	Note:260942.1
	Note:557661.1
	Note:215187.1
	Note:445126.1	How to manually store an outline using V$SQL and DBMS_OUTLN.CREATE_OUTLINE
	Note:555579.1	10.2.0.4 Patch Set - Availability and Known Issues
	NOTE:601739.1  11.1.0.7 Patch Set - List of Bug Fixes by Problem Type
  	Doc ID: 	
	
SQL Ids:
	67mynxt63pr82:  this is the SQL ID for the show all trax related actions
	
-- dbms.stat no_invalidate is set to auto: "false"

telnet sun802.msc.az.boeing.com
cd /las_ora_apps/sqlt/sqlt/run
sqlplus user/password@service
start sqltxtract.sql 6gdcn1fq2sxtq

select * from v$sql, v$sql_shared_cursor where v$sql.sql_id = '67mynxt63pr82' and v$sql.sql_id =v$sql_shared_cursor.sql_id;
select * from v$sql_shared_cursor where sql_id = '67mynxt63pr82';
select * from v$sql where sql_id = '67mynxt63pr82';
select to_char(last_active_time,'MM-dd-yy hh24:mi:ss') from v$sql where sql_id = '67mynxt63pr82';
select * from V$sql_plan where sql_id = '5vmmky8ka6b36';

Questions:
	What causes the creation of child cursors that have all 'N' in the v$sql_shared_cursor?
	
remove histogram
need to patch bug 6810189
	
Steps Taken:
	newest patches applied (over 1000 bugs fixed)
	dbms.stat no_invalidate changed from "auto" to "false"
	large log file removed

=======  chained rows  =======
SELECT count (1) from chained_rows;
ANALYZE TABLE osee_define_txs LIST CHAINED ROWS INTO chained_rows;
SELECT count (1) from chained_rows;

=======  tracing  =======
$ORACLE_HOME/admin/lba7/udump

=======  stored outlines  =======
Doc ID: 	Note:6336044.8 which is preventing creating a stored outline

-- do this:
select * from v$parameter where name like '%cursor%';
alter system set use_stored_outlines=true;
alter session set create_stored_outlines = true;
-- now execute exact sql (copy from enterprise manager) and the outline will automatically be created
alter session set create_stored_outlines = false;

-- on Oracle 11g:
 	-- must be done each time the database instance is restarted or write database startup trigger
	alter system set use_stored_outlines=true;
	login as admin
	select hash_value, child_number, plan_hash_value from v$sql where sql_id = 'ggbncw5fg04sn';

	
	-- create the outline
declare begin dbms_outln.create_outline(hash_value, child_number); end;

	-- now test for success
	select * from dba_outlines;
	select * from user_outlines;
	select * from dba_outline_hints where name = 'SYS_OUTLINE_08111414044364301' order by join_pos;

=======  table information  =======
select * from dba_tables where owner='OSEE' and table_name='OSEE_DEFINE_TXS';
select * from dba_objects where owner='OSEE' and object_name='OSEE_DEFINE_TXS';
select * from dba_tablespaces where tablespace_name ='OSEE_DATA';

=======  sql plus  =======
!stty erase ^H
set pagesize 300
set linesize 120
 
=======  ashrpt  =======
cd $ORACLE_HOME/rdbms/admin
sqlplus osee_admin/osee_admin@lba7
@ashrpt
-2:00
/lba_users/rbrooks/workarea/ashrpt.html


cudx4784s2fx1 490550842 45.86 CPU + Wait for CPU 44.29 SELECT rel_link_id, a_art_id,... 
      latch: cache buffers chains 1.52  
3902zvx99s9rm 3655678348 38.68 CPU + Wait for CPU 37.22 SELECT art1.art_id, txd1.bran... 

_____________________
How to Transfer Stored Outlines from One Database to Another (9i and above)
  	Doc ID: 	NOTE:728647.1
______________________________
select t.* from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'TYPICAL ALLSTATS LAST')) t  where s.sql_id = '254fj1vwk9x18' ; 
____________________________


create or replace trigger enables_outlines_trigger
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;
/


 /*+ ordered */
http://www.dba-oracle.com/oracle11g/oracle_11g_extended_optimizer_statistics.htm
http://www.dba-oracle.com/art_otn_cbo_p7.htm