www.xmldb.nl | Category Archives: XMLDB

Technology preview – Oracle XQuery for Hadoop (New Big Data Connector)

Posted on 27 September, 2013 by Marco Gralike Posted in 12cR1, Unstructured Data, XMLDB Leave a comment

Yesterday I went to the Big Data machine engineered systems demo grounds, to get an insight, exclusive demo from Dmitry Lychagin. Dmitry, being part of the XDB team explained with a lot of enthusiasm the new XQuery connector for Hadoop. Although currently not yet released, but available shortly, he demonstrated the potential while using XQuery as a basis, a lot of transformation possibilities from Hadoop to another platform.

“Oracle XQuery for Hadoop” (OHX) has integration with Oracle Loader for Hadoop, Oracle noSQL DB and no (XSD) schema is needed to make this work. Can’t wait to get my hands on it and give a try with a simple VirtualBox setup with Hadoop and a database transforming van unstructured into relational or unstructured in XML or…

Thanks Dmitry for giving me all the (me Hadoop newbie) detailed info and demonstrating it.

Oracle OpenWorld: JSON Support in the Oracle 12c Database (upcoming new feature)

Posted on 26 September, 2013 by Marco Gralike Posted in 12cR1, Unstructured Data, XMLDB 1 Comment

Currently I am attending the “Schema less data management using SQL/JSON” session during Oracle OpenWorld. This session demonstrated the new upcoming feature (=not in the current release) in the 12c database which supports JSON data handling in the database. That is, of course, with the nowadays common disclaimer, it probably will, but is not guaranteed by Oracle that they actually also will do so as presented… Nether the less it would be, could be a welcome new functionality. Without going into the flame wars between the XML and JSON communities, JSON is widely used and probably therefor has its merits in current build solutions.

JSON, JavaScript Object Notation has fairly the same kind of structures as XML. Due to it’s growing use JSON support is the logical next step for, in this case the Oracle XMLDB development team, to implement this support in the database. The XMLDB team mentioned the following goals.

There will not be a new datatype, like the XMLType for XML content, but make current datatypes supportive for JSON data, following the current JSON standards, while creating a standard for these relational / JSON needs in a query method currently called SQL/JSON.Oracle’s vision in all of this is to be flexible enough and not support only one JSON datatype. Therefore the multiple datatype storage options.

The streaming method will, for example support the JSON_VALUE operator, which enable you to access and query the JSON data and (a bit like the XMLTABLE operator) return the data in the needed data format.

Another operators will be, behold the disclaimer of course, JSON_QUERY, JSON_EXISTS, JSON_TABLE rowsource…

The following show a few examples of the demonstrations given on how it will, could work, in one of the next database versions…

Oracle 12c: XMLIndex Support for Hash Partitioning

Posted on 28 June, 2013 by Marco Gralike Posted in 12cR1, Binary-, CLOB-, Object Relational Storage, Oracle, Performance 3 Comments

XMLIndex support for Hash Partitioning is now also supported in Oracle 12c. In Oracle 11 there was already support for RANGE and LIST partitioning, but not yet for HASH partitioning. Some examples for the later two:

LIST Partitioning on XMLType

--
-- Create partitioned LIST partitioned XMLType table
--
 
DROP TABLE list_part_xml PURGE;
 
CREATE TABLE list_part_xml OF XMLType
  XMLTYPE STORE AS SECUREFILE BINARY XML
  VIRTUAL COLUMNS
  (
    LISTING_TYPE AS (XMLCast(XMLQuery('/LISTING/@TYPE'
         PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100)))
  )
  PARTITION BY LIST (LISTING_TYPE)
  (
    PARTITION health    VALUES ('Health')   tablespace "USERS",
    PARTITION law_firms VALUES ('Law Firm') tablespace "USERS"
  );  
 
-- 
-- Create LOCAL partitioned XMLIndex
--
 
CREATE INDEX list_part_xmlindex ON list_part_xml(object_value)
 INDEXTYPE IS XDB.XMLINDEX
 LOCAL
 PARALLEL
 PARAMETERS ('PATH TABLE xml_path_table_list
              PIKEY INDEX pikey_ix_list
              VALUE INDEX value_ix_list');  
 
--

RANGE Partitioning on XMLType

or for RANGE parititioning on XML and an index example, this could done via…

--
-- Create partitioned RANGE partitioned XMLType table
-- 
 
DROP TABLE range_part_xml PURGE;
 
CREATE TABLE range_part_xml OF XMLType
  XMLTYPE STORE AS SECUREFILE BINARY XML
  VIRTUAL COLUMNS
  (
    RANGE_TYPE AS (XMLCast(XMLQuery('/RANGE/@TYPE'
         PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100)))
  )
  PARTITION BY RANGE (RANGE_TYPE)
  (
     PARTITION range_part_01  VALUES less than (100) tablespace "USERS"
    ,PARTITION range_part_02  VALUES less than (200) tablespace "USERS"
    ,PARTITION range_part_max VALUES less than (MAXVALUE)
  );
 
-- 
-- Create LOCAL partitioned XMLIndex with extended syntax
--
 
CREATE INDEX range_part_xmlindex ON range_part_xml(object_value)
 INDEXTYPE IS XDB.XMLINDEX
 LOCAL
 PARALLEL 4
 PARAMETERS ('PATH TABLE xml_path_table_range (PARALLEL 10)
              PIKEY INDEX pikey_ix_range
              VALUE INDEX value_ix_range (PARALLEL 2 TABLESPACE USERS)');
 
--

HASH Partitioning on XMLType

As mentioned, now in Oracle 12c, HASH partitioning is also supported for XMLType Indexes, an example:

--
-- Create partitioned HASH partitioned XMLType table
-- 
 
DROP TABLE hash_part_xml PURGE;
 
CREATE TABLE hash_part_xml OF XMLType
  XMLTYPE STORE AS SECUREFILE BINARY XML
  VIRTUAL COLUMNS
  (
    HASH_ID AS (XMLCast(XMLQuery('/HASH/@ID'
         PASSING OBJECT_VALUE RETURNING CONTENT) AS NUMBER(38)))
  )
  PARTITION BY HASH (HASH_ID)
  (
     PARTITION hash_section_01 tablespace "USERS"
    ,PARTITION hash_section_02 tablespace "USERS" 
    ,PARTITION hash_section_03 tablespace "USERS" 
 );
 
-- 
-- Create LOCAL partitioned XMLIndex with even more extended syntax
--
 
CREATE INDEX hash_part_xmlindex ON hash_part_xml(object_value)
 INDEXTYPE IS XDB.XMLINDEX
 LOCAL
 NOPARALLEL
 PARAMETERS ('PATH TABLE xml_path_table_hash (TABLESPACE USERS)
              PIKEY      INDEX pikey_ix_hash     (PARALLEL 4 TABLESPACE USERS)
              PATH ID    INDEX path_id_ix_hash   (PARALLEL 2 TABLESPACE USERS)
              ORDER KEY  INDEX order_key_ix_hash (PARALLEL 2 TABLESPACE USERS)
              VALUE      INDEX value_ix_hash     (PARALLEL   TABLESPACE USERS)
              ASYNC (SYNC ALWAYS) STALE (FALSE)');
 
--

For more information about XMLIndex usage etc, see the Oracle XMLDB Developers Guide for Oracle Database 12c
.

Oracle 12c: XQuery Full Text

Posted on 28 June, 2013 by Marco Gralike Posted in 12cR1, Oracle, Unstructured Data, XMLDB 2 Comments

New in Oracle 12c and one of the big new features in XMLDB is the XQuery Full Text functionality and, as mentioned in the post about XQuery Update, is the official W3C standard to handle unstructured pieces of XML content.

The XQuery Full Text and XQuery Full Text Index extends the XQuery XMLDB language to efficiently and XML aware handle unstructured content in XML. So finally in Oracle 12c, we are able to handle “structured”, “semi-structured” and “unstructured” content, XML aware and based on standards, across the board with regards to XML content.

Screen-Shot-2013-06-28-at-14.24.38

XQuery Full Text FTSelection/Match

The following “Full Text FTSelection Operators” are supported:

FTAnd (ftand)
FTMildNot (not in)
FTOr (ftor)
FTOrder (ordered)
FTUnaryNot (ftnot)
FTWindow (window)
FTWords

The following “XQuery Full Text Match Options” are supported:

FTStemOption (stemming, no stemming)

The default behavior specified in the XQuery and XPath Full Text 1.0 Recommendation is used for each unsupported match option, with the following exceptions:

FTLanguage (unsupported) – The language used is the language defined by the default lexer, which means the language that was used when the database was installed.
FTStopWordOption (unsupported) – The stoplist used is the stoplist defined for that language.

For instance, the following XQuery Full Text will search for “Wikipedia Content” in a table with Wikipedia stored XML content, within a window of 6 words, for matches of “oracle” and “12c” via the ftand operator (Full Text AND)
.

SELECT xt1.PAGE_TEXT AS "Wikipedia Content"
  FROM BINARYXML_RANGE_PART_XSD t1,
       XMLTABLE(xmlnamespaces(DEFAULT 'http://www.mediawiki.org/xml/export-0.8/' ),
                '$P/page/revision/text'
                PASSING t1.content AS "P"
                COLUMNS
                  PAGE_TEXT varchar2(4000) PATH '.'
               ) xt1
WHERE XMLExists('xquery version "1.0"; (: :)
                 declare default element namespace "http://www.mediawiki.org/xml/export-0.8/"; (: :)
                 $P/page/revision/text[. contains text 
                                         {$PHRASE1} ftand {$PHRASE2} 
                                         using stemming window 6 words]'
                 PASSING
                    t1.content AS "P",
                   'oracle' AS "PHRASE1",
                   '12c' AS "PHRASE2"
               )
/

During tests it proved very fast on English Wikipedia content (10++ Gb) and delivered the results within less than a second. But such a statement will only be picked up very efficiently if the new, introduced in 12c, corresponding Oracle XQuery Full-Text Index has been created.

As seen in the manual this can be done very easily via, for example:

Creating an XML Full-Text Index

Index preference BASIC_STORAGE specifies the tablespace and creation parameters for the database tables and indexes that constitute an Oracle Text index.

BEGIN
  CTX_DDL.create_section_group('mysecgroup', 'PATH_SECTION_GROUP');
  CTX_DDL.set_sec_grp_attr('mysecgroup', 'XML_ENABLE', 'T');

  CTX_DDL.create_preference('mypref', 'BASIC_STORAGE');
  CTX_DDL.set_attribute('mypref',
                        'D_TABLE_CLAUSE',
                        'TABLESPACE my_ts
                         LOB(DOC) STORE AS SECUREFILE 
                         (TABLESPACE my_ts COMPRESS MEDIUM CACHE)');
  CTX_DDL.set_attribute('mypref',
                        'I_TABLE_CLAUSE',
                        'TABLESPACE my_ts
                         LOB(TOKEN_INFO) STORE AS SECUREFILE
                         (TABLESPACE my_ts NOCOMPRESS CACHE)');
END;
/

CREATE INDEX po_ctx_idx ON po_binxml(OBJECT_VALUE)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('storage mypref section group mysecgroup');

So on my Wikipedia table this looked like:

DROP INDEX FT_RANGE_PART_IDX FORCE;
  BEGIN
    CTX_DDL.DROP_SECTION_GROUP('FTRI');
  END;
/
 
BEGIN
  CTX_DDL.DROP_PREFERENCE('STORAGE_PREFS');
END;
/
 
BEGIN
  CTX_DDL.CREATE_SECTION_GROUP('FTRI','PATH_SECTION_GROUP');
  CTX_DDL.SET_SEC_GRP_ATTR('FTRI','XML_ENABLE','T');
  CTX_DDL.create_preference('STORAGE_PREFS', 'BASIC_STORAGE');
  CTX_DDL.set_attribute('STORAGE_PREFS',
                        'D_TABLE_CLAUSE',
                        'LOB(DOC) STORE AS SECUREFILE (COMPRESS MEDIUM CACHE)');
  CTX_DDL.set_attribute('STORAGE_PREFS',
                        'I_TABLE_CLAUSE',
                        'LOB(TOKEN_INFO) STORE AS SECUREFILE (NOCOMPRESS CACHE)'
                       );
  END;
/
 
CREATE INDEX FT_RANGE_PART_IDX
 ON BINARYXML_RANGE_PART_XSD(CONTENT)
 INDEXTYPE IS CTXSYS.CONTEXT
 LOCAL
  PARAMETERS('storage STORAGE_PREFS
              section group FTRI')
 PARALLEL;

More information

More information can be found in the Oracle 12c XMLDB Developers Guide, or via the following introduction / presentation:

Hotsos 2013 – Creating Structure in Unstructured Data from Marco Gralike

…and/or its accompanied pdf document, describing the syntax used for one of the storage methods, called “Creating Structure in Unstructured Data, DML DDL used” (pdf 559 Kb)

Oracle 12c: Database Parameters…

Posted on 26 June, 2013 by Marco Gralike Posted in 12cR1, Internals, Oracle, RDBMS 2 Comments

For those in a desperate need to learn all 3351 database parameter variations…

x_ksppi.txt

…if you break it, then…

…please let me remind you some info mentioned on my “About”-page:

The content presented has been tested as good as humanly possible. But be warned: always properly test it before you use them yourself… Applying ideas, presented here in any content or in any other form, are (always) your own responsibility.

Never, never, never use untested statements, ideas or scripts on production environments! It is not only just plain stupid, it is also your own responsibility!

Just in case you were stupid enough and got into a “I told you so…” – situation. Some stuff is for educational purposes only and/or should be done ONLY while being advised to do so by Oracle Support.

Oracle 12c: Export by View

Posted on 26 June, 2013 by Marco Gralike Posted in 12cR1, Oracle, XMLDB 1 Comment

A small enhancement, but what a golden nugget! At least that’s what I think.

It is now possible in Oracle 12c to export data via a view. I my realm, Oracle XMLDB, that can be an interesting feature to export stored XML content in a relational format and import it, for example, in a relational table/environment. Let me show you how.

Lets create an XMLTYPE table and insert some XML into it:

 
SQL> CREATE TABLE xmltype_table OF xmltype;
 
TABLE created.
 
SQL> DESC xmltype_table
 Name					   NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
TABLE OF PUBLIC.XMLTYPE STORAGE BINARY
 
SQL> SELECT xmltype(cursor(SELECT * FROM all_objects)) AS XMLCONTENT
  2    FROM dual;
 
XMLCONTENT
--------------------------------------------------------------------------------
< ?xml version="1.0"?>
<rowset>
  <row>
    <owner>SYS</owner>
    <object_name>I...</object_name>
...
...
</row>
</rowset>
 
SQL> SELECT COUNT(*) FROM xmltype_table;
 
  COUNT(*)
----------
	 0
 
SQL> INSERT INTO xmltype_table (object_value)
  2  SELECT xmltype(cursor(SELECT * FROM all_objects))
  3    FROM dual;
 
1 ROW created.
 
SQL> commit;
 
Commit Complete.

Now if you would, in SQL*Plus, set “set long” to a very big value, you would see the canonical XML document with all information of ALL_OBJECTS where your account has access to. To demonstrate the export via view functionality, in this example, we will have to create a view on top of this XML content in the table XMLTYPE_TABLE. This can be done, for instance, via creating a view via the following method.

 
SQL> SELECT xt1.owner_name AS MY_OWNER
  2	  , xt1.object_name AS MY_OBJECT
  3    FROM xmltype_table tab1
  4	  , xmltable('/ROWSET/ROW'
  5		     passing tab1.object_value
  6		     COLUMNS
  7		       owner_name  VARCHAR2(30) path 'OWNER',
  8		       object_name VARCHAR2(50) path 'OBJECT_NAME'
  9		    ) xt1
 10  WHERE rownum < = 10
 11 ;
 
MY_OWNER		       MY_OBJECT
------------------------------ --------------------------------------------------
SYS			       ICOL$
SYS			       I_CDEF3
SYS			       TS$
SYS			       CDEF$
SYS			       I_FILE#_BLOCK#
SYS			       I_FILE2
SYS			       I_OBJ5
SYS			       I_OBJ1
SYS			       I_OBJ4
SYS			       I_USER2
 
10 ROWS selected.

Before we can export the data via a view, we have to create a DIRECTORY and (of course) the view…

 
SQL> CREATE directory XMLDMP AS '/tmp';
 
Directory created.
 
SQL> CREATE VIEW XMLTYPE_VIEW
  2  AS
  3  SELECT xt1.owner_name AS MY_OWNER
  4  	  , xt1.object_name AS MY_OBJECT
  5    FROM xmltype_table tab1
  6  	  , xmltable('/ROWSET/ROW'
  7  		     passing tab1.object_value
  8  		     COLUMNS
  9  		       owner_name VARCHAR2(30) path 'OWNER',
 10  		       object_name VARCHAR2(50) path 'OBJECT_NAME'
 11  		    ) xt1
 12   WHERE rownum < = 10
 13  ;
 
VIEW created.
 
SQL> DESC XMLTYPE_VIEW
 Name						       NULL?	TYPE
 ----------------------------------------------------- -------- ------------------------------------
 MY_OWNER							VARCHAR2(30)
 MY_OBJECT							VARCHAR2(50)

Now outside the database we can use the datapump export utility to export the data via the just created view.

 
[oracle@orcl12 tmp]$ expdp test/test DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp VIEWS_AS_TABLES=TEST."XMLTYPE_VIEW" REUSE_DUMPFILES=Y
 
Export: Release 12.1.0.1.0 - Production ON Wed Jun 26 15:14:23 2013
 
Copyright (c) 1982, 2013, Oracle AND/OR its affiliates.  ALL rights reserved.
 
Connected TO: Oracle DATABASE 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, Advanced Analytics AND REAL Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp VIEWS_AS_TABLES=TEST.XMLTYPE_VIEW REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "TEST"."XMLTYPE_VIEW"                       5.632 KB      10 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /tmp/xmltype_table.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 26 15:14:36 2013 elapsed 0 00:00:11

The "VIEW_AS_TABLES" signals expdp that:

VIEWS_AS_TABLES

Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

Note that in network import mode, a table name may be appended to the view name.

To demonstrate that when you import the dumpfile again, that it delivers a relational table, do the following:

SQL> SELECT tname AS NAME
  2	  , tabtype AS TYPE
  3  FROM tab
  4 ;
 
NAME			       TYPE
------------------------------ -------
XMLTYPE_VIEW		       VIEW
XMLTYPE_TABLE		       TABLE

On the command prompt:

oracle@orcl12 ~]$ cd /tmp
[oracle@orcl12 tmp]$ ls -ltra
totaal 200
...
...
drwxrwxrwt 16 root   root       4096 jun 26 15:14 .
-rw-r-----  1 oracle oinstall 114688 jun 26 15:14 xmltype_table.dmp
-rw-r--r--  1 oracle oinstall   1097 jun 26 15:14 export.log
 
[oracle@orcl tmp]$ impdp test/test DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp REMAP_TABLE=XMLTYPE_VIEW:RELATIONAL_TABLE
 
Import: Release 12.1.0.1.0 - Production ON Wed Jun 26 15:33:00 2013
 
Copyright (c) 1982, 2013, Oracle AND/OR its affiliates.  ALL rights reserved.
 
Connected TO: Oracle DATABASE 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, Advanced Analytics AND REAL Application Testing options
Master TABLE "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** DIRECTORY=XMLDMP DUMPFILE=xmltype_table.dmp REMAP_TABLE=XMLTYPE_VIEW:RELATIONAL_TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "TEST"."RELATIONAL_TABLE"                   5.632 KB      10 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Wed Jun 26 15:33:03 2013 elapsed 0 00:00:02

Now checking via SQL*Plus it shows a newly created table with name RELATIONAL_TABLE, with the same definition as it's source (the view XMLTYPE_VIEW) and with 10 records inserted.

 
SQL> SELECT tname AS NAME
  2	  , tabtype AS TYPE
  3  FROM tab;
 
NAME			       TYPE
------------------------------ -------
RELATIONAL_TABLE	       TABLE
XMLTYPE_VIEW		       VIEW
XMLTYPE_TABLE		       TABLE
 
SQL> SELECT COUNT(*) FROM relational_table;
 
  COUNT(*)
----------
	10
 
SQL> SELECT * FROM relational_table;
 
MY_OWNER		       MY_OBJECT
------------------------------ --------------------------------------------------
SYS			       ICOL$
SYS			       I_CDEF3
SYS			       TS$
SYS			       CDEF$
SYS			       I_FILE#_BLOCK#
SYS			       I_FILE2
SYS			       I_OBJ5
SYS			       I_OBJ1
SYS			       I_OBJ4
SYS			       I_USER2
 
10 ROWS selected.

It is probably good to mention that the Oracle XMLDB functionality (based on W3C XML standards) is an Oracle kernel build-in, based on C code, and therefore out performs most outside, mainly Java based, solutions.

Just so you know.

Oracle 12c: Oracle XMLDB is now Mandatory!

Posted on 26 June, 2013 by Marco Gralike Posted in 12cR1, Oracle, RDBMS, XMLDB 2 Comments

Is this one of my highlights? No not really, but it is a remarkable one. Oracle XMLDB functionality is now mandatory and can not be uninstalled. As mentioned in the new feature section of the Oracle XMLDB Developers Guide for Oracle 12c:

Oracle XML DB is now a mandatory component of Oracle Database. You cannot uninstall it, and there is no option not to include it when you create Oracle Database. It is automatically installed when you create a new database or (if not existing already) when you upgrade an existing database to Oracle Database 12c Release 1 (12.1.0.1).

…and a bit further on…

Automatic Installation of Oracle XML DB

If Oracle XML DB is not already installed in your database prior to an upgrade to Oracle Database 12c Release 1 (12.1.0.1) or later, then it is automatically installed in tablespace SYSAUX during the upgrade.

If Oracle XML DB has thus been automatically installed, and if you want to use Oracle XML DB, then, after the upgrade operation, you must set the database compatibility to at least 12.1.0.1. If the compatibility is less than 12.1.0.1 then an error is raised when you try to use Oracle XML DB.

If Oracle XML DB was automatically installed during an upgrade and the current compatibility level is less than 12.1.0.1, then Oracle DB is automatically uninstalled during any downgrade to a prior release.

As a database administrator, I like “lean and mean”, so therefore my mixed feelings about this change. As the Oracle ACE Director for this functionality, I think it was inevitable, seen the changes over the years.

You might not realize it, but a lot of Oracle XMLDB functionality has been used and implemented over the years from Oracle 11.1 and onwards. It is used in area’s like dictionary views, packages like DBMS_METADATA, datapump utilities, APEX (PL/SQL Gateway servlet in the XDB Protocol Server environment), Data and Audit vault, enhanced security or DBFS.

Now with pluggable databases in the mix for 12c, it has become an intrinsic part of the database. The new DB Express, the replacement for DB Console in the database, is depending on some of the core XMLDB repository functions.

So database administrators alike, beware, $ORACLE_HOME/rdbms/admin/catqm.sql, is probably a good thing to get acquainted with. I advised my colleagues to start installing it, during new installations and/or upgrades to 11.2.x and onwards, so they get a feeling for the wonders and quirks of this database functionality and life gets a little bit easier when upgrading in the future to the latest Oracle 12c release.

HTH.

Hotsos Revisited 2013 – Presentatie materiaal

Posted on 3 April, 2013 by Marco Gralike Posted in Events, General, Oracle, Performance, Unstructured Data, XMLDB Leave a comment

Hierbij nog dank voor allen die aanwezig waren bij de weer gevulde, informatieve & gezellige avond tijdens “Hotsos Revisited 2013″.

Wij presentatoren hebben genoten van het ambiance. Hier ook nog voor degenen die graag het nog een keer willen nalezen het presentatie materiaal van Toon, Jacco, Gerwin, Frits en mij…

Presentatie materiaal in alfabetische volgorde:

Hopelijk zien we jullie misschien weer tijdens de Masterclass over Oracle storage “Understanding storage met Oracle ACE Director James Morle” en anders hopelijk tot een volgende keer bij AMIS in Nieuwegein.

Marco

Hotsos 2013 – Presentation “Creating Structure in Unstructured Data”

Posted on 5 March, 2013 by Marco Gralike Posted in 11gR1, 11gR2, 12cR1, Binary-, CLOB-, Object Relational Storage, Events, General, Oracle, Unstructured Data 1 Comment

Hereby, for those who want another look or for people to share, my presentation content “Creating Structure in Unstructured Data” given during the Hotsos 2013 Symposium on Monday morning.

HTH

Marco

Hotsos 2013 – Creating Structure in Unstructured Data from Marco Gralike

HOWTO – Oracle Shared Server Setup

Posted on 21 February, 2013 by Marco Gralike Posted in APEX, General, Howto, Oracle, Performance, SQL*Net, Protocol Server, XMLDB Leave a comment

A customer of mine run out of memory due to much server processes of dedicated connected client sessions. As an alternative I tried to explain the options between DEDICATED and SHARED SERVER concepts as an initial attempt/workaround for the client software problems. Looking around on the internet for pictures and or newbie documentation, I found the following nugget on YouTube that explains the basics and how to address them.

Due to the fact that the XMLDB XDB Repository Architecture, which makes it possible to connect to the database via HTTP(s)/WebDAV en FTP, also makes use of the Oracle Shared Server Architecture (or referenced in the old days as MTS – Multi Threaded Server), this information is still very valid concerning those XDB Repository HTTP(s), WebDAV and FTP connections and/or APEX connections based on the PL/SQL Gateway (which is a part of the XDB Protocol Server functionality).

For my single instance databases, I configure a value of shared_servers=5, and most of the time that should be enough not to cause connection “hiccups” (clients “freeze” for a short while), and an appropriate value for LARGE_POOL_SIZE. Most of the time in a standard DBCA created database, the DISPATCHER parameter is already configured but not really active, because not a lot of people are aware of the XMLDB Repository access architecture and therefore don’t connect via this method. Also due to an enhancement request I once made during 11.1, HTTP(s)/WebDAV/FTP ports are not enabled via DBMS_XDB.SETHTTPPORT() and DBMS_XDB.SETFTPPORT(), that is, have both a value of 0.

On a production database you could decide to even get rid of the default setup by disabling DISPATCHER and SHARED_SERVER processes, if need be. As said, most of the times the DISPATCHER is enabled and though it doesn’t do any work, it will eat some of your CPU and memory resources. On a 10.x environment this will even be more likely. As I mentioned, my enhancement was honored from 11.1 and upwards, so…

The video talks about configuring DISPATCHER, SHARED_SERVER, LARGE_POOL_SIZE, etc. I never really tweak parameters before it is really needed. So one of the small pieces of advice I would give you, is that you should realize that, besides setting values to be able peak connection strains on the database, you should also realize that LARGE_POOL_SIZE (when set) will also automatically be used for other mechanisms like RMAN backup optimization. So while trying to figure out stuff like “sessions”, “circuit” etc value, keep in mind that this static LARGE_POOL_SIZE parameter, during peak moments, has to take into account stuff like RMAN backup windows/run times on that database.

HTH

Marco

December 2013
M	T	W	T	F	S	S
« Nov
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31