ITPub博客

Direct Path Reads

db time ,db cpu

APPLIES TO:Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.1 [Release 11.2 to 12.1]IBM AIX on POWER Systems (64-bit)SYMPTOMSAfter an upgrade from AIX 5.3 to AIX 7.1, an increase of virtual memory consumption and paging activity is noticed.All the points were checked a...

Table 2-5 Recommended Values for Virtual Memory ManagerParameterValueminperm%3 (AIX 5.3 default is 20)maxperm%90 (AIX 5.3 def...

...

Introduction to Database Memory ComponentsThe basic memory structures associated with Oracle Database include:System Global Area (SGA)The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. Th...

Temporary tablespaces are shared objects and they are associated to an user or whole database (using default temporarytablespace). So, in RAC, temporary tablespaces are shared between the instances. Many temporary tablespaces can be cre...

When running an OLTP systems, it i??s possible to see high TX enqueue contention on index associated with tables, which are having high concurrency from the application.  This usually happens when the application performs lot of INSERTs and DELETEs concurrently. For RAC system, the concurre...

All About the SQLT Diagnostic Tool (文档 ID 215187.1)SQL Tuning Health-Check Script (SQLHC) (文档 ID 1366133.1)

select SQL_ID,SQL_PLAN_HASH_VALUE,SQL_EXEC_START from V$ACTIVE_SESSION_HISTORY;select PLAN_HASH_VALUE,FIRST_LOAD_TIME from v$sql where sql_id='bksyj647thx15'select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')  TIMESTAMPfrom dba_hist_sql_plan where SQL_ID=...

--通过段调优顾问回收大表的分配空间和高水位线 --创建用户 SQL> create tablespace hzqtbs datafile '/u01/app/oracle/oradata/prod/hzqtbs.dbf' size 30m;Tablespace created.SQL> create user hzq identified by hzq default tablespace hzqtbs quota unlimited on hzqtbs account unlock; User created.SQL> gran...

  rem rem  This script uses PL/SQL. rem  le hit ratio  rem   col global_name for A20  variable d number  variable c number  variable r number  begin    select value into :c    from v$sysstat   ...

APPLIES TO:Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterInformation in this document applies to any platform.SYMPTOMSAn ALTER TABLE ... SHRINK has been done and no space seems to have been releasedCAUSEMisunderstanding of how Oracle Storage worksSO...

--回收表空间alter table test enable row movement; --开启行迁移功能。alter table test shrink space compact;--(可以在压缩期间进行DML操作和查询) ,收缩表，不会降低hwmalter table test shrink space; --( 调整HWM时将阻塞DML操作),收缩表，并且降低hwmalter table test_idx shrink space cascade;--收缩表并降低...

The initialization parameter PARALLEL_DEGREE_POLICY controls whether automatic degree of parallelism (DOP), parallel statement queuing, and in-memory parallel execution are enabled. This parameter has three possible values:MANUAL - Disables automatic DOP, statement queuin...

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组；这样的子查询称为标量子查询（scalar subquery）。例如，一个子查询可以用到下面例子的select子句中，这个例子列出所有的系以及它们拥有的教师数：select dept_name,  (select co...

Stale statisticsStatistics gathered by?DBMS_STATS?can become out-of-date. Typically, statistics are stale when 10% or more of the rows in the table have changed since the last time statistics were gathered.The?GATHER_DATABASE_STATS?or?GATHER_SCHEMA_STATS?procedu...

 Height-Balanced Histograms:当桶数量>列中的不同值的个数,或者不同值大于254.优化器首先会检查在直方图中使用谓词作为endpoint的有多少个bucket？1，如果有多个，计算公式：Number of bucket endpoints / total number of buckets) *  number of rows in the table2，对于只是一个bucket或者不是任何bu...

柱状图（Histograms）：Height-Balanced Histograms：列中不同值的个数小于直方图的桶数，每个桶的值不同。Frequency Histograms：列中不同值的个数大于直方图的桶数，每个桶的值相同，最后一个桶除外。 收集方法：GATHER_TABLE_STATS==》method_opt 通过size参数指定直方图的桶数：SIZE {integer | REPEAT |...

Stale statisticsStatistics gathered by DBMS_STATS can become out-of-date. Typically, statistics are stale when 10% or more of the rows in the table have changed since the last time statistics were gathered.The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedu...