Note: Instant client 11g requires Linux-Native Asynchronous I/O Access Library (libaio)
Created Monday 6/8/2007
This document describes oracle configuration within the context of Fedora Core. Installing 3rd party apps like TOra are also discussed.
Note: Instant client 11g requires Linux-Native Asynchronous I/O Access Library (libaio)
The 10g instant client installation is pretty instantaneous. Download either the rpm or tarballs. The Instant Client distribution is broken up into several discrete packages. At the very least, the following must be installed:
After the mandatory packages listed above, the following can optionally be installedl:
After unpacking the tarballs, the Oracle Instant Client lib directory must contain symlinks to the version of the Client Shell and OCI libraries. These must be available as libclntsh.so and libocci.so. For example, if Instant Client 10.1 is installed, then symlinks to libclntsh.so.10.1 and libocci.so.10.1 should be created, as follows:
bash $ cd $ORACLE_HOME/lib bash $ ln -s libclntsh.so.10.1 libclntsh.so bash $ ln -s libocci.so.10.1 libocci.so
To get tora working under 10g instance client do the following:
TOra is distributed from sourceforge as a tarball, with the standard configure and make(1) machinery. The configure script for TOra is a little tricky. Basically for Fedora with gnome, the configure script requires a few little tweeks via some command line switches. The QT development headers must be installed if compiling TOra from source.
Warning: The development headers for the prerequisite applications must be installed along with the prerequisite apps
The following packages (with development headers) must be installed prior to running the configure for TOra. These are valid prerequisites as at TOra 2.0:
The above prerequisites can be installed via yum with:
bash # yum install qt qt-devel gcc-c++ qscintilla.i386 qscintilla-devel.i386
The configure options estentially come down to the following (if your compiling with KDE, then the qt options may not be relevant):
bash $ ./configure --without-kde --with-qt-dir=/usr/lib64/qt4 \
--with-oracle-includes=$ORACLE_HOME/sdk/include/ \
--with-oci-version=11G \
--enable-plugin
After the configure succeeds, then it's pretty much smooth sailing with the usual make and make install. The tora compilation takes quite a while.
This section is a very brief overview with some tips for working with Oracle's Optimizer Mode.
SQL> select value from v$parameter where name='optimizer_mode'
The Oracle cost-based optimizer can be set to one of the following modes:
With the default being choose, which favours full table scans. If most of the queries being run include restrict (where) clauses then the optimzer mode first_rows can improve performance. To set the optimizer mode for a single session:
SQL> alter session set optimizer_mode='first_rows';
To set the optimizer mode during oracle initialization, modify the initialization file, which is typically held in the pfile directory, which can be found in something like:
bash # cd /u01/app/oracle/admin/<SID>/pfile/init<SID>.ora
Where <SID> is the name of the database. The other option is to use alter system, which requires DBA role. This can be done with:
SQL> alter system set optimizer_mode='first_rows'
Oracle 10g comes with a web-based administration console, which is far nicer to use, more flexible and user to install than the traditional dbastudio (oemapp dbastudio). The web admin console runs on port 1158 by default and has a servlet path of "/em". E.g., to start the admin console on the localhost (default port 1158):
This will start the admin console session for the default database specified in the em app configuration. Login as system or other authorized user.
The database character (which is different to the nls_characterset) is the character set used for storing strings in the dbms. The default database typically non-unicode and is locale dependent. For example, a locale of en_AU might configure a database character set of Latin-1 (western europe), which is WE8ISO8859P1 (For Western Europe ISO-8859-1). The default database character set for Oracle database instances is the 7-bit ascii character set called US7ASCII.
If the database character set is different from that of the underlying operating system, then oracle will perform character set conversion. Clearly, character set conversion comes at some overhead and so it's best to try and match the character set for both dbms and the o/s.
If the db character set must be different to that of the o/s, then there are some options as to how and when character set conversion takes place. For example, in a jdbc thin configuration, character set conversion is done as required on the server. This will occur for each result set passed back to the client). An alternative to this is to push character set conversion back onto the client. This can be done with Net/8 and the OCI driver. For character set conversion to work properly, the client machine must have the Net/8 client component installed locally.
The current database character set is not present in the initialization parameters and show cannot be determined using the show parameter command (unlike the nls character set, which can). Query the database parameters to see the current database character set value:
SQL> TODO
Before setting the database character set, it is a good idea to ensure that the dbms supports the character set that is being selected. This can be done with a simple sql statement (the following will prompt for a value for the bind variable charset, e.g., WE8ISO8859P1 or AL32UTF8).
SQL> select convert('a','&charset','us7ascii') from dual;
The sql interpretor will ask for the character set name and will respond with ORA-01482: unsupported character set if character set is unsupported and with the a single row containing 1 column with the converted value for 'a' if the character set is supported.
The database character set can be specified during database instance creation. However, if the database character set needs to be changed once the instance is created, then use the following ALTER statement, supplying the name of the character set to use as an argument:
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
The db instance must be restarted after the database character set has been changed. See also the restrictions in #6D, below.
Not all character sets supported by oracle can be used as database character sets. In particular fixed-width multibyte character sets are not supported as database character sets (but these can be used for table character sets as for nvarchar2 columns).
Also, if the database character is being changed after the db instance has been created, then the new character set must be a strict superset of the current database character set. For example, the character set UTF8 is a strict superset of US7ASCII and AL32UTF8 is a strict superset of UTF8 (and of US7ASCII). As an example, it's not possible to change a database character set to US7ASCII from UTF8 (nor from AL32UTF8).
There is useful information on the Database Character Set, both at Oracle and at other sites,:
Oracle uses disk files as the physical storage mechamism for system and user data. A database will have several tablespaces, which are logical units. Examples of tablepaces are USERS, which is for user tables and data, TEMP which is for rollback, TOOLS, for example schemas, INDX which by convention holds indexes. Each tablespace will have at least one but may have many database files associated with it. A database file is typically located in the directory /u01/oracle/app/radata<SID>datafile/, where SID is the database name).
There may be several files located in the datafile directory, each possibly belonging to different tablespaces. By convention, a database database contains the name of the tablespace to which it belongs. In the following example, the database called FLYWHEEL has several tablespaces and several datafiles:
bash $ ls -laF /u01/app/oracle/oradata/flywheel/ -rw-r----- 1 ora10g dba 7159808 Dec 20 15:31 control01.ctl -rw-r----- 1 ora10g dba 7159808 Dec 20 15:31 control03.ctl -rw-r----- 1 ora10g dba 1073750016 Dec 20 15:12 indx.dbf -rw-r----- 1 ora10g dba 52429312 Dec 20 15:31 redo01.log -rw-r----- 1 ora10g dba 52429312 Dec 20 14:40 redo02.log -rw-r----- 1 ora10g dba 52429312 Dec 20 14:42 redo03.log -rw-r----- 1 ora10g dba 534781952 Dec 20 15:30 sysaux01.dbf -rw-r----- 1 ora10g dba 555753472 Dec 20 15:30 system01.dbf -rw-r----- 1 ora10g dba 245374976 Dec 19 23:03 temp01.dbf
From the above we can infer that FLYWHEEL has 5 tablespaces: INDX with 1 datafile 'indx.dbf', TEMP with 1 datafile 'temp01.dbf' and SYS with 2 datafile 'system01.dbf' and 'sysaux01.dbf'
The amount of space allocated to a tablespace can be dynamically altered. Having tablespaces comprised of several distinct datafiles makes the process of extending the tablespace pretty straightforward as all that is required is to add a new datafile to the tablespace. Oracle can configure tablespaces to autoextend, which ensures that the DBMS increases the size of the tablespace automatically, as needed. The autoextend option can be specified to grow a tablespace by specific increments, up to a set size or can be set so as to grow in an unlimited fashion, filling up the disk partition that the datafiles for the tablespaces reside on.
The attributes of a database datafile can be altered using any one of the many system tools, such as TOra or the web Oracle Enterprise Manager. Another option is to use interactive sql sqlplus. The following sqlplus DDL alters the autoextend attribute of the 'temp01.dbf' datafile, specifying unlimited growth:
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DANNO/temp01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;
Another option in allocating space to the dbms is to shrink indexes. Depending on the type of index a large amount of space can be wasted, especially if the index is sparse. The alter index DDL has an option for shrinking an index.
In the following, the index SOMEINDX in the FOO schema is shrunk. Typically, index shrinking should be applied periodically to a number of sparse indexes:
SQL> alter index "FOO"."SOMEINDX" shrink space
There are many tools for DBMS Management. There are graphical tools, web-based tools, tools written in java using the JDBC driver and of course there is the interactive sql program sqlplus, which is bundled with Oracle. Many of the tools have an option to show SQL, which is useful as it shows the user how the DBMS catalog and DBMS attributes can be changed.
The following is a list of some of the more popular tools (commands for sqlplus are not covered in this section as this is too large a topic. Refer to to the Oracle SQLPlus Command Reference for more information on sqlplus):
The oracle substr function can be used to extract a substring from a string value. The function takes 3 arguments which are the string to manipulate, the starting index (offset is 0 or 1) and the number of characters. E.g. the following both result in the first 2 characters of the current date:
SQL> select substr(to_char(sysdate),0,2) as day dual; DAY -------- 07
SQL> select substr(to_char(sysdate),1,2) from dual; DAY -------- 07
The substr function can also return the last n characters from a string, which can be done by specifying a negative index. An index of -1 refers to the last character in the string. E.g., to answer the 2-digit year from the current system date:
SQL> select substr(to_char(sysdate),-2,2) as YEAR from dual; YEAR -------- 08
In DEDICATED mode, the maximum number of connections permissible to an oracle instance is determined by the operating system's maximum number of semaphores less about 10. In most unix environments the semaphore count is typically between 20 and 60, which means the maximum number of connections for DEDICATED mode is between 10 and 50. For Linux, the number of semaphores configured for the O/S can be established by looking at the kernel data from the /proc psuedo-filesystem
bash $ cat /proc/sys/kernel/sem 20
For Solaris, kernel data such as system shared memory and semaphores are configured via /etc/system, which is loaded at boot time (no such luxury as /proc in Solaris). See comments in Sun Microsystems documentation Sun Microsystems doco Configuring Shared Memory and Semaphores .
In simple terms. simply cat(1) the system configuration:
bash $ cat /etc/system
Tip: The solaris command prtconf will display the system configuration. E.g., prtconf | grep Memory
Note: A single Oracle connection can spawn multiple processes
A single connection can spawn multiple Oracle processes and Oracle will only service requests as long as the instance's process count has not exceeded the specified threshold value (which defaults to 150). The maximum processes for an instance can be determined by querying the V$PARAMETER view:
SQL> select p.name, p.value from V$PARAMETER p where p.name = 'processes';
Implicit PL/SQL is invoked using begin/end in the sql command interpretor (i.e., SQL*Plus)
SQL> var num_var number
SQL> var txt_var varchar2(20)
SQL> begin
2 select 44, 'fourty-four' into :num_var, :txt_var from dual;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print num_var
NUM_VAR
----------
44
SQL> print txt_var
TXT_VAR
------------------
fourty-four
It's also possible to assign value to a bind variable with execute. Since an execute is basically a wrapper around a begin/end PL/SQL block, a variable can be assigned a value with:
SQL> exec :num_var := 42 SQL> exec :txt_var := 'fourty-two'
And then another record can be inserted:
SQL> exec insert into sqlplus_bindvar_ex values (:num_var, :txt_var) SQL> select * from sqlplus_bindvar_ex
PL/SQL provides the NO_DATA_FOUND pragma which can be used to perform conditional PL/SQL when a select fails to return any data. In the following example, the first sql statement always fails and the second statement assigns the value of 10 to the variable n in an exception block.
SQL> var n number
SQL> BEGIN
2 select 1 into :n from dual where 1>1;
3 EXCEPTION when NO_DATA_FOUND THEN
4 select 10 into :n from dual;
5 END;
SQL> /
SQL> print n
N
---------
10
SQL> quit
The PL/SQL procedure raise_application_error can be used to raise a user initiated exception. The procedure takes 2 arguments and an optional 3rd boolean. The first argument is the error code, which must be a numeric value between -20000 and -20999. The second argument is a string message. The following PL/SQL raises an error when no data are returned.
SQL> var n number SQL> begin 2 select 1 into :n from dual where 1>1; 3 exception when no_data_found then 4 raise_application_error(-20999, 'The value of 1 is not greater than 1 - why on earth not?'); 5 end; SQL> / ERROR at line 1: ORA-20999: The value of 1 is not greater than 1 - why on earth not? ORA-06512: at line 4
It's possible to get SQL*Plus to spit out an exit code by using whenever sqlerror exit n (where n is the exit code to return). The following example exits the SQL interpretor with an exit code of 10 when no data are returned from a query. This is done by detecting the no_data_found exception and then raising a user defined error:
SQL> whenever sqlerror exit 10 SQL> var n number SQL> begin 2 select 1 into :n from dual where 1>1; 3 exception when no_data_found then 4 raise_application_error(-20999, 'Always fails - every time!'); 5 end; SQL> / ERROR at line 1: ORA-20999: Always fails - every time! ORA-06512: at line 4
Tip: Stopping the Oracle listeners is only required for multithreaded DBMS's (Shared Server)
Note 1: Oracle's dbshut and dbstart scripts use sqlplus for 8i+ DBMS and sqldba for Oracle 7 and less.
Note 2: In the following, SQL> means run from within SQL*Plus, oracle9i $ means run the command user oracle9i, bash $ means run the command as any user and bash # means run the command as user root
Since Oracle 8i, the SQL*Plus utility is used for bouncing a database. Versions prior to 8i (7 and prior) use sqldba. This section describes cycling Oracle 8i and greater. The algorithm can be broken down into:
The following describe the actions required to shutdown an imaginary DBMS called MY_ORACLE_DB running on the server dbms.host.com under the O/S user oracle9i. In the following, it is assumed that oracle9i has ssh access. Often, this is not the case and many dbms servers require su(1) access only (from the privileged root user).
This step overs login and environment configuration:
bash $ ssh oracle9i@dbms.host.com bash $ echo $ORACLE_HOME /ora/product/10.1.0 bash $ echo $ORACLE_SID MY_ORACLE_DB
Note: See #23.2 on configuring oracle dispatchers
The Oracle Dispatchers (Also called Listeners) are required for multithreaded oracle (i.e., SHARED mode servers). The oracle binary lsnrctrl is used to start, stop and check the status of the dispatchers.
oracle9i $ lsnrctrl status oracle9i $ lsnrctrl stop
Warning: Be sure that the correct ORACLE_SID is set, otherwise the wrong instance maybe shutdown
The SQL*Plus utility is used to start and stop the DBMS. It can also be used to check if an instance is up and running. The /nolog option is imporant as it indicates that no transaction management (i.e., no rollback segments) are to be used for the session.
Tip: After connect, the DBMS responds with Connected. if the instance is up or Connected to an idle instance if it's down.
oracle9i $ sqlplus /nolog SQL*Plus: Release 10.1.0.4.0 - Production on Tue Dec 9 10:46:38 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> shutdowm immediate SQL> quit
Note: Only issue a shutdown abort on development databases or instances where data integrity are not an issue
The shutdowm immediate is the preferred mode of shutting down the dbms as it will attempt to complete active transactions. If the DBMS cannot complete transactions, then the abort option can be issued, which will simply terminate all sessions regardless of what they're in the middle of doing. This is clearly not a good option for a production instance as it can lead to data corruptoins and integrity issues.
Much like an instance shutdown, an instance startup is done by using SQL*Plus in non-transactional mode. Again, be sure to login as the oracle process owner on the host serving the DBMS instance and be sure that ORACLE_SID is set to the correct instance (see #8.A)
oracle9i $ sqlplus /nolog
SQL*Plus: Release 10.1.0.4.0 - Production on Tue Dec 9 10:46:38 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
<<<< add in the output from a startup >>>
DBMS Mounted.
SQL> quit
The instance is started now and this can be verified by connecting as the sysdba user and then checking the DBMS response to the connect operation. A response of Connected. implies that the DBMS instance is up and running
oracle9i $ sqlplus /nolog SQL*Plus: Release 10.1.0.4.0 - Production on Tue Dec 9 10:46:38 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> quit;
Next start up the dispatchers using lsnctrl. After starting the dispatchers, it may take a while for the dispatchers to be able to provide dbms connections
Note: It may take a few minutes for the dispatchers to start servicing remote connection requests.
oracle9i $ lsnrctrl start oracle9i $ lsnrctl status | egrep $ORACLE_SID Service "CITD01" has 2 instance(s). Instance "CITD01", status UNKNOWN, has 1 handler(s) for this service... Instance "CITD01", status READY, has 2 handler(s) for this service...
After starting the dispatchers, we can attempt a remote TNS connection.
The tnsping command can be used to verify that a DBMS instance defined in the tnsnames file is up and running. The tnsping(1) command is delivered with the standard oracle distribution in $ORACLE_HOME/bin
bash $ tnsping my_oracle_db TNS Ping Utility for Solaris: Version 10.1.0.4.0 - Production on 09-DEC-2008 11:43:37 Copyright (c) 1997, 2003, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbms.host.com)(PORT=1521))(CONNECT_DATA=(SID=MY_ORACLE_DB))) OK (10 msec)
Note: See psoug.org db_link
It is possible to create a dbms link in a given instance which allows access to data from a separate instance. The create database link command will create the remote link. Creating a remote link in an instance requires the create database link priviledge in the dbms instanc where the link is being created but also requires access in the remote instance for connect and query on the object being linked to.
DSBMS privileges required for creating a database link are:
The link access is controlled by a tnsnames entry. A special entry can be created for the link or an existing entry can be used. In the following example, a special entry called 'devlink' is created in the tnsnames.ora
oracle9i $ cat tnsnames.ora
-- START tnsnames.ora
--
-- Entry #1: tnsnames entry for dbms link
--
dev_link =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbms.host.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = my_oracle_db)
)
)
-- END tnsnames.ora
Example link creation:
SQL> create public database link somelink
connect to current_user
using 'dev_link'
Tip: Use the alter table statement to enable or disable a constraint. The constraint name is not quoted.
Note: The prompt SQL> indicates sql commands to be run through an SQL interpretor.
System constraints like entity- (EI) and referential-integrity (RI)as well as user-defined constraints can be enabled and disabled as required. The alter table statement is used to enable or disable a contraint. The EI and RI contraints are always named starting with the prefix SYS_ whereas user-defined contraints can be given any arbitrary name as long as the name conforms to Oracle object naming rules.
To determine which contraints exist on a given object, query the all_objects catalog table. The following example describes the contraints that exist for the table object SOME_TABLE
To enable or disable a contraint, use the alter table command. The following example enables a (previously disabled) constraint called SOME_CONTRAINT on a table SOME_TABLE
SQL> alter table some_table enable constraint some_constraint
The Oracle environment allows the DBA to define roles, which can be assigned to users. A role is a group of one or more priviledges and of course a single priviledge can be assigned to a user without requiring a role. The table system_privilege_map is a list of the defined privs known the dbms and the table dba_users holds x. The SQL command grant is used to asign a role and revoke is used to remove a role.
Before a user can even connect to a db instance, the connect role must be assigned:
SQL> grant connect to foo;
To determine which roles and privs a user is assigned requires access to the dba_users, dba_role_privs and dba_sys_privs tables. E.g., the following sql does a union over these tables to determine the assigned privs for the user foo:
SQL> select lpad(' ', 2*level) || granted_role "User with roles and privs"
from (
select null as grantee, username as granted_role
from dba_users -- the users
where username = 'FOO'
union
select grantee, granted_role
from dba_role_privs -- the roles to roles relations
union
select grantee, privilege
from dba_sys_privs -- the roles to privilege relations
)
start with grantee is null
connect by grantee = prior granted_role
SQL>
This is trivial if there is already access to a dba user as that user can simple grant dba priviledges. However, if no dba user is accessible, then this action is still possible so long access is possible to the host on which the db instance in question is running. The steps are:
E.g.,
bash $ ssh oracle@oracle.host.com bash@oracle $ sqlplus /nolog SQL> connect / as sysdba SQL> grant dba to some_user SQL> quit
Note: Oracle SQL create user reference for examples of creating externally authenticated users
Creating a database user requires DBA role priviledges (see #21). The create user statement is used to create a user and assign a passwd. Options for the create user statement allow the specification of default tablespaces, index tablespaces and quotas. After the user is created, it is essential to assign at least the connect role to the new user as otherwise the use can't even connect to the DBMS.
The following example creates an internally authenticated user called foo with passwd foo and ensures that all tables (and indexes) are created in the tablespace called DATA. The user is granted CONNECT and CREATE TABLE:
SQL> create user foo identified by foo default tablespace USERS temporary tablespace TEMP quota unlimited on USERS; SQL> grant connect to foo; SQL> grant create table to foo;
Tip: Specify 'USING TABLESPACE' clause of 'CREATE INDEX' to store indexes to a different tablespace to it's table
Oracle places all user objects in the tablespace specified in the default tablespace clause. If user tables and indexes are to kept in separate tablespaces, then the using tablespace clause of create table or create index must be specified. Using the previous example user foo, tables and indexes are allocated to tablespace DATA. To create an index in tablespace INDX:
SQL> connect foo/foo SQL> create table sometable(a int); SQL> create bitmap index sometable_index on sometable(a) using tablespace indx;
A dbms user can be allocated a default tablespace, which is where any tables created by that user are stored. In addition, a temporary tablespace can be allocated, which is where temporary tables (such as intermediate result sets) are created. In addition to allocating default and temporary tablespaces, a quota, or limit, can be placed on the tablespace on a per user basis.
The following sqlsniplets illustrate changing a user's default tablespace and removing any quota from that tablespace (for that user)
SQL> -- switch tablespace to 'SOME_TS' for user 'SOME_USER' SQL> alter user some_user default tablespace some_ts quota unlimited on some_ts SQL> SQL> -- The quota can be done independently too SQL> alter user some_user quota unlimited on some_ts
Note: See also #10 Max number of connections
Tip: Refer to Oracle's documentation on Configuring Oracle Database for Shared Server
The default out of the box configuration for Oracle DBMS configures the server in DEDICATED mode, which is suitable for batch oriented processing. A dbms in DEDICATED mode will allocate resources per connection (see x) and will block connection requests when the threshold limit is reached, until proceses are freed and a connection can be established.
Multithreaded operation of the DBMS. also called SHARED mode, provides a much more efficient use of resources and scales better at a slight degradation in batch oriented performance.
The v$parameter table can be used to determine the operational resource mode of the dbms. The following query will return the number of shared servers configured for the current dbms instance. For DEDICATED mode dbms configurations, the number of shared servers will be 0.
SQL> col name format a32 SQL> col value format a32 SQL> col description format a64 SQL> SQL> select name,value,description from v$parameter where name like '%shared_servers%' SQL> ; NAME VALUE DESCRIPTION ----------------------- --------------- ------------------------------------ shared_servers 1 number of shared servers to startup max_shared_servers max number of shared servers shared_server_sessions max number of shared server sessions
In addition, the v$shared_server table can be used to identify what requests are being serviced by the dbms. The v$shared_server can also be used to establish if the DBMS is in shared mode as it will be empty for a DEDICATED mode database instance.
Warning: A correctly utilized shared server configuration will see MESSAGES > 0 and STATUS will be (mostly) EXEC. If MESSAGES is 0 and STATUS is WAIT(COMMON) then the shared server is not being utilized and client sessions will register as DEDICATED.
SQL> -- Determine if dbms is running in shared mode (and if so, what iequests are being serviced) SQL> select * from v$shared_server SQL> ; NAME PADDR STATUS MESSAGES BYTES BREAKS CIRCUIT IDLE BUSY REQUESTS ---- ---------------- ---------------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- S000 0000040017C01528 EXEC 244169 164632844 0 000000038C0721A8 259752 68032 108318 S001 0000040017C01C10 TERMINATED 68 14446 0 00 3476 3403 34 S002 00000400578037B0 TERMINATED 108 14970 0 00 4285 2597 54
The results of the query can be interpreted thusly,
The dispatchers are configured by adding entries to the listener configuration, defiend in $ORACLE_HOME/network/admin/listener.ora. The optimum number of dispatchers is calculated by taking into account the maximum number of concurrent users divided by connections per process. For example, if there are an anticipated 400 concurrent connections on average and 200 connections per dispatcher then 2 dispatchers is recommended.
The following listener.ora configuration defines a listener on host some.dbhost.com on port 1521
# Start: listener.ora Network Configuration File: /ora/dev/product/10.1.0.5/db/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /ora/dev/product/10.1.0.5/db)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = some.dbhost.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
)
# End
The dispatchers (also called listeners) are started using the lsnctrl command, which is provided in the Oracle binary distribution directory (e.g., $ORACLE_HOME/bin/lsnctrl). The dispatchers are started with the start option and are stopped with the stop option. There is also a status argument which queries the current state.
oracle $ lsnctrl start LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 15-APR-2009 10:59:25 Copyright (c) 1991, 2004, Oracle. All rights reserved. Starting /ora/dev/product/10.1.0.5/db/bin/tnslsnr: please wait... TNSLSNR for Solaris: Version 10.1.0.5.0 - Production System parameter file is /ora/dev/product/10.1.0.5/db/network/admin/listener.ora Log messages written to /ora/dev/product/10.1.0.5/db/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=csqarc01d-db01d)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=csqarc01d-db01d)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 10.1.0.5.0 - Production Start Date 15-APR-2009 10:59:25 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora/dev/product/10.1.0.5/db/network/admin/listener.ora Listener Log File /ora/dev/product/10.1.0.5/db/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=csqarc01d-db01d)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
oracle $ lsnctrl status LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 15-APR-2009 11:05:27 Copyright (c) 1991, 2004, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=csqarc01d-db01d)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Solaris: Version 10.1.0.5.0 - Production Start Date 15-APR-2009 10:59:25 Uptime 0 days 0 hr. 6 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora/dev/product/10.1.0.5/db/network/admin/listener.ora Listener Log File /ora/dev/product/10.1.0.5/db/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=csqarc01d-db01d)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "CITD02" has 1 instance(s). Instance "CITD02", status READY, has 1 handler(s) for this service... Service "CITD02XDB" has 1 instance(s). Instance "CITD02", status READY, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Oracle will install the Oracle Cluster Synchronization Service Daemon (OCSSD), even if the installation is not configured for to use the Oracle RAC or ASM. The OCSSD instance is run as root and appears in the process list as ocssd.bin. The OCSSD is controlled (started, stoped) via the init.d script /etc/init.d/init.cssd. The OCSSD must be stopped in order to install new Oracle modules or Oracle patchsets as otherwise the Oracle Installer will moan //cssd.bin still running//. To stop the OCSSD, run the following (replace stop with start to start the daemon):
bash # /etc/init.d/init.cssd stop
The OCCSD maybe configured by the Oracle Installer to load via inittab. If this is the case (and assuming that the installation is not configured for RAC or ASM) then simply comment out the line containing /etc/init.d/init.cssd run. E.g.,
cat /etc/inittab | grep init.cssd #h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1
The task of identifiying which processes are blocked on which other processes is done by querying the v$lock view.
The simplest approch is to use a 2-step solution which identifies which SIDs are blocked on other sids. This particular query (called locks step #1) is:
SQL> -- Step #1 SQL> select lock1.sid sid_doing_blocking, lock2.sid sid_being_blocked SQL> from v$lock lock1, v$lock lock2 SQL> where lock1.block =1 SQL> and lock2.request > 0 SQL> and lock1.id1=lock2.id1 SQL> and lock1.id2=lock2.id2 SQL>
The previous query will print two columns sid_doing_blocking and sid_being_blocked. To identify more characteristics of the sid doing the blocking (such as from which host and which username), feed the sid_doing_blocking value in the locks step #2:
SQL> -- Step #2 SQL> -- change the sid to some sid output from locks step #1 SQL> select s.sid, s.username, s.osuser, s.machine from v$session s where s.sid=314
The other approch is to combine the 2 queries into a single subselect to identify which sids are blocking which other sids. This is
SQL> -- Identify locks in a single step SQL>select s1.username || '@' || s1.machine SQL> || ' ( SID=' || s1.sid || ' ) is blocking ' SQL> || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status SQL> from v$lock l1, v$session s1, v$lock l2, v$session s2 SQL> where s1.sid=l1.sid and s2.sid=l2.sid SQL> and l1.BLOCK=1 and l2.request > 0 SQL> and l1.id1 = l2.id1 SQL> and l2.id2 = l2.id2 ;
A column can be added to an existing table (but cannot be removed) and an existing column can be increased but not decreased in size. E.g.,
SQL> alter table foo ADD column a varchar2(10); SQL> alter table foo MODIFY a varchar2(15);
Oracle default index structures are B-Tree and these provide good response for high-cardinality result sets (lots of distinct values). Oracle also provides the BITMAP index which provides rapid response for low cardinality result sets (not many distinct values).
SQL> create index my_idx on my_table (my_high_cardinality_col); SQL> create BITMAP index my_bitmap_index on my_table (my_low_cardinality_col);
Connecting via the Thin JDBC Oracle Driver to an Oracle Real Application Cluster (RAC) is a little different that simply specifying the jdbc:oracle:thin:@<host>:<port>:<sid> as is the case when connecting to a simple Oracle configuration. For the connection to establish property, be sure to include the connection in descriptor as would be done a tnsnames.ora entry for the same cluster. E.g., the jdbc thin url for connecting to SID called MYSID on a load balanced RAC over two multi-threaded servers dbsrv1 and dbsrv2, via ports 1521 is:
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=on) (ADDRESS_LIST= (ADDRESS=(protocol=tcp)(host=dbsrv1)(port=1521)) (ADDRESS=(protocol=tcp)(host=dbsrv2)(port=1521))) (CONNECT_DATA=(SERVER=SHARED)(SERVICE_NAME=MYSID)))
29. Explain Plan
The explain plan command can be used to determine oracle's strategy for executing a query. The general form of the command is explain plan [into sometable] for query. If the into is ommitted, then the plan results are written to PLAN_TABLE. The structure of a plan table should be:
SQL> CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(255), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_ALIAS VARCHAR2(65), OBJECT_INSTANCE NUMERIC, OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMERIC, PARENT_ID NUMERIC, DEPTH NUMERIC, POSITION NUMERIC, COST NUMERIC, CARDINALITY NUMERIC, BYTES NUMERIC, OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMERIC, OTHER LONG, DISTRIBUTION VARCHAR2(30), CPU_COST NUMERIC, IO_COST NUMERIC, TEMP_SPACE NUMERIC, ACCESS_PREDICATES VARCHAR2(4000), FILTER_PREDICATES VARCHAR2(4000), PROJECTION VARCHAR2(4000), TIME NUMERIC, QBLOCK_NAME VARCHAR2(30), OTHER_XML CLOB );
The plan table should be cleared prior to capturing the strategy for the query in question. Assume that a table my_table(mycol int) exists. Capturing a full select over this table results in:
SQL> create table my_table(mycol int); SQL> insert into my_table(mycol) values (1); SQL> insert into my_table(mycol) values (10); SQL> insert into my_table(mycol) values (100); SQL> delete plan_table; SQL> explain plan for select * from my_table where mycol > 0; SQL>
The preceeding explain plan has created the entires in the plan_table for the associated query. To view the results, we need to use control break reporting on the plan_table, connecting ID to PARENT_ID:
SQL> select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object"
SQL> from plan_table
SQL> start with id = 0
SQL> connect by prior id=parent_id;
SQL>
+-----------------------+---------+ | Operation | Object | +-----------------------+---------+ | SELECT STATEMENT () | | | TABLE ACCESS (FULL) | A | +-----------------------+---------+ 2 rows selected (0.007 seconds)
The above plan shows (unsurprisingly) a full table scan over the relation A. This can be improved by indexing the table over the column used in the query (i.e., column mycol).
SQL> create index my_table_index on my_table(mycol);
SQL> delete from plan_table;
SQL> explain plan for select * from my_table where mycol > 0;
SQL
SQL> select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object"
SQL> from plan_table
SQL> start with id = 0
SQL> connect by prior id=parent_id;
+----------------------+----------+
| Operation | Object |
+----------------------+----------+
| SELECT STATEMENT () | |
| INDEX (RANGE SCAN) | A_INDEX |
+----------------------+----------+
2 rows selected (0.007 seconds)
The preceding now shows that the query strategy is using an index range scan for the query. This is more optimal than the full table scan shown previously (the executions times are the same because the table cardinality is trivially small at 3)
Stuart Moorfoot © 6 August 2007 foo@bund.com.au