2007/01/07

been busy

hi folks. sorry for the long absence. been hectic over what most call the "break". it isn't a break, for us: our clients go on hols and that means we get busy filling in.

some examples of the stuff I've been doing:

SQL> @prob01
Press return to continue...
COUNT(*)
----------
7
1 row selected.
Elapsed: 00:00:52.13
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4147120 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


horrible, ain't it? well, after a slight rework of the SQL, I got this:


SQL> @solution
Press return to continue...
COUNT(*)
----------
7
1 row selected.
Elapsed: 00:00:01.40
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
77 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


much better!
if you want to see the change in the sql, drop me a line and I'll send you the plans and details. no re-indexing, just recode to let the optimizer - 9ir2 - do a better job with the existing indexes. it was picking up a non-unique low-cardinality index in a sub-query. the change was to turn the sub-query into a join that uses the PK. 10g is supposed to do that automagically. 9i doesn't...


another problem.

those of you stuck with running that software will recognize this immediately and what the issue is:

SEGMENT_TYPE
------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ---------- -------------- -----------
TABLE
PS_EN_BOM_EXPL
FSAPP 11 78643200 10485760
INDEX
PS_EN_BOM_EXPL
PSINDEX 20914 52428800 262144

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ----------
ALLOCATIO PLU
--------- ---
PSINDEX 262144 262144 1
2147483645 0 262144 ONLINE PERMANENT LOGGING LOCAL
UNIFORM NO


if there ever was an argument for Oracle to implement sparse object allocation in 11g, it is this application software. unfortunately it will be too late for those who have been stuck with this crap for years and are now looking at alternatives. another perfect example of Oracle not listening to folks who have to put up with this stuff year after year...

Picture this:
15000 indexes, all in the same tablespace, about 500 of them indexing tables with significant data, the rest on empty tables!

the maker of the application will disown you if you "change our tablespaces, we know better than you how to run our software, no?".

familiar? yeah. thank God Oracle bought them, we might see some reason prevail...

so how do you cope without sparse allocation?

if you create a LMT with uniform allocation of anything more than a few hundred KB, you basically are wasting disk space for the vast majority of empty objects. and you will see this number of extents on anything with any significant data! not terribly bad but definitely not a recommended approach.

well, you do what everyone else does: you ignore the idiots from application support and go ahead and allocate objects the way they should be allocated, in the correct LMT tablespaces set to the correct uniform size.

that's what you do. and stuff their "knowledge of their application"!

another option is to ignore the whole problem, grin, and blame "bloody Oracle".

like so many still do...




anyways




here is SQLPuss, our young Persian-Himalayan cat:



I know: sad! Hey, I don't give a fig!!!

he's a true character. I reckon he's convinced he's a dog. with two dogs around the place all the time, he's got no options, anyway...


meet Rover, he's the German Shepherd of a friend of ours.



I've never seen a larger GS anywhere! He's as big as he looks, 80 Kilos in weight - yes Virginia, that's nearly 180 pounds! - and not a bit of fat anywhere. a total sook as well: wouldn't hurt a fly!

more photos soon in the deviantart site. I've been remiss of posting there as well.
all will be fixed soon.

catchyalata, folks

1 Comments:

Blogger Alex Gorbachev said...

Wow. GS is impressive. We had a back GS back home and she was considerably smaller but not a meek creature at all!

Monday, January 08, 2007 2:36:00 pm  

Post a Comment

Links to this post:

Create a Link

<< Home