SQL Mini-challenge entry: Craig Martin v4

by Craig Martin

So from my last post Brendan Furey pointed out one minor detail.. that my code was seriously flawed. It only returned the results from one of the criteria found. Picky, picky :) So this is my last effort.. Back up to 12 buffers, but still works when you change the parameters:

select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id, l.city
from dual
    left outer  (more...)

SQL Mini-Challenge Update

The SQL Mini-Challenge is now closed. The judges will be Kyle Hailey, Tim Gorman, and Iggy Fernandez. The winner will be announced in the fall issue of the NoCOUG Journal.

Correctness is the primary screening criterion that will be used by the judges. Submissions are expected to produce the same results as the original query even if the data changes. The following script has been constructed to test submissions:

update employees
set
  first_name='Stevie',
  last_name='Wonder',
  hire_date=sysdate,
  job_id='AD_VP',
  department_id=50
 (more...)

OCP 12C – Index and Table Enhancements

Table Enhancements Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application. To create an invisible column: [crayon-544665ad911af900442543/]  You can’t create invisible columns on : External [...]

The post OCP 12C – Index and Table Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
            999
 
SQL>

OCP 12C – ADR and Network Enhancements

ADR enhancements In oracle 12c the Automatic Diagnostic Repository contains a new log directories with 2 subdirectories : DDL Debug The DDL log When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log. To enable DDL logging [...]

The post OCP 12C – ADR and Network Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

OCP 12C – Emergency Monitoring, Real-Time ADDM

Emergency Monitoring Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung. Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening. You can access real-time performance data from ASH and access [...]

The post OCP 12C – Emergency Monitoring, Real-Time ADDM appeared first on Oracle DBA Scripts and Articles (Montreal).

Plan depth

A recent posting on OTN reminded me that I haven’t been poking Oracle 12c very hard to see which defects in reporting execution plans have been fixed. The last time I wrote something about the problem was about 20 minhts ago referencing 11.2.0.3; but there are still oddities and irritations that make the nice easy “first child first” algorithm fail because the “depth” calculated by Oracle doesn’t match the “level” that you would get from (more...)

Dallas Oracle Users Group Performance Meetup

I spoke at a one day DOUG meeting yesterday. It was pretty cool. Very small intimate group of about 50. The speakers were Nitin Vengurlekar, Charles Kim, Cary Millsap and myself. All are Ace Directors and either work at Viscosity or Enkitec. As a bonus, Tanel Poder showed up to weigh in on some open discussion. Anyway, I thoroughly enjoyed it. I promised the group I would post my slides and a zip file with (more...)

How to setup git as a daemon

This is a quick post on using git on a server. I use my Synology NAS as a fileserver, but also as a git repository server. The default git package for Synology enables git usage on the command line, which means via ssh, or via web-DAV. Both require a logon to do anything with the repository. That is not very handy if you want to clone and pull from the repository. Of course there are (more...)

Learning Spark Lightning-Fast Big Data Analytics by Holden Karau, Andy Konwinski, Patrick Wendell, Matei Zaharia; O’Reilly Media

Apache Spark started as a research project at UC Berkeley in the AMPLab, which focuses on big data analytics. Spark is an open source cluster computing platform designed to be fast and general-purpose for data analytics - It's both fast to run and write. Spark provides primitives for in-memory cluster computing: your job can load data into memory and query it repeatedly much quicker than with disk-based systems like Hadoop MapReduce. Users can write applications (more...)

Friday Philosophy – Is Dave Working?

Is Dave across the desk from you working at the moment? Or is he goofing off? You can’t see his screen but I reckon you can make a fair stab at what he is up to, without recourse to any sort of IT monitoring systems at all. How?

How fast is Dave typing?

If Dave is typing fast, he is almost certainly not working. He’s goofing. There are very few things you can do when (more...)

sreadtim

Here’s a question that appeared in my email a few days ago:

 

Based on the formula: “sreadtim = ioseektim + db_block_size/iotrfrspeed” sreadtim should always bigger than ioseektim.

But I just did a query on my system, find it otherwise, get confused,

SQL> SELECT * FROM SYS.AUX_STATS$;<

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    10-08-2014 10:45
SYSSTATS_INFO                  DSTOP                                     10-10-2014 10:42
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     680.062427
SYSSTATS_MAIN                   (more...)

NZOUG14 Beckons

New Zealand is famous for Kiwis, pristine landscape, and New Zealand Oracle User Group (NZOUG) conference.  The location of choice is New Zealand when it comes to making Lord of the Rings and making Oracle Lord of the Databases.


NZOUG 2014 will be held 19–21 November in the Owen G. Glenn Building at the University of Auckland. The main conference will be held on the 20th and 21st, preceded by a day of workshops (more...)

Setting Up Chargeback in EM 12.1.0.4

Some time back, I posted a blog entry about Metering and Chargeback in Enterprise Manager, where I covered the basic capabilities of the Chargeback Application. It’s taken me some time to get back to the promised followup blog post on setting up Chargeback, what with Oracle Open World getting in the way of things, but that’s what I’m finally doing now.

Chargeback can be setup in EM12c by any user with the EM_CBA_ADMIN role, or (more...)

Just a couple of screenshots of sqlplus+rlwrap+cygwin+console

I previously wrote that I peeped the idea about showing the session information in terminal title from Timur Akhmadeev’s screenshots, and Timur wrote:

I’m using (a bit modified) Tanel Poder’s login.sql available in his TPT scripts library: http://tech.e2sn.com/oracle-scripts-and-tools

Scripts:
Tanel’s i.sql
My title.sql and on_login.sql

Colored prompt is the one of many features of rlwrap.

Screenshots:
Connected as simple user:
baikal-xtender
Connected as sysdba:
xtsql-sysdba

SQL*Plus on OEL through putty:
putty-to-oel-sqlplus

@inc/title “*** Test ***”
inc-title-test

Bug 8477973

I ran the following query on an Oracle 11.1.0.6 database but it failed with an ORA-02020:
 
SQL> SELECT ppc.sttlmnt_dt day_date
  2  FROM   vrm_d18_ppc   ppc,
  3         meter_nhh     mtr,
  4         mtd_registers reg
  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr
  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id
  7  AND    ppc.prfl_clss_id = mtr.profile_class_id
  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id
  9  AND    reg. (more...)

Update OEM Harvester after 12.1.0.4 Upgrade

After we upgraded our OMS environment we realized that our Harvester wasn’t uploading data to MOS anymore.  Of course, you say, you just replaced your former ORACLE_HOME with a new home for 12.1.0.4.

Procedure:

1. Download p5567658_120030_Linux-x86-64.zip or the latest OCM installer for your environment.  You’ll find a tab for downloading OCM on your front page in MOS.  While you are there you should also download the Quick Start guide.

2. Unzip (more...)

DevOps and Databases?

I’m super looking forward to next weeks DevOps Enterprise Summit in San Francisco. You might ask “Why?!” since I’m a database guy and not a DevOps guy.  I can understand that reaction.

I don’t know about you, but when I hear the term DevOps I have to roll my eyes and think “oh, the latest greatest tech industry buzzword.” So why would I as a DBA care about DevOps? With DevOps, as with (more...)

What to do if edb360 takes long to run

Every once in a while it comes to my attention that edb360 takes several hours to run. What can be done? My advice is to let it run for several hours if possible. In most environment it completes in less that 1 hour, but I have seen cases where it may take 5 or 6. The reason is simple: too many SQL statements to execute. And some of those queries are executed on top of (more...)

Tweaked bind variable script

I modified the bind variable extraction script that I normally use to make it more helpful to me.

Here was my earlier post with the old script: blog post

Here is my updated script:

set termout on 
set echo on
set linesize 32000
set pagesize 1000
set trimspool on

column NAME format a3
column VALUE_STRING format a17

spool bind2.log

select * from 
(select distinct
to_char(sb.LAST_CAPTURED,'YYYY-MM-DD HH24:MI:SS') 
  DATE_TIME,
sb.NAME,
sb.VALUE_STRING 
from 
DBA_HIST_SQLBIND  (more...)