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
left outer (more...)
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:
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).
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 -
PL/SQL procedure successfully completed.
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'
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).
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).
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 126.96.36.199; 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...)
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...)
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...)
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...)
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...)
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
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...)
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
My title.sql and on_login.sql
Colored prompt is the one of many features of rlwrap.
Connected as simple user:
Connected as sysdba:
SQL*Plus on OEL through putty:
@inc/title “*** Test ***”
I ran the following query on an Oracle 188.8.131.52 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
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 184.108.40.206.
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...)
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...)
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...)
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
select * from