o get historical information on what SQL statements are incurring the most disk reads

SELECT * FROM ( SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,

sum(disk_reads_total) disk_reads_total, sum(executions_delta) execs_delta, sum(executions_total) execs_total

FROM dba_hist_sqlstat GROUP BY sql_id ORDER BY 2 desc)

WHERE rownum <= 5;

top five recent SQL statements that performed the highest disk I/O

SELECT sql_text, disk_reads FROM (SELECT sql_text, buffer_gets, disk_reads, sorts,

cpu_time/1000000 cpu, rows_processed, elapsed_time FROM v$sqlstats ORDER BY disk_reads DESC)

WHERE rownum <= 5;

If you join V$SQLSTATS to V$SQL, you can see information for particular users. See the following example:

SELECT schema, sql_text, disk_reads, round(cpu,2)

FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,

t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time

FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)

WHERE parsing_schema_name = 'SCOTT' ORDER BY disk_reads DESC)

WHERE rownum <= 5;

The V$SQL_MONITOR view contains currently running SQL statements, as well as recently run SQL statements.

If you wanted to see the top five most CPU-consuming queries in your database, you could issue the following query:

SELECT * FROM ( SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds F

ROM v$sql_monitor ORDER BY cpu_time desc)

WHERE rownum <= 5;

Identifying Who Is Using the Temporary Tablespace

select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr

and o.sqladdr = h.address (+)

and o.tablespace = t.tablespace_name

order by s.sid;

mapping an operating system process back to a database process

ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head

SELECT

'USERNAME : ' || s.username || CHR(10) ||

'SCHEMA : ' || s.schemaname || CHR(10) ||

'OSUSER : ' || s.osuser || CHR(10) ||

'PROGRAM : ' || s.program || CHR(10) ||

'SPID : ' || p.spid || CHR(10) ||

'SID : ' || s.sid || CHR(10) ||

'SERIAL# : ' || s.serial# || CHR(10) ||

'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||

'MACHINE : ' || s.machine || CHR(10) ||

'TYPE : ' || s.type || CHR(10) ||

'TERMINAL : ' || s.terminal || CHR(10) ||

'SQL ID : ' || q.sql_id || CHR(10) ||

'SQL TEXT : ' || q.sql_text

FROM v$session s

,v$process p

,v$sql q

WHERE s.paddr = p.addr

AND p.spid = '&PID_FROM_OS'

AND s.sql_id = q.sql_id(+);

In this example, since the process is running a SQL statement, further details about the query can be extracted by generating an execution plan:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));

In case you need to KILL the process:

alter system kill session '1177,38583';

To view objects that are waiting for I/O resources

SELECT

object_name

,object_type

,owner

FROM v$session a

,dba_objects b

WHERE a.event LIKE 'db file%read'

AND b.data_object_id = a.row_wait_obj#

To determine which sessions are currently waiting for I/O resources, query V$SESSION

SELECT

username

,program

,machine

,sql_id

FROM v$session

WHERE event LIKE 'db file%read';

Which SQL are using excesive i/O

SELECT *

FROM

(SELECT parsing_schema_name, direct_writes, SUBSTR(sql_text,1,75), disk_reads

FROM v$sql

ORDER BY disk_reads DESC)

WHERE rownum < 20;

Gathering Statistics

Gathering stats doesn't lock the table/index, only locks the object statistics.

As of 11g, the gathering of object statistics is integrated into the automated tasks scheduled with the Scheduler:

SELECT task_name, status

FROM dba_autotask_task

WHERE client_name = 'auto optimizer stats collection';

Statistics history ,Display current value:

SELECT dbms_stats.get_stats_history_retention() AS retention from DUAL;

Set retention period to 14 days:

dbms_stats.alter_stats_history_retention(retention => 14);

When statistics were modified for a given table:

SELECT stats_update_time FROM dba_tab_stats_history

WHERE owner = 'SYS' and table_name = 'TAB$';

Restore object statistics of the schema SH to the values that were in use 1 day ago:

dbms_stats.restore_schema_stats(ownname => 'SH',as_of_timestamp => systimestamp – 1,force => TRUE)

dbms_stats log information about their execution:

SELECT operation, start_time,2 (end_time-start_time) DAY(1) TO SECOND(0) AS duration

FROM dba_optstat_operations ORDER BY start_time DESC;

Beautifying SqlPlus

SET LINESIZE 200

SET PAGESIZE 30

SET PAUSE ON

SET PAUSE 'MUCH HARDER'

COLUMN C1 FORMAT A20

COLUMN C2 NOPRINT

define_editor='vi'

steps to convert a RAC database to single instance

1) Stop database using srvctl

srvctl stop database -d prod

2) Remove the database entry from crs

bash-3.00$ srvctl remove instance -d prod -i prod1

Remove instance prod1 from the database prod? (y/[n]) y

bash-3.00$ srvctl remove instance -d prod -i prod2

Remove instance prod2 from the database prod? (y/[n]) y

bash-3.00$ srvctl remove database -d prod

Remove the database prod? (y/[n]) y

bash-3.00$

3) Start the database on first instance

SQL> startup

ORACLE instance started.

Total System Global Area 838860800 bytes

Fixed Size 1222168 bytes

Variable Size 188746216 bytes

Database Buffers 645922816 bytes

Redo Buffers 2969600 bytes

Database mounted.

Database opened.

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> alter system set cluster_database_instances=1 scope=spfile;

System altered.

SQL> alter database disable thread 2;

Database altered.

4) Delete the unwanted thread and redo logfiles

SQL> select thread#, group# from v$log;

THREAD# GROUP#

---------- ----------

1 1

1 2

1 3

2 4

2 5

2 6

6 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

5) Drop the unwanted undo tablespace

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

6) Create pfile from spfile

SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

7) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

Dedicated vs Shared Servers

I'll just summarise, because I can see some rather iffy advice being offered in this thread.

If you are running in 9i or above, there are two parameters (not one) which govern whether or not you are capable of running in shared server mode.

DISPATCHERS governs whether a job dispatcher runs. You have to have at least one of them configured before shared server is possible. However, the mere fact that a dispatcher runs does NOT mean your database is running in shared server mode. There also have to be shared server processes capable of handling the work dispatched by the dispatcher(s), and those are configured with the SHARED_SERVERS parameter. If that's set to any number greater than 1, you have shared server processes running on your instance. But that STILL doesn't mean you're running in shared server mode! If you have SHARED_SERVERS=57 and no dispatcher, you simply have 57 processes sitting around doing nothing whatsoever (and incapable of doing useful work!)

In short, you have to have DISPATCHERS and SHARED_SERVERS set.

Note, for example, that 10g configures a single dispatcher for all databases by default (if they're created with DBCA and you don't get in there to stop it happeneing), but it does NOT configure SHARED_SERVERS, so by default a 10g database does not run in shared server mode.

The other thing I'd clarify is that a database doesn't really run in shared server mode anyway! The fact that your instance has a dispatcher and shared server processes running doesn't necessarily mean your users will end up connected to the dispatcher and having shared server processes handling their job requests. They will by default, but if the tnsnames.ora they use to connect (or its centralised equivalent) contains the line SERVER=DEDICATED, then they will get to use dedicated server processes, no matter what the dispatcher or shared server processes might think about it!

With dispatchers and shared server processes configured, in other words, an instance can "support shared server connection requests". That's rather different than "running in shared server mode". The distinction is important because privileged actions (startup, shutdown, backup and recover commands) cannot be processed by a shared server process, so it's important for an instance that is configured for normal users to use shared server processes to still support the connection to dedicated server processes by suitably credentialled users.

If a user does end up connected to a shared server process, there is usually a performance penalty to pay compared to using a dedicated server process. A user submits a query and instead of it being immediately processed by a server process, it gets submitted to a dispatcher ...which promptly sticks it on a job queue! You then have to wait for a shared server process to become free and decide to pick your job off the queue. That's inevitably slower than doing it the dedicated way.

People use shared server as the first line of scaling up their databases... and you're right that it primarily depends on the number of users connected to the server concurrently. In dedicated server mode, a new connection means a new process gets spawned (or a new thread on Windows) and a new connection socket is opened. Servers can only handle so many connection sockets, processes or threads before they start to keel over under the strain. Shared server, as the name suggest, means that new connections do not cause new server processes to be spawned. So 300 users can be processed with, maybe, 30 or 40 processes in total. If your box would normally keel over handling 300 dedicated connections, then clearly with that sort of sharing ratio, you'd be able to scale to nearer 3000 users before it starts wilting by using shared processes.

But it's also a bit subtler than that: a data warehouse would be daft to implement shared server, even if it did have 300+ concurrent users. That's because the users of such systems typically run queries that run for hours... and a shared process that is nabbed to perform one job for hours on end isn't really a shared process any more, is it?! So the rule of thumb as to when to implement shared server is yes, (a) when your concurrent user count starts reaching levels that your server just doesn't seem able to sustain any more AND (b) when you can be sure that the users tend to issue short, sharp queries -say, about 3 seconds or so to process, max.

Again, there are mixed states to get through, too. You might have lots of OLTP-type sub-3-second transactions on the same database on which one or two users regularly run big reports. In that case, you make sure the reporters have a tnsnames.ora that says SERVER=DEDICATED and the OLTP-type people use one that has SERVER=SHARED in it; configure the DISPATCHERS and SHARED_SERVER parameters for the instance and then those that can benefit from shared servers can do so and those that wouldn't won't be stealing shared processes from those that can!

The alternative approach for those with more cash is to go and buy better server hardware that can cope with the user community numbers! Shared Server configuration, however, comes free. You pays your money and you takes your choices!

example:

alter system set max_shared_servers=100 scope=both;

alter system set max_dispatchers=150 scope=both;

alter system set dispatchers='(protocol=TCP) (SERVICE=gendb) (disp=5)' scope=both;

alter system set shared_servers=40 scope=both;

recall to amend the tnsnames.ora file on the client: (SERVER=SHARED)