DBA Blogs

Index Monitoring

Tom Kyte - Thu, 2018-11-29 14:46
Hi All, I know index monitoring in 12.1.0.2 is not that great out of the box. I have a query that runs against the DBA_HIST_SQL_PLAN view, as below. Would this be a suitable way to get index usage? (We have the Diag+Tuning packs available to us) ...
Categories: DBA Blogs

Parallel_degree_policy AUTO caused serial plan in qat

Bobby Durrett's DBA Blog - Thu, 2018-11-29 13:41

Recently during testing a merge statement was running forever in our qat environment after a simple change. We had simply added a few columns to an existing table. After looking around we found that our qat environment had the parameter parallel_degree_policy set to AUTO, but our other environments had it set to MANUAL. I thought I would show how we figured this out.

I had the SQL_ID for the problem merge statement and used my sqlstat.sql script to see if the same merge statement ran in production. I did not find any production executions. I was told that this merge statement was already in our production code and ran all the time without an issue. So, then I used my findsql.sql script to find the SQL_ID of the production version of the merge statement. 

To use findsql.sql you have to edit it to replace MYUSER with a real user on your database. I use my own personal user. Then you have to update the LIKE statement in the where clause to identify the query that you want. I replaced this line in the findsql.sql text on GitHub:

SQL_TEXT like '%a.FISC_WK_OF_Yr < to_number(to_char(sysdate+1, ''iW''))%' and

with this line which is part of the text of the problem merge statement:

upper(SQL_TEXT) like upper('%MERGE INTO DDMADM.FWB_MDL MDL%') and

I easily found the equivalent merge statement in production. The QAT merge statement had SQL_ID 00cmatqz5wymd and the prod one had 7pgqd82bgdah2. My coworker, who had brought me in on this issue, said that on QAT the query was not running in parallel. So, I used my getplans.sql script to look at the two plans. On prod the plan used parallelism:

-----------------------------------------
| Id  | Operation                        
-----------------------------------------
|   0 | MERGE STATEMENT                  
|   1 |  MERGE                           
|   2 |   TEMP TABLE TRANSFORMATION      
|   3 |    PX COORDINATOR                
|   4 |     PX SEND QC (RANDOM)          
|   5 |      LOAD AS SELECT              
|   6 |       UNION-ALL                  
|   7 |        WINDOW SORT               
|   8 |         PX RECEIVE               
|   9 |          PX SEND HASH            
|  10 |           VIEW                   
|  11 |            UNPIVOT               
|  12 |             PX BLOCK ITERATOR    
|  13 |              TABLE ACCESS FULL   

On QAT it did not:

-----------------------------------------------------
| Id  | Operation                                    
-----------------------------------------------------
|   0 | MERGE STATEMENT                              
|   1 |  MERGE                                       
|   2 |   VIEW                                       
|   3 |    NESTED LOOPS OUTER                        
|   4 |     VIEW                                     
|   5 |      TEMP TABLE TRANSFORMATION               
|   6 |       LOAD AS SELECT                         
|   7 |        UNION-ALL                             
|   8 |         WINDOW SORT                          
|   9 |          VIEW                                
|  10 |           UNPIVOT                            
|  11 |            PARTITION RANGE SINGLE            
|  12 |             TABLE ACCESS BY LOCAL INDEX ROWID
|  13 |              INDEX RANGE SCAN                

I found this extra line at the end of the QAT plan:

- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

This triggered a memory of a coworker of mine who was trying out some degree of parallelism parameters a couple of years ago. After searching the internet, I quickly found that the automatic DOP message was related to the parameter parallel_degree_policy. We found that it was set to manual on prod, so we set it to manual on QAT and the merge statement went back to the expected performance.

I guess the point of this post is to keep a close watch on the messages that show up at the end of your execution plan. They could lead you to a parameter setting that is the source of your problem.

Lastly, this is my first post using the new WordPress Gutenberg plugin. What do you think? It was pretty easy to put in the output from my scripts in little unformatted boxes by themselves. Much easier than with the previous WordPress editor.

Bobby

P.S. I have found one negative to Gutenberg. I can’t find a way to use the Jetpack Proofreading function to check my grammar. So, if you see that I have used passive voice in a bunch of my sentences you will know why. Hopefully they will add this into a future update. Of course, I could just use my brain to check the grammar but where is the fun in that?

P.P.S. I started working on another blog post and found a simple solution to my grammar checking needs using Gutenberg. I just copy and paste the blog post text into Microsoft Word and use its grammar checker. Pretty neat.

Categories: DBA Blogs

explain plan analysis

Tom Kyte - Wed, 2018-11-28 20:26
Hi Team , can you help me to analyse the below execution plan : <code> -------------------------------------------------------------------------------------------------------- | Id | Operation | Name ...
Categories: DBA Blogs

Why this sql causes an internal error in Oracle?

Tom Kyte - Wed, 2018-11-28 20:26
Hi! I'm running this little SQL on 12c, also tried on 11g, and got an internal error on both. I couldn't understand why. Probably a bug, as noted by people in stackoverflow, where I originally asked about: https://stackoverflow.com/questions/53527...
Categories: DBA Blogs

Pluggable database save state -12C

Learn oracle 12c database management - Wed, 2018-11-28 15:40
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;

Pluggable database altered.


SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ WRITE

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

***********************************************
DISCARD PDB SAVED STATE
***********************************************

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN


SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

Categories: DBA Blogs

how to create nested cursor in plsql

Learn oracle 12c database management - Wed, 2018-11-28 15:39




select * from empinfo; 

"ID"    "FNAME"    "LNAME"    "DEPTNO"
1    "arv"    "red"    100
2    "sam"    "kum"    200
3    "tom"    "com"    100
4    "Hef"    "Kom"    200
5    "fam"    "ily"    300



select * from deptinfo;

"ID"    "DEPTNO"    "DEPTNAME"    "MGR"
1    100    "HR"    "Bill"
2    200    "IT"    "Greg"
3    300    "SALES"    "Phil"


-- sqlcode starts here

declare

CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;

CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;

OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('REC1.id:  '||REC1.ID||'  belongs to dept ID : '||REC2.DEPTNO||'  DEPT :'||REC2.DEPTNAME);

END LOOP;
CLOSE CUR2;

end loop;
close CUR1;
end;

Categories: DBA Blogs

Partitioning -- 10 : Virtual Column Based Partitioning

Hemant K Chitale - Wed, 2018-11-28 03:44
Oracle 11g supports specifying a Virtual Column as the Partition Key.

A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions.  The actual value is not stored in the block holding the row but is computed when the row is retrieved.

For example :

create table my_sales_table
(invoice_id number primary key,
invoice_date date,
sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
customer_id number,
sale_value number
)
/

insert into my_sales_table
(invoice_id, invoice_date, customer_id, sale_value)
values
(1,sysdate,100,10200)
/

select invoice_id, invoice_date, sale_year
from my_sales_table
/

INVOICE_ID INVOICE_DATE SALE_YEAR
1 28-NOV-18 2018


The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.

drop table my_sales_table;

create table my_sales_table
(invoice_id number primary key,
invoice_date date,
sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
customer_id number,
sale_value number
)
partition by list(sale_year)
(partition p_2018 values (2018),
partition p_2019 values (2019),
partition p_2020 values (2020)
)
/

insert into my_sales_table
(invoice_id, invoice_date, customer_id, sale_value)
values
(1,sysdate,100,10200)
/

select invoice_date, sale_year from my_sales_table partition (p_2018)
/

INVOICE_DATE SALE_YEAR
28-NOV-18 2018


Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.


Categories: DBA Blogs

How to limit the resources at pluggable level in database 12cR2

Tom Kyte - Wed, 2018-11-28 02:06
Some bad performance pdbs may result in disaster for the whole db machine, in order to avoid this situation we need to limit the pdb resource usage! There's tens of pdbs in our 12cR2, each one requires DIFFERENT resources, we have to limit them with...
Categories: DBA Blogs

oracle 11g place limits on memory to session/user

Tom Kyte - Wed, 2018-11-28 02:06
Hello ! Here is my question : Is it possible to allow a minmum amount of memory to a specific session or a user on Oracle 11g? If it is, could anyone, please, explain it to me ? :) My fundamental need is as follow : I have two applications th...
Categories: DBA Blogs

expressions on pivot columns

Tom Kyte - Wed, 2018-11-28 02:06
On LiveSQL there is a tutorial, 'Converting Rows to Columns and Back Again: Databases for Developers' https://livesql.oracle.com/apex/livesql/file/tutorial_GNZ3LQPJ0K6RTD1NEEPNRQT0R.html It guides me through on how to pivot rows into columns an...
Categories: DBA Blogs

ORA-12154: TNS:could not resolve the connect identifier specified for ODBC

Tom Kyte - Wed, 2018-11-28 02:06
Greetings, I received the following error while trying to connect to Oracle instantclient 10.1 in MS Access 2016: ORA-12154: TNS:could not resolve the connect identifier specified [#12154) [Microsoft][ODBC Driver Manager] SQLSetConnectAttr fail...
Categories: DBA Blogs

ORA-12560 ERROR - do I have all the services I need ?

Tom Kyte - Wed, 2018-11-28 02:06
Hello, I am using a Macbook and I downloaded the Parallels virtual machine to get Windows 10 in order to download SQL Plus, I have done all of the minimum requirements and installed SQL Plus, but when it comes to entering my username and password...
Categories: DBA Blogs

Database Link across versions

Tom Kyte - Wed, 2018-11-28 02:06
Can a database link created on an Oracle 12 database access tables hosted on an Oracle 11 database? We have an existing application Oracle Argus that we are upgrading to Argus 8.1.2.1 which will run on Oracle 12 Exadata server. We have an existing...
Categories: DBA Blogs

Insert datetime values from .NET apps

Tom Kyte - Wed, 2018-11-28 02:06
Hello, Team. A .NET app is being developed by our team. We are using Oracle database 18.3.0. In order to insert date and time in a date column developers use the following code: <b>TO_DATE(TO_CHAR(:DATE_COLUMN,'DD/MM/RRRR' HH:MI:SS AM), 'DD/...
Categories: DBA Blogs

Unix Strings command and data not encrypted

Tom Kyte - Wed, 2018-11-28 02:06
Hello TOM, I have a problem with the Unix command named STRINGS. If I do not use encryption on my datas, I can read my string datas via Unix, even if these datas were truncated under Oracle. And dtranger, I have their history too... Fir...
Categories: DBA Blogs

Table storage parameters

Tom Kyte - Wed, 2018-11-28 02:06
I've always created tables using the defaults for storage parameters. <code> create table blah ( a number; b varchar2(100) ); </code> however I recently worked with an organization where the following storage parameters were always spe...
Categories: DBA Blogs

Default Value From A Sequence: Sequence Updates On Provided Value Entries With INSERT Through LOOP But Not on Individual INSERTs

Tom Kyte - Wed, 2018-11-28 02:06
I have two samples of code below that use the same table and sequence definitions. The first one inserts using individual insert statements, with the first three inserts giving an override value to the sequenced column and the final insert relyi...
Categories: DBA Blogs

Compare all table content from one schema to another

Tom Kyte - Tue, 2018-11-27 07:46
Hi, I need to compare full schema table contents with another schema. Both will have same tables, with exact the same DDL, just some records will be different in few columns. Is there any way to execute a script or any way to do it in SQL Deve...
Categories: DBA Blogs

parsing recusirvely "#" from strings without PL/SQL procedure

Tom Kyte - Tue, 2018-11-27 07:46
Dear Tom, First of all thanks for the site, the tips... always useful... I faced recently an sql issue and i designed a beginning of answears but i think it should be optimized. I have a table containing posts from a social network platforms. Thos...
Categories: DBA Blogs

Sending mail using utl_mail

Tom Kyte - Tue, 2018-11-27 07:46
Dear Team, I tried to install UTL_MAIL using the below command sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb it runs successfully and created the package and synonym but when i tri...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs