XPLAN PLANS Made Easy

Found this on David Aldridge's blog ... http://oraclesponge.wordpress.com

he DBMS_XPLAN supplied package gives results that are so much more usable than the old "set autotrace traceonly explain", or any manual method of reading the plan_table, that the only thing holding me back from using it more often was the lengthy syntax.

No more: I have created views to save my aching fingers. Feel free to use them, modify them, whatever for your own purposes -- just don't blame me if they break something ;)

create or replace view xplan_basic as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));*

create or replace view xplan_typical as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));

create or replace view xplan_parallel as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));

create or replace view xplan_serial as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));

I am now a mere "select * from xplan_parallel" away from my execution plans.

Thanks David.

wireless keyboard

Brought a wireless keyboard and mouse a few days ago. It works a treat. On the mac it asked me to configure before I logged in and away it went.

To my delight, most of the special keys on the keyboard work on the mac. pretty good work from microsoft. The only problem is that it is black !!!

Keyboards

Purchased a new keyboard and mouse (of the wireless variety) today. My lovely wife spilt water over the old keyboard and now it doesn't work. boo hoo.

As I have a mac at home, the keyboard was a mac. Seems to me as though apple design some really nice hardware, but ofter they don't think about the practacality of things (like building a keyboard that still works after some water is thrown on it).

snapshot too old errors ...

There is a bug with oracle 9i and materialized views whereby there are numerous snapshot too old errors. This bug is around the source database whereby the optimizer is giving an incorrect execution plan, when the parameter star_transformation_enabled is set to TRUE.

This bug is fixed in oracle 10.0. The workaround is to change the parameter to temp_disable.

Direct Path Write wait

The 'direct path write' wait seems to occur when the disk the database is writing to can't keep up with requests. It is normally an indicator that there is something amiss with the disk, or possibly the DR sync is working as expected.

SIZING Tables

TABLES

To work out the sizings of x number of records then need to be added to a table, use the following ...

Analyze the table or partition. Find the avg_row_len, from either dba_tables or tab_tab_partitions. Then calculate the space required by ...

space_required = avg_row_len * number_of_rows

This formula is really simple and is not perfect as it doesn't take block headers, pct_free etc into consideration, but should be good as a guide.

ORACLE LINKS

metalink
tahiti

db link passwords

To work out what database links are using what passwords, run the following sql.

select name,ctime,host,userid,password from sys.KU$_DBLINK_VIEW;

This should work upto 10G.

STORAGE

Storage in an oracle database goes something like this ...

An oracle database is made up of one (two from 10g) or tablespaces. Each tablespace can have one or more datafiles. A datafile can be a file or pointer to a raw device or use ASM (automatic storage management).

When creating a tablespace you can specify locally managed or dictionary managed. Local managed should mean quicker dictionary response as all the info relating to extents held in the tablespaces etc are held in the tablespace and not the data dictionary.

When creating locally managed tablespaces, you have the option of setting the extents to uniform or automatic. I always think uniform is the best then you can control what segments go into what tablespace. This is probably quite old school, but there you go.

undo created at db startup

It appears undo at database startup is dependent on

- transactions_per_rollback_segment
- max_sessions

oracle (9.2.0 at least) uses the following formula to work out how many undo segments it uses when the undo tablespace is created.

max_rollback_segments = (max_sessions * 1.1) / transactions_per_rollback_segment

oracle will always have at least this number. We had the problem that max_sessions was changed and the undo tablespace was not recreated. Consequently the database was only ever creating what the old session level was set at.

We were also getting some weird errors around ORA-1555, snapshot too old errors when we kick a number of jobs off at once and they fail immediately with ORA-1555 errors.

We are in the process of making this change to production so time will tell !!!

UNDO SQL

Below is some sql I used to get an idea about how undo is being managed in an oracle database.

Firstly some simple stuff ...

Look at the segments in the UNDO tablespace.

select segment_name, sum(bytes)/(1024*1024)
from dba_extents
where tablespace_name = 'tablespace_name'
group by segment_name
order by segment_name;

LOOK at current rollback segments, by status, either ONLINE or OFFLINE.

select status, count(*) from dba_rollback_segs
where owner = 'PUBLIC' group by status;

MERGE the above two statements together ... This can take a minute or so to run, but is quite useful as it shows the total number of segments that can be used. Bearing in mind that the database will try and add some more.

select a.segment_name, b.status, sum(a.bytes)/(1024*1024)
from dba_extents a, dba_rollback_segs b
where a.segment_name = b.segment_name AND b.owner = 'PUBLIC'
group by a.segment_name, b.status;

UNDO, Some Discussion

OK, below are issues relating to the undo problems with oracle 9.2.0.3.

Seems as though there are a few bugs. The problem we seem to have is when we do either materialized view refreshes and truncate table type commands.

The problem seems to be that oracle can not create enough undo segments. When running these again, often straight after the failure it works. This generally seems to be the case.

The way undo seems to normally work is that it creates segments when required. There are particular rules around when it grabs new extents, how much space it grabs etc. The smon process is responsible for dropping undo segments that are not required any more. It generally does this every 12 hours. There is an event to stop this from happenning.

Welcome

Hi,

This is my first ever BLOG !!! WOW!!!

Quick introduction about this BLOG. Its all about me, my work life, my home life and other bits and tads.

enjoy !!!