DBA Blogs

Endless executing get_ddl

Tom Kyte - Wed, 2017-04-05 20:26
Hello, I have problem with executing dbms_metadata.get_ddl. You can find sqript with issue here: https://livesql.oracle.com/apex/livesql/file/content_ES4SXSIQNK2JLYFHH3V752UA6.html Why does get ddl procedure lasting for a long time(endless, mo...
Categories: DBA Blogs

Jetpack 4.8 outage

Bobby Durrett's DBA Blog - Wed, 2017-04-05 17:18

I just got my bobbydurrettdba.com blog back online. It seems that an automatic update of Jetpack to version 4.8 took it down last night. I thought I would post a quick comment on how I resolved it because it is slightly different from what the Jetpack support site says.

When I tried to get into my blog today I just got a white screen saying there was a problem. I could not get into the wp-admin screen to do anything. Then I found out that Jetpack 4.8 had been pushed out and had brought down a lot of WordPress sites. Then I found out that Jetpack 4.8.1 had just come out today to fix it.

Here is the url for the 4.8.1 fix to this issue: fix

The fix points to the manual plugin install url which talks about deleting your /wp-content/plugins/jetpack folder and then manually installing by downloading the 4.8.1 jetpack zip, unzipping it, and ftping it up to /wp-content/plugins.

But all I had to do, after backing up /wp-content/plugins/jetpack was to rename it to /wp-content/plugins/jetpack.old. Then I was able to get into my site and to update the plugin to 4.8.1 through the normal web-based process.

Strangely enough the Jetpack plugin update removed my /wp-content/plugins/jetpack.old directory and replaced it with /wp-content/plugins/jetpack.

Here is what the directory looked like after I ran the update from my blogs admin page:

I never did ftp the 4.8.1 jetpack directory over though I had it unzipped and ready to go on my laptop.

Anyway, I didn’t delete my jetpack directory.  I just renamed it to jetpack.old. Then I ran the normal plugin update process.

Bobby

p.s. My site stats for today are not looking so good with the blog down all day:

Categories: DBA Blogs

12.2 Some Cool Partitioning New Features (Big Wheels)

Richard Foote - Wed, 2017-04-05 03:21
I previously discussed just how easy it is to convert online a non-partitioned table to be partitioned with Oracle Database 12.2. Thought I might run through a number of really cool new partitioning features and capabilities that were also introduced in 12.2. To start, I’m just going to create a basic range-partitioning table and populate […]
Categories: DBA Blogs

Limit the number of opening and closing oracle sessions.

Tom Kyte - Wed, 2017-04-05 02:06
Hi all, Issue: Large number of opening and closing Oracle database sessions (on average 100k per hour).App is loading files into database by using sqlldr and for each file creating a new session. After loading session is closed. Probably it has n...
Categories: DBA Blogs

How to use synonym to repoint the table

Tom Kyte - Wed, 2017-04-05 02:06
Hi, I am using 10g, other than renaming of table to swap the latest result from the data loading. Instead, this is not created as table, but Materialized view. How do we swap the latest result from materialized view but not interrupting the existi...
Categories: DBA Blogs

IGNORE_ROW_ON_DUPKEY_INDEX like hint for Foreign Key Constraint Violation.

Tom Kyte - Wed, 2017-04-05 02:06
Hi, Like we have IGNORE_ROW_ON_DUPKEY_INDEX hint which will say to ignore any duplicates that are there in my select query, do we have any hint to say ignore records which violated foreign key constraints and load them? For example: if I have ...
Categories: DBA Blogs

how to replace the line break with tab space in database in all the column of the database

Tom Kyte - Wed, 2017-04-05 02:06
We have one database which has 10 table contains 10 columns in each table, which contain Line break data(\n) in 5 Columns I Want all the 5 different columns data to be replaced with spaces if it contains line break. I want it in a single query at sch...
Categories: DBA Blogs

Extra line at the end of file while calculating checksum in oracle

Tom Kyte - Wed, 2017-04-05 02:06
Hi Tom, This is the file generated by my procedure: <code> "FV","3.0" "FH","20170404","001","1","loftpaypal@loft.com" "BH","1","USD","loftpaypal@loft.com" "1","RF","03F359879C231283P","USD","13.99","","591111895" "2","RF","0FA889952A037821...
Categories: DBA Blogs

Cannot "select *" table from other Server with schema name prefix

Tom Kyte - Wed, 2017-04-05 02:06
Hi Tom, My department has 2 Oracle database servers at different city, let's say db1 and db2. Data exchange between database is only using Oracle jobs which either refreshing materialized views or delete-insert tables. Few days ago there was ne...
Categories: DBA Blogs

ERR_CONNECTION_REFUSED Error.

Tom Kyte - Wed, 2017-04-05 02:06
Hi Friendz. I am unable to connect to localhost for apex 4.2 version. I am getting error "ERR_CONNECTION_REFUSED" when i ran local host on chrom. (http://localhost:8080/apex/apex_admin) [ This site can?t be reached localhost refused to ...
Categories: DBA Blogs

Literals with two single quotes unexpectedly working

Tom Kyte - Wed, 2017-04-05 02:06
Hi Tom, after many years using Oracle, I fortuitously found a amazing statement, with strange literals accepted in SQL and PL/SQL, although their syntax are incorrect. Test case is in LiveSQL link. I tested it on 10.2.0.4 and 12.1.0.2 versions...
Categories: DBA Blogs

Specify values that are allowed in comma delimited string using regex

Tom Kyte - Wed, 2017-04-05 02:06
Hello, i want to know if it is possible to specify what kind of values are allowed when i insert comma separated string. e.g : the value that are allowed: subcat1,subcat2,subcat3,subcat4. I have the table project with the column subcategories and ...
Categories: DBA Blogs

oracle interval partitioning 11.2.0.4

Tom Kyte - Wed, 2017-04-05 02:06
Hi, I have 4 tables in 4 schemas with monthly interval partitioning: Schemas: C, D, F, G Tables: T1, T2, T3, T4 So, I have these 4 tables in each schema (C, D, F, G). On April 1st, Oracle failed to create partitions (April) for same table (tab...
Categories: DBA Blogs

Google Big Querry and Oracle Smart Scan

Pakistan's First Oracle Blog - Tue, 2017-04-04 23:26
Marveling at the technology is my pastime and lately there are 2 technologies which truly have made me say ' Simply Wow.' One is Google's Big Query and the other one is Oracle's Exadata Smart Scan.

I have been managing data in different databases for a long time to appreciate how critical it is for the client to get the results out of their data as fast as possible. It's all about the returning results at the end after issuing a query or clicking a button.

End user or developer don't really care as how many terabytes of data is there. DBAs and data architects might love to boast about the humongous volumes of data they store and support but there is nothing to write home about, if that data cannot be retrieved as quickly as possible.

When I first migrated a 9TB database to Oracle Exadata few years back and ran a heavy report first time, it returned results in a jiffy, while my jaws dropped. This report used to take at least 70 minutes before without smart scan. I had to bring the developer to double check whether the results were correct or not . Oracle's Exadata smart scan is phenomenal.

I got similar jaw-dropping experience yesterday when I saw Google Cloud Platform's product Big Query in action during an Onboard session Sydney. A SQL with regex was run on multi terabyte of dataset with lots of sorting and it returned the results from the cloud in few seconds. Best thing about Big Query is that the all-familiar SQL is used and nothing fancy is needed. You get your petabytes of data warehouse in Google's cloud and then use your SQL to analyze that dataset. Sweet part is the agility and transparency with which that data is returned.

Simply beautiful.
Categories: DBA Blogs

Bushy Join Trees in Oracle 12.2

Pythian Group - Tue, 2017-04-04 10:05

There are multiple optimizer features introduced in the Oracle Database 12.2 Release. Bushy Join Trees is the one that I’ll be demonstrating in this post.

First, I’ll create four tables with two indexes:

create table t1 as select rownum n1, rownum n2 from dual connect by  level <= 1000000;
create table t2 as select rownum n1, rownum n2 from dual connect by  level <= 100;
create table t3 as select rownum n1, rownum n2 from dual connect by  level <= 1000000;
create table t4 as select rownum n1, rownum n2 from dual connect by  level <= 100;

create index idx_t1 on t1(n1);
create index idx_t3 on t3(n1);

Now, I’ll run the next query:

select * from t1, t2, t3, t4 where t1.n1 = t2.n1 and t3.n1 = t4.n1 and t1.n2=t3.n2;

The most efficient “Bushy” execution plan for this query looks like the next one:

    -----HJ----- 
    |           |
    |           |
---NL---    ---NL---
|      |    |       |
T1     T2   T3      T4

This plan joins T1 and T2 tables by Nested Loop using idx_t1 index, joins T3 and T4 tables by Nested Loop using idx_t3 index and finally joins results of the previous joins by the Hash Join.
But Oracle has never been able to generate such execution plan automatically. You had to rewrite this query with subqueries and bunch of hints in order to force this kind of execution plan.

The following example shows the typical execution plan that Oracle can generate:

Execution Plan
----------------------------------------------------------
Plan hash value: 1007837908

-----------------------------------------------------------------------------------------
| Id  | Operation		       | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	|     1 |    32 |   888   (1)| 00:00:01 |
|*  1 |  HASH JOIN		       |	|     1 |    32 |   888   (1)| 00:00:01 |
|*  2 |   HASH JOIN		       |	|   100 |  2600 |   885   (1)| 00:00:01 |
|   3 |    NESTED LOOPS 	       |	|   100 |  1600 |   303   (0)| 00:00:01 |
|   4 |     NESTED LOOPS	       |	|   100 |  1600 |   303   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL	       | T2	|   100 |   600 |     3   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN	       | IDX_T1 |     1 |	|     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| T1	|     1 |    10 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL	       | T3	|  1000K|  9765K|   579   (1)| 00:00:01 |
|   9 |   TABLE ACCESS FULL	       | T4	|   100 |   600 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."N1"="T4"."N1")
   2 - access("T1"."N2"="T3"."N2")
   6 - access("T1"."N1"="T2"."N1")

We can see that a full T3 table scan and T3 table can be significantly large.

Oracle 12.2 has introduced new BUSHY_JOIN hint and bunch of hidden “_optimizer_bushy” parameters:
_optimizer_bushy_join
_optimizer_bushy_fact_min_size
_optimizer_bushy_fact_dim_ratio
_optimizer_bushy_cost_factor.

_optimizer_bushy_join parameter is ‘off’ by default and you have to set it to ‘on’ or to use a BUSHY_JOIN hint.

Let’s try with a hint:

select   /*+ qb_name(main) BUSHY_JOIN(@"MAIN" ( "T1"@"MAIN"   "T2"@"MAIN" )) */
 * from t1, t2, t3, t4 where t1.n1 = t2.n1 and t3.n1 = t4.n1 and t1.n2=t3.n2;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1929967733

----------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name		   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |		   |   100 |  6800 |   606   (0)| 00:00:01 |
|*  1 |  HASH JOIN		       |		   |   100 |  6800 |   606   (0)| 00:00:01 |
|   2 |   NESTED LOOPS		       |		   |   100 |  1600 |   303   (0)| 00:00:01 |
|   3 |    NESTED LOOPS 	       |		   |   100 |  1600 |   303   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL	       | T4		   |   100 |   600 |	 3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN	       | IDX_T3 	   |	 1 |	   |	 2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID | T3		   |	 1 |	10 |	 3   (0)| 00:00:01 |
|   7 |   VIEW			       | VW_BUSHY_D96D1B60 |   100 |  5200 |   303   (0)| 00:00:01 |
|   8 |    NESTED LOOPS 	       |		   |   100 |  1600 |   303   (0)| 00:00:01 |
|   9 |     NESTED LOOPS	       |		   |   100 |  1600 |   303   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL	       | T2		   |   100 |   600 |	 3   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN	       | IDX_T1 	   |	 1 |	   |	 2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID| T1		   |	 1 |	10 |	 3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_1"="T3"."N2")
   5 - access("T3"."N1"="T4"."N1")
  11 - access("T1"."N1"="T2"."N1")

We can see VW_BUSHY_D96D1B60 internal view at step 7, and this is a definitely a “bushy” plan. The feature still is not enabled by default, but you don’t need to rewrite the query for a proper plan.

Categories: DBA Blogs

Inline Queries Vs. Store Procedure

Tom Kyte - Tue, 2017-04-04 07:46
what are the disadvantages of using Inline Queries instead of using Store Procedure? what is the performance impact on Inline Queries Vs. Store Procedure? thanks
Categories: DBA Blogs

Identity Vs Sequence

Tom Kyte - Tue, 2017-04-04 07:46
Hi , Good day. We can have an alternative to sequence as below. This is quite similar to Identity column in SQL server. CREATE TABLE identity_test_tab ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, description VARCHAR2...
Categories: DBA Blogs

tkprof output questions

Tom Kyte - Mon, 2017-04-03 13:26
Recently we ran a 10046 trace on some scripts running long and generating the tkprof output from that trace file. The top two sqls are shown below and I am looking for some clarifications. 1. Dynamic sampling is shown for this query but I am not ...
Categories: DBA Blogs

Bitmap index and OLTP

Tom Kyte - Mon, 2017-04-03 13:26
Hi Tom, In one of the interview, interviewer asked me about the scenario where bitmap index is useful in OLTP system. and I was totally clueless..... I wasn't able to think any of the scenario. Later I googled every possible combination to ge...
Categories: DBA Blogs

Oracle Database Audit Trail

Tom Kyte - Mon, 2017-04-03 13:26
Hi All, My Company is trying to configure Oracle Audit Trail. I was testing it on test environment with XML, EXTENDED setting. My questions are 1. Too much files are being generated at OS level and how can I control it? or make it write on the sa...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs