Thursday, November 27, 2014

Adjusted Block Counts on Partitioned Tables without Partition Level Stats and IN-LISTS


An article on 10053 Trace Files - Global Stats on Partitioned Tables by Doug Burns is a good introduction to my problem.  Doug has included excerpts from the 10053 trace covering a few variations, but one that appears incomplete is the case where there are:

  • Global stats
  • No partition-level stats
  • Single partition being queried
  • IN-LIST Query
In this situation I have a few differing results

This is what I don't want, a block count of 1, as that means a full table scan is always going to be better than an index scan.  A full table scan is perfectly fine *if* there is actually only 1 block, but there's actually whole lot  more - the optimizer got it wrong.  Worse, good old bind variable peeking means that this peeked full table scan plan will also get used on partitions that contain lots of data.


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: INVOICE  Alias: INVOICE  (Using composite stats)
  (making adjustments for partition skews)
  ORIGINAL VALUES::    #Rows: 343372313  #Blks:  8296936  AvgRowLen:  142.00
  PARTITIONS::
  PRUNED: 1
  ANALYZED: 0  UNANALYZED: 1
    #Rows: 343372313  #Blks:  1  AvgRowLen:  142.00

My findings though are somewhat uncertain.

I have a test table with 3 partitions (two large, one small), a locally partitioned index on the partitioning key, and global stats.  Then I query the small partition with

SELECT * FROM test WHERE id in (2000001, 2000002)

Variation 1 (as above): The optimizer adjusts to use 1 block.

Variation 2: If I create the test table, then modify it (changing the partitioning key to NOT NULL), then the optimizer appears to adjust to use the actual number of blocks in the segment.

Variation 3: If I create the test table with a NOT NULL partitioning key, the optimizer adjusts to use 1 block.

I haven't been able to work out how why variation 2 is different.  This is the result I want ALWAYS.
Peter.

Saturday, July 6, 2013

A Joomla Bug Squad lesson on JLayouts

Or... How I learnt about JLayouts in 10 minutes, and why contributing to the Joomla Bug Squad can be a valuable learning experience.


JLayout solves the problem of hardcoded markup.  

JLayout provides a simple mechanism for html markup to be moved 
into a separate file which can then be overriden by templates.

First some background about me.  I'm fairly new to Joomla, have only started about a year or two ago with Joomla 1.6 after needing a flexible, Open Source, Content Managment System (CMS) with Access Control (ACL).  Soon after this an article was written, by Mark Dexter I think, encouraging people to join the Joomla Bug Squad as a good way of learning Joomla. So that's exactly what I did, and it's been a valuable and rewarding experience.

Once such experience was hearing about and learning about JLayouts, developed by Yannick Gautlier in September 2012 to fix a bug.  It started with Jacques Rentzke and a question about Administrator Templates, Bluestork, and Bootstrap.  The mention of Bootstrap brought a few other comments on the technical merits and implications for layout markup.  At this point,  Michael Babker chimed in with JLayouts as the simple solution to the markup dependencies.  With Seth Warberton joining the discussions it's no surprise that the difficulties of hardcoded bootstrap markup received a bit more focus.  Michael again mentioning JLayouts, Jacques finding some documentation on JLayouts, agreement that it would be great if JLayouts were everywhere.  But... isn't that going to take a LOT of work?  What's required to get JLayout working?

Start the clock, the lesson starts, and Michael says, "gimme 10 and I'll whip up a JToolbar conversion".  Elin Waring starts by pointing to existing code examples where JLayouts are used, first the layout (layouts/joomla/edit/details.php), next the invocation (administrator/components/com_contact/view/contact/tmpl/edit.php).  The layout can then be overridden in the template (/administrator/templates/hathor/html/layouts/joomla/edit/details.php). Then, right on 10 minutes, Michael comes back with a fully worked and easily understandable example converting the JToolbarButtonConfirm class to rendering markup using JLayout, delivered as a single github link to a commit showing all the changes.  A little later Michael comes back with a more complete JToobar.

And we're all very much the wiser as a result.  To give back, I do some testing, I thank the main players for the lesson on JLayouts, and Jacques suggests a JCM article.

The takeaway... This article hasn't attempted to teach anyone how to use JLayouts, but hopefully it might inspire the reader to read further, and maybe even contribute to the Joomla Bug Squad.

A simple example based on the JLayout documentation.

Create a layout file, /layouts/joomla/content/helloworld.php:

defined('JPATH_BASE') or die;
 
?>

$text = $displayData['text'];

Hello

Then in any file, such as components/com_content/views/article/tmpl/default.php add:

$data = array();
$data['text'] = JText::_('World!');
$layout = new JLayoutFile('joomla.content.helloworld');
echo $layout->render($data);
 ?>
View an article on the front end and you will see Hello World!.
To override, copy the layout file templates/mytemplate/html/layouts/joomla/content/helloworld.php:

$data = array();
$data['text'] = JText::_('World!');
$layout = new JLayoutFile('joomla.content.helloworld');
echo $layout->render($data);
?>
The JToolbarButtonConfirm example by Michael:
Some  complete further expansion of the JToolbar conversion to JLayout:
The JLayout documentation:
Further JLayout enhancements by Roberto Segura to come:



Tuesday, June 11, 2013

Streams Pool only for Streams? Think Again!

Here's some enlightenment from Arup Nanda.

The Arup Nanda Blog: Streams Pool is only for Streams? Think Again!

The streams pool isn't only used for streams.  It's also used by the following.

  • Data Pump
The streams pool will be dynamically allocated, defaulting to 10% of the shared pool size, and stealing from the buffer cache.

So... what else uses the streams pool ?  We have a 11.2.0.3 database where the streams pool is dynamically allocated on startup by "something".  That "something" is the same process as identified by this log message, identified in the session trace by an sqlplus session.

Alert Log

  • [30114] Successfully onlined Undo Tablespace 2.
Session Trace
  • *** MODULE NAME:(sqlplus ...




Friday, July 20, 2012

What's the meaning of the asterisk in Oracle's ORATAB file?


The perplexing asterisk is something Oracle DBAs have all seen when editing /etc/oratab for years.  But how many are old enough to know what it means, and more importantly if it is still required, and what it should be set to, especially when multiple ORACLE_HOMEs are used.

It's somewhat pleasing that I haven’t seen it in any installations lately but it remains on some machines where prior Oracle versions were installed and have been upgraded.

The best suggestion I found from searching the web was to look in the oraenv script.  From the header of the script…  An asterisk '*' can be used to refer to the NULL SID.  So now you know. J  And if your're like me you’re still just as confused.  Join the club.  Nothing in the oraenv script actually does anything with it.

There is some further information in the dbhome script.  It actually uses it, and when explicitly passed a "" (i.e. dbhome "") then you are returned the ORACLE_HOME matching the line with the asterisk.   But oraenv won't accept the "" so it’s pretty academic.

A web search for “NULL SID” offers some better insights.  It returned Oracle 7 document where it stated that a NULL SID was not supported by SQLNET V2.  So it looks like it was something use back in the very early oracle versions before the ORACLE_SID was introduced to identify a database, and something that hasn't been used for years.

So, what should you do when you find a line with an asterisk in an oratab file?  Remove it and stop the confusion.

Wednesday, July 4, 2012

HTML5 Sections

The HTML5 specification defines new sections and provides various scenarios where they can be used.  What it doesn't do is provide a "complete" outline, so here goes...


  • body 
    • header
      • hgroup
        • h1
        • h2
      • nav
    • aside
    • article
      • header
      • nav
      • aside
      • div
        • section
      • footer
    • footer
      • nav


Have I missed anything?

Oracle: Impacts of partition maintenance in RAC

Problem: Excessive gc cr block 2-way or db file sequential read waits against TABPART$ during an insert after adding/dropping a partition in another instance on a table with 10,000 partitions.  

Analysis: Rather surprisingly, the waits aren't for the TABPART$ blocks that needed to be reloaded but the segment header blocks of all the partitions.  These were being loaded in CR mode during query parsing.

After changing the table partitioning it make sense that Oracle would be flushing the TABPART$ information for that table from the library cache of the other instances.  The libarary cache on the instance performing partition maintenance doesn't get purged as this is updated as part of the operation.   On other instances, the next operation on that table then needs to re-populate the library cache from information in TABPART$ (which it does for ALL partitions even if only one is required).  As part of reading from TABPART$, the segment headers of all partitions need to be inspected (not quite sure why).  For 10,000 partitions, there are at least 10,000 physical reads (1 minute @ 6ms per read) or block transfers (5 to 15 seconds @ 0.5 to 1.5ms per transfer) required.

Once the library cache is re-loaded, subsequent statements are fast (until the next partition maintenance operation).

The flush of partition information from the library cache cannot be avoided.

The wait time can be reduced by ensuring the blocks are available on one instance in current (CUR) mode.  Segment headers can't be retained in a KEEP pool, so the only way to make them available is to load them on an idle instance.

The wait time can also be reduced by pre-loading the segment headers in current mode on all instances impacted, however these blocks will not be frequently accessed hence may quickly drop out of the cache.

Loading segment header blocks in current mode is necessary as blocks in consistent (CR)  mode cannot be re-used..  Segment header block can reside in multiple instances in current mode at the same time.

When the segment headers are loaded as part of the TABPART$ query, they are loaded on consistent  mode.  Forcing them into current mode can only done by retrieving rows from each of the partitions.  This can be done using a FULL TABLE SCAN of the table with the SAMPLE option with a small number.  (If anyone knows a better way that doesn't require parsing 10,000 statements with partition clauses or bind/executes, I'm all ears.)  This operation needs to be run twice, as the first time the segment headers are retrieved in consistent mode.

Workaround/Solution


1. Use an IDLE node to hold current copies of segment header blocks

2. Pre-load segment header blocks on all active instances


Supporting Queries


To inspect the segment header blocks



col object_name for a10
col subobject_name for a10
col status for a5
select file#,block#,class#,bh.status, object_name, subobject_name
  from v$bh bh
  join dba_objects obj
    ON (objd = data_object_id)
 where object_name in ('TEST')
   and class# = 4
 order by object_name, subobject_name
/

To load segment header blocks in current mode

select /*+ FULL(test) */ count(*) from test sample (0.00001);


Tuesday, February 7, 2012

Concurrent Oracle 11.2.0.2 32-bit and 64-bit ODBC on Windows 7 x64

Whilst installing the latest Oracle InstantClient 11.2.0.2 with ODBC on Windows 7 I've discovered something new.

=> Both the 32-bit and 64-bit drivers can co-exist.  A single User Data Source can be used for both 32-bit and 64-bit applications.  This means they can be maintained with the default 64-bit ODBC Data Source Administrator.  No more switching between the 32-bit and 64-bit ODBC Administrators.

Why does this work?  

  1. The ODBC Data Source entries for the User DSN are stored in a shared location in the registry (HKCU/Software/ODBC).  Items in this location are not redirected for 32-bit to the Wow6432Node tree.
  2. The ODBC Driver entries are stored in a redirectable location in the registry (HKLM/Software/ODBC).  Items in this location redirect to the Wow6432Node tree (HKLM/Software/Wow6432Node/ODBC).
  3. The Driver names for both 32-bit and 64-bit entries are the same.  Hence, applications will obtain the data source information from the shared location, then obtain the driver location from the redirectable entries.
  4. Whilst the Driver location is specified in the shared DSN entries, there is no reliance on that location.  There is a fallback to the ODBC Driver entries in the registry.
The configuration


I have the 64-bit driver installed in c:\oracle\instantclient_11_2_64, and the 32-bit driver installed in c:\oracle\instantclient_11_2.  The driver name is "Oracle in instantclient_11_2".

When doesn't it work?


This will not work for System Data Source entries as these are stored in the redirectable location (HKLM/Software/ODBC).