sql - Oracle 10g Full table scan(parallel access) 100x times faster than index access by rowid -

-

The product had a query that was running for several hours (5-6) hours. I looked into its execution plan, and found that it was ignoring parallel signs on a large table. Reason - This index was using table access by ROWID. So when I added a / * + full (huge_free) * / signal before the parallel (huge_free) sign, the query began to run in parallel, and it was less Finished in less than 3 minutes. I could not understand, for this reason, the performance of the huge difference could be the benefit of parallel FTS:

  1. If you have more useless If CPUs are then parallel operation is naturally fast.
  2. Parallel operation in 10g direct I / O bypass buffer cache, which means "buffer is busy waiting" or there is no risk of any other dispute for buffer cache.

Make sure that there are the above benefits, but then the following losses are still there:

  1. Parallel actions still need to be I / O, and Whatever it is for us to access in the table, it will be as much as the whole table scan and expensive (all reads physically)
  2. Parallel operations are not very scalable, which means That is, if there are not enough resources, it will slow down

Hands with the above knowledge, I see only one reason that could cause poor performance of the query when this index was used by the ROEDID - "Wait a busy buffer Like "some kind of dispute but this ARUR does not appear at the top 5 wait events. The top two events were "DB File Sequentially Read" and "DB File Scattered Reading". Is there anything else that has missed me to take into consideration? Please highlight me.

First of all, without knowing anything about your data versions, statistics, selectivity of your predictions , e.t.c. I suppose that instead of trying to use an index that's a big benefit you're seeing, by scanning a table, the index is not necessarily fast and table scans are not necessary. If you are using an array from an index to reach a line, then Oracle is limited to reading a single block (reads in sequential oracle terms) and it will be read several times a block if many in the block There are rows. A full table scan, on the other hand, reads good, efficient multiballak (read scattered in the words of Oracle). Of course, reading a single single block is going to be more efficient than reading a single multiball, but the reader is as much skilled as Multiballock, so the bite is read efficiently. In addition, if you are using an index, you will need to read several blocks from the index periodically to know the next line to read from the table.

You do not really need to read as much data as before the table. A table scan is more efficient than an index. Based on many other factors, the tipping point is probably in the 10-20% range (this is a very, very difficult estimate). Imagine that you had to get a bunch of phone book names and there was an index in the phone book in which the information was included on which you are filing and the page on which the entry is on. You can use the pointer to find the name of a person you want to see, flip the indicated page, record the information, flip back into the index, find the next name, flip back, etc. Or you can just start the first name, unless you get the name of interest, scan, record the information, and continue the scan. It does not take much time before ignoring index and getting better by reading from the table.

Adding equality does not decrease the amount of work you do in your query (in fact, adding parallel to query coordinates means that you are doing more work). It's just that you're doing that job in a short period of time, using the available resources of the server. If you are running a query with 6 parallel slaves, which can definitely allow the query to run at a total of 5 times faster (parallel queries clearly linearly due to overheads slightly). If this is the case, then you expect that the scan of the table was made 20 times faster by scanning the table and adding another aspect of 5 to add a similarity to its 100x improvement.


Comments

Post a Comment

Popular posts from this blog

java - org.apache.http.ProtocolException: Target host is not specified -

-
I have written a simple httprequest / response code and I am getting this error below: I used to type in my classpath httpclient, httpcore, common -Codecs and general logging. I am very new to Java and I do not know what's going on here. Please help me. code: import org.apache.http.client.HttpClient; Import org.apache.http.client.methods.HttpGet; Import org.apache.http.HttpResponse; Import org.apache.http.impl.client.HttpClientBuilder; Import org.apache.http.Header; Import org.apache.http.HttpHeaders; Public class UnshorteningUrl {public static zero main (string [] args throws exception {HttpGet request = zero; HTTP Client Client = HTTP ClientBuilder.credit (). Build (); Try {Request = New HTTP Gate ("trib.me/1lBFzSi"); HttpResponse httpResponse = client.execute (request); Header [] header = httpResponse.getHeaders (HttpHeaders.LOCATION); // Preconditions.checkState (headers.length == 1); String new url = header [0] .getValue (); System.out.println ("new url...
Read more

java - Gradle dependencies: compile project by relative path -

-
Is it possible to specify a dependency on another gradeal project in the granular (in android studios) outside the current project limits? For example, with a relative path like this: dependence {collection project ('../../ stdlib / dagger'}} MyApp (path / user / foo / workspace / mypad) The app (Path / User / Foo / Workspace / MyPass / App) And I have an Android Library Project with 3 subdomules: stdlib (path is / user / foo / Utilities (Path / User / Foo / Workspace / Stadlib / Utilis) http (Path / User / Foo / Workspace / Stadlib / HT) / Li> What I have to do is compile the Dagger, Util, http module in the MyApp project. The Stidelib Library module under heavy development And MyApp will grow in the form of Growth, so I do not want to push them into a Mewen repository. I am a little change. is likely to add another Gredle projects in some way? I will push the study library into the Maven repository after the source is in search of a temp...
Read more

ruby on rails - Object doesn't support #inspect when used with .include -

-
So I have participated in a glitch error that I do not even know how to debug. I am very new to the railing, so it has kicked my ass well when I use the .includes (school) seen by the student, it gives a blue box error, But when I look at any other model and .includes (school) , the school works just fine. class student & lt; ActiveRecord :: Base has has_many: relationships, foreign_key: "liker_id", dependent :: has_many: matches, foreign_key: "student_1_id", dependent :: deleted belong_to: school belong_to: conference belong_to: would_you_rather belong_to: home city belong_to: year is_to: Major and class school & lt; on the console activatecord :: Affiliate for the base: conference includes: respondent, class: "school", foreign_key: "rival_id" (e.g. For: School) Select SQL generated Select "School". * "School" from "school" "ID" IN (1) error message back NoMethodError: u...
Read more