Oracle调优
文章平均质量分 77
chncaesar
健身 搬砖 偶尔写个代码
展开
-
Oracle Execution Plan -- Statistics
Typical Execution PlanConsider the follow query against SH schema.select /*MY5*/ cust_id, sum(amount_sold) from sales where prod_id=:prod_id group by cust_id;With DBMS_XPLAN.Display_cursor,原创 2013-12-17 13:38:39 · 1198 阅读 · 0 评论 -
执行计划相关SQL
alter session set statistics_level=all;select /*+ my7 */ * emp;select t.* from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALLSTATS LAST')) t where sql_text like '%my7%';原创 2014-04-03 21:29:47 · 691 阅读 · 0 评论 -
TKPROF报告及其自动化
10046与Tkprof工具获取当前连接的trace文件SELECT (SELECT value FROM v$parameter WHERE name='user_dump_dest')|| '/' || (SELECT instance_name FROM v$instance ) || '_ora_' ||spid ||'.trc' trace_fileFROM v$proce原创 2013-12-19 23:06:48 · 774 阅读 · 0 评论 -
Oracle常见hint
Hint直接跟在select/update/merge/insert后,否则不起作用。结构:/*+ */Parallel(4): 由于Oracle并发采用生产者-消费者模式,这里表示每组生产者/消费者有4个slave。实际进程数量有N*4+1个,通常N=2。 1是QC, Query Coordinator。Append: 常见于insert语句,指令Oracle采用direct-path原创 2014-01-02 22:11:00 · 3358 阅读 · 0 评论 -
SQL执行计划及统计信息相关视图
v$active_session_history内容反应了SGA中ASH buffer。Oracle每一秒写入一次ASH buffer。在查询时,一般按照sample_time 排序。v$session针对当前的会话(session),而这两个视图保存了历史信息。当一条SQL已经完成,断开会话时,这两个视图就很有用。例子:SELECT * FROM dba_hist_active_原创 2013-12-22 20:25:47 · 4671 阅读 · 0 评论 -
Oracle Collecting Statstics
Procedure signature:DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, --Owner/schema nametabname VARCHAR2, --Table Namepartname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_e原创 2013-12-17 13:35:37 · 676 阅读 · 0 评论 -
Regexp_like导致Oracle优化器判断失误例子
最近工作中碰到一个SQL,大致如下:select from tab1, tab2where tab1.col1=tab2.col2and tab1.col4='0005'and regexp_like(decode(:bindvar1,'All','All', tab1.col3),('^(' || regexp_replace(:bindvar1,'[,,]', '|'原创 2013-12-27 15:43:46 · 1566 阅读 · 0 评论 -
Oracle Database Cardinality Feedback
This blog is an extraction of https://blogs.oracle.com/optimizer/entry/cardinality_feedbackIntroduced in 11gR2. The purpose of this feature is to automatically improve plans for queries that are e转载 2013-12-15 00:10:19 · 858 阅读 · 0 评论 -
Oracle Execution Plan -- Operation
Hash Group ByStarting with 10g, Oracle introduced “HashGroup By” as the default grouping by mechanism.Oracle employs a hash algorithm tocalculate the hash value for each row based on the GROUP原创 2013-12-25 20:52:58 · 782 阅读 · 0 评论 -
Oracle Join Methods
过程通过一个例子来说明SQL_ID gtmmr9gaxqhfr, child number 0-------------------------------------SELECT /*+ USE_HASH(d e) */e.ename, d.dname FROM emp e, dept d wheree.deptno=d.deptnoPlan hash value: 1123原创 2014-01-06 14:08:46 · 646 阅读 · 0 评论 -
Oracle物化视图2 -- Query Rewrite及参数
Query Rewrite的条件Individual materialized views must have the ENABLE QUERY REWRITE clause.The session parameter QUERY_REWRITE_ENABLED must be set to TRUE (the default) or FORCE.Cost-based optimizati原创 2013-12-13 15:54:04 · 1551 阅读 · 0 评论 -
Pipelined Table Function Statistics and Dynamic Sampling
This is an extraction of Adrian Billington's article:http://www.oracle-developer.net/display.php?id=429At some point, you might need to join a pipelined function to another rowsource (such as转载 2013-12-16 10:32:09 · 832 阅读 · 0 评论 -
Oracle常见等待事件
IO 等待事件Buffer Busy WaitsAn Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. Possible reasons: The block is bei原创 2013-12-26 20:21:22 · 1184 阅读 · 0 评论 -
使用exists(Semi-Join)优化distinct语句
在Oracle 官方文档,semi-join是这么解释的:A semijoin returns rows that match an EXISTS subquery without duplicating rowsfrom the left side of the predicate when multiple rows on the right side satisfy thec原创 2013-12-16 10:23:31 · 2277 阅读 · 0 评论 -
Oracle Parallel Execution
PARALLEL_DEGREE_POLICY Specifies whether or not automatic degree of Parallelism,statement queuing, and in-memory parallel execution will be enabled. MANUAL is the default, which disables automatic d原创 2013-12-14 15:01:54 · 3500 阅读 · 0 评论