Hi,
This article will include a couple of great tips…one using a hint I work with in my SQL Tuning class and the other solving a performance issue with partitioning using PL/SQL Dynamic Execute Immediate feature!
First, a little background. This assignment was one of my common 3-day performance tuning assignments. I can usually find the performance issues. I can usually solve the performance issues as well. Sometimes, I’m just giving advice on how to solve the performance issue. Sometimes the clients can’t change the source code. This is where flexibility becomes very important.
This client had a VERY large Oracle partitioned database. At the time, it was Oracle9. They loaded 10 million rows an hour to this database! It could never be taken down. There was no test or QA database either. There are a rolling 365 partitions, no indexes, no stats. The analysts typically looked at either a 3-day or a 5-day window for their data reporting needs.
Day 1 of Client Performance Tuning.
A Materialized View refresh was taking over 4 hours to run. I reviewed the code and associated Explain Plan. The SQL included a 2-table join and the Explain Plan showed that the Cost-based Optimizer (CBO) was selecting a Merge Join operation as the row counts were in the hundreds of millions, particularly on the one side.
My standard practice when I see a Merge Join operation is to add the ‘USE_NL’ hint to get it to do a Nested Loop operation. Sometimes you also need the LEADING or ORDERED hint to tell the CBO in which order to process tables in the FROM clause…I like the LEADING hint better because you specify the tables or table alias names in the hint and don’t have to change the SQL text. The ORDERED hint is an older hint that says take the tables in the order found in the FROM clause. Usually I don’t need either of these because the CBO usually gets the tables in the right order (based on statistics).
Make sure to check out the Gather Plan Stats blog I submitted this month. It discusses the importance of looking at the actual row counts associated with each line of the Explain Plan versus the Explain Plan you asked for (using a variety of methods including the Explain Plan button in Toad) because you asked for one. This plan is not the one it executed with. When working with problem SQL, you need the actual row counts when the SQL executed. You get these in a number of ways including the method showed in the Gather Plan Stats blog and a SQL Trace…that Toad can also do.
Without asking a bunch of questions, we added the hint.
IF the CBO takes the hint, there are two things that will happen:
- Performance will be much better
- Performance will be much worse
I wrote a Toad World article a year ago that shows how you can tell if your hints were considered, if they contained errors, and if they were used.
I didn’t give the environment a lot of thought. I assumed they would be running a test. Nope. The refresh happens sometime after midnight each day and this was live code! The next morning, the client was just thrilled because the MV took about an hour and a half to run…a significant savings in time. I commented on putting it into production. She said ‘That was production…’…wow…news to me!
That was my first Lucky Break on this site.
Day 2 of Client Performance Tuning
This day’s task involved looking at why they are consistently getting Buffer Busy Waits wait events on their large data loads.
The massive data loads were quite intrusive upon the system. If one didn’t get done inside of an hour, another kicked off, and they would start getting these Buffer Busy Waits wait events…which means…the buffer cache was not big enough to hold all the new rows and space was not getting cleared up fast enough either.
Commit points always comes up too…not sure it would help with this particular issue but these applications were all designed for maybe hundreds of thousands of rows, not millions…
Share Pool Info Script…you can ask me for the code (dan@danhotka.com ).
column psize format 999,999,999 heading 'Shared Pool Size'
column fspace format 999,999,999 heading 'Free Space'
column pfree format 99 heading '% Free'
SELECT to_number(p.value) psize, s.bytes fspace, (s.bytes/p.value) * 100 pfree
FROM v$parameter p, v$sgastat s
WHERE p.name = 'shared_pool_size'
AND s.name = 'free memory'
and s.pool = 'shared pool'
/
You can run these in Toad. You can run these using the Quest Script Runner as well. My scripting tool of choice is SQL*Plus…I’m a Neanderthal when it comes to data processing (what we used to call IT)…yes…Mr. Character-mode himself. Don’t get me wrong, I love today’s tools but back then…what I loved about Windows 3.1 (came out around Oracle7) the best was 2 VT200 emulators running on a single device! Back in the day, PC’s cost close to $2000, and these VT100 and VT200 dumb ASCII green screen direct-connect terminals were cheap at $400 each.
Anyway, a little formatting but this script will show the size of the shared pool and how much is currently in use.
We ran the script and it showed a Library Cache size of 2GB and they were using about 500MB of SQL text in the course of their operations. So, there was easily a gig and a half available.
The Lucky Break was for the client this time. We/I found a considerable amount of memory that was already allocated to the Oracle RDBMS environment but was not currently being used.
My recommendation was to take 1GB of memory from the library cache and give it to the default buffer cache to help with these massive data loads.
They did take it under advisement. I do not know if they tried this when they recycled the database next. Real-time changes to these caches wasn’t available until Oracle10.
Day 3 of Client Performance Tuning involved using Dynamic SQL to solve a pesky partitioned SQL problem. I’ll cover this one in a future PL/SQL problem solving blog.
Luck did show up here again, I was able to use Dynamic SQL to solve a common coding problem with SQL going against partitioned objects. Maybe this is more of my skills in being able to solve difficult SQL and PL/SQL performance issues.
Stay tuned!!!
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com