Oracle Performance Tune Plan

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)
(
J5w]1o~-N0'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT','BETWEEN','BY',ITPUB个人空间ho`4nDy#v
'CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT','CREATE',
1R{d0Cc7c0'CURRENT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP',
|3]N~!Y
r|0'ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP',ITPUB个人空间t}1_
@)E
'HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL',
5z,W
M&{&aeiYQu0'INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG',ITPUB个人空间$q1Gl"\]2o
'MAXEXTENTS','MINUS','MLSLABEL','MODE','MODIFY','NOAUDIT','NOCOMPRESS',ITPUB个人空间"p*I3V1j,^
[
w
'NOT','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON','ONLINE','OPTION',ITPUB个人空间CBL9\yW
@
'OR','ORDER','PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME',ITPUB个人空间@#L v[5p'd7NC
'RESOURCE','REVOKE','ROW','ROWID','ROWNUM','ROWS','SELECT','SESSION',
Jqh
jBf$N,O*lh2c:w0'SET','SHARE','SIZE','SMALLINT','START','SUCCESSFUL','SYNONYM','SYSDATE',ITPUB个人空间2k
p
D2c8Jw7h+RN1K
P
'TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER',
 fa3E3x$Y0'VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH'
3W)KQL~Lq0)
Or table_name LIKE ‘% %’
STEP2. Check column naming standard
The rule is same with table name
Select TABLE_NAME,COLUMN_NAME,DATA_TYPEITPUB个人空间^4Up"H&m:p0F
From user_tab_columns
1I
l3kCCO0Where column_name In
9Y%mm/OB+{a0--(SELECT keyword FROM v$reserved_words)
(ITPUB个人空间-Q!_Hf
M
'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT','BETWEEN','BY',ITPUB个人空间"Q8]i2ez]%S	Z
'CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT','CREATE',
 L;o4z4e;x
ap%g0'CURRENT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP',ITPUB个人空间3C.CD0|2U!g}E8e
'ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP',ITPUB个人空间P[9Bqe%v1k$InR&E
'HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL',
d
TVeN-YV0'INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG',ITPUB个人空间1D9r:O8gu
g$Z
O
'MAXEXTENTS','MINUS','MLSLABEL','MODE','MODIFY','NOAUDIT','NOCOMPRESS',
{c;I]FG0'NOT','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON','ONLINE','OPTION',
8_X{p8e:H0'OR','ORDER','PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME',ITPUB个人空间6tX-cc6AL.M-j
t
F
'RESOURCE','REVOKE','ROW','ROWID','ROWNUM','ROWS','SELECT','SESSION',ITPUB个人空间
p
I;}Jib
'SET','SHARE','SIZE','SMALLINT','START','SUCCESSFUL','SYNONYM','SYSDATE',ITPUB个人空间'FQ;|"l;R8wW4ph
'TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER',
$^]R-V:\5Dzg
A0'VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH'
1a:CO9T`0)
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
Injavaapplication, developer use the property file to encapsulate SQL syntax. The format in property file is like this:
XXX_SELECT=SELECTID, DT FROMT_XXXWHEREIS_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
(
SQLMODULEVARCHAR2(100),
SQLTAGVARCHAR2(100),
SQLCONTENTVARCHAR2(3000),
SQLTABLEVARCHAR2(200),
SQLWHEREVARCHAR2(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'orOperatetype =’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'orOperatetype =’DELETE’;
commit;
STEP13.Insert into the XXX_property_sql table from XXX_property_sql_temptable
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
RsSW	O4o0From XXX_property_sql t
Build index based on javadynamic 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 fromcustomer’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 thev$session_longopsview and get the long time run SQL.
This view displays the status of various operationsthat 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 thev$sqlarea view and get thetop 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 thev$sqlarea view and get thetop 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 andget 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