Tablespace, Diskspace or sql abuse..

#1

Hi you'll,

I've encounered a problem that recieved the following error:

ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)
ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)
ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)

My dba claimes that the problem is caused by a not logical query, I
think it is a tablespace problem, maybe you can be the judge.

My query joins a table (of 1358453 rows) to itself :
The comparison fields are a date field and an int field when the date
field is joined with the ">" operator and the int field is joined by
equalization,
The query is again joined to my previous table when the join method is
that the last table date field is between the first table date field and
the second table date field.
Is this query unacceptable

--
Posted via http://dbforums.com

#2

"nikey11" <member31783 (AT) dbforums (DOT) com> wrote

Quote:

Hi you'll,

I'm not it, but...

Quote:

ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)

My dba claimes that the problem is caused by a not logical query, I
think it is a tablespace problem, maybe you can be the judge.

You're both right. Can you confirm that file 201 is
one of the datafiles in temporary tablespace?

Quote:

Is this query unacceptable

God only knows. I'd need to see what tables and indexes
are being used, what data model, what are you trying to
achieve, etc.

Now, is it *efficient*? An EXPLAIN PLAN would help.
Versions, OS, etc.

Quote:

Posted via http://dbforums.com

How come all of a sudden we get so many posts
here from dbforums? Something wrong with its
"private" lists? Did this place just get
"discovered"?

Andrewst, what's going on? There are more
posts here from your forum than inside it...

--
Cheers
Nuno Souto
wizofoz2k (AT) yahoo (DOT) com.au.nospam

#3

"Norman Dunbar" <Norman.Dunbar (AT) lfs (DOT) co.uk> wrote

Quote:

A quick check with oerr shows this :

oerr ora 27069
27069, 00000, "skgfdisp: attempt to do I/O beyond the range of the file"
// *Cause: internal error, the range of blocks being read or written is
// outside the range of the file, additional information
indicates
// the starting block number, number of blocks in I/O, and the
// last valid block in the file
// *Action: check for trace file and contact Oracle Support

I suspect that internally, Oracle 'thinks' the file is 'x; extents long,
but the OS 'thinks' it is less than 'x'. End result, carnage !

You say that you are running a query, but the error is obtained writing
to the file in question. I suspect that this could be a file in your
temporary tablespace. Whatever it is, you should check for any trace
files in BACKGROUND_DUMP_DEST and log an iTAR with support. Your DBA
should be able to manage this - he hasn't been very helpful so far

)

If this is 8i or above, then the use of tempfile temporary tablespace can
give rise to this error. When you create a tempfile datafile, Oracle only
'touches' the file, and doesn't actually map out and format the entire
thing. If another file on the same disk grows into the space that the
tempfile one day needs to properly format, then you get this sort of mess.

Likely, you will have to drop and re-create the temporary tablespace to fix
the problem.

Then you can tell your DBA that this sort of thing should never happen,
because temporary tablespaces generate so much I/O, it's poor practice to
house them along with other, 'genuine' tablespaces. And if they are housed
on their own, as they should be, then the non-formatting of tempfiles at the
time of their creation becomes a non-issue.

If you like gloating, you can re-phrase that last paragraph slightly more
clearly: he doesn't know his job.

;-o

#4

Another possibility is that your tempfile is allocated, and your block
size is at least 8K, on a system that is not configured for large file
support, i.e. files over 2GB in size. Make sure that your system can
support large files if the other suggestions fail.

HTH,
Brian

nikey11 wrote:

Quote:

Hi you'll,

I've encounered a problem that recieved the following error:

ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)
ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)
ORA-01114: IO error writing block to file 201 (block # 359561)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 359567)

My dba claimes that the problem is caused by a not logical query, I
think it is a tablespace problem, maybe you can be the judge.

My query joins a table (of 1358453 rows) to itself :
The comparison fields are a date field and an int field when the date
field is joined with the ">" operator and the int field is joined by
equalization,
The query is again joined to my previous table when the join method is
that the last table date field is between the first table date field and
the second table date field.
Is this query unacceptable

--
Posted via http://dbforums.com

--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

#5

First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
swift replies have been most helpfull in confronting my evil dba...
I've proven to him that he was wrong by his assumption that my query is
not logical by running it on a smaller table.

My question for you now is this:
Have I been unproffesional by creating a query that combines a table of
1358453 rows 3 times to itself?
should I considered a different approach?

All the best to you,
noam.

--
Posted via http://dbforums.com

#6

Hi Arcangelo,

Good call !!!

Cheers,
Norman.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar (AT) LFS (DOT) co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: Arcangelo [mailto:arcangelo-c (AT) yahoo (DOT) com]
Posted At: Tuesday, June 24, 2003 3:11 PM
Posted To: server
Conversation: Tablespace, Diskspace or sql abuse..
Subject: Re: Tablespace, Diskspace or sql abuse..

If this is 8i or above, then the use of tempfile temporary tablespace
can
give rise to this error. When you create a tempfile datafile, Oracle
only
'touches' the file, and doesn't actually map out and format the entire
thing. If another file on the same disk grows into the space that the
tempfile one day needs to properly format, then you get this sort of
mess.

<SNIP>

#7

As with may things Oracle, it depends !!

It depends on what the query is actually trying to do,
also on whether the database was properly designed and normalised (or
denormalised as appropriate !),
and probably lots of other things that I can't quite remember at the
moment !

On the other hand, if said query is performing a cartesian join to
itself (three times) then this is probably not a good thing.

HTH

Cheers,
Norm.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar (AT) LFS (DOT) co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: nikey11 [mailto:nikey11]On Behalf Of nikey11
Posted At: Tuesday, June 24, 2003 4:00 PM
Posted To: server
Conversation: Tablespace, Diskspace or sql abuse..
Subject: Re: Tablespace, Diskspace or sql abuse..

First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
swift replies have been most helpfull in confronting my evil dba...
I've proven to him that he was wrong by his assumption that my query is
not logical by running it on a smaller table.

My question for you now is this:
Have I been unproffesional by creating a query that combines a table of
1358453 rows 3 times to itself?
should I considered a different approach?

All the best to you,
noam.

--
Posted via http://dbforums.com

#8

Hi!

Quote:

If this is 8i or above, then the use of tempfile temporary tablespace can
give rise to this error. When you create a tempfile datafile, Oracle only
'touches' the file, and doesn't actually map out and format the entire
thing. If another file on the same disk grows into the space that the
tempfile one day needs to properly format, then you get this sort of mess.

Likely, you will have to drop and re-create the temporary tablespace to
fix
the problem.

If this is a sparse file issue as you describe, then the file system should
be (almost) full.
Otherwise OS should be able to handle expanding the file correctly.

ls -ls command in most unixes and linux should show you the *real* disk
usage of a file,
it's helpful when dealing with sparse files such as temp files. Of course I
would
either create a "permanent" file for temp tablespace, then reuse the file
when creating
correct temp ts, to make the file to allocate space "correctly".
Or as another solution, you just take the newly created sparse tempfile
offline,
copy it to another name and rename it back to original name. OS copy copies
sparse
blocks to real blocks.

2 cents,
Tanel.

#9

"Ryan Gaffuri" <rgaffuri (AT) cox (DOT) net> wrote

Quote:

by 'tempfile' you mean a locally managed temporary tablespace correct?

Not exactly, he means tablespaces created with the command

create temporary tablespace blah
TEMPfile 'temp01.dbf' size 1049m;

Its the specification of the datafile rather than the extent management
clause that is important.

Quote:

this doesnt happen with dictionary managed temporary tablespaces. This
is pretty interesting. Never saw that before.

IIRC it does or could happen with DMT's. Unfortunately I don't have a DMT
system to play with at home to prove it. This is probably a bit daft. Try
the following (on an 8i instance)

create temporary tablespace blah
tempfile 'blah.dbf' size 10m;

followed by

select extent_management from dba_tablespaces where tablespace_name='BLAH';

should prove you can have a dictionary managed sparse file.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

#10

"nikey11" <member31783 (AT) dbforums (DOT) com> wrote

Quote:

First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
swift replies have been most helpfull in confronting my evil dba...
I've proven to him that he was wrong by his assumption that my query is
not logical by running it on a smaller table.

My question for you now is this:
Have I been unproffesional by creating a query that combines a table of
1358453 rows 3 times to itself?

Not necessarily. If you have just written

select a.*,b.*,c.*
from large_tab a,large_tab b,large_tab c;

Then this wouldn't be the best piece of code ever seen. On the other hand we
might routinely include the same large table 3 times in the same query.
Access would nearly always be via indexed columns and return small
resultsets (in theory - in practice Accountants write the sql sometimes).
Self Joins aren't in and of themselves a bad thing, but they need to be part
of a sensible design and anticipated access paths.

Quote:

should I considered a different approach?

You should consider EXPLAINing all SQL that takes longer than a cutoff
period (say 10 seconds) to return results and see if the SQL is sufficiently
tuned. There are 3 things if you take this approach seriously that you need
to know.

1. It can be hard

2. There are tools and your DBA to help you.
3. If you consider the efficiency of your SQL and not just wether it returns
the right results your DBA will love you. Or at least not hit you often.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************