Frederick Tang Weblog

Stories about Oracle concepts I have learnt from work, and the occasional brain-dump…

A very long hiatus

leave a comment »

It has been 10 months I haven’t made a new post on this blog. My life has taken quite a turn since October last year. I started a new job that involves more than just DBA work, but also managing IBM AIX systems. It’s production support work as well as project work, so it can get a bit tough going with the shift work and on-call duties. Blah! it’s all about challenges in life!

There’s been so much to do in this job, the majority of my time is spent on performance tuning on the data-warehouse and Informatica jobs running their ETL load. I have built up an Oracle Enterprise Manager Grid Control 10g infrastructure, but I want to move onto 11g soon. Right now, I am working on a project to upgrade our databases to 10g and 11g, and finding SQL Tuning Sets very useful. I also got to had a look into Real Application Testing.

It has been a great learning curve on this new job, and I am not finding time to read and write blogs as I used to. Maybe I will settle down soon, so hopefully, I will get back into blogging.

Written by fredericktang

August 20, 2010 at 3:39 pm

Posted in bdump

ORA-01652: unable to extend temp segment

leave a comment »

This is a short explanation about one scenario where a SQL query can result in an ORA-01652 error, for the benefit of a database user instead of a DBA. The following has been tested on an Oracle10g (10.2.0.4) database.

This week during a routine check of a pre-production database I built, I found a couple of ORA-01652 errors in the alert log, so I proceeded to extract an AWR report over the timeframe of the error, in the hope that I could guess which SQL caused the error. But to simplify the scenario for this post, I will show a small demo case instead.

An ORA-01652 error typically means that a SQL query required more temporary segments than the temporary tablespace can provide. Temporary tablespace is typically used for sort operations, such as joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics [source].

Let’s just show a simple example:

SQL> select * from mrt_alert order by alertname;
select * from mrt_alert order by alertname
              *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace FREDT_TEMP

The first thing that comes to mind might be report this error to your resident DBA, or simply extend the temporary tablespace to whatever is required. But how big do we need to extend this temporary tablespace to, so that our SQL query will work? Use Explain Plan.

SQL> explain plan for
  2  select * from mrt_alert order by songname;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2703675187

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   320K|   153M|       | 39661   (1)| 00:07:56 |
|   1 |  SORT ORDER BY     |           |   320K|   153M|   384M| 39661   (1)| 00:07:56 |
|   2 |   TABLE ACCESS FULL| MRT_ALERT |   320K|   153M|       |  5405   (2)| 00:01:05 |
----------------------------------------------------------------------------------------

9 rows selected.

The Explain Plan tells us the SORT ORDER BY operation requires 384MB of free temporary tablespace. The temporary tablespace is usually shared between other database users in the database. Remember to have uptodate statistics on the table, otherwise, the Explain Plan will show non-accurate information. In 10g, this is mostly done for you via the nightly dbms_stats scheduled job run.

Before shooting a request straight to our DBA to ensure the temporary tablespace have more than 384MB of free space, it’s necessary to just do a sanity check on our SQL query. For example:

1. How many rows are there in our table, so much that we need 384MB of space to sort?
2. Can we reduce the number of columns to view?
3. Can we limit our results by adding some conditions using the WHERE clause?

SQL> select count(*) from mrt_alert;

  COUNT(*)
----------
    320203
SQL> select bytes/1024/1024 as MB from user_segments              
  2  where segment_name='MRT_ALERT';

        MB
----------
       192

If we are only interested in one column for example, then we need less space for the SORT ORDER BY operation. As we can show below, we reduced the Temporary Segments required from 384MB to 19MB, but selecting only one column.

SQL> explain plan for
  2  select alertname from mrt_alert order by alertname;

Explained.

SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2703675187

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   320K|  7192K|       |  7618   (2)| 00:01:32 |
|   1 |  SORT ORDER BY     |           |   320K|  7192K|    19M|  7618   (2)| 00:01:32 |
|   2 |   TABLE ACCESS FULL| MRT_ALERT |   320K|  7192K|       |  5401   (2)| 00:01:05 |
----------------------------------------------------------------------------------------

9 rows selected.

It is possible to further reduce space required for the SORT ORDER BY operation, by adding some conditions using a WHERE clause. First let’s start with the demonstration before explaining what’s going on.

SQL> explain plan for
  2  select alertname from mrt_alert where alertname like 'A%' order by alertname;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1920725646

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |  3895 | 89585 |  5308   (2)| 00:01:04 |
|   1 |  SORT ORDER BY     |                |  3895 | 89585 |  5308   (2)| 00:01:04 |
|*  2 |   TABLE ACCESS FULL| MRT_ALERT      |  3895 | 89585 |  5306   (2)| 00:01:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ALERTNAME" LIKE 'A%')

14 rows selected.

We can see that the TempSpc column in our Explain Plan has disappeared. Shervin provided a very simple explanation here. Basically, when the disk based temporary tablespace is not required, the TempSpc column is not shown.

Metalink Note: 102339.1 Temporary Segments: What Happens When a Sort Occurs further explains “the sort area allocated inside the Program Global Area (PGA) is used first. If the sort operation needs additional memory, then the sorted rows are written to disk to free up the sort area so that it can be re-used for the remaining sort. This means that temporary segments are created.“. In simple words, if the sort can be done in memory, it won’t resort to disk.

So there, we have managed reduce the Temporary Segments usage from 384MB to 19MB, and with a WHERE clause, the SORT ORDER BY operation can be done entirely in memory, and we don’t even have to rely on Temporary Segments (i.e. 0MB). Therefore, we don’t have to talk to our DBA about adding more space to the Temporary Tablespace.

There is additionally one extra step we can do however, which can eliminate a SORT ORDER BY operation altogether. This is done by introducing a B*Tree index.

SQL> create index IX_MRT_ALERT_2 on MRT_ALERT(ALERTNAME);

Index created.

SQL> exec dbms_stats.gather_index_stats(user, 'IX_MRT_ALERT_2');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select alertname from mrt_alert where alertname like 'A%'
  3  order by alertname;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 249942460

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |  3895 | 89585 |    21   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IX_MRT_ALERT     |  3895 | 89585 |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ALERTNAME" LIKE 'A%')
       filter("ALERTNAME" LIKE 'A%')

14 rows selected.

As can be observed from the Explain Plan, Oracle uses Index Range Scan to retrieve results that satisfy our WHERE clause. The result of an Index Range Scan returns results in an order – in this case, a VARCHAR2 column – alphabetical order. Therefore, Oracle can avoid a SORT ORDER BY operation as the results is already ordered.

There’s a caveat though, in Metalink Note: 67409.1 When will an ORDER BY statement use an Index, “Oracle explains that the index is only used when the column must be NOT NULL, otherwise, the index will not be considered“. It then gives an explanation for this behaviour which I won’t copy and paste here.

Conclusion
I guess I have presented an over-simplified scenario here, and the SQL query you are testing might be a lot more complex than this, but it is important to just do some sanity checks before asking a DBA to increase database resource capacity.

I have shown some steps where it is possible to reduce Temporary Segments usage, and with a clever usage of an index, to complete remove the need to perform a SORT. Having said all the above, if you do require the additional Temporary Segments, then by all means, go for it!

Written by fredericktang

October 28, 2009 at 4:18 am

Posted in 10g, Oracle

sys_connect_by_path and ORA-01489

leave a comment »

This is strictly on an issue on a Oracle9i database. It has been fixed in Oracle10g.

Just would like to write a brief note about a test case we found with using sys_connect_by_path to concatenate values on a column from multiple rows into one string, on an Oracle9i database.

This technique has been discussed on a few websites:
http://www.williamrobertson.net/documents/one-row.html
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
http://www.oracle.com/technology/oramag/code/tips2006/101606.html

As we might all know, VARCHAR2 in Oracle 9i has a limit of 4000 bytes (note, not characters). This limits applies to string concatenation, and will (or should) result in ORA-01489: result of string concatenation is too long if the result of concatenation yields a string greater than 4000 bytes in length. Eddie has demonstrated some examples here: http://awads.net/wp/2005/12/06/more-on-generating-strings/

I discovered a difference in behaviour when concatenating strings using sys_connect_by_path on Oracle9i, between a Multibyte character set database and a Single-byte character set database, where I would get an ORA-01489 error at just over the 2000 bytes mark on a Multibyte database. For example, UTF8 is Multibyte and WE8ISO8859P1 is a Single-byte character set. (http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/appa.htm#956722)

There are a couple of ways to find out which character set the database is built on (requires dba privilege or select_catalog_role granted):

select value$ from props$ where name='NLS_CHARACTERSET';
select * from database_properties where property_name='NLS_CHARACTERSET';

We construct a simple test case, a table with an ID column, and a VARCHAR2 column. I am going to insert a fixed string of 10 characters into the VARCHAR2 column, then apply the string concatenation technique using sys_connect_by_path. I am not using any special characters, so each character should just be 1 byte. It is possible to get size of a varchar2 string using vsize().

create table test_concat_sys
( 
   id     number,
   str    varchar2(4000)
);

insert into test_concat_sys
SELECT 1 as id, '1111111111' RNDMSTR
FROM all_objects
WHERE rownum <= 200;

commit;

On a UTF8 character set database, this will work, and the last row will be 2002 bytes (182×10+182). That’s 182 rows of 10 bytes, plus 1 byte for each ‘/’ in the concatenation delimiter.

SELECT VSIZE(SYS_CONNECT_BY_PATH(str, '/')) as BYTES
FROM
(
   select A.*, row_number() over (partition by id order by id) row#
   from test_concat_sys A
   where rownum <= 182
)
START WITH row#=1
CONNECT BY PRIOR id=id and prior row# = row# -1;

This will result in an ORA-01489 error:

SELECT VSIZE(SYS_CONNECT_BY_PATH(str, '/')) as BYTES
FROM
(
   select A.*, row_number() over (partition by id order by id) row#
   from test_concat_sys A
   where rownum <= 183
)
START WITH row#=1
CONNECT BY PRIOR id=id and prior row# = row# -1;

The behavior is working as expected on a Oracle 9i Single-byte database or an Oracle10g (or later) release, and we can set rownum upto 363 rows (363×10+363 = 3993), one more row and it will return ORA-01489 as expected since we will reach the limit (364×10+364 = 4004).

I have logged a SR for this, and the reply from Oracle was that they “officially accepted this is the right candidate for bug.”, but “9i is in sustaining support mode and development will not provide any fix for this. Also this issue is fixed in 10g so they would not accept it.”

All the more reason to upgrade to 10g if not already done so. This bug is rather odd to hit because we wouldn’t normally expect to concatenate a string upto 2000 characters and beyond. But in our case, the business obviously did.

There are other ways to concatenate strings from multiple rows, they are also covered in the websites linked at the beginning of this article.

Written by fredericktang

October 21, 2009 at 1:38 am

Posted in 9i, Oracle

impdp ORA-06550 ORA-00917

with one comment

Just want to quickly write about this bug.

I created a Datapump export (expdp) of a partitioned table from an Oracle 10.2.0.3 database, and import (impdp) into an Oracle 10.2.0.4 database.

During impdp, everything worked well until importing statistics, then the process ran into the following errors:

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 12, column 17:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 4, column 121:
PL/SQL: SQL Statement ignored

Turns out this is a bug with expdp/impdp partitioned table, described in Metalink Note “Bug 6862987 – impdp throws an error when importing partitioned table stats”, which happens “when importing statistics for tables if there are global statistics only.”. This bug is fixed in the 10.2.0.5 Patchset. The workaround is not to import statistics.

I dumped the sqlfile and had a look what’s really going on, it turns out there was a repeated syntax error with some of the INSERT statements:

INSERT INTO “SYS”.”IMPDP_STATS” (type, version,
c1, c2, c3, c4, c5, n1,
n2, n3, n4, n5, n6, n7, n8, n9, n10, n11,
d1, r1, r2, ch1, flags)
VALUES (‘C’, 4, ‘HTTP_LOG’, ‘P20090921’, NULL, ‘PROTOCOL_ID’, ‘PBC_ADMIN’,
0, 0, 0, NULL, 0, NULL, NULL, 0, NULL, NULL, NULL,
‘, NULL), NULL, NULL, NULL, 0);

Written by fredericktang

September 14, 2009 at 12:28 am

Posted in 10g, Oracle

PL/SQL Developer Associate

leave a comment »

I have added another Certification to my bag, passing the 1Z0-147 Program with PL/SQL exam. I studied the Oracle documentation and used the Selftest software to prepare for this exam.

Written by fredericktang

August 5, 2009 at 4:23 pm

Posted in Oracle

Completed OCE and OCA

leave a comment »

I have taken a brief break from blogging for some personal reasons, and lately I have taken the time to pass and complete a couple of Oracle certifications: 1Z0-047 (Oracle Database SQL Expert Exam), and 1Z0-042 (Oracle Database 10g: Administration I Exam).

It’s certainly quite an experience. I must say on-the-job experience helped a lot, and reading up the Oracle documentation helped me to learn a lot of concepts that I obviously overlooked, such as restrictions and notes about a specific feature.

Written by fredericktang

July 6, 2009 at 12:11 pm

Posted in 10g, Oracle

Client side connect time load balancing for RAC

with 3 comments

Recently, I took an interest in studying how various load balancing features work in a 10g RAC. I thought Jim has written a very good article on this, in a language most people can understand. In my opinion, there are 3 types of load balancing on a 10g RAC. I say in my opinion because Run Time Load Balancing relies on Server Side Load Balancing configurations, but I have separated it out anyways.

  1. Client Side Load Balancing
  2. Server Side Load Balancing
  3. Run Time Load Balancing (using Implicit Connection Cache)

In this post, I wanted to test for evidence of Client Side Load Balancing, using Client Side Oracle Net Tracing.

Definition

Client Side Load Balancing is by definition “evenly spreads new connection requests across all listeners”, and “Oracle Database randomly selects an address in the address list and connects to that node’s listener. This provides a balancing of client connections across the available listeners in the cluster.” [source]. Another definition, “whereby if more than one listener services a single database, a client can randomly choose between the listeners for its connect requests. This randomization enables all listeners to share the burden of servicing incoming connect requests.” [source].

Client Side Load Balancing is configured by adding LOAD_BALANCE=ON in tnsnames.ora file. The Net Services Reference tells us how and were to embed this parameter – “embed this parameter under either the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.” [source]. Note however, whether you place LOAD_BALANCE under ADDRESS_LIST or outside, will make a different in terms of load balancing ADDRESS_LISTs or load balancing ADDRESSes.

Configuration

I have a 2-node 10g RAC cluster, and for load balancing, I have the following entry in my tnsnames.ora file.

MODDB=
 (description=
  (load_balance=on)
   (address=(protocol=tcp)(host=moddb1-vip)(port=2521))
   (address=(protocol=tcp)(host=moddb2-vip)(port=2521))
  (connect_data=
      (service_name=MODDB)
  )
 )

I want to trace the Client Side Load Balancing behaviour from the client side, so I enable Oracle net tracing by adding the following lines in sqlnet.ora [source], and I should fine trace files for each physical connection made under $ORACLE_HOME/network/trace.

TRACE_LEVEL_CLIENT = USER
TRACE_FILE_CLIENT = SQLTRC

Testing a connection

We can first test one connection…

$ sqlplus -s tester/tester@MODDB

exit

Now take a look at the trace file produced, under $ORACLE_HOME/network/trace. An inspection of of the trace file shows two entries that are of interest (it is possible to observe server-side load balancing as well, but that’s a topic for another post):

niotns: Calling address: (description=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb1-vip)(port=2521))(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))
nsc2addr: (DESCRIPTION=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))

Unfortunately, I couldn’t find much documentation on how to read a trace file, I can only guess…

* niotns entry shows a lookup of tns information based on the service name I supplied “MODDB”.
* nsc2addr entry shows Oracle Net connects to listener on moddb2-vip.

It’s difficult to observe load balancing with just one connection, so I wrote a script to run 1000 connections, using Bash Shell scripting:

#!/bin/bash

for a in {1..1000}
do
echo $a
sqlplus -s tester/tester@MODDB<<EOF
EOF
done

exit 0

Examining the traces

Extending the idea above, we can use a combination of grep, and wc to see the result of load balancing. For some reason, each connection produced 2 trace files, so we have 2000 trace files for 1000 connections.

$ ls -l *.trc |wc -l
2000

$ grep nsc2addr *.trc | grep load_balance |grep moddb1-vip |wc -l
498

$ grep nsc2addr *.trc | grep load_balance |grep moddb2-vip |wc -l
502

We can see over 1000 connections, 498 were made to moddb1-vip listener, whilst 502 were made to moddb2-vip listener. It is a fairly even distribution. But just how random is it? I have used the output of the trace files, and plot the first 100 connections on a scattered chart. The first note one can observe is that, these connections are not in a round-robin fashion. (1 = moddb1-vip; 2 = moddb2-vip)

timeplot

Conclusion

As demonstrated above, it is quite simple to setup Oracle Net tracing on the client, to test and show whether Client Side Load Balancing is working properly. It is worth to note (again), that this type of load balancing has nothing to do with balancing server side load. It is to do with balancing load across the listeners.

Written by fredericktang

March 13, 2009 at 5:26 am

Posted in 10g, 9i, Oracle

Follow

Get every new post delivered to your Inbox.