Tom Kyte 谈 DBMS_ROWID、解析和调整大小

On DBMS_ROWID, Parsing, and Sizing
By Tom Kyte

Our technologist identifies rows, analyzes ratios, and fills space.

I have a partitioned table that spans multiple tablespaces, and some of these tablespaces are read-only. How do I determine if a given record from the table belongs to a read-only tablespace?

This is fairly straightforward. I can take the rowid and pass it to the DBMS_ROWID package to extract the various rowid components. In this case, I am interested in the database block number and the file number information in the rowid. I can then use the block and file numbers in a query against the DBA_DATA_FILES view to determine the tablespace name and then join that to the DBA_TABLESPACES view to determine the tablespace status.

To see this, I’ll need a small partitioned table that spans at least two tablespaces. For the purposes of demonstration, I have set up two tablespaces named RW (for the read/write data) and RO (for the read-only data). Initially both tablespaces are read/write, as shown in Listing 1.

Code Listing 1: Creating table T in RW and RO tablespaces

SQL> CREATE TABLE t
  2    (
  3     dt  date,
  4      x   int,
  5      y   varchar2(13)
  6    )
  7    PARTITION BY RANGE (dt)
  8    (
  9      PARTITION part1
 10      VALUES LESS THAN
 11      (to_date('01-jan-2008’,
 12         'dd-mon-yyyy’))
 13      tablespace rw,
 14      PARTITION part2
 15      VALUES LESS THAN
 16      (to_date('01-jan-2009’,
 17         'dd-mon-yyyy’))
 18      tablespace ro
 19    )
 20    /

Table created.

SQL> insert into t (dt,x,y)
  2    values
  3    (to_date('01-jun-2007’,
  4     'dd-mon-yyyy’),
  5     1, 'hello world’ );

1 row created.

SQL> insert into t (dt,x,y)
  2    values
  3    (to_date('01-jun-2008’,
  4     'dd-mon-yyyy’),
  5     2, 'goodbye world’ );

1 row created.

Now I’ll make the RO tablespace read-only:

SQL> alter tablespace RO read only;
Tablespace altered.

And then I am ready to join this table T to DBA_DATA_FILES and DBA_TABLESPACES to see the status of each row, as shown in Listing 2.

Code Listing 2: Joining T, DBA_DATA_FILES, and DBA_TABLESPACES

SQL> select x.*, ts.status
  2      from (
  3    select t.*,
  4          dbms_rowid.rowid_relative_fno( rowid ) rfno,
  5          dbms_rowid.rowid_to_absolute_fno( rowid, user, ’T’ ) afno
  6      from t
  7           ) x, dba_data_files df, dba_tablespaces ts
  8     where df.relative_fno = rfno
  9       and df.file_id = afno
 10       and df.tablespace_name = ts.tablespace_name
 11    /

DT          X  Y              RFNO    AFNO   STATUS
----------  -- -----------    ------  ------ ------
01-JUN-07   1  hello world    15      15     ONLINE
01-JUN-08   2  goodbye world  16      16     READ ONLY

Now, because I am already calling PL/SQL from SQL and incurring that runtime hit, this might be a case where I hide some of the complexity of the join in a PL/SQL function that is callable from SQL. The function could look like the ts_status function in Listing 3.

Code Listing 3: ts_status function

	
SQL> create or replace function ts_status( p_rowid in rowid ) return varchar2
  2    is
  3       l_status dba_tablespaces.status%type;
  4    begin
  5       select ts.status into l_status
  6         from dba_data_files df, dba_tablespaces ts
  7         where df.relative_fno = dbms_rowid.rowid_relative_fno( p_rowid )
  8           and df.file_id = dbms_rowid.rowid_to_absolute_fno( p_rowid, ’OPS$TKYTE’, ’T’ )
  9           and df.tablespace_name = ts.tablespace_name;
 10
 11       return l_status;
 12    exception when NO_DATA_FOUND
 13    then
 14       raise program_error;
 15    end;
 16    /

Function created.

SQL> select t.*, ts_status(t.rowid) ts_status
  2    from t;

DT            X     Y               TS_STATUS
-----------   ---   -------------   ----------------------
01-JUN-07     1     hello world     ONLINE
01-JUN-08     2     goodbye world   READ ONLY

The ts_status function in Listing 3 achieves the same goal as the query in Listing 2 but makes it a little easier to code day to day. A couple of notes on using this function, however:

• In general, if you can do it in pure SQL, do it in pure SQL. Do not invoke PL/SQL from SQL unnecessarily—it can add measurably to the runtime of a query. (In this case, I was calling the DBMS_ROWID package, so I was paying the penalty already.)

• The EXCEPTION block in the function is there to handle a situation peculiar to PL/SQL called from SQL. If you call PL/SQL that returns a “no data found” exception to a SQL statement, the SQL statement will return that exception to the client fetching from the result set and that client will think, “No more data” and stop fetching. In this case, “no data found” would be a grievous error, so I turn it into an error that will stop the client dead in its tracks. The client will know that something went wrong.

On a related note, I received a very similar question recently:

I need to know if there is a way to return the name of the partition from which a row of data is coming back.

DBMS_ROWID can help here as well. I can use it to extract the ROWID_OBJECT, which is the data object ID—the unique identifier of a segment. I can then join that to one of the XXX_OBJECTS (XXX could be DBA, ALL, or USER) views to see what the partition name is, as shown in Listing 4.

Code Listing 4: Returning partition name

SQL> select t.dt, uo.subobject_name,
  2           dbms_rowid.rowid_object(t.rowid) data_object_id
  3      from t, user_objects uo
  4     where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
  5    /

DT          SUBOB   DATA_OBJECT_ID
----------  ------  --------------
01-JUN-08   PART2   84859
01-JUN-07   PART1   84858

If you have never heard of the DBMS_ROWID package or seen it in action, you might want to check it out. It can come in very handy when looking at how the data is organized in a table and for figuring out where a row comes from.

 

Execute to Parse

I’m confused about the execute-to-parse ratio in Statspack/automatic workload repository reports. I have observed this ratio as 12.02 percent. My team is suggesting a change to the CURSOR_SHARING parameter value—to SIMILAR—to improve this ratio. Is that a correct approach? Can you please explain what this ratio actually means?

The last part of that question should throw up a bunch of red warning flags. You are looking at a ratio: the execute-to-parse ratio. You feel it is “bad” and would like to fix it, so you suggest changing a parameter to fix it. But then you ask for the ratio to be explained, meaning you’re not really sure what it is, what it means, and if the value you have is bad. Not only that, but the suggestion to change CURSOR_SHARING cannot and will not change the execute-to-parse ratio.

OK, first of all, let’s start with an explanation of what the execute-to-parse ratio is. It is a measure of how many times, on average, your SQL statements are executed rather than parsed. The way this ratio is computed, it will be a number near 100 percent when the application executes a given SQL statement many times over but has parsed it only once. (An application must parse a SQL statement at least once to execute it but needs to parse the SQL statement only once to execute it over and over again.) This ratio will be near 0 percent if the application parses a statement every time it executes it (parse count = execute count). This ratio will go negative if the application parses more often than it executes (and that would definitely be a sign that something is seriously wrong in the application). The formula (from Statspack) is simply

'Execute to Parse %:’, round(100*
(1-:prse/:exe),2)

As you can see, if :prse (parse count) is about the same as :exe (execute count), the execute-to-parse ratio will be 0 percent. If :exe is much larger than :prse, :prse/:exe will be near zero and the execute-to-parse ratio itself will be near 100 percent. If :prse is greater than :exe, the execute-to-parse ratio will go negative (indicating “this is bad”).

So that is, technically speaking, what the ratio is. A number near 100 percent would be great but might not be attainable. A negative number should definitely be avoided—it would indicate that the application actually parses a SQL statement but never executes it. The application developers would need to be educated, because a parse is an expensive operation and their goal is to reduce the number of parse calls—not create extra, unnecessary ones!

So what about the observed percentage, 12 percent? There is room for improvement there, but it cannot come from CURSOR_SHARING. Changing that parameter from its default (and preferred) setting of EXACT to SIMILAR might change the type of parsing happening (the parameter change can convert a hard parse into a soft parse), but it will never reduce the number of parse calls made by the application. After the parameter change, the application will still be calling “prepare statement” to parse SQL—under the covers, it might be a soft parse, but it will still be a parse.

In short, your colleagues’ suggestion would do nothing to alter the execute-to-parse ratio and could, in fact, cause major issues in your already-running system. Don’t even consider changing the CURSOR_SHARING parameter in order to try to fix your execute-to-parse ratio.

The only way to modify the execute-to-parse ratio would be to alter the two variables used in the formula. You can change either (1) the number of times you parse or (2) the number of times you execute.

I vote for the first option. Your application should look for ways to reduce the number of times it parses SQL, and how to do this varies from environment to environment. Using JDBC, for example, you can enable JDBC statement caching (a quick search for “JDBC statement cache” will turn up plenty of information). Using .NET, you can also enable statement caching (a quick search for “Oracle .NET statement cache” will turn up lots of information as well).

My preferred way to improve your execute-to-parse ratio, however, is to move all the SQL out of the client application and into stored procedures. PL/SQL is a statement caching machine—it has, from its very beginning, kept a cache of cursors open for us. When you say “close this cursor” in PL/SQL, PL/SQL tells you, “OK, it’s closed,” but it really isn’t. The PL/SQL engine smartly keeps the cursor open, knowing that you are just going to call that stored procedure and execute that SQL again.

Suppose you have a subroutine in your client application that is called 10,000 times a day. Further, suppose that it executes five SQL statements and those statements are parsed and executed every single time that routine runs. You will be doing 50,000 parse calls and 50,000 executes.

If you were to move those five SQL statements into a stored procedure and execute just one PL/SQL call in the client application—even assuming that you didn’t cache that statement in the client—you would now have

• 10,000 parse calls for the PL/SQL block
• 5 parse calls for the 5 SQL statements
• 10,000 execute calls for the PL/SQL block
• 50,000 execute calls for the SQL in the PL/SQL code

So now you will have 10,005 parse calls and 60,000 executes (plus many fewer round-trips between client and server). The parse calls (be they hard or soft parses) are extremely expensive. Having this one application instance cut down the number of parse calls to 20 percent of what it used to be will have an impact on performance and scalability—a profound impact.

In short, the only way to really affect the execute-to-parse ratio is to change the number of execute or parse calls—and this is something the application developer has to do. No magic parameter can be set.

Memory Versus Temporary Space

I’m trying to get a handle on what kinds of operations the database does in the TEMP space versus what it does in the program global area (PGA) memory. A consistent word I hear for the kinds of operations that occur in both areas is sorting. I know that GROUP BY operations, as well as hash joins and analytic functions, occur in the PGA. What does that leave for the TEMP space?

As a companion to this question, assuming a query is parallelized, what is actually going on in the LARGE_POOL? I realize that it contains parallel execution messages, but I’m not certain what that really means.

Older Oracle Database releases used to use sort_area_size and hash_area_size parameters to control how much PGA memory could/would be used before swapping data out to disk (TEMP). But I’m not going to talk about PGA memory management in older releases; I’m going to talk about automatic PGA memory management, which is much more dynamic.

So let’s say you run some SQL, and this SQL is going to do some operation such as

• Hashing
• Sorting (order by, analytics, sort distinct, group by sort, and so on)
• Anything that needs “memory”

Oracle Database will allocate a work area (a sort area, a hash area, whatever). The size of this work area will be determined by Oracle Database based on the current workload on the system.

If this work area (in your PGA memory) fills up and you are not done with the operation (the sort, the hash, whatever), Oracle Database will swap the contents of that out to disk—a write to TEMP. Later the database will read the contents of TEMP back in. TEMP works like virtual memory in a sense: the database will page your PGA work area out to it and page it back in. (It is not true virtual memory—that is just an analogy.)

All the operations you mentioned—group by, hash joins, analytic functions—occur in the PGA but could definitely be swapped out to TEMP.

As for the large pool, Oracle Database uses that in parallel queries to send messages back and forth. The parallel execute servers are in different processes—they cannot talk directly to each other, because they each have their own address space. Hence, one process will put some data into the large pool (a piece of shared memory everyone can access), and another will read it from there. It is just a shared-memory structure that every Oracle process can read from and write to.

More on the PGA

OK, I understand the above now. You wrote, “The size of this work area will be determined by Oracle Database based on the current workload on the system,” but what if I don’t have lots of users or a highly concurrent workload? How does this all work then?

In general, you have many users. Automatic PGA memory management is designed for an environment in which you have between 0 and an infinite number of users, and it is designed to allocate memory up to the target level in a “fair” and “sharing” way.

The goal of automatic memory management is to not use all of the memory all at once. The idea is to hold some in reserve, for the very possible condition of “more users need some right now” and to avoid “but we’ve given it all to Joe over there.”

The following is an excerpt on this topic from my book Expert Oracle Database Architecture. (The content has been edited for space and Oracle Magazine product name and style conventions.)

Choosing between manual and auto memory management. So, which method should you use: manual or automatic? My preference is to use the automatic PGA memory management by default.

One of the most perplexing things for a DBA can be setting individual memory management parameters, especially parameters such as SORT|HASH_AREA_SIZE. There is a lot of confusion over how big or small these values should be. Not only that, but the values you would like to use for them might vary over time as the day goes by. At 8:00 a.m., with two users, a 50MB sort area size might be reasonable. However, at 12:00 p.m. with 500 users, 50MB might not be appropriate. This is where the WORKAREA_SIZE_POLICY = AUTO setting and the corresponding PGA_AGGREGATE_TARGET come in handy. Setting the PGA_AGGREGATE_TARGET, the amount of memory you would like Oracle Database to feel free to use to sort and hash, is conceptually easier than trying to figure out the perfect SORT|HASH_AREA_SIZE, especially because the perfect value varies by workload.

Historically, the DBA configured the amount of memory used by Oracle Database by setting the size of the system global area—the buffer cache; the log buffer; and the shared, large, and Java pools. The remaining memory on the machine would then be used by the dedicated or shared servers in the PGA region. The DBA had little control over how much of this memory would or would not be used. That person could set the SORT_AREA_SIZE, but if there were 10 concurrent sorts, Oracle Database could use as much as 10 * SORT_AREA_SIZE bytes of RAM; if there were 100 concurrent sorts, Oracle Database would use 100 * SORT_AREA_SIZE bytes, and so on. Couple that with the fact that other things go into the PGA, and you really don’t have good control over the maximal use of PGA memory on the system.

What you would like to have happen is for this memory to be used differently as the memory demands on the system grow and shrink. The more users, the less RAM each should use. The fewer users, the more RAM each should use. Setting WORKAREA_SIZE_POLICY = AUTO and PGA_AGGREGATE_TARGET to the maximum amount of PGA memory the database should strive to use is just such a way to achieve this. Oracle Database will distribute this memory over the active sessions as it sees fit. Further, with Oracle9i Database Release 2 and up, there is even a PGA advisory (part of Statspack, available via a V$ dynamic performance view and visible in Oracle Enterprise Manager), much like the buffer cache advisor. It will tell you over time what the optimal PGA_AGGREGATE_TARGET for your system is to minimize physical I/O to your temporary tablespaces. You can use this information to either dynamically change the PGA size online (if you have sufficient RAM) or decide whether you might need more RAM on your server to achieve optimal performance. Are there times, however, when you won’t want to use automatic PGA memory management? Absolutely, and fortunately they seem to be the exception and not the rule. The automatic memory management was designed to be multi-user “fair.” In anticipation of additional users joining the system, the automatic memory management limits the amount of memory allocated as a percentage of the PGA_AGGREGATE_TARGET. But what happens when you don’t want to be fair, when you know that you should get all of the memory available? Well, that would be the time to use the ALTER SESSION command to disable automatic memory management in your session (leaving it in place for all others) and to manually set your SORT|HASH_AREA_SIZE as needed. For example, that large batch process that takes place at 2:00 a.m. and does tremendously large hash joins, some index builds, and the like? It should be permitted to use all the resources on the machine. It does not want to be “fair” about memory use—it wants it all, because it knows that it is the only thing happening in the database right now. That batch job can certainly issue the ALTER SESSION commands and make use of all resources available. So, in short, I prefer to use automatic PGA memory management for end-user sessions—for the applications that run day to day against my database. Manual memory management makes sense for large batch jobs that run during time periods when they are the only activities in the database. Tom Kyte is a database evangelist in Oracle’s Server Technologies division and has worked for Oracle since 1993. He is the author of Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005) and Effective Oracle by Design (Oracle Press, 2003), among others.