Author: Liuying Bo collected Time: April 29, 2004
Reprint, please indicate the source, thank you!
1. Find out useless index:
DML performance is low, one of the most serious reasons is the existence of useless indexes. All SQL insertions, updates, and delete operations will become slower when they need to modify a large number of indexes when each row of data is changed. Many Oracle administrators will assign indexes for it as long as you see a list of WHERE statements in a SQL query. Although this method can make SQL run more quickly, the function-based Oracle index allows database managers to excessively assign index on the line of data tables. Excessive assignment claims seriously affect the performance of critical Oracle data sheets.
Before Oracle9i appeared, there was no way to determine the index that the SQL query did not use. Oracle9i has a tool that allows you to use the alter index command to monitor the use of an index. Then you can find these unused indexes and remove them from the database.
Below is a script that opens the monitoring function of all indexes in a system:
Spool run_monitor.sql
SELECT 'ALTER INDEX' || Owner || '.' || index_name || 'Monitoring Usage;'
From dba_indexes
WHERE OWNER NOT IN ('sys', 'system');
Spool OFF;
@run_monitor
You need to wait for some time until you have run enough SQL statements on the database, then you can query the new V $ Object_USAGE view.
SELECT INDEX_NAME, TABLE_NAME, MON, USED
From v $ object_usage;
In the following, we can see that V $ Object_usage has a column called use, its value is YES or NO. It won't tell you how many times Oracle uses this index, but this tool is still useful for finding an index that is not used.
SQL> SELECT * FROM V $ OBJECT_USAGE WHERE ROWNUM <10;
Index_name Table_name Monitoring Used Start_Monitoring End_Monitoring
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ------------------------- ----------------
ASD DIM_ACCT_ITEM_TYPE_TEMP YES NO 01/15/2004 13:50:59
Idx_account_accessory_tariff1 account_accessory_tariff Yes No 01/15/2004 13:50:59
Idx_account_quota_log1 account_quota_log yes no 01/15/2004 13:50:59
Idx_account_system_parameters1 account_system_parameters Yes no 01/15/2004 13:50:59
IDX_ACCT2 ACCT YES NO 01/15/2004 13:50:59
IDX_ACCT3 ACCT YES NO 01/15/2004 13: 51: 00IDX_ACCCT4 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT1 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT2 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
2. View a long action has been done:
The V $ session_longops view allows the Oracle expert to reduce the runtime of DDL and DML statements that run time. For example, in a data warehouse environment, even if you use parallel index creation techniques, build a lot of G-bytes of indexes take a lot of hours. Here you can query the V $ session_longops view quickly find out how much a specific DDL statement has been completed. In fact, the V $ session_longops view can also be used for any running time, including updated operations for running time.
The following script will display a status information indicating that the DDL operation has been used for a long run time. Note You must get the SID from the V $ Session and insert it into the SQL statement below:
SELECT SID, Start_Time, Elapsed_seconds, Message
From v $ session_longops
Where Sid = 13
ORDER by start_time;
Here is an exemplary example showing the running process of the CREATE INDEX statement that runs long.
SID Message --- ----------------------------------------------- ------------------ 11 Table Scan: Cust.pk_idx: 732 Out of 243260 Blocks Done
3. Use the set transaction command to solve ORA-01555 errors
When performing a big transaction, sometimes Oracle will report the following error: ORA-01555: Snapshot Too Old (Rollback Segment Too Small) This shows that oracle is too small to randomly assign it to this transaction, then you can specify it. A sufficiently large rollback segment to ensure successful execution of this transaction. For example, SET Transaction Use Rollback Segment Roll_ABC; delete from table_name where ...
COMMIT;
Oracle automatically releases the specified for ROLL_ABC after submission.
4. Remove the replication of the table
Method principle:
1, Oracle, each record has a RowID, RowID is unique in the entire database, RowID determines which data file, block, line per record is in Oracle.
2. In the repeated record, the contents of all columns may be the same, but the ROWID will not be the same, so as long as it is determined that the maximum ROWID has the maximum ROWID, the rest is removed.
Implementation:
SQL> CREATE TABLE A (BM Char (4), MC VARCHAR2 (20));
Table created
SQL> INSERT INTO A VALUES ('1111', 'AAAA');
SQL> INSERT INTO A VALUES ('1112', 'aaaa'); SQL> Insert Into a Values ('1113', 'AAAA');
SQL> INSERT INTO A VALUES ('1114', 'AAAA');
SQL> INSERT INTO A SELECT * FROM A;
4 ROWS INSERTED
SQL> commit;
Commit completion
SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC
----------------------------------------
Aaairiaaqaaaajqaaa 1111 aaaa
Aaairiaaqaaaajqaab 1112 AAAA
Aaairiaaqaaaajqaac 1113 aaaa
Aaairiaaqaaaajqaad 1114 aaaa
Aaairiaaqaaaajqaae 1111 aaaa
Aaairiaaqaaaajqaaf 1112 aaaa
Aaairiaaqaaaajqaag 1113 aaaa
Aaairiaaqaaaajqaah 1114 AAAA
8 rows success
Isolated repeated record
SQL> SELECT ROWID, BM, MC from a where a.rowid! = (SELECT MAX (ROWID) from a b where a.bm = b.bm and a.mc = b.mc);
Rowid BM MC
----------------------------------------
Aaairiaaqaaaajqaaa 1111 aaaa
Aaairiaaqaaaajqaab 1112 AAAA
Aaairiaaqaaaajqaac 1113 aaaa
Aaairiaaqaaaajqaad 1114 aaaa
Delete repeat
SQL> Delete from a a a where a.rowid! = (Select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc);
Delete 4 records.
SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC
----------------------------------------
Aaairiaaqaaaajqaae 1111 aaaa
Aaairiaaqaaaajqaaf 1112 aaaa
Aaairiaaqaaaajqaag 1113 aaaa
Aaairiaaqaaaajqaah 1114 AAAA
5. Recovery when controlling file corruption
According to the following error message, we found that the database can only start an instance, an error occurs when reading the control file. During the database design, from a secure perspective, the system uses three mirrored control files, and now the three control files Version numbers are inconsistent. SVRMGRL> startuporacle instance startedtotal system global area 222323980 bytesfixed size 70924 bytesvariable size 78667776 bytesdatabase buffers 143507456 bytesredo buffers 77824 bytesORA-00214: controlfile 'd: /oracle/oradata/orcl/control01.ctl' version 57460 inconsistent with file 'd: / oracle / OraData/orcl/control02.ctl 'Version 57452. According to the above analysis, we tried to modify the parameter file. Modify the control_file parameters in the parameter file to a control file, using Control01, Control02, Control03, respectively. But the database cannot be started, and the three control files are corrupted.
Since there is no backup of the file, we can only take the practice of rebuild control files. D: /> SVRMGRLORCLE Server Manager Release 3.1.6.0.0 - Production Copyright (C) 1997, 1999, Oracle Corporation. all rights reserved. Oracle8i Enterprise Edition Release 8.1.6.0.0 - Productionwith The Partitioning OptionjServer Release 8.1.6.0.0 - ProductionsVRMGR> Connect Internal Connection is successful. SVRMGR> ShutdowM Abort has turned off the Oracle instance. SVRMGR> Startup Nomount has launched an Oracle instance. System Global Area has a total of 108,475,660 bytes Fixed Size 70924 bytes Variable Size 46116864 bytes Database Buffers 62210048 bytes Redo Buffers 77824 bytes SVRMGR> create controlfile reuse database orcl noresetlogs archivelogLogfile group 1 'd: / oracle / ORADATA / ORCL / Redo01.log ', Group 2' D: /oracle/oradata/orcl/redo02.log'data/orcl/redo03.log'datafile 'D: / Oracle / ORADATA /orcl/system01.dbf' ,'d:/oracle/oradata/orcl/users01.dbf' ,'d:/oracle/oradata/orcl/temp01.dbf' ,'d:/oracle/oradata/orcl/tools01. DBF ',' D: /oracle/oradata/orcl/indx01.dbf ',' D: /oracle/oradata/orCl/dr01.dbf ',' D: /oracle/oradata/orCl/RBS01.DBF '; statement has deal with.
After successfully rebuilding the control file, we tried to open the database, but the system error, prompting the media recovery. SVRMGR> Recover DataFile 'D: /oracle/oradata/orcl/system01.dbf'; media has recovered. SVRMGR> Recover DataFile 'D: /oracle/oradata/orcl/users0101.dbf'; media has recovered. SVRMGR> Recover DataFile 'D: /oracle/oradata/orCl/temp01.dbf'; media has been recovered. SVRMGR> Recover DataFile 'D: /oracle/oradata/orcl/tools01.dbf'; media has recovered. SVRMGR> Recover DataFile 'D: /oracle/oradata/orcl/indx01.dbf'; media has been restored. SVRMGR> Recover DataFile 'D: /oracle/oradata/orCl/dr01.dbf'; media has been recovered. SVRMGR> Recover DataFile 'D: /oracle/oradata/orcl/rbs01.dbf'; media has recovered. After the media is restored, reopen the database, prompting the log file to be recovered. SVRMGR> Recover Database Until Cancel; log has been restored.
After all of the control files, data files, log files are restored, three files are synchronized, and the database is turned on, and the database recovery work is successfully completed. SVRMGR> ALTER DATABASE OPEN RESETLOGS; database has changed.
Turn off the database immediately and make the database's cold backup, saved the data of the database.
