Several tips for Oracle's use

oracle| Tips
Author: Liu Yingbo Collection and finishing
Time: April 29, 2004

Reprint please indicate the source, thank you!



1. Find Useless index:

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.


SID message
--- ---------------------------------------------------------------
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.
Oracle8i Enterprise Edition Release 8.1.6.0.0-production
With the partitioning option
Jserver Release 8.1.6.0.0-production
Svrmgr> Connect Internal
The connection was successful.
Svrmgr> SHUTDOWM Abort
The ORACLE instance has been closed.
Svrmgr> Startup Nomount
An ORACLE instance has been started.
System global Zone Total has 108,475,660 bytes
Fixed Size 70,924 bytes
Variable Size 46,116,864 bytes
Database buffers 62,210,048 bytes
Redo buffers 77,824 bytes
Svrmgr>create controlfile Reuse Database ORCL noresetlogs Archivelog
Logfile Group 1 ' D:\oracle\oradata\orcl\redo01.log ',
Group 2 ' D:\oracle\oradata\orcl\redo02.log ',
Group 3 ' D:\oracle\oradata\orcl\redo03.log '
DataFile ' D:\oracle\oradata\orcl ystem01.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 been processed. After successfully rebuilding the control file, we tried to open the database, but the system error, prompting the need for media recovery.
Svrmgr>recover datafile ' D:\oracle\oradata\orcl ystem01.dbf ';
The media has been restored.
svrmgr> recover datafile ' d:\oracle\oradata\orcl\users0101.dbf ';
The media has been restored.
svrmgr> recover datafile ' d:\oracle\oradata\orcl\temp01.dbf ';
The media has been restored.
svrmgr> recover datafile ' d:\oracle\oradata\orcl\tools01.dbf ';
The media has been restored.
svrmgr> recover datafile ' d:\oracle\oradata\orcl\indx01.dbf ';
The media has been restored.
svrmgr> recover datafile ' d:\oracle\oradata\orcl\dr01.dbf ';
The media has been restored.
svrmgr> recover datafile ' d:\oracle\oradata\orcl\rbs01.dbf ';
The media has been restored. 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.