Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
11g Release 2 (11.2.2)

E21639-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 PL/SQL Installation and Environment

The chapter shows you how to manage PL/SQL in your TimesTen database, set connection attributes, and display system-provided packages. It also describes the ttSrcScan utility, which you can use to check for PL/SQL features unsupported in TimesTen. The chapter concludes with examples to assist you in your setup procedures.

Topics in this chapter include:

Confirming that PL/SQL is installed and enabled in TimesTen

This section covers the following topics:

PL/SQL installation and the ttmodinstall utility

TimesTen installs PL/SQL by default. If you choose not to install PL/SQL (which is discouraged), you can use the TimesTen ttmodinstall utility to install it later. For more information, see "ttmodinstall" in Oracle TimesTen In-Memory Database Reference.

Note:

Only the instance administrator can run this utility.

Checking that PL/SQL is enabled in a TimesTen database

PL/SQL is enabled by default (first connection attribute setting PLSQL=1). You can confirm the status of PL/SQL in your database in the following ways. In these examples, $ is the UNIX prompt and Command> is the ttIsql prompt.

  • Use the ttStatus utility to determine if PL/SQL is enabled in your database, as indicated in the following example. See "ttStatus" in Oracle TimesTen In-Memory Database Reference for information about this utility.

    $ ttstatus
    TimesTen status report as of Sat Sep 17 13:58:27 2011
     
    Daemon pid 20921 port 28959 instance myserver
    ...
    ------------------------------------------------------------------------
    Data store /mypath/install/info/DemoDataStore/sampledb_1122
    There are xx connections to the data store
    Shared Memory KEY 0x2e0183b1 ID 1343492
    PL/SQL Memory KEY 0x2f0183b1 ID 1376261 Address 0x10000000
    Type            PID     Context     Connection Name              ConnID
    Process         16678   0x08f718c0  sampledb_1122                     1
    ...
    Replication policy  : Manual
    Cache Agent policy  : Manual
    PL/SQL enabled.
    ------------------------------------------------------------------------
    ...
    End of report
    
  • Using the ttIsql utility, call the ttConfiguration built-in procedure to determine the PLSQL connection attribute setting for your database. The value 1, as shown in the following example, indicates PL/SQL is enabled. Refer to "ttConfiguration" in Oracle TimesTen In-Memory Database Reference for information about this built-in procedure.

    Command> call ttconfiguration;
    ...
    < DataStore, /mypath/install/info/DemoDataStore/sampledb_1122 >
    ...
    < PLSQL, 1 >
    ...
    < UID, MYUSER >
    61 rows found.
    

PL/SQL connection attributes

There are several TimesTen connection attributes specific to PL/SQL, as summarized in Table 6-1 that follows. For additional information on these connection attributes, see "PL/SQL first connection attributes" and "PL/SQL general connection attributes" in Oracle TimesTen In-Memory Database Reference.

The table also notes any required access control privileges and whether each connection attribute is a first connection attribute or general connection attribute. First connection attributes are set when the database is first loaded, and persist for all connections. Only the instance administrator can load a database with changes to first connection attribute settings. A general connection attribute setting applies to one connection only, and requires no special privilege.

Table 6-1 PL/SQL Connection Attributes

Attribute Summary

PLSQL

First connection attribute

Required privilege: Instance administrator

Enables PL/SQL in the database.

If PLSQL=1, PL/SQL is enabled.

If PLSQL=0, PL/SQL is not enabled.

You can enable PL/SQL when your database is initially created or at any first connection. Once PL/SQL is enabled, it cannot be disabled.

Default: 1 (for platforms where PL/SQL is supported)

PLSQL_MEMORY_ADDRESS

First connection attribute

Required privilege: Instance administrator

Specifies the virtual address, as a hexadecimal value, at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. This memory address must be identical in all connections to a given database and in all processes that connect to that database.

If a single application simultaneously makes direct connections to multiple databases, then you must set different values for each of the databases.

Default: Platform-specific value

Refer to "PLSQL_MEMORY_ADDRESS" in Oracle TimesTen In-Memory Database Reference for platform-specific information.

PLSQL_MEMORY_SIZE

First connection attribute

Required privilege: Instance administrator

Determines the size, in megabytes, of memory allocated for the PL/SQL shared memory segment, which is shared by all connections. This memory is used to hold recently executed PL/SQL code and metadata about PL/SQL objects.

Default: Platform-specific value

Refer to "PLSQL_MEMORY_SIZE" in Oracle TimesTen In-Memory Database Reference for information about calculating the PL/SQL memory size and for platform-specific values and tuning information.

PLSCOPE_SETTINGS

General connection attribute

Required privilege: None

Controls whether the PL/SQL compiler generates cross-reference information. Possible values are IDENTIFIERS:NONE or IDENTIFIERS:ALL.

You can use the ALTER SESSION statement to change this value within your session.

Default: IDENTIFIERS:NONE

PLSQL_CCFLAGS

General connection attribute

Required privilege: None

Use this to set inquiry directives to control conditional compilation of PL/SQL units, which enables you to customize the functionality of a PL/SQL program depending on conditions that are checked. This is especially useful when applications may be deployed to multiple database environments. For example, to activate debugging features:

PLSQL_CCFLAGS='DEBUG:TRUE'

PL/SQL conditional compilation flags are similar in concept to flags on a C compiler command line, such as the following:

% cc -DEBUG=TRUE ...

You can use the ALTER SESSION statement to change PLSQL_CCFLAGS within your session.

See "Conditional Compilation" in Oracle Database PL/SQL Language Reference for information about this feature.

Default: NULL

PLSQL_CONN_MEM_LIMIT

General connection attribute

Required privilege: None

Specifies the maximum amount of PL/SQL shared memory (process heap memory) that PL/SQL can allocate for the current connection. (Note that this memory is not actually allocated until needed.) This is memory used for runtime data, such as large PL/SQL collections, as opposed to cached executable code. This limit setting protects other parts of your application, such as C or Java components, when PL/SQL might otherwise take all available runtime memory.

The amount of space consumed by PL/SQL variables is roughly what you might expect comparable variables to consume in other programming languages. As an example, consider a large array of strings:

type chararr is table of varchar2(32767) 
             index by binary_integer;
big_array chararr;

If 100,000 strings of 100 bytes each are placed into such an array, approximately 12 megabytes of memory is consumed.

Memory consumed by variables in PL/SQL blocks is used while the block executes, then is released. Memory consumed by variables in PL/SQL package specifications or bodies (not within a procedure or function) is used for the lifetime of the package. Memory consumed by variables in a PL/SQL procedure or function, including one defined within a package, is used for the lifetime of the procedure or function. However, in all cases, memory freed by PL/SQL is not returned to the operating system. Instead, it is kept by PL/SQL and reused by future PL/SQL invocations. The memory is freed when the application disconnects from TimesTen.

The PLSQL_CONN_MEM_LIMIT value is a number specified in megabytes. A setting of 0 means no limit.

You can use the ALTER SESSION statement to change this value within your session.

Default: 100 megabytes

Note: In ttPLSQLMemoryStats output, the related value CurrentConnectionMemory indicates how much process heap memory PL/SQL has actually acquired through malloc(). (Also see Example 6-3.)

PLSQL_OPTIMIZE_LEVEL

General connection attribute

Required privilege: None

Specifies the optimization level used to compile PL/SQL library units. The higher the setting, the more effort the compiler makes to optimize PL/SQL library units. Possible values are 0, 1, 2, or 3.

You can use the ALTER SESSION statement to change this value within your session.

Default: 2

PLSQL_TIMEOUT

General connection attribute

Required privilege: None

Controls how long PL/SQL program units are allowed to run, in seconds, before being terminated. A new value impacts PL/SQL programs currently running. Possible values are 0 (meaning no time limit) or any positive integer.

You can use the ALTER SESSION statement to change this value within your session.

(Also be aware of TimesTen SQL query timeout settings, as discussed in "Setting a timeout duration for SQL statements" in Oracle TimesTen In-Memory Database C Developer's Guide, and the TimesTen TTC_Timeout setting, discussed in "TTC_Timeout" in Oracle TimesTen In-Memory Database Reference.)

Default: 30 seconds

Notes:

  • The frequency with which PL/SQL programs check execution time against this timeout value is variable. It is possible for programs to run significantly longer than the timeout value before being terminated.

  • If you are using TimesTen Client/Server, PLSQL_TIMEOUT should be significantly less than TTC_Timeout, and cannot be 0 (for no timeout) if TTC_Timeout is greater than 0. For details, see the TTC_Timeout documentation referenced above.


Notes:

There are additional TimesTen connection attributes you should consider for PL/SQL. For more information about them, refer to the indicated sections in Oracle TimesTen In-Memory Database Reference.
  • If PL/SQL is enabled in your database, the value for the DDLCommitBehavior general connection attribute must be 0. See "DDLCommitBehavior".

  • If the LockLevel general connection attribute is set to 1 (database-level locking), certain PL/SQL internal functions cannot be performed. Therefore, set LockLevel to 0 for your connection. You can then use the ttLockLevel built-in procedure to selectively switch to database-level locking for those transactions that require it. See "LockLevel" and "ttLockLevel".

  • The PL/SQL shared memory segment is not subject to the MemoryLock first connection attribute. See "MemoryLock".

The rest of this section provides some examples for setting and altering PL/SQL connection attributes.

Example 6-1 Create a database with PL/SQL default connection attributes

This example defines a database pldef without specifying PL/SQL connection attributes. (Be aware that only an instance administrator can create a database.)

Sample odbc.ini entry:

[pldef]
Driver=/mypath/install/lib/libtten.so
DataStore=/mypath/install/info/DemoDataStore/pldef
DatabaseCharacterSet=US7ASCII

Connect to database pldef:

$ ttisql pldef
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=pldef";
Connection successful: DSN=pldef;UID=myuser;DataStore=/mypath/install/info/DemoDataStore/pldef;
DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;
DRIVER=/mypath/install/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)

Call the ttConfiguration built-in procedure to display settings, which shows you the default PL/SQL settings:

Command> call ttconfiguration;
...
< DataBaseCharacterSet, US7ASCII >
< DataStore, /mypath/install/info/DemoDataStore/pldef >
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
...
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...
< UID, MYUSER >
61 rows found.

Example 6-2 Use ALTER SESSION to change attribute settings

This example uses ALTER SESSION statements to alter PL/SQL connection attributes, changing the settings of PLSCOPE_SETTINGS, PLSQL_CONN_MEM_LIMIT, and PLSQL_OPTIMIZE_LEVEL. It then calls the ttConfiguration built-in procedure to display the new values.

Command> ALTER SESSION SET PLSCOPE_SETTINGS = "IDENTIFIERS:ALL";
 
Session altered.
 
Command> ALTER SESSION SET PLSQL_CONN_MEM_LIMIT=200;
 
Session altered.
 
Command> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;
 
Session altered.
 
Command> call ttconfiguration;
...
< DataStore, /mypath/install/info/DemoDataStore/pldef >
...
< PLSCOPE_SETTINGS, IDENTIFIERS:ALL >
...
< PLSQL_CONN_MEM_LIMIT, 200 >
...
< PLSQL_OPTIMIZE_LEVEL, 3 >
...
< UID, MYUSER >
61 rows found.

Example 6-3 View PL/SQL performance statistics

The ttPLSQLMemoryStats built-in procedure returns statistics about PL/SQL library cache performance and activity. This example shows some sample output. Refer to "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference for information about this procedure.

Command> call ttplsqlmemorystats;
< Gets, 5.000000 >
< GetHits, 0.000000e+00 >
< GetHitRatio, 0.000000e+00 >
< Pins, 4.000000 >
< PinHits, 0.000000e+00 >
< PinHitRatio, 0.000000e+00 >
< Reloads, 0.000000e+00 >
< Invalidations, 0.000000e+00 >
< CurrentConnectionMemory, 0.000000e+00 >
< DeferredCleanups, 0.000000e+00 >
10 rows found.

Note:

CurrentConnectionMemory is related to the PLSQL_CONN_MEM_LIMIT connection attribute documented in "PL/SQL connection attributes", indicating the amount of heap memory that has actually been acquired by PL/SQL.

The ttSrcScan utility

If you have an existing PL/SQL program and want to see whether it uses PL/SQL features that TimesTen does not support, you can use the ttSrcScan command line utility to scan your program for unsupported functions, packages, types, type codes, attributes, modes, and constants. This is a standalone utility that can be run without TimesTen or Oracle Database being installed and runs on any platform supported by TimesTen. It reads source code files as input and creates HTML and text files as output. If the utility finds unsupported items, they are logged and alternatives are suggested. You can find the ttSrcScan executable in the quickstart/sample_util directory in your TimesTen installation.

Specify an input file or directory for the program to be scanned and an output directory for the ttSrcScan reports. Other options are available as well. See the README file in the sample_util directory for information.