Oracle Performance Analysis 2

Oracle Performance Analysis

Software Environment: Oracle 11g, dual-machine RAC.

Problem:

Large table (Tens) query, slow operation, often timeout terminal (ORA-01013: User request to cancel the current operation)

Analysis:

1) partition Table

2) Historical Data archive

Solve:

Adoption of Scenario 2).

2. Create history tables,

3. Create Table  as Select *  from where ...

   Deletes the table data. The amount of data is too large (7444741) can not be deleted directly, need to delete in bulk.

   Declare     cursorMyCursor is SELECTROWID from{TableName}WHERELtl_time<To_date ('2017-08-01','YYYY-MM-DD')Order  byrowID--------The cursor sorted by ROWID, the deletion condition is xxx=xxxx, according to the actual situation. Type Rowid_table_type is  Table   ofrowIDIndex  byPls_integer; V_rowid Rowid_table_type;BEGIN   OpenMyCursor; LoopFetchMyCursorBulkCollect intoV_rowid limit the;--------Each processing 5000 rows, that is, every 5000 lines of one commit     Exit  whenV_rowid.Count=0; ForAll IinchV_rowid.first. V_rowid.lastDelete  from{TableName} nologgingwhererowID=V_rowid (i); Commit; EndLoop; CloseMyCursor;END;

Oracle Performance Analysis 2