Skip navigation.

DBA Blogs

Log Buffer #450: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-11-20 15:08

This Log Buffer Editions picks few blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege.
  • Lost SYSMAN password OEM CC 12gR5.
  • How Terminal Emulation Assists Easy Data Management.
  • Using EMCLI List Verb to Get Detailed Information of EM Targets.
  • How to change apex_public_user password in ORDS.

SQL Server:

  • When the connection between you and the the target host are multiple servers across the continent, the latency will drive crazy mad.
  • SQLCMD and Batch File magic.
  • Greg Larson walks through the GUI installation process for SQL Server 2016 and explore these new installation options.
  • A Single-Parameter Date Range in SQL Server Reporting Services.
  • Is SQL Server killing your application’s performance?

MySQL:

  • MariaDB Galera Cluster 10.0.22 and Connector updates.
  • Building MaxScale from source on CentOS 7.
  • Orchestrator & Pseudo-GTID for binlog reader failover.
  • InnoDB holepunch compression vs the filesystem in MariaDB 10.1.
  • Open-sourcing PinLater: An asynchronous job execution system.

 

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Links for 2015-11-19 [del.icio.us]

Categories: DBA Blogs

Lost SYSMAN password OEM CC 12gR5

Hans Forbrich - Thu, 2015-11-19 10:42
I run my own licensed Oracle products in-house.  Since it is a very simple environment, largely used to learn how things run and verify what I see at customer sites, it is not very active at all.  But it is important enough to me to keep it maintained.

After a bit of a hiatus in looking at the OEM, which is at 12cR5 patched, I went back and noted that I was using the wrong password.  No problem, I thought: since OMS uses VPD and database security, just change the password in the database.

While I'm there, might as well change the SYSMAN password as well, since I have a policy of rotated passwords.

A few things to highlight (as a reminder for next time):


  • Use the right emctl.  There is an emctl for the OMS, the AGENT and the REPO DB.  In this case, I've installed the OMS under middleware, therefore  
    • /u01/app/oracle/middleware/oms/bin/emctl
  • Check the repository and the listener
  • Start the OMS.  
    • If the message is "Failed to connect to repository database. OMS will be automatically restarted once it identifies that database and listener are up." there are a few possibilities:
      • database is down
      • listener is down
    • If the message is "Connection to the repository failed. Verify that the repository connection information provided is correct." check whether 
      • SYSMAN password is changed or 
      • SYSMAN is locked out
  • To change the sysman password:
    • In database 
      • sqlplus / as sysdba
      • alter user SYSMAN identified by new_pwd account unlock;
    • In oms
      • ./emctl stop oms
      • ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys_pwd -new_pwd new_pwd
      • ./emctl stop oms 
      • ./emctl start oms
And test it out using the browser ...
Categories: DBA Blogs

How Terminal Emulation Assists Easy Data Management

Kubilay Çilkara - Wed, 2015-11-18 21:25
Just about every company will need terminal emulation at some point. Yours may not need it now, but as time goes on, the day will come when you need information that can only be accessed with an emulator. This software allows a computer to basically take on the functionality of an older version. Doing so makes it possible for the user to access data that would otherwise be impossible to find. If you’re not sold on the benefits of using this type of software, consider the following ways that it assists with making data management easier.
Obtain Data from Outdated Systems
This is the most obvious way a terminal emulator helps with data management. Right now, you could have all kinds of data out of reach because it was created with software you no longer used or stored on a platform that differs from the current one on your system.

Without an emulator, you’re basically without options. You simply have to find a workaround. The best solution would involve using a machine with the old operating system installed. This isn’t just extremely inconvenient though; it also isn’t very cost-effective and is sure to become a bottleneck sooner or later.

With terminal emulation, no data ever has to be out of reach. Whether its information from 10 years ago or 20, you can quickly retrieve whatever it is you need.
Access Multiple Terminals at Once
There’s far more these applications can do to assist with data management though. Over time, your company has probably—or will, someday—go through multiple platforms. This means that going back and grabbing the data you need could involve more than one system. If you tried using the aforementioned workaround, you’d be in for a huge challenge. It would take multiple computers, each with a specific operating system and then going between them to get the information you need or cross reference it as necessary.Modern emulators can access as many terminals as you need all on the same computer. Usually, it just involves putting each one on separate tabs. Not only can you get all the info you need, then, you can do it from the screen all at once. This makes it extremely easy to cross reference data from one system against others.
Customize Your Queries Another great benefit that comes with terminal emulation is that you can actually customize your searches to various degrees. For many companies, accessing old data means looking at screens that represent the info in the most rudimentary of ways. There may only be a few colors used for fonts on a black screen. Obviously, this can make data management a bit of a challenge, to say the least.With the right software, though, you can control the font color, the character size, background and more. This makes it much easier to see the information you want, highlight it and otherwise keep track of the data. Never again suffer through old screens from outdated platforms when you need something.
Mobile Functionality
Like just about everything these days, terminal emulators have now gone mobile. You can now pull off emulation from just about anywhere in the world on your mobile device. This is a great way to make emulation possible for any work at home employees who may be working for your company. If you hire on a consultant or freelance professional from somewhere outside the office, mobile functionality means they can now benefit from easy data management. Next time you’re on a business trip and need to access information from yesteryear, the ability to do so will be at your fingertips.

Mobile functionality may not seem like the most important aspect to have as far as emulation is concerned, but it’s better to have the option there than wish it was possible.
Save Money
Data management can be a costly endeavor. Unfortunately, it’s one of those costs your company really can’t hope to avoid. You need to manage your data, so you better find room in your budget. With terminal emulation, you can at least save money on this essential process.

Like we brought up earlier, without an emulator, you’re looking at a lot of hardware in order to make sure vital data is never out of reach, to say nothing of how inconvenient that option would be. You’re also looking at the potential costs of losing that data if anything happens to your dedicated machines. Furthermore, added hardware always comes with extra costs. There’s the space you need, electricity, IT support and more.

In light of those added expenses, simply using emulation software should be a no-brainer. You don’t need any extra hardware and these platforms are designed to stay out of the way until you need them, so they won’t hurt your staff’s ability to use their current machines.
Limitless Scalability
No matter what kind of software we’re talking about, scalability always needs to be a concern. Today, your company may only have so much data to manage. A year from now, though, there’s going to be a lot more. Five years from now, your company’s collection of data will be expansive.Obviously, managing this data efficiently is going to take a resource that can scale without difficulty. Keep in mind that a lot of companies increase their amount of data exponentially. This means scalability is necessary, but so is being able to scale at a moment’s notice to whatever size is needed.Terminal emulation and scalability are virtually inseparable when you have the right software. No alternative solution is going to be able to keep up. Again, if you tried using hardware to manage your data, you can forget about easy scalability and forget about doing so without spending a lot of money in the process.


Data management needs to be a priority for every organization, no matter what industry they’re in. However, simple data management isn’t enough anymore. Instead, you need emulation software that will make it easy, cost-effective and scalable. Otherwise, your business will always be greatly limited in what information it can access and the benefits that could be derived from it.

Mike Miranda writes about enterprise software and covers products offered by software companies like Rocket software about topics such as Terminal emulation,  Enterprise Mobility and more.
Categories: DBA Blogs

Pythian Champions Gender Diversity in Tech

Pythian Group - Wed, 2015-11-18 13:45

 

At Pythian, we embody and value diversity in every form—in our religion (or lack thereof), our geography, our culture, and of course the location we’ve chosen for our home.  We deeply believe that the best teams are diverse and we are committed to increasing our diversity in all these areas.

We’ve been doing time-zone diversity for over a decade and we’ll keep doing it—we’re actually pretty good at it. In support of time zone diversity, we’ve become an industry leader in the art of creating high-performance teams by blending people from all over the world regardless of religion, culture, country of origin or residence. But now it’s time for us to turn our attention to gender diversity.

And so, with this note and video, I am proud to announce the Pythia Program. We’re going to increase the number of brilliant women who work with us and we will take a leadership position in a very important conversation to encourage more women to enter STEM fields and the tech industry.

Inspired by Microsoft, Twitter, Pinterest and Google, Pythian is the first technology company headquartered in Canada to announce its gender stats: 35 percent of the company’s leadership team are women, 27 percent of our managers are women and only 9 percent of technical roles at Pythian are held by women. Pythian also claims a Pythia Index of 56 percent.

The Pythia Index is the ratio of people in a business or in a team that are women leaders or roll up to a woman leader, as a percentage of total headcount. At Pythian the Pythia Index is 56%, which means that 56% of Pythianites are themselves women leaders or report up the org chart to a woman. Conversely, 44% of Pythian employees have zero women leaders all the way up the org chart from them.

So how do we currently compare to industry leaders? According to the most recent published numbers, Microsoft reported that women comprise 29.1 percent of its workforce, but only 16.6 percent work in technical positions and just 23 percent hold leadership roles. Twitter said women fill 10 percent of its technical jobs, with 21 percent in leadership. And women Googlers account for 17 percent of the search giant’s tech jobs, while only 21 percent manage others. Industry averages among all surveyed major US tech firms are 15.6% of technical roles and 29.6% of leadership.*

With this announcement, I am challenging our business to surpass industry averages within no more than three years, and sooner if possible.

The Pythia Program is central to how we plan to realize the best possible talent mix. By acknowledging and strengthening our deliberate talent blending practices, we hope not only to garner interest from potential employees, but to encourage other businesses to set similar diversity goals. This is not a corporate social responsibility initiative, it’s good for business, and it’s good for the technology sector.

Under the Pythia program we will:

  • Continue to focus on eliminating any unconscious gender biases in our recruiting and management practices.
  • Actively promote Pythian as a diversity-focused company so we can get more than our fair share of the top female leaders and tech talent.
  • Help build our future pipeline of female leaders and tech talent by working with and supporting organizations who have programs to encourage more women to go into tech.

This project is very important to me personally and I’ll continue to blog on the subject in the future.

 

Discover more about the Pythia Program.

 

*Source: cnet.com

Categories: DBA Blogs

High “cursor: pin S wait on X” waits?

Pythian Group - Tue, 2015-11-17 14:59

If your system meets any of the following criteria:

– Oracle 12.1.0.2.0 or higher
– Partitioned tables
– Parallel query heavily used
– Bind variables in use, as they should be

and, you’re seeing unusually high “cursor: pin S wait on X” waits, then you may want to know that this week, patch 21834574 was released.

To give you a little bit of background information; the issue is with a new code path introduced in Oracle version 12, which is related to some of the brand new infrastructure life-cycle management (ILM) functionality. This ILM feature – whether you are intentionally using any ILM functionality or not – causes delays when a new child cursor is created in the library cache. On a client’s system we have observed waits of up to 0.3 seconds.

In a nutshell, here’s what’s happening:

– A new parallelized query using bind variables is hard parsed.
– Oracle creates a new cursor, and adds a child cursor.
– Each of your PX slaves will then wait for the following event: “cursor: pin S wait on X”.
– The mutex being waited on is of type Cursor Pin at the location “kkslce [KKSCHLPIN2]”. This is normal and these waits can’t be fully avoided.

But what’s not normal is that the mutex is being waited on for a quarter of a second in each slave session.

Oracle has now released a patch which implements a new _fix_control which can be set to enable the fix once the patch is applied. As always, please consult with Oracle Support before applying this patch and setting this parameter, to make sure that you really are seeing this particular bug. There are others in the current versions of Oracle which share very similar symptoms, and the only way to be certain is to double-check with Oracle support or development.

Happy patching!

 

Discover more about our expertise in the world of Oracle

Categories: DBA Blogs

IoT, Mobility and Cloud Integration Hands-On: Controlling Raspberry Pi GPIO Using Oracle MAF App and Oracle ADF Application Deployed on Oracle Java Cloud Service (Part I)

Internet of Things ecosystem is continuously changing our world and opening a New Digital Era for business. Billions of different sensors on millions of devices collecting information on everything...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Trace Files -- 8a : Using SQL Trace for Performance Evaluations

Hemant K Chitale - Sun, 2015-11-15 09:43
All previous examples of tracing are only to demonstrate enabling tracing and viewing the results.

Now, I present a case where we can use tracing to evaluate performance of queries.  There may be a follow-up post(s) to expand on or add example(s).

I start with a table with 7million rows and more than 750MB in size (larger than the database buffer cache).  This is a multiplied copy of DBA_OBJECTS.  Statistics have not been gathered on the Table.  The Table has no Indexes.

[oracle@ora11204 ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 15 22:51:15 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_session.session_trace_enable(waits=>TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from all_objects_many_list where owner = 'HEMANT';

COUNT(*)
----------
256

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8944.trc

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora11204 ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8944.trc MY_ALL_OBJ_M_L_QRY.PRF sys=NO

TKPROF: Release 11.2.0.4.0 - Development on Sun Nov 15 22:53:12 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


[oracle@ora11204 ~]$

We expect a Full Table Scan against the Table.  But the tkprof output shows something else as well.

SQL ID: 9s5xc0z5khvq7 Plan Hash: 3546461632

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("ALL_OBJECTS_MANY_LIST")
FULL("ALL_OBJECTS_MANY_LIST") NO_PARALLEL_INDEX("ALL_OBJECTS_MANY_LIST") */
:"SYS_B_2" AS C1, CASE WHEN "ALL_OBJECTS_MANY_LIST"."OWNER"=:"SYS_B_3" THEN
:"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "HEMANT"."ALL_OBJECTS_MANY_LIST"
SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8")
"ALL_OBJECTS_MANY_LIST") SAMPLESUB


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.88 123 65 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.88 123 65 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Optimizer mode: ALL_ROWS
Parsing user id: 87 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=65 pr=123 pw=0 time=887883 us)
3289 3289 3289 TABLE ACCESS SAMPLE ALL_OBJECTS_MANY_LIST (cr=65 pr=123 pw=0 time=20429 us cost=19 size=149234 card=5146)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 35 0.08 0.70
db file scattered read 11 0.07 0.17
********************************************************************************

SQL ID: bpgst4ajh1wb2 Plan Hash: 2662061148

select count(*)
from
all_objects_many_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.68 12.88 97792 97798 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.68 12.91 97794 97800 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=97798 pr=97792 pw=0 time=12886862 us)
256 256 256 TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97798 pr=97792 pw=0 time=37649164 us cost=26772 size=26520 card=1560)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
reliable message 1 0.03 0.03
enq: KO - fast object checkpoint 1 0.00 0.00
direct path read 1542 0.18 12.54
SQL*Net message from client 2 18.06 18.06
********************************************************************************

Note the additional SQL that was executed before my query. That is the Dynamic Sampling query identified by the OPT_DYN_SAMP comment.  This query, although being recursive (indicated by "dep=1" in the raw trace file) is not excluded by the SYS=NO, because it is actually executed by the user "HEMANT" (indicated by "uid=87", "lid=87", where 87 is the userid of  database user 'HEMANT').

Let's see a portion of the raw trace file relating to this Dynamic Sampling query.

PARSING IN CURSOR #140417579320304 len=616 dep=1 uid=87 oct=3 lid=87 tim=1447599117299160 hv=3408424647 ad='7f8bb8f0' sqlid='9s5xc0z5khvq7'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("ALL_OBJECTS_MANY_LIST") FULL("ALL_OBJECTS_MANY_LIST") NO_PARALLEL_INDEX("ALL_OBJECTS_MANY_LIST") */ :"SYS_B_2" AS C1, CASE WHEN "ALL_OBJECTS_MANY_LIST"."OWNER"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "HEMANT"."ALL_OBJECTS_MANY_LIST" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "ALL_OBJECTS_MANY_LIST") SAMPLESUB
END OF STMT
PARSE #140417579320304:c=1000,e=670,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1447599117299155
EXEC #140417579918768:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1447599117300059
FETCH #140417579918768:c=1000,e=20,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1447599117300113
CLOSE #140417579918768:c=0,e=7,dep=2,type=3,tim=1447599117300148
EXEC #140417579320304:c=1000,e=1319,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=1,plh=3546461632,tim=1447599117300542
WAIT #140417579320304: nam='db file sequential read' ela= 17 file#=4 block#=330 blocks=1 obj#=35014 tim=1447599117300633
WAIT #140417579320304: nam='db file sequential read' ela= 12384 file#=4 block#=1044 blocks=1 obj#=35014 tim=1447599117313168
WAIT #140417579320304: nam='db file sequential read' ela= 30217 file#=4 block#=1839 blocks=1 obj#=35014 tim=1447599117343572
WAIT #140417579320304: nam='db file sequential read' ela= 14367 file#=4 block#=4273 blocks=1 obj#=35014 tim=1447599117358103
WAIT #140417579320304: nam='db file sequential read' ela= 26739 file#=4 block#=4666 blocks=1 obj#=35014 tim=1447599117384964
WAIT #140417579320304: nam='db file sequential read' ela= 15278 file#=4 block#=8352 blocks=1 obj#=35014 tim=1447599117400429
WAIT #140417579320304: nam='db file scattered read' ela= 55038 file#=4 block#=14030 blocks=8 obj#=35014 tim=1447599117455816

*** 2015-11-15 22:51:57.527
WAIT #140417579320304: nam='db file sequential read' ela= 71363 file#=4 block#=14417 blocks=1 obj#=35014 tim=1447599117527415
WAIT #140417579320304: nam='db file sequential read' ela= 81866 file#=4 block#=18668 blocks=1 obj#=35014 tim=1447599117609519
WAIT #140417579320304: nam='db file sequential read' ela= 65981 file#=4 block#=23052 blocks=1 obj#=35014 tim=1447599117675718
WAIT #140417579320304: nam='db file sequential read' ela= 14640 file#=4 block#=25282 blocks=1 obj#=35014 tim=1447599117690503
WAIT #140417579320304: nam='db file sequential read' ela= 65302 file#=4 block#=26230 blocks=1 obj#=35014 tim=1447599117755921
WAIT #140417579320304: nam='db file scattered read' ela= 51919 file#=4 block#=27340 blocks=8 obj#=35014 tim=1447599117808090
WAIT #140417579320304: nam='db file scattered read' ela= 70379 file#=4 block#=27479 blocks=8 obj#=35014 tim=1447599117878651

Note the mix of single-block 'db file sequential read' waits and multi-block 'db file scattered read' waits.  Also, note from the block numbers (indicated by "block#") that the reads are not really sequential blocks from disk.  The first block read is block 330 of file 4.  That is the header block of the Table (i.e. Segment) (we could query DBA_SEGMENTS to confirm this).  After that, Oracle has randomly identified blocks to scan.  The multi-block reads can be identified by "blocks=8"  (in this extract we see only 3 multi-block reads, as I haven't presented the entire raw trace file here.  The Dynamic Sampling had 35 'db file sequential read' waits and 11 'db file scattered read waits' -- which we see in the tkprof output.  The tkprof shows that although there were physical block read requests for 123 blocks, only 65 were actually sampled.  Dynamic Sampling took 0.88seconds (i.e. added 0.88seconds to the time to execute my query0.

Dynamic Sampling is done using Buffered reads.  However, the Full Table Scan of the large table is executed using Direct Path reads.  That is why the wait event for the count(*) query is different -- it is a 'direct path read' wait that occurs many times.

ARSING IN CURSOR #140417579185064 len=65 dep=0 uid=87 oct=3 lid=87 tim=1447599118188941 hv=2734748002 ad='7f956058' sqlid='bpgst4ajh1wb2'
select count(*) from all_objects_many_list where owner = 'HEMANT'
END OF STMT
PARSE #140417579185064:c=24996,e=1098676,p=127,cr=184,cu=0,mis=1,r=0,dep=0,og=1,plh=2662061148,tim=1447599118188936
EXEC #140417579185064:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2662061148,tim=1447599118189025
WAIT #140417579185064: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=35014 tim=1447599118189093
WAIT #140417579185064: nam='reliable message' ela= 33598 channel context=2123970008 channel handle=2123913464 broadcast message=2125033832 obj#=35014 tim=1447599118223060
WAIT #140417579185064: nam='enq: KO - fast object checkpoint' ela= 3905 name|mode=1263468550 2=65560 0=1 obj#=35014 tim=1447599118227031
WAIT #140417579185064: nam='direct path read' ela= 32287 file number=4 first dba=331 block cnt=13 obj#=35014 tim=1447599118259487
WAIT #140417579185064: nam='direct path read' ela= 97 file number=4 first dba=345 block cnt=15 obj#=35014 tim=1447599118259681
WAIT #140417579185064: nam='direct path read' ela= 9530 file number=4 first dba=361 block cnt=15 obj#=35014 tim=1447599118269284
WAIT #140417579185064: nam='direct path read' ela= 81 file number=4 first dba=377 block cnt=15 obj#=35014 tim=1447599118269448
WAIT #140417579185064: nam='direct path read' ela= 4879 file number=4 first dba=393 block cnt=15 obj#=35014 tim=1447599118274418
WAIT #140417579185064: nam='direct path read' ela= 18847 file number=4 first dba=409 block cnt=15 obj#=35014 tim=1447599118293388
WAIT #140417579185064: nam='direct path read' ela= 3692 file number=4 first dba=425 block cnt=15 obj#=35014 tim=1447599118297175
WAIT #140417579185064: nam='direct path read' ela= 2612 file number=4 first dba=441 block cnt=15 obj#=35014 tim=1447599118299862
WAIT #140417579185064: nam='direct path read' ela= 42872 file number=4 first dba=514 block cnt=62 obj#=35014 tim=1447599118342814
WAIT #140417579185064: nam='direct path read' ela= 12735 file number=4 first dba=576 block cnt=64 obj#=35014 tim=1447599118355614
WAIT #140417579185064: nam='direct path read' ela= 41997 file number=4 first dba=642 block cnt=62 obj#=35014 tim=1447599118397975
WAIT #140417579185064: nam='direct path read' ela= 11584 file number=4 first dba=704 block cnt=64 obj#=35014 tim=1447599118409765
WAIT #140417579185064: nam='direct path read' ela= 10909 file number=4 first dba=770 block cnt=62 obj#=35014 tim=1447599118420916
WAIT #140417579185064: nam='direct path read' ela= 10969 file number=4 first dba=832 block cnt=64 obj#=35014 tim=1447599118432086

Before we look at the 'direct path read' waits, please see the 'reliable message' and 'enq: KO - fast object checkpoint' waits.  Oracle will always cause a segment checkpoint before it starts doing Direct Path reads.  This is to ensure that any dirty buffers for that segment (the table "all_objects_many_list") are flushed to disk before Oracle starts using Direct Path reads to bypass the Buffer Cache. 

Next, if we look at the 'direct path read' waits, we see that they are reported differently from the 'db file sequential || scattered read' waits.  Instead of reporting "block#" and "blocks", this wait event reports "dba" ("data block address") and "block cnt".  Note the first read is of 13 blocks starting at block number 331 (after the Segment Header).  The next read starts at block number 345 (which is 331+13+1).  This is a 15 block read.  The third read starts at block 361 (which is 345+15+1).  Thus, we can see that the Table is laid out contiguous extents in the same datafile (file=4).

The "ela=" time is the time in micrseconds for each read.  The obj# is the Object ID of the table.
During the Full Table Scan, there were 1,542 occurrences of the 'direct path read' wait event.  The average read time for 'direct path read' waits can be computed by dividing the 12.54seconds over the 1,542 occurrences.  But we must note that some of these reads were 16 or fewer blocks and others were 64 or so blocks. The first read was 32,287microseconds,  the next was 97microseconds.  It is possible that either the OS or the Storage Cache did some pre-fetching of blocks.  But note the fluctuations in read wait times.  Averages can be misleading.  It is a good idea to sometimes walk through the trace file (preferably using a tool) to determine a Histogram of these waits.

If we look at the small extract of the trace file that I've pasted here, we can see that the reads were growing.  There were 64 blocks read in the last wait event that I've pasted.  Reviewing the entire trace file, I see that "64" is the highest  reported "block cnt" figure.  However, the distribution of extent size goes upto 8MB (1024 blocks), yet the largest read was 64 blocks because of how Oracle has auto-configured 'db_file_multiblock_read_count'.  Remember, tkprof does NOT show you the sizes of the multi-block reads either as 'db file scattered read' or 'direct path read'.  You have to read or parse the raw trace file to identify this.


SQL> select blocks, count(*) 
2 from dba_extents
3 where owner = 'HEMANT'
4 and segment_name = 'ALL_OBJECTS_MANY_LIST'
5 group by blocks
6 order by 1;

BLOCKS COUNT(*)
---------- ----------
8 16
128 63
1024 88

SQL>
SQL> show parameter db_file_multi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 106
SQL>

The DBA here had not specifically configured db_file_multiblock_read_count to 128 (I would certainly have done so, and would have got better performance on the Full Table Scan).
,
,
,
Categories: DBA Blogs

Oracle Cloud Platform Partner Webcast: Oracle IoT Cloud Service Driving value in the connected world

Partner Webcast: Oracle IoT Cloud Service Driving value in the connected world ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Comparing Schemas Between Hive Clusters

Pythian Group - Fri, 2015-11-13 13:36

 

When running several different hive instances, we found the process of maintaining/confirming synchronization to be quite time consuming. While several tools made available by Cloudera and other frameworks do provide a visual interface for an individual instance of hive, there was no available tool for comparing across clusters. We came up with a useful way to compare hive schemas between clusters.

Our process contains 3 main steps:

  1. Fetch the schema from all hive environments, store the schema as a dictionary in a file.
  2. Create a class to perform comparisons leveraging python list, dict and pandas dataframe structures.
  3. Display the results/visualize the changes using a web interface (this approach uses python Flask).

 

Step 1- Fetch the Schema From all Environments
To fetch the schema we use the hive metastore api via the hive-thrift.py package.

First, we create a socket to connect to the hive thrift server.

transport = TSocket.TSocket(host, port)
transport = TTransport.TBufferedTransport(transport)
protocol = TBinaryProtocol.TBinaryProtocol(transport)

global metastore_client
metastore_client = ThriftHiveMetastore.Client(protocol)
transport.open()

Once that is done, it’s straightforward to get all of the tables in a db (there are lots of other great methods  that have been documented).
db = 'myDatabase'
tables = metastore_client.get_all_tables(db)

Once we get the tables, we can call the get_fields() method
for table in tables:
for field in metastore_client.get_fields(db, table):
# field is a named tuple FieldSchema(comment, type, name)
print field

Using the methods above, we retrieve all of the values and store them in a dictionary, locally in a text file, one file per environment.
{'myDatabase': {
'tbl_customer':
[
FieldSchema(comment=None, type=int, name='user_id'), FieldSchema(comment=None, type='string', name='first_name'), FieldSchema(comment=None, type='string', name='last_name')
],
'tbl_orders':
[
FieldSchema(comment=None, type=int, name='order_id'), FieldSchema(comment=None, type='string', name='item_id'), FieldSchema(comment=None, type='string', name='price')
],

etc..

Note that thrift gives us a tuple for the ddl, so for ease, we declare this named tuple locally.
FieldSchema = collections.namedtuple(‘FieldSchema’, [‘comment’,’type’, ‘name’])

 

Step 2 – Create Class to Compare Dictionaries

Now that we have a separate schema file for each hive instance, we can load these files into dictionaries and begin to compare them.

f_stage_dict = open('schema_files/stage_schema_thrift.out')
f_prod_dict = open('schema_files/prod_schema_thrift.out')
d_stage = eval(f_stage_dict.read())
d_prod = eval(f_prod_dict.read())

Create a class to compare the dictionaries. Comparisons are done leveraging a variety of nice python tools (lists, set comparison, pandas dataframes, etc..). More detail in the repo.

Class DictCompare:
def get_fields_by_table(self, db, table):...
# returns list of fields for tableA and tableB
def get_databases(self):...
# union of all dbs in all schemas A,B
def compare_fields(self, db, table)...
# returns tablediff tuple showing all field differences
def compare_dbs(self, db)...
#returns list of differences between dbs
tables_a = [table for table in self.dict_a[db]] tables_b = [table for table in self.dict_b[db]] db_diffs_a = set(tables_a).difference(set(tables_b))
db_diffs_b = set(tables_b).difference(set(tables_a))
.....

Instantiate the class by passing the path of the schema files, and the friendly names used in the config.json file.
dc = sc.DictCompare(schema_files_path,”dev”, “stage” )
dbs = dc.get_databases()

# show all of the databases..
print "DBS=",dbs

# get all diffs for a given database
compare_dict = dc.compare_dbs("my_db")

 

Step 3 Display the Results / Visualize the Changes

I used a quick and simple python flask website to display the differences. Note there is a form on the page that allows users to select the hive instances to compare.

@app.route('/home.html')
@app.route('/home')
def homepage():
form=EnvForm(csrf_enabled=False)
env_a = form.env_a.data # 'stage', for example
env_b = form.env_b.data # 'prod', for example
dc = sc.DictCompare(schema_files_path,env_a, env_b )
dbs = dc.get_databases()
return render_template("home.html", dbs=dbs, a_name =dc.a_name, b_name = dc.b_name,form =form )

Source code (open sourced under Apache2 license):

I Look forward to any comments or feedback.

 

Discover more about our expertise in Big Data and Infrastructure technologies. 

Categories: DBA Blogs

Log Buffer #449: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-11-13 13:19

 

This Log Buffer Edition covers some of the niftiest blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • OBIEE 11g and Essbase – Faking Federation Using the GoURL.
  • You can use one of these to link an exception name with an Oracle error number. Once you have done this, you can use the exception name in the exception block which follows the declaration.
  • This is a short post to help out any “googlers” looking for an answer to why their 12.1.0.5 EM Cloud Control install is failing in the make phase with ins_calypso.mk.
  • A short video that Jonathan Lewis did at the OTN lounge at RMOUG a couple of years ago has just been posted on YouTube. It’s about the improvements that appear in histograms in 12c.
  • Changing the name of the server or load-balancing server handling your BI Publisher workload for OEM can be done with a single EM CLI command.

SQL Server:

  • Manoj Pandey was going through some sample Scripts provided by Microsoft SQL Server team on their site, and was checking the JSON Sample Queries procedures views and indexes.sql script file.
  • When you open Excel 2016, go to the Power Pivot tab, click the Mange button to bring up Power Pivot window In Power Pivot, click Get External Data, choose From Other Sources Choose Microsoft Analysis Services.
  • After the introduction of the StringStoresCompatibilityLevel property in SSAS dimension after SQL Server 2012, SSAS database designer may try to create a MOLAP dimension with more unique values than what is allowed in previous SQL Server versions.
  • After SQL Server 2012 is released, SSISDB provides stored procedures to create SSIS package executions. There is one problem though.
  • This is the fourth installment in a blog series. The previous entry is located here Based on the previous blogs in this series, you should have gotten your database hosted in WASD by now & secured access to your server.

MySQL:

  • db4free.net finally runs MySQL 5.7 which was released on October 21.
  • What Does The Universal Scalability Law Reveal About MySQL?
  • There are many dimensions by which a DBMS can be better for small data workloads: performance, efficiency, manageability, usability and availability.
  • The new Mydumper 0.9.1 version, which includes many new features and bug fixes, is now available.
  • Nginx is well-known for its ability to act as a reverse-proxy with small memory footprint. It usually sits in the front-end web tier to redirect connections to available backend services, provided these passed some health checks.

 

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

US-EU Safe Harbor Framework Invalidated – It’s time to Love Your Data!

Pythian Group - Fri, 2015-11-13 12:43

Pythian has been closely following the developments in the EU relating to the invalidation of the US-EU Safe Harbor Framework.

On Friday, November 6, 2015, the European Commission issued guidance on the cross-border data transfer issues. This guidance spoke more to “alternative transfer tools” available to legitimize data flows to jurisdictions deemed “not adequate,” like the United States.

If you do transfer data and rely on Safe Harbor principles, we recommend you:

Keep Calm
Don’t rush to other transfer mechanisms that may turn out to be less than ideal.

Love your data!
Consider:
What personal data you are transferring outside the EU?
Where is it going?
What arrangements have you made to ensure that it is adequately protected?

Carry on and call Pythian
We can help you mitigate your risk of incidex* and prepare you for the pending EU regulation, General Data Protection Regulation (GDPR) slated to become law by end of 2015 and will mandate that all companies with EU customers follow the new rules wherever they’re based by 2018.

*Incidex: Today’s formula for total cost of ownership isn’t the traditional capex + opex + long-term expenses. Instead, there’s another more important cost component in today’s application landscape that Pythian refers to as “incidex”, or the cost of not investing in technologies that will secure and protect your revenue generating systems. If you redefine TCO as capex plus opex plus incidex, it’s a more holistic approach. And you can use the holistic TCO analysis to justify an investment in security technology.

Categories: DBA Blogs

Update hinted for wrong index

Bobby Durrett's DBA Blog - Fri, 2015-11-13 10:52

I worked with our support team to improve the performance of a PeopleSoft Financials update statement yesterday. The update statement had an index hint already in it but the index was not the best one of the available indexes.

Here is the original update statement:

UPDATE /*+ INDEX(B PSCPYMNT_VCHR_XREF) */
 PS_PYMNT_VCHR_XREF B
 SET BANK_SETID = :1,
 BANK_CD = :2,
 BANK_ACCT_KEY = :3,
 PYMNT_METHOD = :4,
 BANK_ACCT_SEQ_NBR = :5,
 EFT_LAYOUT_CD = :6,
 STL_THROUGH = :7
 WHERE B.REMIT_SETID = 'XYZ'
 AND B.REMIT_VENDOR = :8
 AND B.PYMNT_SELCT_STATUS = 'N'
 AND B.PYMNT_ID = ' '
 AND B.BANK_ACCT_KEY NOT LIKE ('EXP%')

I listed out the columns for the indexes on the table using the “querytuning” part of my standard sql tuning scripts.

Here are the columns for the hinted index:

REMIT_SETID
REMIT_VENDOR
VNDR_LOC

The where clause includes only the first two columns.

But another similar index, PSEPYMNT_VCHR_XREF, exists with these columns:

REMIT_SETID
REMIT_VENDOR
PYMNT_SELCT_STATUS

The where clause has all three of these columns. So, why was the original query hinted this way? Does the E index not work better than the C index? I ran this query to see how selective the condition PYMNT_SELCT_STATUS = ‘N’ is.

>select PYMNT_SELCT_STATUS,count(*)
 2 from PS_PYMNT_VCHR_XREF B
 3 WHERE B.REMIT_SETID = 'XYZ'
 4 AND B.REMIT_VENDOR = '12345678'
 5 group by PYMNT_SELCT_STATUS;

P COUNT(*)
- ----------
C 5
N 979
P 177343
X 5485

I included the conditions on the first two columns that both indexes share, but removed the other conditions from the original update. A count on the number of rows that meet the conditions of only these two columns shows how many rows the original index will have to use to check the remaining where clause conditions.

I grouped by PYMNT_SELCT_STATUS to see how many rows met the condition PYMNT_SELCT_STATUS = ‘N’ and how many did not. Grouping on PYMNT_SELCT_STATUS shows how many rows the new index will use to check the remaining conditions in the where clause. I ran this query to see if the second index would use fewer rows than the first.

This query showed that only 979 of the over 180,000 rows met the condition. This made me think that the E index which includes PYMNT_SELCT_STATUS has a good chance of speeding up the original update. I ran a count with a hint forcing the C index and then again forcing the E index:

>
>set timing on
>
>select /*+ INDEX(B PSCPYMNT_VCHR_XREF) */ count(*)
 2 from PS_PYMNT_VCHR_XREF B
 3 WHERE B.REMIT_SETID = 'XYZ'
 4 AND B.REMIT_VENDOR = '12345678'
 5 AND B.PYMNT_SELCT_STATUS = 'N'
 6 AND B.PYMNT_ID = ' '
 7 AND B.BANK_ACCT_KEY NOT LIKE ('EXP%');

 COUNT(*)
----------
 982

Elapsed: 00:13:52.53
>
>select /*+ INDEX(B PSEPYMNT_VCHR_XREF) */ count(*)
 2 from PS_PYMNT_VCHR_XREF B
 3 WHERE B.REMIT_SETID = 'XYZ'
 4 AND B.REMIT_VENDOR = '12345678'
 5 AND B.PYMNT_SELCT_STATUS = 'N'
 6 AND B.PYMNT_ID = ' '
 7 AND B.BANK_ACCT_KEY NOT LIKE ('EXP%');

 COUNT(*)
----------
 982

Elapsed: 00:00:01.28

The original hint caused the select count(*) query to run in 13 minutes while the new hint caused it to run in 1 second. Clearly the new E index causes the query to run faster!

The developer that I was working with found the problem update statement in some PeopleCode and was able to edit the hint forcing it to use the better index. We migrated the modified code to production and the user was able to run the update statement without the web site timing out. Prior to the change the user was not able to complete the update because the SQL statement took so long it exceeded our application server timeout.

Bobby

 

Categories: DBA Blogs

Cloud Registered Level in Oracle Partner Network

As you may know, we announced a new entry level into OPN a few weeks ago at OOW15. We are now able to take partner applications via Oracle Partner Store. This means effective today, companies not...

We share our skills to maximize your revenue!
Categories: DBA Blogs