Archive for the ‘Tuning’ Category.
June 27, 2013, 8:44 pm
Since everyone seems to be all twitterpated about Oracle Database 12c this week, I thought I’d post a quick note to let you know that the slides from the presentation on 12c Adaptive Optimization I did at the Hotsos Symposium 2013 (with a lot of help from Maria) are now available in the Whitepapers / Presentations section of this blog.
While I’m on the topic, I found this little blurb in the Oracle Database 12c Release 1 New Features Guide:
That’s the section that talks about the Adaptive Optimization stuff. I think the documentation folks meant that they were describing performance features that didn’t require any manual intervention, but it sort of reads like the features are really easy to describe, or maybe that the writers weren’t going to work very hard on describing them. At any rate, the wording struck me as humorous. :)
June 20, 2013, 3:32 pm
In Part 1 of this series I talked about the basic problem, which is that plan_hash_values are not based on the whole plan. One of the main missing components is the predicates associated with a plan, but there are other missing parts as was pointed out in Part 1 of Randolf Geist’s post on the topic. At any rate, predicates seem to be the most critical of the missing parts.
The purpose of this second post on the topic is to talk about diagnosis. Basically how do you identify when some other part of a plan has changed that doesn’t affect the plan_hash_value, specifically a predicate.
So first I thought I would show a few examples of statements with the same sql_id and plan_hash_value that have other plan differences (in the predicate section). To do this I used a method proposed by Randolf Geist a few years back in his 2nd post on the topic which covered Alternative Ways to Calculate a PLAN_HASH_VALUE In that post, Randolf shows several ways to compute a hash value on any or all of the columns in the v$sql_plan table. I wrote a simple script around one of the those methods (find_pred_mismatch.sql), and as you might guess from the name, I limited this version to not include all the columns in v$sql_plan, but to only identify statements with mismatched predicates. To be more explicit, the script will locate statements in the shared_pool that have multiple child cursors, where there are more than one set of predicates to go with a single plan_hash_value. Here’s an example:
SYS@DEMO1> @find_pred_mismatch
Type created.
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER THE_HASH ARE_H
------------- --------------- ------------ ---------- -----
063m5s0cvrr19 1502175119 0 2709091620 DIFF!
093fgfvygm51m 3114044936 0 3689661040 DIFF!
0cn2wm9d7zq8d 1540383338 0 3746559344 DIFF!
0pt4jfmq9f1q0 3078496799 0 1309675335 DIFF!
155cwuv2pfp1d 768389713 0 2982291916 DIFF!
18c2yb5aj919t 1032442798 0 1714458756 DIFF!
1n9crga6mbw2x 4174841998 0 2752042257 DIFF!
1ytxrt5qp9qdd 2707146560 0 3757837347 DIFF!
23buxzfxyp1vy 3143617369 0 2089881285 DIFF!
23nad9x295gkf 891847141 0 4056778555 DIFF!
24zvjzuyrxh3w 1877711096 0 1680905434 DIFF!
28n17ru48jnh5 1665111388 0 3584687734 DIFF!
2j0fw19fph49j 1337823903 0 2431841938 DIFF!
2kd6nusgzc3uw 3151266570 0 3024843785 DIFF!
2rpwgryn7pxz5 3329544515 0 452505826 DIFF!
35nhk48nxwc0v 2553960494 0 117262982 DIFF!
3bc73t2h9mwxc 1420457580 0 1226225583 DIFF!
3gputsqv4u1j3 3161304745 0 2252819340 DIFF!
3zauy2zqryrsx 1420457580 0 1128973296 DIFF!
42q1qby3huf2c 3069437562 0 4008632079 DIFF!
47mm81hm9sggy 1836210496 0 1554180227 DIFF!
4g46czps3t55u 2714703948 0 4063401817 DIFF!
4n2gca427719q 1360319091 0 4013571180 DIFF!
4tpsnbkt1dm5j 2960949352 0 3341004783 DIFF!
5dyhfnkzta2zm 3767331201 0 4238766232 DIFF!
5h91zx386wbht 293199272 0 949799344 DIFF!
5s34t44u10q4g 2693539438 0 839739072 DIFF!
5uw1u291s3m0k 219265157 0 642280427 DIFF!
61tn3mam0vq0b 2012170170 0 2048362545 DIFF!
63t3ufgq37m0c 1155609947 0 844291465 DIFF!
69k5bhm12sz98 3091659676 0 356777601 DIFF!
6cp74g22fzahf 76968983 0 1617454724 DIFF!
6wm3n4d7bnddg 1772758172 0 1148123313 DIFF!
78kp0fcyxavzb 2960949352 0 1085639264 DIFF!
7ah4afrggrw5c 4213028598 0 4285032606 DIFF!
7g4rxwbvhdh3q 3170022080 0 2083442940 DIFF!
7hspvruktu52b 4016032974 0 2538340188 DIFF!
84p3g5b5bsfvn 681044650 0 3826083810 DIFF!
86521pa77y28j 3760090177 0 3887843475 DIFF!
8ak9gkw2mjhvr 1526940012 0 2946674232 DIFF!
8p9z2ztb272bm 408663731 0 3293625021 DIFF!
aca4xvmz0rzup 427901911 0 4215668999 DIFF!
akh9zqqkx3wj7 2306922995 0 2084689096 DIFF!
akx4284f2vjnv 3948068913 0 2662025793 DIFF!
amycufzt6uq5f 3283312188 0 1896511712 DIFF!
atnkqhrp3t7xa 2196914545 0 26873046 DIFF!
aw2x7hh2a9ag0 1148557212 0 719001678 DIFF!
b41wak2bb7atw 108532975 0 1699960507 DIFF!
bhvyz9bgyrhb2 1134671139 0 2402404248 DIFF!
c8gnrhxma4tas 4024720576 0 2473084105 DIFF!
cc7vvmrsxzyq1 1849127868 0 1912933403 DIFF!
cjtaqp92v10bn 922118807 0 2313573387 DIFF!
ckfgcyv05xptf 2869192503 0 3932622422 DIFF!
cw860p03hy5ff 1502175119 0 2915988156 DIFF!
cyw0c6qyrvsdd 192117504 0 2551186960 DIFF!
d53nc7j6n1057 1356236608 0 582788179 DIFF!
dyj1ssw8jw54f 1836210496 0 66902761 DIFF!
fkjkrv5ycz96u 2247257083 0 1809299677 DIFF!
gdn3ysuyssf82 4024720576 0 2473084105 DIFF!
gwbdd5m45ugpm 3180770434 0 235716193 DIFF!
60 rows selected.
SYS@DEMO1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'));
Enter value for sql_id: 24zvjzuyrxh3w
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 24zvjzuyrxh3w, child number 0
-------------------------------------
SELECT script FROM sys.metaxsl$ WHERE xmltag=:1 AND transform=:2 AND
model=:3
Plan hash value: 1877711096
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS STORAGE FULL| METAXSL$ | 3 | 99 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(("XMLTAG"=:1 AND "TRANSFORM"=:2 AND "MODEL"=:3))
filter(("XMLTAG"=:1 AND "TRANSFORM"=:2 AND "MODEL"=:3))
SQL_ID 24zvjzuyrxh3w, child number 1
-------------------------------------
SELECT script FROM sys.metaxsl$ WHERE xmltag=:1 AND transform=:2 AND
model=:3
Plan hash value: 1877711096
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS STORAGE FULL| METAXSL$ |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(("MODEL"=:3 AND "TRANSFORM"=:2 AND "XMLTAG"=:1))
filter(("MODEL"=:3 AND "TRANSFORM"=:2 AND "XMLTAG"=:1))
Note
-----
- rule based optimizer used (consider using cbo)
44 rows selected.
Continue reading ‘SQL Gone Bad – But Plan Not Changed? – Part 2’ »
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).
