ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Performance Tune Plan

Oracle Performance Tune Plan

原创 Linux操作系统 作者:bq_wang 时间:2008-01-03 00:00:00 0 删除 编辑
偶本来就不是DBA,写出来的东西也不怕别人笑话了,以下内容是为项目写的,仅限Oracle入门或者项目开发人员使用J

Objective

The objective of this document is to provide the simplified steps for Oracle performance tune of application system development.

And the document doesn’t involve performance tune of OS, Database parameter, and database physical and logical Structure.

Keywords

Performance tune, Scalability

INTRODUCTION

This document is prepared on the basis of my project experience for Oracle Performance tune and coding standard.

Following is the step of Oracle performance tune plan:

1. Establish the Oracle naming and Code standard.

2. make sure the synchronization between the E-R model and DB schema

3. When finish the first two steps, and the job will succeed half.

4. Building the index script. based on the DB foreign key and the selection of the fields.

5. Building the index script. based on java property file.

6. Building the index script. based on java dynamic SQL.

7. provide the estimation of table data volume and table type for customer and we can focus on the frequent transaction and large table to make performance tune

8. When create the index script. and the task has finish 90%.

9. Check the DB performance view and find the Top N SQL consumed with high CPU, Memory and I/O.

Oracle Naming and Coding Standard

  • Use full, descriptive, pronounceable names (or well-known abbreviations).
  • Use consistent naming rules.
  • Use the same name to describe the same entity or attribute across tables.
  • All the entity and object Names must be from 1 to 30 bytes
  • Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. So never use the oracle reserved key word.
  • Nonquoted identifiers must begin with an alphabetic character from your database character set.
  • Never use SQL like “SELECT * FROM tablename”
  • When coding, all the oracle reserved key word should be upper.

STEP1. Check table naming standard

v$reserved_words comprise all the oracle reserved key word, but not all the reserved word can’t be used as table name or column name. Only some restricted key word can’t be used. Table name should not blank character.

SELECT * FROM user_tables

WHERE table_name IN

-- (SELECT keyword FROM v$reserved_words)

(
'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT','BETWEEN','BY',
'CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT','CREATE',
'CURRENT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP',
'ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP',
'HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL',
'INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG',
'MAXEXTENTS','MINUS','MLSLABEL','MODE','MODIFY','NOAUDIT','NOCOMPRESS',
'NOT','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON','ONLINE','OPTION',
'OR','ORDER','PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME',
'RESOURCE','REVOKE','ROW','ROWID','ROWNUM','ROWS','SELECT','SESSION',
'SET','SHARE','SIZE','SMALLINT','START','SUCCESSFUL','SYNONYM','SYSDATE',
'TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER',
'VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH'
)

Or table_name LIKE ‘% %’

STEP2. Check column naming standard

The rule is same with table name

Select TABLE_NAME,COLUMN_NAME,DATA_TYPE
From user_tab_columns
Where column_name In
-- (SELECT keyword FROM v$reserved_words)

(
'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT','BETWEEN','BY',
'CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT','CREATE',
'CURRENT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP',
'ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP',
'HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL',
'INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG',
'MAXEXTENTS','MINUS','MLSLABEL','MODE','MODIFY','NOAUDIT','NOCOMPRESS',
'NOT','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON','ONLINE','OPTION',
'OR','ORDER','PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME',
'RESOURCE','REVOKE','ROW','ROWID','ROWNUM','ROWS','SELECT','SESSION',
'SET','SHARE','SIZE','SMALLINT','START','SUCCESSFUL','SYNONYM','SYSDATE',
'TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER',
'VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH'
)

Or column_name Like '% %'

STEP3. Check the same columns have same data type

Use the same name to describe the same entity or attribute across tables, if the data type is not uniform, when join query, it will bring out the bad performance and wrong business logic.

SELECT a.column_name,a.data_type,b.data_type

FROM

(SELECT distinct column_name,data_type

FROM all_tab_columns

WHERE TABLE_NAME LIKE 'T%') a,

(SELECT distinct column_name,data_type

FROM all_tab_columns

WHERE TABLE_NAME LIKE 'T%') b

WHERE a.column_name=b.column_name

AND a.data_type<>b.data_type

STEP4. Check the same columns have same data type and same data length

It hasn’t too much infect, sometimes it will bring out confused.

SELECT a.column_name,a.data_type,b.data_type,a.data_length,b.data_length

FROM

(SELECT DISTINCT column_name,data_type,data_length

FROM all_tab_columns

WHERE TABLE_NAME LIKE 'T%') a,

(SELECT DISTINCT column_name,data_type,data_length

FROM all_tab_columns

WHERE TABLE_NAME LIKE 'T%') b

WHERE a.column_name=b.column_name

AND a.data_type=b.data_type

AND a.data_length<>b.data_length

Make sure synchronization between E-R model and DB schema
In theory, the E-R model should be synchronize with DB schema, when we modified the E-R model, we should create the upgrade script-modified SQL syntax, but sometimes when the requirement change is very big and the modification is very large, the E-R model is out of joint with DB schema. How we make sure the synchronization?
STEP1. 
We should create a full SQL script, and then reduce the redundancy, later create a new tablespace, execute the SQL script, and then we get a latest DB environment.
STEP2.
Create a DB link like REMOTEXXX, when two DB not in the same instance.
Grant the old environment privilege to new user, then use the schema’s name.
STEP3. 
Compare the new table or dropped table with old environment.
--Check the tables in ER-Model, but not in development or old environment. Then get the create table script, but not include index, foreign key and primary key constraint.
SELECT sqlText FROM 
(
 SELECT 'CREATE TABLE '||table_name AS sqlText,-1 AS column_ID,table_name 
   FROM user_tables 
 UNION
 SELECT '(' AS sqlText,0 AS column_ID,table_name 
   FROM user_tables 
 UNION
 SELECT ');' AS sqlText,100 AS column_ID,table_name 
   FROM user_tables
 UNION
 SELECT 
   CASE WHEN DATA_TYPE='NUMBER' 
        THEN
          CASE WHEN DATA_PRECISION IS NULL 
               THEN COLUMN_NAME||' INTEGER,'
               ELSE COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'||DECODE(column_ID,(SELECT MAX(B.column_ID) FROM user_tab_columns B WHERE A.table_name=B.table_name),' ',',')
END 
        WHEN DATA_TYPE='NVARCHAR2'
        THEN COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_LENGTH/2)||')'||DECODE(column_ID,(SELECT MAX(B.column_ID) FROM user_tab_columns B WHERE A.table_name=B.table_name),' ',',')
        WHEN DATA_TYPE IN ('CHAR','VARCHAR2') 
        THEN COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_LENGTH)||')'||DECODE(column_ID,(SELECT MAX(B.column_ID) FROM user_tab_columns B WHERE A.table_name=B.table_name),' ',',')
        WHEN DATA_TYPE IN ('DATE','BLOB','CLOB','LONG','NCLOB')
        THEN COLUMN_NAME||' '||DATA_TYPE||DECODE(column_ID,(SELECT MAX(B.column_ID) FROM user_tab_columns B WHERE A.table_name=B.table_name),' ',',')
   END AS sqlText,
   column_ID,
   A.table_name 
   FROM user_tab_columns A
  WHERE A.table_name Like 'T%'
) d
 WHERE d.table_name Not In
 (
 SELECT c.table_name 
   From user_tables@REMOTEXXX c
  WHERE c.table_name LIKE'T%'
)
ORDER BY table_name,column_ID,sqlText
-- Check the tables not in ER-Model, but in development or old environment. Then get the drop table script.
SELECT 'DROP TABLE '||RemoteTable.Table_name||';' AS dropTableSQL
  FROM user_tables@REMOTEXXX RemoteTable  
 WHERE RemoteTable.Table_name Not In 
 (
  SELECT LocalTable.Table_Name From user_tables LocalTable 
   WHERE LocalTable.TABLE_NAME LIKE 'T%'
 )
   AND RemoteTable.TABLE_NAME LIKE 'T%'
STEP4. 
Compare the different fields between E-R model and old environment.
--Check the fields in E-R model and not in old environment and create ‘ALTER TABLE XXX ADD XXX’ script.
SELECT 'ALTER TABLE '||TABLE_NAME||' ADD "'||COLUMN_NAME||'" '||
 (CASE WHEN DATA_TYPE='NUMBER' 
  THEN
    CASE WHEN DATA_PRECISION IS NULL 
      THEN 'INTEGER'
      ELSE DATA_TYPE||'('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'
    END 
  WHEN DATA_TYPE='NVARCHAR2'
  THEN DATA_TYPE||'('||TO_CHAR(DATA_LENGTH/2)||')'
  WHEN DATA_TYPE IN ('CHAR','VARCHAR2') 
  THEN DATA_TYPE||'('||TO_CHAR(DATA_LENGTH)||')'
  WHEN DATA_TYPE IN ('DATE','BLOB','CLOB','LONG','NCLOB')
  THEN DATA_TYPE
  END)||' ;' SQLTEXT
FROM USER_TAB_COLUMNS A
Where (a.table_name,a.column_name) Not In
(
Select b.table_name,b.column_name From test_remote b
Where b.table_name Like 'T%'
)
And A.table_name Like 'T%'
ORDER BY COLUMN_ID
--Check the fields in old environment and not in E-R model and create ‘ALTER TABLE XXX DROP XXX’ script.
SELECT 'ALTER TABLE '||TABLE_NAME||' DROP COLUMN '||COLUMN_NAME||’  ;' SQLTEXT
FROM USER_TAB_COLUMNS@REMOTEXXX A
Where (a.table_name,a.column_name) Not In
(
Select b.table_name,b.column_name From USER_TAB_COLS b
Where b.table_name Like 'T%'
)
And A.table_name Like 'T%'
ORDER BY COLUMN_ID
Build index based on foreign key constraint
A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. 
In fact, any operation on parent table will impact the child table, like update, delete, insert, usually they are queried together; we get the detail information from parent table.
The sentence is reference from Oracle Official Document
Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used.
So we must find all the foreign key constraint and foreign key fields and find some high-selection field to build index.
STEP1. 
Create a new function to get the constraint field name, Oracle use user_cons_columns and user_constraints to storage the constraint information, sometimes the constraint comprise one more than fields. The function is used to get all the constraint fields combined with ‘,’.
CREATE OR REPLACE FUNCTION f_GetConstraintColumn(itablename IN VARCHAR2,iConstraintName IN VARCHAR2)
  RETURN VARCHAR2 IS
  TYPE cur_type is REF CURSOR;
  myCur cur_type;
  v_Column1Value VARCHAR2(4000);
  v_Result VARCHAR2(32767);
begin
  OPEN myCur FOR SELECT column_name
                   FROM user_cons_columns 
                  WHERE table_name=itablename AND constraint_name=iConstraintName
                  ORDER BY POSITION;
  LOOP
    Fetch myCur Into v_Column1Value;
    Exit When myCur%notfound;
    v_Result:=v_Result||v_Column1Value||',';
  End Loop;
  Close myCur;
  v_Result:=substr(v_Result,1,length(v_Result)-1);
  return(v_Result);                                     
END;
STEP2.
Get the entire distinct field name. then we judge the fields by manual, and find which field is more fit for building index, we choose the high –selection fields.
SELECT 
DISTINCT f_GetConstraintColumn(a.table_name, a.constraint_name) ForeignKeyColumnsName
FROM user_constraints a,user_constraints b
WHERE a.constraint_type='R' 
  AND a.r_constraint_name=b.constraint_name
  AND a.table_name Like 'T%'
ORDER BY a.table_name, a.constraint_name
STEP3. 
According the high-selection fields, we can build out index script.
SELECT
a.table_name ForeignKeyTableName, a.constraint_name, 
f_GetConstraintColumn(a.table_name, a.constraint_name) ForeignKeyColumnsName,
a.r_constraint_name,b.table_name PrimaryKeyTableName,
f_GetConstraintColumn(b.table_name, a.r_constraint_name) PrimaryKeyColumnsName,
'Create index '||replace(a.constraint_name,'FK','IDX')||' on '||a.table_name||'('||f_GetConstraintColumn(a.table_name, a.constraint_name)||');' indexSQL
FROM user_constraints a,user_constraints b
WHERE a.constraint_type='R' 
  AND a.r_constraint_name=b.constraint_name
  AND a.table_name Like 'T%'
  AND f_GetConstraintColumn(a.table_name, a.constraint_name) In 
    ('INV_ID','ISSUE_ID','MELT_ID','MELT_SUM_ID','MIX_ID','PRICING_ID',
     'PROD_ORDER_ID','PROD_ORDER','REF_INV_ID','SETT_ID','STYLE_ID',     'VOU_ID','WIP_ISSUE_DET_ID','WIP_ISSUE_ID','WIP_RET_ID','METAL_LOT_CODE',
     'SCHEDULE_ID','MELT_CONT_ID','APP_INV_DET_ID','ORDER_DET_ID',
     'STYLE_STONE_CARD_ID','MLD_ISSUE_DET_ID','CUS_ID,FAC_COMP_ID',
     'T_WIP_ISSUE_ID','FG_STOCK_ID','PO_ID','LOT_CODE','INV_ID',
     'NEW_CONT_ID','SET_ID','SCAN_ID','WIP_ISSUE_ID','MENU','PN_ID',
     'STYLE_ID','DES_ID','VOU_ID','FAC_INV_ID','REP_CONT_ID','DEMD_DET_ID')
ORDER BY a.table_name, a.constraint_name  
Build index based on java property file
In java application, developer use the property file to encapsulate SQL syntax. The format in property file is like this:
XXX_SELECT=SELECT ID, DT FROM T_XXX WHERE IS_DELETED='N'
So we need to parse the every entry, and divide into Operation type, Table part, where clause part, then we analyze the table and where to make decision how to create index, base on operation type, we can’t concern on the insert SQL and dual table.
STEP1. Create analyze table, the table storage the different SQL part.
create table XXX_PROPERTY_SQL
(
  SQLMODULE   VARCHAR2(100),
  SQLTAG      VARCHAR2(100),
  SQLCONTENT  VARCHAR2(3000),
  SQLTABLE    VARCHAR2(200),
  SQLWHERE    VARCHAR2(3000),
  SQLORIGINAL VARCHAR2(3000),
  OPERATETYPE VARCHAR2(30)
)
--create a temporary table like XXX_PROPERTY_SQL
STEP2. Load the property SQL file into the SQLOriginal field of XXX_property_sql_temp table, first we format the property file, then through PL/SQL development tool, copy into the fields and save it into DB.
STEP3. Delete the unfit SQL, like remark line and NULL line, and other line.
delete from XXX_property_sql_temp where sqloriginal like '#%' or sqloriginal is null;
commit;
STEP4. Change the sqlmodule name
Update XXX_property_sql_temp 
Set sqlmodule=&isqlmodule;
commit;
STEP5. Change the sqltag name, get the sentence before ‘=’
Update XXX_property_sql_temp 
Set sqltag=substr(sqloriginal,1,instr(sqloriginal,'=')-1);
commit;
STEP6. Change the sqlcontent name, get the sentence after ‘=’
Update XXX_property_sql_temp 
Set sqlcontent=substr(sqloriginal,instr(sqloriginal,'=')+1);
commit;
STEP7. change the sqlcontent to upper, in order to make the sentence no sensitive.
update XXX_property_sql_temp 
Set sqlcontent=trim(upper(sqlcontent));
commit;
STEP8. Change the sql operate type, ‘INSERT’, ‘UPDATE’, ‘DELETE’, ‘SELECT’ is 6 character length.
update XXX_property_sql_temp
Set peratetype=substr(sqlcontent,1,6);
commit;
STEP9. Process the sqltable only for ‘UPDATE’ operation, get the table part between the first blank and the second blank.
update XXX_property_sql_temp 
Set SQLTABLE=substr(SQLCONTENT,instr(SQLCONTENT,' ')+1,INSTR(SQLCONTENT,' ',instr(SQLCONTENT,' ')+1)-instr(SQLCONTENT,' ')-1)
where peratetype='UPDATE';
commit;
STEP10. Process the sqlWHERE only for ‘UPDATE’ operation, get the where clause after ‘WHERE’ string.
update XXX_property_sql_temp 
Set SQLwhere=SUBSTR(SQLCONTENT,INSTR(SQLCONTENT,'WHERE')+6)
where peratetype='UPDATE';
commit;
STEP11. Process the sqltable only for SELECT’ operation, get the table part between ‘WHERE’ and ‘FROM’
update XXX_property_sql_temp 
Set SQLTABLE=Trim(SUBSTR(SQLCONTENT,INSTR(SQLCONTENT,'FROM')+4,INSTR(SQLCONTENT,'WHERE')-INSTR(SQLCONTENT,'FROM')-4))
where peratetype='SELECT' or Operatetype =’DELETE’;
commit;
STEP12. Process the sqlWHERE only for SELECT’ operation, get the where part after ‘WHERE’
update XXX_property_sql_temp 
Set SQLwhere=DECODE(INSTR(SQLCONTENT,'WHERE'),0,'',Trim(SUBSTR(SQLCONTENT,INSTR(SQLCONTENT,'WHERE')+5)))
where peratetype='SELECT' or Operatetype =’DELETE’;
commit;
STEP13. Insert into the XXX_property_sql table from XXX_property_sql_temp table
insert into XXX_property_sql select * from XXX_property_sql_temp;
commit;
STEP13. Later then delete from XXX_property_sql_temp;
delete from XXX_property_sql_temp;
commit;
STEP14. Analyze the entire property files loop;
STEP15. Analyze the tables and fields in where clause, and build the index script. 
Select SQLTable,SQLWhere,OperateType,SQLContent
From XXX_property_sql t
Build index based on java dynamic SQL.
The part is more like property file part, dynamic SQL usually is more complex, and all the SQL only captured from the log file. We must overall read the SQL and the business logic, then evaluate how to build the index.
Build index based on data volume and table type from customer’s estimation.
Base the data volume and table type, we can choose those large data volume and frequently transaction table; the estimation can help us focus on the main table and transaction table.

Check the DB performance view and find the Top N SQL consumed with high CPU, Memory and I/O.

STEP1. Query the v$session_longops view and get the long time run SQL.
This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
SELECT
 se.sid,
 opname,
 TRUNC(sofar / totalwork * 100, 2) || '%' as pct_work,
 elapsed_seconds elapsed,
 ROUND(elapsed_seconds * (totalwork - sofar) / sofar) remain_time,
 sql_text
FROM v$session_longops sl, v$sqlarea sa, v$session se
WHERE sl.sql_hash_value = sa.hash_value
AND sl.sid = se.sid
AND sl.serial#=se.serial#
AND sofar != totalwork
ORDER BY start_time;
STEP2. Query the v$sqlarea view and get the top N SQL consumed disk;
SELECT * FROM 
(SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls,sql_text 
   FROM v$sqlarea
  ORDER BY disk_reads Desc) 
 Where ROWNUM<10;
STEP3. Query the v$sqlarea view and get the top N SQL that has bad performance;
SELECT
 executions , 
 disk_reads, 
 buffer_gets,
 ROUND((buffer_gets-disk_reads)/buffer_gets,2) hit_radio,
 ROUND(disk_reads/executions,2) reads_per_run,
 sql_text
FROM v$sqlarea
WHERE executions>0
AND buffer_gets > 0
AND (buffer_gets-disk_reads)/buffer_gets < 0.8
ORDER BY 10 DESC
STEP4. Query the v$session_longops view and get the SQL full table scanned.
SELECT s.sql_text 
  FROM (
        SELECT opname,target,b.num_rows,b.tablespace_name,
               sql_hash_value,count(target) 
          FROM v$session_longops a,all_all_tables b

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6517/viewspace-145596/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 别了,我的2007!
请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    412
  • 访问量
    1106891