See the whole article: Perl dbi and Oracle, introduction, dbi is a perl module, well documented in both online sources and the "Programming the perl dbi" book by Alligator Descartes, Alistair Carty, tim Bunce and Linda mui (o'reilly, isbn: ) but still, i can see. That motivated me to write this article. Oracle has many features and data types, while using dbi with each particular feature is not always entirely trivial. The purpose of this article is to cover many of those uses from Perl practitioner's point of view. Here is what you can find in this article: The sole purpose of this article is to serve as an introductory reading for those who have never used the two together. Therefore, some knowledge of both Oracle and Perl is assumed, and although I will not try to show off my obfuscation abilities, this article still assumes that the reader has read "Learning Perl" and has some experience with Oracle rdbms. This article will not cover basic features of neither Perl nor Oracle.
Parsing meaning Archives - oracle dba updates- crb tech
Question 5: Please explain why sql server does not select the same query plan every time for the same code (with different parameters) and how sql server can be forced to use a specific query plan. The query plan is chosen based on the parameters and code being issued to the sql server optimizer. . Unfortunately, a slightly different query plan can cause the query to execute much longer and use more resources than another query with exactly the same code and only parameter synonym differences. The optimize for hint can be used to specify what parameter value we want sql server to use when creating the execution plan. This is a sql server 2005 and beyond hint. Additional information: Optimize parameter Driven queries with the optimize for hint Next Steps As you prepare for an interview, consider the interview tips. Check out these related tips: If you have a real "stumper of a question feel free to post it in the comments section for this tip. Last Update: About the author Since 2002, jeremy kadlec has delivered value to the global sql server community as an m co-founder and Edgewood Solutions sql server Consultant. View all my tips Related Resources. Mladen Gogala is the author of ". Easy Oracle php ".
Taking this step will prevent update statistics from running during an unexpected time of the resume day and cause performance problems. By setting this configuration it is necessary to manually update statistics on a regular basis. Additional information: The norecompute option of update statistics question 4: Explain a sql server deadlock, how a deadlock can be identified, how it is a performance problem and some techniques to correct deadlocks. A deadlock is a situation where 2 spids have data locked and cannot release their lock until the opposing spid releases their lock. Depending on the severity of the deadlock, meaning the amount of data that is locked and the number of spids that are trying to access the same data, an entire chain of spids can have locks and cause a number of deadlocks, resulting. Deadlocks can be identified by Profiler in either textual, graphical or xml format. Deadlocks are a performance problem because they can prevent 2 or more processes from being able to process data. . A deadlock chain can occur and impact hundreds of spids based on the data access patterns, number of users, object dependencies, etc. Deadlocks could require a database design change, t-sql coding change to access the objects in the same order, separating reporting and oltp applications, including nolock statements in select queries that can accept dirty data, etc.
However, when the nolock hint is used, dirty data is read which can compromise the results returned to the user. Additional information: Getting rid of some blocking issues with nolock question 5: Explain three different approaches to capture a query plan. Showplan_text showplan_all graphical query Plan _exec_query_optimizer_info _exec_query_plan _exec_query_stats question Difficulty advanced question 1: True or False - a left outer join is always faster than a not exists statement. False - with always being the operative word. . Depending on the situation the outer join may or may not be faster than a not exists statement. . It is necessary to test the techniques, review the query plans and tune the queries accordingly. Question 2: Name three different options to capture the input (code) for a query in sql server. Dbcc inputbuffer fn_get_sql _exec_sql_text Additional information: question 3: Explain why the norecompute option of update statistics is used. This command is used on a per table basis to prevent the table from having statistics automatically updated based on the 'auto Update Statistics' database configuration.
Sop drafter - sluneč
A query plan is the maison physical break down of the code being passed to the sql server optimizer. The value from a performance tuning perspective is that each component of the query can be understood and the percentage of resource utilization can be determined at a micro level. . As query tuning is being conducted, the detailed metrics can be reviewed to compare the individual coding techniques to determine the best alternative. Additional Information: question 3: True or False - it is always beneficial to configure tempDB with an equal number of fixed sized files as the number of cpu cores. False - with always being the operative word in the question. Depending on the version of sql server, the disk subsystem, load, queries, etc., a 1 to 1 ratio of files to cores may be necessary on high end sql servers with intense processing.
If you do not have that luxury, a starting point may to be have half the number of tempdb files as compared to cpu cores. This is a configuration to load test and monitor closely depending on the type of processing, load, hardware, etc. That your sql server is expected to support. Question 4: Explain the nolock optimizer hint and some proscons of using the hint. The nolock query hint allows sql server to ignore the normal locks that are placed and held for a transaction allowing the query to complete without having to wait for the first transaction to finish and therefore release the locks. This is one short term fix to help prevent locking, blocking or deadlocks.
Because sql server will scan the index or the table as opposed to seeking the data. . The scan operation is a much more costly operation than a seek. Often a slightly different approach can be used to prevent using the function in the where clause yielding a favorable query plan and high performance. Additional information: Performance tip: avoid functions in where clause. Question 4: How is it possible to capture the io and time statistics for your queries? Use the set statistics io and set statistics time settings in your queries or enable the settings in your Management Studio session.
Additional information: Getting io and time stats for your queries. Question 5: True or False - it is possible to correlate the performance monitor metrics with Profiler data in a single sql server native product? Question Difficulty moderate, question 1: How can I/O statistics be gathered and reviewed for individual database files? By using the fn_virtualfilestats function to capture the metrics. This process can be automated with a script to determine the file usage with numerous samples. Additional Information: Gathering I/O statistics down to the database file level question 2: What is a query plan and what is the value from a performance tuning perspective?
Delaware dba registration - doing Business As Filing
Third party applications - performance monitoring and tuning applications from vendors in the sql server community. Question 2: Explain how the hardware running sql server can help or hinder performance. Taxed resumes cpus will queue requests and hinder query performance. Insufficient memory could cause paging about resulting in slow query performance. Incorrectly configured disk drives could exacerbate io problems. Additional information: Hard Drive configurations for sql server and. Hardware 101 for dbas, question 3: Why is it important to avoid functions in the where clause?
Solution, question Difficulty easy, question 1: dissertation Name five different tools which can be used for performance tuning and their associated purpose. Performance monitorSystem Monitor - tool to capture macro level performance metrics. Additional information, profiler - tool to capture micro level performance metrics based on the statements issued by a login, against a database or from host name. Server Side Trace - system objects to write the detailed statement metrics to a table or file, similar to Profiler. Dynamic Management views and Functions - sql server objects with low level metrics to provide insight into a specific portion of sql server. The database engine, query plans, service Broker, etc. Management Studio's built-In Performance reports - ability to capture point in time metrics as pre-defined by microsoft. Custom scripts - custom scripts can be developed to monitor performance, determine io usage, monitor fragmentation, etc. All in an effort to improve performance.
10g, 11g, 11gR2 Not possible being a dba to create view / materialized view in different schema based on its private db link create view. From table1@DB_link * error at line 21: ora-02019: connection description for remote database not found Versions affected: Oracle 7, 8i, 9i, 10g, 11g (Fixed in 11gR2) Oracle workaround: connect as schema_owner grant create table, create materialized view to schema_owner not possible to grant easily read. Not possible easily to redirect user to a different schema versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2 alter session set current schema unfortunately this does not work for some situations. Imagine: select * from user_tables; still shows your user tables Not possible to assign a user to a default schema on logon Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2 Microsoft sql server (ms sql) implementation: Easily done with default database concept (why not. Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2 Microsoft sql server (ms sql) implementation: Easily done based on the database concept Oracle workaround: For that type of recovery an auxiliary instance needs to be build first where the actual recovery happens Add your own. Please share it with others using the social site of your choice: Filed under dbms comparison, oracle tags 10g, 11g, 11gR2, 7, 8i, 9i, database, dba, microsoft, oracle, schema, sql, sql server, table, workaround Previous article next article. By: Jeremy kadlec, read Comments (11 related Tips: More professional development Interview questions dba, problem. In the latest installment of the sql server interview questions, we will outline questions suitable for a dba or developer interview to assess the candidates skills related to sql server performance tuning.
Dba@sql create materialized view. Test_mv build immediate refresh on demand as select * from. Resource_reporting_v / select * from source_reporting_v error at line biography 5: ora-01031: insufficient privileges, oracle workaround: a) Use always sysdba account for that operation sys@sql create materialized view. Test_mv build immediate refresh on demand as select * from source_reporting_v materialized view created. B) Grant create table to the materialized view owner and only then create a view: alter session set current_schema hr; grant create table to hr; @mv. Sql revoke create table from HR; Not possible to grant read access only on PL/sql source code (procedures, packages, triggers, views) to different schema (except dba user, who has access on all PL/sql source code ). You can perform the following operations to give read access on the source code to other user but then other user can also execute the code.
Code for, performance from the Start - database Trends
Oracle introduces hundreds of new database features in every database release. At the same time some tiny small restrictions in the code exist for a long time that bother database developers and dbas at their daily work. Below i explore some of the common Oracle thesis restrictions i often encounter during database administration. I give the workarounds for most of them and compare some with Microsoft sql server implementations. So in, oracle database server: Not possible to prevent a schema user to perform following tasks: dropping own objects creating indexes on own tables and some more, versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2. Not possible for a dba user to create a db link in different schema. Versions affected: Oracle 7, 8i, 9i, 10g, 11g, 11gR2. Sql example: sys@sql alter session set current_schemascott; sys@sql create database link link1 connect to jane identified by doe using 'us_supply error at line 1: ora-01031: insufficient privileges, oracle workaround: grant rights to create a database link to a schema owner connect. Not possible for a dba user to create a materialized view in a different schema.