Search

OakieTags

Who's online

There are currently 0 users and 20 guests online.

Recent comments

Oakies Blog Aggregator

The Oracle ACE Program : My 10 Year Anniversary

ace-directorOn 1st April 2006 I got an email telling me I was an Oracle ACE, so either this is the most drawn out April Fools’ Day joke ever, or I’ve been in the Oracle ACE Program for 10 years. Wow!

The numbers look something like this.

  • Nearly 21 years working with Oracle technology. (August 1995)
  • Nearly 16 years doing my website. (Original name: 03 July 2000 or Current name: 31 August 2001)
  • Nearly 11 years blogging. (15 June 2005)
  • 10 years on the Oracle ACE Program. (01 April 2006)
  • Nearly 10 months making videos. (6 July 2015)

It’s quite scary when you look back at it. </p />
</p></div>

    	  	<div class=

Understanding scheduler syntax

When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run. To help you understand, a nice little utility is to use EVALUATE_CALENDAR_STRING”


SQL>  set serveroutput on size 999999
SQL>
SQL> declare
  2    l_start_date TIMESTAMP;
  3    l_next_date TIMESTAMP;
  4    l_return_date TIMESTAMP;
  5  begin
  6    l_start_date := trunc(SYSTIMESTAMP);
  7    l_return_date := l_start_date;
  8
  9    for ctr in 1..10 loop
 10      dbms_scheduler.evaluate_calendar_string(
 11        'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10',
 12        l_start_date, l_return_date, l_next_date
 13        );
 14      dbms_output.put_line('Next Run on: ' ||
 15        to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
 16        );
 17      l_return_date := l_next_date;
 18    end loop;
 19  end;
 20  /
Next Run on: 03/31/2016 09:00:00
Next Run on: 03/31/2016 10:00:00
Next Run on: 04/01/2016 09:00:00
Next Run on: 04/01/2016 10:00:00
Next Run on: 04/04/2016 09:00:00
Next Run on: 04/04/2016 10:00:00
Next Run on: 04/05/2016 09:00:00
Next Run on: 04/05/2016 10:00:00
Next Run on: 04/06/2016 09:00:00
Next Run on: 04/06/2016 10:00:00

PL/SQL procedure successfully completed.

Best Practices with Enterprise Manager 13c

Since the introduction of Enterprise Manager 12c, folks have been asking for a list of best practices.  I know a lot of you have been waiting for this post!

day

1.Use previously deployed, older hardware for your Enterprise Manager deployment on 13c.

Enterprise Manager is a simple, single service system.  There is no need for adequate resources and ability to scale.  In fact, I’ll soon be posting on my blog about building an EM13c on a Raspberry Pi 3.

2. Please feel free to add new schemas, objects and ETL’s to the Oracle Management Repository, (OMR.)

This database doesn’t have enough to do with metric collections, data rollup, plugin, metric extensions and notifications.

3. Turn on the standard statistics jobs and baseline collection jobs on the OMR.

The OMR has its own version of the stats job, but running two jobs should make it run even better and even though baselines aren’t used, why not collect them, just in case?

4. Set the EM13c to autostart, but set the listener to stay down.

The Oracle Management Service, (OMS) shouldn’t require the listener to connect to the OMR when starting, after all.

5.  If there is a lot of garbage collection, just add more memory to the java heap.

If we give it more memory, then it will have less to clean up, right?  More is better and there isn’t any way to find out what it should be set to anyway.

6. If you want to use the AWR Warehouse, you should use the OMR database for the AWR repository, too.

It shouldn’t make a difference to network traffic, datapump loading or resource workloads if they share a box.  These two databases should work flawlessly on the same hardware, not to worry about network traffic, etc.

7.  If you have a lot of backlog for job processing on your EM13c, you should trim down the worker threads.

Serializing jobs always speeds up the loading of data.

8. Sizing an Enterprise Manager EM13c is a simple mathematical process, which I’ve displayed below:

mathhttp://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/03/math.png?resiz... 300w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/03/math.png?resiz... 768w" sizes="(max-width: 553px) 100vw, 553px" data-recalc-dims="1" />

(If I didn’t mention it earlier, there will be a quiz at the end of this post…)

9.  Never apply patches to the Enterprise Manager tiers or agents.

Each release is pristine and bugs don’t exist.  It will only require more work in the way of applying these patches and downtime to your EM13c environment.

10.  Patch any host, database or agent monitored by the Enterprise Manager manually.

Patch plans and automation of patching and provisioning is a terrible idea and the only way a DBA can assure if something is done right is if they do it manually themselves.  Who needs a good night’s sleep anyway?

 

 



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Best Practices with Enterprise Manager 13c], All Right Reserved. 2016.

ANSI Join Syntax in SQL Server

Writing your database queries using explicit join syntax helps toward
clarity and readability while reducing the opportunity for error and wrong
results.



Read the full post at www.gennick.com/database.

ANSI Join Syntax in SQL Server

Another in a series of posts in response to Tim Ford's #EntryLevel Challenge.


Anyone new to SQL Server will sooner or later, and probably sooner, encounter exhortations to write joins in "ANSI join syntax". While the term is misleading and in fact incorrect, the practice of using the so-called "ANSI join syntax" contributes toward queries that are easier to understand and maintain. 

Clear Intentions

Following are two queries that produce the same result -- all possible combinations of product and subcategory names from the two tables listed in the FROM clause. (Such a result is termed a Cartesian product). Notice how I've written the joins in the respective FROM clauses.

--Query 1: Comma-delimited syntax
SELECT psc.Name, p.Name
FROM Production.ProductSubCategory AS psc, Production.Product AS p

--Query 2: Explicit Join Syntax
SELECT psc.Name, p.Name
FROM Production.ProductSubCategory AS psc CROSS JOIN Production.Product AS p

A developer looking at Query 1 might wonder whether I had forgotten to write my WHERE clause. The bizarre result of a Cartesian product combined with the ambiguity of the syntax induces doubt as to my true intention when writing the query. 

Query 2 leaves no doubt as to my intention. Bizarre as it might seem to want a Cartesian product in this instance, the syntax makes clear that I did not generate such a result by accident. The advantage of the explicit syntax in Query 2 is that my intention is clear.

Readability and Reduced Error

Explicit join syntax improves readability and helps toward reducing programmer error. Consider the following business requirements:

  • List products and their subcategories
  • Do that for caps, gloves, and socks
  • Also do it for the accessories in Category 4

Queries 3 and 4 show two attempts to generate these business results. If you're on your game, you'll spot right away that one query gives a wrong result.

--Query 3: Comma-delimited syntax
SELECT psc.Name, p.Name, psc.ProductSubcategoryID, p.ProductSubcategoryID, psc.ProductCategoryID
FROM Production.ProductSubCategory AS psc, Production.Product AS p
WHERE (
        psc.Name='Caps' 
        OR psc.Name='Gloves'
        OR psc.Name='Socks'
      )
      AND psc.ProductSubcategoryID = p.ProductSubcategoryID
      OR psc.ProductCategoryID = 4;

--Query 4: Explicit Join Syntax
SELECT psc.Name, p.Name
FROM Production.ProductSubCategory AS psc INNER JOIN Production.Product AS p
  ON psc.ProductSubcategoryID = p.ProductSubcategoryID
WHERE psc.Name='Caps' 
   OR psc.Name='Gloves'
   OR psc.Name='Socks'
   OR psc.ProductCategoryID = 4;

Query 3's WHERE clause is a badly-ordered jumble to show how a join condition can get "lost" among other conditions that are unrelated to the join. I missed a beat while writing the query, and my example in Query 3 actually generates a wrong result.

Query 4 isolates the join condition in an ON clause. There's no ambiguity. It's clear at a glance how the two tables are being joined. And the WHERE clause becomes much simpler to write.

My first attempt at Query 4 gave the correct result, whereas it took me three tries to get to a correctly-working version of Query 3. (You are seeing Try #2 above). Explicit join syntax not only helps toward making your intentions explicit, it reduces your workload and helps you toward correct results more quickly than might otherwise be the case.

Terminology

What about terminology? I commented earlier on the phrasing "ANSI join syntax". Here are my reasons for not liking that particular phrasing:

  • Reference to ANSI slights the greater portion of the planet
  • The comma-delimited syntax is ANSI syntax as well

I used to speak of "ANSI join syntax" myself. Then I progressed to saying "ISO join syntax" in recognition that the planet did not end at my particular country's borders. Then a friend pointed out that comma-delimited join syntax is fully-supported in the ANSI and ISO SQL standards.

I've come to prefer phrasing that does not convey the incorrect impression that the comma-delimited syntax is somehow non-standard. It may be non-preferred, but it is standard. Hence my use of "explicit join syntax" to describe the practice of specifying join types and conditions in the FROM clause of a query.

My Exhortation

Banish the comma-delimited join syntax from your repertoire. Write joins using explicit syntax so that others who come after you won't have doubts over your intentions. Writing with explicit syntax keeps your intentions clear, it keeps your join conditions separate from the melee of other conditions in the WHERE clause, and you'll benefit from being able to think more clearly about a query and get to correct results faster and with greater confidence. 

AskTom – the personal touch

AskTom has been answering questions from the user community for nearly 16 years. 

But what if that service could have an even more personal touch ?  We are pleased announce the largest advance in AskTom service since it started way back in 2000.

See all the details in the video below for the new service we are launching on April 1st.

New Oracle Cloud Offering – Indexing as a Service (IDXaaS) (I Pity The Fool)

This of course is an April Fools joke. Sorry !! A very exciting announcement !! I’ve recently been promoted within Oracle Corporation to lead their brand new Oracle Cloud offering “Indexing as a Service” (IDXaaS) team, based on my previous work and experience in the indexing space. Yes, I’m both thrilled and excited to be […]

Dealing with URL’s

If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier


SQL> create or replace function utl_url_escape(x varchar2) return varchar2 is
  2  begin
  3    return utl_url.escape(x,true);
  4  end;
  5  /

Function created.

SQL> col escaped_char format a20
SQL> WITH special_chars
  2  AS (SELECT '<' AS sp_char FROM dual UNION ALL
  3      SELECT '>' AS sp_char FROM dual UNION ALL
  4      SELECT '.' AS sp_char FROM dual UNION ALL
  5      SELECT '#' AS sp_char FROM dual UNION ALL
  6      SELECT '{' AS sp_char FROM dual UNION ALL
  7      SELECT '}' AS sp_char FROM dual UNION ALL
  8      SELECT '|' AS sp_char FROM dual UNION ALL
  9      SELECT '\' AS sp_char FROM dual UNION ALL
 10      SELECT '^' AS sp_char FROM dual UNION ALL
 11      SELECT '~' AS sp_char FROM dual UNION ALL
 12      SELECT '[' AS sp_char FROM dual UNION ALL
 13      SELECT ']' AS sp_char FROM dual UNION ALL
 14      SELECT '<' AS sp_char FROM dual UNION ALL
 15      SELECT '`' AS sp_char FROM dual UNION ALL
 16      SELECT '+' AS sp_char FROM dual UNION ALL
 17      SELECT '/' AS sp_char FROM dual UNION ALL
 18      SELECT '?' AS sp_char FROM dual UNION ALL
 19      SELECT '&' AS sp_char FROM dual UNION ALL
 20      SELECT '''' AS sp_char FROM dual)
 21  SELECT sp_char
 22  ,      utl_url.escape(sp_char) AS escaped_char
 23  FROM   special_chars;

S ESCAPED_CHAR
- --------------------
< %3C
> %3E
. .
# %23
{ %7B
} %7D
| %7C
\ %5C
^ %5E
~ ~
[ [
] ]
< %3C
` %60
+ +
/ /
? ?
& &
' '

19 rows selected.

Opatch quick tip

I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error:


C:\oracle\stage\22581007>opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\1210~1.2
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : C:\oracle\product\1210~1.2\cfgtoollogs\opatch\22581007_Mar_31_2016_18_32_02\apply2016-03-31_18-32-01 PM_1.log

Unable to lock Central Inventory.  OPatch will attempt to re-lock.
Do you want to proceed? [y|n]

My first thought was this was due probably to the fact that I’ve got countless installations on this laptop, some running, some stopped, etc etc.

Turns out to be a much simpler issue. When running OPatch on Windows, make sure you are running it with the “As Administrator”.

Simple Smile

3 Days of Oracle Security Training In York, UK

I have just updated the public Oracle Security training dates on our Oracle Security training page to remove the public trainings that have already taken place this year and to add a new training in York for 2016. After the....[Read More]

Posted by Pete On 31/03/16 At 01:53 PM