Oracle Logbook

August 30, 2007

Data Pump Consistent Export

Filed under: Uncategorized — heliodias @ 5:52 pm

http://www.dizwell.com/prod/node/112

Advertisements

August 29, 2007

Oracle Unbreakable Linux

Filed under: Uncategorized — heliodias @ 2:01 pm

Why Oracle call him Linux as Unbreakable?
Yesterday i saw the podcast Oracle OpenWorld Keynote Video
with Oracle CEO Larry Ellison’s  announcement
in his Oracle OpenWorld 2006 keynote presentation.
And i finally understand why Oracle claim Unbreakable.
It´s a amazing support that Oracle offers, if you have a big problem on
your Linux Red Hat, than Oracle will fix it on your Currently Version,
So you don´t have to wait until an new version that upgrade your SO to fix your bug.

WITH at start of statement

Filed under: Uncategorized — heliodias @ 3:41 am

The With have two distinct propose, that could be combine.
First: make the query more clean (more readable) eg:

select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=10
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=15
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=20
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=25
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=30;

Now compare With
With base_table as
(select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14)
select * from base_table where tab1.col30=10
union all
select * from base_table where tab1.col30=15
union all
select * from base_table where tab1.col30=20
union all
select * from base_table where tab1.col30=25
union all
select * from base_table where tab1.col30=30

I know that in both cases i could use OR but it´s just to ilustrate

So Oracle will have two diferents behaviours, 
If selectivity is bad , than Oracle will simply expand the base_table as if was a view.
But if selectivity is good than Oracle execute just once,  and give you only the result set.
Regards
Helio Dias
http://heliodias.com

In response to :

http://forums.oracle.com/forums/thread.jspa?messageID=2035401&#2035401

August 28, 2007

What’s blocking my lock?

Filed under: Uncategorized — heliodias @ 2:16 pm

http://orafaq.com/node/854

Find query that generate more redo logs

Filed under: Uncategorized — heliodias @ 11:24 am

You may query

select s.sid, s.serial#,i.block_changes from v$session s, v$sess_io i where s.sid = i.sid order by 3

Regards

Hélio Dias
http://heliodias.com

http://forums.oracle.com/forums/thread.jspa?threadID=548141&tstart=30

11g hide hash password

Filed under: Uncategorized — heliodias @ 11:22 am

It seems like 11g hidden hash password,

Now you can´t query password column from dba_users

Library cache pin

Filed under: Uncategorized — heliodias @ 11:20 am

The library cache pin is simply a session marking a library object (e.g. cursor) for use – either shared (because it wants to use an existing cursor, say) or exclusive (because it needs to invalidate and recompile a cursor).

This should be a very rapid action but in your case it isn’t – your average wait is more than 2 seconds. It isn’t possible to say why this is happening, but if you are doing cross-instance invalidations (e.g. truncating tables regularly) AND you have a very busy system (lots of CPU in use) that can be enough to cause these symptoms.

On a long shot – the scattered reads (possible tablescans) may be causing some remastering (gc remaster or similar), and this can cause a freeze (gcs drm freeze or similar): this shouldn’t cause problems to the library cache – which operates under the ‘global enqueue’ processes rather than ‘global cache’ processes – but maybe there is some connection.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

 Answering to http://forums.oracle.com/forums/thread.jspa?threadID=547850&tstart=60

August 27, 2007

Origin of the word Query

Filed under: Uncategorized — heliodias @ 5:32 pm

The word derives from the Latin quaere (the imperative form of
quaerere, meaning to ask or seek).

August 25, 2007

11g Performance improves

Filed under: Uncategorized — heliodias @ 12:41 pm

http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#AREANO02327

11g Week by week

Filed under: Uncategorized — heliodias @ 12:31 pm

http://www.datasoftech.com/library.html

Next Page »

Create a free website or blog at WordPress.com.