According to me, the best part of being in UK as a DBA is, to have the opportunity to join UKOUG events. I started to join SIG events this year and DBMS SIG July 2009 was the third and the best one so far, I joined as a delegate.
Here is my review of presentations and the event.
Upgrading Oracle Estate by Phill Brown.
This was a lucky presentation for me, because it was related with Oracle Failsafe, which we are currently having problems with. Phill explained the issues they faced during upgrading Oracle Failsafe, Oracle Database and filestructure on windows clusters at the same time at one of their clients . My highlights from the presentation are some metalink notes like Failsafe Errors (108442.1) , Oracle DB and Windows memory (46001.11) and 10G Agents on Failsafe (330072.1). I asked Phill if they tried to configure DB Console apart from grid configuration but he said they did not try it. The problem we are having with Oracle Failsafe is, if you have your Oracle Home on local disks, DB Console is having problems with Oracle Failsafe, because its configuration repository sits in Oracle Home and for that reason Oracle suggest to put Oracle Home to network drive but that causes other problems to us. I wish they found a solution during this project but I am not lucky enough
Oracle Support June Update by Phil Davies
This was the second time I listened this presentation series from Phil and again it was very helpful. I think He is from Oracle support and his summary of available patches and patch bundles are very good for the ones who doesn’t have time to look available patches from metalink.
First news is that 10.2.0.5 will be terminal patch release and probably will be available in 2009 Christmas .
He mentioned that one of his clients having a very fast growing repository problem after 10.2.0.5 EM Grid Control release. He warned us not to collect everything available in templates, and instead collect what you actually need, to not have this problem.
He gave a good list of available patches, bugs and Here are the ones caught my eye at the first place
Generic Support Status Notes (strongly recommended to keep an eye on notes below)
For 11.1.0 Note id 454507.1
For 10.2.0 Note id 316900.1
For 10.1.0 Note id 263719.1
For 9.2 Note id 189908.1
Dataguard Merged Patches
11.1.0.7
Physical/redo Note 7676737.8
Logical StandbyNote 7628387.8
Data Guard BrokerNote 7628357.8
10.2.0.4
Logical standby Note 7937113.8
Physical Bundle #1Note 7936993.8
Broker Recommended Note 7936793.8
10.2.0.3
Physical/Redo Transport Patch 6081547
Logical Standby/Logminer Patch 6081550
Data Guard Broker Patch 6048286
RMAN 10.2.0.3 Bundle Patch 6081556
11.1.0.7
Physical/redo Note 7676737.8
Logical StandbyNote 7628387.8
Data Guard BrokerNote 7628357.8
10.2.0.4
Logical standby Note 7937113.8
Physical Bundle #1Note 7936993.8
Broker Recommended Note 7936793.8
10.2.0.3
Physical/Redo Transport Patch 6081547
Logical Standby/Logminer Patch 6081550
Data Guard Broker Patch 6048286
RMAN 10.2.0.3 Bundle Patch 6081556
Recommended Patch Bundles Note 756388.1
Bits and Pieces
This time slot is arranged for free talking about experiences of delegates and it was very successful. Chris Dunscombe from SIG committee did a small presentation about the problem they had with Delayed Cleanout and ORA-1555 problem. End of his presentation there was a perfect discussion between Joel Goodman and Jonathan Lewis. I was sitting in the middle and they were at right and left edge of the room. It was like watching Wimbledon Final from the middle of Center Court. I think Jonathan Lewis will cover this issue but I still plan to write a post to cover it more detailed. DB Links Master Class Part 1 by Joel Goodman
This was the second time I watched this part of the Master Class . I was in Edinburgh DBA SIG when I first watched this presentation and it was just after AOT by Tanel Poder, so I couldn’t not focus that much.
This time I listened better and I plan to review it as another post, after listening the second part .
Graphing AWR Data in Excel by David Kurtz
This was presentation of this blog post. His approach has the smilar idea of PerfSheet of Tanel Poder which is using excel instead of getting lost in a pool of the metric values. If you ask me I am happy with PerfSheet.
Lunch
Best lunch I had at any SIG so far
Row Migration can Aggravate Contention on Cache Buffer Chain Latch.
This is again presentation for David’s another blog post. This presentation was about using method from the previous presentation to catch the abnormality. I suggest you to read the blog post for more info. I think missing part of the story was, how come he got the idea to check row migration after detecting the abnormality. David said it was idea of the DBA which does not depend on any hot block analysis.
How to Read Your Statspack/AWR Report by Jonathan Lewis
Perfect as previous 3 presentations I watched from Jonathan Lewis. He covered 5 Statspack reports in 1 hour time which were brought by delegates. It was very nice and a bit hard to watch him while he is doing his job. Although This session was very helpful, I think you need to have 20+ years of Oracle Experience with supporting Math Degree to catch the possible problems as fast and as right as he does. To cover more you need to start with this blog post of him and read all 11 of his series. One good advice I caught, do not create index in last update date columns this will cause high redo generation because of index leaf splits if the row has high updates.
I found chance to ask Jonathan’s advices for DBA who started after 9i (when features of Oracle DB and the Documents is too big comparing to v5) . Here are his advices
1- Read concepts guide and Admin guide, min of 2 times to cover the topics. Try the thing you learn
2- Spend time on OTN forums. You don’t have to reply just try to solve other peoples problems. Don’t give up and chase the problems which means have the enthusiasm to wake up at 3am
3- Do not focus on internals because 99% of the time they are useless
4- I asked him how many hours he sleeps, his answer was 6. 1 more than I thought
Thanks to the SIG committee and sponsors (Guardian) for this nice event. I hope one they I will be brave enough to do a presentation in English at one of these SIGS.
Basically he creates a procedure to run explain plan and gives the execute access to that procedure.
This is the procedure
CREATE OR REPLACE PROCEDURE explain_it (p_plan_id IN VARCHAR2,
p_sql IN VARCHAR2,
p_plan OUT my_types.expl_cur) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'SELECT LPAD('' '',2*(LEVEL-1))||OPERATION||'' ''||OPTIONS||'' ''||OBJECT_NAME||'' ''||';
l_sqlstr := l_sqlstr||'DECODE(ID,0,''COST = ''||POSITION) "QUERY PLAN" FROM plan_table ';
l_sqlstr := l_sqlstr||'START WITH ID = 0 AND STATEMENT_ID = :b1 ';
l_sqlstr := l_sqlstr||'CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = :b2';
EXECUTE IMMEDIATE 'EXPLAIN PLAN SET statement_id = '''||p_plan_id||''' FOR '||p_sql;
OPEN p_plan FOR l_sqlstr USING p_plan_id, p_plan_id;
END;
/
or to use with dbms_xplan.display
CREATE OR REPLACE PROCEDURE explain_it (
p_sql IN VARCHAR2,
p_plan OUT sys_refcursor) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'select * from table (dbms_xplan.display)';
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR '||p_sql;
OPEN p_plan FOR l_sqlstr ;
END;
/
and this is how it works
SQL> create user read_only identified by read_only
2 ;
User created.
SQL> grant create session to read_only;
Grant succeeded.
SQL> create table exp_test as select * from user_objects where rownum<1000;
Table created.
SQL> create view v_exp_test as select * from exp_test;
View created.
SQL> grant select on v_exp_test to read_only;
Grant succeeded.
SQL> grant execute on explain_it to read_only;
Grant succeeded.
----connect with read_only
SQL> connect read_only/read_only
Connected.
SQL> select count(*) from hr.v_exp_test;
COUNT(*)
----------
156
SQL> explain plan for select count(*) from hr.v_exp_test;
explain plan for select count(*) from hr.v_exp_test
*
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view
---TRY WITH NEW PROCEDURE
SQL> var my_cur refcursor;
SQL> EXEC hr.explain_it('SELECT * FROM hr.v_exp_test',:my_cur);
PL/SQL procedure successfully completed.
SQL> print my_cur;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3488715207
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 156 | 29640 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EXP_TEST | 156 | 29640 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
12 rows selected.
Last 5 days, I was at Hurricane Festival enjoying Hard Rock, without any internet access and I couldn’t keep my promise to write blogroll report on time
After 4 days of tiring camping conditions, I am now ready to write last weeks highlights.
As you see from the blogroll page I follow over 140 Oracle DBA related blogs and all blog aggregators from orana.info. I try to read updates from all of them but the this huge amount of different source of information about different topics started to blow my mind. I start not to remember what I read and which one was useful in technical context, and most of the blog headers do not match with my future search criteria. For these reasons, I decide to write my own weekly blogroll report just for indexing technical oracle blog posts with my own keywords. ( Log Buffers of Pyhtan Group are very nice but not enough) . I am planning to write every Friday night. If you find anything useful please leave a comment so the post will be complete.
As you all know Oracle Enterprise Manager can provide incredible amount of information for the database by gathering information from the database. This information is not just in V$ / DBA_ or any internal table, there are also other tables under SYSMAN schema for providing information to EM repository.
on 11.1.0.6 Windows XP version for single instance EM Repository, my DB shows 681 tables under SYSMAN schema
on 10.2.0.4 HP-UX box for single instance EM Repository, my DB shows 341 tables under SYSMAN schema
As you can see it doubled up on 11G. New features brings new tables to this schema.
I could not find good documentation about the tables under SYSMAN schema but the structure of the table names are meaningfull enough to understand what they can be used for. For most of them The naming is like MGMT_X_Y. X stands for the generic name like HC (in my understanding Hardware Components/Configuration or something else ) and Y is for information under the generic root like HARDWARE_MASTER. Table full name is MGMT_HC_HARDWARE_MASTER
Lets start with this table and see what we can get from the table
As you can see Tables under SYSMAN schema can give you very nice information, which you can’t find in any V$ view or DBA_ table especially for Host system.
Lets see what else we can get under MGMT_HC_%
SQL> select table_name from dba_tables where table_name like 'MGMT_HC%';
TABLE_NAME
------------------------------
MGMT_HC_SYSTEM_SUMMARY
MGMT_HC_HARDWARE_MASTER
MGMT_HC_CPU_DETAILS
MGMT_HC_IOCARD_DETAILS
MGMT_HC_NIC_DETAILS
MGMT_HC_OS_SUMMARY
MGMT_HC_OS_PROPERTIES
MGMT_HC_OS_COMPONENTS
MGMT_HC_FS_MOUNT_DETAILS
MGMT_HC_VENDOR_SW_SUMMARY
MGMT_HC_VENDOR_SW_COMPONENTS
We can get Operating system summary from MGMT_HC_OS_SUMMARY (this is my favourite on windows because it is hard to find 32bit 64 bit information even from OS itself)
SQL> @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5
NAME : Microsoft Windows XP Workstation
VENDOR_NAME : Microsoft Corporation
BASE_VERSION : 5.1
UPDATE_LEVEL : Service Pack 2
DISTRIBUTOR_VERSION : N/A
MAX_SWAP_SPACE_IN_MB : 3933
ADDRESS_LENGTH_IN_BITS : 32-bit
PATCHES : 152
-----------------
PL/SQL procedure successfully completed.
Do you want to learn which operating system fixes are installed on the box, then MGMT_HC_OS_COMPONENTS is the right table for you
SQL> @printtab "select * from sysman.MGMT_HC_OS_COMPONENTS where rownum<4"
SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5
NAME : KB867282
TYPE : Patch
VERSION :
DESCRIPTION : Windows XP Hotfix - KB867282
INSTALLATION_DATE :
-----------------
SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5
NAME : KB873333
TYPE : Patch
VERSION :
DESCRIPTION : Windows XP Hotfix - KB873333
INSTALLATION_DATE :
-----------------
SNAPSHOT_GUID : B4E96AB2F8F9436E8A15B562B90E12B5
NAME : KB873339
TYPE : Patch
VERSION :
DESCRIPTION : Windows XP Hotfix - KB873339
INSTALLATION_DATE :
-----------------
PL/SQL procedure successfully completed.
As you see these tables are there and ready to be used. There are too many other tables under SYSMAN schema, which might have valuable info for your needs. I think my duty is finished here, remaining is homework for you, to discover the information under the tables listed via this sql command
select table_name from dba_tables where owner='SYSMAN' and num_rows>0;
I could’t take myself from reading his blog journey from the very beginning. All of his posts are nice, clear and interesting. If you are an Oracle lover, I highly recommend you follow this highly technical blog
On 11G user and system traces are on the same directory (%DIAGNOSTIC_DEST%\diag\rdbms\SID\trace on my machine) and automatic health monitoring looks like a bit more active than 10G. On 10G I wasn’t seeing automatic trace generation for user sessions, however on 11.1.0.6 Oracle nearly creates trace for every session and it realy drives me mad when I am working on 10046 traces. I checked unsupported parameters and I found _disable_health_check parameter. When you set this parameter to TRUE a Oracle stops generating trace files for every user session. Parameter needs restart of database.
It looks like there are 32 other diagnostic related parameters on 11G but _disable_health_check was enough to solve my problem.
I can see this parameter is available on 10GR2 and 11GR1 but not available at least on 9.2.0.4
!!!!! As you see, it is unsupported dont use it on any production system without asking Oracle Support.
One of my biggest problem as a permanent worker is dealing with third party product databases. The main problem with them is, there is no one on site in case of a problem, calling someone from vendor is a cost option which most of the companies try to avoid from.
Second problem is that, it is really hard to know, how that application works at database level. When you ask the guy who uses the program, they start to talk business language with me and my brain, most of the time struggles to understand anything other than 0 and 1.
Problems do not finish here, I dont know how it works at your company but most of the time when there is a major upgrade on the software they send a piece of DB upgrade script and you run it.
Please be honest, how many of you check , before you run this min 1000 lines of sql code ???
I hope I am wrong, but probably most of you check it after it causes a problem on the DEV server especially when you are at a big enterprise which has lots of these third party products. If it doesn’t cause a problem then you run it on prod.
Lets say you got a VM server or slow development server and most of the time users are already crying for slow performance on DEV system when they compare it with PRD so when it runs slow on DEV probably DEV system will be blamed instead of the batch upgrade code. That is exactly how the incident occurred on one of our systems.
One of my colleague ran the batch job which took over 5 hours and they (him and the business guy) decided this slowness is because of VM DEV box. They believed that PRD box will do this in less than 2 hours which was acceptable for business.
Mistakes so far
Mistake 1- DBA did not check the code.
Mistake 2- DBA did not check the response time
Mistake 3- Common beliefs (slow DEV) directed both business and dba wrongly.
After they start upgrade on PRD, it was 5 hours so far when I decided to involve to the problem. What I saw was was a fancy update with a more fancy execution plan.
it was something like below
SQL> create table test as select * from dba_tables;
Table created.
SQL> create table test2 as select * from test;
Table created.
SQL>
SQL> explain plan for
2 update test t
3 set OWNER=(select owner from test2 where owner=t.owner and table_name=t.table_name),
4 TABLE_NAME=(select TABLE_NAME from test2 where owner=t.owner and table_name=t.table_name),
5 TABLESPACE_NAME=(select TABLESPACE_NAME from test2 where owner=t.owner and table_name=t.table_name),
6 CLUSTER_NAME=(select CLUSTER_NAME from test2 where owner=t.owner and table_name=t.table_name),
7 IOT_NAME=(select IOT_NAME from test2 where owner=t.owner and table_name=t.table_name),
8 STATUS=(select STATUS from test2 where owner=t.owner and table_name=t.table_name),
9 PCT_FREE=(select PCT_FREE from test2 where owner=t.owner and table_name=t.table_name),
10 INI_TRANS=(select INI_TRANS from test2 where owner=t.owner and table_name=t.table_name),
11 MAX_TRANS=(select MAX_TRANS from test2 where owner=t.owner and table_name=t.table_name),
12 NEXT_EXTENT=(select NEXT_EXTENT from test2 where owner=t.owner and table_name=t.table_name),
13 PCT_INCREASE=(select PCT_INCREASE from test2 where owner=t.owner and table_name=t.table_name),
14 AVG_SPACE=(select AVG_SPACE from test2 where owner=t.owner and table_name=t.table_name),
15 INSTANCES=(select INSTANCES from test2 where owner=t.owner and table_name=t.table_name),
16 IOT_TYPE=(select IOT_TYPE from test2 where owner=t.owner and table_name=t.table_name),
17 COMPRESS_FOR=(select COMPRESS_FOR from test2 where owner=t.owner and table_name=t.table_name),
18 READ_ONLY=(select READ_ONLY from test2 where owner=t.owner and table_name=t.table_name),
19 DROPPED=(select DROPPED from test2 where owner=t.owner and table_name=t.table_name);
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1747411015
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 2350 | 461K| 23 (0)| 00:00:01 |
| 1 | UPDATE | TEST | | | | |
| 2 | TABLE ACCESS FULL| TEST | 2350 | 461K| 23 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEST2 | 1 | 34 | 23 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TEST2 | 1 | 34 | 23 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| TEST2 | 1 | 51 | 23 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| TEST2 | 1 | 51 | 23 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| TEST2 | 1 | 51 | 23 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL| TEST2 | 1 | 40 | 23 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| TEST2 | 1 | 47 | 23 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL| TEST2 | 1 | 47 | 23 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL| TEST2 | 1 | 47 | 23 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL| TEST2 | 1 | 47 | 23 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL| TEST2 | 1 | 47 | 23 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL| TEST2 | 1 | 47 | 23 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL| TEST2 | 1 | 41 | 23 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL| TEST2 | 1 | 42 | 23 (0)| 00:00:01 |
|* 17 | TABLE ACCESS FULL| TEST2 | 1 | 45 | 23 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL| TEST2 | 1 | 37 | 23 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL| TEST2 | 1 | 37 | 23 (0)| 00:00:01 |
----------------------------------------------------------------------------
There wasn’t any index no stats nothing. Tables were created and updated and the developer was unaware from the syntax below. (I know that Oracle is intelligent enough to cope with the dodgy one but as least developer should write more readable code)
I asked to interrupt the running update and create index and gather stats before this update. Guess what, everything run under 1 minute.
But my colleague was against this idea that this script needs to be re-sent to vendor and must come back to us. I did not have time to deal with this discussion and left him to solve however he wants to as long as he doesn’t waste my resource and time. Finally vendor approved what we did and the problem solved.
At our team meeting, I said this is unacceptable and anything running over 1 hour on OLTP DEV (no matter how the DEV configuration is ) systems deserves to be checked for performance initially by DBA, and thank god at least this is accepted.
IMHO, as long as DBA doesn’t change any logical thing on the code, he should have the freedom of modifying the code, because ,sending code to vendor for review, is just easy way to get rid of dirt. Vendor doesn’t have our data, doesn’t have my stats, doesn’t have our configuration etc etc, and probably they are trying to write an application which will work on every RDBMS (deadliest mistake).
There is also a security related issue with these kind of third party codes but it is another issue …
Moral of the story for me is check everything before your run, during running don’t leave it monitor it, if you haven’t run it on DEV / TST ask for timings and don’t trust word of anybody without any proof.
