Availablity

Not really oracle related, other than it runs on an oracle database and I don't like to pick on a company in public, but.... We have a product that uses ESRI's SDE software for mapping. We get most of our support through a vendor who has a very good working knowledge of ESRI. We have been getting some pressure to make the software available 24x7 due to changes in the staffing model. For maintance, SDE requires no users accessing the software, that doesn't make 24x7 very easy to do. We spoke with the vendor with no success. So I thought I would contact ESRI direct. I go and look up the page, find the ESRI developer site. I really and truly started to laugh out loud as I read the home page at http://edn.esri.com/index.cfm?fa=home.welcome . To make it easy to find, here is a copy/paste: Announcements Developer Summit—More Developer Summit session information has been posted. The information can be found under the Media Center tab. We apologize for the delay in posting these items. Limited Availability on Sunday—Each Sunday EDN's availability between approximately 8:00 a.m. and 1:30 p.m. (Los Angeles Time) may be interrupted intermittently due to routine maintenance. We apologize for any inconvenience this may cause you. I have a feeling trying to convince them that downtime for maintenance is not acceptable is going to be a very, very hard sell.

Another for the WTF

Question from a developer: "Why does this query take so long to process?"

SELECT 1 FROM HEADER WHERE 1 IN (SELECT 1 FROM DETAIL WHERE 1=1 AND DETAIL.HEADERID = '3432223' AND DETAIL.SITEID='OVA3' AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR')) ;

Can anybody spot the problem? They are looking for a simple return, they do not look at the value of the return as long as one row is returned, the java returns TRUE and they continue on. They run the above SQL for every record in HEADER that they are looking to update to look to see if there are corresponding detail lines. The HEADER table has 200K + records. I suggested this one to them.

SELECT 1 FROM HEADER WHERE 1=1 AND HEADERID IN (SELECT HEADERID FROM DETAIL WHERE 1=1 AND DETAIL.HEADERID = '200504505' AND DETAIL.SITEID='OVA3' AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR') ) ;

There are much better ways to do this, I will let the developer work on those, he is very embaressed and is spending the needed time on the issue and has since this one found other queries that were along the similiar lines.

Not to start a war here or anything

BUT, tomorrow is the beginning of the end for Windows Oracle Database servers in our company. The very LAST oracle database that runs on windows is in the pipe to be converted to HPUX, due date - June 4th, but this might get pushed back a couple of weeks due to factors I can't control. Testing is ongoing, but it looks good, users are impressed by the performance and the vendor is happy. 132 Databases in the company, 131 on a flavour of unix (HPUX,Linux many types, AIX and Solaris) and ONE windows. Free of windows at last... Now, if I could only convince management to allow me to run linux on my laptop. Now... to start the conversion process off of HPUX RISC onto something else... I am thinking AIX. :)

Multiple Failures

Yesterday (Saturday) was a very bad day... today isn't much better, and tomorrow is looking pretty gloomy too. Yesterday, had a major power failure at our main data center. That was the first failure, UPS all kicked in and started working, but notification system to tell everybody that the UPS were working failed, nobody knew we were on UPS. Second failure. Generator kicks in as it is supposed to, but fuel line from the main tank disconnects from the generator, generator runs out of petrol and stops. Third Failure. UPS systems ran out after a few hours and everything went down as hard as hard can be. As it was the weekend, user load was low, but when the systems went "blank" and didnt' come back, the users noticed. They go to our online emergency contact page to get numbers - gone, servers have no power. They go to the fancy "in-phone" directory listing for our new VOIP phones, gone, server has no power. They can't send emails, email system is in the data center. New system, the redundant backup system is still sitting beside the production system for final testing. Somebody looks up the managers home phone via 411 and gives him a call, and the word goes out. Holiday long weekend, NOBODY around. Manager is calling relatives looking for people. Generator gets repaired and fired up. We are at about 4 hours of downtime now. Everything comes back and the UPS start to charge. We do nothing until the UPS charge fully... 2 hours more hours, start to bring everything back up. Our new SAN won't come back. Our new NAS won't come back. Both have been rigoursly tested and guarenteed by the manufacturer. Won't come back. A manager is driving around the city looking for the senior system administrator's car hoping he is out for dinner or shopping instead of gone for the weekend. His cell phone died last week and it hasn't been fixed yet. Believe it or not, he finds him and his family. Only takes an hour more and everything including the SAN and NAS is back and functioning. Time for the DBA's to take over. 23 databases come back no problem, automatic crash recovery, no issues. Three don't. We get one back up and running with a rman restore via control files (MAN I love RMAN). The second one is actually our primary rman catalog database, it is gone, history. It of course doesn't back up to a catalog server, entire blade is corrupted. No hope of fixing. Rebuild it and restore from an export on tape, back and running in 3 hours. The third one is easily restored via rman. Find out during the restore of the export, that the brand new very expensive tape array is not functioning. That is being rebuilt as I type. New lesson learned. When you are configuring and testing your redundant systems. Don't build them at your local site and move them when done, instead ship them direct to where they are going and build them there. Turns out the mail notifications on the UPS being kicked on didn't get sent because the UPS on the network gear the data center's SMTP server is on failed and the server went down hard. UPS has a "certified working" sticker from 22 days ago. No data loss to any of the databases thank goodness for that. The meetings next week on this are going to be very bad indeed.

Use what you learn

Way back, way way back I remembered reading about the "WITH query_name (select ...) subq select * from subq", which oracle calls Subquery Factoring I remember thinking at the time "that's neat" and then of course promptly forgot about it. Well, today I spent about 3 hours trying to optmize an audit report on changes to employees in our payroll system. A huge nasty bit of SQL that made numerous FTS into the audit table and the employee table to gather the audit information the way the users demanded it. I tried materialized views, rewriting the queries, even considered pipelined functions, then for some weird reason the old noodle remembered the subqueries - 1 minute to find it in the SQL reference guide, another 5 to rebuilt the SQL. Cost on the query cut from over 30,000 to 543. The time of the query, cut from over 8 minutes to under 5 seconds. This is a direct copy/paste straight out of the 10G SQL reference manual:

 Subquery Factoring: 
Example The following statement creates the query names dept_costs 
and avg_cost for the initial query block containing a join, and then 
uses the query names in the body of the main query. 

WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total 
 FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name)
 , avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) 

SELECT * FROM dept_costs 
 WHERE dept_total > (SELECT avg FROM avg_cost) 
 ORDER BY department_name; 

DEPARTMENT_NAME                DEPT_TOTAL 
------------------------------ ---------- 
Sales                          313800 
Shipping                       156400 

Nothing new or earth shattering here... just a comment on how the brain works.

Children

Nothing work or oracle related here... I have been lucky enough to have two wonderful children, a great daughter who I am proud of , a very artistic and quiet girl with many friends. Then there is my son, Honour roll 3 years in a row, principles roll, achievement awards and generally a wonderful boy who I am proud of as well. I tend to talk about my kids far too much, I am sure my coworkers are thoroughly tired of hearing about me going on about my children. But all things must come to an end. Nothing horrible here, or tragic. Just my son even though he is 12, has decided that being a teenager is the thing for him. With the "teen angst" to go with it. I was hoping it would be put off for at least a few more years. But, I have a "wannabe" teen in the house... I hope it is worse than having a real teen, I can't see how it can really be any worse. My mother is probably STILL laughing, it is amazing how cruel parents can be. ;)

Google does it again

at http://pages.google.com Now that is easy to use web page development. Simply, easy. And I found a great use for it, storing files that can be referenced from this blog. I modified the testing I did with Jonathon Lewis's script testing of FTS or not with an impossible predicate and instead of having a huge blog post, now it is a short blog post with a link to the output - much much cleaner. Good for posting scripts too. You can expect more scripts to be posted shortly. http://herodt.googlepages.com/home is the page I banged together.

Contractors

We have a contractor working remotely helping us with performance problems with one of our off the shelf applications, nice enough fellow, very knowledgeable about the application as well as the business logic on why the application does what it does. This was to a particular module in the application, not application wide. The database isn't the performance issue, it is of course the first thing blamed. But luckily management has been proven the fact that the database isn't the issue, instead it was bad design from the vendor in the application and database - hence the good, but brutily expense contractor. I had a bit of a run in with him today, he must have had a bad day and I somehow earned the right to have him rip a strip off of me. A few weeks ago I had started monitoring on all of the indexes in the production database (built in 9i monitoring) and had earlier in the week passed him a spreadsheet of the data. This was being done because the module in production was just not performing well. Well, he was very upset at the results as it appeared about $8K worth of his consulting time of creating and modifying indexes had shown very little result in the production database - even though the results had been stellar in the test database. He started in on how some configuration differences had to exist between the two databases and I had better find the differences and fix them immediately. Not his exact words, but I wouldn't communicate language like that to others sensitive ears. After a few minutes of this, I calmly hung up on him in mid sentence and went about my business trying hard to not resist the urge to pop into the gas guzzler and pay him a visit (only about 2 hours away) and have a nice face to face conversation with him. A period of time passed, and one of our application support people came by asking what I had done to upset the contractor who was now currently on the phone with a 3 managers painting a unlikable picture of me. I explained the contractors issues to the support person, who looked at me with a twinkle in his eye and asked me to follow him. At his cube dwelling he popped open a change request, from about 2 months ago from the contractor asking the in the production database, an application wide parameter be modified to not append a site code defination to all of the SQL being passed to the database. There was a fair bit of communication on the subject on the change, but in the end it was approved and implemented. I am sure you can guess where this is going. He had in his original effort used the site code to reduce the selectivity on the driving tables in his index creating. We bypassed the change protocols and put the parameter back into place the way it had been. The support person started to use the module to generate reports and generally move about the application to start to execute queries. I trundled on back to my cube and monitored what the kindly support person was doing. Low and behold - the indexes where being used and the SQL was executing effeciently and quickly. The application person took a print out of the change and went to visit with the managers on my behalf. I felt that would be better to do instead of it looking like I was trying to cover my own behind. Much better when others have the capability of doing it for you. A short time passed - under 10 minutes and I receive a call from the contractor. Very apologetic for his own stupidity and promises of un-expensed lunchs for me when he is at the office next week. sigh.

Mr. Jonathan Lewis's article "When 2+2=5"

"From web page of Jonathan Lewis comes his debunking of the myth (Urban legend?) that using the predicate of 1=2 causes a full table scan. On the page When 2 +2=5 Mr. Lewis goes through in detail his tests to prove this myth wrong. This is simply a continuation of his tests on versions 7 through 10xe of oracle to validate or invalidate his conclusions as Mr. Lewis does state this is the same on many versions of oracle but doesn’t provide proof. I did this because a colleague of mine disagreed with Mr. Lewis’s findings and just “knew” that Mr. Lewis was wrong and I had some time at home to kill. I will not draw any conclusions, or make any statements. I have simply provided the complete result sets and let you come to your own conclusions. All of these tests were done in production databases that were busy doing other work at the time, I thought this an appropriate environment instead of a sterile environment. I have provided the output from V$VERSION in every log. The entire output is here I moved the post to a file on my googlepages site as the post was massive and difficult to read. Draw your own conclusions based on the information supplied above.

Oracle Enterprise Manager 10g V2

Finally Oracle has a decent tool for administering the oracle enterprise - and so much more. I have been a staunch well documented and card carrying member of the anti-oem group for many years now. We just got OEM installed onto a linux server and agents on our big HPUX iron and our windoze box. I was concerned when I saw that the agent download for HPUX was 600+ meg. But it installed quick and easy. The install blew up our one and only windows server, but to me, that is a good thing - one more nail in the coffin for us getting rid of the server. It didn't blow up the test windows box, so we were a little surprised. The information is correct, the overhead is minimal, and it just plain works. We are in the process of reducing the number of checking scripts that run and populate a home grown monitoring database and are beginning to rely on OEM. One of the items that pushed me over the edge to allow it to be installed was the appartenly vapourware OEM2GO product. I should have guessed it wasn't as documented since the documentation reads like a marketing brochure. If anybody has managed to use OEM2GO on their PDA's please speak up any useful tips or hidden documentation would be wonderful. I can connect to OEM with my PDA, but it is the full blown version of OEM and the poor PDA can't handle it.

Almost an entry for Oracle WTF

Ok, I know "why" but WHY!! Names have been changed to protect the guilty. SELECT * FROM T1 WHERE (STATUS IN ('STATUS1', 'STATUS2', 'STATUS3') AND T1TYPE NOT IN ('PRICE','BLANK') AND 'SOMETEXTVALUE' IN ((SELECT COMPANY FROM COMPANIES WHERE COMPANY = 'SOMETEXTVALUE' ) UNION (SELECT COMPANY FROM COMPANIES WHERE PARENTCOMPANY = 'SOMETEXTVALUE' )) AND ORGID= 'SOMEORGID') AND ( ((T1TYPE NOT IN ('PRICE', 'BLANK') OR PRIVATE= 'Y' ) AND SITEID= 'SOMESITE' ) OR (T1TYPE IN ('PRICE', 'BLANK') AND PRIVATE= 'N' ) ) / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost -------------------------------------------------------------------------------- 0 SELECT STATEMENT 49362 7230K 334 1 MERGE JOIN CARTESIAN 49362 7230K 334 2 VIEW VW_NSO_1 2 100 9 3 SORT UNIQUE 2 90 9 4 UNION-ALL * 5 INDEX SKIP SCAN COMPANY_NDX 1 20 2 * 6 TABLE ACCESS BY INDEX ROWID COMPANIES 1 70 3 * 7 INDEX SKIP SCAN COMPANY_NDX 1 2 8 BUFFER SORT 24681 2410K 334 * 9 TABLE ACCESS FULL T1 24681 2410K 163 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 6 - filter("COMPANIES"."PARENTCOMPANY"='SOMECOMPANY') 7 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 9 - filter(("T1"."STATUS"='STATUS1' OR "T1"."STATUS"='STATUS2' OR "T1"."STATUS"='STATUS3') AND "T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' AND "T1"."ORGID"='SOMEORIGID' AND (("T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' OR "T1"."PRIVATE"='Y') AND "T1"."SITEID"='SOMESITE' OR ("T1"."T1TYPE"='BLANK' OR "T1"."T1TYPE"='PRICE') AND "T1"."PRIVATE"='N')) Get a call from a really nice application support person - "Did YOU do anything, our "T1" reports are taking a long time." After a bit of digging - "We applied a patch on the weekend". Took about 30 seconds to track down SQL like the one above. Was ran for every entry (about 1,000 rows) in another table to "validate" the presence of data in T1. My answer "Back the patch out, call the vendor, remind them what a bind variable is, send this nice explanation of a cartesion join to them too." I love my job! (really I do)

Scripts

This is a repost of a post I made earlier in March, I just simply could not convince blogspot to properly display the scripts with the fonts etc that I wanted. The first script (current_users.sql) gives you the SID,SERIAL# for every session. So, now got a session that has been active for too long, or using a lot of CPU? Then trace the session remotely.

SQL> exec sys.dbms_system.set_ev(:SID,:SERIAL, 10046,12,'');


I have another script I use that you simply pass a SPID to and it figures everything out for you. I will post it later. You will then have a trace file sitting on the server in the UDUMP location. You can then use tkprof to create an output file you can read. Using the parameter sort=prsela,fchela,exeela sorts the statements from longest running to shortest running, so hopefully the offender is at or near the top. To get the file name (dedicated server) you can run the following:

SELECT PAR.VALUE||'/'|| 'ora_'||P.SPID||'_'||lower(I.INSTANCE_NAME)||'.trc'
FROM V$SESSION S, V$PROCESS P, V$INSTANCE I,
V$PARAMETER PAR
WHERE
1=1
AND S.PADDR = P.ADDR
AND P.SPID IN (&THESPID)
AND PAR.NAME = 'user_dump_dest'
;


You will have to edit the output in take into consideration the case sensitivity of your parameters. Don't want to trace.. or the session is "locked", then I also have a another script in this for you.
current_sql.sql
Simply call this script and pass the SPID to it, or more than one SPID with them comma separated (hence the IN). I choose SPID because in a UNIX environment they are easy to find and relate the session to the OS. I haven't attempted this script in the Windows world.

DEFINE THESPID = &1
SET VERIFY OFF
SET LINES 132
SET PAGES 40
COLUMN SIDSER FORMAT A12
COLUMN SQL_TEXT FORMAT A40
COLUMN STATUS FORMAT A10
TRUNCATE COLUMN SPID FORMAT A10
COLUMN USERNAME FORMAT A10 TRUNCATE
SELECT P.SPID ,S.SID','S.SERIAL# SIDSER
,T.SQL_TEXT ,S.STATUS ,S.USERNAME
FROM V$SESSION S, V$SQLAREA T
, V$PROCESS P, V$SESSION_LONGOPS L
WHERE
1=1
AND S.PADDR = P.ADDR (+)
AND S.SQL_HASH_VALUE (+) = T.HASH_VALUE
AND P.SPID IN (&THESPID)
AND L.SQL_HASH_VALUE (+) = S.SQL_HASH_VALUE
GROUP BY
P.SPID,S.SID,S.SERIAL#,T.SQL_TEXT
,S.STATUS,S.USERNAME
/


COLUMN OPNAME FORMAT A10 TRUNC
COLUMN TARGET FORMAT A15 TRUNC
COLUMN MESSAGE FORMAT A40 WRAP
COLUMN SEC_REM FORMAT A10
COLUMN MINS_REM FORMAT A10
COLUMN WAIT NEWLINE FORMAT A60
SELECT DISTINCT L.OPNAME,L.TARGET,L.MESSAGE
,TO_CHAR(TIME_REMAINING) SEC_REM
,TO_CHAR(ROUND((TIME_REMAINING/60),2)) MINS_REM
FROM V$SESSION S, V$SQLAREA T, V$PROCESS P
, V$SESSION_LONGOPS L
WHERE
1=1
AND S.PADDR = P.ADDR (+)
AND S.SQL_HASH_VALUE (+) = T.HASH_VALUE
AND P.SPID IN (&THESPID)
AND L.SQL_HASH_VALUE (+) = S.SQL_HASH_VALUE
;

You get the SQL current executing, as well you get anything in v$session_longops. Very helpful in tracking down problems.

Newsgroups

Here are some interesting posts on
comp.databases.oracle.server

Follow up (new post) .

This person is complaining to the news group that the information provided by the helpful folks in group was not appropriate and actually caused the company the person is employed with to pay a fine. I would assume because the person wasn't able to get the instant answer they were looking for so it is the communities fault.

This person really flabbergasts me. I would love to have a long conversation with this person going over the basics of problem solving and the importance of testing and benchmarking thoroughly. I have a feeling it would be a waste of time and effort on my part, something akin to talking to a lamp post.

News groups are great, forums are great. Excellent way to get information. But always check that information out first.

After reading a few other posts by the same author. I would recommend a few hundred hours of professional oracle training for the individual.

Statspack

Quick SQL to submit a statspack snapshot ever 10 minutes for 12 hours... or so.
set lines 132
show parameter job
DECLARE
I NUMBER;
A NUMBER;
START_TIME DATE;
BEGIN
EXECUTE IMMEDIATE 'DECLARE JOBOUT NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBOUT,''statspack.snap;'',:A,null); COMMIT; END;' USING SYSDATE;
FOR I IN 1 ..72 LOOP
A := I*10;
START_TIME := SYSDATE+A/(24*60);
EXECUTE IMMEDIATE 'DECLARE JOBOUT NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBOUT,''statspack.snap;'',:A,null); COMMIT; END;' USING START_TIME;
NULL;
END LOOP;
END;
/
SELECT
JOB
,to_char(next_date,'DD-MON-YYYY HH24:MI:SS') next_date
from
user_jobs
order by
next_date
/
exit

Oh no... no no no no no

I was "ordered" today to do an Application Express presentation and demonstration tomorrow during the lunch break of a meeting. TOMORROW... yeah.. lucky me. What a nightmare, less than 24 hours notice. First presentation in... ummm... 9 years... yeah, thats nine years. Only 15 people, but my boss (May his soul rest in peace, he is the one asking for the presentation), IT managers from 4 other sites and some senior developers. All knowledgable and have relatively powerful influence in the company. I have been busy making data, building a demonstration and stealing power point slides from Oracle Corporation. I am to show its benefits and why we should be using it. I have to show why it is better than Lotus Notes "databases" and "applications".

Script to find all the files

I have built this over the years.. stolen pieces, added pieces, removed pieces. I am sure the baseline came from some public source. If you can identify it let me know. This is a script I run every 24 hours on every one of my databases and store the results.

SELECT
A.*
FROM
(
SELECT
DECODE(D.TABLESPACE_NAME,NULL,DECODE(FILES.TYPE,'CONTROL','CONTROL_FILE','LOG','REDO_LOG_FILE'
,'ARCHIVE_LOG_DEST','ARCHIVE_LOG_DEST','DUMP_DEST '
,'DUMP_DEST','TEMP','TRUE_TEMP'),D.TABLESPACe_NAME) "TABLESPACE_NAME"
,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME
,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )"MOUNT_POINT"
,FILES.FILE_NAME "FILE_NAME_PATH"
,tO_CHAR(FILES.BYTES / 1024/1024,'999,999,999.99') "MEG"
,COUNT "BUFFER_WAITS"
, TIME "SECONDS_WAITED"
,D.AUTOEXTENSIBLE
,D.MAXBYTES/1024/1024 "MAX_MEG"
,tO_CHAR(FILE_FREE.FREE_BYTES / 1024/1024,'999,999,999.99') "FILE_FREE_MEG"
,HIGH.HIGHWATERM
FROM
(
SELECT FILE_NAME,BYTES "BYTES",'DATA' "TYPE",FILE_ID FROM DBA_DATA_FILES UNION ALL SELECT MEMBER "FILE_NAME",BYTES "BYTES",'LOG' "DATA",0 "FILE_ID" FROM V$LOGFILE F, V$LOG L WHERE F.GROUP#=L.GROUP#
UNION ALL SELECT NAME "FILE_NAME",SUM(RECORD_SIZE) "BYTES",'CONTROL' "TYPE",0 FILE_ID FROM V$CONTROLFILE,V$CONTROLFILE_RECORD_SECTION GROUP BY NAME
union all select REPLACE(VALUE,'location=','') "FILE_NAME",0 "BYTES",'ARCHIVE_LOG_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 and vp.name like 'log_archive_dest%' and vp.value is not null and vp.isdefault='FALSE'
UNION ALL SELECT VALUE "FILE_NAME",0 "BYTES",'DUMP_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 AND UPPER(NAME) LIKE '%DUMP%DEST%'
UNION ALL SELECT NAME "FILE_NAME",BYTES,'TEMP' "TYPE",0 FILE_ID FROM V$TEMPFILE
) FILES
,V$INSTANCE V
,DBA_DATA_FILES D
,(SELECT * FROM v$datafile df,x$kcbfwait fw WHERE 1=1 AND FW.INDX+1 = DF.FILE#) WAITS
,(SELECT FILE_ID,SUM(BYTES) "FREE_BYTES" FROM DBA_FREE_SPACE GROUP BY FILE_ID) FILE_FREE
,(SELECT A.TABLESPACE_NAME ,A.FILE_NAME ,A.FILE_ID,
ROUND(((B.MAXIMUM+C.BLOCKS-1)*D.DB_BLOCK_SIZE)/1024/1024) HIGHWATERM
FROM DBA_DATA_FILES A ,(SELECT FILE_ID,MAX(BLOCK_ID) MAXIMUM FROM DBA_EXTENTS GROUP BY FILE_ID) B
,DBA_EXTENTS C ,(SELECT VALUE DB_BLOCK_SIZE FROM V$PARAMETER WHERE UPPER(NAME)='DB_BLOCK_SIZE') D
WHERE 1=1 AND A.FILE_ID = B.FILE_ID AND C.FILE_ID = B.FILE_ID AND C.BLOCK_ID = B.MAXIMUM ORDER BY
A.TABLESPACE_NAME
,A.FILE_NAME
) HIGH
WHERE
1=1
AND D.FILE_NAME (+)=FILES.FILE_NAME
AND D.FILE_ID= WAITS.FILE# (+)
AND FILE_FREE.FILE_ID(+) = FILES.FILE_ID
AND HIGH.FILE_ID (+) = FILES.FILE_ID
ORDER BY SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') ),REPLACE(REPLACE(FILES.FILE_NAME,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )'/',NULL),'oracle/'V.INSTANCE_NAME'/',NULL)
) a
WHERE
1=1
ORDER BY 1,2,3,4
;Certainly isn't the fastest or the prettiest script out there. But the information it provides is beneficial. You will need to modify the mount point parsing to match your environment. I couldn't find the one I built for windows which takes that into account.

Application upgrade

Further to my post about project management and notification. I get myself invited to a planning meeting for the application upgrade. They have done vast amounts of testing, they have 4 databases that they have tested the application upgrade in all of them and everything works smoothly. Takes about 8 hours for the patching scripts to run, then another 12-13 hours for the data load of the old data extracted into the newly patched application. I simply ask one question "Do you think you have enough room in production for all that data?". I know I am in for problems when I get a bunch of blank stares and open mouths. "There should be" and "yeah probably" are the common answers.. then the answer that almost cost me my job as I almost couldn't restrain myself telling this senior fellow exactly what I thought of him. The answer "Well thats YOUR job isn't it". I dang near lost it on the fellow. So I simply snapped back something to the effect of you should have told me sometime in the last two months, not less than a week from go live, your not my wife and I can't read your mind. So, after the meeting I go and take a look at the 2 dev databases I built for them. I know how big they were when I built them, and I know how big they are now. Final numbers, 23 gig larger. 23 gig for an application patch, 23 gig. I am stunned. 23 gig. I can't get over it. I check the other database, yeah 23 gig. 23 gig. More than a 50% increase over the size that is there. I talk with the support people, they once again give the blank stares and open mouth answers I hate. Lots of room in production for the data. I make sure the UNDO is set to grow and there is enough room for the archive logs. I am thinking of approaching them with some hints and suggestions on speeding up the data load as I have caught a snippet or two of their insert scripts. Pure insert scripts into tables with triggers and indexes active. If they are happy with sitting in the office for 24 hours. So be it. I recommend scheduling a full RMAN backup while the users are in, then after the users are out a full export. Then to top it all off a cold backup of the database as it has to go down for the patching anyway. I have a export shell script that splits the database up into chunks based on dba_segments, aiming for 1 gig files, actually building export parameter files listing the tables to keep the files under 1 gig. The objects over one gig, I create parameter files that use the export functionality to split the file into 1 gig files. Then it creates the pipe files for compressing on the fly, and groups the exports into 4 shells scripts so I can run the exports as 4 seperate processes on the server. Took me about 1 week to make the script, and proved its usefullness the first time I used it. I will post it here if enough people ask.

Experience and Exuberance

I have every DBA's dream, a new developer, fresh out of school (overseas). Trained and experienced in Java, JSP, .NET, PL/SQL and SQL. He started about 2 months ago and was first learning one of our large apps to be an application support person before being turned over to me for training in everything oracle. Great guy, full of ideas. Nothing but good comments about him.

I am under instruction to teach him how in my mind to properly code and develop oracle based applications in our environment. Suddenly, this dream turned into a nightmare. Being a lean shop, we have normally outsource our big applications or have gotten them "of the shelf". We have a mess of different versions of the database, different contractors supporting little bits and pieces of some applications, and not others. Home grown integration programs, ETL utilities that date back to DOS 5.0 days converting data from a mainframe system that was to be replaced in 1998 (commissioned 1990) and is still ticking today. The only standard we have right now, is we have no standards but are working towards one. From someone that has been around for ages, it all makes sense - until you try to explain it to someone new. Oh Boy.

I spent about 3 hours with the fellow, going over basics. Most of which he knew, but he was polite and let me ramble on to him anyway. When I say basics - I mean basics. Don't use weak passwords, name your accounts descriptively, use packages instead of procedures/functions, primary keys, foreign keys, do the work in the database- not the front end, bind variables, bind variables, benchmark and test and document before moving from dev/test to production. I am here to help.

I started to focus in on a single system, one that interfaces with our Oracle Financials system and always has a list of changes wanted/needed to the interface. It is, based on storage and user base, the 3rd largest application in our company. The owner of the software vendor actually used to work for our company, he quit and started selling us software. He and his people write good stuff, very focused, no fluff.

The particular change I started to describe to him, is one I thought would a good introduction to the thousands of lines of interface code, we interface accounts payables invoices, fixed asset information, general ledger and even accounts receivables into and from our financials system. The users are constantly choosing the wrong financials period when they post their data. They must choose a period because they can post into many periods so we can't figure it out for them.

When they pick a wrong period - specifically a period that is closed - it involves IT support to go in, clean up the source system and the financials system and let the users send it into the proper period again. Only about 30 minutes work, but one month end we had 9 users do it wrong at different times of the day. We need something to look across into the financials system and see what period is opened and closed. Easy enough.

We go into the financials system, and work out a view on the tables that show what period is opened per module. He comprehends that all with great speed.

We then go into the source system and start to work out the best place to put the check. We decide it had better go at the very beginning of the generation phase as not to waste too much time of the users, so I show him where it should be, he understands and I let him get to work. About 2 - 3 hours worth of coding work to get it all completed on the backend and on the front end.

He sends me an email early the next morning, he documented everything, he had decided to put the code into a trigger on the table that holds the batch information instead of in the PL/SQL that does the generation because the PL/SQL is controlled by the application vendor, everything links to that table for the header/details, a single record is created no matter how much data is being sent. Seems to be a reasonable place to put it. I go over his documentation (not the code) and it seems to make sense. I have him clear it with one of the "power users" and then give the go ahead to put it into test and let some users at it.

A couple of days go by and I inquire with him on why I haven't heard from him.
He says he can't figure it out, he runs his code in an anonymous block and returns in about 2-3 seconds. Yet he puts it into the trigger on the batch table, creates a batch and he has waited hours for the batch to return. Interesting, ok, well I ask him to create a test schema, take a copy of the tables and lets work on it in there. We spend an hour or so and I can't see any problems. I watch him put it into the test database, looks good. We generate a batch and well... minutes turn to tens of minutes. I look at the code... looks great, copy it out, works great.

WTF? I look into it it, I just can't figure it out. So far we have spent about a 5 days on these 16 lines of code. Everything works. I take a step back. Think about things. Stop thinking like developer, go to DBA mode.

Go to DBA_objects and search for objects changed in the last 5 days. Find the before insert for each row trigger on the batch table, and what's this, changes to the before insert for each row table on the invoice detail table. Take a look at the trigger. There is an exact duplicate of his perfectly working code in the trigger. So, basically, for every record put into this invoice detail table, it queries our financials system to make sure the period attached to the invoice is valid and open. Take a look, the test batch he was trying to generate would have over one hundred thousand detail lines.

"Whoops there it is".

Quick fix and a recompile and his code now works wonderfully well. No perceptible difference in speed to the users and they now can't generate to a closed period.

My experience should have told me to keep better track of his exuberance.

He is still a great guy, and now really focused on what he has open in what window.

Notification and project management

Always seems to be a lack of that. Can't get over it. I was told yesterday of a huge 3 day outage in 10 days. I have apparently been scheduled to do a bunch of late night/early morning work over the weekend. I am out of town that weekend (already planned) and that seemed to throw a huge wrench into their plans. DBA work critical to the application patching... no DBA. They have known for 2 months. First word I got of it was an email from a contractor asking me if I could pick her up at the airport since I will be at the office in the morning on Saturday. I had even booked the monday off and the manager who is supposedly managing this project missed that.

ORA-04031 errors

Starting getting ORA-04031 today in one of our 24x7 databases. That is a good way to ruin your day, but this was a 9i database - that isn't supposed to happen. I run scripts from our central database that monitors possible shared pool fragmentation (among other things) every ½ hour, charts it all and more importantly sends an email when things are looking like it could be a problem. I was amazed, the utility never reported a problem via email. I started looking at the issue, had 15 phone calls in 5 minutes, so I flushed the shared pool to at least get people into the system. I checked the shared pool right after I flushed it, in only a few seconds, was already up to 70% usage (450meg shared pool).

No way, that just isn't right. The application designers have never heard of the term "bind variable" before coming to our shop, one of these "database independent" , middle tier independent, we just run java server pages applications. A horror show and a constant source of "its too slow" complaints from all 500+ users. After a few minutes investigation, I found a session that had been running for 105 minutes and was still going. No way, my utility would have told me that, it looks for things like that. I am getting phone calls again that people are getting 04031 errors when opening screens. Take a look, 100% shared pool, so I flush it again.

I can now deal with the session, turns out it is pulling massive amounts of data over a DB link from our financials systems (a couple million rows) with thousands and thousands of queries. I hate integration done by developers. I kill both sessions from both databases and flush the shared pool again. 10 minutes later, 55% usage, the balance is restored. No more complaints. Turns out the guy that started the report, went HOME.

Now, onto my utility, it can't be broken, I wrote it, and I normally write really good stuff.It is accessed via an APEX front end using LDAP authentication… thing of beauty. I pop straight to the logging screen, yup, there are the connections to the problem database. Drill into the results, yup, there is a possible shared pool fragmentation alert and a long running query alert. Go to the outgoing email logs, yup 3 emails sent 12 minutes before all of the phone calls.

Check my inbox, and my blackberry no messages. So, I think email server problems. Go check the email server, I can ping it and send an email from my desktop, check the SMTP servers, they are all working, I can connect and do a POP command. Now what… go to the machine the utility DB runs on, manually send an email, wait, wait, wait Hmmm not getting the email. Try from another machine. Not getting the email. Try from gmail, no email.

I get one of our tech guys to check the mail server. Find out that the lotus notes mail server has decided to stop forwarding email, actually turns out all 3 notes servers decide that forwarding mail is a bad thing, no reason why just "Failure to process message", over 3000 messages lost, gone, poof but luckily reboot fixes it (must be a Microsoft server).

Those emails appear to be gone forever though, I certainly hope the CEO wasn't expecting anything important. I then proceed to calmly explain to my manager the he is going to have dozens of phone calls, and other managers coming to him for the next week complaining that their site lost money and people can't do their work because the "database crashed" and how could we let this happen, why do they pay so much of their monthly department earnings to the IT department?

The managers of course haven't a foggiest idea what a "database" is, or care when it is explained to them it was the fact some goober accountant decided that he really and truly needed all of the detail data in our financials system over the last 7 years in his crystal report, so he actually caused all of the problems. My poor manager is going to get raked over the coals, he is going to get all of this, because, LOTUS NOTES SUCKS.