DML performance is low, one of the most serious reasons is the existence of useless indexes. All SQL inserts, updates, and deletions become slower when they need to modify a large number of indexes when each row of data is changed. Many Oracle managers will assign indexes to an SQL query if they see a column in the where statement. While this approach allows SQL to run faster, a functionally based Oracle index makes it possible for database managers to overallocated indexes on rows in a datasheet. Overallocated indexes can severely affect the performance of key Oracle data tables.
Before Oracle9i, there was no way to determine which indexes are not used by SQL queries. Oracle9i has a tool that allows you to use the ALTER INDEX command to monitor the use of indexes. Then you can look up these unused indexes and delete them from the database.
Here is a script that opens the monitoring function for 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'll need to wait a bit longer until you've run enough SQL statements on the database, and then you can query the new V$object_usage view.
Select index_name,table_name,mon,used
From V$object_usage;
Below, we can see that V$object_usage has a column called used, and its value is yes or No. It does not tell you how many times Oracle has used this index, but this tool is useful for finding unused indexes.
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:00
Idx_acct4 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. See how much of a long operation has been done:
The V$session_longops view enables Oracle experts to reduce the elapsed time of a long-running DDL and DML statement. For example, in a data warehouse environment, even with parallel index creation techniques, it takes many hours to build a large number of G-byte indexes. Here you can query the V$session_longops view to quickly find out how much of a particular DDL statement has been completed. In fact, the V$session_longops view can also be used for any long-running operation, including an update operation that is running for a long time.
The following script displays a status message describing the time that a long-running DDL operation has been used. Note that you must obtain the SID from the v$session and insert it into the following SQL statement:
Select Sid,start_time,elapsed_seconds,message
From V$session_longops
where SID = 13
Order BY Start_time;
Here is an example of an output that shows the running process of the CREATE INDEX statement that is running for a long time.
One Table scan:cust. pk_idx:732 out of the 243260 Blocks done
3. Resolve ORA-01555 error with SET transaction command
When performing large transactions, Oracle sometimes reports the following error:
Ora-01555:snapshot Too old (rollback segment too small)
This means that Oracle's randomly allocated rollback segment for this transaction is too small for it to specify a rollback segment that is large enough to ensure the successful execution of the transaction. For example
Set TRANSACTION use rollback segment ROLL_ABC;
Delete from table_name where ...;
Commit
Oracle automatically releases the ROLL_ABC designation after the commit is completed.
4. Delete duplicate records in a table
Method principle:
1. In Oracle, each record has a rowid,rowid that is unique across the database, ROWID determines which data files, blocks, and rows are in Oracle for each record.
2. In duplicate records, all columns may have the same content, but ROWID will not be the same, so just make sure that those with the largest rowid in the duplicate record are OK, and all the rest is deleted.
Implementation method:
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 Complete
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 selected
Find Duplicate Records
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 duplicate records
Sql> Delete from a where a.rowid!= (select Max (ROWID) from a b where a.bm=b.bm and A.MC=B.MC);
Deletes 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. Control the recovery of file corruption
Based on the following error message, we found that the database can only start the instance and error when reading the control file. In the database design process, from a security perspective, the system uses three mirrored control files, and now three control file version number is inconsistent.
Svrmgrl>startup
Oracle Instance started
Total system global Area 222323980 bytes
Fixed size 70924 bytes
Variable size 78667776 bytes
Database buffers 143507456 bytes
Redo Buffers 77824 bytes
Ora-00214:controlfile ' D:\oracle\oradata\orcl\control01.ctl ' version 57460 inconsistent with file ' d:\oracle\oradata\ Orcl\control02.ctl ' version 57452.
Based on the above analysis, we try to modify the parameter file. Modify the Control_file parameter in the parameter file to a control file, using Control01, CONTROL02, and CONTROL03 respectively. However, the database failed to start, indicating that all three control files were corrupted.
Because there is no backup of the control files, we can only take the practice of rebuilding the control files.
D:\>svrmgrl
Oracle Server Manager Release 3.1.6.0.0-production
Copyright (c) 1997,1999,oracle Corporation. All rights reserved.
After the media is restored, reopen the database, prompting the log file to be restored.
svrmgr> recover database until cancel;
The log has been restored.
After the control files, data files and log files are restored, the three files are synchronized and the database is opened, and the recovery of the database is completed successfully.
svrmgr> ALTER DATABASE open resetlogs;
The database has changed.
Close the database immediately, and make a cold backup of the database, save the data of the database intact.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
