Archive for the ‘Tuning’ Category.
January 14, 2013, 7:33 pm
I know no one really likes the term “tuning” these days, but it’s a short catchy word that gets the idea across. So I’ll just stick with it for the title of this post.
Note that this is one of those posts that’s not really supposed to be about how to solve a particular problem. It’s really just a story about a distraction that I ran into and I how I thought about getting around the issue and then ultimately resolving the root cause. Maybe you will find it instructive to see the process.
So I have this script that I use occasionally (paramon.sql) to see what parallel query slaves are doing. Unfortunately the script doesn’t have a header in it, but I’m pretty sure I lifted it from Randolf Geist. I can’t find it on his blog anywhere, but it looks like his style of writing SQL, and PX Query is something he’s written a lot about, so I’m pretty sure that’s where I got it. (Update: see Jonathan Lewis’s comment below attributing the script to Andy Brooker) Anyway, the script has worked great for me in the past but I recently noticed that it was really sluggish on a couple of 11gR2 DB’s running on Exadata. Here’s an example:
-bash-3.2$ !sql
sqlp
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 14 12:23:15 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- -------------------------- -------------------------- ------- ----------
dbm1 09-JAN-2013 03:25 14-JAN-2013 12:23 5.37 464246
SYS@dbm1> set SQLPROMPT "11.2.0.3> "
11.2.0.3>
11.2.0.3> @paramon
Enter value for status:
Node Name Status Pid Sid Parent OSUSER Schema CHILD_WAIT PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
P000 AVAILABLE 35
P001 AVAILABLE 36
P002 AVAILABLE 37
P003 AVAILABLE 38
P004 AVAILABLE 39
P005 AVAILABLE 40
P006 AVAILABLE 41
P007 AVAILABLE 42
P008 AVAILABLE 43
P009 AVAILABLE 44
P010 AVAILABLE 45
P011 AVAILABLE 46
P012 AVAILABLE 47
P013 AVAILABLE 48
P014 AVAILABLE 49
P015 AVAILABLE 50
P016 AVAILABLE 51
P017 AVAILABLE 52
P018 AVAILABLE 53
P019 AVAILABLE 54
P020 AVAILABLE 55
P021 AVAILABLE 56
P022 AVAILABLE 57
P023 AVAILABLE 58
P024 AVAILABLE 59
P025 AVAILABLE 60
P026 AVAILABLE 61
P027 AVAILABLE 62
P028 AVAILABLE 63
P029 AVAILABLE 64
P030 AVAILABLE 65
P031 AVAILABLE 66
32 rows selected.
Elapsed: 00:00:23.11 |
So on this 11g DB it took 23 seconds to run the query. On one of my 10g DB’s though the performance was stellar.
Continue reading ‘Tuning paramon.sql’ »
November 23, 2011, 2:15 pm
I had an interesting little project this morning. Of course it takes longer to write it down than to do actually do it, but it was kind of interesting and since I haven’t done a post in quite some time (and it’s the day before Thanksgiving, so it’s pretty quite at the office anyway) I decided to share. One of the Enkitec guys (Tim Fox) was doing a performance comparison between various platforms (Exadata using it’s IB Storage Network, Oracle Database Appliance (ODA) using it’s direct attached storage, and a standard database on a Dell box using EMC fiber channel attached storage). The general test idea was simple – see how the platforms stacked up for a query that required a full scan of a large table. More specifically, what Tim wanted to see was the relative speed at which the various storage platforms could return data. The expectation was that the direct attached storage would be fastest and the fibre channel storage would be slowest (especially since we only had a single 2G HBA). He tested ODA and Exadata and got basically what he expected, but when he went to test the database on the Dell he was surprised that it was actually faster than either of the other two tests. So here’s some output from the initial tests: First the Exadata. It’s an X2 quarter rack with one extra storage server. Note that we had to set cell_offload_processing to false to turn off the Exadata storage optimizations, thus giving us a measurement of the hardware capabilities without the Exadata offloading.
> !sqlp
sqlp
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 11:08:28 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@DEMO1> @uptime
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- ----------------- ----------------- ------- ----------
DEMO1 07-NOV-2011 12:37 23-NOV-2011 11:08 15.94 1377058
SYS@DEMO1> set sqlprompt "_USER'@'EXADATA'>' "
SYS@EXADATA>
SYS@EXADATA> ! cat /etc/redhat-release
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
SYS@EXADATA> ! uname -a
Linux enkdb03.enkitec.com 2.6.18-194.3.1.0.3.el5 #1 SMP Tue Aug 31 22:41:13 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
SYS@EXADATA> alter session set "_serial_direct_read"=always;
Session altered.
SYS@EXADATA> alter session set cell_offload_processing=false;
Session altered.
SYS@EXADATA> set autotrace on
SYS@EXADATA> set timing on
SYS@EXADATA> select count(*) from instructor.class_sales;
COUNT(*)
----------
90000000
Elapsed: 00:00:43.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3145879882
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 314K (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| CLASS_SALES | 90M| 314K (1)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1168567 consistent gets
1168557 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@EXADATA> set autotrace off
SYS@EXADATA> @fss
Enter value for sql_text: select count(*) from instructor.class_sales
Enter value for sql_id:
SQL_ID CHILD EXECS AVG_ROWS AVG_ETIME AVG_CPU AVG_PIO AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ----------------------------------------
b2br1x82p9862 0 1 1 43.00 3.16 1,168,557.00 1,168,567 select count(*) from instructor.class_sa
Elapsed: 00:00:00.08 |
So the test on the Exadata took 43 seconds to read and transport roughly 1 million 8K blocks. The same test on the ODA looked like this: Continue reading ‘Tuning Oracle to Make a Query Slower’ »
August 16, 2011, 9:45 am
I modified my create_1_hint_sql_profile.sql script (which I blogged about here: Single Hint Profiles) to allow any arbitrary text sting including quote characters. This is a script that I use fairly often to apply a hint to a single SQL statement that is executing in a production system where we can’t touch the code for some reason. For example, it’s sometimes useful to add a MONITOR hint or a GATHER_PLAN_STATISTICS hint to a statement that’s behaving badly so we can get more information about what the optimizer is thinking. I recently updated the script to allow special characters in the hint syntax. This feature is useful when you want to add something like an OPT_PARAM hint that takes quoted arguments. The change makes use of the q-Quote feature which I blogged about here: q-Quote. (the original version just barfed on quotes being input as part of the hint)
Here’s an example of how to use it:
SYS@SANDBOX1> alter session set cell_offload_processing=false;
Session altered.
Elapsed: 00:00:00.00
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;
AVG(PK_COL)
-----------
1849142.5
1 row selected.
Elapsed: 00:00:28.08
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg 0 2684249835 1 28.07 0 No .00 select avg(pk_col) from kso.skew3 where col1 < 0
1 row selected.
Elapsed: 00:00:00.02
SYS@SANDBOX1> @create_1_hint_sql_profile.sql
Enter value for sql_id: a6j7wgqf84jvg
Enter value for profile_name (PROFILE_sqlid_MANUAL):
Enter value for category (DEFAULT):
Enter value for force_matching (false):
Enter value for hint_text: opt_param('cell_offload_processing' 'true')
Profile PROFILE_a6j7wgqf84jvg_MANUAL created.
Elapsed: 00:00:00.07
SYS@SANDBOX1> @sql_profile_hints
Enter value for profile_name: PROFILE_a6j7wgqf84jvg_MANUAL
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
opt_param('cell_offload_processing' 'true')
1 rows selected.
Elapsed: 00:00:00.04
SYS@SANDBOX1> select avg(pk_col) from kso.skew3 where col1 < 0;
AVG(PK_COL)
-----------
1849142.5
1 row selected.
Elapsed: 00:00:05.11
SYS@SANDBOX1> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3 where col1 < 0
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a6j7wgqf84jvg 0 2684249835 1 28.07 0 No .00 select avg(pk_col) from kso.skew3 where col1 < 0
a6j7wgqf84jvg 1 2684249835 1 5.10 0 Yes 99.99 select avg(pk_col) from kso.skew3 where col1 < 0 |
In the example I turned off cell offload processing with the ALTER SESSION and ran a SQL statement that took 28 seconds. Then I used my fsx.sql script to verify that the statement was not offloaded and to find the SQL_ID. Next I created a 1 hint Profile with an OPT_PARAM hint that set the cell_offload_processing parameter back to TRUE using the new version of my create_1_hint_sql_profile.sql script. Next I used my sql_profile_hints.sql script to verify the text of the hint that was added to the Profile. It looked good including the quotes. When I executed the statement a second time it ran in 5 seconds. I then used fsx.sql again to see that the statement was offloaded for the second execution (child 1).
July 21, 2011, 9:58 am
I ran into an interesting issue last week having to do with plan stability. The problem description went something like this:
“I have a statement that runs relatively quickly the first time I run it (around 12 seconds). Subsequent executions always run much slower, usually around 20 or 30 minutes. If I flush the shared pool and run it again elapsed time is back to 12 seconds or so.”
The query looked a little like this:
Continue reading ‘Cardinality Feedback’ »
July 15, 2011, 5:31 pm
Karen Morton, Cary MIllsap and I will be participating in a on-line panel discussion about Oracle Performance on July 28th. Since we all worked together in the past we thought it would be a fun to listen to each other answer questions. Embarcadero is sponsoring this event and invited us to participate. Here’s a graphic from the mailer they sent out.
I only point it out because Cary and Karen look like they are posing for a picture, while I, as usual, look like someone just poured a drink down my pants. That’s normal though. I’ve been told I have a great face for radio.
You can sign up here: Register Now!
