VMCD.ORG

Focus on Oracle mail:ylouis83#gmail.com

« Previous Entries

Block split and entry reorder

Posted by admin on 12th December 2012

今天跟研发的同学讨论到了这个问题,index block split过程中涉及到的重新排序问题.

SQL> create table idx_split (a number, b varchar2(1446), c date);   
 
Table created
 

SQ[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

处理坏块一例(2)

Posted by admin on 17th August 2012

帮朋友check一次中国银联数据库的坏块问题(并不是严格意义上的坏块),大致情况为smon需要trascation recover —>特定的block 发现无法读取 —>transaction recover出现问题 —>smon terminated

Wed Aug 15 19:02[......]

Read more

Tags:
Posted in Oracle Experience | 2 Comments »

Bitmap index tips

Posted by admin on 7th June 2012

对于BITMAP index的使用 一直存在一些争议 我们来看看下面几个观点：

1. Low cardinality – Some dabase vendor, like Oracle, provides very practical suggestion

If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.
B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER.
There are 100 or more rows for each distinct value in the indexed column. When this limit is met, the bitmap index will be much smaller than a regular index, and you will be able to create the index much faster than a regular index.


2. No or little insert/update

Updating bitmap indexes take a lot of resources. Here are the suggestions
Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity. Designers must ensure that the benefits of any index outweigh the negatives of index maintenance.
Use this simple estimation guide for the cost of index maintenance: each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERT into a table with three indexes, then it will be approximately 10 times slower than an INSERT into a table with no indexes. For DML, and particularly for INSERT-heavy applications, the index design should be seriously reviewed, which might require a compromise between the query and INSERT performance.


3. Multiple Columns

One of the advantage is that multiple bitmap indexes can be merged and the column does not have to selective!More than one column in the table has an index that the optimizer can use to improve performance on a table scan Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O

对于观点1 观点2 其实就是说明bitmap index 适合cardinality distinct小,并且并发DML很少的环境(OLAP) 但是对于大量的插入更[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

创建index之前如何确定其大小

Posted by admin on 8th March 2012

昨天看了roger的一篇文章，如何在创建index 之前估算它的大小，这里有一种更简单的方法实现：

主要思路就是采用了dbms_space.create_index_cost这个包

--create a testing table named t
SQL> create table t as select * from dba_objects;
 
Table created.
 
SQL> declare
begin
for i in 1..10 loop
insert /*+ APPEND */ into t select * from t;
commit;
end loop;
end;
/
 
PL/SQL procedure successfully completed.
 
SQL>  select count(*) from t;
 
  COUNT(*)
----------
  38171648
 
1 row selected.
 
--As told by Oracle docs, you have to have updated statistics in order to allow CREATE_INDEX_COST to produce accurate estimation
SQL> exec dbms_stats.gather_table_stats('LIU','T');
 
PL/SQL procedure successfully completed.
 
 
 
SQL> variable used_bytes number
SQL> variable allocated_bytes number
SQL> set autoprint on
SQL> exec dbms_space.create_index_cost( 'create index t_idx on t(object_id,object_name,object_type) tablespace PURCHASE', :used_bytes, :allocated_bytes );
 
PL/SQL procedure successfully completed.
 
 
ALLOCATED_BYTES
---------------
     1946157056
 
 
USED_BYTES
----------
1259664384
 
 
Verification
SQL> create index t_idx on t(object_id,object_name,object_type);
 
Index created.
 
 
SQL> select segment_name ,sum(bytes) from dba_segments where owner='LIU' and segment_name='T_IDX' group by segment_name;
 
SEGMENT_NAME                                      SUM(BYTES)
--------------------------------------------------------------------------------- ----------
T_IDX                                          1930428416
 
1 row selected.

可以看到预计空间为 1946157056byte 实际空[......]

Read more

Tags:
Posted in Oracle Experience | 1 Comment »

Fractured blocks when Rman backup is running

Posted by admin on 20th February 2012

系统alert在主库出现下面错误 OS为linux 5.5

Stopping background process CJQ0
Sat Feb 11 03:41:07 2012
Hex dump of (file 9, block 561424) in trace file /data/oracle/diag/rdbms/yhdstd/yhddb1/trace/yhddb1_ora_11327.trc
Corrupt block relative dba: 0x02489110 (file 9, block 561424)
Fractured block found during backing up datafile
Data in bad block:
 type: 6 format: 2 rdba: 0x02489110
 last change scn: 0x0007.a9d2831f seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xe6b90601
 check value in block header: 0x8c60
 computed block checksum: 0x82af
Reread of blocknum=561424, file=/data/oracle/oradata/yhddb1/md_data01.dbf. found valid data

这个情况在9i比较常见，rman备份时datafile 正在处在剧烈的io操作，如大批量的写入等等，oracle判断此块为Fractured，但是这并不是真正意义上的Corrupt[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

HugePages on Linux

Posted by admin on 16th January 2012

Regular Pages and HugePages

This section aims to give a general picture about memory access in virtual memory systems and how pages are referenced.[......]

Read more

Tags:
Posted in Unix/Linux | 5 Comments »

How does “Nologging” attribute

Posted by admin on 13th January 2012

NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:
DML:
1. Dir[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

Oracle 坏块处理(final)

Posted by admin on 8th January 2012

收到开发人员报告，oracle出现坏块，信息如下：

Hex dump of (file 7, block 1407500) in trace file /data/oracle/admin/orcl/bdump/orcl_p004_7383.trc
Corrupt block relative dba: 0x01d57a0c (file 7, block 1407500)
Fractured block found during crash/instance recovery
Data in bad block:
type: 6 format: 2 rdba: 0x01d57a0c
last change scn: 0x0000.e02e5f2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x88150601
check value in block header: 0x462f
computed block checksum: 0xd738
Reread of rdba: 0x01d57a0c (file 7, block 1407500) found same corrupted data

至此，我们确定该坏块是 7月28号上午9:45分数据库server crash导致的。

解决思路：
首先确认该坏块所属对象

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> set pagesize 100
SQL> set linesize 144
SQL> col segment_name format a50
 
SQL> select
object_id||'-'||segment_name||'/'||partition_name segment_name,
segment_type,
RELATIVE_FNO
from dba_extents a
,dba_objects b
where file_id = '7'
and 1407500  between block_id and block_id + blocks - 1
and a.SEGMENT_NAME   = b.OBJECT_NAME
and (a.PARTITION_NAME = b.SUBOBJECT_NAME or a.PARTITION_NAME is null)
and a.owner          = b.owner
 
SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO
------------------ ------------
159761-IDX_D_D_PRODUCT_ID/ INDEX 7

确认该坏块属[......]

Read more

Tags:
Posted in Oracle Experience | 2 Comments »

分区表中的index 失效及处理

Posted by admin on 4th January 2012

分区表中 local 索引的维护会在oracle 操作表分区的时候自动进行，需要注意的是global 索引，当global索引所在表执行alter table 涉及下列操作时，会导至该索引失效，需要重新建立：

——————————-[......]

Read more

Tags:
Posted in Oracle Experience | No Comments »

在线重定义一例

Posted by admin on 29th December 2011

创建需要redefinition的表,结构同源表

-- Create table
create table yhd_so_temp
(id                            NUMBER(18) not null,
  end_user_id                   NUMBER(18),
  order_amount                  NUMBER(11,2),
  order_code                    CHAR(12),
  order_status                  NUMBER(4),
  order_type                    NUMBER(4),
  order_need_cs                 NUMBER(1),
  order_delivery_fee            NUMBER(11,2),
  order_source                  NUMBER(4),
  back_operator_id              NUMBER(18),
  order_paid_by_rebate          NUMBER(11,2),
  order_pay_cfm_bk_op_id        NUMBER(18),
  order_payment_confirm_date    DATE,
  order_payment_signal          NUMBER(4),
  order_payment_method_id       NUMBER(18),
  order_payment_code            VARCHAR2(500),
  order_create_time             DATE,
  order_to_logistics_time       DATE,
  order_out_of_inventory_status NUMBER(4),
  good_receiver_id              NUMBER(18),
  order_need_invoice            NUMBER(1),
  parent_so_id                  NUMBER(18),
  delivery_date                 DATE,
  expect_receive_date           DATE,
  expect_receive_time           NUMBER(4),
  receive_date                  DATE,
  order_delivery_method_id      NUMBER(18),
  order_paid_by_account         NUMBER(11,2),
  order_paid_by_others          NUMBER(11,2),
  tracker_session_id            VARCHAR2(400),
  account_payable               NUMBER(11,2),
  product_amount                NUMBER(11,2),
  session_id                    VARCHAR2(400),
  data_exchange_flag            NUMBER(1),
  good_receiver_name            VARCHAR2(500),
  good_receiver_address         VARCHAR2(400),
  good_receiver_province        VARCHAR2(400),
  good_receiver_city            VARCHAR2(400),
  good_receiver_county          VARCHAR2(400),
  good_receiver_post_code       VARCHAR2(400),
  good_receiver_phone           VARCHAR2(400),
  supplier_process_status       NUMBER(4),
  po_id                         NUMBER(18),
  order_paid_by_coupon          NUMBER(11,2),
  cancel_date                   DATE,
  is_leaf                       NUMBER(1),
  order_cs_remark               VARCHAR2(2000),
  has_imported                  NUMBER(1),
  good_receiver_mobile          VARCHAR2(400),
  do_cancel_flag                NUMBER(10) not null,
  so_item_count_change_flag     NUMBER(1) not null,
  order_paid_by_card            NUMBER(11,2) not null,
  reference_point               NUMBER,
  real_point                    NUMBER,
  recompense_points             NUMBER not null,
  membership_base_point         NUMBER not null,
  group_ratio                   NUMBER,
  wlt_point                     NUMBER not null,
  track_info                    VARCHAR2(2000),
  is_fragile                    NUMBER(1),
  is_liquid                     NUMBER(1),
  bought_times                  NUMBER(18),
  spec_proc_flag                NUMBER(4),
  id_card                       VARCHAR2(400),
  order_import_source           NUMBER(18),
  is_new_good_receiver_hpone    NUMBER(1),
  partner_amount                NUMBER(11,2),
  partner_amount_type           NUMBER(1),
  is_vip                        NUMBER(1),
  good_receiver_city_id         NUMBER(18),
  good_receiver_country_id      NUMBER(18),
  good_receiver_county_id       NUMBER(18),
  good_receiver_province_id     NUMBER(18),
  allyes_uid                    VARCHAR2(300),
  payment_gateway_id            NUMBER(18),
  need_allocation               NUMBER(1) not null,
  warehouse_id                  NUMBER(18),
  delivery_supplierid           NUMBER(18),
  order_import_do_flag          NUMBER(1),
  cancel_operator_id            NUMBER(18),
  mc_site_id                    NUMBER(8),
  order_need_integral           NUMBER(9),
  virtual_stock_status          NUMBER(2),
  business_type                 NUMBER(2),
  flag                          NUMBER(1),
  order_finish_time             DATE,
  update_time                   DATE,
  good_remark                   VARCHAR2(300),
  order_cs_reason               VARCHAR2(400),
  treatment_time                DATE,
  treatment_user_id             VARCHAR2(400),
  is_half_day_delivery          NUMBER(1) default 0,
  central_version               NUMBER(5,2),
  order_promotion_discount      NUMBER(11,2),
  is_need_double_form           NUMBER(1) default 0,
  creditcard_owner_name         VARCHAR2(120),
  is_moved_by_hand              NUMBER(1) default 0 not null,
  order_delivery_person_mobile  VARCHAR2(400),
  estimate_receive_date         DATE,
  delivery_remark               VARCHAR2(500),
  receive_remark                VARCHAR2(500),
  supplier_delivery_time        VARCHAR2(400),
  order_delivery_add_fee        NUMBER(11,2),
  user_ip                       VARCHAR2(500),
  user_guid                     VARCHAR2(400),
  print_num                     NUMBER(2),
  order_create_pay_time         DATE,
  delivery_method_type          NUMBER(8),
  delivery_service_type         NUMBER(8),
  pay_service_type              NUMBER(8),
  good_receiver_area            VARCHAR2(500),
  good_receiver_area_id         NUMBER(18),
  is_new_deliveryorder          NUMBER(3),
  delivery_level                NUMBER(1),
  order_weight                  NUMBER(11,2)
)
partition by range (ORDER_CREATE_TIME)
(
  partition P0811 values less than (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace data01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition P0812
 
..........
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 
SQL> alter session force parallel dml parallel 4;
 
Session altered.
 
SQL> alter session force parallel query parallel 4;
 
Session altered.
 
SQL> set timing on;
 
SQL>
SQL>
SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'EDW1_USER', orig_table => 'YHD_SO',int_table=> 'YHD_SO_TEMP',options_flag=>dbms_redefinition.cons_use_rowid);
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:06:00.41

花了6分钟 速度很不错

下面copy index 约束等，对于分区表建议不要copy index,约束,手工建立local i[......]

Read more

Tags:
Posted in Oracle Experience | 1 Comment »

« Previous Entries