oracle排序 y在n前面,尔东成

最新推荐文章于 2022-07-26 15:53:55 发布

weixin_39541600

最新推荐文章于 2022-07-26 15:53:55 发布

阅读量230

点赞数

oracle排序 y在n前面

基于SAP ASE数据库的SAP系统安装

一、安装准备

1. 下载最新的 Best Practice for SAP ASE from Note: 1680803。

3. 下载光盘(跟正常一致，略)

二、安装

1. SAP ASE 数据库必须通过 SWPM ( sapinst) 自动安装，不建议手动安装

2. 启动SWPM, 按正常情况安装

安装完成。

三、安装后主要工作：

1. 打DBA_Cockpit 的补丁。

先打SAP Note 1668882 has been applied

through transaction SNOTE

然后再打：

1619967 - SYB: DBA Cockpit Correction Collection SAP Basis

7.31

(如果是NW7.40,

就打note

1882376)

注意：打完后，一定要按notes: 2125358 手工repair all

如下：

2. 打完后，检查参数配置

DB13 -->

3. 根据notes:

1749935 - SYB: Configuration Guide for SAP ASE 15.7

配置参数

具体见blog 最后部分 (附件1)

配置自动备份

chown sybafd:dba /sybase/AFD/log_archives

Configuration AFDDB:

@stripe_dir = '/sybase/AFD/backups' ,

@compression = '101' ,

@verify = 'header'

Configuration AFDLOG:

@stripe_dir = '/sybase/AFD/log_archives' ,

@compression = '101' ,

@verify = 'header'

DB13 设置后台作业：

此处一定要选 “Schedule as Recurring Action", 否则不能设置周期性作业。

另外，log备份要频繁一些(建议4个小时一次)，否则有可能log空间会满掉。

5. 设置ATM ( Automatic Table Maintenance)

附件1

下面是相关的执行语句：

su - syb

isql64 -W999 -Usapsa -P -S -X

如isql64 -W999 -Usapsa -P123456 -SECD -X

exec sp_configure "enable functionality group",1

exec go

exec sp_configure "enable inline default sharing" ,

exec go

exec sp_configure "select for update" , 1

exec go

exec sp_configure "enable permissive unicode" , 1

exec go

exec sp_configure "quoted identifier enhancements" , 1

exec go

exec sp_configure "streamlined dynamic SQL" , 1

exec go

exec sp_configure "suppress js max task message" , 1

exec go

exec sp_configure "kernel mode" , threaded

exec go

exec sp_configure "enable job scheduler" , 1

exec sp_configure "job scheduler interval", 40

exec sp_configure "job scheduler tasks" , 2

exec sp_configure "js job output width" , 1024

exec sp_configure "enable monitoring" , 1

exec sp_configure "enable stmt cache monitoring" , 1

exec sp_configure "errorlog pipe active" , 1

exec sp_configure "deadlock pipe active" , 1

exec sp_configure "sql text pipe active" , 0

exec sp_configure "plan text pipe active" , 0

exec sp_configure "lock timeout pipe active" , 1

exec sp_configure "per object statistics active" , 1

exec sp_configure "statement statistics active" , 1

exec sp_configure "SQL batch capture" , 1

exec sp_configure "wait event timing" , 1

exec sp_configure "object lockwait timing" , 1

exec sp_configure "process wait events" , 1

exec sp_configure "enable metrics capture" ,

exec sp_configure "enable cis" , 1

exec sp_configure "statement pipe active" , 0

exec sp_configure "errorlog pipe max messages" , 500

exec sp_configure "deadlock pipe max messages" , 500

exec sp_configure "statement pipe max messages" , 0

exec sp_configure "sql text pipe max messages" , 0

exec sp_configure "plan text pipe max messages" , 0

exec sp_configure "lock timeout pipe max messages" , 500

exec sp_configure "max SQL text monitored" , 2048

exec sp_configure "FIPS login password encryption" , 1

exec sp_configure "net password encryption reqd" , 1

exec sp_configure "enable compression" , 1

exec sp_configure "compression info pool size" , 32768

exec sp_configure "enable xml" , 1

exec sp_configure "enable unicode normalization" , 0

exec sp_configure "enable surrogate processing" , 0

exec sp_configure "disable character set conversions" , 0

exec sp_configure "enable unicode conversions" , 1

exec sp_configure "default character set id" , 190

exec sp_configure "default language id" , 0

exec sp_configure "maximum job output" , 16777216

exec sp_configure "maximum job output" , 16384

A size of at least 512 MB memory is recommended for the procedure

cache. Depending on your system's workload and the available

memory, this may need to be increased:

> procedure cache size >= 262144

The statement cache needs to have at least 100 MB:

> statement cache size >= 51200

> lock scheme = datarows

> select on syscomments.text = 1

> row lock promotion HWM = 2147483647

> row lock promotion LWM >= 2147483646

> number of locks >= 1000000

> lock wait period = 1800

> lock spinlock ratio = 40

You need to set the lock hash table size according to the number

of locks. It is recommended to set the lock hash table size to at

least the number of locks in full millions times 8192. The value of

this parameter should be a power of two, otherwise ASE will round

it up to the next power of two and show a warning message.

> lock hashtable size >= @number of locks@ / 1000000 *

8192

> lock hashtable size >= 8192

exec sp_configure "lock hashtable size",40960

The following settings affect memory usage of SAP ASE. The values

given are required to allow SAP applications to run on SAP

ASE:

exec sp_configure "number of open objects", 40000

exec sp_configure "number of open indexes", 30000

exec sp_configure "number of open partitions",30000

exec sp_configure "number of alarms",1000

exec sp_configure "number of aux scan descriptors",1024

exec sp_configure "number of devices",200

exec sp_configure "disk i/o structures",4096

exec sp_configure "kernel resource memory",32768

exec sp_configure "heap memory per user",49152

exec sp_configure "size of unilib cache",512000

The number of user connections needs to be adapted to your

system size.

Recommendation for SAP NetWeaver ABAP: [total number of work

processes * 4]

Recommendation for SAP NetWeaver Java: [number of server nodes *

50]

In both cases, use a minimum of 200 user connections:

> number of user connections >= 200

exec sp_configure 'number of user connections', 500

CPU grace time has to be set to 1000.

> cpu grace time = 1000

The following settings are required for network

communication:

> max network packet size = 16384

> default network packet size = 16384

> additional network memory >= 10485760

For the recovery interval, note that the parameter name is

misleading as the interval is based on an estimate of 1000 pages

per minute, whereas the real rate is approximately 10x higher. The

value given here represents roughly a 5-minute recovery

window:

> recovery interval in minutes <= 60

> print recovery information = 1

Additional settings that are requirements or recommended for

running SAP applications on SAP ASE:

exec sp_configure "disable varbinary truncation",1

exec sp_configure "allocate max shared memory",1

exec sp_configure "optimizer level",ase_current

exec sp_configure "enable literal autoparam",0

exec sp_configure "enable semantic partitioning", 1

exec sp_configure "permission cache entries",128

exec sp_configure "enable java",0

exec sp_configure "deadlock checking period",500

exec sp_configure "read committed with lock",0

exec sp_configure "auto query tuning",0

exec sp_configure "max query parallel degree",1

exec sp_configure "number of histogram steps",100

exec sp_configure "optimization goal",allrows_mix

exec sp_configure "optimization goal = {allrows_mix |

sap_oltp}

exec sp_configure "optimization goal = allrows_mix [REC:BI]

exec sp_configure "histogram tuning factor", 20

exec sp_configure "capture compression statistics", 0

exec sp_configure "print deadlock information",1

exec sp_configure "min pages for parallel scan",60000

exec sp_configure "cpu accounting flush interval",5000000

exec sp_configure "i/o accounting flush interval",5000000

exec sp_configure "session tempdb log cache size",32768

exec sp_configure "number of large i/o buffers",32

exec sp_configure "enable housekeeper GC",5

exec sp_configure "sysstatistics flush interval", 5

exec sp_configure "sysstatistics flush interval >= 1

exec sp_configure "global cache partition number", 8

exec sp_configure "number of oam trips",1

exec sp_configure "number of index trips",1

exec sp_configure "user log cache spinlock ratio", 5

exec sp_configure "default exp_row_size percent", 3

exec sp_configure "housekeeper free write percent", 10

exec sp_configure "number of pre-allocated extents", 32

exec sp_configure "SQL Perfmon Integration = 0 [REC:WIN]

exec sp_configure "number of network tasks",4 [REC:LNX]

exec sp_configure "number of network tasks = 4 [REC:SOL]

exec sp_configure "number of network tasks = 4 [REC:HPUX]

exec sp_configure "number of network tasks = 4 [REC:AIX]

exec sp_configure "number of network tasks = 1 [REC:WIN]

Stack size and stack guard size need to be set to the following

values, depending on your operating system:

> stack size >= 151552

> stack size >= 194560 [REQ:AIX]

> stack size >= 409600 [REQ:HPUX]

> stack guard size = 16384

> stack guard size = 4096 [REQ:WIN]

Data Caches

Depending on the size and the workload, you should consider a

larger default data cache, a separate large I/O cache pool, and a

separate cache for the database log:

{ Data Caches

> default data cache >= 409600

}

exec sp_cacheconfig 'default data cache', '62G'

Logging Error Messages

Using the stored procedure 'sp_altermessage', you can enable

logging of certain error messages. Call the following command in

isql:

sp_altermessage , 'with_log', 'true'

The current setting for a specific message number can be checked

using the following SQL command:

SELECT CASE WHEN dlevel & 128 = 0 THEN 'false' ELSE 'true'

END from master..sysmessages where error =

{ Messages

Message number 701 is raised when the procedure cache size is

insufficient to run a request.

> 701 = 'true'

Message number 1105 is raised when it is not possible to

allocate additional space to store data or logs:

> 1105 = 'true'

Message number 2901 is raised when the stack limit has been

exceeded:

> 2901 = 'true'

Message number 12205 is raised when a lock could not be set

within the configured wait time:

> 12205 = 'true'

}

Database Options

Database options are set using the stored procedure sp_dboption.

Set the following options for the databases and saptools. The

current settings can be checked using the stored procedure

sp_helpdb.

{ DB Options

> ddl in tran = 1 [REQ; S:@SID]

> ddl in tran = 1 [REQ; S:saptools]

> ddl in tran = 1 [REQ; S:tempdb]

> ddl in tran = 1 [REQ; S:saptempdb]

> allow nulls by default = 1 [REQ; S:@SID]

> allow nulls by default = 1 [REQ; S:saptools]

> allow nulls by default = 1 [REQ; S:tempdb]

> allow nulls by default = 1 [REQ; S:saptempdb]

> allow wide dol rows = 1 [REQ; S:@SID]

> allow wide dol rows = 1 [REQ; S:saptools]

> allow wide dol rows = 1 [REQ; S:tempdb]

> allow wide dol rows = 1 [REQ; S:saptempdb]

> no free space acctg = 0 [REQ; S:@SID]

> no free space acctg = 0 [REQ; S:saptools]

> unique auto_identity index = 0 [REQ; S:@SID]

> unique auto_identity index = 0 [REQ; S:saptools]

use AFD

exec master..sp_dboption AFD, 'ddl in tran',true

exec master..sp_dboption saptools, 'ddl in tran',true

exec master..sp_dboption tempdb, 'ddl in tran',true

exec master..sp_dboption saptempdb, 'ddl in tran',true

exec master..sp_dboption AFD, 'allow nulls by

default',true

exec master..sp_dboption saptools, 'allow nulls by

default',true

exec master..sp_dboption tempdb, 'allow nulls by

default',true

exec master..sp_dboption saptempdb, 'allow nulls by

default',true

exec master..sp_dboption AFD, 'allow wide dol

rows',true

exec master..sp_dboption saptools, 'allow wide dol rows',true

exec master..sp_dboption tempdb, 'allow wide dol

rows',true

exec master..sp_dboption saptempdb, 'allow wide dol rows',true

exec master..sp_dboption AFD, 'no free space

acctg',false

exec master..sp_dboption saptools, 'no free space acctg',false

exec master..sp_dboption AFD, 'auto_identity

index',false

exec master..sp_dboption saptools, 'auto_identity

index',false

use

PID

exec master..sp_dboption PID, 'ddl in

tran',true

exec master..sp_dboption saptools, 'ddl in

tran',true

exec master..sp_dboption tempdb, 'ddl in

tran',true

exec master..sp_dboption saptempdb, 'ddl in

tran',true

exec master..sp_dboption PID, 'allow nulls by

default',true

exec master..sp_dboption saptools, 'allow nulls

by default',true

exec master..sp_dboption tempdb, 'allow nulls by

default',true

exec master..sp_dboption saptempdb, 'allow nulls

by default',true

exec master..sp_dboption PID, 'allow wide dol

rows',true

exec master..sp_dboption saptools, 'allow wide

dol

rows',true

exec master..sp_dboption tempdb, 'allow wide dol

rows',true

exec master..sp_dboption saptempdb, 'allow wide

dol

rows',true

exec master..sp_dboption PID, 'no free space

acctg',false

exec master..sp_dboption saptools, 'no free

space

acctg',false

exec master..sp_dboption PID, 'auto_identity

index',false

exec master..sp_dboption saptools,

'auto_identity

index',false

Set the following option for database :

> deallocate first text page = 1 [REC; S:@SID]

exec master..sp_dboption PID, 'deallocate first text page',true

It is recommended that you set the 'abort tran on log full

option', but this is not a requirement:

> abort tran on log full = 1 [REC; S:@SID]

Page compression can be set for all databases. This is strongly

recommended for the SAP database:

> page compression = 1 [REC; S:@SID]

exec master..sp_dboption PID, 'page compression',true

For productive use, set the options for the database as

follows:

> enforce dump tran sequence = 1 [REQ; S:@SID]

> full logging for all = 1 [REQ; S:@SID]

> select into/bulkcopy/pllsort = 0 [REQ; S:@SID]

> select into/bulkcopy/pllsort = 1 [REQ; S:tempdb]

> select into/bulkcopy/pllsort = 1 [REQ; S:saptempdb]

> trunc log on chkpt = 0 [REQ;

S:@SID]

exec sp_dboption PID,'enforce dump tran sequence',true

exec sp_dboption PID,'full logging for all',true

exec sp_dboption PID,'trunc log on chkpt',false

Set the following option for database 'sybsystemdb':

> trunc log on chkpt = 1 [REC; S:sybsystemdb]

}

exec sp_dboption sybsystemdb,'trunc log on chkpt',true

Table Options

Table options are set using the stored procedure

sp_chgattribute. To check the current settings of a table, use

stored procedure sp_help.

The following tables are known to be volatile. If

'concurrency_opt_threshold' is set to '-1', the ASE optimizer will

always perform an index scan without consideration of the table's

statistics. More information on this can be found in SAP Note

2049506.

{ Table Options

sp_chgattribute

'AFD.SAPSR3.ARFCSSTATE',"concurrency_opt_threshold",-1