ITPub博客

性能测试时发现阻塞在数据库端，测试人员找到了我。通过查询v$session_wait后发现基本上是datafile和redo文件的读写等待事件。监控时发现主机的cpu和内存还正常，loadrunner压力时发现入库操作（往表插数据阻塞）一直影响其他进程，对数据库作了statspack后发现往一张表中插入数据消耗了大量的buffer get。查看该表的存储参数后发现INITIAL_EXTENT为64K，NEXT_EXTENT为空，该表对应的表空间的ALLOCATION_TYPE为UNIFORM。在这种情况下表数据扩张时每次申请新的extent的大小为64k。由于是非常频繁插入数据，显然每次扩张64K的大小太小了，导

最近在浏览一项目的第三方软硬件汇总表时，看到了oracle数据库的报价如下：产品名称版本使用权类型用户数发货数量使用权单价使用权费用Oracle 9i enterprise edition9iFU4001￥9,932.00￥3,972,800.00Partitioning9iFU4001￥2,483.00￥993,200.00Performance Tuning Pack9iFU4001￥745.00￥298,000.00Diagnostic Pack9iFU4001￥745.00￥298,000.00Change Management Pack9iFU4001￥745.00￥298,000.001.4 数据库的售后服务　　　　　　数量产品说明单价小计1Oracle基本技术支持服务/年(系统列表价22%):￥1,289,200.00￥1,289,200.00　　　

Oracle Management Service (OMS)Oracle HTTP Server (OHS)Oracle Application Server Containers for J2EE (OC4J)Enterprise Manager (EM)Distributed Configuration Management Control (dcmctl)Oracle Process Management Notification Control (opmnctl).Oracle Management Repository (OMR)Database Configuration Assistant (DBCA)[@more@]Automatic Storage Management (ASM)Oracle HTTP Server (OHS)Distributed Configuration Management Control (dcmctl)Oracle Process Management Notification Control (opmnctl)High-water m

Base statistics represent the raw data collected. For example, counting the number of physical reads in the system since startup is a base statistic.Metrics are secondary statistics derived from base statistics. Most metrics track the rates of change of activities in the Oracle database. For example, the average physical reads in the system in the last 60 minutes is a metric. Metrics are used by internal client components forsystem health monitoring, problem detection, and self-tuning.[@more@]Fo

This component is the central element of the new manageability infrastructure. It provides services to internal Oracle server components to collect, process, maintain, and access performance statistics for problem detection and self-tuning purposes.[@more@]AWR is the infrastructure that provides services to Oracle Database 10g components to collect,maintain, and utilize statistics for problem detection and self-tuning purposes.The AWR infrastructure consists of two major parts:An in-memory stati

第一步、创建directory，给用户授权，并创建sqlldr导入的目标表结构$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 17 15:17:58 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production[@more@]SQL> create directory external_table_dir as '/oracle/sqlldr/';Directory created.SQL&gt

The Grid Control framework includes the underlying component pieces that provide the ability to centrally manage your computing environment. These component pieces include: Oracle Management Repository (OMR) One or more Oracle Management Services (OMSs) One or more Oracle Management Agents (OMAs) This framework can be on a single server or distributed across multiple servers depending on the needs of the business and the size of the enterprise being managed. [@more@]Grid Control manages all curr

Recently,I saw one of my colleagues operating data in the product database.when he query data he used the statement of "select **** from A for update" in the SQL window of the PL/SQL developer tool.we knowed that the FOR UPDATE would make the table A be locked,therefore this will result in other operations crash for waiting all the time!As we know,the clause of For UPDATE would bring a row lock for the table,so just cautiously used "FOR UPDATE" especially in the product d

It is often useful to dump the internal contents of Oracle internal structures such as the control files, library cache, and redo log headers. The following undocumented Oracle commands can be used for this purpose.For each of these commands, a trace file will be generated in your UDUMP directory. After issuing the command, you can go to your UDUMP directory to view the contents of the dump.[@more@]1 – SCN Dump - This command dumps the control file contents to see SCN information. The SCN inf

A temporary tablespace group can be thought of as a shortcut for a list of temporary tablespaces.A temporary tablespace group consists of only temporary tablespaces.A temporary tablespace group has the following properties:[@more@]?It contains at least one temporary tablespace. There is no explicit limit on how many tablespaces are contained in a group.?It has the same namespace as tablespaces. It is not possible for a tablespace and a temporary tablespace group to have the same name.?A temporar

When the Oracle database stores data in a heap-organized table, the rows are not stored in a usercontrolled order. Rather, the decision about where to place a row is dependent on storage heuristics. The Oracle database does not guarantee the return order of the rows unless the queryincludes an ORDER BY clause.[@more@]Inside a sorted hash cluster, the rows are organized in lists of sorted rows. Each list corresponds to a particular value of the hash key columns defined by the corresponding sorted

1.建表,列servkeys将用到全文检索.create table SERVICEKEY(SERVCODE VARCHAR2(12) not null,,SERVKEYS VARCHAR2(2048) not null);2. 创建中文语义的定义beginctx_ddl.create_preference('chinese_lexer','CHINESE_VGRAM_LEXER');end;/ [@more@]3. 创建中文语义的索引create index idx_ctx_servkey on servicekey(servkeys) indextype is ctxsys.contextparameters('lexer chinese_lexer');4.全文检索中用于定时更新DR$idx_ctx_servkey$I、DR$idx_ctx_servkey$K、DR$idx_ctx_servkey$R和DR$idx_ctx_servkey$N表中的记录，重建索引create o

This note assumes the Oracle software is installed into the $ORACLE_HOME.On Unix, the environment variable LD_LIBRARY_PATH must be set to$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib[@more@]Installation script should look as follows :============================================Note: In SQL*Plus we use '?' instead of $ORACLE_HOME ------------------- cut here ------------------------------connect SYS/password as SYSDBAset echo oncol comp_name for a30spool textinstall.logRem ===============================

The concept of a service was first introduced in Oracle8i as a means for the listener to perform connection load balancing between nodes and instances of a cluster. However, the concept,definition, and mplementation of services have been dramatically expanded. Services are a feature for workload management that organize the universe of work execution within the database to make that work more manageable, measurable, tunable, and recoverable. A service is a grouping of related tasks within the da

Recently,a 10g database occured lots of alerts about ORA-03135 & ORA-3136,as follows:Tue Jul 31 10:30:29 2007WARNING: inbound connection timed out (ORA-3136)Tue Jul 31 10:30:29 2007WARNING: inbound connection timed out (ORA-3136)Tue Jul 31 10:30:54 2007WARNING: inbound connection timed out (ORA-3136)Tue Jul 31 10:30:54 2007WARNING: inbound connection timed out (ORA-3136)Tue Jul 31 10:30:55 2007WARNING: inbound connection timed out (ORA-3136)Tue Jul 31 10:30:55 2007WARNING: inbound connection

一现网数据库cpu经常告警，从statspack报告中可以看到如下的语句消耗了大量的IO和CPU资源：SELECT NVL(MAX(MENUSEQ),0) FROM MENU WHERE PORTALTYPEID = 4 AND PARENTMENUID = :B1;初一看，这个查询语句明显应该是应用所发出的，不应该是后台程序，因为后台程序的话需有into variable才行。[@more@]查询后台程序的脚本，发现语句“select nvl(max(menuseq),0) into v_maxmenuseq from menu where portaltypeid = 4 and parentmenuid = v_parentmenuid;”确实存在，莫非statspack中的语句就是这个语句？难道PL/SQL中的into variable不会体现

一同事在公司的bbs技术栏里发布一帖子，请教如下的问题：在Pro*C中使用Merge语句，来代替原来的Update or Insert语句，在预编译时，总是报错。代码如下：EXEC SQL MERGE INTO tab1 a USING (select * from tab2 where tab2.col1=:parameter1 and tab2.col2=:parameter2) bON (a.col1 = b.col1 AND a.col2 = b.col2)WHEN MATCHED THEN UPDATE SET a.col3 = :parameter3WHEN NOT MATCHED THEN INSERT VALUES (b.col1, b.col2, b.col3);所使用的预编译器是:Pro*C/C++: Release 9.2.0.4.0[@more@]异常信息如下：PCC-S-02201, Encountered the

最近在查看一数据库主机的性能时意外发现一个进程占用的系统资源特别高，并且持续占用着。Load averages: 2.02, 1.87, 2.09 538 processes: 530 sleeping, 8 running Cpu states:CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 0 1.83 54.0% 0.0% 7.1% 38.9% 0.0% 0.0% 0.0% 0.0% 1 2.21 35.4% 0.0% 7.5% 57.1% 0.0% 0.0% 0.0% 0.0%--- ---- ----- ----- ----- ----- ----- ----- ----- -----avg 2.02 44.7% 0.0% 7.3% 48

The ASH contains recent information on active sessions sampled every second. The AWR takes snapshots of the database every hour, so the information in the AWR could be almost an hour old and will not help in diagnosing issues that are current on the database. [@more@]Typically, to resolve issues currently on the database, detailed information pertaining to the last 5 or 10 minutes is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and records the eve

上周对first_rows和rownum<2做了测试，见如下的结果。从结果可以看出相应的差距。分有索引和无索引两种情况，每种情况下有7种写法。 [@more@]SQL> desc tempName Type Nullable Default Comments -------------- ------------- -------- ------- -------- ID NUMBER(15) REQUESTID VARCHAR2(20) Y MESSAGETYPE VARCHAR2(1) REQUESTTIME DATE Y HREF VARCHAR2(250) Y SENDER VARCHAR2(20) Y RECEIVER VARCHAR2(15) MESSAGECONTENT VARCHAR2(500) Y MESSAGESUBJECT VARCHAR2(200) Y SENDHOSTIP VARCHAR2(20) REQUESTIP VARCHAR2(15) Y