Search results “Oracle hints result cache”
Oracle Performance - Result Cache Exercises
Oracle Performance - Result Cache Exercises
Views: 650 The Silent DBA
Efficient Function Calls From SQL (Part 3) : PL/SQL Function Result Cache
The third part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at the Cross-Session PL/SQL Function Result Cache introduced in Oracle 11g. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#oracle-11g-caching https://oracle-base.com/articles/11g/cross-session-plsql-function-result-cache-11gr1 Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Auckland Oracle DBAs Meetup : Website: http://www.meetup.com/Auckland-Oracle-DBAs-Meetup/ Sponsors: - DBvisit: http://www.dbvisit.com/ - Datacom: http://www.datacom.co.nz/ - Oracle New Zealand: http://www.oracle.com/ - Pythian: http://www.pythian.com/ - Enterprise IT: http://www.enterpriseit.co.nz/ Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2766 ORACLE-BASE.com
Oracle Function Result Cache | Steven Feuerstein
Returning for his seventh 2 Minute Tech Tip, Oracle architect and "Oracle PL/SQL Programming 6e" author Steven Feuerstein delivers the details on the Function Result Cache, a feature introduced in Oracle Database 11g that can add a performance boost to your applications.
Oracle 11g SQL Results Cache Training by SkillBuilders Dave Anderson
Learn how, by using the Oracle Database 11g Results Cache, you can get phenomenal performance from repeated executions of queries against large, static data. From Dave Anderson of SkillBuilders.com/Oracle.
Views: 3283 SkillBuilders
Rev up your Oracle Functions using Result Cache
Dan shows you how to dramatically increase function response time using a newer Oracle memory structure called Result Cache. Still on Oracle10? Dan has a solution for you as well.
Views: 113 Dan Hotka
Efficient Function Calls From SQL (Part 2) : The DETERMINISTIC Hint
The second part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at the DETERMINISTIC hint. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#deterministic-hint Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Dana Singleterry : Blog: https://blogs.oracle.com/dana/ Twitter: https://twitter.com/dsingleterry Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2175 ORACLE-BASE.com
Result Cache
Parses and Data Dictionary watch more videos at https://www.tutorialspoint.com/videotutorials/index.htm Lecture By: Mr. Parth Panjab, Tutorials Point India Private Limited
Oracle 11g Result Cache Tutorial by SkillBuilders.com
Dave Anderson of SkillBuilders.com/11g demonstrates how to improve query performance with the Oracle 11g Result Cache.
Views: 2464 SkillBuilders
Efficient Function Calls From SQL (Part 4) : Scalar Subquery Caching vs PL/SQL Function Result Cache
The fourth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we compare Scalar Subquery Caching with the the Cross-Session PL/SQL Function Result Cache to see which we should use for best performance. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#scalar-subquery-caching-revisited Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Alex Gorbachev : Blog: http://www.oracloid.com/ Twitter: https://twitter.com/alexgorbachev Website: http://www.pythian.com/about/alex-gorbachev/ Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 1485 ORACLE-BASE.com
Full DB In-Memory Caching
This is the first of two demonstrations about In-Memory caching new features, introduced in Oracle Database This one illustrates the Full Database In-memory Caching feature enabling an entire database to be cached in memory when the database size is smaller than the buffer cache size. Caching and running a database from memory leads to huge performance benefits. Copyright © 2014 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.
Efficient Function Calls From SQL (Part 1) : Scalar Subquery Caching
The first part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at Scalar Subquery Caching. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#scalar-subquery-caching Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Bruno Borges : Blog: https://blogs.oracle.com/brunoborges/ Website: http://brunoborges.com/ Twitter: https://twitter.com/brunoborges Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2244 ORACLE-BASE.com
Oracle 11g New Features : SQL Plan Management  (Baselining)
Oracle 11g SQL Plan Management, a.k.a. Baselining, will solve the major issues of change control. We can give you an absolute guarantee - if you configure this as part of your upgrade - that SQL statement execution (performance) will never regress. You may also like our new and free Oracle 12c video tutorials, https://www.skillbuilders.com/free-oracle-database-tutorials . This tutorial is particularly relevant: it provides a review of baselining then reveals 12c performance features https://www.skillbuilders.com/free-oracle-database-tutorials/oracle-12c-sql-performance-features-agenda-session/
Views: 10119 SkillBuilders
Efficient Function Calls From SQL (Part 6) : Function-Based Indexes
The sixth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we look at using function-based indexes to improve the performance of queries that use function calls in the WHERE clause. For more information see: https://oracle-base.com/articles/8i/function-based-indexes https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#functions-in-the-where-clause Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Ronald Bradford : Blog: http://ronaldbradford.com/blog/ Twitter: https://twitter.com/RonaldBradford Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 1375 ORACLE-BASE.com
Top PL/SQL Tips In Just One Hour
Here's a video I recorded of a webcast to a team of developers who had one hour to spare to learn about some PL/SQL best practices. I offer tips on using compile time warnings, avoiding hard-coding/repetition, using nested subprograms and in the performance arena, lightning fast coverage of FORALL, function result cache, NOCOPY and PRAGMA UDF. Fun stuff and here's the guide: 03:00 Compile-time warnings 16:25 Don’t repeat anything 21:15 Tips on writing SQL in PL/SQL 24:50 Avoid hard-coding of formulas 28:35 Keep executable sections tiny 35:15 Avoid row-by-row processing 42:10 Function result cache 54:28 NOCOPY parameter hint 56:02 Optimizing user-defined function execution in SQL Files referenced in presentation may be found at http://www.oracle.com/webfolder/technetwork/tutorials/plsql/sfdemo.zip. But for the latest set of PL/SQL scripts, go to LiveSQL.oracle.com and search by keywords (forall, nocopy, etc.). More on PL/SQL at my blog https://stevenfeuersteinonplsql.blogspot.com PL/SQL quizzes, workouts and classes at https://devgym.oracle.com ======================================== Practically Perfect PL/SQL with Steven Feuerstein Copyright © 2018 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Efficient Function Calls From SQL (Part 5) : Pipelined Table Functions
The fifth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we compare the performance of conventions table functions with pipelined table functions. For more information see: https://oracle-base.com/articles/misc/pipelined-table-functions https://oracle-base.com/articles/misc/efficient-function-calls-from-sql Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Mike Dietrich : Blog: https://blogs.oracle.com/UPGRADE Twitter: https://twitter.com/MikeDietrichDE Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 10736 ORACLE-BASE.com
Webinar: Ten (Other) Top Tips for Improving PL/SQL Performance
Bulk processing (FORALL and BULK COLLECT), along with the function result cache, are the "big ticket" items when it comes to performance optimization with PL/SQL. But there's still more we can do to tweak our code for even better response times for our users. This third webast in the series starts with the automatic compiler optimization, showcases the extraordinary speediness of PGA data manipulation (a.k.a., package variables), and demonstrates the effect of the simple NOCOPY hint. We finish up with an introduction to pipelined table functions and some thoughts on optimizing your algorithms.
Views: 10063 ODTUG
Oracle SQL Tuning - How to Read Execution Plans (Explain Plan)
In this free tutorial you will learn how to generate and read (interpret) an execution plan in Oracle Databases. See more FREE Oracle Tuning tutorials at http://skillbuilders.com/free-oracle-tutorials. Understanding what the Oracle Database does with your SQL is essential to tuning - and the execution plan is the key. Oracle Certified Master DBA John Watson will provide a brief introduction (4 minutes) - which includes John's tuning methodology, then demonstrate EXPLAIN PLAN, SQL*Plus AUTOTRACE and DBMS_XPLAN.DISPLAY_CURSOR. In the tutorial, John will teach you: - How to read an execution plan - Find the 1st step in the plan - Decipher the order of the steps in the plan - That EXPLAIN PLAN can be very misleading Prerequisites: To get the most from this tutorial, you should: 1 Know how to code SQL 2 Be familiar with SQL*Plus 3 Know - in very general terms - what an execution plan is. 4 Have a basic understanding of the Library Cache (this is where Oracle Database stores parsed SQL statements) 5 Have a basic understanding of the Cost Based Optimizer (this is the part of the database that parses your SQL, creates an execution plan. Hopefully the correct - most efficient - plan).
Views: 61741 SkillBuilders
05 Query Hints
Views: 315 Moto Nobre
Sql server query plan cache
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/sql-server-query-plan-cache.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/sql-server-query-plan-cache_12.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss 1. What happens when a query is issued to SQL Server 2. How to check what is in SQL Server plan cache 3. Things to consider to promote query plan reusability What happens when a query is issued to SQL Server In SQl Server, every query requires a query plan before it is executed. When you run a query the first time, the query gets compiled and a query plan is generated. This query plan is then saved in sql server query plan cache. Next time when we run the same query, the cached query plan is reused. This means sql server does not have to create the plan again for that same query. So reusing a query plan can increase the performance. How long the query plan stays in the plan cache depends on how often the plan is reused besides other factors. The more often the plan is reused the longer it stays in the plan cache. How to check what is in SQL Server plan cache SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY cp.usecounts DESC As you can see we have sorted the result set by usecounts column in descending order, so we can see the most frequently reused query plans on the top. usecounts - Number of times the plan is reused objtype - Specifies the type of object text - Text of the SQL query query_plan - Query execution plan in XML format To remove all elements from the plan cache use the following command DBCC FREEPROCCACHE In older versions of SQL Server up to SQL Server 6.5 only stored procedure plans are cached. The query plans for Adhoc sql statements or dynamic sql statements are not cached, so they get compiled every time. With SQL Server 7, and later versions the query plans for Adhoc sql statements and dynamic sql statements are also cached. Things to consider to promote query plan reusability For example, when we execute the following query the first time. The query is compiled, a plan is created and put in the cache. Select * From Employees Where FirstName = 'Mark' When we execute the same query again, it looks up the plan cache, and if a plan is available, it reuses the existing plan instead of creating the plan again which can improve the performance of the query. However, one important thing to keep in mind is that, the cache lookup is by a hash value computed from the query text. If the query text changes even slightly, sql server will not be able to reuse the existing plan. For example, even if you include an extra space somewhere in the query or you change the case, the query text hash will not match, and sql server will not be able find the plan in cache and ends up compiling the query again and creating a new plan. Another example : If you want the same query to find an employee whose FirstName is Steve instead of Mark. You would issue the following query Select * From Employees Where FirstName = 'Steve' Even in this case, since the query text has changed the hash will not match, and sql server will not be able find the plan in cache and ends up compiling the query again and creating a new plan. This is why, it is very important to use parameterised queries for sql server to be able to reuse cached query plans. With parameterised queries, sql server will not treat parameter values as part of the query text. So when you change the parameters values, sql server can still reuse the cached query plan. The following query uses parameters. So even if you change parameter values, the same query plan is reused. Declare @FirstName nvarchar(50) Set @FirstName = 'Steve' Execute sp_executesql N'Select * from Employees where [email protected]', N'@FN nvarchar(50)', @FirstName One important thing to keep in mind is that, when you have dynamic sql in a stored procedure, the query plan for the stored procedure does not include the dynamic SQL. The block of dynamic SQL has a query plan of its own. Summary: Never ever concatenate user input values with strings to build dynamic sql statements. Always use parameterised queries which not only promotes cached query plans reuse but also prevent sql injection attacks.
Views: 20935 kudvenkat
Pl/SQL in Oracle 11g Tutorial #16: How to write and calling first function
Pl/SQL in Oracle 11g Tutorial #16: How to write and calling first function. Write a first function and call . Thanks for Watching my Channel “Learn TechToTech”. Please subscribe my channel for getting first updates after uploading video.Social Media pages of Channel are: 1. My Website : www.learnfromrakesh.com 2. My Twitter: https://twitter.com/LearnTechtotech 3. Facebook Page: https://www.facebook.com/LearnTechToTech 4. Blogger : https://learntechtotech.blogspot.in/ 5. Google+ : https://learntechtotech.blogspot.in/ 6. Pinterest : https://in.pinterest.com/LearnTechToTech/ 7. LinkedIn: https://www.linkedin.com/in/Learntechtotech/ Playlist of Different Technology: 1. Operating System : https://goo.gl/q6SfrW 2. Python Programming Language : https://goo.gl/L8b5dc 3. C Language : https://goo.gl/SwvDu9 4. C language for Placement: https://goo.gl/AaQBa4 5. Java: https://goo.gl/M8F2uy 6. MySql : https://goo.gl/vdJbHQ 7. Android Mobile Application Development: https://goo.gl/M6woaT 8. Kotlin Programming Language : https://goo.gl/GXE5cd 9. Go Programming: https://goo.gl/Ua3xYW 10. Internet of Things(IoT): https://goo.gl/f2afkY 11. Oracle 11g : https://goo.gl/zds8r2 12. C++ : https://goo.gl/C1psMT
Views: 38 Learn TechToTech
Advanced SQL SubQueries in Oracle Database
Learn to code advanced correlated SQL subqueries in the Oracle Database. From SkillBuilders.com/Oracle.
Views: 9224 SkillBuilders
04 05 NOCOPY hint
Views: 1059 Chanon Hemtanon
Silent SQL Server Demo: The Plan Cache
SQL Server caches execution plans by query, but even the tiniest differences between queries can result in different plans being cached. Brent demos with sp_BlitzCache.
Views: 3541 Brent Ozar Unlimited
Proyecto final de administración de bases de datos relacionales II
Views: 426 César Retana
Explaining the Explain Plan: Interpreting Execution Plans for SQL Statements
Presenter: Maria Colgan, Oracle Corporation The execution plan for a SQL statement can often seem complicated and hard to understand. Determining if the execution plan you are looking at is the best plan you could get or attempting to improve a poorly performing execution plan can be a daunting task, even for the most experienced DBA or developer. This session examines the different aspects of an execution plan, from selectivity to parallel execution, and explains what information you should be gleaming from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision it did as well as a set of corrective measures that can be used to improve each aspect of the plan.
Views: 2975 ODTUG
Oracle Exadata Smart Scan - Limitations and Best Practices
Smart Scan is a wonderful capability, but you don't always get it. It's impossible for many execution plans, and this is a major restriction. If you think about what a Smart Scan actually does, it delivers individual columns, individual rows back to the instance. Now, a buffer cache can accept only blocks. Therefore, Smart Scan cannot possibly put those columns of rows into the buffer cache. It's simply not formatted appropriately. So, a Smart Scan has to return values directly into the session's PGA or, to put it another way, the only access method that can use Smart Scan is direct read. Well, what access methods can use direct read? There are only two, which are table full scan and index fast full scan. Any other access method, typically index range scan, table access by row ID, cannot use a Smart Scan. The second major issue, there are strict limitations of the type of objects that can be accessed through Smart Scan. It really is only heap tables. You can't use indexes. You can't use clusters. You can't use IOTs. Heap tables only. Perhaps hardest to track down and giving sometimes very erratic results is that Smart Scan can be interrupted by various conditions. You've met all the requirements for Smart Scan, directory and so on, got the right execution plan. The Smart Scan starts and then hits something that causes a problem. Issues that we know cause problems are, for instance, read consistency, also delayed block cleanout, change rows. Any of those issues and a few others mean that the storage tier will have to interrupt its Smart Scan, deliver complete blocks into that buffer cache, let your session then do what is necessary to the block, and only then can the Smart Scan proceed. Now, in order to maximize the use of Smart Scan, there may be quite a lot of work. Very often, you'll have to adjust your index structures. Making them invisible is a nice technique there. There are many, many, many parameters that can influence the likelihood of achieving a Smart Scan, and almost inevitably you're going to be rewriting a lot of hint SQL and putting hints in it to get the correct execution plans that can enable a Smart Scan to occur. This is all because of one fundamental problem; the optimizer is not in any way aware of the Exadata. The optimizer develops an execution plan in exactly the way it would without the Exadata storage. The use of Smart Scan, the awareness of Exadata comes at the next level down. The optimizer develops the plan through a normal pass and then passes it through to the SQL execution engine, and it's the SQL execution engine that determines, on a case-by-case basis, whether to use the Smart Scan. This means that you might develop a plan and execute the statement 50 times. Forty-nine times, you get a Smart Scan. The 50th time, for whatever reason, the SQL execution engine decides not to. This can result in somewhat erratic performance.
Views: 1601 SkillBuilders
VTS: SQL Tuning Without Trying - Arup Nanda
This VTS session features learn about the SQL Tuning Advisor feature in Oracle Database, and how to use it effectively and interpret the results for superior performance in the context of the real world presented by a real-life DBA. Presented by Oracle Ace Director Arup Nanda.
Views: 7964 Database Community
Oracle SQL Tuning - Equal SQL, Part 1 of 5 by John Watson, SkillBuilders.com
Watch Oracle Master John Watson demonstrate why not all equivalent SQL statements are created equal. John demonstrates how SQL statements that produce equivalent result sets can have radically different response times, why this happens and common solutions. All five new video tutorials are available at SkillBuilders.com/Oracle-Tutorials 1. Introduction including the First Demonstration 2. SubQuery Factoring 3. Star Transformations 4. Aggregations 5. Summary Some experience analyzing Oracle execution plans is required to get the most from this lesson.
Views: 15123 SkillBuilders
Digging Deep Into Oracle PL/SQL Compiler Warnings
Gain an understanding of the Oracle PL/SQL compiler warnings and how to enable them with Themis instructor John Mullins.
Views: 345 Themis Education
Oracle 11g CBO New Features
Learn what's different about cost-based optimization in 11g Release 2. In this training Dave Anderson will guide you through some critical performance and SQL tuning enhancements and other surprises in the Oracle 11g cost-based optimizer. Through live demonstrations and lecture, you will learn about features such as adaptive cursor sharing, feedback-based optimization, new join techniques, DBMS_STATS enhancements, direct (PGA) reads for "large" full table scans, new hints and more. Presented at NYOUG by SkillBuilders' Founder and Oracle DBA, Dave Anderson.
Views: 6876 SkillBuilders
Big RAM: How Java Developers Can Fully Exploit Massive Amounts of RAM
This session looks at the implications of the ever-increasing availability of large amounts of RAM (hundreds of gigabytes, or multiple terabytes) for Java developers and discusses techniques they can use to fully exploit these quantities of RAM. It shows evidence suggesting that some garbage-collected environments, such as JVMs, have limitations when fully utilizing such quantities of RAM. It then proposes strategies for fully exploiting large amounts of RAM when developing in Java, including use of alternative garbage collectors, off-heap memory in the JVM, open source and commercial caching solutions, and operating system caches. Code samples and performance test results are provided. Copyright © 2013 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.
79/125 Oracle PLSQL: Design consideration  / parallel_enable/ result_cache
تعلم اوراكل حتى الاحتراف Learn Oracle PLSQL EXAM 1Z0-144 https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC116
Views: 430 khaled alkhudari
Removing Query Hints with Plan Guides (Dear SQL DBA Episode 43)
If you need to add, remove, or replace hints from ad-hoc queries and you can't change the code, plan guides can help. See a demo of removing hints from parameterized TSQL run from an application, and get tips on how to make your plan guides work in SQL Server. Get code samples from this episode and links for more info here: https://sqlworkbooks.com/2017/05/removing-query-hints-with-plan-guides-dear-sql-dba-episode-43/
Views: 550 SQL Workbooks
Making Smart Scan for Exadata Work (Demonstration)
Making Smart Scan Work - Demo I'll go through a simple example of the kind of thing we need to think about when trying to achieve Smart Scan. Let me just enable tracing, run a simple query, select and * from *. Now, there's my query. The result set comes back, and how is it executed? An index full scan with the PK* index. The optimizer is intelligent enough to know that my query can be satisfied purely by reading the index and therefore it didn't have to go to the table at all. It looks very good. Well, it wasn't. Index full scans are an operation that cannot be offloaded. So, to execute that statement, every block of that index was delivered into the buffer cache of my own database instance, and the compute node then has to do the work of extracting those values. What can we do about it? One solution would be to hint the code. I select and demand an index fast full scan, and now we see the magic word "storage." An index fast full scan is offloadable, because an index fast full scan can do direct reads. An alternative approach? Well, you might not want to hint many, many thousands of lines of code. An alternative approach would be to do it through DDL. For example, take that index, make it invisible. Now run my statements without any hint at all, table access storage full, and that was offloaded. Having made the index invisible, Oracle has no option but a full-table scan, and a full-table scan is offloadable. Now, this means I have three options of this very simple example. I can let the optimizer get on with what it wants to do, and then I use an index that I'm doing block serving into the buffer cache. I can hint the code, index fast full scan. That's probably the best option for performance, but it's also the most work. In this intervening case, I make the index invisible so that it will still be used of course for enforcing the primary key constraints, but it will not be visible to the optimizer and therefore influence the optimizer towards using plans that can be offloaded the Smart Scan. Making the choice between this and influencing the optimizer in the correct direction is an extremely difficult job, and it is not always easy to determine what is going to be the best solution.
Views: 3002 SkillBuilders
How to create Virtual Columns in Oracle Database
How to create Virtual Columns in Oracle Database 12c When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMBER(3), salary1 AS (ROUND(salary*(1+comm1/100),2)), salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL, CONSTRAINT employees_pk PRIMARY KEY (id) ); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (1, 'JOHN', 'DOE', 100, 5, 10); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (2, 'JAYNE', 'DOE', 200, 10, 20); COMMIT; Querying the table shows the inserted data plus the derived commission-based salaries. SELECT * FROM employees; ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 JOHN DOE 100 5 10 105 110 2 JAYNE DOE 200 10 20 220 240 2 rows selected. SQL The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views. COLUMN data_default FORMAT A50 SELECT column_name, data_default FROM user_tab_columns WHERE table_name = 'EMPLOYEES'; COLUMN_NAME DATA_DEFAULT ------------------------------ -------------------------------------------------- ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2) SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2) 8 rows selected. SQL Notes and restrictions on virtual columns include: 1)Indexes defined against virtual columns are equivalent to function-based indexes. 2)Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML. 3)Tables containing virtual columns can still be eligible for result caching. 4)Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled: a)Constraint on the virtual column must be disabled and re-enabled. b)Indexes on the virtual column must be rebuilt. c)Materialized views that access the virtual column must be fully refreshed. d)The result cache must be flushed if cached queries have accessed the virtual column. e)Table statistics must be regathered. 5)Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables. 6)The expression used in the virtual column definition has the following restrictions: a.It cannot refer to another virtual column by name. b.It can only refer to columns defined in the same table. c.If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column. e.The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Views: 398 OracleDBA
Oracle database 12C | oracle DBA tutorial | oracle DBA certification training - Exolearn
Exolearn! provides the best ambiance to learn and prepare well for the Oracle DBA interview questions and assists in getting Oracle certification. Why Learn Oracle DBA Training? Oracle Database 12c is the most used database for maintaining the database system. The career in Oracle DBA training is good, you can get many other opportunities to opt in advance level of Oracle DBA. ExoLearn provides a best faculty and environment to learn and understand the Oracle DBA tutorial. We provide good technical support to solve any of your issues. Oracle Database certification will assist you to stand out in the group of similar skills candidates and also it add weights to your profile. Oracle DBA Training Course Target: Software installation and the creation of new Oracle Database 12c. An in-depth exploration of the Oracle database architecture, including memory, process and data structures, and the management of those structures. Specialized facilities intended to maximize the performance of enterprise-scale transactional applications, such as the shared SQL and PL/SQL cache, the result cache, the database buffer cache, and other similar components. You can take Oracle database certification exam. Management of security policies and procedures, including administration of user accounts, roles, privileges, and profiles. Utilization of advanced self-tuning and self-management capabilities, including the use of Oracle-Managed Files, database Advisers and other components of the Management Framework. Performance monitoring, problem troubleshooting, and resolving lock and conflict issues. Using the Oracle Enterprise Manager and SQL interfaces for administration tasks. Control over database support services, including the Oracle Database 12c Control, Oracle Net, and others. A primer on backup and recovery structures and strategies. Operating system-specific considerations for various platforms, especially Linux and MS Windows Server. Course Targeted Audience: Oracle web server and application server administrators. Technical consultants, support engineers, project managers and other technical management and support personnel. Oracle DBA Training Prerequisites: Basic knowledge of database concepts, techniques and the ability to use graphical user interface. Oracle DBA Training Format: Oracle database Training at ExoLearn will be instructor-led lecture and discussion. The course will end with hands-on labs experience along with project completion. Oracle certification of the course after successful completion of the quiz. Companies Using Oracle Database 12c: In 500 Fortune’s top listed companies they use Oracle Database. Google is one of the best examples for this.The career in Oracle database 12 c Is awesome, you get lots of opportunities. Oracle DBA Career Opportunities: Professionals with Oracle DBA skills have a better opportunity and their demand has been on the steady rise this year. Prepare your resume for Oracle database certification Website : https://www.exolearn.com/ Facebook : https://www.facebook.com/exolearn/ Twitter : https://twitter.com/exolearn Linkedin : https://in.linkedin.com/company/exolearn Blog : https://www.exolearn.com/blog/
ORACLE 11G. Tuning y Performance.
Grabación webinar que tuvo lugar el 5 de octubre. Recuerda asiste a webinars como este. Repasa la agenda en www.tecnowebinars.com
Views: 13663 Videositpuntocom
How to Cache Database Queries with Redis in Laravel - Redis Series Episode 4
In this short episode, I show you how you can cache database queries for a certain period of time. This is really nice when you have data you know won't change during x period of time, because once it's in cache, Laravel knows to automatically skip the database query and immediately deliver the requested content from RAM. Super fast, super easy. Here's the complete project throughout all episodes: https://gitlab.com/Christf24/RedisCast/tree/master/ Next episode will go over tagging with Redis (for blog posts, movie genres, or whatever else). Previous episode (Counting page views, and creating a "Top Viewed Article" list on home page): https://www.youtube.com/watch?v=ZMjw5WFPG8c Next episode (Tagging articles, products, movies, etc..): http://youtu.be/L2keoHFThwY
Views: 20221 Christophe Limpalair
Calibrate IO Test
Here is a video of us running the first calibrate_io test and retrieving the results. For the sake of saving time we are going to show the results from the previous calibrate_io run after kicking off calibrate_io. Also note we used nmon during the middle of the video to note the IO activity.
Views: 1092 HouseOfBrickTech
Oracle Database 11g New Features Demo: Adaptive Cursor Sharing
Learn 11g new performance feature that should solve the problem of bind variables and data skew. Dave Anderson of SkillBuilders.com/Oracle.
Views: 5113 SkillBuilders
Oracle 11g Pro Performance Tuning Ls 4: Pctfree parameter
In this tutorial you will learn how to improve oracle 11g performance by using PCTFREE parameter.
Views: 682 DBA Pro
Destiny 2 All Dreaming City Region Chest Locations (Dreaming City Region Chests Locations Guide)
Destiny 2 All Dreaming City Region Chest Locations (Dreaming City Region Chests Locations Guide) All gameplay recorded with - http://e.lga.to/360gametv This guide shows you all Dreaming City Region Chest Locations in Destiny 2. The Dreaming City are 12 Region Chests located. On your map you can spot a small symbol which shows where a Region Chest is located, after you collect a Region Chest the symbol disappears. 00:06 - Region Chest #01 (Divalian Mists) 00:40 - Region Chest #02 (Divalian Mists) 01:11 - Region Chest #03 (Divalian Mists) 02:05 - Region Chest #04 (Divalian Mists) 02:47 - Region Chest #05 (Rheasilvia) 03:44 - Region Chest #06 (Rheasilvia) 04:10 - Region Chest #07 (Rheasilvia) 05:00 - Region Chest #08 (Rheasilvia) 06:01 - Region Chest #09 (The Strand) 06:42 - Region Chest #10 (The Strand) 07:12 - Region Chest #11 (The Strand) 07:52 - Region Chest #12 (The Strand) Destiny 2 All Guides: https://www.youtube.com/playlist?list=PLuGZAFj5iqHfaxgbnnOMGr9ItUIl3R6TY Destiny 2 Forsaken Bounties: https://www.youtube.com/playlist?list=PLuGZAFj5iqHdHNBzVKw7bEYGAaUzXvGuS Destiny 2 Forsaken Guides: https://www.youtube.com/playlist?list=PLuGZAFj5iqHdbkxy71FaEvHkP1s0XfBEe Special Thanks to the Patreon Donators: cptObbes, suedwanderer, Dikau Support / Donate: Paypal: http://bit.ly/1JySiRV Patreon: https://www.patreon.com/360gametv Visit my sites / partner: Partner: http://e.lga.to/360gametv Twitter: http://twitter.com/360GameTV Subscribe: http://www.youtube.com/subscription_center?add_user=360GameTV Achievements / Trophies:
Views: 16601 360GameTV
Oracle SQL Tuning - Data Warehouse Star Transformations, Even with Standard Edition!
Joining large tables in a Data Warehouse will often generate star transformations in the execution plan. So, what is a star transformation? And, what if you don't have Enterprise Edition - so you can't build Bitmap Indexes? In this free tutorial, Oracle Master John Watson will demonstrate star transformations in EE and how to make them work even without bitmap indexes - Standard Edition can save you thousands in licensing fees! This is Part 3 of a 5 Part series. View all video tutorials at SkillBuilders.com/EqualSQL.
Views: 4525 SkillBuilders
WebLogic Server HTTP Session Replication Part 2
Part 1 of a 2-part recording showing how HTTP session replication works in WebLogic Server 10.3.4: the first recording showed how to use WebLogic Server's built-in clustering architecture to provide session replication for a "shopping cart" demo application - this second recording shows how to achieve the same result using Oracle Coherence to manage session data. The video also shows how to start, stop and monitor Coherence cache server processes using the WebLogic Server node manager.
Views: 9930 OracleWebLogic
#Kscope16 Interview: Steven Feuerstein, Oracle Corporation
http://kscope16.com http://kscope17.com To view Steven's presentations go to http://odtug.com. PL/SQL Goodies in Oracle Database 12c Topic: Database - Subtopic: PL/SQL Learn about the new features added to the PL/SQL language in Oracle Database 12c, from the UDF pragma to whitelisting with ACCESSIBLE BY. OK, I confess: They may not blow your mind, but they will improve your ability to build maintainable, performant applications with PL/SQL. The PL/SQL Compiler Does More Than Compile! Topic: Database - Subtopic: PL/SQL An exploration of compile-time warnings in PL/SQL: Did you know that the PL/SQL compiler will not only tell you when you have compile errors but also give you advice about how to improve the quality and performance of your code? Sure you did, but still you want to join Steven for a quick exploration of compile-time warnings: how to enable them, what they can do for you, and why you should let Oracle do more of your heavy lifting for you.
Views: 83 ODTUG
Tutorial 41: INDEX in Oracle. Functional Based Index.
Hi Friends! Here we are learning about Functional Based Index (FBI). Hope the concept and examples are clear to you. -- Link of Case Manipulation Function ( LOWER(), UPPER(), INITCAP() ) https://youtu.be/Ol-iVyp09fc -- Link of SQL LENGTH() Function https://youtu.be/I3gEsN_iYJo For any help/issue/suggestion/feedback let me know in comment box. Thanks. Happy Coding.
Views: 58 YourSmartCode
Manik Surtani discusses the evolution of JBoss Cache -  the Infinispan project
Manik Surtani was a key figure in the development of the JBoss Cache over the last decade, he now leads the evolution of JBoss Cache - the Infinispan project. He discusses some of the real-world applications that Infinispan is used in, and talks about the need for low-latency, highly available data grids in some of today's most critical applications. This interview was conducted by O'Reilly Media at the Oracle OpenWorld/JavaOne 2011 conferencein San Francisco, CA.
Views: 1088 O'Reilly