Bind variables are often known as one of the key feature for better SQL query performance. Bind variables as per Oracle documentation is a placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.
You can think of SQL query as a kind of “function” in any programming language and bind variables as “values” that you pass to the function.
Select * from EMP where EMP_ID=1;
Select * from EMP where EMP_ID=:a;
First statement uses a literal value (1) to run the query while the second SQL statement uses bind variable (:a) to run the SQL statement. The value of (:a) will be provide to Oracle at run time.
A “cursor” is a memory area in the library cache that is allocated to the SQL statement which users execute. This memory area stores key information about the SQL statement like SQL text, SQL execution plan, statistics etc.
Each SQL statement has one Parent cursor and one or more child cursors.Let us understand what a parent and child cursors are.
Usually we monitor the EXPDP/IMPDP jobs by monitoring the log files generated by expdp/impdp process. Also we monitor alert log too just in case some error pops up. This helps most of the time. If you have a long running expdp/impdp sessions as you are exporting/importing huge GBs then it helps to have a more detailed monitoring of the expdp/impdp jobs. Some of the useful queries which can be used to monitor the Data Pump Jobs are mentioned below.
To start with some of the important tables/views that you should refer to monitor Data Pump Jobs are:
It is required sometime to connect oracle database to SQL Server database using uni-direction or bi-direction methods. Since these are two different database so something as simple as direct DBLINK will not work here. Below are the basic concepts of making connection from SQL SERVER to Oracle and vice-versa.
During one of our 11gR2 database restore activity we found below error message in the RMAN “duplicate database command”
channel aux4: ORA-19870: error while restoring backup piece /oraclone/ORCL/rman_clone/ORCL_F_20170605_c1s60alv_1_1
ORA-19504: failed to create file "+DATA3"
ORA-15045: ASM file name '+DATA3' is not in reference form
ORA-17502: ksfdcre:5 Failed to create file +DATA3
ORA-15081: failed to submit an I/O operation to a disk
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
SVR4 Error: 13: Permission denied
Usually in OLTP environment like EBS Applications, tables are often get fragmented due to multiple DML activities that happens.Fragmented tables cause queries on those tables to slow down. It is very important to de-fragment this table and to reclaim the fragmented space from these objects.
For EBS we have also seen that usually gathered statistics, indexing and proper SQL tuning is plenty to improve and maintain acceptable performance but sometime it is required to reorg the table.
One primary cause of fragmentation is that when you run delete command on the tables it delete the rows but doesn’t frees up the memory and also do not changes the high water mark.