Skip to main content

Home/ eDBA Services - Oracle & MySQL/ Group items tagged sql

Rss Feed Group items tagged

Filter: All | Bookmarks | Topics Simple Middle
Dariusz Owczarek

SQL Plan Management - 0 views

www.pythian.com/...nagement-be-the-11g-killer-app

dba oracle sql

shared by Dariusz Owczarek on 25 Nov 09 - Cached
  • Dariusz Owczarek on 25 Nov 09
     
    The SQL Plan Management new feature of Oracle 11g completes (replaces ?) the outlines by providing a new plan stability capability to Oracle 11g Enterprise Edition.
Dariusz Owczarek

Bind variables and bind variable peeking | Somewhere in between - 0 views

database.com.mk/?p=136

dba edba oracle sql

shared by Dariusz Owczarek on 07 Jun 10 - Cached
  • Each time you execute this statement; Oracle will convert it to an ASCII function and apply a hashing algorithm over it; than it will check if this SQL statement is already present in the SHARED POOL.
  • If the statement is in the SHARED POOL, Oracle will reuse (soft parse) it together with its execution plan. If the statement is not in the SHARED POOL, Oracle will have to do a hard parse.
  • Oracle’ CBO can generate more optimized execution plan if the he knows upfront the values of the filter predicated, meaning if the values are literals and not bind variables.
  • ...4 more annotations...
  • When you execute an SQL with bind variables, the value for the filter predicate is unknown.
  • In Oracle 8i, CBO will generate one execution plan, regardless of the input of “:a”. In Oracle 9i,10g CBO will wait until the cursor is opened, bind the value from the bind variable and then optimize the SQL. In Oracle 11g CBO has a new feature called “adaptive cursor sharing” which will be discussed in another post
  • Bind variable peeking is when Oracle’s CBO waits until he gets the value for the bind variable and then optimizes the SQL. But, this is very important: this is done in the hard parsing phase of the SQL.
  • When to use bind variables In OLTP system = YES When you execute many statements per second = YES Data Warehouse = NO Data Mining = NO End month reports = NO
Dariusz Owczarek

SQL Functions Quick Reference - 1 views

download.oracle.com/...sqlqr02.htm

dba oracle sql

shared by Dariusz Owczarek on 12 Jun 09 - Cached
  • Dariusz Owczarek on 12 Jun 09
     
    This chapter of Oracle Database SQL Language Quick Reference presents the syntax for SQL functions.
Dariusz Owczarek

SQL Syntax Quick Reference - 0 views

download.oracle.com/...sqlqr01.htm

dba oracle sql

shared by Dariusz Owczarek on 12 Jun 09 - Cached
  • ALTER INDEX [ schema. ]index { { deallocate_unused_clause | allocate_extent_clause | shrink_clause | parallel_clause | physical_attributes_clause | logging_clause } ... | rebuild_clause | PARAMETERS ( 'ODCI_parameters' ) ) | COMPILE | { ENABLE | DISABLE } | UNUSABLE | VISIBLE | INVISIBLE | RENAME TO new_name | COALESCE | { MONITORING | NOMONITORING } USAGE | UPDATE BLOCK REFERENCES | alter_index_partitioning } ;
  • ALTER ROLE role { NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ] package | EXTERNALLY | GLOBALLY } } ;
  • ALTER SEQUENCE [ schema. ] sequence { INCREMENT BY integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } } ... ;
  • ...31 more annotations...
  • ALTER SESSION { ADVISE { COMMIT | ROLLBACK | NOTHING } | CLOSE DATABASE LINK dblink | { ENABLE | DISABLE } COMMIT IN PROCEDURE | { ENABLE | DISABLE } GUARD | { ENABLE | DISABLE | FORCE } PARALLEL { DML | DDL | QUERY } [ PARALLEL integer ] | { ENABLE RESUMABLE [ TIMEOUT integer ] [ NAME string ] | DISABLE RESUMABLE } | alter_session_set_clause } ;
  • ALTER SYSTEM { archive_log_clause | checkpoint_clause | check_datafiles_clause | distributed_recov_clauses | FLUSH { SHARED_POOL | BUFFER_CACHE } | end_session_clauses | SWITCH LOGFILE | { SUSPEND | RESUME } | quiesce_clauses | rolling_migration_clauses | alter_system_security_clauses | shutdown_dispatcher_clause | REGISTER | SET alter_system_set_clause [ alter_system_set_clause ]... | RESET alter_system_reset_clause [ alter_system_reset_clause ]... } ;
  • CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password [ dblink_authentication ] } | dblink_authentication ]... [ USING connect_string ] ;
  • CREATE [ OR REPLACE ] DIRECTORY directory AS 'path_name' ;
  • CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index ON { cluster_index_clause | table_index_clause | bitmap_join_index_clause } [ UNUSABLE ] ;
  • CREATE PROFILE profile LIMIT { resource_parameters | password_parameters }... ;
  • CREATE ROLE role [ NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ] package | EXTERNALLY | GLOBALLY } ] ;
  • CREATE SEQUENCE [ schema. ] sequence [ { INCREMENT BY | START WITH } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } ]... ;
  • CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ schema. ] synonym FOR [ schema. ] object [ @ dblink ] ;
  • CREATE USER user IDENTIFIED { BY password | EXTERNALLY [ AS 'certificate_DN' ] | GLOBALLY [ AS '[ directory_DN ]' ] } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | { QUOTA { size_clause | UNLIMITED } ON tablespace }... EDO | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | { QUOTA { size_clause | UNLIMITED } ON tablespace }... | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } ]... ] ;
  • CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema.] view [ ( { alias [ inline_constraint... ] | out_of_line_constraint } [, { alias [ inline_constraint...] | out_of_line_constraint } ] ) | object_view_clause | XMLType_view_clause ] AS subquery [ subquery_restriction_clause ] ;
  • LOCK TABLE [ schema. ] { table | view } [ partition_extension_clause | @ dblink ] [, [ schema. ] { table | view } [ partition_extension_clause | @ dblink ] ]... IN lockmode MODE [ NOWAIT | WAIT integer ] ;
  • PURGE { { TABLE table | INDEX index } | { RECYCLEBIN | DBA_RECYCLEBIN } | TABLESPACE tablespace [ USER username ] } ;
  • GRANT { grant_system_privileges | grant_object_privileges } ;
  • ALTER PROFILE profile LIMIT { resource_parameters | password_parameters } ... ;
  • ALTER RESOURCE COST { { CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA } integer } ... ;
  • ALTER TABLE [ schema. ] table [ alter_table_properties | column_clauses | constraint_clauses | alter_table_partitioning | alter_external_table_clauses | move_table_clause ] [ enable_disable_clause | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS } ] ... ;
  • ALTER TABLESPACE tablespace { DEFAULT [ table_compression ] storage_clause | MINIMUM EXTENT size_clause | RESIZE size_clause | COALESCE | SHRINK SPACE [ KEEP size_clause] | RENAME TO new_tablespace_name | { BEGIN | END } BACKUP | datafile_tempfile_clauses | tablespace_logging_clauses | tablespace_group_clause | tablespace_state_clauses | autoextend_clause | flashback_mode_clause | tablespace_retention_clause } ;
  • ALTER USER { user { IDENTIFIED { BY password [ REPLACE old_password ] | EXTERNALLY [ AS 'certificate_DN' ] | GLOBALLY [ AS '[directory_DN]' ] } | DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | { QUOTA { size_clause | UNLIMITED } ON tablespace } ... | PROFILE profile | DEFAULT ROLE { role [, role ]... | ALL [ EXCEPT role [, role ] ... ] | NONE } | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } } ... | user [, user ]... proxy_clause } ;
  • ALTER VIEW [ schema. ] view { ADD out_of_line_constraint | MODIFY CONSTRAINT constraint { RELY | NORELY } | DROP { CONSTRAINT constraint | PRIMARY KEY | UNIQUE (column [, column ]...) } | COMPILE } ;
  • CREATE SCHEMA AUTHORIZATION schema { create_table_statement | create_view_statement | grant_statement }... ;
  • CREATE [ GLOBAL TEMPORARY ] TABLE [ schema. ] table { relational_table | object_table | XMLType_table }
  • CREATE [ BIGFILE | SMALLFILE ] { permanent_tablespace_clause | temporary_tablespace_clause | undo_tablespace_clause } ;
  • DELETE [ hint ] [ FROM ] { dml_table_expression_clause | ONLY (dml_table_expression_clause) } [ t_alias ] [ where_clause ] [ returning_clause ] [error_logging_clause];
  • INSERT [ hint ] { single_table_insert | multi_table_insert } ;
  • MERGE [ hint ] INTO [ schema. ] { table | view } [ t_alias ] USING { [ schema. ] { table | view } | subquery } [ t_alias ] ON ( condition ) [ merge_update_clause ] [ merge_insert_clause ] [ error_logging_clause ] ;
  • RENAME old_name TO new_name ;
  • REVOKE { revoke_system_privileges | revoke_object_privileges } ;
  • SELECT [ subquery_factoring_clause ] subquery [ for_update_clause ] ;
  • TRUNCATE TABLE [schema.] table [ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ] [ {DROP | REUSE} STORAGE ] ;
  • UPDATE [ hint ] { dml_table_expression_clause | ONLY (dml_table_expression_clause) } [ t_alias ] update_set_clause [ where_clause ] [ returning_clause ] [error_logging_clause] ;
  • Dariusz Owczarek on 12 Jun 09
     
    This chapter of Oracle Database SQL Language Quick Reference presents the syntax for Oracle SQL statements.
Dariusz Owczarek

Oracle PL/SQL Examples - 2 views

www.java2s.com/...CatalogOracle.htm

dba oracle pl_sql sql

shared by Dariusz Owczarek on 10 Jun 09 - Cached
Dariusz Owczarek

Oracle GoldenGate Best Practices and Tips - 0 views

decipherllc.blogspot.com/...engate-best-practices-and.html

oracle goldengate

shared by Dariusz Owczarek on 24 Feb 11 - No Cached
  • PARALLEL PROCESSING Ensure the system has enough shared memory. GoldenGate runs as an Oracle process. Each Extract or Replicat process requires upwards of 25-50 MB of system shared memory. This means less memory for the Oracle DBMS, especially the SGA. Use parallel Replicat groups on the target system to reduce latency thru parallelism. Consider parallel Extract groups for tables that are fetch intensive (e.g., those that trigger SQL procedures). Group tables that have R.I. to each other in the same Extract-Replicat pair. Pair each Replicat with its own trail and corresponding Extract process. When using parallel Replicats, configure each one to process a different portion of the overall data.
  • PASSTHRU PARAMETER Consider using this parameter if there is no filtering, conversion or mapping required and you’re using DATAPUMP. In pass-through mode, the Extract process does not look up table definitions, either from the database or from a data definitions file. Pass-through mode increases the throughput of the data pump, because all of the functionality that looks up object definitions is bypassed. This saves database fetches to improve performance.
  • INSERTAPPEND A new GoldenGate 10.4 feature. Use for large transactions . Puts records at end of table rather than doing a more costly insert into other areas of table.
  • ...6 more annotations...
  • To reduce bandwidth requirements: Use compression options of the RMTHOST parameter to compress data before it is sent across the network. Weigh the benefits of compression against the CPU resources that are required to perform the compression.
  • To increase the TCP/IP packet size: Use the TCPBUFSIZE option of the RMTHOST parameter to increase the size of the TCP socket buffer that Extract maintains. By increasing the size of the buffer, you can send larger packets to the target system. Consult with Network Support before setting TCPBUFSIZE.
  • Use SQL Arrays The BATCHSQL parameter will increase the performance of Replicat. BATCHSQL causes Replicat to create arrays for similar SQL statements and apply them at an accelerated rate. Normally, Replicat applies one SQL statement at a time.
  • Use the CHECKPOINTSECS in Extract or Replicat; if increased, less frequent checkpoints; increases data to be reprocessed if process fails; keep transaction logs available in case of reprocessing
  • Use the GROUPTRANSOPS; increases number of SQL operations in a Replicat ; reduces I/O to checkpoint file and checkpoint table.
  • Data Filtering and Conversion: Use primary Extract for data capture only. Use a data pump on the source to perform filtering and thereby send less data over the network. Alternatively, use Replicat for conversion and, if the network can handle large amounts of data, also for filtering.
  • Dariusz Owczarek on 24 Feb 11
     
    Oracle GoldenGate Best Practices and Tips
Dariusz Owczarek

Oracle SQL Tuning tricks - 0 views

www.dba-oracle.com/t_sql_tuning_tricks.htm

dba oracle tuning

shared by Dariusz Owczarek on 28 Oct 09 - Cached
  • Dariusz Owczarek on 28 Oct 09
     
    Oracle SQL tuning experts use an endless number of techniques, but there are some common tricks that can be used to optimize all SQL.
Dariusz Owczarek

Oracle Diagnostic Tools - 0 views

skillport.books24x7.com/...viewer.asp

dba edba oracle

shared by Dariusz Owczarek on 04 Jun 10 - Cached
  • Enterprise Manager A graphical all-purpose tool that can be used to identify when a spike occurred, drill down to the cause, and examine ADDM recommendations. The benefit of a graphical representation of performance data is visible (pun intended). Data visualizations display any skew directly.
  • Automatic Database Diagnostic Monitor (ADDM) An expert system that automatically identifies and recommends solutions for many instance-wide performance problems. Best used for longer-duration performance problems (that is, problems that are continuous or ongoing for a large proportion of the snapshot interval). The symptoms and problems are available by running the ADDM report, and through Enterprise Manager.
  • Active Session History (ASH) An all-purpose tool providing data that is useful when investigating system-wide problems, shorter-duration spikes, or smaller-scoped problems (for example, for a specific user, or SQL, or a module/action).The advantage of using ASH data when compared to other diagnostic information is that the data is of a finer granularity. This allows you to look at a problem to identify how the symptoms "build up," or allows you to determine exactly which resources are involved and who is using them. The ASH data can be queried directly or accessed via a targeted ASH report.
  • ...3 more annotations...
  • Automatic Workload Repository (AWR) Instance-wide summary data that is used when ADDM is not able to identify the problem in the system, and the problem is of longer duration. Also used to verify the ADDM analysis. The data can be queried directly but is most often accessed via the AWR instance report.
  • Statspack (SP) Instance-wide summary data used to manually diagnose performance problems. You should use SP when you are not licensed for the Diagnostics Pack, and so can't use ADDM or AWR.
  • SQL trace This traces the execution flow (resource utilization, execution plan, and waits) by SQL statement. The information can be used to examine the flow and resource utilization for a specific user, feature, or SQL statement identified as problematic.
Dariusz Owczarek

SQL SELECT Syntax - 0 views

download.oracle.com/...statements_10002.htm

dba oracle sql

shared by Dariusz Owczarek on 12 Jun 09 - Cached
  • Dariusz Owczarek on 12 Jun 09
     
    SQL SELECT statement or subquery is used to retrieve data from one or more tables, object tables, views, object views, or materialized views.
Dariusz Owczarek

SQL GRANT Syntax - 0 views

download.oracle.com/...statements_9013.htm

dba oracle sql

shared by Dariusz Owczarek on 12 Jun 09 - Cached
  • Dariusz Owczarek on 12 Jun 09
     
    SQL GRANT statement is used to grant system or object privileges to users and roles. It is also used to grant roles to users and roles.
Dariusz Owczarek

Meaning of Oracle Key Statistics - 0 views

skillport.books24x7.com/...viewer.asp

dba edba oracle performance

shared by Dariusz Owczarek on 08 Jun 10 - Cached
  • Statistics are somewhat fallible in that they are seldom 100 percent accurate, but in most cases they do sufficiently indicate what was intended. Be sure you understand what each statistic represents and the units used (there is a big difference between microseconds and centiseconds).
  • Time-breakdown statistics (Time Model) make it significantly easier to determine the type of operations that are consuming resources in the database.
  • DB time: Time spent by all user processes in the database (that is,. non-idle wait time + CPU time).
  • ...11 more annotations...
  • DB CPU: Time spent by all user processes on the CPU, in Oracle code. On most systems, the majority of time will be spent in DB CPU, SQL execute elapsed time, or PL/SQL execution elapsed time (and possibly Java). Time spent in parse and connection management should be low, so if the levels indicate a high percentage of DB time, a problem exists in the relevant area. You can use this data to correlate with Top 5 Timed Events and Load Profile.
  • Database time (DB time) is an important time-based statistic: it measures the total time spent in the database by active sessions (that is, foreground user processes either actively working or actively waiting in a database call). DB time includes CPU time, I/O time, and other non-idle wait time.
  • Because DB time represents the sum of the time that all sessions spend in database calls, it can easily exceed the elapsed wall-clock time.
  • The objective of tuning an Oracle system could be stated as reducing the time that users spend in performing actions in the database, or simply reducing DB time.
  • Wait time is artificially inflated when the host is CPU bound because the wait time includes the actual time spent waiting (for example, waiting for a disk I/O), as well as the time spent by the process in the OS run-queue waiting to be rescheduled.
  • Therefore, when the host is CPU bound, it is important to reduce CPU utilization before addressing wait-related problems, because otherwise you may be addressing the wrong problem.
  • You can use ASH data to estimate DB time when the actual DB time is not available—for example, if a session has exited. Because ASH samples active sessions every second, you can estimate DB time (in seconds) to be the number of ASH samples counted.
  • V$OSSTAT is OS-related resource utilization data that the Oracle server collects. The statistics available vary by platform. You can use V$OSSTAT to determine CPU utilization (BUSY_TICKS and IDLE_TICKS), and also compare this to the host's CPU utilization statistics. Also look for high OS_CPU_WAIT_TIME, which may indicate the host is CPU bound.
  • V$OSSTAT statistics can be compared with the Time Model statistics, for example to determine how much of the total CPU used on the host is attributable to this instance: DB CPU / BUSY_TICKS.
  • note that the units for these two statistics differ.
  • In 10g, each wait event (V$SYSTEM_EVENT) is classified into one of nine wait classes: Application, Commit, Concurrency, Configuration, Network, Other, System I/O, User I/O, and Idle. The class names are reasonably self-explanatory except Other, which is a catchall bucket for wait events that should not ever contribute any significant wait time.
Dariusz Owczarek

Adaptive Cursor Sharing - 0 views

database.com.mk/?p=333

dba edba oracle

shared by Dariusz Owczarek on 06 May 10 - Cached
  • Adaptive Cursor Sharing is a new feature starting from Oracle version 11g release 1. The idea behind is to improve the execution plans for statements with bind variables. CBO has been enhanced to allow multiple execution plans to be used for a single statement with bind variables, without hard parsing the SQL.
  • There is no special way to configure ACS. It is on by default and of course there is a hidden initialization parameter to turn it off if needed. Key role in the process of decision whether ACS will be used for a particular statement are: Two new columns in V$SQL view (IS_BIND_SENSITIVE and IS_BIND_AWARE) Three new views (V$SQL_CS_HISTOGRAM, V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS)
Dariusz Owczarek

Oracle 11g Database Online Documentation - 0 views

www.oracle.com/homepage

dba oracle documentation

shared by Dariusz Owczarek on 30 Jul 09 - Cached
  • Reference HTML
  • Concepts HTML
  • SQL Language Reference HTML
Dariusz Owczarek

Oracle's V$ Views - Dynamic Performance Views - 1 views

www.adp-gmbh.ch/...dynamic_performance_views.html

dba oracle v$ dictionary

shared by Dariusz Owczarek on 27 Jun 09 - Cached
  • v$database This view lets you access database information.
  • v$datafile This view contains an entry for each datafile of the database.
  • v$dataguard_status Shows error messages in a data guard environment.
  • ...11 more annotations...
  • v$flash_recovery_area_usage See also v$recovery_file_dest
  • v$lock This view stores all information relating to locks in the database.
  • v$mystat This view records statistical data about the session that accesses it.
  • v$parameter Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora).
  • v$session
  • v$session_longops Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded.
  • v$session_wait This views shows what wait event each session is waiting for, or what the last event was that it waited for.
  • v$session_wait_history This view is new in Oracle 10g and allows improved timing and statistics.
  • v$sqlarea Join v$sqlarea's address with v$session's sql_address.
  • v$sql_bind_capture New with Oracle 10g This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
  • v$version Use this view to find out what version you actually work on: select * from v$version;
  • Dariusz Owczarek on 27 Jun 09
     
    Oracle's data dictionary provides information that Oracle needs to perform its tasks. The data dictionary is stored in table structures owned by SYS. Their content is exposed through dictionary views. Dynamic performance views (so called v$ views) are part of Oracle's data dictionary.
1 - 14 of 14
Showing 20▼ items per page