Questions and Answers



Space freed by recursive transactions

13 April 1999

When I rebuild a set of indexes using ALTER INDEX REBUILD from within a PL/SQL block using DBMS_SQL, the space required to perform the rebuilds is not released until all the indexes in the set have been rebuilt. I have tried issuing a COMMIT after each index is rebuilt, but this has no effect, nor would I expect it to. At the extreme, if I rebuild all indexes in the tablespace, I need twice as much space available as I need to store the indexes.

If I create a script containing the ALTER INDEX REBUILD commands and run it from SQL*Plus, space is released after each index is rebuilt, as I would expect.

Is there a fix or workaround that would allow me to rebuild a set of indexes through DBMS_SQL calls that does not require keeping my index tablespace more than 2 times its current usage?

? Because your index rebuilds are not top level transactions, but recursive transactions, the call to free the temporary segments that they have used will always fail because the row cache entry will be marked as dirty and the top level transaction is still active. So Oracle cannot be confident that a parent transaction is not using the segment. This is why it is necessary to have SMON clean up temporary segments.

Your SQL*Plus workaround can be made into a complete solution by writing another script to generate the rebuild index script file dynamically and then execute it.


PL/SQL timing

7 July 1999

Is there any utility / tool that can tell us the time taken to execute a PL/SQL package?
? Within PL/SQL you can use DBMS_UTILITY.GET_TIME. If you are just executing it from SQL*Plus, then try SET TIMING ON.

SLAX: parser

5 October 1999

I'm getting major performance differences between my production system and my development system. A couple of the observed differences are that the development system has both the Oracle7 Distributed Database option and the SLAX: parser option. What in the world is the SLAX: parser option? I can't find any mention of it on my 7.3.4 documentation CD.
? PL/SQL needs a slightly different parser for each host language context. The way Oracle have done this is to use a parser generator called SLAX to generate a PL/SQL parser dynamically when linking the other products. From the installer's point of view, SLAX is a separate product that is included according to dependency rules if required. Somewhat ambiguously, the name SLAX is also used for the generated parsers.

BAMIMA: Bam buffer

11 November 1999

What is "BAMIMA: Bam buffer" as shown in the KSMLRCOM column of X$KSMLRU?
-------------------- ---------- ----------
BAMIMA: Bam Buffer         5480         40
pl/sql DS pg              17048        760
BAMIMA: Bam Buffer        28104       1811
? PL/SQL is compiled into a pseudo-code (pcode) form. The BAM buffer is a memory structure into which the pcode is written during compilation. From the number of chunks flushed, it looks like you're not yet on Oracle8 and don't have a reserved pool configured. If so, let me encourage you to do so.


9 December 1999

I'm trying to write a stored procedure (Oracle 7.3.4 / SunOS 5.6) to kill all sessions for a given user. It compiles; it executes; but it doesn't actually kill the sessions, nor mark them as killed in V$SESSION. I've changed the command to various other DDL statements and it works as expected. I've traced the SQL, and the parse, execute and fetch all appear to succeed. If I cut and paste the output, it works exactly as it should but not when run from the stored procedure. Do you have any ideas why this might not be working? Am I missing something simple?
? Yes, check the documentation. ALTER SYSTEM is not a DDL statement. It is a "system control statement". And as the book says "ALTER SYSTEM is not supported in PL/SQL". However, you can work around the restriction using DBMS_SQL.

PL/SQL or C?

29 December 1999

We need to do some batch processing on about 1,000,000 rows per night. Given that PL/SQL is stored in the database in a compiled form, is it worth the effort of rewriting the code in C?
? The "compiled" form a PL/SQL is called PCODE, which stands for pseudo code, and is interpreted at runtime by the PL/SQL engine. That is, it is not really compiled; it is just pre-compiled. There is also a machine dependent form of pseudo code used in some cases called MPCODE. Oracle is working on native compilation for their Java code, and there is a chance that they will do the same for PL/SQL. Nevertheless, on current versions compiled C code can be expected to outperform PL/SQL by a long way. It depends on what you are doing, but I would be surprised if the performance gain were less than 20%.

Wrapped PL/SQL

5 January 2000

Does wrapped PL/SQL code take less memory in the SGA?
? No, wrapped code takes more space in the data dictionary, but the same amount of memory in the library cache when it is loaded.

expected symbol name is missing

13 January 2000

This is a question regarding PL/SQL program unit compilation. When I compile/recompile certain procedures, I get a message, "expected symbol name is missing", but the compilation itself goes through successfully. I looked at the procedure body, but didn't find anything wrong. Can you tell me what is happening here, and why I get the message?
? This is an SQL*Plus error. You may be using the & character in your program unit. SQL*Plus expects a variable name after that character.

Partitions dropped from PL/SQL

24 March 2000

I have been working on a PL/SQL package to support rolling partitions. During every run, it gets a spurious out of space error. If I fix the script to sleep for 30 seconds and retry, it works. What's the problem?
? The problem is that the space used by segments dropped from a PL/SQL procedure is not available for reuse until the procedure exits, or until a space management error occurs which posts SMON to do the cleanup.
Copyright Ixora Pty Ltd Send Email Home