A developer friend of mine sent me an email today frustrated that none of the DBA’s he worked with could explain this error to him and sent it onto me. I thought I would get a post out with this one as I think it trips up a lot of folks:

“I have a report query that worked fine on Wednesday and today produced a ‘single-row query returns too many rows’ error. So when I went to track it back through Toad, the query just flat out stopped working. ”

Here was my explanation and a few hints to correct the problem:
It’s not your query, per say, but your data and you may have to change a query to take into consideration the change in data, even if it isn’t the one that you think you need to.

The error you are receiving is due to a subset of data that it requires that used to return only one row that now returns two, (which is what the error text is trying to tell us.) It requires one row to ensure the values that are being passed to it to fulfill the statements that rely on this data. Sometimes you can simply put a “distinct” into your select and fix the problem, but before you can decide that, what you are looking for is some pre-required query that is used to fulfill the failing statements variables, so let’s say, (just making up a coding scenario here…)

Procedure P_UPD_MEMBER is(p_mbr_cd);
and begins:

Cursor v_mbr_id is:
Select member_id from members where member_cd=p_mbr_cd;

This query would then be utilized to populate the member_id field for the next 1, 2, 3,etc. statements as it runs through the procedural code.

The problem is, the data has changed and due to a three column requirement on the primary key, they are allowed to have non-unique member_id’s where in the past they were unique. Let’s say our p_mbr_cd that we passed into our procedure is ‘CTGIS’. If we query the table and it returns the following, we’ve got a problem:

So now, your query for your cursor is returning two rows instead of one, invalidating your cursor’s requirements for one row, so when the next statement, let’s say it looks something like:

Update MEMBER_DIM
……
where member_id=v_mbr_id;

Guess what you get? ORA-01427: single-row subquery returns more than one row!! How can it place two values into the v_mbr_id of this statement?

To correct the issue, first diagnose what the problem is:
Look at the statement that populates each of your variables for the statement that is failing.

Run the select and see if more than one row is returned.
If more than one row does return, then inspect the data and see if there is an easy change to your select statement for the cursor that will ensure it returns one row and you will be back in business.
If not, then go back to the people that are loading the data and tell them to stop screwing it up! You will then need to go back, correct the data issue and rerun the procedure to have it complete successfully.

The Challenge: You want one process in your application to use a separate temp tablespace created on faster “ram disks” even though it executes the same packages on the same objects as the other applications.

The complication in the challenge is that TEMP is not like other resources such as default tablespace. Even though you might create a new user with a separate default temp tablespace, the database will allocate temp by the default tablespace of the owner of the package/objects involved. The object’s owner temp tablespace will then be used for all temp tables, hashing and sorting.

Now I’ve created a new user to utilize a new temp tablespace group I’ve striped across my new ram disks:

create user new_fast_user identified by “f4st_f0rwrd”
default tablespace users
temporary tablespace ram_temp;

To work around the package owner issue, you can still utilize the new user with the separate temp tablespace, but you will need to use invokers rights in the packages involved, (and if more than one package or other code)and add the authorization to use the current users defaults.

to perform this, you would add the following to the code involved:

CREATE OR REPLACE PACKAGE “INVOKER_PKG”
authid current_user <–This command invokes the current user’s default settings
AS
TYPE RefCurs IS REF CURSOR;
BEGIN
…
CREATE TABLE INVOKER_TEST_TAB AS
…

This command only needs to be before the declaration for the package, not in each individual procedure or function called in a package, btw.
Once you have all synonyms and grants in place for the new user, this is a simple way of allowing a specific user to have specific rights to a unique, performance enhancing temp tablespace.

You can test this by executing the pkg for the new user:

select a.username, a.sid, a.serial#, a.osuser, b.tablespace, round((b.blocks*32768)/(1024*1024*1024)) gigs, c.sql_text
from v$session a, v$tempseg_usage b, v$sqlarea c
where a.saddr = b.session_addr
and c.address(+)= a.sql_address
and c.hash_value(+) = a.sql_hash_value
order by b.tablespace, b.blocks desc;

before “authid current_user” is added:

NEW_FAST_USER 821 1907 oracle TEMP 27 create table invoker_test_tab

and then after with “authid current_user” added and recompiled in the package:

NEW_FAST_USER 821 1907 oracle RAM_TEMP 30 create table invoker_test_tab

We’re now using the NEW_FAST_USER’s temp tablespace and not the owner of the package’s temp tablespace!

This is a subject that I see a lot of debate on, but as a DBA who works in mostly large data warehouse and mart environments, I am a pro-rebuild DBA.

Many of the large systems I work on are home-grown and designed. This means that there is either design or code issues that lend itself to poor choices that can leave high fragmentation and ridiculouly over-sized indexes. I have rarely seen this in smaller, OLTP environments, but offer me a large table in a warehouse that somebody has mistakenly decided to stage data to and I will bet you that the index has the potential to be larger than the table. Yes, you heard me, larger than the object it is only a partial representation of.

Homegrown systems are not the only culprits of this, either. Oracle Business Suite is a major offender of this scenario. I love every chance I get to be an Apps DBA. There are so many challenges and situations where Oracle broke it’s own “Ten Commandmants” of Oracle database design. This is my kind of heaven, since I get to take it apart and put it back together the right way, (well, as much as Oracle will let you when we’re talking the business suite!) If you are looking for indexes that can grow larger than the tables they represent, check out the PO, (Purchase Order) indexes. Due to the way PO insert, updates and continually deletes on what can grow to be quite large tables, the indexes can quickly become fragmented.

This subject came up after one of my fellow DBA’s and I were looking at a poor performing query with large waits on sequential reads. Due to the logic in the code, we decided to do a quick check on the table/index size to determine if it was time to partition this table and the waits on the index.

select event,p1text, p1, count(*), sum(time_waited) waiter
from v$active_session_history
where session_id = 697
and event = ‘db file sequential read’
group by event, p1text, p1
order by waiter desc;

EVENT P1 TEXT P1 COUNT(*) WAITER
—————————————- ———- ———- ———- ———-
db file sequential read file# 61 3158 147953231
db file sequential read file# 52 3031 140491700
db file sequential read file# 53 2123 75409199
db file sequential read file# 96 137 3999203
db file sequential read file# 124 141 3435120
db file sequential read file# 86 117 3224430
db file sequential read file# 85 92 2495747
db file sequential read file# 116 92 2009065
db file sequential read file# 76 69 1935742

select file_name, tablespace_name from dba_data_files where file_id in (52,53,61);
FILE_NAME TABLESPACE_NAME
——————————————— ——————————
/u14/oradata/prodbase/email_index_01.dbf EMAIL_IDX
/u15/oradata/prodbase/email_index_02.dbf EMAIL_IDX
/u16/oradata/prodbase/email_index_03.dbf EMAIL_IDX

SELECT SEGMENT_NAME, BYTES/1024/1024 from DBA_SEGMENTS
WHERE SEGMENT_NAME LIKE ‘EMAIL%’
AND OWNER=’XXX’
AND SEGMENT_TYPE IN (‘TABLE’,'INDEX’);

SEGMENT_NAME MB
————— ————-
EMAIL_TBL 12478
EMAIL_IDX1 13822

Ready to be partitioned? Considering Oracle recommends investigating anything 2-4GB for partitioning benefits, I’d say yes.

Rebuild the index? Why would anyone NOT rebuild this index??

Investigate the code that is causing the serious fragmentation?
Yes, YES!! My choice would be to first patition the table, creating a local index, then investigate the code with recommendations changing from inserting directly into the main table to creating a staging table that performs all massaging of data first, then create the index and exchange partition. If it also needs saying, ensure the code updates the statistics before the exchange on the partition to ensure solid statistics for the objects involved.

With this size of a table, this type of fragmentation and waits, it would be asking only for poorer performance in the long run if you did not advise your developers and management of what they need to hear.

You have a statement that somebody, without thinking about resources, requested 96 total parallel processes for. They have now come to you, the DBA and want to know why, they aren’t getting the parallel they have requested:

The statement starts out something like this:
create table table_too_large_data
tablespace mart_data1 compress nologging pctfree 0 parallel 16
as
SELECT /*+ use_hash(i itmaff) parallel(i, 16) parallel(itmaff, 16) */
last_name ,…

The developer who wrote it, did not take the parameter PARALLEL_THREADS_PER_CPU into consideration, so they didn’t even realize they have requested pretty much everything above *2..

As a DBA, you should first query the following:
select * from V$PX_PROCESS_SYSSTAT;

Parallel Servers VALUE
Servers In Use 48
Servers Available 0
Servers Highwater 168 <– Max that could ever be allocated

The Highwater will match the parameter PARALLEL_MAX_SERVERS. This value is the max slave processes that can be spawned, but this does not guarantee that there are enough resources to spawn this many slaves, so keep that in mind. the servers in use tell you how many are being utilized currently and available tells you how many can be allocated- which at this time, is 0.

It is important to next see, how much has degradated? Is this the only process or are their others? If your environment utilizes a lot of parallel, it is important to track this information and keep track of how your parallel processing is performing or if it is being “abused”.

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE ‘%PARALLEL OPERATIONS%’
OR UPPER (NAME) LIKE ‘%PARALLELIZED%’ OR UPPER (NAME) LIKE ‘%PX%’;

NAME VALUE
queries parallelized 56083
DML statements parallelized 6
DDL statements parallelized 160
DFO trees parallelized 56249
Parallel operations not downgraded 56128
Parallel operations downgraded to serial 951
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 119
Parallel operations downgraded 1 to 25 pct 2

As you can see from the above information, there have been almost 1000 processes downgraded to serial- no parallel at all, even though it was requested. Also note how many other have been downgraded by percentage. This number is calculated by the number requested, not the calcuation for PARALLEL_THREADS_PER_CPU, (which is most often set to a value of 2) so realize, degradation may be worse than it appears from the results in this query.

You should then examine what processes are getting parallel, tying up the resources and figure out how best that they can “play nice together”. This means parallel hints are set to a degree that fits the worst case scenario for the load on the database, not as if this were the only process running.

OEM Reports that a server load is critical and top confirms this-
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14721 oracle 15 0 4243m 201m 196m S 1.7 0.6 0:43.83 oracle
14998 oracle 15 0 8338m 47m 42m S 1.3 0.1 0:00.44 oracle
14713 oracle 15 0 4248m 2.8g 2.8g S 0.7 8.8 0:53.50 oracle
14715 oracle 15 0 4243m 2.8g 2.8g S 0.7 9.1 0:55.10 oracle
13734 oracle 16 0 4241m 693m 690m D 0.3 2.2 0:02.96 oracle
13744 oracle 15 0 4241m 697m 693m S 0.3 2.2 0:02.97 oracle

Upon inspecting the database, you find that this isn’t due to any queries, but caused by SMON as it performs a parallel recovery after another DBA has killed a transaction that was performing less than optimally.

Captured in the alert log:
Parallel Transaction recovery caught exception 30319 Parallel Transaction recovery caught error 30319 Dead transaction 0x000a.05c.00003e84 recovered by 48 server(s)

to resolve the situation, update the fast_start_parallel_rollback parameter in the database to “low” so smon won’t spawn so many recovery processes and let the recovery complete.

method_opt=>’for all columns size 1′

This is the method option for DBMS_STATS.GATHER_TABLE_STATS that is commonly recommended- “Let Oracle look at all the columns and decide what kind of statistics, histograms, etc. need to be gathered on each…” I’ve never been real thrilled about it, especially in a data warehouse or mart environment where we rarely utilize more than a few columns per table in our where clauses. Really, why does Oracle need so much specific info on columns it’s not doing anything other than selecting? We have a set of columns that we consistently use in our where clause- these are the ones that we need info on and the basic table info…

Here’s where this common recommendation becomes a potential disaster in a largely partitioned environment:
This can cause the cluster to grow out of control in the SYSTEM tablespace that manages statistics and histograms on partitioned columns! There is a “feature” in Oracle that causes this to happen if you have a heavily partitioned database, reuse partitions or use exchange partition.

select segment_name, bytes/1024/1024 MB from dba_Segments
where tablespace_name=’SYSTEM’
order by bytes desc;

SEGMENT_NAME MB
C_OBJ#_INTCOL# 2755 <–Cluster created from the tables and indexes that contain column info stats and yes, this is in SYSTEM!
HIST_HEAD$ 280
I_H_OBJ#_COL# 216
I_HH_OBJ#_INTCOL# 144
I_HH_OBJ#_COL# 144

The only way Oracle states you can *safely* correct this is to rebuild you database from scratch. You can’t shrink this, rebuild it, etc. I tested this fully in one of the marts, since these databases are rebuilt from scratch each new mart and if we set up larger tables to use this method of collecting statistics, we quickly ran up a 13GB SYSTEM tablespace, which may not seem like much, but for SYSTEM, that is outrageous and will make your data dictionary SLOW AS MOLASSAS… 

The safest and best choice for method_opt, due to the fact that we pretty much ALWAYS index our partition key is:
method_opt=>’for all indexed columns’ or collect stats on just the indexed columns, those are the columns in our where clauses…

for new tables that are partitioned without indexing, (which will be our goal with a star schema, we will be looking at the partitioning key and grouping by this for dynamic scripts or for in code:
method_opt=>’for columns size <# buckets if desired>’ collect stats/histograms on our partition key/and or any columns we add to the method_opt that is consistently needed in our where clauses.