First, check the Oracle parameter and adjust
1. View Oracle's init.ora file
Parameter 1: db_block_buffers =
Parameter 2: Shared_Pool_SIZE =
Parameter 3: Process = 500
Parameter 4: Distributed_stransactions = 200
Parameter 5: sort_area_size = 131072
Calculation method:
(Shared_Pool_Size DB_BLOCK_BUFFERS * 16384) / 1024/1024 (M) generally Memory 2G : db_block_buffers = 9600 Memory 1G : db_block_buffers = 6400 Memory 512M : db_block_buffers = 3200 Memory 2G : Shared_pool_size = 600 (m) * 1024 * 1024 = 629145600 Memory 1G : Shared_pool_size = 350 (m) * 1024 * 1024 = 367001600 Memory 512M : Shared_pool_size = 170 (m) * 1024 * 1024 = 178257920 2. View Oracle Online Log: It is recommended to be five online logs, each for at least 5M the above; Add online log: Alter Database Add Logfile Group 4 ('D: /crds/log/redo4.log') Size 5120K; Alter Database Add Logfile Group 5 ('D: /crds/log/redo5.log') Size 5120k; Switch the current log group: Alter system switch logfile; / / Switch a log group every run Alter system switch logfile; / / Switch a log group every run Delete the old log group: Alter Database Drop Logfile Group 1; Alter Database Drop Logfile Group 2; Alter Database Drop Logfile Group 3; Delete the log file under the operating system: pull the file directly into the recycle station; Rebuild online log group 1, 2, 3: Alter Database Add logfile group 1 ('d: /crds/log/redo1.log') Size 5120k; Alter Database Add Logfile Group 2 ('D: /crds/log/redo2.log') Size 5120k; Alter Database Add Logfile Group 3 ('D: /crds/log/redo3.log') Size 5120K; 3. View the data table and the index of the INCREASE Size BY is set to 0 and the unlimited use table space; Treatment SQL> SET PAGESIZE 5000; SQL> SET Heading OFF; SQL> Spool C: /aa.sql SQL> SELECT 'ALTER TABLE' || Table_name || 'Storage (pctincrease 0);' from tabs where pct_increase <> 0; SQL> Spool OFF; SQL> @c: / aa; // will automatically grow to 0; Processing the index to process SQL> Spool C: /Index.sql SQL> SELECT 'ALTER INDEX' || index_name || 'Storage (pctincrease 0);' from user_indexes where pct_increase <> 0; SQL> Spool OFF; SQL> @c: / index; // will automatically grow to 0; 4. View the proportion of Oracle tablespace use; 30% of idleness; Select DF.TABLESPACE_NAME "Table Space Name", Totalspace "Total Space M", FreeSpace "Remaining Space M", Round (1-freePace / Totalspace) * 100, 2) "Usage%" From (SELECT TABLESPACE_NAME, ROM (SUM (BYTES) / 1024/1024) Totalspace From DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF, (SELECT TABLESPACE_NAME, ROUND (SUM (Bytes) / 1024/1024) FreeSpace From DBA_FREE_SPACE Group by TableSpace_name) FS WHERE DF.TABLESPACE_NAME = fs.tablespace_name; If the usage rate reaches 80 or more, consider adding a table space size; View the Temp table space size, change it to 300M - 500M ; 5. Check if the data sheet and the indexed home table space are correct; Data table SELECT TABLE_NAME, TABLESPACE_NAME from Tabs Order by TableSpace_name; Index file Select index_name, tableSpace_name from user_indexes order by tablespace_name, index_name 6. Fragmentation of the table space First check the debris space SQL> Column Fsfi Format 999, 99 SQL> SELECT TABLESPACE_NAME, SQRT (Max (Blocks) / SUM (Blocks)) * (100 / SQRT (SQRT (Count (Blocks)))) FSFI from DBA_Free_Space Group by TableSpace_name Order By 1; After the output is output, the SQL statement is organized; SQL> SET PAGESIZE 5000; SQL> SET Heading OFF; SQL> Spool c: /tablespace.sql SQL> Column Fsfi Format 999, 99 SQL> SELECT 'ALTER TABLESPACE' || TableSpace_name || 'Default Storage (Pctincrease 1);' From DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; // change the automatic growth to 1 SQL> SELECT 'ALTER TABLESPACE' || TABLESPACE_NAME || 'Coalesce;' from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; // Finishing the fragmentation SQL> SELECT 'ALTER TABLESPACE' || TableSpace_name || 'Default Storage (Pctincrease 0);' from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; // After finishing, the automatic growth is changed to 0; SQL> Spool OFF; SQL> @C: /TABLESPACE.SQL; 7. View immersive SQL statement: Can be viewed from disk_reads, buffer_gets, share_mem Select buffer_gets, first_load_time, disk_reads, sharable_mem, sql_text from v $ SQL Order by disk_reads; // According to Disk_reads / Sharable_MEM / BUFFER_GETS; // Find out of the larger, optimize; 8. View the dead lock user ID and kill it Select * from v $ session where taddr is not null; Make a note of the sid, serial #, use the command to kill, release resources; Alter System Kill Session 'SID, Serial #'; 9. View all table index fields SELECT A.TABLE_NAME BM, A.index_name SYM, B.TABLESPACE_NAME BKJ, SUBSTR (A.COLUMN_NAME, 1,100) SYZD, A.COLUMN_POSITION SYH From user_ind_columns a, user_indexes b Where a.index_name = B.index_name Order by BM, SYM, SYH 10. View returning segment hit rate SELECT RN.NAME, RS.GETS is accessed, RS.Waits Wait for the number of times, (Rs.Waits / Rs.gets) * 100 hits REM V $ ROLLSTAT RS, V $ ROLLNAME RN Where rs.usn = rn.usn; // Hit rate> There is a rebound section when there is 2%, and the number of times must be increased. Generally, at least 4 concurrent tasks need to use a return to the segment;
