123
 123

Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes --

2010-06-21 Mon

12:49 Oracle’s OPT_ESTIMATE hint: Usage Guide (3064 Bytes) » The Pythian Blog

It’s one of those hints that you’ve heard about many times, but all the information is scattered, and finding what you need is really hard.

Many (including myself) have expressed the deepest desire to see it documented, but that never happened. I’ve just troubleshot a problem and used this hint a lot during my “what if” scenario testing, and found this hint to be quite useful. Thus, I decided to document it here on the blog.

I will come back and update this blog as I discover new parameters, and feel free to suggest what you’ve found in the comments. If you can, include an example to illustrate usage.

Note that this usage guide is not official, and is build by my best understanding on how this hint operates.

OPT_ESTIMATE

Purpose

To influence costing and cardinality estimates by the optimizer in the hope that a better execution path is selected. This hint supersedes the “CARDINALITY” hint.

Usage

Parameters separate by space (optionally by comma). “[]” means that “query block” is optional.

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

  • query block

    optional and in the format “@QB_NAME”. For example, in the lack of any QB_NAME hints, you could say: OPT_ESTIMATE(@SEL$1 TABLE….)

  • operation_type

    QUERY_BLOCK
    unknown

    TABLE
    refers to table

    Example: 

    select /*+OPT_ESTIMATE(TABLE DD ROWS=10)*/
count(*) from dual DD

    INDEX_FILTER
    adjusts index filtering – number of rows produced by the index lookup, does not affect the cost to scan the index

    INDEX_SCAN / INDEX_SKIP_SCAN
    For index range or skip scans – how many rows will be scanned in the index – affects the COST of the index access and the number of rows outputted

    JOIN
    the join result of 2 tables – oracle joins 2 tables at a time. Note that you can adjust join cardinalities between 2 tables in order to make that join not to be used.

  • identifier

    qry_block is always optional

    TABLES: tab_alias@qry_block

    INDEX: tab_alias@qry_block index_name

    JOINS: JOIN(tab1_alias@qry_block tab2_alias@qry_block)

  • adjustment

    ROWS=number

    SCALE_ROWS=number – multiplier to be used. For example 0.1 is 10 times less rows than originally estimated

    MIN=number – unknown

    MAX=number – unknown

12:05 Locks (1 Bytes) » Oracle Scratchpad
07:35 Percona Server 5.1.47-rel11.0 (8912 Bytes) » MySQL Performance Blog

Dear Community,

Percona Server version 5.1.47-rel11.0 is available for download now.

The changes in this release include:

New features

Fixed bugs

  • #573100 Can't compile 5.1.46
  • #573104 separate name in INNODB_SYS_TABLES table
  • #580324 Security bug in upstream
  • #586579 Percona Server 11 fails to compile with CFLAGS=-DUNIV_DEBUG
  • #569156 CentOS 5: mysql-server conflicts with MySQL-server
  • #589639 Recovery process may hang when tablespaces are deleted during the recovery
  • #570840 deb package conflicts with libdbd-mysql-perl

The binary packages are available on our website.

Starting from this release there is a uniform naming scheme for Percona binaries for 5.0 and 5.1 based packages. RPM are named as:
Percona-Server-<sub package>-<package suffix>-<mysql version>-<release number>.<revision number>.<os code>.<platform>.rpm
Examples:

PLAIN TEXT
CODE:
  1. Percona-Server-server-51-5.1.47-rel11.0.46.rhel5.x86_64.rpm
  2. Percona-Server-client-51-5.1.47-rel11.0.46.rhel5.x86_64.rpm

Here:

  • "Percona-Server" is a name of the product.
  • "server" or "client" - sub-package
  • 51 - suffix, which means the packages are based on 5.1.x versions

Together "Percona-Server-server-51" is the package name.

  • 5.1.47 is the MySQL version the package is based on
  • rel11.0 is  release number
  • 46 is BZR revision
  • rhel5 is an operating system code
  • x86_64 is a platform

Percona Server obsoletes Percona XtraDB both on rhel and debian paltform. To install RPM or DEB package from Percona repository you just have to do "yum install Percona-Server-server-51". If there is Percona-XtraDB installed it will be replaced by Percona Server. However Percona-Server doesn't obsolete MySQL, so you have to deinstall it first. Conflicts are inevitable otherwise.

If there are MySQL dependant  applications already installed, you might want to install Percona Shared Compat package before installing Percona Server.

The latest source code of Percona Server, including development branch you can find on LaunchPAD.

Please report any bugs found on Bugs in Percona  Server.
For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

Entry posted by Aleksandr Kuzminsky | 2 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

06:41 CouchDB Cheat Sheet (897 Bytes) » myNoSQL

Jan-Piet Mens put together a six page CouchDB cheat sheet:

You can download it from ☞ here.

02:52 Most Popular MongoDB Ruby libs Compared: MongoMapper and Mongoid (1225 Bytes) » myNoSQL
Most Popular MongoDB Ruby libs Compared: MongoMapper and Mongoid:

Useful information for MongoDB users in the Ruby-land:

At first glance, Mongoid and MongoMapper seem to take similar approaches and have very similar APIs, […] it turns out they have different motivations, aims, and, underneath it all, they target different use cases pretty well.

00:42 那确实，技术的提高，… (317 Bytes) » OracleDBA Blog---三少个人涂鸦地！

那确实，技术的提高，还是需要多看书以及血的教训，很多东西，别人和我说100遍，可能都不以为然，自己经历过，记得最牢固。RT @hellodba: 不过，有句话是真的：从来就没有什么神话。

00:39 理由？哪天你不是在场… (515 Bytes) » OracleDBA Blog---三少个人涂鸦地！

理由？哪天你不是在场么，你知道的。RT @NinGoo: 丫的何必打破很多人心中美好的幻想 RT @DavidGuo: 多谢大辉帮我推这个文章，哈哈哈哈 RT @Fenng: 从来就没什么神话 http://www.oracledba.com.cn/blog/?p=617

00:38 卖了吧，留着也没用，… (199 Bytes) » OracleDBA Blog---三少个人涂鸦地！

卖了吧，留着也没用，2w，可以给你未出世的宝宝买好多奶粉了，嘿嘿RT @niubro: 有人出2w买俺的qq号

00:36 多谢大辉帮我推这个文… (262 Bytes) » OracleDBA Blog---三少个人涂鸦地！

多谢大辉帮我推这个文章，哈哈哈哈 RT @Fenng: 从来就没什么神话 http://www.oracledba.com.cn/blog/?p=617

00:03 NoSQL Week In Review 23 (12840 Bytes) » myNoSQL

After a short break, we are continuing our NoSQL week in review series.

What’s Hot in the NoSQL World

  1. When should I use MongoDB?
  2. Integrating Hive and HBase at Facebook
  3. Cassandra, HBase, and PNUTS Compared
  4. Tracking page views with MongoDB
  5. Video: 2 Hours Riak Tutorial

To these, I’ll add the two questions of the week:

and a useful tip for backing up MongoDB using Amazon S3

NoSQL Week in Review

Have a great NoSQL week!

2010-06-20 Sun

18:55 从来就没什么神话 » OracleDBA Blog---三少个人涂鸦地！

2010-06-19 Sat

22:00 利用率问题 » 知道分子
08:30 上海归来 » 玉面飞龙的BLOG