ClubDBA: March 2013

Tuesday, March 26, 2013

All Oracle Application server, Database books / study material / pdf / Documents

All Oracle books / study material / pdf / Documents at one place.

http://tahiti.oracle.com/

Monday, March 18, 2013

Oracle system statistic average by day

Oracle system statistic average by hour

select to_char (begin_interval_time, 'day' ) snap_time, avg(value) avg_value
from dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
group by to_char(begin_interval_time, 'day')
order by
decode(
to_char (begin_interval_time, 'day'),
'sunday', 1,
'monday', 2,
'tuesday', 3,
'wednesday', 4,
'thursday', 5,
'friday', 6,
'saturday', 7
);

Query uses dba_hist_sysstat view to show average values by day of week.

Friday, March 15, 2013

Oracle system statistic average by hour

select to_char (begin_interval_time, 'hh24' ) snap_time, avg(value) avg_value
from dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = '&stat_name'
group by to_char(begin_interval_time, 'hh24')
order by to_char (begin_interval_time, 'hh24');

Query uses dba_hist_sysstat view to show average values by hour or the day.

Thursday, March 14, 2013

Oracle Tuning Hierarchy

Saturday, March 9, 2013

Improving SQL Statement Tuning with Automatic SQL Tuning

This tutorial describes how to benefit from Automatic SQL Tuning to automatically tune your high loaded SQL statements.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/manage/ast/ast.htm

SQL usage & statistics in the shared pool

v$sqlarea contains statistics about SQL in the shared pool and its usage statistics

select * from
( select sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 21

Query shows SQL statement by their CPU usage.

CPU usage statistics (CPU Time)

CPU usage is important for response time analysis.
Response Time = Service Time + Wait Time.
Service Time = CPU Parse Time/CPU Recursive Time/CPU Other
If CPU usage shows large response time, the database should be tuned according to CPU usage.

Query show the CPU time utilization by the database since last startup of database.

CPU-Time

Select
name,
value
from v$sysstat
where upper (name) like '%CPU%';
CPU statistics from v$sysstat, database 9i R2

Thursday, March 7, 2013

Temporary space used by SQL call in current session (oracle 10g)

select
sql_text,
sid,
c.username,
machine,
tablespace,
extents,
blocks
from
sys.v_$sort_usage a,
sys.v_$sqlarea b,
sys.v_$session c
where
a.sqladdr = b.address and
a.sqlhash = b. hash_value and
a.session_addr = c.saddr
order by sid;

We can check, SQL call current session information and details. How much temporary space SQL call is using.

Temporary space used by connected sessions (oracle 10g)

select
tablespace_name,
current_users,
total_extents,
used_extents,
free_extents,
max_used_size,
max_sort_size
from
sys.v_$sort_segment
order by 1;

Temporary space storage issue like large disk sorts can cause out of space conditions.
Query shows that users are currently using space in temporary tablespace.

Storage Space consumption by user account (Oracle 10g)

Select
owner,
round((byte_count / 1024 /1024 ), 2) space_used_MB,
round(100 * (byte_count / tot_bytes) , 2) pct_of_database
from
(select
owner,
sum(bytes) as byte_count
from
sys.dba_segments
where
segment_type not in ('TEMPORARY', 'CACHE')
group by owner
order by 2 desc),
( select sum(bytes) as tot_bytes
from sys.dba_segments);

IF users account found with large amount of data, user should checked is there unused or unnecessary objects have not been left.

Temporary space used by connected sessions (oracle 10g)
Temporary space used by SQL call in current session (oracle 10g)

Wednesday, March 6, 2013

Truncate and Delete command

Truncate and Delete command in SQL Server

DELETE	TRUNCATE
DDL command	DML command
Used delete all or selected rows from a table based on WHERE clause.	Removes all rows from a table.
This is a logged operation for every row.	This is also a logged operation but in terms of deallocation of data pages.
Any row not violating a constraint can be Deleted.	Cannot TRUNCATE a table that has foreign key constraints.
Need to Commit or Rollback	Cannot be Rolled back.
Does not reset the identity column. Starts where it left from last.	Resets identity column to the default starting value.

Tuesday, March 5, 2013

Best study material for oracle 10g/11g/12c

OCP/OCA, oracle 10g/11g/12c studdy guide, online and download

http://www.oracle.com/pls/db102/homepage
http://www.oracle.com/pls/db112/homepage
http://docs.oracle.com/cd/E24628_01/index.htm

Oracle Wait activity depending on there wait class

select
wait_class,
total_waits,
round(100 * (total_waits / sum_waits), 2) pct_waits,
round((time_waited / 100), 2) time_waited_secs,
round(100 * (time_waited / sum_time), 2) pct_time
from
(select
wait_class,
total_waits,
time_waited
from
v$system_wait_class
where
wait_class != 'Idle'),
(select
sum(total_waits) sum_waits,
sum(time_waited) sum_time
from
v$system_wait_class
where
wait_class != 'Idle')
order by 5 desc;

Summary of waits depending on wait class.
10g wait class identifier makes the identification of wait behavior easier.
Wait events are grouped into different classes.

Calculate I/O Timing values for datafile

select f.file#,d.name,PHYRDS,PHYWRTS,READTIM/PHYRDS, WRITETIM/PHYWRTS
from v$filestat f, v$datafile d
where f.file#=d.file#
order by readtim/phyrds desc

Display the physical read physical writes and the time required, average, to complete single read or write.

Monday, March 4, 2013

Show physical I/O at the data file level

Check the physical I/O at the data file level
break on begin_interval_time skip 2
column begin_interval_time format a25
select
begin_interval_time, filename, phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot;

The AWR also provides the dba_hist_filestatxs table to track Disk I/O.

Sunday, March 3, 2013

New 11gR1 Background Processes

SQL SERVER – Last Date Time Updated for Any Table

Query provides details of when was the table last updated. Without WHERE Condition it will provide details of the entire database.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DatabaseName')
AND OBJECT_ID=OBJECT_ID('TableName')

Note: In WHERE class replace DatabaseName & TableName with your Database and Table name.

Saturday, March 2, 2013

Checking Installed version of Oracle

In normal situation, we can check the edition of Oracle 10/11g using SQLPlus. Just login using SQLPlus, and there will be a message on top of the SQL prompt, stating that the Oracle is either Standard or Enterprise.

The banner which is shown when yo connect to server, is of the client
installed on your local PC (the client PC), so that will not show the
correct version, though the client version and the server version are same
most of the time, but in rare cases these may be different.

The V$VERSION will give you the correct version and the architecture.

SELECT * from v$version;

So you must check v$version in all cases, where you will find the correct
version and architecture details.

ClubDBA