Bookmark and Share
Last Updates
Display: 1 - 10 of 10, Total Pages: 1
1
[Blog] Oracle SGA [Original] 2008-9-18 5:51pm

The main components of the SGA are the database buffer cache, the shared pool, the redo log buffer, the Java pool, and the optional large pool.

Read Full Article...

Section:Oracle DBA | Read:992 | Comment:0 | Rating:0%

[Blog] Locking Issues [Original] 2008-8-29 12:48am

If a major table or tables are locked unbeknownst to you, the database could slow down dramatically in very short order.

Read Full Article...

Section:Oracle DBA | Read:817 | Comment:0 | Rating:0%

[Blog] Supplemental Logging in a Streams Environment [Original] 2008-8-28 5:50pm

Supplemental logging places additional column data into a redo log whenever an UPDATE operation is performed. Such updates include piecewise updates to LOBs. The capture process captures this additional information and places it in LCRs.

Read Full Article...

Section:Oracle DBA | Read:2077 | Comment:0 | Rating:0%

[Blog] V$LOGMNR_CONTENTS [Original] 2008-8-19 11:47pm

LogMiner presents the information in the redo logs through the V$LOGMNR_CONTENTS fixed view.

Read Full Article...

Section:Oracle DBA | Read:969 | Comment:0 | Rating:0%

[Blog] Database Wait Statistics [Copied] 2008-8-6 12:46pm

1. Summary

High buffer cache hit ratios don't necessarily mean that the application is well tuned. All a high buffer cache hit ratio is telling you is that your physical reads are very small when compared to the total logical reads from the buffer cache. It's entirely possible for you to have a 99.0 buffer cache hit ratio and still have a database slowdown.

 

Read Full Article...

Section:Oracle DBA | Read:1175 | Comment:0 | Rating:0%

[Blog] SQL Trace and TKPROF [Copied] 2008-8-4 1:10pm

1. If your application has a lot of dynamically generated SQL, the SQL Trace utility is ideal for tuning the SQL statements.

2. SQL Trace will enable you to track the following variables:

* CPU and elapsed times
* Parsed and executed counts for each SQL statement
* Number of physical and logical reads
* Execution plan for all the SQL statements
* Library cache hit ratios

3. There are three parameters related to SQL Trace: Statistics_Level, Timed_Statistics, User_Dump_Dest

Timed_Statistics

To collect performance statistics such as CPU and execution time, set the value of the timed_statistics parameter to true in the init.ora file or SPFILE, or use the alter system set timed_statistics = true statement to turn timed statistics on instancewide. You can also do this at the session level by using the alter session statement as follows:

SQL> alter session set timed_statistics = true;
Session altered

4. Enabling SQL Trace

You can do at the instance level by using the alter session statement or the DBMS_SESSION package.

1) including the line sql_trace=true in your init.ora file or SPFILE or by using the alter system command to set sql_trace to true.

2) SQL> alter session set sql_trace=true; Session altered.

3) DBMS_SESSION.set_sql_trace


5. Interpreting the Trace Files with TKPROF

$ tkprof trace_file.trc trace.prf sys=no explain =y

TKPROF Command-Line Arguments

PARAMETER DESCRIPTION
==========================================================
Filename -- The input trace file produced by SQL Trace

Explain -- The explain plan for the SQL statements

Record -- Creates a SQL script with all the nonrecursive SQL statements

Waits -- Records a summary of wait events

Sorts Sorts the statements in descending order

Table -- The name of the tables into which the TKPROF utility temporarily puts the execution plans

Sys -- Enables and disables listing of SQL statements issued by SYS

Print -- Lists only a specified number of SQL statements instead of all statements

Insert -- Creates a script that stores the trace information in the database

6. Examining the Formatted Output File

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

Read Full Article...

Section:Oracle DBA | Read:1209 | Comment:0 | Rating:0%

[Blog] How to install Statspack? [Modified] 2008-8-4 11:49am
1. connect as SYS and run the installation scripts from the $ORACLE/HOME/rdbms/admin directory.

2. To install Statspack, you need to run three scripts: spcuser.sql, spctab.sql, and spcpkg.sql. The three scripts perform the following tasks:

Spcuser.sql creates the perfstat user.

Spctab.sql creates the necessary tables and other objects to store the statistics.

Spcpkg.sql creates the Statspack package.

3. You need to run the three scripts under different schemas. You must run the first script, spcusr.sql, under the SYS schema, and you must run the next two as the Statspack user perfstat.

Read Full Article...

Section:Oracle DBA | Read:640 | Comment:0 | Rating:0%

[Blog] Oracle DBA常用脚本(3) -- Generate a script to create database users [Copied] 2007-4-3 1:17pm

set pagesize 0
set escape on
spool create_user_ddl.sql
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY', ' by values ' ||
'''' || password || '''') || chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED & LOCKED', ' account lock password expire', null)
||';' from dba_users U, dba_ts_quotas Q

-- Comment this clause out to include system and default users

where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+);
set pagesize 100
set escape off

Read Full Article...

Section:Oracle DBA | Read:5798 | Comment:0 | Rating:0%

[Blog] Oracle DBA常用脚本(2) -- dump table DDL from the database [Original] 2007-3-29 4:09pm

###a script to dump a table DDL creation script for a schema provided as the parameter###
下面这个script用来产生一个能生成某个schema的所有objects DDL的script.

$> more extract_objects.sql
-- create_ddl.sql
-- Use this to generate the DDL to create objects
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
set long 2000
set longchunksize 200
set heading off
spool object_ddl.sql

select 'select dbms_metadata.get_ddl(' || '''' ||
OBJECT_TYPE || '''' || ',' || '''' || OBJECT_NAME ||
'''' || ',' || '''' || OWNER || '''' || ') from dual;'
FROM dba_objects
Where Owner = '&schema' and OBJECT_TYPE != 'TABLE PARTITION';

spool off

在sqlplus中运行:

SQL> @extract_objects.sql;
Enter value for schema: CHERRY
select dbms_metadata.get_ddl('TABLE','CITY','CHERRY') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0025132','CHERRY') from dual;
select dbms_metadata.get_ddl('TABLE','PHONE','CHERRY') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0025133','CHERRY') from dual;

结果会输出到文件object_ddl.sql,编辑object_ddl.sql,加入以下行:

set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
set long 2000
set longchunksize 200
set heading off
spool table_ddl.sql

在sqlplus中运行编辑后的object_ddl.sql,结果输出到table_ddl.sql,table_ddl.sql会有所有这个schema的table DDL和index DDL。

Read Full Article...

Section:Oracle DBA | Read:15368 | Comment:6 | Rating:0%

[Blog] Oracle DBA 常用脚本(1) : start and shutdown Oracle database [Original] 2007-3-27 6:31pm

start, shutdown and abort the Oracle database

##########start the database: dbstart.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry as sysdba
startup pfile=/rdbms/oracle/ora1022i/64/dbs/initOra1022a_cherry.ora
exit
0xff

##########shutdown the database: dbstop.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry as sysdba
shutdown immediate
exit
0xff

##########abort the database: dbabort.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry as sysdba
shutdown abort
exit
0xff

Running a sql script/truncate table

##########running sql scripts: dbinsert.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry
@insert_table.sql
exit
0xff

##########truncate table: dbtruncate.sh##########
#!/bin/ksh
sqlplus /nolog << oxff
connect cherry/cherry
truncate table test;
exit
0xff

Read Full Article...

Section:Oracle DBA | Read:14737 | Comment:4 | Rating:0%

1
Latest Comments
eozzrixrlo

on line..
- By admin, 2008-4-24 2:34am
rvaqlckrdu

free nud..
- By admin, 2008-4-24 2:33am
xndfjmwcpj

- By admin, 2008-4-5 1:02pm
vsjblsgatm

somali p..
- By admin, 2008-4-5 1:01pm
jknsdsmvyi

- By admin, 2008-3-12 1:48am
fkgyuchizk

tramadol..
- By admin, 2008-3-12 1:48am
wnniqpohsg

- By admin, 2008-4-24 2:32am
unxnmahoya

in phent..
- By admin, 2008-4-24 2:29am
bkgulrjbtt

- By admin, 2008-3-12 1:47am