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:
This section covers the following topics:
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.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.
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 |
|---|---|
|
First connection attribute Required privilege: Instance administrator Enables PL/SQL in the database. If If 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) |
|
|
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. |
|
|
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. |
|
|
General connection attribute Required privilege: None Controls whether the PL/SQL compiler generates cross-reference information. Possible values are You can use the Default: |
|
|
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 See "Conditional Compilation" in Oracle Database PL/SQL Language Reference for information about this feature. Default: |
|
|
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 You can use the Default: 100 megabytes Note: In |
|
|
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 Default: 2 |
|
|
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 (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 Default: 30 seconds Notes:
|
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.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.