Oracle experience skills
1. Delete table space
DROP TABLESPACE TABLESPACENAME [include Contents [and DataFiles]]]
2. delete users
DROP User_name Cascade
3. Delete table precautions
When you delete all the data in a table, you must use the TRUNCATE TABLE table; The TableSpace is consumed on 100 megabytes.
4. Having clause usage
The Having clause controls the row group determined by the Group By clause, and only columns in a constant, polygroup function, or group BY clause is allowed in the HAVING clause.
5. Usage of external connection " "
External join " " is connected to the left or right side by it in the left or right side of "=". If one of the " " operator is not directly matched in the table with " " budget Any line, the front of the former matches one of the space in the latter and returned. If the two do not bring ' ', the two cannot be matched in both. Use the external connection " ", you can NOT IN operations with a very low alternative efficiency, greatly improve operational speed. For example, the following command is very slow
Use external joints to increase the query speed of the table connection
When making a table connection (commonly used for view), the following methods are often used to query the data:
SELECT PAY_NO, Project_name
From a
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO from b where value> = 120000);
---- But if there is a 10,000 records, Table B has 10,000 records, then use it for 30 minutes to check, mainly because Not in is a comparison, a total of 1000 * 10,000 comparison Can get the result. After this use of an external joint, it can shorten to 1 point:
SELECT PAY_NO, Project_name
From A, B
Where a.pay_no = b.pay_no ( )
And b.pay_no is null
And B.Value> = 12000;
6. Usage of set transaction commands
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 this transaction. At this time, it can specify a sufficiently large rollback segment to ensure the success of this transaction. For example,
SET Transaction Use Rollback Segment Roll_ABC;
Delete from table_name where ...
COMMIT;
The rollback segment roll_abc is assigned to this delete transaction, and the commit command will cancel the specified of the rollback segment after the end of the transaction. 7. Database reconstruction should pay attention to problems
Some views may bring problems during database reconstruction, because the order of structural inputs may cause the input of the view to the input of its low-level table, so that the view will fail. To solve this problem, Methods to take two steps: First enter the structure, then enter the data. The command is as follows (uesRName: JFCL, Password: HFJF, Host Sting: ORA1, Data File: Expdata.dmp):
IMP JFCL / HFJF @ ora1 file = Empdata.dmp rows = n
IMP JFCL / HFJF @ ora1 file = Empdata.dmp full = y buffer = 64000commit = y ignore = y
The first command enters all database structures, but there is no record. The second input structure and data, 64,000 bytes are submitted once .Ignore = Y option guarantees that the second input is both only in the case of the object.
Select a.empno from Emp a where a.empno not in
(SELECT Empno from Emp1 Where Job = '
Sale
');
If the external join, the rewrite command is as follows:
SELECT A.EMPNO from Emp A, EMP1 B
Where a.empno = B.Empno ( )
And b.empno is null
And b.job = '
Sale
"
It can be found that the running speed is significantly improved.
8. Newly built another table from known tables:
CREATE TABLE B
As SELECT * (can be a few columns in Table A)
From a
WHERE a.column = ...
9. Find, delete repeat record:
French 1: This looks quickly with group by statement
Select Count (NUM), Max (Name) from the version of the NUM column repeated, lists the number of records, and lists his Name properties
Group by num
Having Count> 1 - After the NUM group is packet, find out the NUM column repeat in the table, that is, the number of occurrences is greater than once.
Delete from student (SELECT above)
In this way, all repetitions have been deleted. -----careful
Method: When the table is relatively large (for example, more than 100,000), the difference in efficiency of this method cannot endure, need another way:
---- After performing the following SQL statement, you can display all Drawing and DSNO the same and repeated records.
SELECT * FROM EM5_PIPE_PREFAB
WHERE ROWID! = (SELECT MAX (ROWID) from EM5_PIPE_PREFAB D --D is equivalent to first, second
WHERE EM5_PIPE_PREFAB.DRAWING = D. Drawing and
EM5_PIPE_PREFAB.DSNO = D.DSNO;
---- You can remove all Drawing and DSNO the same and repeated records after the SQL statement is performed.
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID! = (SELECT MAX (ROWID) from EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING = D. Drawing and
EM5_PIPE_PREFAB.DSNO = D.DSNO;
10. Return to the table [N, M] record:
Take the Northern Row in a column
Select column_name from
(Select Table_name. *, Dense_Rank () over (Order By Column Desc) Rank from Table_Name)
WHERE RANK = & n;
If you want to return to the top 5 records:
Select * from Tablename WHERE ROWNUM <6; (or rownum <= 5 or rownum! = 6)
If you want to return to Article 5-9 Records:
Select * from TableName
WHERE ...
And Rownum <10
minus
Select * from TableName
WHERE ...
And Rownum <5
ORDER BY NAME Select the result After ordering the display with Name. (First choose again)
Note: You can only use the above symbols (<=,! =).
Select * from tablename where rownum! = 10; returned to the top 9 records.
No use:>,> =, =, between ... and. Since Rownum is a pseudo column that always starts from 1, Oracle believes that this condition is not true, not recorded.
In addition, this method is faster:
SELECT * FROM
SELECT ROWNUM R, A from YourTable
WHERE ROWNUM <= 20
ORDER BY NAME)
WHERE R> 10
This takes this 11-20 records! (First choose again to sort and then select)
To sort the selection, you must use the SELECT nested: inner layer sorted out.
Rownum is generated with the results, once it is generated, it will not change; at the same time, the result of the generated is in turn, no 1 will never have 2!
Rownum is a pseudo column generated during the process of query collection, and if there is RownUM condition in the WHERE condition, the ROWNUM condition is:
1: If the judgment condition is constant, then:
Only rownum = 1, <= greater than 1 natural number, = greater than 1, no results, greater than one number is no results
That is, when a ROWNUM does not satisfy the condition, the query ends this is stop key!
2: When the determination value is not constant
If the condition is = var, only when the var is 1, it meets the conditions. At this time, there is no STOP Key, Full Scan must be performed, and the data that meets the other WHERE conditions must be determined.
After selecting a line, you can choose Rownum = 2 ...
11. Quickly compile all views
---- When the database is poured into the new server (database reconstruction), you need to re-compile the view, because the table space view will have problems with the table of other tablespaces, and PL / SQL can be used Language characteristics, fast compilation.
SQL> Spool ON.SQL
SQL> SELECT 'ALTER VIEW' || TNAME || '
'From tab;
SQL> spool off
Then perform ON.SQL.
SQL> @ on.sql
Of course, authorization and creating symbols can also be made quickly, such as:
SQL> SELECT 'GRANT SELECT ON'
|| tname || 'to username;' from tab;
SQL> SELECT 'CREATE SYNONYM
'|| TNAME ||' for username. '|| TNAME ||'; 'from Tab;
--------------------
Let your sky only sweet and beautiful
Forgot - how to cry
Article option:
Lunatic
STRANGER
06/13/03
11:33
[Essence] Re: Oracle Common Command [Re: LUNATIC]
12. Read and write text-type operating system files
---- In the version of PL / SQL 3.3, the UTL_FILE package allows the user to read the operating system file via the PL / SQL. as follows:
Decalre
FILE_HANDLE UTL_FILE.FILE_TYPE;
Begin
FILE_HANDLE: = UTL_FILE.FOPEN
'C: /', 'Test.txt', 'A');
UTL_FILE.PUT_LINE (File_Handle, 'Hello, It's A Test Txt File');
UTL_FILE.FCLOSE (File_Handle);
END;
13. Use the new value of the column and the old value in the database trigger
---- Almost always use the column value of the trigger base table in the database trigger, if a statement needs a certain value to modify the value before modifying, use: OLD can, use a column modified new value, Use: New is ok. Such as: Old.dept_no,: new.dept_no.
14. Database file mobile method
When you want to move the database file into another directory, you can use the alter Database command to move (strong than the ALTER TABLESPACE):
1. Close the instance using the Server Manager.
SVRMGR> Connect Internal;
SVRMGR> Shutdown;
SVRMGR> EXIT;
2. Use the operating system command to move the database file location (assuming the operating system here is Solaris 2.6). Use the mv command in UNIX to move the file to a new location.
#mv /ora13/orarun/Document.dbf / ora12 / oralun
3. Load the database and use the AlTer Database command to change the file name in the database.
SVRMGR> Connect Internal;
SVRMGR> Startup
Mount
Run73
;
Svrmgr> ALTER DATABASE RENAME FILE
> '/ ORA13 / ORARUN / DOCUMENT.DBF'
> '/ ORA12 / ORARUN / Document.dbf';
4. Start instances.
SVRMGR> ALTER DATABASE OPEN
15. Connection query results:
Table A1 A2
Record 1 a
1 b
2 x
2 Y
2 z
Use SELECT to select the following results:
1 ab
2 xyz
There are two examples below:
1. Use the PL / SQL code to implement, but require your combination of lengths that cannot exceed the length of Oracle Varchar2
Create or Replace Type Strings_Table is Table of Varchar2 (20);
/
Create or Replace Function Merge (PV in strings_table) Return varcha2
IS
Ls varchar2 (4000);
Begin
For i in 1..pv.count loop
Ls: = LS || PV (i);
End loop;
Return LS;
END;
/
Create Table T (ID Number, Name Varchar2 (10));
INSERT INTO T VALUES (1, 'Joan');
INSERT INTO T VALUES (1, 'Jack');
INSERT INTO T VALUES (1, 'Tom');
INSERT INTO T VALUES (2, 'Rose');
INSERT INTO T VALUES (2, 'Jenny');
Column Names Format A80;
Select T0.ID, MERGE (Cast (SELECT NAME AUE T.ID = T0.ID) AS STRINGS_TABLE) NAMES
From (SELECT DISTINCT ID FROM T) T0;
DROP TYPE STRINGS_TABLE;
Drop function merge;
Drop Table T; 2. Purely SQL:
Table DEPT, EMP
To get the following results
Deptno, DName, Employees
---------------------------------
10, Accounting,
Clark
King; Miller
20, research, smith;
ADAMS
Ford; Scott; Jones
30, Sales, Allen; Blake; Martin; James; Turners
Employee Series each DEPT is back as a record
This Example Uses a max of 6, and would Need More Cut N Pasting to do more That:
SQL> Select Deptno, DName, EMPS
2 from
3 Select D.deptno, D. DName, Rtrim (E.ENAME || ',' ||
4 Lead (E.ename, 1) over (Partition by D.deptno
5 ORDER by E.ename || ',' ||
6 Lead (E.ename, 2) over (Partition by D.deptno
7 Order by E.ename || ',' ||
8 Lead (E.ename, 3) over (Partition by D.deptno
9 ORDER BY E.ename || ',' ||
10 Lead (E.ename, 4) OVER (Partition by D.deptno
11 ORDER BY E.ename || ',' ||
12 Lead (E.ename, 5) over (Partition by D.deptno
13 ORDER BY E.ename), ',') EMPS,
14 row_number () over (Partition by D.deptno
15 Order by E.ename) x
16 from EMP E, DEPT D
17 Where d.deptno = E.DEPTNO
18)
19 where x = 1
20 /
DEPTNO DNAME EMPS
------------------------------------------------ ------------
10 Accounting Clark, KING, MILLER
20 Research
ADAMS
, Ford, Jones, Rooney, Scott, Smith
30 Sales Allen, Blake, James, Martin, Turner, Ward
16. Built a number in Oracle will automatically increase the field to facilitate the query
1. Establish a sequence:
CREATE SEQUENCE CHECKUP_NO_SEQ
Nocycle
MaxValue 9999999999
START with 2;
2. Create a trigger:
Create or Replace Trigger set_checkup_no
Before insert on checkup_history
For Each Row
Declare
Next_CHECKUP_NO NUMBER;
Begin
- Get the next checkup number from the sequence
SELECT Checkup_no_seq.nextval
INTO next_CHECKUP_NO
From Dual;
- Ise the sequence number as the primary key
--for the record being inserted: new.checkup_no: = next_CHECKUP_NO;
END;
17. View object dependencies (such as views and table references)
View View: DBA_DependenCIES records related dependencies
Check the West I don't know which view to see, I can see in DBA_OBJECTS,
SELECT Object_name from dba_objects where object_name like '% role%' (if view role)
Then DESC will be generally known.
18. To find all the specific dates of all Friday in the month
SELECT TO_CHAR (T.D, 'YY-MM-DD') from (
SELECT TRUNC (Sysdate, 'mm') ROWNUM-1 AS D
From DBA_Objects
WHERE ROWNUM <32) T
WHERE to_CHAR (T.D, 'mm') = to_char (sysdate, 'mm') - find the date of the current month on Friday
And Trim (TO_CHAR (T.D, 'Day')) = 'Friday'
------------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
If the where to_char (t.d, 'mm') = to_char (sysdate, 'mm') is changed to sysdate-90, that is, find the current
The date of the first three months of the month.
Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author. Commonly viewed SQL viewing database SQL
1, the name for the table space and size select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
2, check the name of the table space physical file and size Select TableSpace_name, File_ID, File_name, Round (Bytes / (1024 * 1024), 0) Total_Space from DBA_DATA_FILES ORDER BY TABLESPACE_NAME
3, see the name and size of the rollback select segment_name, tablespace_name, r.status, (initial_extent / 1024) InitialExtent, (next_extent / 1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v $ rollstat v Where r.segment_id = V.usn ( ) Order by segment_name;
4, check the control file SELECT NAME FROM V $ ControlFile;
5, check the log file Select MEMBER from V $ logfile;
6. View the use of table space SELECT SUM (BYTES) / (1024 * 1024) As Free_Space, TableSpace_name from dba_free_space group by tablespace_name; select a.tablespace_name, a.bytes total, b.bytes buy, c.bytes free, B.BYTES * 100) /A.BYTES "% used", (C.BYTES * 100) /A.BYTES "% free" from sys.sm $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME; 7, to view the library database objects select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;
8. View the version of the database Select Version from product_component_version where substr (product, 1, 6) = 'Oracle'
9. View the database creation date and archive SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ Database; Storage Performance Evaluation
When the storage performance assessment, we use the disk performance index (DPI, DISK Performance Index). The following table lists the various indices in the DPI. This score system does not mean the full-scale assessment of the use and allocation of the disk. However, representing a barometer, reflecting whether there is a place where the current disk is used and whether there is a need to improve or pay attention.
MPI index
classification
Required level
Highest score
Adjustment table and index
Yes
30
Table line connection problem
no
30
Separate key Oracle file
Yes
30
Rollback segment balance
30
Temporary section balance
30
Use the top 10 SQL disk usage
<5%
60
Whether to adjust the top 25 SQL using the most disk
Yes
40
MPI index
Total
250
1. Adjustment table and index
Since the data blocks of the tables and indexes are typically read simultaneously, they should be placed on different disks as much as possible to reduce the I / O conflict of the file.
Inspection Method:
Select I.index_name, t.table_name, t.tablespace_name from user_tables t, user_indexes I where t.table_name = i.table_name and t.tablesPACE_NAME = i.tablespace_name;
The return result is the table and associated indexes created in the same tablespace. It is recommended to create new tablespaces to specifically store index and put the current index Rebuild to the newly created table space.
Alter Index IDX_Name Rebuild TableSpace TS_NAME;
Evaluation Guidelines:
grade
fraction
Table and index are placed on the same disk
0
Store the disk array, no further adjustment
20
Store the disk array, has been adjusted for the RAID type
30
Tables and indexs have been planned on different disks
30
2. Table link problem
When updating a table, there is no sufficient remaining space in the data block to accommodate the modification, the "row link" phenomenon occurs, which is linked to another data block with sufficient space, that is, one The record spans multiple data blocks so that more I / O will consume more I / O when reading the record. When there is a large number of "row links" in the database, the overall performance of the database will decline. Inspection Method:
SQLPlus / NOLOG
Connect App_User / Password
SQL> @ $ oracle_home / rdbms / admin / utslchain.sql
SQL> Analyze Table
SQL> SELECT Count (*) chained_rows, table_name from chained_rows group by table_name
If no row is returned, it means that there is no "row link" phenomenon. Otherwise, the "row link" is displayed in each table in accordance with the analyzed table.
The "Row Link" phenomenon generates an improper relationship with the PCTFree parameters. The PCTFREE value defaults to 10%. If there is a large number of row links in the system, the block reserved space specified by this parameter is too small, not enough to accommodate all records in the block. The PCTFREE value of the corresponding table should be increased.
Evaluation Guidelines:
grade
fraction
Row link phenomenon
0
Do not exist line link phenomenon
30
3. Separate key Oracle file
Whether it is for security or performance, it is recommended to distribute key Oracle files on the available separate disks.
First after the error occurs, the data file used to be recovered and the control file used to recover, the archive log file, the archive log file should be separated. If possible, distribute the following key files on different disks.
System Table Space (SYSTEM), Temporal Temperature (TEMP), Rolling Table Space (Undo), Online Refine Log File (Redo), and Archive Log File (ARCH), often accessible user tablespace, frequently accessible user index Table space, operating system disk, key Oracle software file in ORACL_EBASE.
At least online Relive log file (REDO) and archive log files (ARCH) should be stored on different disks with other files, and because most of the time of the log file is only write attributes, it is necessary to consider the weakness of RAID5 in writing. Try not to store the log file on the array group of RAID5.
Inspection Method:
select file_name, tablespace_name, bytes from dba_data_filesunion allselect file_name, tablespace_name, bytes from dba_temp_filesunion allselect name file_name, NULL, NULL from v $ controlfileunion allselect member file_name, to_char (a.group #) tablespace_name, b.bytes bytes from v $ logfile a, v $ log b where a.group # = b.group # union all (select value file_name, nULL, nULL from v $ parameter where name like 'log_archive_dest_%' and value is not null minus select value file_name, nULL, nULL from v $ Parameter Where Name Like 'Log_archive_Dest_State%'); Returns the location of all critical files in the database, by the results returned by the DBA and SA, confirming that the storage location of the key file has been adjusted in accordance with the actual situation.
Evaluation Guidelines:
grade
fraction
No adjustment, all on a single disk
0
No adjustments, all on RAID
20
Have been adjusted
30
4. Rollback segment balance
Before Oracle 9i and Oracle9i, if there is no automatic management of the rollover segment, the performance of the rollback segment is still required to monitor and adjust.
Check if you use a returned segment automatic management:
Select Name, Value from V $ Parameter Where Name Like '% UNDO_%'
If the value of the undo_management is auto in the return result, it means that the rollback segment is automatically managed, and the undo_tablespace value shows the backup scroll space that automatically manages, the undo_retrion value shows the time limit for retaining the rollback data in the rolling table space In seconds.
Note: If the value of undo_management is auto but undo_tablespace does not set the corresponding value, then use the System table space in the System table space, this is absolutely avoided.
If no return segment is automatically managed, you need to monitor the frequency of users who use the rollback segment, in principle, it is considered that more than 1 user should not use 1 return segment.
Inspection Method:
Select A.Name, B.Extents, B.rsize, B.xActs, B. Waits, B.Gets, Optsize, Status from V $ ROLLNAME A, V $ ROLLSTAT B Where A.usn = B.USN;
Check the output result, for all returns, if the XACTS (Active Transaction) and Waits are often exceeded by 1, then it means that the number of returning segments is needed to avoid possible contention.
Method for increasing the rollback segment:
Create Rollback Segment RS_Name TableSpace Rbs Storage (Initial
1
M
NEXT
2
M
); alter rollback segment rs_name online;
If you use a rollback segment, you can query the usage and allocation of the current rollback segment from the views of V $ undostat, V $ ROLLSTAT, DBA_UNDO_EXTENTS.
Evaluation Guidelines:
grade
fraction
Have a returning segment waiting phenomenon
0
No return segment waiting phenomenon
30
Automatic management of returns
30
5. Temporary section balance
When the Sort_Area_SIZE size defined in the initialization parameter cannot meet the space of the sort required, the temporary segment in the temporary table space will be used, and the disk sort is slower than 100,000 times, so the disk sort is the performance adjustment work. An important part.
It may cause sorted operations with Create Index, Distinct, Order By, Group By, etc.
Inspection Method:
SELECT NAME, VALUE from V $ sysstat where name like '% sorts%';
Returning Sorts (Memory) indicates memory sorting, and sorts (disk) represents disk sorting, if there is a large number of disk sort, it indicates that we need to increase the size of the sort zone such as sort_area_size, or you need to check the current system Whether the SQL of a large number of disks has been adjusted (check the SQL of the first 25-bit disk) will be mentioned later).
Check the session information that uses disk sort, you can locate a session of a large disk sort.
Inspection Method:
Select B.Name, A.SID, A. Value from V $ SESSSTAT A, V $ statname b Where a.statistic # = b.statistic # and b.Name = 'sorts (disk)' and A.Value> 0 Order By A.Value DESC;
If possible, we should distribute multiple temporary data files in the temporary table space on different disks to reduce the disk conflicts that may be generated when sorting.
In Oracle9i, we can set PGA_AGGREGATE_SIZE initialization parameters to specify that all sessions will use the PGA size, but also set the Workarea_Size_Policy parameter to Auto. Other details see the "4. Sort" section in the memory performance assessment.
Evaluation Guidelines:
grade
fraction
There is no assessment for the existing disk sort
0
Adjustment has been adjusted on the existing disk
30
6. The top 10 statements of the most waste disk read operation accounts for the proportion of all statements
Usually in an optimized system, 10 most commonly used SQL statements account for more than 50% of the disk read operations across the system. These SQLs are the most important part of the optimization, and it is also a high priority in optimization. Usually our optimized goal is to reduce the percentage of these SQL disk read operations to 5-19%.
Inspection Method:
select sum (pct_bufgets) from (select rank () over (order by disk_reads desc) as rank_bufgets, to_char (100 * ratio_to_report (disk_reads) over (), '999.99') pct_bufgets from v $ sqlarea) where rank_bufgets <11;
Evaluation Guidelines:
grade
fraction
<5%
60
5-19%
50
20-25%
30
> 25%
0
7. Adjust the first 25 most waste disk read operations
In the case where there is no adjustment, in most systems, the disk read operation of the number of statements accounting for the first 25-bit sentences will occupy 75% of the entire system, which is critical to adjust this part of the statement. This part of the script is used to get the number of SQL statements that account for the top 25 bits. EXEC in the output result indicates the number of times the SQL is executed. Inspection Method:
set serveroutput on size 1000000declare execution number; top25 number; text1 varchar2 (4000); x number; len1 number; cursor c1 is select executions, disk_reads, substr (sql_text, 1, 4000) from v $ sqlarea order by disk_reads desc; begin dbms_output .put_line ('exec' || '' || 'TEXT'); DBMS_OUTPUT.PUT_LINE ('-----' || '' || "-------- - '||' '||' ------------- '; Open C1; for i in 1 .. 25 loop fetch c1 int1; dbms_output.put_line RPAD (TO_CHAR (Execution), 5) || '|| RPAD (TO_CHAR (TOP25), 8) ||' '|| Substr (Text1, 1, 66)); LEN1: = Length (Text1); x: = 66; While Len1> x - 1 loop dbms_output.put_line ('-' || Substr (Text1, X, 66)); x: = x 66; end loop; end loop;
Evaluation Guidelines:
There is no specific assessment criterion, which requires developers or DBA to confirm that the statement that belongs to the application system in these 25 SQL has been tuned.
8. Other storage related adjustments
1) The total number of extents in the dictionary management table space is not more than 4096
Inspection Method:
select a.tablespace_name, sum (a.extents) from dba_segments a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.extent_management = 'DICTIONARY' group by a.tablespace_name order by sum (a.extents);
Check the output result, if the total number of EXTENTs in a table space exceeds 4096, then it is necessary to expand the extent size of this table space, excessive Extent has a negative impact on the spatial management of DMT.
2) The number of EXTENTs in the local management table space does not exceed 1024
Inspection Method:
select segment_name, segment_type, extents, bytes, b.tablespace_name from dba_segments a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.extent_management = 'LOCAL' and a.extents> 1024; check the output records are returned Single segment is more than 1024 objects, for these objects, you should create a single table space with larger Extent size, then go in the new table space.
3) Check if the Pctincrease value of the dictionary management table space is 0
For all EXTENTs in the table space, it is recommended that all segments in the tablespace do not set a stand-alone Storage parameter. For the PctinCrease parameters of the table space, it is recommended to set to 0, and the MineXtents parameter should be set to ensure that the initial allocation of sufficient space gives the newly created object.
For the LMT table space, the PctincRease and next parameters in the Storage parameter are invalid, and it is recommended to set the appropriate UNIFORM parameter management table space Extent assignment.
4) Consider using partitions to avoid disks
The partition table has strong practicability in the improvement of the convenience and performance of management, and can even think that the partition is the best way to improve the performance related to large tables. By accessing a small segment of a table or an index, the partition can improve efficiency well without accessing the entire table or index. If a table or index partition is on a different disk, it can greatly increase data throughput and get good database performance.
For partitioning, please refer to other partition documents for the purpose of use.
5) Whether the non-partition key index of the partition table is a global partition index
Since the data volume of the partition table is usually huge, if the index is created on the non-partition key of the partition table, it is recommended to create a global partition index, which can better improve performance. Note: If a partition is truncated or deleted a partition, you must Rebuild's all global indexes in this partition table, otherwise these global indexes will be in the Invalid status, resulting in the use of these indexed SQL statements fail.
Database Performance Check Guidance Scheme
Author: kamus
Date: 2004-9
After the system is stable, check the product database every month in accordance with this guidance scheme.
This guidelines apply to Oracle9i databases because some scripts can run in 9i.
The inspection method is to execute a command script in SQLPLUS after logging in to the database as a SYSDBA ("Check Method" section of each section has a detailed command script).
Command to log in to the database:
SQLPlus "Sys / Password As Sysdba"
One. Memory performance assessment
When the memory performance assessment, we use the memory performance index (MPI, Memory Performance Index), which lists the various indices in the MPI, which does not mean all-round assessments for the use and allocation of memory, Only a barometer reflects the use and allocation status of the current system memory.
MPI index
classification
Required level
Highest score
Buffer Cache> 98% 30 Data Data Dictionary (Dictionary Cache)> 98% 30 Bank Slow His Library Cache> Sort in Memory (Sort In Memory> 98% 30 Idle Data buffer ratio 10-25% 30 Use the maximum of the first 10 SQL occupied memory <5% 60 Has the first 25 SQLs that have been adjusted using the most 25 SQL is 30 whether to fix frequently used in the cache is 10 MPI index The total score 2501. Buffer hit rate
The percentage of non-disk read (buffer hit) is shown in the total reader of data. Of course, very high hit rate does not represent the database performance must be excellent, and it is possible to be a bad SQL to cause a large buffer reading. This hit rate can better reflect the database performance after the first query has been adjusted. .
Inspection Method:
Select (1 - (SUM (Decode (Name, 'Physical Reads', Value, 0)) / (SUM (Decode (Name,' DB Block Gets', Value, 0)) SUM (Decode (Name, 'Consistent Gets ', value, 0)))))) * 100 "Hit Ratio" from V $ sysstat;
Evaluation Guidelines:
grade
fraction
<90% 0 90-94% 10 95-98% 20> 98% 30
2. Data word code
The percentage of the internal memory reading of the data dictionary and other objects is shown.
Inspection Method:
Select (1 - (summisses) / sum (gets)) * 100 "Hit Ratio" from v $ rowcache;
Evaluation Guidelines:
grade
fraction
<85% 0 86-92% 10 92-98% 20> 98% 30
3. Subcommitte
The percentage of the memory read operation of SQL and PL / SQL objects is shown. Also note that a high hit rate does not always reflect the database performance.
Inspection Method:
Select SUM (Pins) / (SUM (Pins) SUM (Reloads)) * 100 "Hit Ratio" from V $ librarycache
Evaluation Guidelines:
grade
fraction
<90% 0 90-94% 10 94-98% 20> 98% 30
4. Sort in memory
Depending on the value of the initialization parameter PGA_AGGREGATE_TARGET or SORT_AREA_SIZE, the user's sorting operation may be executed in memory, or it may be executed in the temporary table space. This check is used to display the percentage of the total sorting in the memory.
Inspection Method:
Select A.Value "Disk Sorts", B.Value "Memory Sorts", ROUND ((100 * B.Value) / Decode (A.Value B.Value), 0, 1, (A.Value B. Value)), 2) "PCT Memory Sorts" from V $ sysstat a, v $ sortstat b where a.name = 'sorts (disk)' and b.name = 'sorts (Memory)'; Evaluation Guidelines:
grade
fraction
<90% 0 90-94% 10 94-98% 20> 98% 30
5. Idle data buffer ratio
The number of idle recording is divided by the total number of records in the X $ BH (i.e., the total number of the assigned data block buffers). Also note that databases with many idle buffers are not necessarily an optimal environment, because it may be that the buffer is set too large, waste memory.
Inspection Method:
Select Decode (State, 0, 'Free', 1, Decode (LRBA_SEQ, 0, 'Available', 'Being Used'), 3, 'Being Used', State) "Block Status", Count (*) from x $ Bh group by decode (state, 0, 'free', 1, decode (lrba_seq, 0, 'available ",' being used '), 3,' being used ', state)
Evaluation Guidelines:
grade
fraction
<5% 0 5-19% 30 20-25% 20> 25% 0
6. The top 10 statements of the most wasteful memory account for the proportion of all memory reading
Usually in an optimization system, 10 most commonly used SQL statements will account for more than 50% of the entire system. These SQLs are the most important part of the optimization, and it is also a high priority in optimization.
Inspection Method:
select sum (pct_bufgets) from (select rank () over (order by buffer_gets desc) as rank_bufgets, to_char (100 * ratio_to_report (buffer_gets) over (), '999.99') pct_bufgets from v $ sqlarea) where rank_bufgets <11;
Evaluation Guidelines:
grade
fraction
<5% 60 5-19% 50 20-25% 30> 25% 0
7. Adjust the prior 25 most wasteful memory
In the case where there is no adjustment, in most systems, the internal memory of the number of statements accounts for 75% of the entire system, and the other statement is adjusted is critical. This part of the script is used to get the number of SQL statements that account for the top 25 bits. Inspection Method:
set serveroutput on size 1000000declare top25 number; text1 varchar2 (4000); x number; len1 number; cursor c1 is select buffer_gets, substr (sql_text, 1, 4000) from v $ sqlarea order by buffer_gets desc; begin dbms_output.put_line ( 'Gets '||' '||' Text '); DBMS_OUTPUT.PUT_LINE (' ------ '||' '|| "-------------') Open C1; for i in 1 .. 25 Loop Fetch C1 INTO TOP25, TEXT1; DBMS_OUTPUT.PUT_LINE (RPAD (TO_CHAR (TOP25), 9) || '|| Substr (Text1, 1, 66)); LEN1: = Length (Text1); x: = 66; while len1> x - 1 loop dbms_output.put_line ('"| | s); x: = x 66; end loop; End loop; END; /
Evaluation Guidelines:
This section does not evaluate the standard, requiring developers or DBA to confirm that the statement that belongs to the application system in this 25 SQL has been tuned.
8. Fixed cache object
Try the object that is fixed (PIN) in the memory, including tables, stored procedures, and more.
Search for objects that need to be greater than 100K continuous space in the shared pool:
Select * from v $ db_object_cache where share_mem> 100000 and type in ('package', 'package body ",' procedure ',' function ');
Investigate the result of returning, confirm whether there is a PIN to the shared pool, return the key in the result, if YES, then indicating that the object is fixed in the shared pool, and NO, indicating that it is not fixed.
If you need to fix it, use the following statement:
Exec dbms_shared_pool.keep ('sys.standard');
The DBMS_Shared_Pool package is not created when the database is default, so you need to create the package first.
CD $ oracle_home / rdbms / admin
SQLPlus "/ as sysdba"
@ dbmspool.sql
If we want to fix the table, you can use the Cache key when you create a table or modify the table properties, place the table to the MRU side of the Buffer Cache's LRU list. Usually we need this operation for smaller but frequently used tables.
Alter Table Table_name Cache;
We can also place frequently used tables to another independent buffer cache, such as a Keep pool. This operation allows the data of these tables to not be quickly cleared from the default buffer cache. ALTER TABLE TABLE_NAME Storage (Buffer pool Keep);
Evaluation Guidelines:
This section does not evaluate the standard, developer or DBA is required to be cautious after system analysis.
two. Storage performance assessment
three. Ten contents that need to be viewed first in the StatsPack report
This article reference:
Oracle9i Performance Tuning Tips & Techniques - Richard J.NIEMIEC
Oracle9i Database Concepts - Tahiti.Oracle.com
Oracle9i Database Reference - Tahiti.Oracle.com
Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
Published on September 30, 2004 2:38 AM
comment
#
Reply: Database Performance Check Guide - Part I
2004-09-30 3:34 AM
5415
can not read it. . . .
#
Reply: Database Performance Check Guide - Part I
2004-09-30 11:14 AM
ADAM
Be taught
#
Reply: Database Performance Check Guide - Part I
2004-10-03 5:14 PM
Mudfei
Looking forward to Part II
#
Reply: Database Performance Check Guide - Part I
2004-10-08 8:30 PM
Fenng
Or basically use ratios (Ratio) to measure performance
Lewis introduced a method, not losing as a good idea
