Skip to main content
summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat (limited to 'plugins/org.eclipse.osee.support.dev/database/db_performance.txt')
-rw-r--r--plugins/org.eclipse.osee.support.dev/database/db_performance.txt222
1 files changed, 111 insertions, 111 deletions
diff --git a/plugins/org.eclipse.osee.support.dev/database/db_performance.txt b/plugins/org.eclipse.osee.support.dev/database/db_performance.txt
index 0fdfdbadc7..e66c7d5e9d 100644
--- a/plugins/org.eclipse.osee.support.dev/database/db_performance.txt
+++ b/plugins/org.eclipse.osee.support.dev/database/db_performance.txt
@@ -1,112 +1,112 @@
-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
+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 \ No newline at end of file

Back to the top