Month: January 2013

Exadata Smart Scan Projection Limitation

Here is an interesting limitation to Exadata Smart Scans – if more than 254 columns from a table (not HCC compressed, more on that in moment) need to be projected, Smart Scans for that particular segment will be disabled and Exadata will fall back to conventional I/O. This means that the number of columns in the projection clause can make a significant difference to performance, since only Smart Scans allow taking advantage of offloading and particularly avoiding I/O via Storage Indexes.Now the expression “254 columns” might ring a bell, since it is the maximum number of columns that Oracle can store in a single row piece – tables consisting of more than 254 columns will have to be stored in multiple row pieces.However, what I’m talking about here is not related to such columns residing in different row pieces of a row – Smart Scans still happily work even if columns from different row pieces are projected (which was subject to several bugs in the past), although you might end up with additional “cell single block reads” in case of truly chained rows rather than just additional logical I/O for picking up the different row pieces from the same block, also sometimes called “intra-block” chaining.No, the limitation simply seems to be that Smart Scans – broadly speaking and ignoring edge cases – can only transport a maximum of 254 columns from a single (non-HCC) segment. Requesting more columns will simply disable Smart Scans for that segment.Now you might say, offloading and in particular offloading column projection isn’t that much relevant if you select that many columns from a table anyway, but the point is that you loose the ability to benefit from Storage Indexes and only transporting the relevant rows back to the compute nodes.Both features can speed up the processing significantly, in particular if the number of rows selected is only a fraction of the total number of rows, and/or the cells could avoid a significant amount of I/O via Storage Indexes.To demonstrate the point I’ve put together a simple test case that generates a test table with more than 254 columns – the script below generates a table of approx. 40GB uncompressed size so that a significant difference in performance could be measured.


set echo on timing on time on

-- MAX is 999, there is a ID column as first col
define num_cols = 300

define compression = nocompress
--define compression = "compress for query low"

drop table many_cols_rg;

purge table many_cols_rg;

declare
s_sql varchar2(32767);
begin
s_sql := q'!
create table many_cols_rg pctfree 0
&compression
parallel nologging
as
with generator1 as
(
select /*+ materialize cardinality(1000) */
rownum as id
-- this makes the rowsource wider otherwise PX BLOCK ITERATOR has problems properly spreading work among the slaves
, rpad('x', 4000) as padding
from
dual
connect by
level <= 1e3
),
generator2 as
(
select /*+ materialize cardinality(10000) */
rownum as id
-- this makes the rowsource wider otherwise PX BLOCK ITERATOR has problems properly spreading work among the slaves
, rpad('x', 4000) as padding
from
dual
connect by
level <= 1e4
)
select
num_id as id
!';
for i in 1..&num_cols loop
s_sql := s_sql || ', char_id as col' || to_char(i, 'FM000');
end loop;
s_sql := s_sql || q'!
from
(
select /*+ no_merge */
a.id + (b.id - 1) * 1e3 as num_id
, cast(to_char(a.id + (b.id - 1) * 1e3, 'FM0000000000') as varchar2(10)) as char_id
from
generator1 a
, generator2 b
)
!';
execute immediate s_sql;
end;
/

exec dbms_stats.gather_table_stats(null, 'many_cols_rg')

Assuming a Storage Index was generated on the ID column a query like the following (using Parallel Query at a DOP of 32 in the test runs here) can benefit from offloading, since in principle all I/O could be avoided via the Storage Index, and virtually no data needs to be transported from the cells to the compute nodes.Note that it projects exactly 254 columns from different row pieces.


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

11:28:22 SQL>
11:28:22 SQL> select a.name, b.value from v$mystat b, v$statname a where a.name in ('cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan') and a.statistic# = b.statistic#;

NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0

Elapsed: 00:00:00.00
11:28:22 SQL>
11:28:22 SQL> select
11:28:22 2 col001,
11:28:22 3 col002,
.
.
.
11:28:23 254 col253,
11:28:23 255 col300/*,
11:28:23 256 col254*/
11:28:23 257 from many_cols_rg where id between -2 and -1;

no rows selected

Elapsed: 00:00:02.40
11:28:25 SQL>
11:28:25 SQL> select a.name, b.value from v$mystat b, v$statname a where a.name in ('cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan') and a.statistic# = b.statistic#;

NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 2,1195E+10
cell physical IO interconnect bytes returned by smart scan 3000784

Elapsed: 00:00:00.01
11:28:25 SQL>

As you can see from the snippet, it took less than 2.5 seconds to run the query on the 40GB segment, and the session statistics report 20GB avoided via the Storage Index (which seems to be an instrumentation bug as it always reports only 50% of the total segment size as a maximum, this output was taken from 11.2.0.2 Exadata BP14). Furthermore only a couple of MB were exchanged between the cells and the compute nodes.The corresponding Real-Time SQL Monitoring report confirms the “Smart Scan”:

Increasing the number of columns projected from the segment above 254 (and as outlined above it doesn’t matter from which row pieces these columns come from) disables the Smart Scan, and it takes more than 9 seconds to run essentially the same query, pumping all 40GB through the compute nodes to filter all rows.


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

11:29:14 SQL>
11:29:14 SQL> select a.name, b.value from v$mystat b, v$statname a where a.name in ('cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan') and a.statistic# = b.statistic#;

NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0

Elapsed: 00:00:00.00
11:29:14 SQL>
11:29:14 SQL> select
11:29:14 2 col001,
11:29:14 3 col002,
.
.
.
11:29:15 254 col253,
11:29:15 255 col300,
11:29:15 256 col254
11:29:15 257 from many_cols_rg where id between -2 and -1;

no rows selected

Elapsed: 00:00:09.22
11:29:24 SQL>
11:29:24 SQL> select a.name, b.value from v$mystat b, v$statname a where a.name in ('cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan') and a.statistic# = b.statistic#;

NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0

Elapsed: 00:00:00.01
11:29:24 SQL>

The corresponding Real-Time SQL Monitoring report confirms the fallback to “direct path reads”:

Funnily, in this deliberately crafted, extreme case here, it is much faster to access the segment twice and get the remaining columns via a self-join in order to benefit from the offloading features – it only takes 4.7 seconds to run the self-join, and the session statistics confirm that both segment scans could leverage offloading and in particular avoid I/O via Storage Indexes:


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

11:29:37 SQL>
11:29:37 SQL> select a.name, b.value from v$mystat b, v$statname a where a.name in ('cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan') and a.statistic# = b.statistic#;

NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0

Elapsed: 00:00:00.00
11:29:37 SQL>
11:29:37 SQL> select
11:29:37 2 a.col001,
11:29:37 3 a.col002,
.
.
.
11:29:37 254 a.col253,
11:29:37 255 a.col300,
11:29:37 256 b.col254
11:29:37 257 from many_cols_rg a, many_cols_rg b
11:29:37 258 where a.id between -2 and -1 and b.id between -2 and -1
11:29:37 259 and a.id = b.id;

no rows selected

Elapsed: 00:00:04.77
11:29:42 SQL>
11:29:42 SQL> select a.name, b.value from v$mystat b, v$statname a where a.name in ('cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan') and a.statistic# = b.statistic#;

NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 4,2390E+10
cell physical IO interconnect bytes returned by smart scan 6001568

Elapsed: 00:00:00.01
11:29:42 SQL>
11:29:42 SQL>

Note: This is one of the cases where you don’t want the optimizer to eliminate a self-join via a query transformation based on corresponding constraints on the join columns 🙂

HCC Compression

Now the funny thing is that if you repeat the table creation script but uncomment the HCC compression, the Smart Scan happily works with up to 1,000 columns of such a compressed segment.So obviously the general code implementation supports transporting rows with more than 254 columns from the cell to the compute nodes, but the question is why does it only do so with HCC compressed segments. It’s probably a question that my client will raise with Oracle Support to find out the answer.

Footnote

At least only the number of “raw” columns projected count towards the limitation – any expressions based on columns don’t count, therefore you can project actually more than 254 expressions from a segment and still benefit from Smart Scans as long as the expressions refer to a maximum of 254 “base” columns.The same limitation could also be reproduced when using (almost) the latest available Exadata version as of writing this (11.2.0.3 BP12, almost because BP14 just came out as far as I know).

Compression in Oracle – Part 3: OLTP Compression

In part two of this series we examined the effect of updates on compressed data when using basic compression, and saw how Oracle will do some “decompression” of a row before updating it with the effect that highly compressed rows might easily become so much larger that even a small amount of change could lead to a large number of… Continue Reading →

Interview with Uma Welingkar, Product Management Director for Siebel Open UI

Among many good reasons to believe that Siebel CRM will live long and prosper is that the good people at Oracle proved that they can deliver real innovation in the form of the Innovation Pack 2012. Of course the spotlight is – and will be in the in the medium term – on Open UI.

Siebel Open UI

I am very glad and thankful that Uma Welingkar, Director of Oracle Product Management has taken the time to answer some of my – and the community’s – questions on Open UI. The interview was conducted via e-mail and Uma’s answers are very interesting. Here is the full interview:

***

Siebel Essentials (SE): Uma, first let me congratulate You and the Open UI team to the GA in December last year. How long did Oracle work on this new UI framework?

Uma Welingkar (UW): It took about 15 months from concept to the release of 2012 IP [SE: Innovation Pack] for Open UI.  There were a number of folks across the development and sales consulting teams that helped drive some of the ideas for the new framework. We showcased the concept at Open World 2011 and the feedback from customers was overwhelmingly positive and we started.

SE: What were the major drivers and objectives, business- and technology-wise, for the Open UI project?


UW: The major business drivers for user experience was  simplicity and usability which translated from a  technology standpoint for  the need to support multiple browsers and devices.

Siebel has focused on the 3 major tenets:

  • Contemporary: Siebel runs in all browsers and all browser-capable devices using the latest web technology standards, such as JavaScript, CSS, and HTML 5 while providing a native application user experience.
  • Productive: Siebel is designed for a user experience that reduces clutter and user keystrokes.
  • User-sensitive: The user experience enables Siebel to adapt easily to business needs, device types and user preferences.

This new framework allows our customers to develop and design business process once and deploy to multiple devices – desktops and tablets.

SE: For many other applications like Fusion Applications, Oracle selects its own ADF technology for the user interface. What where the main reasons for the Siebel team to develop a new framework?

UW: One of the major reasons for choosing the latest web technology standards  (HTLM5, JQuery and CSS) was that is uses the existing investment that both our customers and we have made with Siebel. Siebel’s dependency on Active-X controls made employee (High Interactivity) applications only available on IE browsers. With the browser and device explosion, we needed to provide our customers a cost-effective solution to build rich-interactive web based applications that could be used for both employee based as well as customer facing applications. This allows our  customers to leverage  both their software and hardware investments to extend Siebel to multiple devices with no new added deployments costs.

The main reasons for developing this new framework:

  • Allows our customers to continue to use the business logic and configurations they have already developed.
  • Provide a fully extensible framework that allows customers and partners a platform to innovate.
  • Provides enhanced accessibility support with native screen reader support  and dynamic content via WAI-ARIA.
  • No compile time controls (Active-X controls) which allows customers the ability to change OOB controls provided to the ones they like.
  • Allows Siebel to move away from explicit certification against each browser to supporting standards based approach giving customers the flexibility to support browsers of the end-users choice.

SE: As I heard, customers were involved early in the project. How did the Open UI project benefit from the beta customers?

UW: Siebel for the past 18 months has adopted “Agile” methodology for development which means we have a 2 month product increment cycle and this allowed us to showcase and demo complete feature sets to our customers. Customers were able to provide valuable feedback that allowed us to make changes prior to the GA release as well as ongoing improvements as we deliver features in the upcoming Innovation Packs with Siebel. We have also had a number of training sessions starting June of last year with partners and beta customers that allowed us to not only make changes to the product but also documentation and training materials.

A number of our partners worked with us to build out industry flows and solutions and showcased them at OpenWorld 2012 and also presented at the Open UI Customer and Partner Panel.

SE: Can you share any information on the customer uptake on Open UI in this early stage?

UW: Since the release of Siebel 2012 IP (8.1.1.9/8.2.2.2) we have had about 1000+ downloads of the software within 30 days of the release. We have a number of them in the development phase for Open UI at this point across the various regions and industries.

SE: How is the customer feedback so far?

UW: The customer feedback so far has been very positive, a number of customers as well as partners feel this is a game changer for Siebel. We have had a very good collaboration model with the field and our sales consultants and pre-sales team have been engaged with customers to provide customer specific demos and gather valuable feedback for the next phases in the development of Open UI.

SE: What highlights does the Open UI team plan for the next Open UI release?

UW: Over the next couple of years, we continue to focus on usability and mobility as our key drivers for Siebel. The Siebel core framework team is focused on delivering and enhancing the framework to allow customers to make UI changes easily without the need to re-compile the Siebel SRF. Our goal is to ensure we deliver controls that are part of our Active-X library into the Open UI library. The best practices document available on MOS provides high level guidance on what’s being made available in the upcoming releases targeted for Spring and Fall of 2013.

In addition to the framework, the cross industry and industry team is looking to deliver new flows based on Open UI.

SE: Uma, Thank You so much for your time.

***End of interview***
Summary
It is good to see that Oracle’s efforts to modernize the Siebel CRM flagship are much appreciated by customers and partners. Besides that it makes me proud that Oracle management takes much time to talk to customers and conduct interviews like the above.
have a nice day
@lex

Configuration des options en Oracle 11g EE avec ‘chopt’

La configuration des options sous Entreprise Edition avant Oracle 11g pouvait être un véritable parcours du combattant. Donc, une attention particulière devait avoir lieu avant l’installation des binaires Oracle; à savoir quelles options devaient être installées! Depuis la version 11g, les choses ont bien changé, grâce à l’utilitaire “chopt” se trouvant sous $ORACLE_HOME/bin.

 

Voici la syntaxe d’utilisation de “chopt”:

chopt [enable |disable] db_option

db_option:

  • dm = Oracle Data Mining
  • dv = Oracle Database Vault
  • lbac = Oracle Label Security
  • olap = Oracle OLAP
  • partitioning = Oracle Partitionin
  • rat = Oracle Real Application Testing
  • ode_net = Oracle Database Extensions for .NET 1.x
  • ode_net_2 = Oracle Database Extensions for .NET 2.0


Voici un exemple d’utilisation de “chopt”:

  • Activation du data mining:

 

oracle@SORCQ01:/u00/app/oracle/product/11.2.0/db_1\bin\ [dbq1] chopt enable dmWriting to /u00/app/oracle/product/11.2.0/db_1/install/enable_dm.log…
/usr/ccs/bin/make -f /u00/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk dm_on ORACLE_HOME=/u00/app/oracle/product/11.2.0/db_1
/usr/ccs/bin/make -f /u00/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u00/app/oracle/product/11.2.0/db_1
ld: 0711-224 WARNING: Duplicate symbol: fc_softc
ld: 0711-224 WARNING: Duplicate symbol: __fe_def_env
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libgeneric11.a[sdbgrfu.o], imported symbol timezone
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdowd.o], imported symbol ksudbrmseccnt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrt.o], imported symbol ksudbrmseccnt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdidx.o], imported symbol ksudbrmseccnt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrcr.o], imported symbol kcbstdbz_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrt.o], imported symbol kcbstdbz_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrcnc.o], imported symbol mdrcnccmt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-783 WARNING: TOC overflow. TOC size: 208456    Maximum size: 65536
        Extra instructions are being generated for each reference to a TOC
        symbol if the symbol is in the TOC overflow area.

 

  • Vérification de la bannière:

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 10 14:00:01 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With Data Mining options

 

  • Désactivation du data mining:

 

oracle@SORCQ01:/u00/app/oracle/product/11.2.0/db_1\bin\ [dbq1] chopt disable dmWriting to /u00/app/oracle/product/11.2.0/db_1/install/disable_dm.log…
/usr/ccs/bin/make -f /u00/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk dm_off ORACLE_HOME=/u00/app/oracle/product/11.2.0/db_1
/usr/ccs/bin/make -f /u00/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u00/app/oracle/product/11.2.0/db_1
ld: 0711-224 WARNING: Duplicate symbol: fc_softc
ld: 0711-224 WARNING: Duplicate symbol: __fe_def_env
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libgeneric11.a[sdbgrfu.o], imported symbol timezone
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdowd.o], imported symbol ksudbrmseccnt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrt.o], imported symbol ksudbrmseccnt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdidx.o], imported symbol ksudbrmseccnt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrcr.o], imported symbol kcbstdbz_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrt.o], imported symbol kcbstdbz_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-773 WARNING: Object /u00/app/oracle/product/11.2.0/db_1/lib//libordsdo11.a[mdrcnc.o], imported symbol mdrcnccmt_
        Symbol was expected to be local. Extra instructions
        are being generated to reference the symbol.
ld: 0711-783 WARNING: TOC overflow. TOC size: 208456    Maximum size: 65536
        Extra instructions are being generated for each reference to a TOC
        symbol if the symbol is in the TOC overflow area.

 

  • Vérification de la bannière:

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 10 14:04:05 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

 

Il est donc bien plus facile d’ajouter ou d’enlever des options avec la version Oracle 11g via “chopt”. De plus, il n’est pas nécesssaire de lancer un arrêt de la base. C’est très pratique.

New Exam Prep Seminar for Java SE 7 Programmer II (OCP)

We’re happy to announce the availability of a brand new Exam Prep Seminar titled “Java SE 7 Programmer II”. This new Exam Prep Seminar is available standalone, and will soon be available through a Certification Value Package, which includes (1) the Seminar, and (2) a certification exam voucher with a free retake.
For those of you preparing for the Oracle Certified Professional, Java SE 7 Programmer certification, this seminar is a great value and and an excellent way to gain valuable insight from one of Oracle University’s top instructors. This Exam Prep Seminar will accelerate your preparation, make your prep time more efficient and give you insight to the breadth and depth of the certification exam.
This type of exam preparation has traditionally only been available at the Oracle OpenWorld/JavaOne conferences, but is now available to anyone through this new format. Of course with online video, you can now start, stop, rewind, and review as needed! Also note that because this seminar is in the Oracle Training On Demand format, you can also watch it on your your iPad through Oracle University’s new free iPad app.

QUICK LINKS

Oracle I/O latency monitoring

One thing that I have found sorely missing in the performance pages of Enterprise Manager is latency values for various types of I/O. The performance page or top activity may show high I/O waits but it won’t indicated if the latency of I/O is unusually high or not. Thus I put together a shell script […]

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569