Skip to content
ANBOB

ANBOB

提供综合数据库运维服务与优化方案(不限Oracle MySQL PG GaussDB GoldenDB OceanBase等), 微信/Tel:(+86)134-365-60330

  • 首页
  • 关于我
  • 联系我们

Performance tuning

Why not use index oracle? 为什么不用索引? 检查列表

2019-06-212012-06-15 by weejar zhang

sys_op_descend – Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.

Categories ORACLE 9i-23ai Tags Performance tuning

Control the order in which the tables accessed join using sql hint leading and ordered

2012-05-29 by weejar zhang

The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

Categories ORACLE 9i-23ai Tags Performance tuning

Optimizer_mode ALL_ROWS and FIRST_ROWS[_N]

2012-05-25 by weejar zhang

all_rows是默认参数,first_rows只是为了向前兼容,ORACLE建议你平时用时使用first_rows_N,N的值越大就越像ALL_ROWS靠近。
…

Categories ORACLE 9i-23ai Tags Performance tuning

sql performance truning ,hint,parallel,10053 trace

2012-05-14 by weejar zhang

在两个表的JOIN 中,CBO总是会计算三种JOIN的连接方式的代价,分别为NL JOIN,SM JOIN,HA JOIN,三种JOIN对前后两表的我称呼还不一样,分别是NL JOIN :outer table,inner table,SM :firest table,second table,HA:build table ,probe table,Nested jOIN中的outer table又叫driver table…

Categories ORACLE 9i-23ai Tags Performance tuning 1 Comment

oracle 消除块竞争(hot blocks)

2012-04-23 by weejar zhang

SELECT p1 “file#”, p2 “block#”, p3 “class#”
FROM v$session_wait
WHERE event = ‘read by other session’;

Categories ORACLE 9i-23ai Tags Performance tuning 1 Comment

oracle high load案例及AWR分析过程

2012-04-23 by weejar zhang

Begin Snap: 18125 20-Apr-12 11:01:00 337 1.0
End Snap: 18126 20-Apr-12 12:00:28 490 1.0
Elapsed: 59.46 (mins)
DB Time: 12,979.44 (mins)

Categories ORACLE 9i-23ai Tags Performance tuning

AWR 之table scans (short tables)

2012-03-12 by weejar zhang

Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD or the WriteAccelerator will significantly increase the speed of small-table full-table scans.

Categories ORACLE 9i-23ai Tags Performance tuning

AWR中的physical writes

2012-03-06 by weejar zhang

investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process.

Categories ORACLE 9i-23ai Tags Performance tuning

AWR 中的consistent gets – examination

2012-03-05 by weejar zhang

“Consistent gets – examination” is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O

Categories ORACLE 9i-23ai Tags Performance tuning

QUERIES NOT SHARED WHEN CURSOR_SHARING=FORCE (并未共享游标)

2025-06-302012-02-23 by weejar zhang

前天有个朋友问我了个问题发现v$sqlarea 中文本相同的sql还有很多记录,也就是并未共享游标,也许很多人知道当cursor_sharing=force,可以尽可能的减小hard parse,记住是min,并不是完全防止硬解析,因为解析也很浪费资源

Categories ORACLE 9i-23ai Tags Performance tuning

bulk collect into forall insert批量分次提交

2020-06-222011-12-23 by weejar zhang

难免开始以为批量提交的小事务会比一个大事务完成后一次提示会更快,可以TOM也再三强调这种想法是不对的,如果非要分次,最好的方法是按业务过程的要求以适当的频度提交,并且相应地设置undo大小 。

Categories ORACLE 9i-23ai Tags FETCH c BULK COLLECT INTO, forall, Performance tuning

Events latch: cache buffers chains

2019-01-072011-10-19 by weejar zhang

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 26,173 42.3
latch: cache buffers chains 21,954 4,041 184 6.5 Concurrency

Categories ORACLE 9i-23ai Tags latch: cache buffers chains, Performance tuning 1 Comment

Tuning enq: TM – contention with foreign key (外键引起的队列)

2024-08-082011-08-26 by weejar zhang

通常会发生此事件的等待,因为包含外键约束的列上缺少索引。 在这种情况下,Oracle在DELETE,INSERT和UPDATE语句期间被迫在子表上获取TM锁。..

Categories ORACLE 9i-23ai Tags enq: TM - contention, foreign key, Performance tuning 1 Comment

fast delete, Best practice on Very large table

2011-08-10 by weejar zhang

Today I read foreign blog,I think I learned the true knowledge
One of most operations we are performing is “Deleting many rows from oracle big tables” ;

Categories ORACLE 9i-23ai Tags Performance tuning 1 Comment

利用rowid快速在线更新海量数据

2011-08-09 by weejar zhang

declare
maxrows number default 1000;
row_id_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
cursor cur_t2 is….

Categories ORACLE 9i-23ai Tags Performance tuning 1 Comment

insert append hint 对INDEX的影响

2011-07-07 by weejar zhang

一般向一个表插入数据时,为了提速用到append 提示,但有没有想过表上的索引是怎么维护的?

Categories ORACLE 9i-23ai Tags Performance tuning 1 Comment

Clustering_Factor(索引的集群因子)对执行计划影响

2011-06-14 by weejar zhang

今天 在一个oracle高级调优的例子,提到Cluster_Factor的概念,觉的很有意思,在这分享一下,CLUSTER_FACTOR对Oracle执行计划会产生重要影响。这个值越高,说明索引的使用效率将会越差….

Categories ORACLE 9i-23ai Tags Performance tuning 1 Comment

oracle 同样的sql生产库比测试库执行慢(案例)

2011-06-102011-06-10 by weejar zhang

昨天开发的让我从生产库中同步一个库到测试机,刚好手头有这周一的exp备份,imp了一份,但是今天早上开发打电话找我说情况有点不对头,同一个SQL在生产库上与测试库的执行时间差很多,测试库2秒,生产库要1分钟多 …

Categories ORACLE 9i-23ai Tags Performance tuning 8 Comments

Troubleshooting Wait event “enq:TX – row lock contention”

2024-05-212011-06-09 by weejar zhang

A TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.

Categories ORACLE 9i-23ai Tags Performance tuning 2 Comments

EXP 边导出边压缩打包

2011-04-11 by weejar zhang

exp 可以导出oracle 的数据库逻辑备份,但一般都是导出完成后再用压缩工具打包,有没有边导出连压缩的办法呢?有

下面就用exp\gzip实现

shell版本bash,在rhel 5下操作
….

Categories ORACLE 9i-23ai, 系统相关 Tags Performance tuning 21 Comments
Older posts
Newer posts
← Previous Page1 Page2 Page3 Page4 Next →
  • AI
  • Cloud
  • Exadata
  • Mongo/Redis
  • MySQL/TiDB/GoldenDB
  • OceanBase
  • OGG
  • ORACLE 9i-23ai
  • PostgreSQL/GaussDB
  • Redis
  • SQL Server
  • 其它国产库
  • 岁月
  • 开发语言
  • 系统相关
  • 达梦

Archives

  • September 2025 (5)
  • August 2025 (5)
  • July 2025 (7)
  • June 2025 (5)
  • May 2025 (8)
  • April 2025 (11)
  • March 2025 (5)
  • February 2025 (3)
  • January 2025 (8)
  • December 2024 (13)
  • November 2024 (17)
  • October 2024 (17)
  • September 2024 (13)
  • August 2024 (12)
  • July 2024 (13)
  • June 2024 (14)
  • May 2024 (11)
  • April 2024 (10)
  • March 2024 (10)
  • February 2024 (8)
  • January 2024 (12)
  • December 2023 (15)
  • November 2023 (8)
  • October 2023 (13)
  • September 2023 (10)
  • August 2023 (7)
  • July 2023 (14)
  • June 2023 (15)
  • May 2023 (12)
  • April 2023 (8)
  • March 2023 (10)
  • February 2023 (7)
  • January 2023 (8)
  • December 2022 (12)
  • November 2022 (6)
  • October 2022 (6)
  • September 2022 (9)
  • August 2022 (9)
  • July 2022 (11)
  • June 2022 (5)
  • May 2022 (7)
  • April 2022 (11)
  • March 2022 (6)
  • February 2022 (4)
  • January 2022 (8)
  • December 2021 (9)
  • November 2021 (9)
  • October 2021 (3)
  • September 2021 (4)
  • August 2021 (11)
  • July 2021 (7)
  • June 2021 (8)
  • May 2021 (3)
  • April 2021 (4)
  • March 2021 (8)
  • January 2021 (4)
  • December 2020 (7)
  • November 2020 (5)
  • October 2020 (2)
  • September 2020 (11)
  • August 2020 (7)
  • July 2020 (13)
  • June 2020 (16)
  • May 2020 (24)
  • April 2020 (7)
  • March 2020 (11)
  • February 2020 (6)
  • January 2020 (10)
  • December 2019 (3)
  • November 2019 (6)
  • October 2019 (8)
  • September 2019 (5)
  • August 2019 (10)
  • July 2019 (3)
  • June 2019 (8)
  • May 2019 (2)
  • April 2019 (3)
  • March 2019 (6)
  • February 2019 (9)
  • January 2019 (1)
  • December 2018 (6)
  • November 2018 (5)
  • October 2018 (2)
  • September 2018 (6)
  • August 2018 (5)
  • July 2018 (4)
  • June 2018 (1)
  • May 2018 (6)
  • April 2018 (1)
  • March 2018 (3)
  • January 2018 (4)
  • December 2017 (5)
  • November 2017 (2)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (6)
  • July 2017 (2)
  • June 2017 (3)
  • May 2017 (3)
  • April 2017 (6)
  • March 2017 (13)
  • February 2017 (6)
  • January 2017 (5)
  • December 2016 (8)
  • November 2016 (9)
  • October 2016 (2)
  • September 2016 (7)
  • August 2016 (10)
  • July 2016 (3)
  • June 2016 (4)
  • May 2016 (3)
  • April 2016 (4)
  • March 2016 (4)
  • February 2016 (2)
  • January 2016 (1)
  • December 2015 (2)
  • November 2015 (2)
  • October 2015 (6)
  • September 2015 (4)
  • August 2015 (6)
  • July 2015 (5)
  • June 2015 (9)
  • May 2015 (8)
  • April 2015 (3)
  • March 2015 (2)
  • February 2015 (4)
  • January 2015 (7)
  • December 2014 (11)
  • November 2014 (5)
  • October 2014 (5)
  • September 2014 (6)
  • August 2014 (5)
  • July 2014 (7)
  • June 2014 (1)
  • May 2014 (5)
  • April 2014 (6)
  • March 2014 (8)
  • February 2014 (11)
  • January 2014 (6)
  • December 2013 (6)
  • November 2013 (5)
  • October 2013 (6)
  • September 2013 (7)
  • August 2013 (8)
  • July 2013 (5)
  • June 2013 (7)
  • May 2013 (5)
  • April 2013 (8)
  • March 2013 (9)
  • February 2013 (1)
  • January 2013 (12)
  • December 2012 (20)
  • November 2012 (10)
  • October 2012 (9)
  • September 2012 (9)
  • August 2012 (8)
  • July 2012 (22)
  • June 2012 (11)
  • May 2012 (17)
  • April 2012 (8)
  • March 2012 (13)
  • February 2012 (7)
  • January 2012 (9)
  • December 2011 (19)
  • November 2011 (16)
  • October 2011 (15)
  • September 2011 (21)
  • August 2011 (23)
  • July 2011 (22)
  • June 2011 (37)
  • May 2011 (39)
  • April 2011 (31)
  • March 2011 (26)
  • February 2011 (14)
  • January 2011 (23)
  • December 2010 (30)
  • November 2010 (17)
  • December 2009 (1)
  • November 2009 (1)
  • September 2009 (1)

标签

19C BACKUP/RECOVERY DataGuard dblink exadata Goldengate HAIP lob mysql oceanbase OGG ora-600 ORA-1017 ORA-4031 ora-7445 ORACLE11g oracle 12.2 ORACLE 12C ORACLE ADMIN ORACLE ADMIN oracle asm oracle dataguard oracle goldengate oracle internal oracle oem oracle plsql/sql oracle rac ORACLE安全 oracle安装 oracle 异常 oracle教材 oracle 权限管理 oracle管理 OS Command OS Command partition Performance tuning postgresql Redis row cache lock shell Troubleshooting Troubleshooting 备份与恢复 年终总结
版权所有© 2008-2027 weizhao.zhang (ANBOB.COM)

禁止未经授权的数据爬取行为,并保留追究法律责任的权利。