配置Oracle Shared Server参数(oracle 10g)

1、DISPATCHERS参数
数据库启动时,启动的调度程序的数量
eg:
配置2个TCP/IP调度程序和3个IPC调度程序
DISPATCHERS = “(PRO=TCP)(DIS=2)(PRO=IPC)(DIS=3)”

使用ALTER命令修改DISPATCHERS参数
ALTER SYSTEM SET DISPATCHERS = “(PRO=TCP)(DIS=2)”

使用DISPATCHERS参数配置连接集储(connection pooling)
连接集储定义:自动断开空闲连接和使用空闲连接为连接请求提供服务
eg:
DISPATCHERS=”(PROTOCOL=tcp)(DISPATCHERS=1)(POOL=ON)(TICK=1)(CONNECTIONS=500)(SESSIONS=1000)”
解释:
POOL=ON 打开连接集储
TICK=1 一个连接在经过一个非活动性的10分钟增量后被看做空闲的
2、MAX_DISPATCHERS参数
ORACLE SHARED SERVER需要的最大调度程序数据

使用ALTER命令修改
ALTER SYSTEM SET MAX_DISPATCHERS=10

3、SHARED_SERVERS参数
ORACLE实例启动和保持最小shared server数量

使用ALTER命令修改
ALTER SYSTEM SET SHARED_SERVERS=10

4、SHARED_SERVER_SESSION参数
ORACLE实例预测shared server会话的总数量

使用ALTER命令修改
ALTER SYSTEM SET SHARED_SERVER_SESSION =10

5、MAX_SHARED_SERVERS参数
并发运行shared server的最大数量
视图V$SHARED_SERVER_MONITOR可以查看ORACLE实例启动以来的shared server数量

使用ALTER命令修改
ALTER SYSTEM SET MAX_SHARED_SERVERS=10

6、CIRCUITS参数
控制一个输入输出网络会话所预测的虚拟电路的总数量

使用ALTER命令修改
ALTER SYSTEM SET CIRCUITS=10

客户端sqlplus连接oracle的过程

如果我运行:sqlplus sys/change_on_install@test as sysdba

1. 查询sqlnet.ora看看名称的解析方式,发现是TNSNAME
2. 则查询tnsnames.ora文件,从里边找test的记录,并且找到主机名,端口和service_name
3. 如果listener进程没有问题的话,建立与listener进程的连接。
4. 根据不同的服务器模式如专用服务器模式或者共享服务器模式,listener采取接下去的动作。默认是专用服务器模式,没有问题的话客户端就连接上了数据库的server process。
5. 这时候网络连接已经建立,listener进程的历史使命也就完成了。

left join and right join

今天早上在drop table操作时,database 挂起,不能drop table。
查alert_sid.log,找到有用信息。

信息如下:

Sat Sep 15 21:04:58 2007
Thread 1 advanced to log sequence 18
Current log# 2 seq# 18 mem# 0: /u01/app/oracle/oradata/dg1/redo02.log
Mon Sep 17 03:05:06 2007
Thread 1 advanced to log sequence 19
Current log# 3 seq# 19 mem# 0: /u01/app/oracle/oradata/dg1/redo03.log
Tue Sep 18 00:57:46 2007
ORACLE Instance dg1 – Can not allocate log, archival required
Tue Sep 18 00:57:46 2007
ARCH: Connecting to console port…
Thread 1 cannot allocate new log, sequence 20
All online logs needed archiving
Current log# 3 seq# 19 mem# 0: /u01/app/oracle/oradata/dg1/redo03.log

不能正确归档!!!

    sys@DG1> archive log list
    DATABASE log mode Archive Mode
    Automatic archival Disable
    Archive destination /u01/app/oracle/product/9.2.0/dbs/arch
    Oldest online log SEQUENCE 18
    NEXT log SEQUENCE TO archive 20
    CURRENT log SEQUENCE 20

[oracle@dg1 dbs]$ ls
initdg1.ora initdw.ora init.ora lkDG1 lkOEMREP orapwdg1 orapwOEMREP spfiledg1.ora spfileOEMREP.ora
[oracle@dg1 dbs]$ pwd
/u01/app/oracle/product/9.2.0/dbs

竟然没有归档路径的arch文件夹

[oracle@dg1 dbs]$ mkdir arch(建一个arch文件夹)

    sys@DG1> ALTER system archive log START;

(让系统归档)

System altered.

查看alert_sid.log

Archiving is enabled
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=13
ARC0: Archival started
Tue Sep 18 08:45:48 2007
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=14
ARC1: Archival started
ARC1: Evaluating archive log 1 thread 1 sequence 17
Tue Sep 18 08:45:48 2007
ARC0: Evaluating archive log 1 thread 1 sequence 17
ARC0: Unable to archive log 1 thread 1 sequence 17
Log actively being archived by another process
ARC0: Evaluating archive log 2 thread 1 sequence 18
Tue Sep 18 08:45:48 2007
ARC1: Beginning to archive log 1 thread 1 sequence 17
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/u01/app/oracle/product/9.2.0/dbs/arch/1_17.dbf’
Tue Sep 18 08:45:48 2007
ARC0: Beginning to archive log 2 thread 1 sequence 18
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/u01/app/oracle/product/9.2.0/dbs/arch/1_18.dbf’
Tue Sep 18 08:46:09 2007
ARC1: Completed archiving log 1 thread 1 sequence 17
ARC1: Evaluating archive log 2 thread 1 sequence 18
ARC1: Unable to archive log 2 thread 1 sequence 18
Log actively being archived by another process
ARC1: Evaluating archive log 2 thread 1 sequence 18
ARC1: Unable to archive log 2 thread 1 sequence 18
Log actively being archived by another process
Tue Sep 18 08:46:09 2007
ARC0: Completed archiving log 2 thread 1 sequence 18
Tue Sep 18 08:46:10 2007
Thread 1 advanced to log sequence 20
Current log# 1 seq# 20 mem# 0: /u01/app/oracle/oradata/dg1/redo01.log
Tue Sep 18 08:46:10 2007
ARC1: Evaluating archive log 3 thread 1 sequence 19
ARC1: Beginning to archive log 3 thread 1 sequence 19
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/u01/app/oracle/product/9.2.0/dbs/arch/1_19.dbf’

    sys@DG1> archive log list
    DATABASE log mode Archive Mode
    Automatic archival Enabled
    Archive destination /u01/app/oracle/product/9.2.0/dbs/arch
    Oldest online log SEQUENCE 18
    NEXT log SEQUENCE TO archive 20
    CURRENT log SEQUENCE 20
    sys@DG1> SELECT * FROM v$log;
 
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
    ———- ———- ———- ———- ———- — —————- ————- ———
    1 1 20 104857600 1 NO CURRENT 3480245 18-SEP-07
    2 1 18 104857600 1 YES INACTIVE 2907378 15-SEP-07
    3 1 19 104857600 1 YES ACTIVE 3178665 17-SEP-07

oracle正确归档了!

oracle又可以正常操作了!

总结:alert_sid.log重要的没话说!
以前还遇到过,归档磁盘满了,数据库挂起的情况!
归档的相关操作:
startup mount;
alter database archivelog;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=D:oraclearchive1′;
alter system set LOG_ARCHIVE_DEST_2=’LOCATION=D:oraclearchive2′;
alter system set log_archive_format=’arch%s.arc’ scope=spfile;
alter system set log_archive_start=true scope=spfile;
alter system archive log start;
alter database open;

sys用户及以sysdba连接的用户不能使用read only和serializable事务的求证

session1中的操作:

    sys@DG1> CREATE TABLE t ( x INT );
 
    TABLE created.
 
    sys@DG1> INSERT INTO t VALUES ( 1 );
 
    1 ROW created.
 
    sys@DG1> EXEC dbms_stats.gather_table_stats( USER, ‘T’ );
 
    PL/SQL PROCEDURE successfully completed.
 
    sys@DG1> SELECT * FROM t;
 
    X
    ———-
    1
 
    sys@DG1> ALTER SESSION SET isolation_level=serializable;
 
    SESSION altered.
 
    sys@DG1> SET autotrace ON statistics
    sys@DG1> SELECT * FROM t;
 
    X
    ———-
    1
    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    3 consistent gets
    0 physical reads
    0 redo SIZE
    372 bytes sent via SQL*Net TO client
    503 bytes received via SQL*Net FROM client
    2 SQL*Net roundtrips TO/FROM client
    0 sorts (memory)
    0 sorts (disk)
    1 ROWS processed
    ———————————
 
    session2中的操作:(同一个用户登录)
 
    sys@DG1> BEGIN
    2 FOR i IN 1..10000
    3 loop
    4 UPDATE t SET x=i+1;
    5 commit;
    6 END loop;
    7 END;
    8 /
 
    PL/SQL PROCEDURE successfully completed.
 
    在session1中查询I/O的变化:
 
    sys@DG1> SELECT * FROM t;
 
    X
    ———-
    10001
    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    3 consistent gets
    0 physical reads
    0 redo SIZE
    374 bytes sent via SQL*Net TO client
    503 bytes received via SQL*Net FROM client
    2 SQL*Net roundtrips TO/FROM client
    0 sorts (memory)
    0 sorts (disk)
    1 ROWS processed

I/O的值没有变,x的值变了,说明用了serializable根本没有起作用。

Bitmap index细节,导致应用系统缓慢

今天看了Tom关于使用bitmap index问题,只是在一个字段上建了一个bitmap index,导致系统停顿,但这个字段看似建bitmap index没错呀!(一般我们会在基数值低的字段建bitmap index)
下面是测试:
打开第一个sqlplus

SQL> CREATE TABLE t(flag varchar2(1));
 
表已创建。
 
SQL> CREATE bitmap INDEX f_index ON t(flag);
 
索引已创建。
 
SQL> INSERT INTO t VALUES('y');
打开第二个sqlplus(不关闭第一个)
SQL> INSERT INTO t VALUES('y');
一直停在这里,不能插入成功!!!

*****要等到第一个sqlplus中的插入语句commit以后,第二个sqlplus中插入语句才能插入成功*****

总结:对bitmap不全面的了解,一个小小的细节就可能导致系统的缓慢!!!