Alex的个人Blog

1. 录制脚本并回放 , 比较适合用于重复的工作.

   From the JDeveloper 10g (10.1.3) Documentation:
   To define accelerators for recording and playing back:
   1. From the main menu, choose Tools then choose Preferences.
   2. In the Preferences dialog, select the Accelerators node.
   3. On the Accelerators page, in the Category list select Code Editor.
   4. In the Actions list, select Macro Toggle Recording.
   5. Put the focus in the New Accelerator field, and enter an accelerator by pressing the key combination on the keyboard.
   6. To add the accelerator you have suggested for beginning and ending the recording of macros, click Add
   7. Now, in the Actions list, select Macro Playback.
   8. Repeat steps 5 and 6 to assign an accelerator for playing back the macro.
   9. Click OK.
   To record a macro:
   1. Open the source file in an editor.
   2. To begin recording, press the key combination you have defined for recording macros.
   3. Now enter the keystroke sequence you wish to record.
   4. To end recording, again press the key combination you have defined for recording macros.
   To play back a macro:
   1. Open the source file in an editor.
   2. Position your cursor in the open file.
   3. Press the key combination you have defined for playing back macros.

2. Gather Statistics on multiple tables at once

   DESCRIPTION
   A snippet to gather stats on multiple tables (owned by your schema)

   CODE
   BEGIN FOR x IN (SELECT * FROM user_tables WHERE table_name IN (‘table1′, ‘table2′, ‘table3′))

   LOOP DBMS_STATS.gather_table_stats (ownname => USER , tabname => x.table_name , partname => NULL , estimate_percent => DBMS_STATS.auto_sample_size , block_sample => FALSE , method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254′ , degree => NULL , granularity => ‘ALL’ , cascade => TRUE , no_invalidate => FALSE );

   END LOOP;

   END;

   /

    

3. 查找为什么某一sql 的 query rewrite 没有用

   CREATE GLOBAL TEMPORARY TABLE rewrite_table(
   STATEMENT_ID VARCHAR2(30), — id for the query
   mv_owner VARCHAR2(30), — owner of the MV
   mv_name VARCHAR2(30), — name of the MV
   SEQUENCE INTEGER, — sequence no of the error msg
   QUERY VARCHAR2(4000), — user query
   MESSAGE VARCHAR2(512), — EXPLAIN_REWRITE error msg
   pass VARCHAR2(3), — rewrite pass no
   flags INTEGER, — associated flags
   reserved1 INTEGER, — currently not used
   reserved2 VARCHAR2(10)) — currently not used
   ON COMMIT PRESERVE ROWS
   /
   – Execute anonymous block to give detailed Query Rewrite confirmation
   DECLARE
       l_stmt VARCHAR2 (4000);

       l_task_name VARCHAR2 (30);
   BEGIN
       DELETE FROM rewrite_table;
       l_stmt := ‘<< put query text here >>’;
       dbms_mview.explain_rewrite (QUERY => l_stmt , mv => ‘<>.<>’ — (or put NULL here for all MViews)
   , STATEMENT_ID => ‘MV_Explain_RW1′
   );
   END;

   /
   – Query the results
   SELECT *
   FROM rewrite_table
   ORDER BY SEQUENCE;

参考资料

http://htmldb.oracle.com/pls/otn/f?p=42626:54:1827078984577445::::P54_ID:341

http://htmldb.oracle.com/pls/otn/f?p=42626:28:1827078984577445:pg_R_1309346028248444550:NO&pg_min_row=16&pg_max_rows=15&pg_rows_fetched=15

http://htmldb.oracle.com/pls/otn/f?p=42626:29:1827078984577445::NO::P29_ID:401