DBA Blogs

how to debug the SQL logic execution ?

Tom Kyte - Thu, 2016-07-07 09:06
AskTom, I have a question about the 'SQL logic execution': Can Oracle Database log the logic of the SQL execution ? (which condition of the WHERE clause failed ?) example: select 1 from Dual WHERE 1 = 1 AND 1 = 1 + 1 --line 5 ; C...
Categories: DBA Blogs

Fuzzy Name Match Stored Procedure Optimization

Tom Kyte - Thu, 2016-07-07 09:06
Hello, Have written PL/SQL stored proc 'FuzzyNameMatch' that interrogates first, middle, last names from a single column in two distinct tables, ie source and compare columns. The algo parses shorter strings through longer and increments counter v...
Categories: DBA Blogs

Need to generate numbers between a given range for each record.

Tom Kyte - Thu, 2016-07-07 09:06
Hi Team, I have below sample table as input : ID MIN MAX 1 5 10 2 3 5 And I want output as follows: ID value 1 5 1 6 1 7 1 8 1 9 2 3 2 4 Id column is a p...
Categories: DBA Blogs

Performance & features of OCCI vs OCI

Tom Kyte - Thu, 2016-07-07 09:06
I'm going to develop an application that needs max performance. I will need: - batch inserts of BLOB - batch updates of BLOB - batch select of BLOB I consider to use OCCI but I'm not sure if it supports all optimizations that are done in OCI. ...
Categories: DBA Blogs

Materialized view logs and unique indexes

Tom Kyte - Thu, 2016-07-07 09:06
I have a table with moderate DML activity, a materialized view log on that table, and one materialized view on a remote database. After some activity in the source app, I can see multiple rows in the MV log with the same m_row$$, with a DMLTYPE$ v...
Categories: DBA Blogs

Best Data Type To use

Tom Kyte - Thu, 2016-07-07 09:06
I've a Table with columns EMPCODE,DAY1,DAY2...DAY31 I've to loop from Day1 to Day31 so what is the best way of doing this,If I should use collections then how should I assign Day1--31 to a Collection and what Collection Should I use. Can anyone p...
Categories: DBA Blogs

Create a SQL Profile to let the Optimizer ignore hints in #Oracle

The Oracle Instructor - Thu, 2016-07-07 03:36

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:04.92
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
--------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1767991108

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |   139K(100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES     |    17M|   131M|   139K  (1)| 00:00:06 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | SALES_BIX |       |       |            |          |
--------------------------------------------------------------------------------------------------

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

   4 - access("CHANNEL_ID"=3)

The index hint directs the optimizer here to use a bad plan that wouldn’t be used otherwise:

SQL> select max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.06
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  ahw4npmjpnu1k, child number 0
--------------------------------------
select max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

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

   2 - filter("CHANNEL_ID"=3)

Now the remedy:

begin
 dbms_sqltune.import_sql_profile(
 name => 'MYPROFILE1',
 category => 'DEFAULT',
 sql_text => 'select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3',
 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')
                                 );
end;
/

PL/SQL procedure successfully completed.

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.05

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
-------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

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

   2 - filter("CHANNEL_ID"=3)

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------
   - SQL profile MYPROFILE1 used for this statement

This works for that SQL statement only without having to modify the application. The SQL profile can be removed like this:

SQL> exec dbms_sqltune.drop_sql_profile('MYPROFILE1')
PL/SQL procedure successfully completed.

All the above is not new, but still I think it might be worthwhile to mention it here for your reference, should you encounter some nasty hints once:-)


Tagged: optimizer, Performance Tuning
Categories: DBA Blogs

Modified IO CPU+IO Elapsed Graph (sigscpuio)

Bobby Durrett's DBA Blog - Wed, 2016-07-06 18:16

Still tweaking my Python based Oracle database performance tuning graphs.

I kind of like this new version of my “sigscpuio” graph:

blogiopluscp2u

The earlier version plotted IO, CPU, and Elapsed time summed over a group of force matching signatures. It showed the components of the time spent by the SQL statements represented by those signatures. But the IO and CPU lines overlapped and you really could not tell how the elapsed time related to IO and CPU.  I thought of changing to a stacked graph where the graph layered all three on top of each other but that would not work. Elapsed time is a separate measure of the total wall clock time and could be more or less than the total IO and CPU time. So, I got the idea of tweaking the chart to show IO time on the bottom, CPU+IO time in the middle, and let the line for elapsed time go wherever it falls. It could be above the CPU+IO line if there was time spent that was neither CPU or IO. It could fall below the line if CPU+IO added up to more than the elapsed time.

So, this version of sigscpuio kind of stacks CPU and IO and just plots elapsed time wherever it falls.  Might come in handy.

Bobby

Categories: DBA Blogs

Oracle doubts

Tom Kyte - Wed, 2016-07-06 14:46
c d f ---- ---- ---- 1 a 100 1 b 200 Output: 1 a 100 b 200
Categories: DBA Blogs

cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Tom Kyte - Wed, 2016-07-06 14:46
I am using PL/SQL in front end and Oracle 11gR2 in back end to developing a db app. In the middle of time, I am going to check hard parsed execution plan of one sql statment. What make me surprised is that the PL/SQL Developer seems has no capability...
Categories: DBA Blogs

Do bind variables in APEX trigger row by row processing?

Tom Kyte - Wed, 2016-07-06 14:46
To start this off, I kind of have a solution for my problem, I'm just trying to understand the mechanics behind it. Ok, here's the set-up: - I have a very simple APEX page: a report on a view + 2 date pickers to filter the data; - The query is a...
Categories: DBA Blogs

why does "select user from dba_users" work?

Tom Kyte - Wed, 2016-07-06 14:46
Hi all, I accidently typed today "select <b>user</b> from dba_users" in SQLPlus instead of "select <b>username</b> from dba_users" and it worked. Not like the correct SQL would have, but i got 21 rows (which is the correct number of users in the dat...
Categories: DBA Blogs

Slow query because the cardinality estimate is wrong for joins on foreign keys

Tom Kyte - Wed, 2016-07-06 14:46
While investigating a very slow query in our OLTP db, I noticed that Oracle severly under estimates the cardinality for joins that are on foreign key. The following script replicates the issue. create table A (part number not null, rec number not...
Categories: DBA Blogs

Tune order by clause in query.

Tom Kyte - Wed, 2016-07-06 14:46
Hi Tom, In the below query order by is taking a lot of time. so i thought of creating a composite index on columns that are present in the order by clause and force that index using hint. <b>But my problem is, here i have to fetch data of lo...
Categories: DBA Blogs

I need to delete 18000 rows from a table but where clause condition varies. How to complete this deletion in simple way

Tom Kyte - Wed, 2016-07-06 14:46
I need to delete 18000 rows from a table but where clause condtion varies for each set of records. A particular where clause condition can delete 3 records. Another particular where clause condition can delete 1 record. I combined both delete stat...
Categories: DBA Blogs

where are the executed statments stored?

Tom Kyte - Wed, 2016-07-06 14:46
suppose i execute a plsql block , all the statments are not executed. Only high load statments are executed. How to see which of the statements are executed and where are they stored, Like which view/table?
Categories: DBA Blogs

Cloud Raining - Where is Oracle with the Cloud - Is the DB Giant sleeping ?

Tom Kyte - Wed, 2016-07-06 14:46
Hello AskTom Team, I have been working with Oracle Database for over a decade and half. With the recent shift of Companies wanting to put their systems in Cloud rather then on prem What is the future of Oracle. I just attended a...
Categories: DBA Blogs

Inner join vs Where

Tom Kyte - Wed, 2016-07-06 14:46
What is the best practice use "Inner Join" o "Where" Example Example A select DISTINCT(ET.DESCRIPTION) FROM EVENTTYPE ET INNER JOIN EVENTDCO E ON E.EVENTTYPEID = ET.EVENTTYPEID INNER JOIN CONTEXTOPERATION CTX ON E.OPERATIONPK ...
Categories: DBA Blogs

Always fill in the DNS Hostname Prefix in Oracle Compute Cloud Instance

Always fill in the DNS Hostname Prefix when creating a new instance in Oracle Compute Cloud. What seems an unimportant optional field can make your life harder than it should be,...

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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs