weejar zhang – Page 53

Tuning ‘log file sync’ Event

2013-04-03 by weejar zhang

The log file sync wait occurs at the end of a transaction (COMMIT or End Of Job)and the database writer (DBWR) process must wait for the log file to synchronize with the database.

Troubleshooting wait event: ‘latch: row cache objects’

2022-06-192013-04-01 by weejar zhang

This latch comes into play when user processes are attempting to access or update the cached data dictionary values.

ORA-00600 [qkssao2s1] internal error issue

2014-01-142013-03-29 by weejar zhang

ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [qkssao2s1], [], [], [], [], [], [], []

SQL hint mean force? More about parallel(二)(不并行的场景)

2014-06-042013-03-25 by weejar zhang

sql中如果使用了parallel hint 或是force parallel query 想并行查询的特性，如果那样做了就一定能保证 query optimizer选择并行的执行计划么？知道那样做实际上产生什么样的影响么？

ITL(Interested Transaction List ),The Oracle database engine locks the data modified by a transaction at the row level. To implement this feature every data block contains a list of all transactions that are modifying it. This list is commonly called interested transaction list (ITL).

Oracle GoldenGate Trail File

2013-03-20 by weejar zhang

Trail files are generated by both the original extract and the Data Pump (extract) process. Trail files are read by the Data Pump (extract) and Replicat (replicat) processes

Schema level flashback(闪回用户或方案)

2013-03-14 by weejar zhang

Technically there is no schema level flashback feature available. and flashback database is a binary operation, the bits and bytes of the flashed back data are identical to what they used to be, it is not a ‘logical’ flashback, it is a physical flashback. However, provided your recovery period does not include any data dictionary … Read more

Script: Who’s using a database link?(找出谁在使用dblink)

2018-11-012013-03-14 by weejar zhang

Every once in awhile it is useful to find out which sessions are using a database link in an Oracle database. It’s one of those things that you may not need very often, but when you do need it, it is usually rather important. Yong Huang includes this script on his website, and notes that … Read more

More about index

2013-03-252013-03-13 by weejar zhang

创建索引可以方便的给DB带来查询上的性能提升，但是DML时index的维护代价也是昂贵的。合理的创建索引，删除不必要的索引。

Tuning PGA Memory

2013-03-11 by weejar zhang

You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.

HAVING expression default Selectivity

2013-03-01 by weejar zhang

In some cases the query optimizer might can not accurately estimate cardinality (e.g. group by having or like with bind variables cause etc..),therefore the Cardinality estimates are based on default seletivity of build-in oracle coredefault seletivity(5%).

oracle forall instructs must be followed by DML

2013-02-26 by weejar zhang

Oracle BULK COLLECT and Oracle FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance. The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is … Read more

goldengate extract process abending caused by nologging DDL

2013-09-102013-01-30 by weejar zhang

I use goldengate(for oracle) to do a SCHEMA synchronized,include DDL operrations. when the the EXTRACT process was running to capture the changes ,then if you try do a DDL such as “alter table t_a no logging” on the capture table will cause EXT process ABENDING, I hit these errors today. ogg version: Oracle GoldenGate Command … Read more

US7ASCII DMP 导入 ZHS16GBK DB 一方案

2014-06-132013-01-28 by weejar zhang

exp 时注意保证三处的字符集一致，对于本例的US7ASCII(10g) DMP 导入 ZHS16GBK DB (11g),采用了修改dmp文件中的两处字符集编号，最终解决了字符集乱码问题。

The lowercase and quotes table names may not be able to audit(小写表名可能不会审计)

2013-01-25 by weejar zhang

昨一好朋友问我开了审计，有些表但无审计记录。下面我来还原这个问题 db_version oracle 10201 for linux SQL> show parameter audit NAME TYPE VALUE ———————————— ———– —————————— audit_file_dest string /u1/oracle/admin/ORCL/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB, EXTENDED audit select any table by access; SQL> select os_username,username,terminal,timestamp,owner,obj_name,audit_option from DBA_AUDIT_TRAIL order by 4; OS_USERNAM USERNAME TERMINAL TIMESTAMP OWNER OBJ_NAME AUDIT_OPTION ———- ———- ———- ——————- —————————— … Read more

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY

2013-01-22 by weejar zhang

1) Export the snapshots and then do a manual purge To purge the Table SYS.WRH$_ACTIVE_SESSION_HISTORY, you may first query the Snapshot History with the query below: select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id; Then, you choose the range of snapshot you want to delete, and you execute the following Procedure: exec dbms_workload_repository.drop_snapshot_range ( low_snap_id … Read more

batch change read only DB in mssql 2K