Some useful links on Oracle SQL optimization (more specifically on the way oracle joins tables)
1. Sachin Arora's explanation on Hash Joins/Nested Loops
2. Optimization of joins
3. Hash joins & Nested Loops on dbaForums
4. @OTN Forums on Nested Loops
5. Hash join Tips
6. Sizing PGA
what are we ? we are what we experience. And some more. I bring together here, collective experiences from different parts of life..
Wednesday, April 1, 2009
Tuesday, March 31, 2009
And he spoke.....
Today could easily be termed as one of the best days of my life...
The baby spoke for the first time.. and that too.. said "papa"... though I was not there to hear it first hand, it has been digitized (recorded in video) and I would hope to see/hear it when I get the first chance...
The very idea that he is already starting to speak up.. I can at least call it uttering... feels pretty great...
Imagine, a few months, this guy was inside a water bubble, a tiny thing barely a few centimeters.. and now he is already waving, smiling, crying, recognizing faces, doing all those pretty and naughty things that change a parent's life forever...
Fabulous feeling.. not enough words with me to describe it... :D
The baby spoke for the first time.. and that too.. said "papa"... though I was not there to hear it first hand, it has been digitized (recorded in video) and I would hope to see/hear it when I get the first chance...
The very idea that he is already starting to speak up.. I can at least call it uttering... feels pretty great...
Imagine, a few months, this guy was inside a water bubble, a tiny thing barely a few centimeters.. and now he is already waving, smiling, crying, recognizing faces, doing all those pretty and naughty things that change a parent's life forever...
Fabulous feeling.. not enough words with me to describe it... :D
Thursday, March 12, 2009
Useful scripts for DBA perspective -
http://www.dbapool.com/scripts.php
More stuff on finding free space available in a database -
http://forums.oracle.com/forums/thread.jspa?threadID=624042
A sql that we found useful is here - (from the above link itself) -
SELECT
NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) NAME ,
mbytes_alloc mbytes ,
mbytes_alloc - NVL (mbytes_free, 0) used ,
NVL (mbytes_free, 0) free ,
((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 pct_used,
100 - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) pct_free
FROM
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_free,
tablespace_name
FROM
SYS.dba_free_space
GROUP BY
tablespace_name
)
a,
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_alloc,
tablespace_name
FROM
SYS.dba_data_files
GROUP BY
tablespace_name
)
b
WHERE
a.tablespace_name(+) = b.tablespace_name
UNION ALL
SELECT
f.tablespace_name ,
SUM (ROUND((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) ) "total MB" ,
SUM (ROUND(NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB" ,
SUM (ROUND ( ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0) ) / 1024 / 1024, 2 ) ) "Free MB" ,
(SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
100 - (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)))
FROM
SYS.v_$temp_space_header f,
dba_temp_files d ,
SYS.v_$temp_extent_pool p
WHERE
f.tablespace_name(+) = d.tablespace_name AND
f.file_id(+) = d.file_id AND
p.file_id(+) = d.file_id
GROUP BY
f.tablespace_name
ORDER BY
5 DESC --&orderby
;
http://www.dbapool.com/scripts.php
More stuff on finding free space available in a database -
http://forums.oracle.com/forums/thread.jspa?threadID=624042
A sql that we found useful is here - (from the above link itself) -
SELECT
NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) NAME ,
mbytes_alloc mbytes ,
mbytes_alloc - NVL (mbytes_free, 0) used ,
NVL (mbytes_free, 0) free ,
((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 pct_used,
100 - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) pct_free
FROM
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_free,
tablespace_name
FROM
SYS.dba_free_space
GROUP BY
tablespace_name
)
a,
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_alloc,
tablespace_name
FROM
SYS.dba_data_files
GROUP BY
tablespace_name
)
b
WHERE
a.tablespace_name(+) = b.tablespace_name
UNION ALL
SELECT
f.tablespace_name ,
SUM (ROUND((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) ) "total MB" ,
SUM (ROUND(NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB" ,
SUM (ROUND ( ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0) ) / 1024 / 1024, 2 ) ) "Free MB" ,
(SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
100 - (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)))
FROM
SYS.v_$temp_space_header f,
dba_temp_files d ,
SYS.v_$temp_extent_pool p
WHERE
f.tablespace_name(+) = d.tablespace_name AND
f.file_id(+) = d.file_id AND
p.file_id(+) = d.file_id
GROUP BY
f.tablespace_name
ORDER BY
5 DESC --&orderby
;
Monday, March 9, 2009
Another month and another vaccine...
Its been another fascinating month... As the book, "What to expect in the first year" says, third month onwards, its the golden time of the babies infancy, I would have to concur, it cant be more correct...
The baby is squealing, smiling, laughing, attempting to curl on his own, would make faces and return ur expressions. And crying too.. out loud.. at times for quite some long time without any apparent reason.
Its all so fascinating. I see him only for three nights/two days a week but then the time is almost good enough to give me memories worth much longer in time and happiness. Its very easy for me to remember his face and expressions now. Its a lovely feeling...
He had another round of his vaccinations this weekend, and I have to say this has been the easiest ones. The pain he has been through in the earlier vaccinations, the trouble he has taken, and the rest of the family shared with him... One time he was turning all colors & crying almost all the night and we had to figure out lots of ways to find soothers for him.
But this around, he had some fever, but it was nearly painless, or may be just that we have had experience of this situation.
The baby is squealing, smiling, laughing, attempting to curl on his own, would make faces and return ur expressions. And crying too.. out loud.. at times for quite some long time without any apparent reason.
Its all so fascinating. I see him only for three nights/two days a week but then the time is almost good enough to give me memories worth much longer in time and happiness. Its very easy for me to remember his face and expressions now. Its a lovely feeling...
He had another round of his vaccinations this weekend, and I have to say this has been the easiest ones. The pain he has been through in the earlier vaccinations, the trouble he has taken, and the rest of the family shared with him... One time he was turning all colors & crying almost all the night and we had to figure out lots of ways to find soothers for him.
But this around, he had some fever, but it was nearly painless, or may be just that we have had experience of this situation.
Wednesday, February 25, 2009
Compile all invalid packages in a schema : oracle
Came across this from sql developer, Looks like a very useful script, especially when its made available readymade
begin
FOR cur IN
(SELECT OBJECT_NAME, OBJECT_TYPE, owner
FROM all_objects
WHERE object_type in ('PACKAGE','PACKAGE BODY')
and owner = :OBJECT_OWNER AND status = 'INVALID' )
LOOP
BEGIN
if cur.OBJECT_TYPE = 'PACKAGE BODY'
then
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile body';
else
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile';
end if;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
end loop;
end;
And then another one to check as to what all objects are invalid -
select object_type, count(*)
from user_objects
where status = 'INVALID'
GROUP BY object_type
begin
FOR cur IN
(SELECT OBJECT_NAME, OBJECT_TYPE, owner
FROM all_objects
WHERE object_type in ('PACKAGE','PACKAGE BODY')
and owner = :OBJECT_OWNER AND status = 'INVALID' )
LOOP
BEGIN
if cur.OBJECT_TYPE = 'PACKAGE BODY'
then
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile body';
else
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile';
end if;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
end loop;
end;
And then another one to check as to what all objects are invalid -
select object_type, count(*)
from user_objects
where status = 'INVALID'
GROUP BY object_type
Subscribe to:
Posts (Atom)

