I have come across of many requests on oracle forums and here and there on how to analyze statspack report and analyze its data to reach the bottle neck of performance issues.
Now, there is a good news, Jonathan Lewis is going to discuss in-depths about 1 statspack report and how to analyze statspack data as set of 10 articles on his blog.
http://jonathanlewis.wordpress.com/
Happy reading,
Jaffar
11.30.2006
11.29.2006
New Job - New Responsibilities.
Its been exactly a month in my new job, how times goes!!!!!.
Previously, I have worked with Saudi Frasi Bank as Sr. Oracle DBA for 2.5 years. Now, I am working with INTRACOM MIDDLE EAST as Sr. Oracle DBA for ITC Project.
Intracom a leading company in Greece got 2 years project with ITC. ITC provides broadband, fiber optics and wireless techologies to various companies, banks and individual customers.
For me its a change working with telecom company having past 5 years of experience in Banking.
During my tenure in the bank, I have carried out many advanced and challnging jobs. It was really a great and unforgatable experience in the bank. All my colleages and my immediate boss were really very good and very cooperative. Despite the fact that I was a consultant, I treated as their employe and bank also provides me couple of Oracle university trainings courses that really helped me to pass my 10g upgrade exam and also the courses on 10gAS was very helpful to understand the technology about Oracle Application Server
Following are few tasks that I had done in the bank with the help of my colleagues:
1. Physical standby database to core banking database.
2.Migrate datawarehouse 1.7TB database from 8i to 9i on AIX, later AIX to HP-Superdom.
3.Complete datawarehouse database restore and recovery.
4. Disaster recovery site for Datawarehouse database.
5.Tuned trading local share market database to achieve heights number of tranaction per second.
Presently, I am responsible for few of known database/applications used in the telecommunications, like, CARMER, REMEDY, EHEALTH and etc.
Futuer plans are to implement heigh availability solutions for applications and database (RAC).
I am also planning to do RAC course in coming months.
Thats all now folks,
Cheers,
Jaffar
Previously, I have worked with Saudi Frasi Bank as Sr. Oracle DBA for 2.5 years. Now, I am working with INTRACOM MIDDLE EAST as Sr. Oracle DBA for ITC Project.
Intracom a leading company in Greece got 2 years project with ITC. ITC provides broadband, fiber optics and wireless techologies to various companies, banks and individual customers.
For me its a change working with telecom company having past 5 years of experience in Banking.
During my tenure in the bank, I have carried out many advanced and challnging jobs. It was really a great and unforgatable experience in the bank. All my colleages and my immediate boss were really very good and very cooperative. Despite the fact that I was a consultant, I treated as their employe and bank also provides me couple of Oracle university trainings courses that really helped me to pass my 10g upgrade exam and also the courses on 10gAS was very helpful to understand the technology about Oracle Application Server
Following are few tasks that I had done in the bank with the help of my colleagues:
1. Physical standby database to core banking database.
2.Migrate datawarehouse 1.7TB database from 8i to 9i on AIX, later AIX to HP-Superdom.
3.Complete datawarehouse database restore and recovery.
4. Disaster recovery site for Datawarehouse database.
5.Tuned trading local share market database to achieve heights number of tranaction per second.
Presently, I am responsible for few of known database/applications used in the telecommunications, like, CARMER, REMEDY, EHEALTH and etc.
Futuer plans are to implement heigh availability solutions for applications and database (RAC).
I am also planning to do RAC course in coming months.
Thats all now folks,
Cheers,
Jaffar
11.25.2006
String of Disasters - a human error.
I was reading Alex Gorbachev's article on his blog, 'Which Risks Are You Protected From?' , http://www.pythian.com/blogs/author/alex/, in which he had discussed how human errors are underestimated which can leadto potential cuase.
While reading the article, I recalled my hardest time in one of my previous organization where I had faced the music of disaster and spent 4 days to bring the database back.
In my one of previous company, where the start-of-art technology being used, in terms of systems hardware and software, I came across a string of disasters due to a human error.
First experience of disaster recovery was for a datawarehouse database of 2TB. Due a human error, one of the disk on UNIX OS, including its mirror disk got corrupt and unfortunately the filesystem that contains all the redo groups and their members were placed on the corrupted disk.
Since, we lost all the redo groups and its members, we only left the an option of full database restoreand recovery and database open with resetlogs.
we use to backup the complete database on every week-end and backup archived logs at every 4 hrs. The database generates around 100 archived logs a day.
Disaster occured just before the next backup schedule.
When restore was started we have faced other problems with the backup utility which we were using.
However, complete db was restored in 3.5 days of time, and recovery took good amount of time as oneweek archived logs were applying. Finally, at the end of 4th day, database was back online available to the users.
Different kind of human errors happend which cause two more times to restore other databases as well.No matter, how good resource (hardware) you have, there is always feaure of human errors.
For me it was a good learning experience to handle the restore and recovery very first time in the production and this disaster cleared many of my backup/recovery doubts.
I felt, sometime, people learn by making mistakes.
Jaffar
While reading the article, I recalled my hardest time in one of my previous organization where I had faced the music of disaster and spent 4 days to bring the database back.
In my one of previous company, where the start-of-art technology being used, in terms of systems hardware and software, I came across a string of disasters due to a human error.
First experience of disaster recovery was for a datawarehouse database of 2TB. Due a human error, one of the disk on UNIX OS, including its mirror disk got corrupt and unfortunately the filesystem that contains all the redo groups and their members were placed on the corrupted disk.
Since, we lost all the redo groups and its members, we only left the an option of full database restoreand recovery and database open with resetlogs.
we use to backup the complete database on every week-end and backup archived logs at every 4 hrs. The database generates around 100 archived logs a day.
Disaster occured just before the next backup schedule.
When restore was started we have faced other problems with the backup utility which we were using.
However, complete db was restored in 3.5 days of time, and recovery took good amount of time as oneweek archived logs were applying. Finally, at the end of 4th day, database was back online available to the users.
Different kind of human errors happend which cause two more times to restore other databases as well.No matter, how good resource (hardware) you have, there is always feaure of human errors.
For me it was a good learning experience to handle the restore and recovery very first time in the production and this disaster cleared many of my backup/recovery doubts.
I felt, sometime, people learn by making mistakes.
Jaffar
How Oracle 10g makes life easier in a day to day administration. (Part I)
Although, Oracle10g is around past couple of years, we have just started migrating our databases to 10g.No question that Oracle 10g comes with few great features of Performance tuning.I have come across of few of new dictionary and dynamic views of Oracle 10g,which are useful in day by day administration.Prior to 10g, to get this information, you required a little bit work, but, in 10g, its simply selectingthese views/dictionary tables.
Please note that you need performance pack and diagnostic licences from Oracle to use these features and they are available only in Oracle Enterprise Edition.
OS Statistics
Prior to 10g, to know the OS level resource consumption, one need to login into the server and use OS levelutilities, like sar, top,vmstat to know the server resource usage.Now in 10g, just viewing v$osstat view can provide some useful information about OS resource consuption status.
v$osstat - view captures machine level information in the database. Its a good starting point to
analyze if there are hardware level resource issues.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2010.htm#sthref3811
STAT_NAME VALUE
------------------------------------- ----------
NUM_CPUS 4 Number of CPUs / processors on the DB Server
IDLE_TIME 609528 (value measured by hundredths of a second)
BUSY_TIME 85007
USER_TIME 62146
SYS_TIME 22861
IOWAIT_TIME 0
AVG_IDLE_TIME 152332
AVG_BUSY_TIME 21205
AVG_USER_TIME 15491
AVG_SYS_TIME 5671
AVG_IOWAIT_TIME 0
OS_CPU_WAIT_TIME 5600
RSRC_MGR_CPU_WAIT_TIME 0
LOAD .29296875
PHYSICAL_MEMORY_BYTES 1.7095E+10
VM_IN_BYTES 9576448
VM_OUT_BYTES 0
DBA_HIST_OSSTAT displays historical operating system statistics.
DBA_HIST_OSSTAT_NAME displays the names of the operating system statistics.
Tablespace Usage Statistics
In earlier version, it required to join few dictionary views and some computationto know the used percent of any tablespace.10g, makes life easier by providing all them in a view, dba_tablespace_usage_metrics. (for me, I dont get the real values in this view.)
select * from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 55448 4194302 1.32198397
SYSTEM 79448 4194302 1.89418883
TEMP 0 4194302 0
TEMPORARY 256 256000 .1
UNDOTBS1 31520 49280 63.961039
USERS 48 640 7.5
DBA_HIST_TBSPC_SPACE_USAGE - This dictionary table contains the history information about tablespace usage metrics, each row contains 1 hr historical information.
desc dba_hist_tbspc_space_usage;
Name Null? Type
----------------------------------------------------------------- -------- -------------
SNAP_ID NUMBER
DBID NOT NULL NUMBER
TABLESPACE_ID NUMBER
TABLESPACE_SIZE NUMBER
TABLESPACE_MAXSIZE NUMBER
TABLESPACE_USEDSIZE NUMBER
RTIME VARCHAR2(25)
select tablespace_size,tablespace_usedsize,rtime
from dba_hist_tbspc_space_usage
where tablespace_id = 2
order by rtime
TABLESPACE_SIZE TABLESPACE_USEDSIZE RTIME
76800 55360 11/25/2006 08:00:57
76800 55368 11/25/2006 09:00:08
76800 55392 11/25/2006 10:00:23
76800 55424 11/25/2006 11:00:35
76800 55448 11/25/2006 12:00:35
76800 55488 11/25/2006 13:00:47
If you want to query the history (usage of any partitcular tablespace),you need to play around with RTIME column.
DB Startuptime log
As per my knowledge, prior to 10g, I don't see any dictionary view or table keeps the history of database startup time and version.
10g, provides, DBA_HIST_DATABASE_INSTANCE which keeps the log about database startup time and the version.
SELECT instance_name,startup_time,version
FROM dba_hist_database_instance;
INSTANCE_NAME STARTUP_TIME VERSION
---------------- -------------------------------- -------
ORCL 07-OCT-06 16.29.29.000 10.2.0.1.0
ORCL 09-OCT-06 13.20.05.000 10.2.0.1.0
ORCL 09-OCT-06 13.38.05.000 10.2.0.1.0
ORCL 09-OCT-06 20.57.31.000 10.2.0.1.0
In my next post, I will be discussing few of other useful dictionary and dynamic views of Oracle10gR2.
Please note that you need performance pack and diagnostic licences from Oracle to use these features and they are available only in Oracle Enterprise Edition.
OS Statistics
Prior to 10g, to know the OS level resource consumption, one need to login into the server and use OS levelutilities, like sar, top,vmstat to know the server resource usage.Now in 10g, just viewing v$osstat view can provide some useful information about OS resource consuption status.
v$osstat - view captures machine level information in the database. Its a good starting point to
analyze if there are hardware level resource issues.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2010.htm#sthref3811
STAT_NAME VALUE
------------------------------------- ----------
NUM_CPUS 4 Number of CPUs / processors on the DB Server
IDLE_TIME 609528 (value measured by hundredths of a second)
BUSY_TIME 85007
USER_TIME 62146
SYS_TIME 22861
IOWAIT_TIME 0
AVG_IDLE_TIME 152332
AVG_BUSY_TIME 21205
AVG_USER_TIME 15491
AVG_SYS_TIME 5671
AVG_IOWAIT_TIME 0
OS_CPU_WAIT_TIME 5600
RSRC_MGR_CPU_WAIT_TIME 0
LOAD .29296875
PHYSICAL_MEMORY_BYTES 1.7095E+10
VM_IN_BYTES 9576448
VM_OUT_BYTES 0
DBA_HIST_OSSTAT displays historical operating system statistics.
DBA_HIST_OSSTAT_NAME displays the names of the operating system statistics.
Tablespace Usage Statistics
In earlier version, it required to join few dictionary views and some computationto know the used percent of any tablespace.10g, makes life easier by providing all them in a view, dba_tablespace_usage_metrics. (for me, I dont get the real values in this view.)
select * from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 55448 4194302 1.32198397
SYSTEM 79448 4194302 1.89418883
TEMP 0 4194302 0
TEMPORARY 256 256000 .1
UNDOTBS1 31520 49280 63.961039
USERS 48 640 7.5
DBA_HIST_TBSPC_SPACE_USAGE - This dictionary table contains the history information about tablespace usage metrics, each row contains 1 hr historical information.
desc dba_hist_tbspc_space_usage;
Name Null? Type
----------------------------------------------------------------- -------- -------------
SNAP_ID NUMBER
DBID NOT NULL NUMBER
TABLESPACE_ID NUMBER
TABLESPACE_SIZE NUMBER
TABLESPACE_MAXSIZE NUMBER
TABLESPACE_USEDSIZE NUMBER
RTIME VARCHAR2(25)
select tablespace_size,tablespace_usedsize,rtime
from dba_hist_tbspc_space_usage
where tablespace_id = 2
order by rtime
TABLESPACE_SIZE TABLESPACE_USEDSIZE RTIME
76800 55360 11/25/2006 08:00:57
76800 55368 11/25/2006 09:00:08
76800 55392 11/25/2006 10:00:23
76800 55424 11/25/2006 11:00:35
76800 55448 11/25/2006 12:00:35
76800 55488 11/25/2006 13:00:47
If you want to query the history (usage of any partitcular tablespace),you need to play around with RTIME column.
DB Startuptime log
As per my knowledge, prior to 10g, I don't see any dictionary view or table keeps the history of database startup time and version.
10g, provides, DBA_HIST_DATABASE_INSTANCE which keeps the log about database startup time and the version.
SELECT instance_name,startup_time,version
FROM dba_hist_database_instance;
INSTANCE_NAME STARTUP_TIME VERSION
---------------- -------------------------------- -------
ORCL 07-OCT-06 16.29.29.000 10.2.0.1.0
ORCL 09-OCT-06 13.20.05.000 10.2.0.1.0
ORCL 09-OCT-06 13.38.05.000 10.2.0.1.0
ORCL 09-OCT-06 20.57.31.000 10.2.0.1.0
In my next post, I will be discussing few of other useful dictionary and dynamic views of Oracle10gR2.
11.20.2006
Oracle DBA Toolbar
Download 'Oracle DBA Toolbar' from OTN.
http://www.oracle.com/technology/toolbar/install/index.html
The Oracle DBA Toolbar (available for Internet Explorer only at the moment - yeah, yeah, we know; a Mozilla/Firefox version is forthcoming) gives you one-click access to key DBA resources on OTN.
http://www.oracle.com/technology/toolbar/install/index.html
The Oracle DBA Toolbar (available for Internet Explorer only at the moment - yeah, yeah, we know; a Mozilla/Firefox version is forthcoming) gives you one-click access to key DBA resources on OTN.
11.14.2006
The Power of ASH (Active Session History)
Oracle 10g, brings many new features through which one can easily tune the bad sqls or also can diagnose the database performance issues.
Using database metrics, active session history and time model views.
Following query fetchs top sqls spent more on cpu/wait/io. (Thanks to Kyle Hailey for this script):
select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
bqts5m5y267ct 0 0 20 20
4gd6b1r53yt88 0 16 1 17
35rqnp0hn3p3j 0 13 0 13
3shtm7x3a54qu 0 0 8 8
0hf43mhpx086p 0 0 4 4
Use any of v$sql to get the sql_text for one of the above sql_id.
SELECT sql_text FROM v$sqlarea WHERE sql_id = 'bqts5m5y267ct';
dbms_xplan.display_awr can be used to extract the sql plan for this sql_id.
SELECT * FROM table(dbms_xplan.display_awr('bqts5m5y267ct');
The above scinario was done in Oracle 10g Re.2 on SunSolaris version 10.
Using database metrics, active session history and time model views.
Following query fetchs top sqls spent more on cpu/wait/io. (Thanks to Kyle Hailey for this script):
select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
bqts5m5y267ct 0 0 20 20
4gd6b1r53yt88 0 16 1 17
35rqnp0hn3p3j 0 13 0 13
3shtm7x3a54qu 0 0 8 8
0hf43mhpx086p 0 0 4 4
Use any of v$sql to get the sql_text for one of the above sql_id.
SELECT sql_text FROM v$sqlarea WHERE sql_id = 'bqts5m5y267ct';
dbms_xplan.display_awr can be used to extract the sql plan for this sql_id.
SELECT * FROM table(dbms_xplan.display_awr('bqts5m5y267ct');
The above scinario was done in Oracle 10g Re.2 on SunSolaris version 10.
11.05.2006
Jonathan Blog.
One of the well known Oracle expert, author and presenter, Mr. Jonathan Lewis has started blogging on oracle technology.
Although, he was regularly updating his blog like site, but, its nice to see him blogging everyday at http://jonathanlewis.wordpress.com/.
Great job Jonathan and hats off to you.
Jaffar
Although, he was regularly updating his blog like site, but, its nice to see him blogging everyday at http://jonathanlewis.wordpress.com/.
Great job Jonathan and hats off to you.
Jaffar
Subscribe to:
Posts (Atom)