DBA Sensation

May 25, 2010

Can’t compile a stored procedure when it’s locked

Filed under: 1. Oracle, [PL/SQL dev&tuning] — Tags: , , — zhefeng @ 10:25 am

Trying to recompile a procedure causes the application to hang
(ie: SQL*Plus hangs after submitting the statement). Eventually ORA-4021 errors
occur after the timeout (usually 5 minutes). Here is the soluation from metalink:
Note:ID 107756.1

Error: ORA 4021
Text: time-out occurred while waiting to lock object
—————————————————————————–
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

Solution Description
——————–

Verify that the package is not locked by another user by selecting from
V$ACCESS view. To do this, run:

SELECT * FROM v$access WHERE object = ”;

Where is the package name (usually in all uppercase). If there is a row
returned, then the package is already locked and cannot be dropped until the
lock is released. Returned from the query above will be the SID that has this
locked. You can then use this to find out which session has obtained the lock.

In some cases, that session might have been killed and will not show up. If
this happens, the lock will not be release immediately. Waiting for PMON to
clean up the lock might take some time. The fastest way to clean up the lock
is to recycle the database instance.

If an ORA-4021 error is not returned and the command continues to hang after
issuing the CREATE OR REPLACE or DROP statment, you will need to do further
analysis see where the hang is occuring. A starting point is to have a
look in v$session_wait, see the referenced NOTE.61552.1 for how to analyze hang
situations in general

Solution Explanation
——————–

Consider the following example:

Session 1:

create or replace procedure lockit(secs in number) as
shuttime date;
begin
shuttime := sysdate + secs/(24*60*60);
while sysdate <= shuttime loop
null;
end loop;
end;
/
show err

begin
— wait 10 minutes
lockit(600);
end;
/

Session 2:
create or replace procedure lockit as
begin
null;
end;
/

Result: hang and eventually (the timeout is 5 minutes):

create or replace procedure lockit as
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object LOCKIT

Session 3:

connect / as sysdba
col owner for a10
col object for a15
select * from v$access where object = 'LOCKIT';

Result:
SID OWNER OBJECT TYPE
———- ———- ————— ————————
9 OPS$HNAPEL LOCKIT PROCEDURE

select sid, event from v$session_wait;

Result:

SID EVENT
———- —————————————————————-
9 null event

12 library cache pin

In the above result, the blocking sid 9 waits for nothing while session 12, the
hanging session, is waiting for event library cache pin.

March 17, 2010

How to migrate sql cluster between domains

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 4:54 pm

Migrating SQL Server 2005/2000 cluster from one domain to the other domain is not supported by Microsoft. However, here is the way how to do that.
i used this way to migrate our production cluster sucessfully.

1. Current settings (active – passive mode)
Cluster name: clustest
Cluster management IP: 10.0.0.142
Virtual server Name: clustestvip1.test.com
VIP: 10.0.0.144
Service account: test\service.product
Cluster group: test\sqlcluster
Node1:
Name –dbnode1.test.com
Physical IP: 10.0.0.140

Node2:
Name – dbnode2.test.com
Physical IP: 10.0.0.204

2. Objective
(1). To change the domain from test.com to test1.com
(2). To change the service account from test\service.acc to test1\service.act
(3). Make sure the sql cluster is still functional after changing

3. Testing steps
(1). Take all cluster resources offline (except the quorum, which cannot be taken offline)
(2). Stop the cluster service on both nodes and change startup type to “manual”
(3). Change the domain of each machine to the new domain and reboot
(4). After reboot, on each machine, change the cluster and SQL service accounts to accounts in the new domain (test\service.acc ->test1\service.act).
Note: according to MSKB 915846 it should be “On one of the cluster nodes, use SQL Server Configuration Manager to change the service account to the new account.”
That’s not true, and you got error from there, have to do it on both nodes in services.msc.
(5). Add the cluster and SQL service accounts to the local Adminstrators group.
(6). Run gpedit.msc to grant the following rights to the new service account:
Path: “Local Computer Policy” -> “Windows Settings” -> “Security Settings” -> “Local Policies” ->”User Rights Assignment”
*Act as part of the operating system
*Adjust memory quotas for a process
*Back up files and directories.
*Debug programs
*Increase scheduling priority
*Increase quotas (not in windows 2003).
*Load and unload device drivers.
*Lock pages in memory
*Log on as a batch job
*Log on as a service
*Manage auditing and security log
*Replace a process level token
*Restore files and directories
7). Update the domain groups SID for cluster services (sql 2000 skip this step)
There are domain groups related to old domain name as below which couldn’t be changed in AD(you can’t find them! They only appear in register as SID at this branch: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup).
So what we need to do is just find the old group sid in registry and replace it with new group sid. Here are the steps:
a. find old cluster group sid by logging in as old service account, then run “whoami /groups” in dos prompt
b. in this case, the service account is service.acc, and cluster group is : test\sqlcluster
c. find the sid for test\sqlcluster is:
S-1-5-21-1679026800-3574736516-1101542067-14559
d. same way to find the new group sid for test1\sqlcluster is:
S-1-5-21-2789037367-2132044359-1364708090-24619
e: replace the old sid with new sid under branch (do a small search there) : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
8). Reboot both nodes
9). Start the cluster service on both machines (put them back to “automatic” mode again)
10). Bring cluster resources online

Reference:
1. Changing the Domain for a SQL 2005 Cluster
http://nyc-dba.blogspot.com/2007/01/changing-domain-for-sql-2005-cluster.html

2. How to move a Windows Server cluster from one domain to another
http://support.microsoft.com/kb/269196

3. Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster
http://support.microsoft.com/kb/915846

March 12, 2010

Why Isn’t Oracle Using My Index?!

Filed under: [System Performance tuning] — Tags: , , , — zhefeng @ 4:02 pm

By Jonathan Lewis
http://www.dbazine.com/oracle/or-articles/jlewis12

The question in the title of this piece is probably the single most frequently occurring question that appears in the Metalink forums and Usenet newsgroups. This article uses a test case that you can rebuild on your own systems to demonstrate the most fundamental issues with how cost-based optimisation works. And at the end of the article, you should be much better equipped to give an answer the next time you hear that dreaded question.

Because of the wide variety of options that are available when installing Oracle, it isn’t usually safe to predict exactly what will happen when someone runs a script that you have dictated to them. But I’m going to risk it, in the hope that your database is a fairly vanilla installation, with the default values for the mostly commonly tweaked parameters. The example has been built and tested on an 8.1.7 database with the db_block_size set to the commonly used value of 8K and the db_file_multiblock_read_count set to the equally commonly used value 8. The results may be a little different under Oracle 9.2

Run the script from Figure 1, which creates a couple of tables, then indexes and analyses them.

create table t1 as
select
trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad(‘x’, 215) v1
from all_objects<
where rownum <= 3000;

create table t2 as
select
mod(rownum,200) n1,
mod(rownum,200) n2,
rpad('x',215) v1
from all_objects
where rownum <= 3000;

create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);

analyze table t1 compute
statistics;
analyze table t2 compute
statistics;

Figure 1: The test data sets.

Once you have got this data in place, you might want to convince yourself that the two sets of data are identical — in particular, that the N1 columns in both data sets have values ranging from 0 to 199, with 15 occurrences of each value. You might try the following check:

select n1, count(*)
from t1
group by n1;

and the matching query against T2 to prove the point.

If you then execute the queries:

select * from t1 where n1 = 45;
select * from t2 where n1 = 45;

You will find that each query returns 15 rows. However if you

set autotrace traceonly explain

you will discover that the two queries have different execution paths.

The query against table T1 uses the index, but the query against table T2 does a full tablescan.

So you have two sets of identical data, with dramatically different access paths for the same query.
What Happened to the Index?

Note: if you've ever come across any of those "magic number" guidelines regarding the use of indexes, e.g., "Oracle will use an index for less than 23 percent, 10 percent, 2 percent (pick number at random) of the data," then you may at this stage begin to doubt their validity. In this example, Oracle has used a tablescan for 15 rows out of 3,000, i.e., for just one half of one percent of the data!

To investigate problems like this, there is one very simple ploy that I always try as the first step: Put in some hints to make Oracle do what I think it ought to be doing, and see if that gives me any clues.

In this case, a simple hint:

/*+ index(t2, t2_i1) */

is sufficient to switch Oracle from the full tablescan to the indexed access path. The three paths with costs (abbreviated to C=nnn) are shown in Figure 2:

select * from t1 where n1 = 45;

EXECUTION PLAN
————–
TABLE ACCESS BY INDEX ROWID OF T1 (C=2)
INDEX(RANGE SCAN) OF T1_I1 (C=1)

select * from t2 where n1 = 45;

EXECUTION PLAN
————–
TABLE ACCESS FULL OF T2 (C=15)

select /*+ index(t2 t2_i1) */
*
from t1
where n1 = 45;

EXECUTION PLAN
————–
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
INDEX(RANGE SCAN) OF T2_I1 (C=1)

Figure 2: The different queries and their costs.

So why hasn't Oracle used the index by default in for the T2 query? Easy — as the execution plan shows, the cost of doing the tablescan is cheaper than the cost of using the index.
Why is the Tablescan Cheaper?

This, of course, is simply begging the question. Why is the cost of the tablescan cheaper than the cost of using the index?

By looking into this question, you uncover the key mechanisms (and critically erroneous assumptions) of the Cost Based Optimiser.

Let's start by examining the indexes by running the query:

select
table_name,
blevel,
avg_data_blocks_per_key,
avg_leaf_blocks_per_key,
clustering_factor
from user_indexes;

The results are given in the table below:
T1 T2
Blevel 1 1
Data block / key 1 15
Leaf block / key 1 1
Clustering factor 96 3000

Note particularly the value for "data blocks per key." This is the number of different blocks in the table that Oracle thinks it will have to visit if you execute a query that contains an equality test on a complete key value for this index.

So where do the costs for our queries come from? As far as Oracle is concerned, if we fire in the key value 45, we get the data from table T1 by hitting one index leaf block and one table block — two blocks, so a cost of two.

If we try the same with table T2, we have to hit one index leaf block and 15 table blocks — a total of 16 blocks, so a cost of 16.

Clearly, according to this viewpoint, the index on table T1 is much more desirable than the index on table T2. This leaves two questions outstanding, though:

Where does the tablescan cost come from, and why are the figures for the avg_data_blocks_per_key so different between the two tables?

The answer to the second question is simple. Look back at the definition of table T1 — it uses the trunc() function to generate the N1 values, dividing the "rownum – 1 "by 15 and truncating.

Trunc(675/15) = 45
Trunc(676/15) = 45

Trunc(689/15) = 45

All the rows with the value 45 do actually appear one after the other in a tight little clump (probably all fitting one data block) in the table.

Table T2 uses the mod() function to generate the N1 values, using modulus 200 on the rownum:

mod(45,200) = 45
mod(245,200) = 45

mod(2845,200) = 45

The rows with the value 45 appear every two hundredth position in the table (probably resulting in no more than one row in every relevant block).

By doing the analyze, Oracle was able to get a perfect description of the data scatter in our table. So the optimiser was able to work out exactly how many blocks Oracle would have to visit to answer our query — and, in simple cases, the number of block visits is the cost of the query.
But Why the Tablescan?

So we see that an indexed access into T2 is more expensive than the same path into T1, but why has Oracle switched to the tablescan?

This brings us to the two simple-minded, and rather inappropriate, assumptions that Oracle makes.

The first is that every block acquisition equates to a physical disk read, and the second is that a multiblock read is just as quick as a single block read.

So what impact do these assumptions have on our experiment?

If you query the user_tables view with the following SQL:

select
table_name,
blocks
from user_tables;

you will find that our two tables each cover 96 blocks.

At the start of the article, I pointed out that the test case was running a version 8 system with the value 8 for the db_file_multiblock_read_count.

Roughly speaking, Oracle has decided that it can read the entire 96 block table in 96/8 = 12 disk read requests.

Since it takes 16 block (= disk read) requests to access the table by index, it is clearer quicker (from Oracle's sadly deluded perspective) to scan the table — after all 12 is less than 16.

Voila! If the data you are targetting is suitably scattered across the table, you get tablescans even for a very small percentage of the data — a problem that can be exaggerated in the case of very big blocks and very small rows.
Correction

In fact, you will have noticed that my calculated number of scan reads was 12, whilst the cost reported in the execution plan was 15. It is a slight simplfication to say that the cost of a tablescan (or an index fast full scan for that matter) is

'number of blocks' /
db_file_multiblock_read_count.

Oracle uses an "adjusted" multi-block read value for the calculation (although it then tries to use the actual requested size when the scan starts to run).

For reference, the following table compares a few of the actual and adjusted values:
Actual Adjusted
4 4.175
8 6.589
16 10.398
32 16.409
64 25.895
128 40.865

As you can see, Oracle makes some attempt to protect you from the error of supplying an unfeasibly large value for this parameter.

There is a minor change in version 9, by the way, where the tablescan cost is further adjusted by adding one to result of the division — which means tablescans in V9 are generally just a little more expensive than in V8, so indexes are just a little more likely to be used.
Adjustments

We have seen that there are two assumptions built into the optimizer that are not very sensible.

* A single block read costs just as much as a multi-block read — (not really likely, particularly when running on file systems without direction)
* A block access will be a physical disk read — (so what is the buffer cache for?)

Since the early days of Oracle 8.1, there have been a couple of parameters that allow us to correct these assumption in a reasonably truthful way.

See Tim Gorman's article for a proper description of these parameters, but briefly:

Optimizer_index_cost_adj takes a value between 1 and 10000 with a default of 100. Effectively, this parameter describes how cheap a single block read is compared to a multiblock read. For example the value 30 (which is often a suitable first guess for an OLTP system) would tell Oracle that a single block read costs 30% of a multiblock read. Oracle would therefore incline towards using indexed access paths for low values of this parameter.

Optimizer_index_caching takes a value between 0 and 100 with a default of 0. This tells Oracle to assume that that percentage of index blocks will be found in the buffer cache. In this case, setting values close to 100 encourages the use of indexes over tablescans.

The really nice thing about both these parameters is that they can be set to "truthful" values.

Set the optimizer_index_caching to something in the region of the "buffer cache hit ratio." (You have to make your own choice about whether this should be the figure derived from the default pool, keep pool, or both).

The optimizer_index_cost_adj is a little more complicated. Check the typical wait times in v$system_event for the events "db file scattered read" (multi block reads) and "db file sequential reads" (single block reads). Divide the latter by the former and multiply by one hundred.
Improvements

Don't forget that the two parameters may need to be adjusted at different times of the day and week to reflect the end-user workload. You can't just derive one pair of figures, and use them for ever.

Happily, in Oracle 9, things have improved. You can now collect system statistics, which are originally included just the four:

+ Average single block read time
+ Average multi block read time
+ Average actual multiblock read
+ Notional usable CPU speed.

Suffice it to say that this feature is worth an article in its own right — but do note that the first three allow Oracle to discover the truth about the cost of multi block reads. And in fact, the CPU speed allows Oracle to work out the CPU cost of unsuitable access mechanisms like reading every single row in a block to find a specific data value and behave accordingly.

When you migrate to version 9, one of the first things you should investigate is the correct use of system statistics. This one feature alone may reduce the amount of time you spend trying to "tune" awkward SQL.

In passing, despite the wonderful effect of system statistics both of the optimizer adjusting parameters still apply — although the exact formula for their use seems to have changed between version 8 and version 9.
Variations on a Theme

Of course, I have picked one very special case — equality on a single column non-unique index, where thare are no nulls in the table — and treated it very simply. (I haven't even mentioned the relevance of the index blevel and clustering_factor yet.) There are numerous different strategies that Oracle uses to work out more general cases.

Consider some of the cases I have conveniently overlooked:

+ Multi-column indexes
+ Part-used multi-column indexes
+ Range scans
+ Unique indexes
+ Non-unique indexes representing unique constraints
+ Index skip scans
+ Index only queries
+ Bitmap indexes
+ Effects of nulls

The list goes on and on. There is no one simple formula that tells you how Oracle works out a cost — there is only a general guideline that gives you the flavour of the approach and a list of different formulae that apply in different cases.

However, the purpose of this article was to make you aware of the general approach and the two assumptions built into the optimiser's strategy. And I hope that this may be enough to take you a long way down the path of understanding the (apparently) strange things that the optimiser has been known to do.

March 11, 2010

How to Troubleshooting Bad Execution Plans

Filed under: [System Performance tuning] — Tags: , — zhefeng @ 11:36 am

Very good sql tuning artical from Greg Rahn

Original Link:

One of the most common performance issues DBAs encounter are bad execution plans. Many try to resolve bad executions plans by setting optimizer related parameters or even hidden underscore parameters. Some even try to decipher a long and complex 10053 trace in hopes to find an answer. While changing parameters or analyzing a 10053 trace might be useful for debugging at some point, I feel there is a much more simple way to start to troubleshoot bad execution plans.

Verify The Query Matches The Business Question

This seems like an obvious thing to do, but I’ve seen numerous cases where the SQL query does not match the business question being asked. Do a quick sanity check verifying things like: join columns, group by, subqueries, etc. The last thing you want to do is consume time trying to debug a bad plan for an improperly written SQL query. Frequently I’ve found that this is the case for many of those “I’ve never got it to run to completion” queries.

What Influences The Execution Plan

I think it’s important to understand what variables influence the Optimizer in order to focus the debugging effort. There are quite a number of variables, but frequently the cause of the problem ones are: (1) non-default optimizer parameters and (2) non-representative object/system statistics. Based on my observations I would say that the most abused Optimizer parameters are:

* OPTIMIZER_INDEX_CACHING
* OPTIMIZER_INDEX_COST_ADJ
* DB_FILE_MULTIBLOCK_READ_COUNT

Many see setting these as a solution to get the Optimizer to choose an index plan over a table scan plan, but this is problematic in several ways:

1. This is a global change to a local problem
2. Although it appears to solve one problem, it is unknown how many bad execution plans resulted from this change
3. The root cause of why the index plan was not chosen is unknown, just that tweaking parameters gave the desired result
4. Using non-default parameters makes it almost impossible to correctly and effectively troubleshoot the root cause

Object and system statistics can have a large influence on execution plans, but few actually take the time to sanity check them during triage. These statistics exist in views like:

* ALL_TAB_COL_STATISTICS
* ALL_PART_COL_STATISTICS
* ALL_INDEXES
* SYS.AUX_STATS$

Using GATHER_PLAN_STATISTICS With DBMS_XPLAN.DISPLAY_CURSOR

As a first step of triage, I would suggest executing the query with a GATHER_PLAN_STATISTICS hint followed by a call to DBMS_XPLAN.DISPLAY_CURSOR. The GATHER_PLAN_STATISTICS hint allows for the collection of extra metrics during the execution of the query. Specifically, it shows us the Optimizer’s estimated number of rows (E-Rows) and the actual number of rows (A-Rows) for each row source. If the estimates are vastly different from the actual, one probably needs to investigate why. For example: In the below plan, look at line 8. The Optimizer estimates 5,899 rows and the row source actually returns 5,479,000 rows. If the estimate is off by three orders of magnitude (1000), chances are the plan will be sub-optimal. Do note that with Nested Loop Joins you need to multiply the Starts column by the E-Rows column to get the A-Rows values (see line 10).
view source
print?
01 select /*+ gather_plan_statistics */ … from … ;
02 select * from table(dbms_xplan.display_cursor(null, null, ‘ALLSTATS LAST’));
03
04 ——————————————————————————————
05 | Id | Operation | Name | Starts | E-Rows | A-Rows |
06 ——————————————————————————————
07 | 1 | SORT GROUP BY | | 1 | 1 | 1 |
08 |* 2 | FILTER | | 1 | | 1728K |
09 | 3 | NESTED LOOPS | | 1 | 1 | 1728K |
10 |* 4 | HASH JOIN | | 1 | 1 | 1728K |
11 | 5 | PARTITION LIST SINGLE | | 1 | 6844 | 3029 |
12 |* 6 | INDEX RANGE SCAN | PROV_IX13 | 1 | 6844 | 3029 |
13 | 7 | PARTITION LIST SINGLE | | 1 | 5899 | 5479K |
14 |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | SERVICE | 1 | 5899 | 5479K |
15 |* 9 | INDEX SKIP SCAN | SERVICE_IX8 | 1 | 4934 | 5479K |
16 | 10 | PARTITION LIST SINGLE | | 1728K | 1 | 1728K |
17 |* 11 | INDEX RANGE SCAN | CLAIM_IX7 | 1728K | 1 | 1728K |
18 ——————————————————————————————

Using The CARDINALITY Hint

Now that I’ve demonstrated how to compare the cardinality estimates to the actual number of rows, what are the debugging options? If one asserts that the Optimizer will choose the optimal plan if it can accurately estimate the number of rows, one can test using the not so well (un)documented CARDINALITY hint. The CARDINALITY hint tells the Optimizer how many rows are coming out of a row source. The hint is generally used like such:
view source
print?
1 select /*+ cardinality(a 100) */ * from dual a;
2
3 ————————————————————————–
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ————————————————————————–
6 | 0 | SELECT STATEMENT | | 100 | 200 | 2 (0)| 00:00:01 |
7 | 1 | TABLE ACCESS FULL| DUAL | 100 | 200 | 2 (0)| 00:00:01 |
8 ————————————————————————–

In this case I told the Optimizer that DUAL would return 100 rows (when in reality it returns 1 row) as seen in the Rows column from the autotrace output. The CARDINALITY hint is one tool one can use to give the Optimizer accurate information. I usually find this the best way to triage a bad plan as it is not a global change, it only effects a single execution of a statement in my session. If luck has it that using a CARDINALITY hint yields an optimal plan, one can move on to debugging where the cardinality is being miscalculated. Generally the bad cardinality is the result of non-representative table/column stats, but it also may be due to data correlation or other factors. This is where it pays off to know and understand the size and shape of the data. If the Optimizer still chooses a bad plan even with the correct cardinality estimates, it’s time to place a call to Oracle Support as more in-depth debugging is likely required.

Where Cardinality Can Go Wrong

There are several common scenarios that can lead to inaccurate cardinality estimates. Some of those on the list are:

1. Data skew: Is the NDV inaccurate due to data skew and a poor dbms_stats sample?
2. Data correlation: Are two or more predicates related to each other?
3. Out-of-range values: Is the predicate within the range of known values?
4. Use of functions in predicates: Is the 5% cardinality guess for functions accurate?
5. Stats gathering strategies: Is your stats gathering strategy yielding representative stats?

Some possible solutions to these issues are:

1. Data skew: Choose a sample size that yields accurate NDV. Use DBMS_STATS.AUTO_SAMPLE_SIZE in 11g.
2. Data correlation: Use Extended Stats in 11g. If <= 10.2.0.3 use a CARDINALITY hint if possible.
3. Out-of-range values: Gather or manually set the statistics.
4. Use of functions in predicates: Use a CARDINALITY hint where possible.
5. Stats gathering strategies: Use AUTO_SAMPLE_SIZE. Adjust only where necessary. Be mindful of tables with skewed data.

How To Best Work With Oracle Support

If you are unable to get to the root cause on your own, it is likely that you will be in contact with Oracle Support. To best assist the support analyst I would recommend you gather the following in addition to the query text:

1. Output from the GATHER_PLAN_STATISTICS and DBMS_XPLAN.DISPLAY_CURSOR
2. SQLTXPLAN output. See Metalink Note 215187.1
3. 10053 trace output. See Metalink Note 225598.1
4. DDL for all objects used (and dependencies) in the query. This is best gotten as a expdp (data pump) using CONTENT=METADATA_ONLY. This will also include the object statistics.
5. Output from: select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
6. A copy of your init.ora

Having this data ready before you even make the call (or create the SR on-line) should give you a jump on getting a quick(er) resolution.

Summary

While this blog post is not meant to be a comprehensive troubleshooting guide for bad execution plans, I do hope that it does help point you in the right direction the next time you encounter one. Many of the Optimizer issues I’ve seen are due to incorrect cardinality estimates, quite often due to inaccurate NDV or the result of data correlation. I believe that if you use a systematic approach you will find that debugging bad execution plans may be as easy as just getting the cardinality estimate correct.

DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS

Filed under: [System Performance tuning] — zhefeng @ 10:14 am

Another very good article about dbms_stats package:
http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/

I’ve written before on choosing an optimal stats gathering strategy but I recently came across a scenario that I didn’t directly blog about and think it deserves attention. As I mentioned in that previous post, one should only deviate from the defaults when they have a reason to, and fully understand that reason and the effect of that decision.

Understanding METHOD_OPT

The METHOD_OPT parameter of DBMS_STATS controls two things:

1. on which columns statistics will be collected
2. on which columns histograms will be collected (and how many buckets)

It is very important to understand #1 and how the choice of METHOD_OPT effects the collection of column statistics.

Prerequisite: Where Do I Find Column Statistics?

Understanding where to find column statistics is vital for troubleshooting bad execution plans. These views will be the arrows in your quiver:

* USER_TAB_COL_STATISTICS
* USER_PART_COL_STATISTICS
* USER_SUBPART_COL_STATISTICS

Depending on if the table is partitioned or subpartitioned, and depending on what GRANULARITY the stats were gathered with, the latter two of those views may or may not be populated.

The Bane of METHOD_OPT: FOR ALL INDEXED COLUMNS

If you are using FOR ALL INDEXED COLUMNS as part of your METHOD_OPT you probably should not be. Allow me to explain. Using MENTOD_OPT=>’FOR ALL INDEXED COLUMNS SIZE AUTO’ (a common METHOD_OPT I see) tells DBMS_STATS: “only gather stats on columns that participate in an index and based on data distribution and the workload of those indexed columns decide if a histogram should be created and how many buckets it should contain“. Is that really what you want? My guess is probably not. Let me work through a few examples to explain why.

I’m going to start with this table.
view source
print?
01 SQL> exec dbms_random.initialize(1);
02
03 PL/SQL procedure successfully completed.
04
05 SQL> create table t1
06 2 as
07 3 select
08 4 column_value pk,
09 5 round(dbms_random.value(1,2)) a,
10 6 round(dbms_random.value(1,5)) b,
11 7 round(dbms_random.value(1,10)) c,
12 8 round(dbms_random.value(1,100)) d,
13 9 round(dbms_random.value(1,100)) e
14 10 from table(counter(1,1000000))
15 11 /
16
17 Table created.
18
19 SQL> begin
20 2 dbms_stats.gather_table_stats(
21 3 ownname => user ,
22 4 tabname => ‘T1′ ,
23 5 estimate_percent => 100 ,
24 6 cascade => true);
25 7 end;
26 8 /
27
28 PL/SQL procedure successfully completed.
29
30 SQL> select
31 2 COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,
32 3 to_char(LAST_ANALYZED,’yyyy-dd-mm hh24:mi:ss’) LAST_ANALYZED
33 4 from user_tab_col_statistics
34 5 where table_name=’T1′
35 6 /
36
37 COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
38 ———– ———— ————— ———– ——————-
39 PK 1000000 NONE 1 2008-13-10 18:39:51
40 A 2 NONE 1 2008-13-10 18:39:51
41 B 5 NONE 1 2008-13-10 18:39:51
42 C 10 NONE 1 2008-13-10 18:39:51
43 D 100 NONE 1 2008-13-10 18:39:51
44 E 100 NONE 1 2008-13-10 18:39:51
45
46 6 rows selected.

This 6 column table contains 1,000,000 rows of randomly generated numbers. I’ve queried USER_TAB_COL_STATISTICS to display some of the important attributes (NDV, Histogram, Number of Buckets, etc).

I’m going to now put an index on T1(PK), delete the stats and recollect stats using two different METHOD_OPT parameters that each use ‘FOR ALL INDEXED COLUMNS’.
view source
print?
01 SQL> create unique index PK_T1 on T1(PK);
02
03 Index created.
04
05 SQL> begin
06 2 dbms_stats.delete_table_stats(user,’T1′);
07 3
08 4 dbms_stats.gather_table_stats(
09 5 ownname => user ,
10 6 tabname => ‘T1’ ,
11 7 estimate_percent => 100 ,
12 8 method_opt => ‘for all indexed columns’ ,
13 9 cascade => true);
14 10 end;
15 11 /
16
17 PL/SQL procedure successfully completed.
18
19 SQL> select COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,
20 2 to_char(LAST_ANALYZED,’yyyy-dd-mm hh24:mi:ss’) LAST_ANALYZED
21 3 from user_tab_col_statistics
22 4 where table_name=’T1′
23 5 /
24
25 COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
26 ———– ———— ————— ———– ——————-
27 PK 1000000 HEIGHT BALANCED 75 2008-13-10 18:41:10
28
29 SQL> begin
30 2 dbms_stats.delete_table_stats(user,’T1′);
31 3
32 4 dbms_stats.gather_table_stats(
33 5 ownname => user ,
34 6 tabname => ‘T1’ ,
35 7 estimate_percent => 100 ,
36 8 method_opt => ‘for all indexed columns size auto’ ,
37 9 cascade => true);
38 10 end;
39 11 /
40
41 PL/SQL procedure successfully completed.
42
43 SQL> select COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,
44 2 to_char(LAST_ANALYZED,’yyyy-dd-mm hh24:mi:ss’) LAST_ANALYZED
45 3 from user_tab_col_statistics
46 4 where table_name=’T1′
47 5 /
48
49 COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
50 ———– ———— ————— ———– ——————-
51 PK 1000000 NONE 1 2008-13-10 18:41:12

Notice that in both cases only column PK has stats on it. Columns A,B,C,D and E do not have any stats collected on them. Also note that when no SIZE clause is specified, it defaults to 75 buckets.

Now one might think that is no big deal or perhaps they do not realize this is happening because they do not look at their stats. Let’s see what we get for cardinality estimates from the Optimizer for a few scenarios.
view source
print?
01 SQL> select /*+ gather_plan_statistics */
02 2 count(*)
03 3 from t1
04 4 where a=1
05 5 /
06
07 COUNT(*)
08 ———-
09 500227
10
11 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
12
13 PLAN_TABLE_OUTPUT
14 ——————————————————————————————
15 SQL_ID 4df0g0r99zmba, child number 0
16 ————————————-
17 select /*+ gather_plan_statistics */ count(*) from t1 where a=1
18
19 Plan hash value: 3724264953
20
21 ————————————————————————————-
22 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
23 ————————————————————————————-
24 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.24 | 3466 |
25 |* 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 500K|00:00:00.50 | 3466 |
26 ————————————————————————————-
27
28 Predicate Information (identified by operation id):
29 —————————————————
30
31 2 – filter(“A”=1)

Notice the E-Rows estimate for T1. The Optimizer is estimating 10,000 rows when in reality there is 500,227. The estimate is off by more than an order of magnitude (50x). Normally the calculation for the cardinality would be (for a one table single equality predicate):
number of rows in T1 * 1/NDV = 1,000,000 * 1/2 = 500,000
but in this case 10,000 is the estimate. Strangely enough (or not), 10,000 is exactly 0.01 (1%) of 1,000,000. Because there are no column stats for T1.A, the Optimizer is forced to make a guess, and that guess is 1%.

As you can see from the 10053 trace (below), since there are no statistics on the column, defaults are used. In this case they yield very poor cardinality estimations.

SINGLE TABLE ACCESS PATH
—————————————–
BEGIN Single Table Cardinality Estimation
—————————————–
Column (#2): A(NUMBER) NO STATISTICS (using defaults)
AvgLen: 13.00 NDV: 31250 Nulls: 0 Density: 3.2000e-05
Table: T1 Alias: T1
Card: Original: 1000000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
—————————————–
END Single Table Cardinality Estimation
—————————————–

Now that I’ve demonstrated how poor the cardinality estimation was with a single equality predicate, let’s see what two equality predicates gives us for a cardinality estimate.
view source
print?
01 SQL> select /*+ gather_plan_statistics */
02 2 count(*)
03 3 from t1
04 4 where a=1
05 5 and b=3
06 6 /
07
08 COUNT(*)
09 ———-
10 124724
11
12 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
13
14 PLAN_TABLE_OUTPUT
15 ——————————————————————————————
16 SQL_ID ctq8q59qdymw6, child number 0
17 ————————————-
18 select /*+ gather_plan_statistics */ count(*) from t1 where a=1 and b=3
19
20 Plan hash value: 3724264953
21
22 ————————————————————————————-
23 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
24 ————————————————————————————-
25 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.19 | 3466 |
26 |* 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 124K|00:00:00.25 | 3466 |
27 ————————————————————————————-
28
29 Predicate Information (identified by operation id):
30 —————————————————
31
32 2 – filter((“A”=1 AND “B”=3))

Yikes. In this case the cardinality estimate is 100 when the actual number of rows is 124,724, a difference of over 3 orders of magnitude (over 1000x). Where did the 100 row estimate come from? In this case there are two equality predicates so the selectivity is calculated as 1% * 1% or 0.01 * 0.01 = 0.0001. 1,000,000 * 0.0001 = 100. Funny that. (The 1% is the default selectivity for an equality predicate w/o stats.)

Now let’s add a derived predicate as well and check the estimates.
view source
print?
01 SQL> select /*+ gather_plan_statistics */
02 2 count(*)
03 3 from t1
04 4 where a=1
05 5 and b=3
06 6 and d+e > 50
07 7 /
08
09 COUNT(*)
10 ———-
11 109816
12
13 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
14
15 PLAN_TABLE_OUTPUT
16 ——————————————————————————————
17 SQL_ID 5x200q9rqvvfu, child number 0
18 ————————————-
19 select /*+ gather_plan_statistics */ count(*) from t1 where a=1 and b=3
20 and d+e > 50
21
22 Plan hash value: 3724264953
23
24 ————————————————————————————-
25 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
26 ————————————————————————————-
27 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.22 | 3466 |
28 |* 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 109K|00:00:00.33 | 3466 |
29 ————————————————————————————-
30
31 Predicate Information (identified by operation id):
32 —————————————————
33
34 2 – filter((“A”=1 AND “B”=3 AND “D”+”E”>50))

Doh! The cardinality estimate is now 5, but the actual number of rows being returned is 109,816. Not good at all. The Optimizer estimated 5 rows because it used a default selectivity of 1% (for A=1) * 1% (for B=3) * 5% (for D+E > 50) * 1,000,000 rows. Now can you see why column statistics are very important? All it takes is a few predicates and the cardinality estimation becomes very small, very fast. Now consider this:

* What is likely to happen in a data warehouse where the queries are 5+ table joins and the fact table columns do not have indexes?
* Would the Optimizer choose the correct driving table?
* Would nested loops plans probably be chosen when it is really not appropriate?

Hopefully you can see where this is going. If you don’t, here is the all too common chain of events:

* Non representative (or missing) statistics lead to
* Poor cardinality estimates which leads to
* Poor access path selection which leads to
* Poor join method selection which leads to
* Poor join order selection which leads to
* Poor SQL execution times

Take 2: Using the Defaults

Now I’m going to recollect stats with a default METHOD_OPT and run through the 3 execution plans again:
view source
print?
01 SQL> begin
02 2 dbms_stats.delete_table_stats(user,’t1′);
03 3
04 4 dbms_stats.gather_table_stats(
05 5 ownname => user ,
06 6 tabname => ‘T1′ ,
07 7 estimate_percent => 100 ,
08 8 degree => 8,
09 9 cascade => true);
10 10 end;
11 11 /
12
13 PL/SQL procedure successfully completed.
14
15 SQL> select column_name, num_distinct, histogram, NUM_BUCKETS,
16 2 to_char(LAST_ANALYZED,’yyyy-dd-mm hh24:mi:ss’) LAST_ANALYZED
17 3 from user_tab_col_statistics where table_name=’T1′
18 4 /
19
20 COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
21 ———– ———— ————— ———– ——————-
22 PK 1000000 NONE 1 2008-13-10 19:44:32
23 A 2 FREQUENCY 2 2008-13-10 19:44:32
24 B 5 FREQUENCY 5 2008-13-10 19:44:32
25 C 10 FREQUENCY 10 2008-13-10 19:44:32
26 D 100 NONE 1 2008-13-10 19:44:32
27 E 100 NONE 1 2008-13-10 19:44:32
28
29 6 rows selected.
view source
print?
01 SQL> select /*+ gather_plan_statistics */
02 2 count(*)
03 3 from t1
04 4 where a=1
05 5 /
06
07 COUNT(*)
08 ———-
09 500227
10
11 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
12
13 PLAN_TABLE_OUTPUT
14 ——————————————————————————————
15 SQL_ID 4df0g0r99zmba, child number 0
16 ————————————-
17 select /*+ gather_plan_statistics */ count(*) from t1 where a=1
18
19 Plan hash value: 3724264953
20
21 ————————————————————————————-
22 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
23 ————————————————————————————-
24 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.20 | 3466 |
25 |* 2 | TABLE ACCESS FULL| T1 | 1 | 500K| 500K|00:00:00.50 | 3466 |
26 ————————————————————————————-
27
28 Predicate Information (identified by operation id):
29 —————————————————
30
31 2 – filter(“A”=1)
view source
print?
01 SQL> select /*+ gather_plan_statistics */
02 2 count(*)
03 3 from t1
04 4 where a=1
05 5 and b=3
06 6 /
07
08 COUNT(*)
09 ———-
10 124724
11
12 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
13
14 PLAN_TABLE_OUTPUT
15 ——————————————————————————————
16 SQL_ID ctq8q59qdymw6, child number 0
17 ————————————-
18 select /*+ gather_plan_statistics */ count(*) from t1 where a=1 and b=3
19
20 Plan hash value: 3724264953
21
22 ————————————————————————————-
23 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
24 ————————————————————————————-
25 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.14 | 3466 |
26 |* 2 | TABLE ACCESS FULL| T1 | 1 | 124K| 124K|00:00:00.25 | 3466 |
27 ————————————————————————————-
28
29 Predicate Information (identified by operation id):
30 —————————————————
31
32 2 – filter((“B”=3 AND “A”=1))
view source
print?
01 SQL> select /*+ gather_plan_statistics */
02 2 count(*)
03 3 from t1
04 4 where a=1
05 5 and b=3
06 6 and d+e > 50
07 7 /
08
09 COUNT(*)
10 ———-
11 109816
12
13 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
14
15 PLAN_TABLE_OUTPUT
16 ——————————————————————————————
17 SQL_ID 5x200q9rqvvfu, child number 0
18 ————————————-
19 select /*+ gather_plan_statistics */ count(*) from t1 where a=1 and b=3
20 and d+e>50
21
22 Plan hash value: 3724264953
23
24 ————————————————————————————-
25 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
26 ————————————————————————————-
27 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.17 | 3466 |
28 |* 2 | TABLE ACCESS FULL| T1 | 1 | 6236 | 109K|00:00:00.22 | 3466 |
29 ————————————————————————————-
30
31 Predicate Information (identified by operation id):
32 —————————————————
33
34 2 – filter((“B”=3 AND “A”=1 AND “D”+”E”>50))

As you can see, the first two queries have spot on cardinality estimates, but the the third query isn’t as good as it uses a column combination and there are no stats on D+E columns, only D and E individually. I’m going to rerun the third query with dynamic sampling set to 4 (in 10g it defaults to 2) and reevaluate the cardinality estimate.
view source
print?
01 SQL> alter session set optimizer_dynamic_sampling=4;
02
03 Session altered.
04
05 SQL> select /*+ gather_plan_statistics */
06 2 count(*)
07 3 from t1
08 4 where a=1
09 5 and b=3
10 6 and d+e > 50
11 7 /
12
13 COUNT(*)
14 ———-
15 109816
16
17 SQL> select * from table(dbms_xplan.display_cursor(null, null, ‘allstats last’));
18
19 PLAN_TABLE_OUTPUT
20 ——————————————————————————————
21 SQL_ID 5x200q9rqvvfu, child number 1
22 ————————————-
23 select /*+ gather_plan_statistics */ count(*) from t1 where a=1 and b=3
24 and d+e > 50
25
26 Plan hash value: 3724264953
27
28 ————————————————————————————-
29 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
30 ————————————————————————————-
31 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.17 | 3466 |
32 |* 2 | TABLE ACCESS FULL| T1 | 1 | 102K| 109K|00:00:00.22 | 3466 |
33 ————————————————————————————-
34
35 Predicate Information (identified by operation id):
36 —————————————————
37
38 2 – filter((“B”=3 AND “A”=1 AND “D”+”E”>50))
39
40 Note
41 —–
42 – dynamic sampling used for this statement

Bingo! Close enough to call statistically equivalent.

Summary

I hope this little exercise demonstrates how important it is to have representative statistics and that when statistics are representative the Optimizer can very often accurately estimate the cardinality and thus choose the best plan for the query. Remember these points:

* Recent statistics do not necessarily equate to representative statistics.
* Statistics are required on all columns to yield good plans – not just indexed columns.
* You probably should not be using METHOD_OPT => ‘FOR ALL INDEXED COLUMNS SIZE AUTO’, especially in a data warehouse where indexes are used sparingly.
* Dynamic Sampling can assist with cardinality estimates where existing stats are not enough.

March 10, 2010

Using Histograms to Help Oracle Cost-Based Optimizer Make Better Decisions

Filed under: [System Performance tuning] — Tags: , , , — zhefeng @ 5:36 pm

Find a very good article talking about histogram, here is the original link:
http://support.confio.com/blog/tag/methodopt/38/

Introduction

Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL and the data distribution is skewed.

Example

Assume a table named PROCESS_QUEUE with one million rows including a column named PROCESSED_FLAG with five distinct values. Also assume a query similar to the following is executed:

SELECT id, serial_number

FROM process_queue
WHERE processed_flag = ‘N’;

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1087 Card=260363 Bytes=7029801)
TABLE ACCESS (FULL) OF ‘PROCESS_QUEUE’ (TABLE) (Cost=1087 Card=260363 Bytes=7029801)

Without histograms and only five distinct values, Oracle assumes an even data distribution and would most likely perform a full table scan for this query. With one million rows and five values, Oracle assumes that each value would return 200,000 rows, or 20% of the rows.

Data Skew

However, what if the data for the PROCESSED_FLAG column was skewed:

SELECT processed_flag, COUNT(1)
FROM process_queue
GROUP BY processed_flag;

PROCESSED_FLAG COUNT
——————————- ———-
P 24
Y 999345
E 30
S 568
N 33

In this case, ony 33 rows have a value of ‘N’, so there has to be a way to tell Oracle to use the index on the PROCESSED_FLAG column. That is where histograms come into use. A histogram would include data similar to above and allow Oracle to know that only 33 rows would be returned for this query.

Collecting Histograms

To collect histograms for this column, a command similar to the following could be used:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(user, ‘PROCESS_QUEUE’, method_opt => ‘for columns processed_flag size 5’)

SELECT id, serial_number
FROM process_queue
WHERE processed_flag = ‘N’;

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=28 Bytes=756)
TABLE ACCESS (BY INDEX ROWID) OF ‘PROCESS_QUEUE’ (TABLE) (Cost=1 Card=28 Bytes=756)
INDEX (RANGE SCAN) OF ‘PQ_IX1’ (INDEX) (Cost=1 Card=28)

Notes About Histograms

Note 1: Using histograms works best for SQL statements that use literal values. If a statement uses a bind variable, the first time the query is parsed, Oracle will peek at the value of the bind variable and choose a plan accordingly. That same plan will be used until the SQL is reparsed. In this case, if the bind variable was ‘Y’ the first time, Oracle may perform a full table scan for this query no matter what value was passed in from then on.

The opposite may also be true. Assume a similar data distribution to above but with 100 distinct values for the PROCESSED_FLAG column. The rows that have a ‘Y’ value are still be 95% of the rows. However, if you used the criteria “WHERE processed_flag=’Y'”, without histograms Oracle may decide to use the index when a full table scan may be a better option.

Note 2: The defaults for the METHOD_OPT parameter changed between Oracle 9i and 10g. In 9i the parameter defaulted to ‘for all columns size 1’ which essentially turns off histograms. The default value in Oracle 10g is ‘for all columns size auto’ which means that Oracle will decide whether or not to collect histograms for a column. In my experience it seems that unneccesary histograms are collected and histogram data is not collected for some columns where it would be useful.

Conclusion

Histograms allow Oracle to make much better performance decisions. The case we discussed in this article is one way that histograms are used and is commonly referred to as “table access method” histograms. Another use for histograms, referred to as “table order join” histograms, is to help Oracle decide the order in which tables will be joined. This helps the CBO know the size of the result sets or “cardinality” to properly determine the correct order in which to do joins.

March 8, 2010

Index Full Scan vs Index Fast Full Scan

Filed under: [System Performance tuning] — Tags: , , , — zhefeng @ 2:06 pm

http://spaces.msn.com/members/wzwanghai/

[Oracle] Index Full Scan vs Index Fast Full Scan
作者:汪海 (Wanghai)
日期:14-Aug-2005 
出处:http://spaces.msn.com/members/wzwanghai/

Index Full Scan vs Index Fast Full Scan

index full scan和index fast full scan是指同样的东西吗?答案是no。两者虽然从字面上看起来差不多,但是实现的机制完全不同。我们一起来看看两者的区别在哪里?

首先来看一下IFS,FFS能用在哪里:在一句sql中,如果我们想搜索的列都包含在索引里面的话,那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句:

SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;

SQL> CREATE INDEX ind_test_id ON TEST(object_id);

SQL> INSERT INTO TEST
SELECT *
FROM dba_objects
WHERE object_id IS NOT NULL AND object_id > 10000
ORDER BY object_id DESC;

17837 rows created.

SQL> analyze table test compute statistics for table for all columns for all indexes;

Table analyzed.

SQL> set autotrace trace;

SQL> select object_id from test;

17837 rows selected.

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
1 0 TABLE ACCESS (FULL) OF ‘TEST’ (Cost=68 Card=17837 Bytes=71348)

这时候 Oracle会选择全表扫描,因为 object_id 列默认是可以为null的,来修改成 not null:

SQL>alter table test modify(object_id not null);

SQL> select object_id from test;

17837 rows selected.

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
1 0 INDEX (FAST FULL SCAN) OF ‘IND_TEST_ID’ (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)

当然我们也可以使用index full scan:

SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test;

17837 rows selected.

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
1 0 INDEX (FULL SCAN) OF ‘IND_TEST_ID’ (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)

我们看到了两者都可以在这种情况下使用,那么他们有什么区别呢?有个地方可以看出两者的区别, 来看一下两者的输出结果,为了让大家看清楚一点,我们只取10行。

INDEX FAST FULL SCAN

SQL> select object_id from test where rownum select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum select object_id from dba_objects where object_name=’IND_TEST_ID’;

OBJECT_ID
———-
70591

索引的object_id为70591,使用tree dump可以看到索引树的结构

SQL> ALTER SESSION SET EVENTS ‘immediate trace name TREEDUMP level 70591’;

—– begin tree dump
branch: 0x6809b8d 109091725 (0: nrow: 100, level: 1)
leaf: 0x6809b96 109091734 (-1: nrow: 294 rrow: 0)
leaf: 0x6c07ec1 113278657 (0: nrow: 262 rrow: 0)
leaf: 0x6c07ebd 113278653 (1: nrow: 518 rrow: 0)
leaf: 0x6c07eb1 113278641 (2: nrow: 524 rrow: 0)
leaf: 0x6c07ead 113278637 (3: nrow: 524 rrow: 0)
leaf: 0x6c07ea9 113278633 (4: nrow: 524 rrow: 0)
leaf: 0x6c07ea5 113278629 (5: nrow: 524 rrow: 0)
leaf: 0x6c07ea1 113278625 (6: nrow: 524 rrow: 0)
leaf: 0x6c07e9d 113278621 (7: nrow: 524 rrow: 0)
leaf: 0x6c07e99 113278617 (8: nrow: 524 rrow: 0)
leaf: 0x6c07e95 113278613 (9: nrow: 532 rrow: 0)
leaf: 0x6c07e91 113278609 (10: nrow: 524 rrow: 0)
leaf: 0x6c07e8d 113278605 (11: nrow: 524 rrow: 0)
leaf: 0x6c07ec8 113278664 (12: nrow: 524 rrow: 0)
leaf: 0x6c07ec4 113278660 (13: nrow: 524 rrow: 0)
leaf: 0x6c07ec0 113278656 (14: nrow: 524 rrow: 0)
leaf: 0x6c07ebc 113278652 (15: nrow: 524 rrow: 0)
leaf: 0x6809bb2 109091762 (16: nrow: 524 rrow: 0)
leaf: 0x6c07eb8 113278648 (17: nrow: 524 rrow: 0)
leaf: 0x6c07eb4 113278644 (18: nrow: 524 rrow: 0)
leaf: 0x6c07eb0 113278640 (19: nrow: 524 rrow: 0)
leaf: 0x6c07eac 113278636 (20: nrow: 524 rrow: 0)
leaf: 0x6809bae 109091758 (21: nrow: 524 rrow: 0)
leaf: 0x6c07ea8 113278632 (22: nrow: 524 rrow: 0)
leaf: 0x6c07ea4 113278628 (23: nrow: 524 rrow: 0)
leaf: 0x6c07ea0 113278624 (24: nrow: 105 rrow: 105)
leaf: 0x6c07e9c 113278620 (25: nrow: 129 rrow: 129)
leaf: 0x6c07eb9 113278649 (26: nrow: 123 rrow: 123)
leaf: 0x6809baa 109091754 (27: nrow: 246 rrow: 246)
leaf: 0x6c07e98 113278616 (28: nrow: 246 rrow: 246)
leaf: 0x6c07e94 113278612 (29: nrow: 246 rrow: 246)
leaf: 0x6809ba6 109091750 (30: nrow: 246 rrow: 246)
leaf: 0x6809bce 109091790 (31: nrow: 246 rrow: 246)
leaf: 0x6809bca 109091786 (32: nrow: 246 rrow: 246)
leaf: 0x6809c05 109091845 (33: nrow: 248 rrow: 248)
leaf: 0x6809c01 109091841 (34: nrow: 246 rrow: 246)
leaf: 0x6809bfd 109091837 (35: nrow: 246 rrow: 246)
leaf: 0x6809bf9 109091833 (36: nrow: 246 rrow: 246)
leaf: 0x6809bf5 109091829 (37: nrow: 246 rrow: 246)
leaf: 0x6809bf1 109091825 (38: nrow: 246 rrow: 246)
leaf: 0x6809bed 109091821 (39: nrow: 246 rrow: 246)
leaf: 0x6809be9 109091817 (40: nrow: 246 rrow: 246)
leaf: 0x6809be5 109091813 (41: nrow: 246 rrow: 246)
leaf: 0x6809be1 109091809 (42: nrow: 246 rrow: 246)
leaf: 0x6809bdd 109091805 (43: nrow: 246 rrow: 246)
leaf: 0x6809bd9 109091801 (44: nrow: 246 rrow: 246)
leaf: 0x6809bd5 109091797 (45: nrow: 246 rrow: 246)
leaf: 0x6809bd1 109091793 (46: nrow: 248 rrow: 248)
leaf: 0x6809bcd 109091789 (47: nrow: 246 rrow: 246)
leaf: 0x6809bc9 109091785 (48: nrow: 246 rrow: 246)
leaf: 0x6809c08 109091848 (49: nrow: 246 rrow: 246)
leaf: 0x6809c04 109091844 (50: nrow: 246 rrow: 246)
leaf: 0x6809c00 109091840 (51: nrow: 246 rrow: 246)
leaf: 0x6809bfc 109091836 (52: nrow: 246 rrow: 246)
leaf: 0x6809bf8 109091832 (53: nrow: 246 rrow: 246)
leaf: 0x6809bf4 109091828 (54: nrow: 246 rrow: 246)
leaf: 0x6809bf0 109091824 (55: nrow: 246 rrow: 246)
leaf: 0x6809bec 109091820 (56: nrow: 246 rrow: 246)
leaf: 0x6809be8 109091816 (57: nrow: 246 rrow: 246)
leaf: 0x6809be4 109091812 (58: nrow: 246 rrow: 246)
leaf: 0x6809be0 109091808 (59: nrow: 248 rrow: 248)
leaf: 0x6809bdc 109091804 (60: nrow: 246 rrow: 246)
leaf: 0x6809bd8 109091800 (61: nrow: 246 rrow: 246)
leaf: 0x6809bd4 109091796 (62: nrow: 246 rrow: 246)
leaf: 0x6809bd0 109091792 (63: nrow: 246 rrow: 246)
leaf: 0x6809bcc 109091788 (64: nrow: 246 rrow: 246)
leaf: 0x6809c07 109091847 (65: nrow: 246 rrow: 246)
leaf: 0x6809c03 109091843 (66: nrow: 246 rrow: 246)
leaf: 0x6809bff 109091839 (67: nrow: 246 rrow: 246)
leaf: 0x6809bfb 109091835 (68: nrow: 246 rrow: 246)
leaf: 0x6809bf7 109091831 (69: nrow: 246 rrow: 246)
leaf: 0x6809bf3 109091827 (70: nrow: 246 rrow: 246)
leaf: 0x6809bef 109091823 (71: nrow: 246 rrow: 246)
leaf: 0x6809beb 109091819 (72: nrow: 248 rrow: 248)
leaf: 0x6809be7 109091815 (73: nrow: 246 rrow: 246)
leaf: 0x6809be3 109091811 (74: nrow: 246 rrow: 246)
leaf: 0x6809bdf 109091807 (75: nrow: 246 rrow: 246)
leaf: 0x6809bdb 109091803 (76: nrow: 246 rrow: 246)
leaf: 0x6809bd7 109091799 (77: nrow: 246 rrow: 246)
leaf: 0x6809bd3 109091795 (78: nrow: 246 rrow: 246)
leaf: 0x6809bcf 109091791 (79: nrow: 246 rrow: 246)
leaf: 0x6809bcb 109091787 (80: nrow: 246 rrow: 246)
leaf: 0x6809c06 109091846 (81: nrow: 246 rrow: 246)
leaf: 0x6809c02 109091842 (82: nrow: 246 rrow: 246)
leaf: 0x6809bfe 109091838 (83: nrow: 246 rrow: 246)
leaf: 0x6809bfa 109091834 (84: nrow: 246 rrow: 246)
leaf: 0x6809ba2 109091746 (85: nrow: 129 rrow: 129)
leaf: 0x6c07eb5 113278645 (86: nrow: 123 rrow: 123)
leaf: 0x6809bf6 109091830 (87: nrow: 246 rrow: 246)
leaf: 0x6809bf2 109091826 (88: nrow: 246 rrow: 246)
leaf: 0x6809bee 109091822 (89: nrow: 246 rrow: 246)
leaf: 0x6809bea 109091818 (90: nrow: 246 rrow: 246)
leaf: 0x6809b9e 109091742 (91: nrow: 246 rrow: 246)
leaf: 0x6809be6 109091814 (92: nrow: 246 rrow: 246)
leaf: 0x6809be2 109091810 (93: nrow: 246 rrow: 246)
leaf: 0x6809bde 109091806 (94: nrow: 246 rrow: 246)
leaf: 0x6809bda 109091802 (95: nrow: 246 rrow: 246)
leaf: 0x6809b9a 109091738 (96: nrow: 246 rrow: 246)
leaf: 0x6809bd6 109091798 (97: nrow: 246 rrow: 246)
leaf: 0x6809bd2 109091794 (98: nrow: 246 rrow: 246)
—– end tree dump

index full scan读取的是0x6c07ea0 这个块,而index fast full scan读取的是 0x6809b9a这个块也就是包含数据的物理存储位置最前的块。分别看一下这两个块的内容
0x6c07ea0 =十进制的113278624
0x6809b9a =十进制的109091738

SQL> select dbms_utility.data_block_address_file(113278624) “file”,dbms_utility.data_block_address_block(113278624) “block” from dual;

file block
———- ———-
27 32416

SQL> select dbms_utility.data_block_address_file(109091738) “file”,dbms_utility.data_block_address_block(109091738)”block” from dual;

file block
———- ———-
26 39834

SQL> alter system dump datafile 27 block 32416;

SQL> alter system dump datafile 26 block 39834;

block 32416的前10行

row#0[6564] flag: —–, lock: 2
col 0; len 4; (4): c3 02 07 11
col 1; len 6; (6): 07 00 7c 20 00 2b
row#1[6578] flag: —–, lock: 2
col 0; len 4; (4): c3 02 16 4e
col 1; len 6; (6): 07 00 7c 20 00 2a
row#2[6592] flag: —–, lock: 2
col 0; len 4; (4): c3 02 16 4f
col 1; len 6; (6): 07 00 7c 20 00 29
row#3[6606] flag: —–, lock: 2
col 0; len 4; (4): c3 02 16 50
col 1; len 6; (6): 07 00 7c 20 00 28
row#4[6620] flag: —–, lock: 2
col 0; len 4; (4): c3 02 18 02
col 1; len 6; (6): 07 00 7c 20 00 27
row#5[6634] flag: —–, lock: 2
col 0; len 4; (4): c3 02 23 60
col 1; len 6; (6): 07 00 7c 20 00 26
row#6[6648] flag: —–, lock: 2
col 0; len 4; (4): c3 02 24 25
col 1; len 6; (6): 07 00 7c 20 00 25
row#7[6662] flag: —–, lock: 2
col 0; len 4; (4): c3 02 24 28
col 1; len 6; (6): 07 00 7c 20 00 24
row#8[6676] flag: —–, lock: 2
col 0; len 4; (4): c3 02 28 18
col 1; len 6; (6): 07 00 7c 20 00 23
row#9[6690] flag: —–, lock: 2
col 0; len 4; (4): c3 02 42 04
col 1; len 6; (6): 07 00 7c 20 00 22

block 39834的前10行
row#0[4591] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 43
col 1; len 6; (6): 02 81 71 f6 00 36
row#1[4605] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 44
col 1; len 6; (6): 02 81 71 f6 00 35
row#2[4619] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 45
col 1; len 6; (6): 02 81 71 f6 00 34
row#3[4633] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 46
col 1; len 6; (6): 02 81 71 f6 00 33
row#4[4647] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 47
col 1; len 6; (6): 02 81 71 f6 00 32
row#5[4661] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 48
col 1; len 6; (6): 02 81 71 f6 00 31
row#6[4675] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 49
col 1; len 6; (6): 02 81 71 f6 00 30
row#7[4689] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 4a
col 1; len 6; (6): 02 81 71 f6 00 2f
row#8[4703] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 4b
col 1; len 6; (6): 02 81 71 f6 00 2e
row#9[4717] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 4c
col 1; len 6; (6): 02 81 71 f6 00 2d

对照一下前面的结果集
block 32416的第一行为10616,数据内的存储格式应该为

SQL> select dump(10616,16) from dual;

DUMP(10616,16)
———————-
Typ=2 Len=4: c3,2,7,11

确实等于dump block所看到的

row#0[6564] flag: —–, lock: 2
col 0; len 4; (4): c3 02 07 11
col 1; len 6; (6): 07 00 7c 20 00 2b

再看block 39834的第1行

SQL> select dump(66266,16) from dual;

DUMP(66266,16)
———————–
Typ=2 Len=4: c3,7,3f,43

跟dump 的结果也一样

row#0[4591] flag: —–, lock: 2
col 0; len 4; (4): c3 07 3f 43
col 1; len 6; (6): 02 81 71 f6 00 36

这就证明了上面所说的index full scan和index fast full scan的不同。
我们也可以用10046事件去跟踪两者走的路径。

SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;

(清空buffer cache,以便观看’db file sequential read’,’db file scattered read’事件)。

SQL> alter session set events’10046 trace name context forever,level 12′;

Session altered.

SQL> select object_id from test where rownum alter session set events’10046 trace name context off’;

Session altered.

[oracle@csdbc udump]$ grep read cs-dbc_ora_15596.trc

Redo thread mounted by this instance: 1
WAIT #1: nam=’db file sequential read’ ela= 33 p1=26 p2=39820 p3=1
WAIT #1: nam=’db file sequential read’ ela= 21 p1=26 p2=39817 p3=1
WAIT #1: nam=’db file sequential read’ ela= 17 p1=26 p2=39819 p3=1
WAIT #1: nam=’db file parallel read’ ela= 53 p1=2 p2=2 p3=2
WAIT #1: nam=’db file scattered read’ ela= 466 p1=26 p2=39821 p3=16

最前面的’db file sequential read’是由于读段头等操作,我们来关注’db file scattered read’事件,因为index fast full scan是采用多块读,从39821开始读取db_file_multiblock_read_count个块(本例里设置为16)。我们关心的 39834块正位于其中。
再来看index full scan的10046 trace

SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;

(清空buffer cache,以便观看’db file sequential read’,’db file scattered read’事件)。

SQL> alter session set events’10046 trace name context forever,level 12′;

Session altered.

SQL>

OBJECT_ID
———-
10616
12177
12178
12179
12301
13495
13536
13539
13923
16503

10 rows selected.

SQL> alter session set events’10046 trace name context off’;

Session altered.

[oracle@csdbc udump]$ grep read cs-dbc_ora_15609.trc

Redo thread mounted by this instance: 1
WAIT #1: nam=’db file sequential read’ ela= 49 p1=26 p2=39821 p3=1
root block,正是先前索引树dump里面的 0x6809b8d
WAIT #1: nam=’db file sequential read’ ela= 32 p1=26 p2=39830 p3=1
WAIT #1: nam=’db file sequential read’ ela= 40 p1=27 p2=32449 p3=1
WAIT #1: nam=’db file sequential read’ ela= 35 p1=27 p2=32445 p3=1
WAIT #1: nam=’db file sequential read’ ela= 28 p1=27 p2=32433 p3=1
WAIT #1: nam=’db file sequential read’ ela= 19 p1=27 p2=32429 p3=1
WAIT #1: nam=’db file sequential read’ ela= 34 p1=27 p2=32425 p3=1
WAIT #1: nam=’db file sequential read’ ela= 32 p1=27 p2=32421 p3=1
WAIT #1: nam=’db file sequential read’ ela= 33 p1=27 p2=32417 p3=1
WAIT #1: nam=’db file sequential read’ ela= 29 p1=27 p2=32413 p3=1
WAIT #1: nam=’db file sequential read’ ela= 37 p1=27 p2=32409 p3=1
WAIT #1: nam=’db file sequential read’ ela= 32 p1=27 p2=32405 p3=1
WAIT #1: nam=’db file sequential read’ ela= 35 p1=27 p2=32401 p3=1
WAIT #1: nam=’db file sequential read’ ela= 34 p1=27 p2=32397 p3=1
WAIT #1: nam=’db file sequential read’ ela= 31 p1=27 p2=32456 p3=1
WAIT #1: nam=’db file sequential read’ ela= 29 p1=27 p2=32452 p3=1
WAIT #1: nam=’db file sequential read’ ela= 31 p1=27 p2=32448 p3=1
WAIT #1: nam=’db file sequential read’ ela= 30 p1=27 p2=32444 p3=1
WAIT #1: nam=’db file sequential read’ ela= 38 p1=26 p2=39858 p3=1
WAIT #1: nam=’db file sequential read’ ela= 31 p1=27 p2=32440 p3=1
WAIT #1: nam=’db file sequential read’ ela= 32 p1=27 p2=32436 p3=1
WAIT #1: nam=’db file sequential read’ ela= 35 p1=27 p2=32432 p3=1
WAIT #1: nam=’db file sequential read’ ela= 31 p1=27 p2=32428 p3=1
WAIT #1: nam=’db file sequential read’ ela= 29 p1=26 p2=39854 p3=1
WAIT #1: nam=’db file sequential read’ ela= 36 p1=27 p2=32424 p3=1
WAIT #1: nam=’db file sequential read’ ela= 32 p1=27 p2=32420 p3=1
WAIT #1: nam=’db file sequential read’ ela= 36 p1=27 p2=32416 p3=1

index full scan走的路径正是文章开始所提到的定位到root block,然后根据leaf block链表一路读取块。看到这里大家应该比较了解index full scan 和index fast full scan的区别了,最后补充一下 index full scan 和 index fast full scan 在排序上的不同。

SQL> set autotrace trace;

SQL> select object_id from test order by object_id;

17837 rows selected.

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
1 0 INDEX (FULL SCAN) OF ‘IND_TEST_ID’ (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)

由于有排序所以oracle自动选择了index full scan避免了排序。那么强制用index fast full scan呢?

SQL> select/*+ index_ffs(test ind_test_id)*/object_id from test order by object_id;
17837 rows selected.

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=17837 Bytes=71348)
1 0 SORT (ORDER BY) (Cost=59 Card=17837 Bytes=71348)
2 1 INDEX (FAST FULL SCAN) OF ‘IND_TEST_ID’ (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)

index fast full scan会多一步sort order by,相信仔细看过这篇文章的人能知道其中结果了吧,还不知道的人请在文章中自己找答案吧。

December 10, 2009

useful windows logo key tips

Filed under: 6. MISC — zhefeng @ 10:32 am

Press To
Windows logo key Display or hide the Start menu
Windows logo key+L Lock the computer
Windows logo key+BREAK Display the System Properties dialog box
Windows logo key+D Show the desktop
Windows logo key+M Minimize all windows
Windows logo key+SHIFT+M Restore minimized windows
Windows logo key+E Open My Computer
Windows logo key+F Search for a file or folder
CTRL+Windows logo key+F Search for computers
Windows logo key+F1 Display Windows Help
Windows logo key+R Open the Run dialog box
Windows logo key+U Open Utility Manager

Application key Display the Shortcut menu for the selected item

November 12, 2009

dbconsole can’t be started with ssl error

Filed under: [RAC] — Tags: , , , — zhefeng @ 2:36 pm

Got problem like failing to start dbconsole, check the trace file got this:
emdctl.trc
———–
2008-09-15 10:58:20 Thread-4136126688 ERROR http: 8: Unable to initialize ssl connection with
server, aborting connection attempt
2008-09-15 10:59:52 Thread-4136126688 ERROR ssl: nzos_Handshake failed, ret=29024.

After searching the metalink, found just need to unsecure and resecure the dbconsole to renew the expired dbconsole certificate:

1. Unsecure the Dbconsole
– Unsecure database control using
$ORACLE_HOME/bin>emctl unsecure dbconsole

2. Force an upload:

$ORACLE_HOME/bin> emctl upload

3. Also consider Resecuring the Dbconsole
– Secure database control using
$ORACLE_HOME/bin>emctl secure dbconsole

Starting with 10.2.0.4, HTTPS is used by default.

chm can’t be opened

Filed under: 6. MISC — Tags: , — zhefeng @ 2:21 pm

My desktop somehow couldn’t open the chm file which was ok before.
After researching i found it’s the microsoft updates caused this:
http://support.microsoft.com/kb/896358

The solution will be open permissions for this kind of chm file. Copy/paste the following code to a text file then save it as .reg, run it and resolve the problem!

REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\HHRestrictions]
“MaxAllowedZone”=dword:00000001
“EnableFrameNavigationInSafeMode”=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\ItssRestrictions]
“MaxAllowedZone”=dword:00000001

« Newer PostsOlder Posts »

Blog at WordPress.com.