下载文档原格式
Oracle 9i 和10g 高性能调优介绍让我们从安装所需要的检查的事物开始来讨论oracle的优化。
调优环境调优的环境是什么？是一个你的调优努力能起作用的一种环境。
调整oralce数据库的所必需的东西⏹好多软件工具⏹训练有素的人⏹分段的测试环境⏹生产环境的一个副本○真实的和期望的生产环境。
在成长快速或者需求改变时，这些环境是经常不同的。
○经可能的使得数据库的大小/内容一致。
在不能完全满足这样的要求时，至少开发和测试数据库应该同生产数据库成比例○统计表是一样的吗？统计表可以拷贝，或者同生产数据库使用同样的时间间隔执行可用工具调整和监控数据库的优秀软件很多。
OEM有很多非常有用的小组件。
Spotlight擅长于对繁忙系统的视频和信息进行实时监控。
两者对调整数据库物理和SQL代码的性能分析都是非常有用的。
也有许多其他的工具可用。
在调整过程中最重要的工具是developer和administrators。
那是为什么你读这本书的原因。
最好的软件工具也意味着是最昂贵的，当然这也不是说较为便宜的工具是无用的了。
通常，越贵的软件为你所做的事情越多。
然而，有时候自动为你做了某些工作，你不理解其内部机制。
你的工具集未必比经过良好训练，经验丰富的数据库管理员和开发人员作得更好。
训练有素的人好的训练有其恰当的地方。
作为系统管理员或者开发者，数据库管理员趋向于有root权限。
每一种训练都有其优缺点。
开发者除了编码SQL，创建数据模块之外，所了解的知识越来越多。
系统管理员对unix之类的操作系统知识具有广泛的了解，关注的是oracle数据库的物理存储的调优。
开发者关注的是数据库模型和创建更为高效的SQL代码方面的优化。
幸的是，事情不总是如此。
有时候开发者趋向于把调优SQL代码和数据模型负担看着是数据库管理的负责范围。
这样就会导致混乱。
分段的环境你需要尽可能多的实验环境。
作为DBA，你不能期望在一个在线的生产数据库上进行调优工作。
WebLogic9和10集群安装配置过程2011-04-21 10:25weblogic的集群很早以前做过，当时写了一份文档，最近真好又有机会实验了Weblogic 10.3的集群配置，两相比较，同时对照着文档过了一遍，确定下文对weblogic9和10都适用。
截了图也方便新手学习。
环境平台：两台Windows 2003 SP2（SP版本要一样），必须位于同一网段，并且必须是IP广播(UDP)可到达的软件：Weblogic9.1拓扑在A机上建立一个Administrator Server，作为管理节点；在AB机上分别建立Managed Server，加入集群appCluster，作为应用程序的运行环境；在B机上分别建立Proxy Server，作为提供外部访问的服务地址。
机器配置信息角色备注app1 IP:*.*.*.*PORT:7001AdminServer 管理服务器app1 IP:*.*.*.*PORT:7002ManagedServer受管服务器app2 IP:*.*.*.*PORT:7002ManagedServer受管服务器app2 IP:*.*.*.*PORT:80Proxy Server 代理服务器安装过程在机器A上创建新的Domain选择“Tool- Configuration Wizard”，单击“下一步”按钮选择安装域源选择安装“WebLogic Server”，单击“下一步”按钮配置管理员用户名和密码输入Weblogic管理员管理服务器（AdminServer）的用户名和密码，在此以“weblogic”作用登录管理服务器的用户名和密码，单击“下一步”按钮配置域启动模式和JDK选择“生产模式”和“JRockit SDK”后，单击“下一步”按钮选择是否自定义环境和服务设置选择“是”后，单击“下一步”按钮配置管理服务器输入Administrator Server的名称，监听地址，监听端口，如果需要SSL支持的话可以在“SSL enabled”后面的复选取框上打勾，配置SSL监听端口，单击“下一步”按钮配置受管理服务器（代理服务器实际上也是一个受管理服务器）输入Managed Server的名称，监听地址，监听端口（注意同一台机器的端口不要冲突），如果需要SSL支持的话，可以在“SSL enabled”复选框上打勾，配置SSL监听端口。
Weblogic参数调优一、调优背景：应银监会总会需求，需对银监会派出机构客户风险监测预警系统进行1轮剔除网络环境的性能测试（在山东试运行阶段的性能测试，由于网络瓶颈较大，造成性能指标较差）。
由于时间紧张，只是匆忙搭建了测试环境，Weblogic、DB2等的参数均是默认设置，并未进行任何调优。
默认设置在系统使用人数不多的情况性能尚可，但当使用人数较多，或者进行大并发的性能测试时，则会显得捉襟见肘。
如：在本次测试过程中，在集团关联处的50并发，就会造成系统崩溃。
如图可见：在集团关联并发50的时候，队列长度已经高达103，空闲线程数已经为0，同时吞吐量极低，由此造成系统崩溃。
通过分析，查找到原因在于：JVM分配内存过少；线程池、连接池里分配的线程数以及容量过低，通过调优后解决了问题二、调优思路：一般来讲，J2EE应用架构环境下的系统调优，首先我们一般会从应用程序出发，去审核代码，做到代码级的优化，然后再调整应用服务器(BEA WebLogic8.1)和数据库(DB2)的参数，最后当然是调整操作系统和网络的性能(包括硬件升级)。
这是一种MDA的先进做法。
诚然，在许多项目中，不可能完全按照这个思路来做，我们把目标首先定位在应用系统所在的应用服务器(BEA WebLogic8.1)上，通过对BEA WebLogic8.1的参数进行设置，使WebLogic8.1能够在最优化的环境中去运行其系统，然后对DB2数据的参数进行优化设置，最后进行性能测试再找出导致性能瓶颈所在的SQL代码或JA V A程序，考量其修改的可行性，并进行最终问题优先级认定，与瓶颈模块进行协商解决性能问题。
当然，大多数时候都出现了性能问题后才想到调优，而且一般都是先进行系统参数调整，实在解决不了才会对代码进行检查。
实际上，我们应当将代码级的调优放在应用设计时来做，测试生产时修改代码将是一件极其痛苦的事情。
而本次测试由于时间紧张，只对Weblogic进行了参数调优，现将调优步骤进行说明：1．JVM调整：(1)首先，在D:\bea\user_projects\domains\mydomain下用编译模式打开startWebLogic.cmd，查找代码：call "%WL_HOME%\common\bin\commEnv.cmd"(windows)或者call "%WL_HOME%\common\bin\commEnv.sh"(linux/aix)找到后，在相应路径用编译模式打开该文件：(2)接着要弄清楚JDK厂商（是bea，还是sun、IBM等）set JA V A_VENDOR=BEA（此处说明应用的为BEA的JDK）(3)之后查找weblgoic运行模式：set PRODUCTION_MODE=默认是开发模式（线程计数默认为15）即为空，在上线时，应将其改为产品模式（线程计数默认为25）：set PRODUCTION_MODE=ture(4)然后需修改分配给JVM的内存（默认为96M）：因已确定厂商为BEA，运行模式为产品模式，所以找到：:beaif "%PRODUCTION_MODE%" == "true" goto bea_prod_modeset JA V A_VM=-jrockitset MEM_ARGS=-Xms1024m -Xmx1024mset JA V A_OPTIONS=%JA V A_OPTIONS% -Xverify:nonegoto continue:bea_prod_modeset JA V A_VM=-jrockitset MEM_ARGS=-Xms1024m -Xmx1024m（此处即为分配给JVM的内存范围）goto continue可根据系统需要，修改分配的内存数注：如为32位的操作系统，则最大支持分配2G内存2．线程池调整：打开Weblogic，在路径：mydomain->服务器->myserver->右键打开“查看执行队列” weblogic.kernel.Default调整如下指标：(1) 线程计数—分配给此队列的线程数，建议值为: 100(2) 线程数增加—在出现溢出时，要添加到队列中的线程数，建议值为: 50(3) 最大线程数—此队列可以拥有的最大线程数。
Weblogic性能调优经验WEBLOGIC性能调优方案1.查看当前线程池大小：登录WEBLOGIC控制台，选择服务器－某服务器－监视－性能，可查看到当前最大线程数量。
由于WEBLOGIC9取消了在页面进行调整线程大小的功能，需要在后台进行修改。
2.修改线程池大小：进入服务器的目录D:\bea\user_projects\domains\base_domain\config，使用编辑器打开config.xml文件，找到如下段落：<server><name>AdminServer</name><listen-address>168.5.130.34</listen-address>z</server>在</server>上面添加如下两行：默认10<self-tuning-thread-pool-size-min>1000</self-tuning-thread-pool-siz e-min><self-tuning-thread-pool-size-max>1000</self-tuning-thread-pool-siz e-max>添加后的结果：<server><name>AdminServer</name><listen-address>168.5.130.34</listen-address><self-tuning-thread-pool-size-min>1000</self-tuning-thread-pool-size-min><self-tuning-thread-pool-size-max>1000</self-tuning-thread-pool-size-max></server>按上面的步骤，继续找到ProxyServer,以及所有MANAGE受管机的<server>段落，分别添加上面的两行语句。
关于Oracle 10g数据库系统性能优化与调整的研究【摘要】当前Oracle数据库应用极为广泛，但是不断增加的数据量和访问量等因素导致数据库系统性能的降低，出现吞吐量降低、响应时间变长等相关的数据库性能问题，这就产生了对数据库系统的优化的需求。
如何对数据库系统进行优化和调整，才能获得更快的响应时间、更大的吞吐量、以及更少的资源占用呢？在本文中笔者就Oracle 10g数据库系统性能优化与调整进行了相关方面的研究。
【关键词】Oracle 10g数据库；系统性能；优化与调整；SQL语句优化1.前言IT系统随着支持用量的增长和新业务的不断扩展，数据处理量大量增加，业务处理模式日趋复杂，必然导致主机CPU和I/O占用不断呈线性增加。
因此，充分使用先用硬件的处理能力对于保护投资至关重要。
Oracle数据库是现在使用最广泛的大型数据库之一，对于规模较大、需要处理的用户数目较多的数据库系统来说工作效率是最重要的指标之一。
在实际应用中，不断增加的数据量和访问量都会导致数据库系统性能将会下降，这就产生了对数据库系统的优化的需求，以实现更快的响应时间、更大的吞吐量、以及更少的资源占用等。
2.影响数据库系统性能的因素在Oracle数据库的实际应用中，其系统性能主要受到数据库运行方面的因素如服务器性能、数据库配置、网络I/O、应用程序实现等因素的影响，各个因素的具体影响情况如表1所示。
表1 影响数据库系统性能的相关因素3.数据库系统性能优化与调整数据库系统的优化措施是通过有目的更改系统的一个或多个组件途径使其满足一个或多个目标的过程。
Oracle数据库的性能优化与调整应从影响系统性能的因素着手，实现对数据库的体系结构、软件结构、具体的业务和技术等方面的优化效果，使得数据库系统实现更快的响应时间、更大的吞吐量、以及更少的资源占用等性能。
2.1内存区调整与优化Oracle数据库实例的内存结构主要包括两个方面，分别为SGA和PGA。
1Oracle 8i参数2Oracle 9i 与 10g参数差异2.1 Server Parameters for Oracle 9i and 10g Green = New in 10g∙Red= Valid in 9i but dropped (or hidden) in 10g∙Bold = Static Parameter - change in Pfile/SPfile∙Normal= Dynamic Parameter - change in Pfile/SPfile/ALTER SYSTEM/SESSION∙∙PARAMETER DEscrīptION∙------------------------------ ----------------------------------------∙ACTIVE_INSTANCE_COUNT = int Active instances in the cluster∙AQ_TM_PROCESSES = int Number of AQ Time Managers to start∙ARCHIVE_LAG_TARGET =int Max no. seconds of redos the standby could lose∙asm_diskgroups = string Disk groups to mount automatically∙asm_diskstring = string Disk set locations for discovery∙asm_power_limit = int Number of processes for disk rebalancing∙AUDIT_FILE_DEST = 'directory'Directory in which auditing files are to reside∙AUDIT_SYS_OPERATIONS = {TRUE|FALSE}∙AUDIT_TRAIL = {NONE | FALSE | DB | TRUE | OS} Enable system auditing 9i∙AUDIT_TRAIL = {NONE | DB | DB_EXTENDED| OS} Enable system auditing 10g ∙∙BACKGROUND_CORE_DUMP = {PARTIAL | FULL}∙BACKGROUND_DUMP_DEST = 'path or directory'∙BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED∙BITMAP_MERGE_AREA_SIZE = int Memory for BITMAP MERGE∙BLANK_TRIMMING = {TRUE|FALSE}∙CIRCUITS = int∙CLUSTER_DATABASE = {TRUE|FALSE} If TRUE startup in cluster database mode∙CLUSTER_DATABASE_INSTANCES = int∙CLUSTER_INTERCONNECTS = ipaddr [:ipaddr...] Interconnects for RAC use∙COMMIT_POINT_STRENGTH = int∙COMPATIBLE = release_number[CHAR: 9.2.0.0.0]∙CONSTRAINT[S] = { IMMEDIATE | DEFERRED | DEFAULT }∙CONTROL_FILE_RECORD_KEEP_TIME = int Time in Days∙CONTROL_FILES =filename [,filename [...] ]∙CORE_DUMP_DEST = 'text'∙CPU_COUNT = int∙CREATE_BITMAP_AREA_SIZE = int∙CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name' } [NOOVERRIDE]∙CURSOR_SHARING = {SIMILAR | EXACT | FORCE}∙CURSOR_SPACE_FOR_TIME = {TRUE|FALSE}∙CURRENT_SCHEMA = schema Change the current schema of the session∙∙DB_2k_cache_size = int bytes Size of cache for 2K buffers∙DB_4k_cache_size = int bytes Size of cache for 4K buffers∙DB_8k_cache_size = int bytes Size of cache for 8K buffers∙DB_16k_cache_size = int bytes Size of cache for 16K buffers∙DB_32k_cache_size = int bytes Size of cache for 32K buffers∙DB_BLOCK_BUFFERS = int Deprecated in favour of DB_CACHE_ SIZE∙DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED∙DB_BLOCK_CHECKSUM = {TRUE | FALSE}∙DB_BLOCK_SIZE = int[bytes] Do not alter after db creation∙DB_CACHE_ADVICE = {ON | READY | OFF}∙DB_CACHE_SIZE = int [bytes]∙DB_CREATE_FILE_DEST = directory∙DB_CREATE_ONLINE_LOG_DEST_n = directory(where n = 1-5)∙Default locn for Oracle-managed control files and online redo logs.∙DB_DOMAIN = domain_name Directory part of global database name∙DB_FILE_MULTIBLOCK_READ_COUNT = int∙DB_FILE_NAME_CONVERT = [(]'dbfile1' , 'dbfile2'...[)]∙Datafile name convert patterns and strings∙for standby/clone db [old string, new string]∙DB_FILES = int∙db_flashback_retention_target= int Max Flashback Database log retention (minutes)∙DB_KEEP_CACHE_SIZE = int [bytes]∙DB_NAME = database_name∙db_recovery_file_dest = string Default database recovery file location∙db_recovery_file_dest_size = int Database recovery files size limit∙DB_RECYCLE_CACHE_SIZE = int [bytes]∙db_unique_name = string Database Unique Name∙DB_WRITER_PROCESSES = int Number of background database writer ∙processes to start∙DBLINK_ENCRYPT_LOGIN = {TRUE|FALSE} Enforce password encryption for distributed login∙DBWR_IO_SLAVES = int∙DDL_WAIT_FOR_LOCKS= {TRUE|FALSE} Disable NOWAIT DML lock acquisitions∙DG_BROKER_CONFIG_FILE n = filename(where n = 1 or 2)∙DG_BROKER_START = {TRUE|FALSE}∙DISK_ASYNCH_IO = {TRUE|FALSE}∙DISPATCHERS = 'dispatch_clause' (see SQL ref manual for detail)(MTS_Dispatchers in Ora 8/9)∙DISTRIBUTED_LOCK_TIMEOUT = int∙DML_LOCKS = int One for each table modified in a transaction∙DRS_START = {TRUE|FALSE} Start DG Broker monitor (DMON process)∙∙ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}∙ENQUEUE_RESOURCES = int Resources for enqueues∙EVENT = debug_string Debug event control∙∙FAL_CLIENT = string Fetch archive log Client∙FAL_SERVER = string Fetch archive log Server∙FAST_START_IO_TARGET = int Upper bound on recovery reads(Deprecated)∙FAST_START_MTTR_TARGET = int∙FAST_START_PARALLEL_ROLLBACK = {FALSE | LOW | HIGH}∙Max number of parallel recovery slaves∙FILE_MAPPING = {TRUE|FALSE}∙FILEIO_NETWORK_ADAPTERS = char Network Adapters for File I/O∙FILESYSTEMIO_OPTIONS = {none | setall | directIO | asynch}∙FIXED_DATE = {'YYYY_MM_DD_HH24_MI-SS' | 'date in default format'}∙Fix SYSDATE value for debugging∙∙GC_FILES_TO_LOCKS = '{file_list=lock_count[!blocks][EACH][:...]}'∙RAC/OPS - lock granularity number of∙global cache locks per file (DFS)∙GCS_SERVER_PROCESSES= int Number of background gcs server processes to start∙GLOBAL_CONTEXT_POOL_SIZE = {1 MB | int MB}∙GLOBAL_NAMES = {TRUE | FALSE} Enforce that database links have same∙name as remote database∙∙HASH_AREA_SIZE = int Size of in-memory hash work area (Shared Server)∙HASH_JOIN_ENABLED = {TRUE|FALSE}∙HI_SHARED_MEMORY_ADDRESS = int SGA starting address (high order 32-bits∙on 64-bit platforms)∙HS_AUTOREGISTER = {TRUE | FALSE} Enable automatic server DD updates in HS∙agent self-registration∙∙IFILE = parameter_file_name Include file in init.ora∙INSTANCE = int Connect to a different RAC instance∙INSTANCE_GROUPS = group_name [,group_name ... ]∙INSTANCE_NAME = instance_id∙INSTANCE_NUMBER = int∙INSTANCE_TYPE = {RDBMS|ASM} Type of instance to be executed∙RDBMS or Automated Storage Management ∙ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED}∙∙JAVA_MAX_SESSIONSPACE_SIZE = int [bytes]∙JAVA_POOL_SIZE = int [bytes]∙JAVA_SOFT_SESSIONSPACE_LIMIT = int∙JOB_QUEUE_PROCESSES = int∙∙LARGE_POOL_SIZE = int [bytes]∙LICENSE_MAX_SESSIONS = int Maximum number of non-system user sessions∙(concurrent licensing)∙LICENSE_MAX_USERS = int Maximum number of named users that can be created∙(named user licensing)∙LICENSE_SESSIONS_WARNING = int Warning level for number of non-system ∙user sessions∙LOCAL_LISTENER = network_name Define which listeners instances register with∙LOCK_NAME_SPACE = namespace Used for generating lock names for standby/primary database∙assign each a unique name space∙LOCK_SGA = {TRUE | FALSE}∙LOG_ARCHIVE_CONFIG= [SEND|NOSEND] [RECEIVE|NORECEIVE] [ DG_CONFIG]∙LOG_ARCHIVE_DEST = string∙LOG_ARCHIVE_DEST_n = {null_string |∙{LOCATION=local_pathname | SERVICE=tnsnames_service}∙[MANDATORY | OPTIONAL] [REOPEN[=integer]]}∙LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER} (n = 1-10)∙LOG_ARCHIVE_DUPLEX_DEST = string∙LOG_ARCHIVE_FORMAT = string[CHAR: "MyApp%S.ARC"]∙LOG_ARCHIVE_LOCAL_FIRST= {TRUE|FALSE} Establish EXPEDITE attribute default value∙LOG_ARCHIVE_MAX_PROCESSES = int∙LOG_ARCHIVE_MIN_SUCCEED_DEST = int Minimum number of archive destinations∙that must succeed∙LOG_ARCHIVE_START = {TRUE | FALSE}∙LOG_ARCHIVE_TRACE = int Tracing level for Archive logs∙LOG_BUFFER = int bytes Redo circular buffer size∙LOG_CHECKPOINT_INTERVAL = int Checkpoint threshold, # redo blocks ∙LOG_CHECKPOINT_TIMEOUT = int Checkpoint threshold, maximum time interval between∙checkpoints in seconds∙LOG_CHECKPOINTS_TO_ALERT = {TRUE|FALSE} Log checkpoint begin/end to alert file∙LOG_FILE_NAME_CONVERT = ['old string','new string']∙Convert patterns/strings for standby/clone db ∙LOG_PARALLELISM = int Number of log buffer strands∙LOGMNR_MAX_PERSISTENT_SESSIONS = int Maximum no of threads to mine∙∙MAX_DISPATCHERS = int Max number of dispatchers∙MAX_DUMP_FILE_SIZE = {size bytes|UNLIMITED} [DEFERRED]∙MAX_ENABLED_ROLES = int Max number of roles a user can have enabled∙MAX_ROLLBACK_SEGMENTS = int Max number of rollback segments in SGA cache∙MAX_SHARED_SERVERS = int Max number of shared servers]∙mts_circuits = int Max number of circuits (10g see CIRCUITS)∙mts_dispatchers Specifications of dispatchers (10g see DISPATCHERS)∙MTS_LISTENER_ADDRESS Address(es) of network listener [CHAR]∙mts_max_dispatchers Max number of dispatchers (10g see MAX_DISPATCHERS)∙mts_max_servers Max number of shared servers (10g see MAX_SHARED_SERVERS)∙MTS_MULTIPLE_LISTENERS= {TRUE|FALSE} Are multiple listeners enabled?∙MTS_SERVERS = int Number of shared servers to start up [NUMBER] ∙mts_service = string Service supported by dispatchers [CHAR]∙mts_sessions = int max number of shared server sessions [NUMBER]∙∙nls_calendar ='string' NLS calendar system name (Default=GREGORIAN) ∙nls_comp = {BINARY | ANSI} NLS comparison, Enterprise Edition∙nls_currency ='string' NLS local currency symbol∙nls_date_format ='format' NLS Oracle date format∙nls_date_language =language NLS date language name (Default=AMERICAN)] ∙nls_dual_currency = currency_symbol∙nls_iso_currency = territory Override the default set by NLS_TERRITORY∙nls_language = language NLS language name (session default)∙nls_length_semantics = {BYTE|CHAR}} Default when creating new columns∙nls_nchar_conv_excp = {TRUE|FALSE} Raise an exception instead of∙allowing an implicit conversion∙nls_numeric_characters ="decimal_character group_separator"∙nls_sort = {BINARY |linguistic_def} Case-sensitive or insensitive sort∙linguistic_def may be BINARY, BINARY_CI, BINARY_AI,∙GERMAN, GERMAN_CI, etc∙nls_territory = territory Territory name (country settings)∙nls_time_format =time_format Time format∙nls_time_tz_format = time_format Time with timezone format∙nls_timestamp_format = time_format Timestamp format∙nls_timestamp_tz_format = time_format Timestamp with timezone format∙∙O7_DICTIONARY_ACCESSIBILITY = {TRUE | FALSE} Allow Dictionary Access (as in Ora V7 )∙OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED Space for application objects Max∙OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED Space for application objects Min∙OLAP_PAGE_POOL_SIZE =int bytes∙OPEN_CURSORS = int Max # cursors per session∙OPEN_LINKS = int Max # open links per Session∙OPEN_LINKS_PER_INSTANCE = int Max # open links per instance∙OPTIMIZER_DYNAMIC_SAMPLING = int∙OPTIMIZER_FEATURES_ENABLE = {8.0.0|8.0.3|8.0.4|8.0.5|8.0.6|8.0.7|8.1.0|8.1.3|8.1.4|8.1.5|8.1.6|8.1.7|9.0.0|9.0.1|9.2.0}∙Configure qry optimiser based on an Oracle release No.∙OPTIMIZER_INDEX_CACHING = int Percent to cache (favour nested loop joins & IN-list)∙OPTIMIZER_INDEX_COST_ADJ = int Adjust the cost of index vs FTS∙OPTIMIZER_MAX_PERMUTATIONS = int Max join permutations per qry block ∙OPTIMIZER_MODE = [RULE | CHOOSE | FIRST_ROWS | ALL_ROWS]∙oracle_trace_collection_name =collection Name for use by Oracle TRACE∙oracle_trace_collection_path=path Path to .cdf & .dat files (ORACLE_HOME/otrace/admin/cdf)∙oracle_trace_collection_size =int bytes Max trace file size∙oracle_trace_enable = {TRUE|FALSE} Enable Oracle Trace∙oracle_trace_facility_name ={ORACLED | ORACLEE | ORACLESM | ORACLEC} TRACE event set∙oracle_trace_facility_path=path TRACE definition files: ORACLE_HOME/otrace/admin/fdf/∙OS_AUTHENT_PREFIX = prefix Prefix for auto-logon accounts [string]∙OS_ROLES = {TRUE|FALSE} Retrieve roles from the operating system∙∙PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE} Tune degree of parallelism∙PARALLEL_AUTOMATIC_TUNING = {TRUE|FALSE} Automatic tuning∙PARALLEL_EXECUTION_MESSAGE_SIZE = int bytes Message buffer size ∙PARALLEL_INSTANCE_GROUP = 'group' RAC: Limit instances used∙PARALLEL_MAX_SERVERS = int∙PARALLEL_MIN_PERCENT = int Min percent of threads required for∙parallel query∙PARALLEL_MIN_SERVERS = int∙PARALLEL_SERVER = [TRUE | FALSE] Startup in parallel server mode∙PARALLEL_SERVER_instances = int No. of instances (used for sizing SGA)∙PARALLEL_THREADS_PER_CPU = int∙PARTITION_VIEW_ENABLED = {TRUE|FALSE} Deprecated (use partition TABLES)∙PGA_AGGREGATE_TARGET = int bytes Automatically size the SQL working area∙plsql_code_type ={INTERPRETED | NATIVE} Code-type∙PLSQL_COMPILER_FLAGS = { [DEBUG | NON_DEBUG] [INTERPRETED | NATIVE] }∙plsql_debug ={TRUE | FALSE}∙plsql_native_c_compiler∙plsql_native_library_dir = ['Path_to_directory']∙plsql_native_library_subdir_count = int∙plsql_native_linker =path Path to linker∙plsql_native_make_file_name =path Pathname of make file∙plsql_native_make_utility =path Pathname of make utility∙plsql_optimize_level Optimize level∙PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED]∙plsql_warnings =string Compiler warnings settings∙See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS∙PRE_PAGE_SGA = {TRUE|FALSE} Pre-page sga for process∙PROCESSES = int User processes∙∙QUERY_REWRITE_ENABLED = {FORCE | TRUE | FALSE} [DEFERRED | NOOVERRIDE]∙QUERY_REWRITE_INTEGRITY = {ENFORCED | TRUSTED | STALE_TOLERATED}∙∙RDBMS_SERVER_DN = Distinguished Name∙READ_ONLY_OPEN_DELAYED = {TRUE | FALSE} Delay opening read_only files until first access∙RECOVERY_PARALLELISM = int Server processes to use for parallel recovery∙REMOTE_ARCHIVE_ENABLE = [RECEIVE[,SEND] | FALSE | TRUE]∙Enable or disable sending archived redo logs to/from remote destinations∙REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}∙Remote-procedure-call dependencies mode∙∙REMOTE_LISTENER =network_name∙REMOTE_LOGIN_PASSWORDFILE ={NONE | SHARED | EXCLUSIVE} Usea password file∙REMOTE_OS_AUTHENT = {TRUE | FALSE} Allow non-secure remote clients to use∙auto-logon accounts∙REMOTE_OS_ROLES = {TRUE | FALSE} Allow non-secure remote clients to use os roles∙REPLICATION_DEPENDENCY_TRACKING = {TRUE | FALSE}∙RESOURCE_LIMIT = {TRUE | FALSE} Master switch for resource limit∙RESOURCE_MANAGER_PLAN = plan_name Turn on Resource Manager plan∙resumable_timeout =seconds Set resumable_timeout∙ROLLBACK_SEGMENTS = (rbs1 [, rbs2] ... )∙ROW_LOCKING = [ALWAYS | DEFAULT | INTENT]∙∙SERIAL_REUSE = {DISABLE | SELECT | DML | PLSQL | ALL} Cursor memmory management∙SERVICE_NAMES = db_service_name [,db_service_name [,...] ]∙SESSION_CACHED_CURSORS = int Session cursors to cache∙SESSION_MAX_OPEN_FILES = int Max no. of BFiles (LOB) each session can open∙SESSIONS = int Max no. of user and system sessions ∙SGA_MAX_SIZE =int bytes Initial SGA size∙sga_target= int bytes Target size of SGA∙SHADOW_CORE_DUMP = {PARTIAL | FULL | NONE} Include SGA in core file ∙SHARED_MEMORY_ADDRESS = int SGA starting address (platform specific)∙SHARED_POOL_RESERVED_SIZE = int bytes Reserved area of shared pool∙SHARED_POOL_SIZE = int Size of shared pool∙SHARED_SERVERS = int Number of shared servers to start up (MTS)∙SHARED_SERVER_SESSIONS = int Max number of shared server sessions ∙SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}∙smtp_out_server= server_clause utl_smtp server and port configuration parameter∙SORT_AREA_RETAINED_SIZE =int bytes[DEFERRED] UGA Memory to retain (Shared Server)∙SORT_AREA_SIZE = int bytes[DEFERRED] In-memory sort work area (Shared Server)∙SORT_MULTIBLOCK_READ_COUNT Obsolete in 9i∙SPFILE =spfile_name Parameter file∙sp_name =name Service Provider Name∙SQL92_SECURITY = {TRUE | FALSE} Require select privilege for update/delete∙SQL_TRACE = {TRUE | FALSE} Enable SQL trace∙sqltune_category =category Qualifier for applying hintsets∙SQL_VERSION =version Sql language version, for compatibility ∙STANDBY_ARCHIVE_DEST = 'filespec' Standby database archivelog destination∙STANDBY_FILE_MANAGEMENT = {MANUAL | AUTO}∙Automate file mmanagement on standby DB∙STAR_TRANSFORMATION_ENABLED = {TEMP_DISABLE | TRUE | FALSE}∙STATISTICS_LEVEL = {ALL | TYPICAL | BASIC} Collect Statistics∙streams_pool_size = int bytes Size of the streams pool∙∙TAPE_ASYNCH_IO = {TRUE | FALSE} Allow I/O requests to tape devices at the same time as CPU processing∙THREAD =int Redo thread to use (RAC)∙TIMED_OS_STATISTICS = int Gather OS statistics every x seconds ∙TIMED_STATISTICS = {TRUE | FALSE} Collect time statistics∙TIME_ZONE = '[+ | -] hh:mm'| LOCAL | DBTIMEZONE | 'time_zone_region'∙TRACE_ENABLED = {TRUE | FALSE} Trace execution path (Internal use only-Oracle support services)∙TRACEFILE_IDENTIFIER = "traceid" Trace file custom identifier∙TRANSACTION_AUDITING = {TRUE | FALSE} [DEFERRED]∙TRANSACTIONS = int Max. number of concurrent active transactions∙TRANSACTIONS_PER_ROLLBACK_SEGMENT = int∙∙UNDO_MANAGEMENT = {MANUAL | AUTO} Undo space management mode (Manual=rollback segs)∙UNDO_RETENTION = int Undo retention in second∙UNDO_SUPPRESS_ERRORS = {TRUE |FALSE} Suppress RBU errors in SMU mode∙UNDO_TABLESPACE =undoname Select an undo tablespace∙USE_INDIRECT_DATA_BUFFERS = {TRUE|FALSE} Configure SGA Memory cache for >4Gb RAM∙USE_PRIVATE_OUTLINES = {TRUE |FALSE |category_name }∙USE_STORED_OUTLINES = { TRUE |FALSE |category_name} [NOOVERRIDE] ∙USER_DUMP_DEST = 'directory_name' User process dump directory∙UTL_FILE_DIR Utl_file accessible directories list∙UTL_FILE_DIR ='Path1', 'Path2'..∙or∙UTL_FILE_DIR ='Path1' # Must be∙UTL_FILE_DIR ='Path2' # consecutive entries∙WORKAREA_SIZE_POLICY = {AUTO | MANUAL} Policy used to size SQL working areas2.2 Session & other ParametersThe following parameters are not initialization parameters:CONSTRAINT, CREATE_STORED_OUTLINES, CURRENT_SCHEMA, ERROR_ON_OVERLAP_TIME, FLAGGER, INSTANCE, ISOLATION_LEVEL , SQL_TRACE?, SKIP_UNUSABLE_INDEXES, TIME_ZONE, USE_PRIVA TE_OUTLINES, USE_STORED_OUTLINES.You cannot set values for these in the parameter file (PFile/SPfile)SQL_TRACE is an initialization parameter, but when changed in a user session, does not update V$PARAMETER.TRANSACTIONS can be changed in the parameter file or using ALTERSYSTEM...SCOPE=SPFILENotesAll Byte values can also be specified in K or M or Ge.g. you can enter 8388608 or 8192 K or 8MAll directory paths follow standard notation i.e UNIX 'quotes' or Windows "double quotes"The default value for many of these parameters does vary across Operating System platforms.To see the current value of any parameter:select name,value,descrīption,issys_modifiablefrom v$parameterwhere name='Some_Parameter'issys_modifiable, shows the type of parameter:FALSE = Static parameter that cannot change its value in the lifetime of the instance; shown in bold in the list above.IMMEDIATE = dynamic, can change the active instance as well as future database restarts. DEFERRED = dynamic, changes only affect subsequent sessions.'Internal' parameters (starting with _ ) should not be modified unless advised by Oracle Support. Changing a Parameter valueUse the ALTER SYSTEM command to set parameters:ALTER SYSTEM set parameter = value SCOPE = MEMORY;ALTER SYSTEM set parameter = value SCOPE = SPFILE;ALTER SYSTEM set parameter = value SCOPE = BOTH;-- In memory. This affects the database now; but will not remain after a restart.-- SpFILE. This does not change the instance immediately, but it will modify the SpFILE so will take effect after a restart.-- Memory and SpFILE - changes the current instance as well as the SpFILE.Some parameters can be modified immediately with ALTER SYSTEM, and some can be modified for a single session with ALTER SESSION. Static parameters must be modified with scope=SpFileRestoring default Parameter valuesFor string parameters, setting to an empty string will restore the default.ALTER SYSTEM SET parameter = '' scope=SpFILE;For any parameter the RESET option will restore the default.ALTER SYSTEM RESET parameter scope=SpFILE sid='*' ;noteWhen resetting a parameter, you must specify sid=mySid or sid='*' even for non-RAC instances. Managing the SpFILEIn Oracle9i and above, the SpFILE is stored in a binary format on the server. You no longer need to have a local copy to start the database remotely. This also means that changes made via ALTER SYSTEM may now persist across server restarts - without any need to remember to update a separate init.ora file.A PFILE (old style text init.ORA) can be created via the command:CREATE PFILE = 'pfilename' FROM SpFILE = 'filename';An SpFILE (binary) can be created via the command:CREATE SpFILE = 'filename' FROM PFILE ='pfilename';To create an SpFile, the database need not be started, the old pfile format is largely for backwards compatibility with older releases.If the system has both a pfile and an SpFile, then Oracle will always use the SpFile.If the system has both a generic init.ora and an SID specific parameter file, Oracle will use the SID specific file.SID specific:spfileSID.ora or initSID.oraGeneric:spfile.ora or init.oraThe location for all parameter files is:ORACLE_HOME\database (Windows)ORACLE_HOME/dbs (Unix/Linux)To startup a database with an SpFILE:SQL> startupTo startup a database with a PFILE, use the pfile= option :SQL> startup pfile=filenameExamplesALTER SYSTEM SET log_archive_format = "live%S.ARC" SCOPE = spfile;ALTER SYSTEM SET shared_pool_size=64M scope=bothSELECT name, value from v$parameter where name = 'spfile';show parameter spfile;New parameters in 9.2audit_sys_operations, dg_broker_start, dg_broker_config_file_n, file_mapping, filesystem_io_options, log_parallelism, olap_page_pool_size, optimizer_dynamic_sampling, statistics_level.New parameters in 10Gasm_diskgroups, asm_diskstring, asm_power_limit, db_flashback_retention_target, db_recovery_file_dest, db_recovery_file_dest_size, db_unique_name, ddl_wait_for_locks, fileio_network_adapters, gcs_server_processes, instance_type, ldap_directory_access, log_archive_config, log_archive_local_first, plsql_code_type, plsql_debug, plsql_optimize_level, plsql_warnings, resumable_timeout, sga_target, smtp_out_server, sp_name, sqltune_category, streams_pool_size."Happiness, wealth, and success are by-products of goal setting; they cannot be the goal themselves" - Denis Waitley, The Joy of WorkingRelatedV$PARAMETERV$SpPARAMETERALTER SYSTEMServer Parameters - Oracle Memory Architecture (Powerpoint)Oracle 8i Memory - older init.ora settings2.2.1 O7_DICTIONARY_ACCESSIBILITY说明: 主要用于从Oracle7 移植到Oracle8i。
Oracle 10g的内存调整报告SGA--(初始化参数sga_target)||__shared pool(初始化参数shared_pool_size) chunk链表结构构成Bucket | || |__library cache| || |__dictionary cache| || |__reserved size (初始化参数shared_pool_reserved_size)|||__ DEFAULT buffer cache(初始化参数db_cache_size) LRU链表结构||__KEEP buffer cache (初始化参数db_keep_cache_size)||__RECYCLE buffer cache (初始化参数db_recycle_cache_size)|||__ large pool (初始化参数large_pool_size)||__java pool (初始化参数java_pool_size)||__ streams pool (初始化参数streams_pool_size)||__log buffer (初始化参数log_buffer)PGA--(初始化参数pga_aggregate_target)||__ sort_area_size||__ bitmap_merge_area_size||__ hash_area_size||__ open_cursors||__ oracle 堆栈和TNS堆栈PRE_PAGE_SGAoracle实例启动时，会只载入各个内存区最小的大小。
而其他SGA内存只作为虚拟内存分配，只有当进程touch到相应的页时，才会置换到物理内存中。
但我们也许希望实例一启动后，所有SGA都分配到物理内存。
这时就可以通过设置PRE_PAGE_SGA参数来达到目的了。
这个参数的默认值为FALSE，即不将全部SGA置入物理内存中。
当设置为TRUE时，实例启动会将全部SGA置入物理内存中。
