RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

These GTemp tables get loaded with tons of intermediary data and become a driving table for subsequent queries that join to the data residing in the GT to complete the processing to generate a final result that get is used to populate application tables.

Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Tuesday, February 19, 2013 5:21 PM To: jonathan_at_jlcomp.demon.co.uk
Cc: oracle-l_at_freelists.org
Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

(Side question: When you got my reply earlier, was it formatted when it hit your mailbox, because when I get it from the list, it has lost its formatting completely.)

I'm not sure I follow what you mean when you say those ITL waits can't be happening? You think the snapshots are incorrect in some way?

Let me give you the full scenario to see if it helps in any way.

At 23:55 I modify the AWR snapshots to start collecting at 10 minute intervals and I was (STRESS: was) collecting MAXIMUM topnsql - I've discovered that was very bad idea because lately I haven't been able to hardly generate an AWR report in a reasonable amount of time. It had gotten so severe that I've totally cleansed my AWR history/snapshots after taking a backup "just in case" though I seriously doubt I'll do anything with that AWR data that I wiped.

So, beginning at 23:55, I'm still collecting 10 minute snapshots but only top 300 SQL using topnsql.

Starting at midnight we have many sessions that kick off from an application server and get distributed across all 3 nodes. This is a very, very, very immature application we're talking about and the code is imaginably inefficient. We have made many strides since I got here in August but we have a long way to go to make this stuff shine.

So we have approximately 37 data load sessions that kick off, then as they finish we have 37 * 3 new sessions that run calculations (using the GTs) so we could have 111 sessions CONCURRENTLY processing all load data into the GT named object - I understand that each of those sessions gets its own "instantiation" of the GT table but somehow that's where the ITL waits are happening.

Does that help clarify at all?

I'll script that query to run every 10 minutes or so during the batch and capture what the temp usage is during the batch processing.

After tonight's run I should have a good clean AWR set of data to start getting AWR data from again as I couldn't hardly get anything from last week's runs. Got greedy trying to capture as many SQL statements as I could because I have a nice SQL script to query the DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT views to give me matching SQLs that share the same FORCE_MATCHING_SIGNATURE that helps me consolidate long running SQLs across all 3 nodes.

Chris

--
http://www.freelists.org/webpage/oracle-l