Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Tracing Code with DBMS_MONITOR

Take a look at the follow-up to my DBMS_APPLICATION_INFO post over at ToadWorld. I discuss how to use DBMS_MONITOR to trace the specific sections of code you registered with DBMS_APPLICATION_INFO.  

Importing AWR repositories from cloned databases

#555555;">I sometimes want to import AWR repositories from cloned databases to compare performance differences between the databases which should have the same performance characteristics. Unfortunately the clones have the same DBID this importing their AWR repositories into a central AWR repository would be probelematic as I wouldn’t have a way to tell which database the data came from as the DBID is the same. So here is a procedure to change the DBID. THe procedure requires importing the data into the awr_stage user, changing the DBID, then moving the data into the AWR repository:
(see #2970a6;" href="http://gavinsoorma.com/2009/07/exporting-and-importing-awr-snapshot-data/">http://gavinsoorma.com/2009/07/exporting-and-importing-awr-snapshot-data/ for info on normal exporting and importing of AWR repositories)

#555555;">The script uses a  #2970a6;" href="https://sites.google.com/site/oraclemonitor/change-awr-dbid">awr_change_dbid.sql to defer the constraint on DBID, change the DBID and then put the constraint back

#555555;">   create tablespace AWR datafile '/home/oracle/oradata/AWR_01.dbf' size 200M;
   Drop Directory AWR_DMP;
   Create Directory AWR_DMP AS '/home/oracle/awr_dumps';
-- create staging user user
   drop user awr_stage cascade; 
   create user awr_stage
     identified by awr_stage
     default tablespace awr
     temporary tablespace temp;
   grant connect to awr_stage;
   alter user awr_stage quota unlimited on awr;
   alter user awr_stage temporary tablespace temp;
-- load data
   begin
     dbms_swrf_internal.awr_load(schname  => 'AWR_STAGE',
 				 dmpfile  => 'my_awr_dump', -- file w/o .dmp extension
                                 dmpdir   => 'AWR_DMP');
   end;
/
-- change dbid
   @awr_change_dbid
   commit;
-- move data
   def schema_name='AWR_STAGE'
   select  '&schema_name' from dual;
   variable schname varchar2(30);
   begin
     :schname := '&schema_name';
     dbms_swrf_internal.move_to_awr(schname => :schname);
   end;
/
-- verify the new DBID in repository
   col host_name for a30
   select distinct dbid,  db_name, instance_name, host_name from
   dba_hist_database_instance;

Top Linux processes and Oracle

Today due to application bug I had to check several time mapping between Linux PID processes and Oracle sessions. With small BASH script this is quite easy now
#!/bin/bash

# ps doesn't show real time CPU usage but average - skewed for long running processed
#TOP5=`ps xo pid --sort pcpu | tail -5 | xargs echo | sed -e 's/\s/,/g'`
TOP5=`top -b -d 1 -n 1 | head -12 | tail -5 | awk '{print $1;}' | xargs echo | sed -e 's/\s/,/g'`

sqlplus / as sysdba <<-EOF
set linesize 200 pagesize 999
select spid, s.username, s.program, sql_id, event from v\$session s, v\$process p where s.paddr = p.addr and spid in ($TOP5);
exit
EOF


regards,
Marcin 

Sun Coast Oracle User Group June 24th Sessions Materials

Thank you to all those who stayed back quite late that night for my two presentations. I hope you found it informative and useful.

As promised, you can download the session materials here.

As always, I will be honored to hear from you.

AWR thoughts

It’s been a week since my last posting - so I thought I’d better contribute something to the community before my name gets lost in the mists of time.

I don’t have an article ready for publication, but some extracts from an AWR report appeared on the OTN database forum a few days ago, and I’ve made a few comments on what we’ve been given so far (with a warning that I might not have time to follow up on any further feedback). I tried to write my comments in a way that modelled the way I scanned (or would have scanned) through the reporting – noting things that caught my attention, listing some of the guesses and assumptions I made as I went along.  I hope it gives some indication of a pattern of thinking when dealing with a previously unseen AWR report.

 

 

AWR thoughts

It’s been a week since my last posting - so I thought I’d better contribute something to the community before my name gets lost in the mists of time.

I don’t have an article ready for publication, but some extracts from an AWR report appeared on the OTN database forum a few days ago, and I’ve made a few comments on what we’ve been given so far (with a warning that I might not have time to follow up on any further feedback). I tried to write my comments in a way that modelled the way I scanned (or would have scanned) through the reporting – noting things that caught my attention, listing some of the guesses and assumptions I made as I went along.  I hope it gives some indication of a pattern of thinking when dealing with a previously unseen AWR report.

 

 

Automatic Diagnostics Repository (ADR) in Oracle Database 12c

There’s a neat little change to the Automatic Diagnostics Repository (ADR) in Oracle 12c. You can now track DDL operations and some of the messages that would have formerly gone to the alert log and trace files are now written to the debug log. This should thin out some of the crap from the alert log hopefully. Not surprisingly, ADRCI has had a minor tweak so you can report this stuff.

You can see what I wrote about it here:

Of course, the day-to-day usage remains the same, as discussed here:

Cheers

Tim…


Automatic Diagnostics Repository (ADR) in Oracle Database 12c was first posted on June 25, 2014 at 3:08 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Number one bottleneck in IT ?

#000000;">“Any improvement not made at the constraint is an illusion.” – Gene Kim paraphrasing “The Theory of Constraints”

What is the constraint in IT?

The constraints in IT are

  1. Provisioning environments for development
  2. Setting up test and QA environments
  3. Architecting development to facilitate easy changes in code
  4. Development speed
  5. Product management  input

Meaning, until the first constraint is eliminated it is pointless and even potentially counter productive to tune the following constraint.

The first constraint for most organizations to tackle is thus the speed and agility with which they can provision environments for development.

The above list comes from Gene Kim the author of The Phoenix Project. He  lays out these top constraints  in this interview Gene Kim interview.

In the interview Gene Kim talks about what causes the biggest delays in application development in IT.  He says, starting around minute 6:45

“I’ve been trained in the theory of constraints and one of the things I think is so powerful is the notion of the constraint in the value stream. What is so provocative about that notion is that any improvement not made at the constraint is an illusion. If you fix something before the constraint you end up with more work piled up in front of the constraint. If you fix something after the constraint you will always be starved for work.

In most transformations, if you look at what’s really impeding flow, the fast flow of features, from development to operations to the customer, it’s typically IT operations.

Operations can never deliver environments upon demand.

People have to wait months or quarters to get a test environment. When that happens terrible things happen. People actually horde environments. They invite people to their teams because the know they have reputation for having a cluster of test environments so people end up testing on environments that are years old which doesn’t actually achieve the goal.

One of the most powerful things that organizations can do is to enable development and testing to get environment they need when they need it.

After that it’s about test setup time and test run time one that is eliminated .

After that is eliminated it’s typically about architecture. How do we make changes that don’t require 15 other changes simultaneously. How do we create more looser couplings.

Then after that the constraint moves into development or product management.

It is a very technical cultural obstacle is just making available environments for people who need it whether it’s production development or tests.”

 MP900382632

 

Integrating PFCLScan and Creating SQL Reports

We were asked by a customer whether PFCLScan can generate SQL reports instead of the normal HTML, PDF, MS Word reports so that they could potentially scan all of the databases in their estate and then insert either high level....[Read More]

Posted by Pete On 25/06/14 At 09:41 AM