H.Tonguç Yılmaz - Oracle Blog

Knowledge comes with experience and experimentation, but of course only your experiences will be limited. Other than documentation I love reading support case studies very much. They are real life experiences, you can also find out some “don’t do”s and bugs here.

So I want to share one recent Ubtools support case study I liked very much;

“ORA-01187: cannot read from file” in one of the RAC Node

And two complementary reminders;

Ubtools support issuenavigator and RSS

Previously released Oracle support case studies

This topic was discussed today on our forum group OracleTURK. First things first, what may be the reasons you may need to find unused or read only Oracle segments in your database.

- of course if we now they are not needed we can easily get rid of them right :)
- if you can find your read only data you can put them into specific separate read only tablespaces, compress those segments as a result gain in backup duration and space.

If your database applications pro-actively handles this data lifecycle management need or you can easily gather this information from them then life will be much more easy for you. Otherwise your database management software, for us Oracle of course :), is your friend again as usual, all you have to do is to choose some combination of features already supplied within your Oracle release depending to your need and their advantages and disadvantages.

After 10g if you have the extra cost option Automatic Workload Repository(AWR-DBA_HIST% views) helps a lot, but what if you are still on 9i or you do not have that cost options? Auditing or Statspack data(PERFSTAT schema’s tables) will help similarly if you configure them. Before getting into the details of some options I tried be warned about the retention of the data you will be analyzing. For example if last 15 days information is relevant for you to decide if a segment is not used or can be read only and your AWR retention is 7 days then you will conclude wrong. So as an initial step controlling AWR or Statspack reports retentions will be important.

SELECT retention FROM dba_hist_wr_ control;

Or if you will be depending on V$SQL kind of dynamic performance views data you must ensure that your last instance startup time is enough behind for your analysis need.

Below mentioned options may have their own alternative performance costs, no pain no gain in the real world, so who can not pay for AWR, Statpack kind of a repository activity can prefer to use some kind of a network sniffer tool capturing any SQL requested from the instance.

1- Database auditing based options

a) Standart auditing;

audit_trail=DB

AUDIT SELECT ON BY ACCESS;

SELECT ..
FROM sys.aud$
WHERE .. ;

b) Fine grained access control;

BEGIN
dbms_fga.add_policy (object_schema=>’..’, object_name=>’..’, policy_name=>’..’);
commit;
END;
/

SELECT ..
FROM dba_fga_audit_trail a, dba_objects b
WHERE a.object_name = b.object_name
AND a.object_schema = b.owner
AND .. ;

2- 10g AWR based options

a) Historical SQL execution plan statistics;

SELECT ..
FROM dba_hist_sql_plan p, dba_hist_sqlstat s
WHERE p.sql_id = s.sql_id
AND .. ;

b) Historical segment statistics;

SELECT ..
FROM dba_hist_seg_stat ss, dba_hist_seg_stat_obj o
WHERE ss.obj# = o.obj#
AND ss.dataobj# = o.dataobj#
AND ss.dbid = o.dbid
AND .. ;

3- Statspack based options

a) Historical SQL statistics;

SELECT ..
COUNT(DECODE(s.command_type, 2, 1, 6, 1, 7, 1, 189, 1)) sql_change,
COUNT(DECODE(s.command_type, 3, 1)) sql_read
FROM perfstat.stats$sqltext s
WHERE s.sql_text LIKE ‘% ‘||t.table_name||’ %’
AND .. ;

b) Historical segment statistics;

SELECT ..
FROM perfstat.stats$seg_stat s, perfstat.stats$seg_stat_obj o
WHERE s.obj# = o.obj#
AND s.dataobj# = o.dataobj#
AND s.dbid = o.dbid
AND .. ;

4- Others require mentioning

DBA_TAB_MODIFICATIONS information is reset after statistics gathering, so you may easily capture the modification amounts for your segments from the last stats gathering time.

SELECT ..
FROM dba_tab_modifications
WHERE .. ;

V$ACCESS can show the accessed objects for the moment that you are querying;

SELECT ..
FROM v$access
WHERE .. ;

And lets meet with Oracle’s new answer to this kidn of a need; Information Lifecycle Management(ILM)

This morning I was requested to check a 10gR2 physical standby instance which was slow to apply redo and the lag was growing rapidly, my initial check showed me 9 to 12 minutes duration for 250 MB redo log file to apply;

Mon May  5 06:03:17 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902209.arc
Mon May  5 06:13:20 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902210.arc
Mon May  5 06:23:07 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902211.arc
Mon May  5 06:32:51 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902212.arc
Mon May  5 06:42:40 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902213.arc
Mon May  5 06:52:01 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902214.arc
Mon May  5 07:00:52 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902215.arc
Mon May  5 07:09:58 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902216.arc
..

After a quick look from unix SQL*Plus as sysdba the Top 5 waits were;

PX Deq: Test for msg   (4,5x amount)
free buffer waits
log file sequential read
PX Deq Credit: send blkd
checkpoint completed                               

And after going through the Note:387343.1 and Data Guard Redo Apply and Media Recovery Best Practices 10gR2 I recommended to increase the buffer cache from 512 MB to 6 GB, reduce the recovery parallelism from 20 to 8 and also some additional parameters like;

*.db_cache_size=6G
*.log_buffer=78643200
*.large_pool_size=2G
*.pga_aggregate_target=10G
*.workarea_size_policy=’AUTO’
*.parallel_min_servers=360
*.parallel_max_servers=520
*.parallel_threads_per_cpu=2
*.optimizer_mode=ALL_ROWS
*.optimizer_dynamic_sampling=4
*.cursor_sharing=EXACT

RECOVER MANAGED STANDBY DATABASE PARALLEL 8;  

After the restart as I monitored the apply process from the alert.log again, now the database was able to apply 250 MB redo log file within 30 seconds and 1 minutes;

Mon May  5 09:01:07 2008
ALTER DATABASE RECOVER  automatic from ‘/assos_arcdir’ standby database PARALLEL 8
Mon May  5 09:01:07 2008
Media Recovery Start
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 8 processes
Mon May  5 09:01:52 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902217.arc
Mon May  5 09:02:29 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902218.arc
Mon May  5 09:03:41 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902219.arc
Mon May  5 09:04:39 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902220.arc
Mon May  5 09:05:33 2008
Media Recovery Log /assos_arcdir/arch_rtx_1_445653769_0000902221.arc
..

And the Top 5 waits were now;

PX Deq: Test for msg   (x amount)
log file sequential read
checkpoint completed
db file parallel write
latch: checkpoint queue latch

So as my lessons learned, I really want to highlight two things, yes again and again these two;

1- If you have enough physical memory, benefit from it to the ends, do not run your production databases with small SGA(critical for OLTP) or PGA(critical for OLAP) especially after 9iR2(nearly no latching problem like the old days with large SGAs). Come on even on laptops we use some GBs of sized SGAs nowadays.

2- Parallelism does not mean faster, I benefit from reducing the degree of parallelism most often than compared to increasing it, 4 or 8 is nearly anytime is enough for my needs. I hope Oracle invests more in automatic parallelism tuning feature in the near future which is FALSE by default on 10gR2 and like automatic undo or automatic pga management also automatic parallel tuning takes its place in my most liked automatic tuning Oracle features :)

Here are some of the sql scripts I used during above troubleshooting from sql*plus, like in above standby case sometimes we only have sql*plus and some simple v$ queries to gather required troubleshooting information.

Each year I find myself in a project which involves a huge data migration or database upgrade somehow. So up to now I blogged several times on my previous experiences;

- http://tonguc.wordpress.com/2007/07/13/i-am-back/
- http://tonguc.wordpress.com/2007/08/31/some-best-practices-of-upgrading-oracle-database/
- http://tonguc.wordpress.com/2007/07/16/deffensive-upgrade-methods-but-still-no-pain-no-gain/
- http://tonguc.wordpress.com/2007/04/24/take-the-risk-and-migrate-10gr2/
- http://tonguc.wordpress.com/2007/03/17/some-migration-experiences-to-share/

For a quick summary on migration best practices I may advise you to take close attention on your SGA and PGA memory pools tuning, keep target database on NOARCHIVELOG mode with big(I prefer 10GB at least) single membered 10 or more redo groups during data transfer at target and test your migration setup several times until you are satisfied with the results. When you are within the expected time window leave it, tuning is an endless road do not let to loose control :)

This week we needed a strategy to easily switch between migration(much more OLAP) and normal(much more OLTP) parameters. So I choosed to use pfile over spfile for this kind of a need and created a special OLAP tuned parameter file and an OLTP pfile from current spfile. As a result a normal startup will open the instance with the original OLTP based parameters which comes from the pfile under $ORACLE_HOME/dbs

– clean shutdown and open with OLTP like default parameter file

SQL> shutdown immediate

SQL> startup

–

And whenever we need OLAP like migration parameters we will close the related instance and open with the special tuned OLAP pfile.

– clean shutdown and open with OLAP like parameter file

SQL> shutdown immediate

SQL> startup pfile=/../../FOR_MIGRATION_USE_ONLY_PFILE_BSCS.ora

–

In this pfile I used IFILE option of parameter file, so that I make sure I take all mandatory parameters like destinations first from the original OLTP like pfile and override only the ones needed for OLAP like settings. Below is a sample I share with you which was designed on a 84 dual core CPU, ~200GB physical memory, 10.2.0.3 IBM AIX instance.

FOR_MIGRATION_USE_ONLY_PFILE_BSCS.ora

So of course any time you need a similar OLAP like parameter file you need to customize and test the SGA, PGA and parallelism parameters depending to the instance’s hardware and Oracle release you will be working.

Reference Guide of your release and this query for hidden parameters may assist you during customization.

– how to query hidden parameters’ information based on x$ views

select a.ksppinm  “Parameter”, b.ksppstvl “Session Value”,
       c.ksppstvl “Instance Value”, ksppdesc “Desc”
  from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
–   and ksppinm in (’_smm_px_max_size’, ‘_smm_max_size’, ‘_pga_max_size’)
 order by a.ksppinm;

–

I have been working on Oracle databases for more than 10 years now. I started with version 7.3 at a local bank and with 8i I moved to the third largest Telco in Europe. If you do value, I also choosed to get certification. I have attended several educations, seminars, conferences, experienced lot of risky projects and weird problems. But my last three days experience was completely something different. Since my native language is not English and this post mostly will not be a technical one, it may get harder for me to express my feelings here.

For years I have been reading Jonathan Lewis’s articles, books, forum posts and of course after 2007 his blog. In my opinion he is a very special Oracle database expert, the reason why I think this way is not his deep knowledge about the technology but his willingness of sharing all his years of hard work and experiences every time with a very gentle way. Also Jonathan is one of the best teachers I experienced in my whole life. Nowadays it is getting harder to find someone having all this attributes together. IMHO some are very experienced but they do not care about sharing or some “huge mr.ego” experts try to beat people all around forums.

Jonathan’s experiences for all those years produced two important books for the community. But let me talk a little on the seminar book; A4 sized ~400 pages, each page supported with at least three or four paragraphs related to Jonathan’s previous on-site consultancy experiences, which you may not learn from documentation or any google search. These are all nasty things we experience during our daily lives, but Jonathan documented them so well that this course book’s content is like a best of summary of my all time best Oracle books. Even very advanced internals were explained with perfect simple powerpoint slides and supported with example scripts which are all provided in a CD.

Normally after a seminar I share my seminar notes(highlights, topics I liked most lets call), but after this kind of a knowledge storm this time I do not have the hearth for it. Instead I will be sharing the course topics briefly, the new questions I have now after the seminar and some hints you may find useful if you want to attend this seminar in the future.

Yes I have lots of new questions now, normally I love this kind of a feeling, having questions is always good for motivation. But this time I also feel some fear(this may not be the exact word to define, anyway), because Jonathan showed me that even on fundamental topics like redo or undo I still have lots of research to do. This is so normal I guess, because I am limited with the experience that I can get from the databases around me. When operational stuff takes lots of time there is no space for new experiences usually. So for sure I may say in consultancy market you get to learn and develop a lot. I admired Jonathan very much and I felt strongly that in near future I will love to accept its risk and quit to do much more dedicated research on Oracle database.

- Course table of contents
- Some hints you may like
- Lots of new questions I have (if you feel you know some answers please comment :)

Jonathan also uses some extreme examples on purpose from his past experiences, I believe to have and keep the interest all during his long sessions, this way of teaching is something I sympathized. Jonathan shared the SQL scripts to reproduce the outputs for our own databases which he might have tested on 9i or his laptop and shared the results.

I think Oracle community must feel very lucky to have some independent(non-Oracle employee) experts who are being honest, also mentioning the dark sides(bugs, don’t DOs) of this great software, advise to test in details, to be doubtful about so called “guru”s and each new feature for our own comfort. This is unique and precious in todays “marketing” world. For Jonathan’s efforts on developing this course I can not find the right words to explain how thankful I am. I will strongly suggest for the readers of my blog who feel them as an experienced Oracle DBA to attend his seminar at any cost.

ps1: also Graham Woods was in Istanbul, he did a very similar presentation he did on UKOUG last December which I already shared my notes before here.

ps2: UKOUG 2008 call for papers submission deadline for Oracle technology category is getting closer, 9 May 2008. This year I also want to come front, do at least one presentation, I have RMAN, APEX and of course Tuning related topics in my mind. So let’s come front, share our experiences with world’s largest independent Oracle User group community.

ps3: In my 20th month now I am very close to my 200,000th hit(wordpress’s counter). Recently my wordpress dashboard automatically evolved somehow and now I have lots of new stats. As far as I see people once loved event 10046 tracing, flashing back options after 10g and of course my initial best practices posts and no matter what I post after these three seems to be dominant and to stay on top visited list forever I guess. So anyway, thank you for reading and I really hope to have more comments in the future since sometimes I really feel I am alone here :)

This event is not only useful for tuning purposes, whenever I experience a weird behavior of the database engine usually also Oracle support requests the trace file produced with this event since the content shows any database calls made inside the traced application.

Today one of my colleagues asked me if he can unwrap a wrapped pl/sql application. I asked him if he really needs to unwrap it or what he is after may be the SQL calls inside that wrapped application. And showed him the tkprof report of the event 10046 trace of a sample wrapped application, he loved it since it was a kind of a reverse engineering of the pl/sql api they were supplied by a third party company which is not around anymore.

Code Listing - Tracing a Wrapped PL-SQL Code Example

Above tkprof report shows that wrapping a simple pl/sql code like that still adds 57 internal SQL statement calls, 20 hard parses and 208 consistent gets. So before buying a wrapped application and deploying it to your production it may be better to ask yourself twice if you could force more the vendor to get the sources to avoid any performance cost and a possible future reverse engineering effort.

Recently I used 10046 event output to troubleshoot a weird ORA-xxxxx error problem with DBMS_CDC_SUBSCRIBE supplied package and I tried to reverse the database calls of APEX application development environment by starting the trace from a database login trace trigger. After getting used to 10g’s beauties like ASH my needs to use 10046 tracing are getting very limited, but I am sure event 10046 will remain as a powerful tool in my Oracle toolbox for years.

Anyway if you need more information on wrapping a pl/sql application please check Note:341504.1 - FAQ: Wrapping PL/SQL Source Code or you persist to unwrap a wrapped pl/sql please check this presentation :)

This post will be like a summary of last two weeks of mine. First of all, yesterday we celebrated my company’s 15th year, I feel very lucky to be a part of this success story for my last 8 years :)

Lately I was and will be mostly working in two projects, in parallel;

1- Migrating our billing/crm database from Tru64 to AIX platform; there are several important lessons learned in this cross-platform xx TB Oracle database migration, I will be blogging about these in details later. For now I will be sharing below Oracle-L discussion of mine, a DDL tracking application which was requested to audit and control especially the database objects which are not supported by the change data capture product Goldengate in this post.

- Oracle 10gR2 Multi-Terabyte Database Cross-Platform Migration Method
- Cross-Platform Tablespace Transportation from Oracle documentation
- Oracle 10gR2 Multi-Terabyte Database Cross-Platform Migration a Case Study

You may find the DDL(Data Definition Language, statements used to define the database structure or schema) types in Oracle 10gR2 here. In this small application I used a database trigger and an autonomous transaction based procedure to log the operation, db_name, login_user, object_owner, object_name, object_type, sql_text, attempt_by and attempt_date informations.

A custom DDL tacking application for Oracle database

Also in one of my previous posts I discussed on 10g’s ORA_HASH SQL function as a data validation tool. During a row by row migration(unload on source>ftp text to traget>load texts parallely on target or like in our case insert into select from over dblink plus apply changes captured by a CDC tool on production during this logical data transfer kind of strategies) a validation need becomes very important.

This validation must involve three major parts in my point of view;

1- the metadata validation,
2- the data validation,
3- the performance validation.

I made several updates on the previous post, if you are interested on this topic please follow this link.

2- Design and development of an operational data source in front of our datawarehouse; this environment will differ from datawarehouse in two major areas, first is it will not be denormalized like a typical dwh and be very similar to the operational database’s tables, so transaformation will be very limited compared to a standard ETL. Second from this environment customers want to have near-real time reporting, so Change Data Capture(CDC) kind of data transfer methods are needed.

We will be first implementing an operational data source for our CRM database which runs Siebel application. There are over 60 typical operational near-real time reports need. Oracle Warehouse Builder 11g release has a new feature to integrate a warehouse with Siebel source databases, we will also be testing the performance and capabilities of this new feature.

But as I commented here we will also be working on a logical standby implementation to have the ETL window flexibility in order to be near-real time.

During this project we also had an interesting discussion on DBMS_STAT package’s two options; CASCADE and GRANULARITY. In Choosing An Optimal Stats Gathering Strategy post of Greg Rahn I commented about this discussion. For an 10g Release 2 datawarehouse CASCADE option may be left to FALSE since after each rebuild at the end of a typical daily ETL optimizer already collects statistics. And for the GRANULARITY option to leave it to its default usually will be enough. These two things may help you avoid unnecessary gathering, and reduce total ETL time of course. Below is a small demo for CASCADE option.

Is DBMS_STATS package’s CASCADE option need to be set to TRUE always?

And for the OTHERS part my three highlights are as following;

1- My team Fenerbahçe defeated English Chelsea in Istanbul, but in London we couldn’t make it to the Champions League semi-finals, in my opinion Chelsea isn’t playing good this year and we missed a very important opportunity. My team has young and unexperienced players, but with this much hungry for success for the coming years I am nearly sure we will force the first 4 again and again :)

2- This week at last wordpress domain is back in my country, nearly for a year now we have been playing hacking games.

3- Last words are for the Oracle Critical Patch Update Announcement April 2008 which also includes two nasty vulnerabilities for APEX.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

References Used :

N/A

After all those years I still love to see I am learning lots of new stuff each day. This is what makes you fit and motivates to go deeper with Oracle I guess. In my opinion especially small things that you forget or even somehow you didn’t still read about makes the difference, here is today’s example :)

– until this morning I was also using column formating for large numbers like this
drop table tt purge;
create table tt (col1 number(18), col2 number(18));

insert into tt values (123456789123456789,123456789123456789);

select * from tt;

      COL1       COL2
———- ———-
1,2346E+17 1,2346E+17

col col1 for 999999999999999999.9999999999
select * from tt;

                          COL1       COL2
—————————— ———-
 123456789123456789.0000000000 1,2346E+17

– but from this morning an OTN forum thread reminded me SQL*Plus’s numwidth option :)
drop table t purge;
create table t (col1 number(18));

insert into t values (123456789123456789);

show numwidth
numwidth 10
select * from t;

      COL1
———-
1,2346E+17

set numwidth 18
select * from t;

              COL1
——————
123456789123456789

Thanks to Eduardo Legatti for this hint.

I saw Marco Gralike’s announcement so I also wanted to make a similar one. But this time Graham Wood will visit my company and we planned to be together with Wood for a half day on 21th of April.

Also on 22nd of April Jonathan Lewis will be in İstanbul, this time for his three days seminar. You can find the details of this announcement here. Below are my favorite links for summaries of Lewis’s previous seminars;

http://tonguc.wordpress.com/2007/03/01/another-great-undocumented-oracle-hint/

http://orajourn.blogspot.com/2007/11/day-1-with-jonathan-lewis-execution.html

http://orajourn.blogspot.com/2007/11/day-2-with-jonathan-lewis-writing.html

http://orajourn.blogspot.com/2007/11/day-3-understanding-and-assisting-cbo.html

It is always good to have an RDA, Statspack or AWR report to let a database to introduce itself to you :)

Of course usually you will be needing much more information than this, especially related to the developed applications on that database.

This morning one of my colleagues asked how he can get cumulative logins and sessions high water mark for an Oracle database, so below may help;

set linesize 1000
column name format a25
column num_cpus format a10
column db_block_size format a15
column requests_for_data format 999999999999999999

SELECT to_char(open_time,
               ‘DD-MON-YYYY HH24:MI:SS’) last_startup,
       SYSDATE - open_time days_passed_from_last_startup,
       vp1.VALUE num_cpus,
       vp2.VALUE db_block_size
  FROM v$thread, v$system_parameter vp1, v$system_parameter vp2
 WHERE instance = ‘bscsdb1′
   AND vp1.NAME = ‘cpu_count’
   AND vp2.NAME = ‘db_block_size’;

LAST_STARTUP         DAYS_PASSED_FROM_LAST_STARTUP NUM_CPUS   DB_BLOCK_SIZE
——————– —————————– ———- —————
25-MAR-2008 03:24:20                    ,459479167 64         8192

SELECT MAX(d.NAME) db_name,
       SUM(s.VALUE / (24 * 60 * 60 * (SYSDATE - t.open_time)) + 0.001) tps
  FROM v$database d, v$sysstat s, v$thread t
 WHERE s.NAME IN (’user commits’, ‘transaction rollbacks’);

DB_NAME          TPS
——— ———-
BSCS      151,125446

SELECT SUM(s.VALUE) requests_for_data
  FROM v$sysstat s
 WHERE s.NAME IN (’db block gets’, ‘consistent gets’);

  REQUESTS_FOR_DATA
——————-
        13120866284

SELECT SUM(s.VALUE) db_time FROM v$sysstat s WHERE s.NAME IN (’DB time’);

   DB_TIME
———-
 639913103

SELECT sessions_highwater FROM v$license;

SESSIONS_HIGHWATER
——————
              5581

SELECT NAME, VALUE FROM v$sysstat WHERE NAME LIKE ‘%logon%’;

NAME                           VALUE
————————- ———-
logons cumulative            1102893
logons current                  5434