偶本來就不是DBA,寫出來的東西也不怕別人笑話了，以下內容是為專案寫的，僅限Oracle入門或者專案開發人員使用

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 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 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 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 peratetype =’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 peratetype =’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
         WHERE a.TARGET=b.owner||'.'||b.table_name
        HAVING count(target)>10
         GROUP BY opname,target,b.num_rows,b.tablespace_name,sql_hash_value) a,v$sqltext s
WHERE a.sql_hash_value=s.hash_value
ORDER BY piece;

About SQL performance tuning
It is a very complex and high logical technique, I think it is not appropriate for discuss here.
First we analyze all the table and index periodic, so that we can use CBO to build the SQL execute plan.
Second we get the worse performance SQL syntax based on the previous step
Third we run explain for SQL syntax or run F5 in PL/SQL developer and get the execute plan.
Last we try to analyze and adjust the SQL, add index or add hint to change the current execute plan, the destination is to get the better high performance SQL.

Oracle Performance Tune Plan

相關文章